Minimum distance of a location; one table of points compared to a set of points SQL 2012 I am trying to find the quickest way to find the minimum distance of one point to a table of points.  The only caveat is that the table of the points I'm trying to find the minimum distance of is 150K single points.

Or better explained Table A has 150K rows/points, Table B 1500 points.  I want to know for every row in Table A what is the minimum distance from all those listed in Table B.

I have a function that does the distance calc, as an added column to the Table A.  It just takes a VERY LONG time.  Table B has a spatial index.

This is what I have:

select a.*,
       dbo.fxn_distance(geography::STPointFromText('POINT(' +
       CAST([Long] AS VARCHAR(20)) + ' ' + CAST([Lat] AS
       VARCHAR(20)) +    ')', 4326)) as DistAway
from Table A a

my function:

create function fxn_distance
(@pointTableA geography
)
returns float
as
begin

declare @distance float

select top 1 @distance = b.GeoLocation.STDistance(@pointTableA)
from TableB b
where geolocation.STDistance(@pointTableA) is not null
order by geolocation.STDistance(@pointTableA)

return @distance
end

Sorry if I am totally a newbie to this, and I know the solution is probably simple, but I just can't wrap my head around this.  So to hopefully clarify: I need to pass the lat/long of every point in Table A and see what the min distance is compared to every row in Table B, for every row in Table A.  I care what the actual distance away is, but not the actual point from Table B.

Any help appreciated.

You may have better performance if you convert the function into a table-valued-function.

Here, I setup the test-bed:

USE tempdb;
CREATE TABLE dbo.TableA
(
    LAT DECIMAL(10,5)
    , LON DECIMAL(10,5)
);

CREATE TABLE TableB
(
    Geolocation GEOGRAPHY NOT NULL
);
GO

Here is the table-valued-function, which is basically your function except this returns a table.

CREATE FUNCTION dbo.fxn_distance
(
    @pointTableA GEOGRAPHY
)
returns table
as return
(
    SELECT TOP 1 Distance = b.GeoLocation.STDistance(@pointTableA)
    FROM TableB b
    WHERE geolocation.STDistance(@pointTableA) IS NOT NULL
    ORDER BY geolocation.STDistance(@pointTableA)
)
GO

Insert a simple test-row into each of the two tables:

INSERT INTO dbo.TableA(LAT, LON)
VALUES (49.0,170.0);

INSERT INTO dbo.TableB(Geolocation)
VALUES (geography::STGeomFromText(
    'LINESTRING(-122.360 47.656, -122.343 47.656)', 4326)
);

The query that uses the inline TVF to determine the closest point:

SELECT a.*
    , d.Distance --This is the distance calculated by the TVF.
FROM dbo.TableA a
CROSS APPLY dbo.fxn_distance(geography::STPointFromText('POINT(' +
       CAST(A.LON AS VARCHAR(20)) + ' ' + CAST(A.LAT AS
       VARCHAR(20)) + ')', 4326)) d ;


How to compare xmin and txid_current() after transactions ID wraparound? Besides its regular columns, Postgres tables also have various system columns available. One of them, xmin, stores the transaction ID used to create a row. Its data type is xid, a four byte integer that wraps around at some point (i.e. not necessarily unique). The function txid_current() in turn returns the current transaction ID, but as bigint, because it "is extended with an "epoch" counter so it will not wrap around during the life of an installation" (to quote the manual).

If transactions wraparound did not yet happen, both values seem to match:

# CREATE TABLE test (label text);
CREATE TABLE
# INSERT INTO test VALUES ('test') RETURNING txid_current();
 txid_current
--------------
   674500
(1 row)
INSERT 0 1
# SELECT xmin FROM test;
  xmin
--------
 674500
(1 row)

But I wonder: are these two values always comparable? As far as I understand, txid_current() will continue to deliver unique values after transaction ID wraparound (at most 2^32 transactions) and xmin will start from zero. This means both start to return different values at that point?

And if this is true, is there a way to extract regular xid of a txid_current() result so that it would match xmin entries in a table (e.g. casting txid_current() to integer)?

Edit: Make it clearer that I care about what happens after a transaction ID wraparound, which very likely happens long before 2^32 transactions. Thanks to Daniel Vérité for noting this in the comments.

You can strip the added epoch to match the value in xmin, i.e. extract the 4-byte integer from the bigint. Since xmin is type xid and not (signed!) integer, we compare the text representation instead:

SELECT * FROM test
WHERE  xmin::text = (txid_current() % (2^32)::bigint)::text;

Detailed explanation:

How to view tuples changed in a PostgreSQL transaction?


Non Clustered Index using INCLUDE I am working on Consolidation INDEX.
I have 3 index on a table

Create NONCLUSTERED INDEX IX1 on TableA(a asc,b asc) INCLUDE(U,V,X,Y,Z)
Create NONCLUSTERED INDEX IX2 on TableA(a asc,b asc) INCLUDE(K,L,X,Y,Z)
Create NONCLUSTERED INDEX IX3 on TableA(a asc,b asc,c asc) INCLUDE(U,V,X,Y,Z)

So as the composite key is same and the order is also same.
I thought we can have 1 index rather than  3

 Create NONCLUSTERED INDEX IX4 on TableA(a asc,b asc,c asc) INCLUDE(U,V,X,Y,Z,K,L)

So , i have 2 Question:
1 : Will creating new index for the 3 will work?
2 : Some developers tell me that difference in include statement as now I have (U,V,X,Y,Z,K,L) all in one can have significant difference in performance so ,I can't club them in 1?

Yes it will work.
It will have some difference in performance but will it be significant or not depends on the indexes usage. Scan operation will become more expensive because an index row is wider on the leaf level. On the other hand you will have one index in your buffer pool instead of three.

I would start from investigating the indexes current usage to make a reasonable decision.

Check user_seeks, user_scans, user_lookups columns:

    SELECT i.name, s.*,si.*
    FROM sys.dm_db_index_usage_stats s
        JOIN sys.indexes i ON i.object_id = s.object_id
            AND i.index_id = s.index_id
        JOIN sys.sysindexes si ON si.id = i.object_id
            AND si.indid = i.index_id
    WHERE s.object_id = OBJECT_ID('YourTableName')
        AND s.database_id = DB_ID()
    ORDER BY i.name

Check query plans for a particular index:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @IndexName AS NVARCHAR(128) = 'IX1';

SET @IndexName = QUOTENAME(@IndexName);

-- Dig into the plan cache and find all plans using this index
;WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
DB_NAME(x.dbid),
x.ObjectName,
stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text,
obj.value('(@Database)[1]', 'varchar(128)') AS DatabaseName,
obj.value('(@Schema)[1]', 'varchar(128)') AS SchemaName,
obj.value('(@Table)[1]', 'varchar(128)') AS TableName,
obj.value('(@Index)[1]', 'varchar(128)') AS IndexName,
obj.value('(@IndexKind)[1]', 'varchar(128)') AS IndexKind,
x.plan_handle,
x.query_plan
FROM (
    SELECT try_CONVERT(XML, qp.query_plan) AS query_plan, cp.plan_handle, OBJECT_NAME(qp.objectid, qp.dbid) AS ObjectName, qp.objectid, qp.dbid
    FROM sys.dm_exec_cached_plans AS cp
        JOIN sys.dm_exec_query_stats s ON s.plan_handle = cp.plan_handle
        cross apply sys.dm_exec_text_query_plan (s.plan_handle, s.statement_start_offset, s.statement_end_offset) AS qp
) x
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements') AS batch(stmt)
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)
OPTION(MAXDOP 1, RECOMPILE);


INSTEAD OF INSERT trigger Consider this a follow-up to BULK INSERT: Trials and Tribulations

I have the following VIEW defined and would like to insert into the multiple underlying tables when sending a BULK INSERT from a CSV.

CREATE VIEW dbo.StagingViewB as
  SELECT
    Q.[SQSQueue],
    Q.[SQSMessageID],
    D.[ReceiverID],
    Q.[DLOrder],
    D.[DetectionDate],
    D.[DetectionMS],
    D.[TagID],
    M.[TxAmplitude],
    M.[TxOffset],
    M.[TxNBW],
    M.[TxChkSum]
  FROM         [StQueue] as Q
    INNER JOIN [StDetection] as D ON Q.eID=D.eID
    INNER JOIN [StDetMeta] as M ON Q.eID=D.eID
WITH VIEW_METADATA

I want an underlying autoincrement primary key (eID) to tie the 3 underlying tables together.  In other words, this is the StQueue DDL

CREATE TABLE [dbo].[StQueue](
    [eID] [bigint] IDENTITY(1,1) NOT NULL,
    [SQSQueue] [varchar](25) NULL,
    [SQSMessageID] [varchar](50) NULL,
    [DLOrder] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [eID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The Q,M,D aliased tables all have eID, but I'm willing to remove the IDENTITY(1,1) part of the definition (on all except one), as I don't think it's wise to trust that three separate tables will remain synchronous with respect to the value of eID for the next insert.

To avoid this issue, should I do a SET @ID = COALESCE(IDENT_CURRENT('StQueue'),0)+1
or

SET @ID = SELECT MAX(eID) FROM StQueue
SET @ID = COALESCE(ID,0)+1

And then do

INSERT INTO StQueue ([SQSQueue], [SQSMessageID],[DLOrder])
  VALUES (SQSQueue, SQSMessageID, DLOrder) From Inserted
INSERT INTO StDetection (eID, ReceiverID, DetectionDate, DetectionMS, TagID)
  VALUES (@ID, ReceiverID, DetectionDate, DetectionMS, TagID) FROM Inserted
INSERT INTO StDetMeta (eID, [TxAmplitude], [TxOffset], [TxNBW], [TxChkSum])
  VALUES (@ID, [TxAmplitude], [TxOffset], [TxNBW], [TxChkSum]) FROM Inserted

Microsoft warns:

  Be cautious about using IDENT_CURRENT to predict the next generated
  identity value. The actual generated value may be different from
  IDENT_CURRENT plus IDENT_INCR because of insertions performed by other
  sessions.

I assume this warning applies equally to the MAX-based method. Should I INSERT...WITH TABLOCK on the underlying tables to avoid that issue? Or would the multi-session/thread issue still be a problem. The problem might still exist because the computation for the next ID number happens before attempting to grab a lock, discovering it's already locked, and spinning wheels until the existing lock is released, the value would be stale any time a lock is encountered or a race exists where two threads are trying to grab the attention of the table (establish a lock) at the same time?  Is there a way I can request a lock for all three tables at once? Would this be wise to do before grabbing IDENT_CURRENT('StQueue')?

Will this whole process be slower than BULK INSERTing and then SELECT eID,x,y,z from a single master table into the 3 "child" tables? Would iterating through a CURSOR be worse?

I ended up abandoning the INSTEAD OF INSERT trigger method initially asked about, as syncing the unique ID across the several tables otherwise was proving problematic.

My current solution, involving the MERGE syntax, which as @AaronBertrand blogged about here, may be bug-ridden.  For example, it may be subject to unexpected sessions of deadlock down the line. But the following works for now:P

CREATE TABLE TxStaging (
    [eID] [bigint] IDENTITY(1,1) NOT NULL,
    [SQueue] [varchar](25) NULL,
    [SMessageID] [varchar](50) NULL,
    [RecID] [int] NOT NULL,
    [DLOrder] [int] NOT NULL,
    [DetectionDate] [datetime2](7) NOT NULL,
    [DetectionUS] [decimal](7, 6) NOT NULL,
    [TagID] [varchar](4) NOT NULL,
    [TxAmplitude] [int] NULL,
    [TxOffset] [int] NULL,
    [TxNBW] [int] NULL,
    [TxChkSum] [varchar](25) NULL,
PRIMARY KEY CLUSTERED ( [eID] ASC )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StQueue](
    [eID] [bigint] /*IDENTITY(1,1)*/ NOT NULL,
    [SQueue] [varchar](25) NULL,
    [SMessageID] [varchar](50) NULL,
    [DLOrder] [int] NOT NULL,
PRIMARY KEY CLUSTERED ( [eID] ASC )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StDets](
    [eID] [bigint] /*IDENTITY(1,1)*/ NOT NULL,
    [RecID] [int] NOT NULL,
    [DetectionDate] [datetime2] NOT NULL, --(7)?
--  [DetectionUS] [decimal](7, 6) NULL,
    [TagID] [varchar](4) NOT NULL,
PRIMARY KEY CLUSTERED ( [eID] ASC )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StDetMeta](
    [eID] [bigint] /*IDENTITY(1,1)*/ NOT NULL,
    [TxAmplitude] [int] NULL,
    [TxOffset] [int] NULL,
    [TxNBW] [int] NULL,
    [TxChkSum] [varchar](25) NULL,
    [NeworDup] int NULL, --unevaluated = NULL; New = 0; Duplicated_in_this_staging_table = 1; Duplicated_in_main_table = 2
PRIMARY KEY CLUSTERED ( [eID] ASC )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE VIEW InsertView as
SELECT
    [SQueue],
    [SMessageID],
    [RecID],
    [DLOrder],
    [DetectionDate],
    [DetectionUS],
    [TagID],
    [TxAmplitude],
    [TxOffset],
    [TxNBW],
    [TxChkSum]
FROM TxStaging
GO

ALTER PROCEDURE [dbo].[usp_PropegateSubTables]  --CREATE or ALTER
AS BEGIN
    MERGE INTO realtimetest.dbo.StQueue WITH (HOLDLOCK) as T
        USING realtimetest.dbo.TxStaging as S on T.eID=S.eID
        WHEN MATCHED THEN
            UPDATE SET T.SQueue=S.SQueue, T.[SMessageID]=S.[SMessageID], T.[DLOrder]=S.[DLOrder]
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (eID, [SQueue], [SMessageID], [DLOrder])
            VALUES (S.eID, S.[SQueue], S.[SMessageID], S.[DLOrder]);
    MERGE INTO realtimetest.dbo.StDets WITH (HOLDLOCK) as T
        USING realtimetest.dbo.TxStaging as S on T.eID=S.eID
        WHEN MATCHED THEN
            UPDATE SET T.RecID=S.RecID, T.DetectionDate=DATEADD(mcs,CAST(S.DetectionUS*1000000.0 as INT),S.DetectionDate), T.TagID=S.TagID
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (eID, RecID, DetectionDate, TagID)
            VALUES (S.eID, S.RecID, DATEADD(mcs,CAST(S.DetectionUS*1000000.0 as INT),S.DetectionDate), S.TagID);
    MERGE INTO realtimetest.dbo.StDetMeta WITH (HOLDLOCK) as T
        USING realtimetest.dbo.TxStaging as S on T.eID=S.eID
        WHEN MATCHED THEN
            UPDATE SET T.[TxAmplitude]=S.[TxAmplitude], T.[TxOffset]=S.[TxOffset], T.[TxNBW]=S.[TxNBW], T.[TxChkSum]=S.[TxChkSum]
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (eID, [TxAmplitude], [TxOffset], [TxNBW], [TxChkSum])
            VALUES (S.eID, S.[TxAmplitude], S.[TxOffset], S.[TxNBW], [TxChkSum]);
END
GO

Python code to fill TxStaging and then the child tables (has some other Try: type logic not included here)

tname = 'InsertView'
fname = 'S:/path/to/In.csv'
fieldSep = ','
linestart = 1
ename = 'S:/path/to/In.err'
sqlconn.execute_non_query("BULK INSERT {} FROM '{}' WITH ( FIELDTERMINATOR = '{}', ROWTERMINATOR = '{}', MAXERRORS = 1024, FIRSTROW = {}, KEEPNULLS, ERRORFILE = '{}', TABLOCK )".format(tname, fname, fieldSep, chr(10), linestart, ename))
procObj = sqlconn.init_procedure('usp_PropegateSubTables')
procObj.execute()
qstring = "DELETE FROM {}".format(tname) #TO DO: should check for presence of error file before going ahead and deleting this table.
    # clear table/view after propagated, but leave counter valid. Don't use TRUNCATE for this reason
    # TRUNCATE could be used if proceeded by a "check max eID" and then a `DBCC checkident(tname, RESEED, @maxeID)`
sqlconn.execute_non_query(qstring)


Will cleaning data cache help to resolve compiler memory pressure? I currently experience RESOURCE_SEMAPHORE_QUERY_COMPILE  waits causing CPU spike 100% that lasts 10-20 mins and disappears only by completely stopping traffic.
I figured it could be a memory pressure and wondering if reducing indexes size will help with memory pressure.

Take a look at this reference on MSDN regarding sys.dm_os_wait_stats.  Here is a quote from the above reference regarding RESOURCE_SEMAPHORE_QUERY_COMPILE:

  Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.

This wait type, as quoted above, is from too many compilations happening at a time.  What it sounds like, if you are consistently seeing this wait type (unlike going from a cold plan cache) then you have too high of an ad hoc workload.  In other words, you may be experiencing the lack of plan reuse (a bunch of plans that aren't parameterized/prepared, or not using stored procedures and therefore a new ad hoc query comes in since there is no plan that can be reused it'll compile a new one).

There are a few ways to remedy this, if that is indeed the case.  The real way to fix this is to ensure that prepared plans and stored procedures are being used.  A brute force method is by setting the database to utilize forced parameterization.  I would save the latter as a last resort, as there could be some downsides to forcing parameterization.

There are a few ways to keep an eye on how your plan reuse is looking.  The easiest (in my opinion) would be to capture a few perfmon counters for the instance:

\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec

It's a pretty good rule of thumb that compilations per sec shouldn't exceed 10% of batch requests per second, and recompilations per second shouldn't exceed 10% of compilations per second.  I typically go from there when troubleshooting plan cache reuse.  Feel free to monitor these counters, and post the results in your question and I can help interpret.

And to answer your direct questions, no reducing index sizes or clearing the data cache will not alleviate your issues.


Which SQL table statistics are used after they are updated when NORECOMPUTE is turned on? Background:
I have a large table whose values are significantly rewritten with a switch ~once a week, but the table's primary key values remain relatively constant. After the switch, statistics are guaranteed to be out of date on the table.
A consequence then is that user queries against this table after the switch are pretty much guaranteed to need to update statistics. This has lead them to timing out. (User queries have a 30 second time limit; these same queries running with up-to-date statistics complete in <1 second.)
To address this, I'm looking to manage statistics manually by (1) calling UPDATE STATISTICS after the  switch, and (2) turning on STATISTICS_NORECOMPUTE. User queries the rest of the week will then potentially use outdated statistics, but that should be fine.
Question:
Let's say I turn STATISTICS_NORECOMPUTE on for this table, have a constant stream of new queries against this table, and I call UPDATE STATISTICS. When will the new statistics be used after the UPDATE STATISTICS call is made? Will they be used by all queries that execute after the statistics transaction is made?
Put another way, I'm considering a SQL query in two phases: when it is submitted, and when it is executed. The query optimizer is responsible for telling a query which statistics to use. Does the optimizer create the query plan when the query is submitted, or when the query finally executes?

According to this:
https://docs.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-2017#recompiling-execution-plans
ALTER TABLE and UPDATE STATISTICS commands trigger execution plan recompilation. So seems like the new statistics would be used by all queries executing after either of those procedures.


Lock Pages in Memory for SQL Server What is a recommendataion regarding turning on Lock Pages in Memory for 64 bit SQL Servers.

Is there any implication on turning on lock pages in memory setting?

Microsoft recoomends as an option if you are experiencing buffer pool paging (http://support.microsoft.com/kb/918483).


SQL To Fetch Partition Boundaries and Filegroups For a given partition scheme name, I need to retrieve the set of

Boundary Value
FileGroup Name

This must work without the existence of any tables participating in the scheme.

i.e. joins via sys.indexes will not work.

I think the answer lies around sys.destination_data_spaces, but have not yet figured the joins to sys.partition_range_values.

This relationship isn't altogether intuitive but I think this query will get you what you need:

SELECT
  PScheme       = ps.name,
  PFunction     = pf.name,
  Boundary      = dds.destination_id,
  BoundaryValue = prv.value,
  [Filegroup]   = fg.name
FROM sys.partition_schemes AS ps
INNER JOIN sys.partition_functions AS pf
        ON ps.function_id = pf.function_id
INNER JOIN sys.destination_data_spaces AS dds
        ON ps.data_space_id = dds.partition_scheme_id
INNER JOIN sys.filegroups AS fg
        ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv
        ON dds.destination_id = pf.boundary_value_on_right + prv.boundary_id
       AND prv.function_id = pf.function_id;

And because LEFT/RIGHT is always a tedious mental exercise for me, it might be that you need to flip the boundary_value_on_right part on the second-last line:

ON dds.destination_id = prv.boundary_id + ABS(1-pf.boundary_value_on_right)

You don't need to have created any tables.


How to grant a user the rights to access to all new (future) databases? On my development machine, I often create new web instances that all run as NT AUTHORITY\NETWORK SERVICE. I want to grant this account rights to access all new databases that I create in the future in my SQL Server 2008 R2 instance. I want to do this because it is too much of a hassle to grant rights to new databases every time I create a new web site.

Well - there is a bigger question regarding why you are using NT Authority\Network, but I asked a similar question ages ago
Permissions on New Databases
So in theory, it is fairly simple.


Count deep nested tables From this SQL schemas I want to count the number of times a user is in a contest.

SELECT * FROM users u
LEFT JOIN trials_has_users tu ON (tu.users_id = '1')
LEFT JOIN trials AS t ON (t.id = tu.trials_id)
WHERE u.id = '1';

Previously, I have the expected number of lines, but I want to make a count
SELECT contest_total FROM users u
LEFT JOIN trials_has_users tu ON (tu.users_id = '1')
LEFT JOIN (
    SELECT
        id,
        COUNT(*) AS contest_total
    FROM
        trials
    WHERE deleted_at IS NULL
    GROUP BY
        id
) AS t ON (t.id = tu.trials_id)
WHERE u.id = '1';

Previously, have 6 rows want 1 (current user id) need LIMIT 1 ?
SELECT contest_total FROM users u
LEFT JOIN trials_has_users tu ON (tu.users_id = '1')
LEFT JOIN (
    SELECT
        id,
        COUNT(*) AS contest_total
    FROM
        trials
    WHERE deleted_at IS NULL
    GROUP BY
        id
) AS t ON (t.id = tu.trials_id)
WHERE u.id = '1'
LIMIT 1;

I would like to receive the number of contests in which the user has participated. My query is right ?

As ypercube pointed out, you want to use COUNT(DISTINCT t.contests_id) here to get the unique count of contests per users. All credit goes to ypercube on this one, but for a readable version of the example query on how to do this:
SELECT u.*, COUNT(DISTINCT t.contests_id) AS contest_total
FROM users AS u
LEFT JOIN trials_has_users AS tu
    ON tu.users_id = u.id
LEFT JOIN trials AS t
    ON t.id = tu.trials_id
WHERE u.id = 1 AND tu.users_id = 1
GROUP BY u.id;


Disabling Triggers when not needed Situation:
Large amount of data (millions to tens of millions of rows) in several tables that is not changed often, but when it does, there are usually lots of changes.

Tables each have a "summary" table associated with them that needs to be scheduled for update when any changes are made to main table.

Current Proposed Solution:
Update/Insert/Delete Trigger created on each main table that inserts the name of a table into a table called UpdatedTables.

A job is scheduled to run each night that checks UpdatedTables, updates the summary tables for the tables listed there, and then deletes the entries from UpdatedTables.

Questions:

Does my solution make sense? Is there anything wrong with it?
Could this cause issues if a large import/update was still going on when the job was scheduled?
Would it be worth disabling the trigger after the first insert and then re-enabling it after the job runs at night?  I know the per transaction cost is minimal, but I don't see the point in it executing 5 million times when it really needs to only run once.

Here are three four options:

Option 1 - consider how long the jobs that populate the summary tables take to run. If that time is not prohibitively long, then consider running the jobs nightly regardless of whether there's been a change or not. That actual frequency of changes, especially in the biggest tables, would influence your decision here.

Option 2 - @SQLWorldWide suggested an alternative from a Pinal Dave article; using the sys.dm_db_index_usage_stats view to detect the last time the table was changed. (Clustered indexes have to change whenever any column changes; for tables without a clustered index, a "dummy" index (index_id = 0) will be shown, again showing the time when anything was inserted, updated, or deleted in the table). However, this has one flaw - these views are reset each time the server is restarted. So, if the server was restarted five minutes before your job ran, you might not update any of the summary tables.

(See "UPDATE" below, for an even better alternative):

Again, depending on how long it takes to refresh all the summary tables, and how often your server is restarted, there is an alternative:

SELECT MAX(last_user_update) as last_change
  FROM (SELECT last_user_update
          FROM sys.dm_db_index_usage_stats
         WHERE database_id = DB_ID('<dbname>')
           AND OBJECT_ID=OBJECT_ID('<tablename>')
        UNION ALL
        SELECT sqlserver_start_time FROM sys.dm_os_sys_info
       ) q
;

(Replace <dbname> and <tablename> with the appropriate values for your database and table, of course.)

This query will return the time of the most recent change to <tablename> of the last time your server was restarted, whichever came last.

Have your job store the time and date when it started running as its last step, and retrieve that last run time as its first step. If the last run time is less than last_changed, then update the summary table.

As noted, this will mean that all the summary tables will be updated after each restart; if that is a major issue, then this option wil lprobably not work for you.

Option 3 - This is your idea, tweaked slightly.

Instead of writing the table name down each time the trigger runs, I'd have UpdatedTables have two columns (more if needed, for instance for the name of the procedure that updates the summary table, or whatever you'd need to run that process when appropriate): tablename, and is_changed, a bit column set to 0 by default. Your trigger would update the bit column to one where the tablename matched and the bit column was zero; after the summary tables are updated, just reset the bit column to 0.

Note that you'd want to lock the table while updating the summary table - otherwise, a change might happen during that update, and no record would be made of it. That's a problem that would hold with your solution as originally written if you prefer that - and is the only thing that jumps out at me as a possible problem with it.

If locking the table isn't an option (and it probably isn't, if a large import/export could be going on when the job that checks for updates runs), you could reset is_changed before updating the summary table. However, in that case, you'd want to be sure to set is_changed back to one if the summary table update failed for some reason, so it would get run again next time.

With the tweaked solution, hopefully the trigger would run very quickly - and, you don't run the risk of have millions of records in UpdatedTables that all say 'Name_of_Updated_Table' - that does consume disk space, after all.

The last few paragraphs answer your first two questions. I would not turn the trigger off while a large job is updating a table (if you are using a trigger), unless the job manually updates your table noting it was changed when it finishes. If the summary table update job ran while your large import update was running, but after your first change had run the trigger, the summary job would clear the update flag for the table - and the rest of the import/update wouldn't get flagged as a change.

UPDATE:

Option 4 - Use a combination of options 2 and 3.

Credit to @SQLWorldwide again, as his comment made me think of this option.

For this option, UpdatedTables should be as described in option 3.

As in option two, record the time when you start the summary table update process each night after it's successfully completed. Record it in table LastSummaryRun, column RunDate. TRUNCATE the table and INSERT the value, so the table only has one row.

At the end of that process, clear the is_changed flags in UpdatedTables and run this:

UPDATE ut
   SET is_changed = 1
  FROM UpdatedTables ut
         CROSS JOIN LastSummaryRun ls
 WHERE EXISTS (SELECT 1
                 FROM sys.dm_db_index_usage_stats
                WHERE database_id = DB_ID('<dbname>')
                  AND OBJECT_ID=OBJECT_ID(ut.tablename)
                  AND last_user_update > ls.RunTime
              )
;

(Again, replace <dbname> and <tablename> with the appropriate values for your database and table, of course.)

This will catch any changes that happened while your job was running, and will make sure we've primed all the values, in case there's a restart.

Execute this same statement in a job that runs every 5 minutes or so.

UpdatedTables should be correct unless:

the server restarted, AND
the only change that day happened in the five minutes before the restart.

If that seems like an acceptable risk, then this is probably the best of all the options. Note that the job can be run more often than every 5 minutes


How to query mysql tables with regular expressions? I'm using a correct regex, already tested with other tools. But in mysql seems not to work properly. Say this data:

And I want to select all the records which start with 2 non-digits followed by a dot, followed by one digit followed by a sequence of .00 (ex. DE.1.00.00.00.00)

\D{2}\.\d?\.0{2}.0{2}.0{2}.0{2}

The above used alone normally works. But on mysql, a simple query like the following won't work. It will return a table with all the columns and without any error, but also without any value. Why?

SELECT *
FROM V35_CATEGORIE cat
WHERE cat.CategoriaCodice REGEXP '\D{2}\.\d?\.0{2}.0{2}.0{2}.0{2}'

I answer by myself. In short, sql syntax above is correct, so how to query mysql tables with regular expressions? Exactly like I did.

The reason why it didn't work, is regexp syntax being too 'complex' for mysql.
I found out that mysql doesn't support the full syntax possibilities of standard regular expressions. I must use simpler symbols/constructs. So for example, instead of \D{2} I can use [A-Z]{2}, and instead of \d, knowing I'll want 1 or 2, I can use [1,2]?. A syntax like the following will perfectly work in mysql to match a string like 'IT.1.12.00.00.00':

[A-Z]{2}\.[1,2]?\.[1-9][0-9](\.00){3}

With full regexp syntax I could have use lookahead assertions and other constructs, but anyway, for now for my needs I can use plain 'old' grammar.


Oracle Jobs: Can I create a job that will not overlap with itself? How can I ensure that a job will never have more than one instance running?

For example, if I have a job that is scheduled to run once a minute, but it actually runs for three minutes, I don't want a second and third instance of the job to start and run concurrently.

Using dbms_job, you're never going to have two instances of a job running at the same time.  If I do something like this with an interval that is 1 minute in the future and a procedure that takes 3 minutes to run

declare
  l_jobno pls_integer;
begin
  dbms_job.submit( l_jobno,
                   'begin proc_that_takes_3_minutes(); end;',
                   sysdate,
                   'sysdate + interval ''1'' minute' );
end;
/

the interval expression is only evaluated after the prior run finishes.  There won't be 3 instances of the job running simultaneously.  The job will just run once every 4 minutes.

This is why you frequently hear people about jobs "drifting" over time-- a job is scheduled to run at 4 AM with an interval of "sysdate + 1" so every day it runs at a slightly later time.  If the job takes 1 minute to run, after 2 months, it's now running just after 5 AM.  And that's why you'd generally want your interval to be something like "trunc(sysdate+1) + interval '4' hour" if you want to run every day at 4 AM.

If you want to provide additional protection in case, say, someone tries to run the procedure in a different session at the same time that the job is running, you can put a dbms_lock.request call in the procedure that tries to acquire a user-defined lock before doing the actual work.

create or replace procedure what_the_job_does
as
  l_status integer;
begin
  l_status := dbms_lock.request( 'MY_LOCK_NAME' );
  if( l_status in (1,4) )
  then
    <<do actual work>>
  end if;
end;


Read-Scale Availability Group between a Transactional subscriber and another machine I am trying to configure Read-Scale replica between a transactional subscriber and a 3rd machine. I could create the group. But, when i attempted to add the DB (which is a transactional - subscriber) to be part of my read scale replica group, I am getting the following message and not able to complete my read scale replica. My replication is already configured and working fine. Configured the AG as well. All i am doing is, trying to add the DB to the AG.
Here is the message:
Special considerations exist when adding a replication Subscriber database to an availability group. For more information, see "Replication and Availability Groups" in SQL Server Books Online.
But the BOL is not saying anything about AG on a subscriber. Any help on this would be highly appreciated.

what sql server version are you using?
The distribution database cannot be placed in an availability group with SQL Server 2012 and SQL Server 2014. Placing the distribution database into an availability group is supported with SQL 2016 and greater, except for distribution databases used in merge, bidirectional, or peer-to-peer replication topologies


Error "Msg 10054, level 20, State 0, Line 0" with MSSQL under Docker I've set up several docker containers in my Ubuntu 16.04 LTS machine. All of them have in common the same container image.

It works pretty well, but some users have reported me a common issue when attempting a long-lasting query and about a minute after it execution, they get an error message:

  Mens. 10054, Nivel 20, Estado 0, Línea 0
  Error en el nivel del transporte al recibir los resultados del servidor.
  (provider: TCP Provider, error: 0 - Se ha forzado la interrupción de una conexión existente por el host remoto.)

The error message is translated into english as Error 10054, level 20, State 0, Line 0.

Is there something I can modify under their configuration?

PS: I've noticed that in server's properties Connection > Remote servers connections > I've "Permitir conexiones remotas con este servidor checked", having 0 seconds (no wait time) of waittime.

EDIT
I attach one container's configuration snapshots:

Omitted the permission config, as I don't think it's relevant.

I've solved the issue by making use of the oficial MSSQL image from Microsoft, as I was using a custom Ubuntu 16.04 LTS + MSSQL packages. All the data was stored within a volume, so the transition from one container to the new was easy.

EDIT:
The previous didn't actually solve the problem. The real issue was that I was querying too much data. Querying an extensive dataset implied a huge RAM consumption, as much as it was permitted. I wasn't limiting it at all, so when reached the maximum amount of RAM available, the OS (dispatcher) would kill the process. The solution has resulted to configure a memory limit.


Field Terminator Problem in Format Files I have a data file in txt format and I used to load it with Bulk Insert in the following command:

Bulk insert [table1]
FROM 'data.txt'
WITH
  (
     FIELDTERMINATOR ='|',
     ROWTERMINATOR ='0x0a',
  )

The code works well. However, while I was trying to use a format file with the bulk insert to load the file, I encountered some problems specifying the field terminator for the last row.

Here is the specification for the last row in the format file:

21  SQLCHAR  0  20  "0x0a"  21  misc_costs       SQL_Latin1_General_CP1_CI_AS

With "0x0a" as the terminator, I get the following error:

  Bulk load data conversion error (truncation) for row 2, column 1 (var1).

With "\n",  data can be loaded, but "|" is also loaded in the last column. For example, I get "0.5|" instead of "0.5".

"|\n","0x0a","|0x0a",'\r\n' returns the same error as "|"

  Bulk load data conversion error (truncation) for row 2, column 1 (var1).

I'm using sql-server 2016, windows 10 64bit.

Can anyone help with this? Thanks in advance for your help!

Jason

After a whole day struggling with Line Feed/Hex code, I finally found that my problem was caused by a missing columns in the format files. The dataset is new to me and I thought it had 21 rows while it actually has 22 rows. That's why sql server kept loading the last "|" from the data file.

Here is a reference for others maybe interested in loading data file from UNIX system with row terminators different from Windows, there are three methods:

use bulk insert and set the row terminator as "0x0a".
use a format file and set the the field terminator of the last field as "\n".
Use a dynamic sql with function Char(10) to insert the line feed(LF) into the command string and then exec() the command string.

Before trying these methods, use a notepad to show all control characters to confirm the control characters used by the text files.

If none of these methods work for you, you may have some problems other than the line feed/row terminator.


Postgres: why did this trigram index slow down regexp queries? I have a TEXT keyvalues column in Postgres:

select * from test5 limit 5;

 id |                      keyvalues
----+------------------------------------------------------
  1 | ^ first 1 | second 3
  2 | ^ first 1 | second 2 ^ first 2 | second 3
  3 | ^ first 1 | second 2 | second 3
  4 | ^ first 2 | second 3 ^ first 1 | second 2 | second 2
  5 | ^ first 2 | second 3 ^ first 1 | second 3

My queries must exclude the ^ character from the middle of the match, so I'm using regular expressions:

explain analyze select count(*) from test5 where keyvalues ~* '\^ first 1[^\^]+second 0';

                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=78383.31..78383.32 rows=1 width=8) (actual time=7332.030..7332.030 rows=1 loops=1)
   ->  Gather  (cost=78383.10..78383.30 rows=2 width=8) (actual time=7332.021..7337.138 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=77383.10..77383.10 rows=1 width=8) (actual time=7328.155..7328.156 rows=1 loops=3)
               ->  Parallel Seq Scan on test5  (cost=0.00..77382.50 rows=238 width=0) (actual time=7328.146..7328.146 rows=0 loops=3)
                     Filter: (keyvalues ~* '\^ first 1[^\^]+second 0'::text)
                     Rows Removed by Filter: 1666668
 Planning Time: 0.068 ms
 Execution Time: 7337.184 ms

The query works (zero rows match), but is way too slow at > 7 seconds.

I thought indexing with trigrams would help, but no luck:

create extension if not exists pg_trgm;
create index on test5 using gin (keyvalues gin_trgm_ops);

explain analyze select count(*) from test5 where keyvalues ~* '\^ first 1[^\^]+second 0';
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1484.02..1484.03 rows=1 width=8) (actual time=23734.646..23734.646 rows=1 loops=1)
   ->  Bitmap Heap Scan on test5  (cost=1480.00..1484.01 rows=1 width=0) (actual time=23734.641..23734.641 rows=0 loops=1)
         Recheck Cond: (keyvalues ~* '\^ first 1[^\^]+second 0'::text)
         Rows Removed by Index Recheck: 5000005
         Heap Blocks: exact=47620
         ->  Bitmap Index Scan on test5_keyvalues_idx  (cost=0.00..1480.00 rows=1 width=0) (actual time=1756.158..1756.158 rows=5000005 loops=1)
               Index Cond: (keyvalues ~* '\^ first 1[^\^]+second 0'::text)
 Planning Time: 0.412 ms
 Execution Time: 23734.722 ms

The query with the trigram index is 3x slower! It still returns the correct result (zero rows). I expected the trigram index to figure out immediately there's no second 0 string anywhere, and be super fast.

(Motivation: I want to avoid normalizing the keyvalues into another table, so I'm looking to encode the matching logic in a single TEXT field using text indexing and regexps instead. The logic works, but is too slow, as is JSONB.)

I expected the trigram index to figure out immediately there's no second 0 string anywhere

'second' and '0' are separate words, so it cannot detect their joint absence as such.  It seems like it could detect the absence of '  0', but this comment from "contrib/pg_trgm/trgm_regexp.c" seems pertinent:

 * Note: Using again the example "foo bar", we will not consider the
 * trigram "  b", though this trigram would be found by the trigram
 * extraction code.  Since we will find " ba", it doesn't seem worth
 * trying to hack the algorithm to generate the additional trigram.

Since 0 is the last character in the pattern string, there will be no trigram of the form " 0a", either, so it just misses that opportunity.

Even if it were not for this limitation, your approach seems extremely fragile.


pg_dump custom archive and create backup date I have archive file with custom format created by pg_dump.

Does it contain the backup creation date?

I can't create backups by myself. I receive the archived files over specific network channel. Backup files have a reception date timestamp, but not the date when it has been created on server.

You can use pg_restore -l <custom_dump_file> and the output should start with something like the following:

;
; Archive created at 2019-02-13 22:59:59 UTC
;     dbname: <database_name>
;     TOC Entries: 2615
;     Compression: -1
;     Dump Version: 1.13-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 10.6 (Ubuntu 10.6-1.pgdg16.04+1)
;     Dumped by pg_dump version: 11.1 (Ubuntu 11.1-3.pgdg16.04+1)
;

As you see, the "Archived created at" line is probably what you're looking for.


SQL Query Optimization My SQL Server version is

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53

and the query below just runs without producing an error or updating.  The table [dbo].[Data] will produce roughly 15 entries that would need to be updated (has less than 1,000 rows in the table itself while [dbo].[Domains] has just under 9000 rows to check against.

Is there a better/quicker way for me to write this query so the appropriate rows will be updated?  Below is sample DDL that has the same data structure as the actual production table I would be using this query with.

 CREATE TABLE [dbo].[Domains](
 [DOMAIN] [varchar](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'gmail.com')
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'hotmail.com')
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'aol.com')
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'yahoo.com')

CREATE TABLE [dbo].[Data](
  [email] [nvarchar](75) NULL
  ,[accept] [nvarchar](20) NULL
 ) ON [PRIMARY]
 GO
 SET ANSI_PADDING OFF
 GO
 INSERT [dbo].[Data] ([email]) VALUES (N'1234@gmail.com')
 INSERT [dbo].[Data] ([email]) VALUES (N'1234@hiho.com')
 INSERT [dbo].[Data] ([email]) VALUES (N'1234@aol.com')

 UPDATE dt
 SET [accept] =
        case
            when EXISTS (SELECT TOP 1 [DOMAIN]
                         FROM [dbo].[Domains] dmn
                         WHERE [dmn].[DOMAIN] = SUBSTRING([dt].[email],     CHARINDEX('@', [dt].[email]) + 1, LEN([dt].[email])))
            THEN 'NO'
            ELSE 'Yes'
        END
FROM [dbo].[Data] dt

This query avoids the Compute Scalar of your query and avoids any changes in your table structure.  Should perform well.

SELECT dt.email
    , accept = CASE WHEN dm.[DOMAIN] IS NOT NULL THEN 'NO' ELSE 'Yes' END
FROM dbo.[Data] dt
    LEFT OUTER JOIN dbo.[DOMAINS] dm ON dt.email LIKE ('%@' + dm.DOMAIN)


Export a very big amount of files from a table I'm using Oracle Database 11g Release 11.1.0.6.0 and have a table recording a very big amount of files (GIF) with IDs (ID).

  Name                                      Type
  ----------------------------------------- ----------------------------
  ID                                        VARCHAR2(50)
  GIF                                       BLOB

And currently I'm using this piece of code to export the pictures:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

This code works well, BUT the problem is, there're about 100 million rows in the table. On my computer the export speed is about only 7 pictures/second.

So, is there any method that can accelerate the exporting? Thank you!

You can use the ORA_HASH function to split the table data in distinct groups. Look at the modified SELECT-FROM-MY_TABLE-clause. Run this script in three different sqlplus sessions and measure the number of files per seconds that are extracted.

script 0:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE where ora_hash(rowid,2,123)=0) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

script 1:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE where ora_hash(rowid,2,123)=1) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

script 2:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE where ora_hash(rowid,2,123)=2) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

If this works you can try to generate and start  more scripts.


How to create an alphanumeric sequence like AAAA0000 and so on I want to create an alphanumeric sequence like this:

AAAA0000
AAAA0001
AAAA0002
AAAA0003
.
.
.
AAAA9999
AAAB0000
AAAB0001
.
.
.
ZZZZ9999

I have created this store procedure to do that but its too slow:

CREATE OR REPLACE FUNCTION public.fn_batch_seq()
  RETURNS text
  LANGUAGE plpgsql
AS
$body$
DECLARE
  v_sequence TEXT := '';
  v_next_sequence TEXT := '';
  v_existing_id BIGINT := 0;
BEGIN

  /*
  *  VARCHAR BATCH SEQUENCE FOR SIMCARDS
  */
  SELECT "sequence" FROM batch_sequence WHERE id = 1 INTO v_sequence;
  IF v_sequence = '' THEN
    RAISE NOTICE 'Error - No existe ningun registro en batch_sequence almacenado';
    RETURN -500;
  END IF;
  SELECT perl_increment(v_sequence) INTO v_next_sequence;

  IF v_next_sequence = '' THEN
    RAISE NOTICE 'Error - La siguiente secuencia generada devolvio null o vacio';
    RETURN -500;
  END IF;

  UPDATE batch_sequence SET "sequence" = v_next_sequence WHERE id = 1;
  RETURN v_next_sequence;

  EXCEPTION WHEN OTHERS THEN
  /*
  * Other errors
  */
  RAISE NOTICE 'Error General - Posibles causas: No existe la tabla batch_sequence o no existe ningun registro en la misma';
  RETURN -500;

END;
$body$
  VOLATILE
  COST 100;

This procedure uses a table to store the sequence:

CREATE TABLE batch_sequence
(
   id        serial   NOT NULL,
   sequence  text     DEFAULT 'AAAA0000'::text NOT NULL
);

-- Column id is associated with sequence public.batch_sequence_id_seq

ALTER TABLE batch_sequence
   ADD CONSTRAINT batch_sequence_pk
   PRIMARY KEY (id);

And for increment the sequence I use a perl procedure:

CREATE OR REPLACE FUNCTION public.perl_increment(text)
  RETURNS text
  LANGUAGE plperl
AS
$body$
my ($x) = @_;
    if (not defined $x) {
        return undef;
    }
    ++$x;
$body$
  VOLATILE
  COST 100;

It works very slow with large amount of data, because it must be executed before inserting every single row. Is there another way to do that with or without Perl?

We can do this with a couple common table expressions (CTEs) that generate some simple series ...

with

letters as
(select chr(i) as letter from generate_series(65,90) i),

digits as
(select lpad(i::text,4,'0') as digit from generate_series(0,9999) i)

select l1.letter || l2.letter || l3.letter || l4.letter || d.digit

from       letters l1
cross join letters l2
cross join letters l3
cross join letters l4
cross join digits d

our first CTE is named letters
letters generates a series of numbers from 65 to 90, which happen to be the ascii codes for letters 'A' to 'Z' so ...
we can generate our characters via the chr(i) function call
at this point letters represents the set of characters 'A' to 'Z'
our next CTE is digits
digits generates a series of numbers from 0 to 9999, and since we're going to be dealing with characters ...
lpad(i::text,4,'0') allows us to convert this series of numbers to left/zero padded character strings
at this point digits represents a set of characters '0000', '0001', ... '9999'
from here we just need to perform a series of cross joins and append our letters/digits together

Here's a dbfiddle for the above.

For the dbfiddle you can remove the limit/offset clause to generate the complete set, alternatively adjust the limit/offset clause to see a range of outputs.

For example, with limit 20 offset 59990 we get:

?column?
--------
AAAF9990
AAAF9991
AAAF9992
AAAF9993
AAAF9994
AAAF9995
AAAF9996
AAAF9997
AAAF9998
AAAF9999
AAAG0000
AAAG0001
AAAG0002
AAAG0003
AAAG0004
AAAG0005
AAAG0006
AAAG0007
AAAG0008
AAAG0009


Calculating percentage from two tables I have two tables:

First one contains unique mentees under one mentor, so mentors can have more than one mentee in that table.
Second one contains interactions of mentors with mentees on different dates, so mentos and mentees may appear multiple times.

I'm trying to create a join between those two tables where the result would be:

'mentor_id'|'# of people'|'# of distinct interactions'

This way I would know whom did the mentor advice during a given period, against how many they are supposed to as %.

What I've done..

SELECT INTER.mentor_id, COUNT(DISTINCT INTER.mentee_id), COUNT(f.mentee_id)
FROM INTER WITH (NOLOCK)
INNER JOIN
    (SELECT mt.mentee_id, mt.mentor_id
     FROM mentee_table mt WITH (NOLOCK)
    ) as f
ON f.mentor_id = INTER.mentor_id
WHERE (//period)
GROUP BY INTER.mentor_id

The problem with this is that when viewing the result without any groupings or aggregates, I receive duplicates from the subquery, since there are more records in INTER.

Schema's

mentee_table

|mentee_id|mentor_id
|1        |3
|2        |3
|3        |5

INTER

|mentee_id|mentor_id
|1        |3
|1        |3
|1        |3
|2        |3
|3        |5
|3        |5

In the end I will just use the counts to calculate percentage, such as

COUNT(DISTINCT INTER.mentee_id)*100/COUNT(f.mentee_id) which means the INTER table, when distinct, can only have <= # of people

Thanks

Edit

I ended up doing this query to also get mentors who never had any interactions. I had to use DISTINCT on both counts..

SELECT mt.mentor_id, COUNT(DISTINCT mt.mentee_id), COUNT(DISTINCT INTER.mentee_id)
FROM mentee_table mt WITH (NOLOCK)
   LEFT JOIN INTER WITH (NOLOCK)
   ON INTER.mentor_id = mt.mentor_id AND INTER.mentee_id = mt.mentee_id
GROUP BY mt.mentor_id
ORDER_BY mt.mentor_id

I see what your problem is you are duplicating the interactions because you are basically just trying to join those two tables together in your query. What you want to do is a correlated subquery to get the answer of how many interactions each mentor has had.

Something like this.

select a.mentor_id, count(distinct(a.mentee_id)) as UniqueMenteeCt,b.InterCt
from mentee_table a inner join
(select a.mentor_id, count(a.mentee_id) as InterCt
 from INTER a
 group by a.mentor_id
)b on a.mentor_id = b.mentor_id
group by a.mentor_id,InterCt


SQL Server 2005 on Windows Server 2012 R2 Install Question Can I Install SQL Server 2005 on Windows Server 2012 R2 ?

Thanks !

That is an unsupported configuration.  Please see this KB article explaining this.

  SQL Server 2005 (the release version and service packs) and earlier versions of SQL Server are not supported on Windows Server 2012 R2, Windows Server 2012, Windows 8.1, or Windows 8. You will receive a warning in the Action Center if Windows 8.1 or Windows 8 detects an instance of SQL Server 2005.


Calculating the number of times a motive was cited over a given period I'm using PostgreSQL 9.4. I have the following surveys table. Each row includes a motive that was cited by the respondent, represented by the motive_value column (think of it as motive_id).

---------------------------------------
id | var      | motive_value | period |
---------------------------------------
1  | 9        | 1            | 1      |
2  | 7        | 2            | 2      |
3  | 2        | 5            | 1      |
4  | 3        | 3            | 1      |
5  | 6        | 1            | 2      |
---------------------------------------

And motives table. There are around 20 motives, but for the sake of brevity I'm including five.

-----------------------------
id | value     | name       |
-----------------------------
1  | 1        | Blocked     |
2  | 2        | Delivery    |
3  | 3        | Forgotten   |
4  | 4        | Info        |
5  | 5        | Other       |
-----------------------------

So, I need to get, grouped by period descending, the top 5 most cited motives. ( note that, most cited motives for the entire surveys table, and not for a given period).

Here's a table that represents what I'm after.

------------------------------------------------------------------
| period | total | blocked | delivery | forgotten | info | other |
------------------------------------------------------------------
| 2      | 2     | 1       | 1        | 0         | 0    | 0     |
| 1      | 3     | 1       | 0        | 1         | 0    | 1     |
------------------------------------------------------------------

The total is the number respondents, or survey rows, in a given period. It's not necessary, but it's nice to have. I'd prefer not to have it if it adds lots of complexity. The other columns represent the motives, and the number of times each motive was cited. For example, in period 1 there were 3 surveys, and one of them cited the blocked motive.

I started with this query. It gets me the top 5 most cited motives.

SELECT
    motive_value,
    motives."name" AS motive,
    COUNT(motive_value) AS sample_size
FROM surveys
INNER JOIN motives ON surveys.motive_value = motives."value"
WHERE var BETWEEN 0 AND 10
GROUP BY motive_value, motive
ORDER BY sample_size DESC
LIMIT 5

I also wrote this query for one motive. It brings me, grouped by period, the number of times a motive was cited.

SELECT
    period,
    COUNT(motive_value) AS sample_size,
    motives."name" AS motive
FROM surveys
INNER JOIN motives ON surveys.motive_value = motives."value"
WHERE (var BETWEEN 0 AND 10) AND motives_value = 1
GROUP BY period, motive
ORDER BY period DESC

The trouble is I need one query for each one of the top 5 motives in the first query. I also need the motives as columns, as shown in my example table. So I'm not sure how to combine those queries, or even create the table where motives are columns.

I think I might have figured this out using Postgresql's array_agg, but with a big downside, so I'd still be interested in seeing other answers.

SELECT
    period,
    ("array_agg"(ROW(motive_value, motive_name, sample_size)))[1:5] AS sample_list
FROM
    (
        SELECT
            period,
            motive_value,
            COUNT(motive_value) AS sample_size,
            motives."name" AS motive_name
        FROM surveys
        INNER JOIN motives ON surveys."motive_value" = motives."value"
        WHERE var BETWEEN 0 AND 10
        GROUP BY period, motive_value, motive_name
        ORDER BY sample_size DESC
    ) AS row
GROUP BY period
ORDER BY period DESC
LIMIT 6

The only down side is that, instead of columns, I get an array like this:

-----------------------------------------------------------------------------
| period | sample_list                                                      |
-----------------------------------------------------------------------------
| 2      | {"(1,\"Blocked\",1)", "(2,\"Delivery\",1)", ... }                |
| 1      | {"(1,\"Blocked\",1)", "(3,\"Forgotten\",1)", "(5,\"Other\",1)" } |
-----------------------------------------------------------------------------


Why clustered index scan? I just started to learn to optimize my queries and to analyze their query plans. I thought this query would generate nonclustered index seek + key lookup.

SELECT ct.*
FROM Person.ContactType AS ct
WHERE ct.Name LIKE 'Own%';

Instead it uses clustered index scan. I don't know why.

I'm working with AdventureWorks2012 database on SQL Server 2012 Express.
There is clustered index on ContactTypeId column and nonclustered index on Name column.
There is third column (ModifiedDate) that is not part of any index. This table contains only 20 rows.

I suspect that query optimizer decided to do clustered index scan because table has only 20 rows and maybe it is quicker to scan index then to do key lookup.

This table is very small!

It has 20 rows of which 2 match the search condition. The table definition contains three columns and two indexes (which both support uniqueness constraints).

CREATE TABLE Person.ContactType(
    ContactTypeID int IDENTITY(1,1) NOT NULL,
    Name dbo.Name NOT NULL,
    ModifiedDate datetime NOT NULL,
    CONSTRAINT PK_ContactType_ContactTypeID PRIMARY KEY CLUSTERED(ContactTypeID),
    CONSTRAINT AK_ContactType_Name UNIQUE NONCLUSTERED(Name)
)

Running

SELECT index_type_desc,
       index_depth,
       page_count,
       avg_page_space_used_in_percent,
       avg_record_size_in_bytes
FROM   sys.dm_db_index_physical_stats(db_id(),
                                      object_id('Person.ContactType'),
                                      NULL,
                                      NULL,
                                      'DETAILED')

Shows both indexes only consist of a single leaf page with no upper level pages.

+--------------------+-------------+------------+--------------------------------+--------------------------+
|  index_type_desc   | index_depth | page_count | avg_page_space_used_in_percent | avg_record_size_in_bytes |
+--------------------+-------------+------------+--------------------------------+--------------------------+
| CLUSTERED INDEX    |           1 |          1 | 15.9130219915987               | 62.5                     |
| NONCLUSTERED INDEX |           1 |          1 | 13.1949592290586               | 51.5                     |
+--------------------+-------------+------------+--------------------------------+--------------------------+

Rows on each index page aren't necessarily in index key order but each page has a slot array with the offset of each row on the page. This is maintained in index order.

The nonclustered index covers two out of the three columns (Name as a key column and ContactTypeID as a row locator back to the base table) but is missing ModifiedDate.

You can use index hints to force the NCI seek as below

SELECT ct.*
FROM   Person.ContactType AS ct WITH (INDEX = AK_ContactType_Name)
WHERE  ct.Name LIKE 'Own%';

But you can see that under SQL Server's cost model this plan is given a higher estimated cost than the competing CI scan (roughly double).

The single page clustered index scan would just need to read all the 20 rows on the page, evaluate the predicate against them and return them.

The single page nonclustered index range seek might potentially be able to perform a binary search on the slot array to reduce the number of rows evaluated however the index does not cover the query so it would also need a potential IO to retrieve the CI page and then it would still need to locate the row with the missing column values on there (for each row returned by the NCI seek).

On my machine running 1 million iterations of the non clustered index plan took 15.245 seconds compared to 11.113 seconds for the clustered index plan. Whilst this is far from double the plan without the hint was measurably faster.

Even if the table was orders of magnitude larger however you may well still not get your expected plan with lookups.

SQL Server's costing model prefers sequential scans to random IO lookups and the "tipping point" between it choosing a scan of a covering index or a seek and lookups of a non covering one is often surprisingly low as discussed in Kimberley Tripp's blog post here.

It is certainly not out of the question that it would choose such a plan for a 10% selective predicate but the clustered index would likely need to be quite a lot wider than the NCI for it to do so.


How to convert from one type of relationship table to another type I currently have a relationship table where it's:

documentID, dataType, dataID

Where documentID is the link to the document file. The dataType is if the data is a specific type of user, for example a student, teacher, vendor, etc. (as these all have different tables). The dataID is the ID of the user.

So for example:

documentID, dataType, dataID
12343, 2, 4323

Would be the file 12343 for the type teacher (type 2 equals teacher) and it would be for teacher with ID 4323.

What I want to do is convert this table to the format:

documentID, dataType, studentID, teacherID, vendorID, etcID...
12343, 2, -1, 4323, -1, -1, ...

The reason I want to do it this way is because the files are now going to be cross-referenced a lot more and in the majority of cases a specific document will be linked to several different users and user types.

I could keep the original table but I would prefer to keep the inserts, updates, etc. to a single database to the document relationship table. That and a few other reasons.

Anyway is there a way to convert from one table to the other? The only possible solution I came up with is to transfer all the documentID and dataTypes in one step. Then transfer the dataID using a combination of matching document ID with dataType, and running one update per dataType. At least in theory... I haven't yet been able to make that work. Is this the only way and if so any suggestions as to the actual SQL would be appreciated. So far I have:

INSERT INTO documentRelNew (documentID) SELECT documentID FROM documentRelOld;

which works great but the following SQL line fails:

UPDATE
    documentRelNew
SET
    documentRelNew.studentID=documentRelOld.dataID
FROM
    documentRelOld
WHERE
    documentRelOld.dataType=2;

It fails at the FROM portion. Yet everything I've read seems to indicate this should work. In theory I need to run this for all the different data types however I can't even get it to work for the first dataType I want to test...

You can add columns to the existing tables.  These should be initially nullable until the data is loaded.  After that you can alter the column definition to NOT NULL if appropriate.  You can also drop columns from the table definition.

The process I use is:

Add new columns to table definition.
Populate new columns. (Before using code that expects these columns to be populated.)
Alter old NOT NULL columns to NULL. (Before using code that doesn't populate these columns.)
Make new columns NOT NULL where appropriate. (After code that doesn't populate these columns has been retired. May require rerun of population scripts.)
Drop old columns.  (Can be done much later.)

These steps may be done over a period of days or weeks.  This depends on your development process.  The population script should be able to identify and populate rows created or modified by old code.


How to convert memory optimized table into disk based table in sql server Could anyone please explain the process to convert memory optimized table into disk based table in sql server?

Thanks & Regards,
Phanindhra S

Could anyone please explain the process to convert memory optimized table into disk based table in sql server?

Create a new disk based table and insert the data from the in-memory table or use select into to copy the data over and create the table at the same time. There are various other ways but all are variations on this.

Please note that you cannot remove the in-memory filegroup from the database, so if you're attempting to clean up all traces of in-memory then I'd suggest migrating the data to a completely new database.


Is a mySQL or mongoDB relational database scalable for holding 80 GBs of additional logs per day? I am currently deciding on a long term architecture solution for storing DNS logs. The amount of data we are talking about numbers some 80 GBs of logs per day at the peak. Currently I am looking at noSQL databases such as mongoDB, as well as relational - mySQL. I want to structure a solution that has three requirements:

Storage: This is a long term project, so I want the necessary capability to store 80 GBs of logs per day (~30 TB a year!). I realize this is pretty ridiculous, so I'm willing to have a retention period (keep 6 months' worth of logs = 15 TB constant).
Scalability: As it is a long term solution, this is a big issue. I've heard that mongoDB is horizontally scalable, while mySQL is not? Any elaboration on this would be very well received.
Query speed: As close to instantaneous querying as possible.

It should be noted that our logs are stored on an intermediary server, so we do not need to forward logs from our dns servers.

MySQL can handle at least some aspects of such.

At 15TB, disk I/O is the number one problem!  Both on INSERT and SELECT.

Indexes are BTree structured.  This gives "close to instantaneous" fetching of one row, given the index.  It also gives rapid scanning in the BTree order, forward or backward.

You can have multiple unrelated indexes, but it is problematical.  Indexes are kept up to date as rows are inserted.  This is not the least bit of a problem if the indexes fit in cache.  And not much of a problem when the indexes are somewhat bigger than cache.  The problem comes when an index is much bigger than can be cached in RAM, and the next insert needs to update some spot in the BTree that is not currently cached.  INSERTing slows to disk speed -- perhaps 10s of millions of rows inserted per day.  (This is regardless of LOAD DATA, batched INSERTs, staging tables, etc.)

So, you can't have all the indexes you may want.  And without indexes, forget about "close to instantaneous".

So, what to do?  I suggest you turn the design around -- What do you want out of this project?  That is, put aside the discussion of size and storage; instead think about what SELECTs you would like.  Then think about how to arrange the data to minimize I/O.  Would you like to present some of the SELECTs for discussion?

I will probably push you toward summarizing the data as it comes in, storing the summaries in tables, and tossing the raw data (or at least not storing it in a database).

Two further notes:

Tokudb is excellent at ingesting indexed data -- it can generally do it faster than the standard "engines" in MyISAM.  MariaDB has Tokudb built in.

For purging the data, see my blog on such.


Microsoft Access database to FileMaker Pro I have all my customer details in Microsoft Access. I want to transfer all this data to FileMaker Pro. I hope there is a way to do this. If so please help me.

You have basically two options (without resorting to any third party tool, besides ODBC):

Use intermediate "common import/export formats": Export data from all the different tables in Access in a format that FileMaker Pro can immediately import, then import the exported files from FileMaker. The safest format is normally CSV --comma separated values--; although it won't work with FileMaker if you have data with line-feeds or carriage-returns inside. You can also try Excel as an intermediate export format (with this format, you may have some limitation if you have long texts). Check: Introduction to importing and exporting data (Access) and Bringing data into FileMaker Pro - Overview and troubleshooting.
Direct import through ODBC: Directly import the tables from FileMaker by means of an ODBC connection**. When you install Office, you normally get ODBC controllers installed by default, so, if you have Access installed in your computer, you're very likely to have ODBC controllers as well. Make an ODBC connection to your Access database, and use it from within FileMaker to import your tables. Check Querying an ODBC data source from FileMaker Pro.

FileMaker can import table data from Access (or other databases). It cannot import table relationships (or foreign key relationships) or primary key definitions, null/not null constraints, index definitions or check constraints. All these metadata you'll need to transfer "by hand". The Access database documenter can help you get most of it.


What are the cons of implementing faceted search with WHEREs and JOINs? I'm implementing a filter search system (I understood today that is called faceted search) for an ecommerce website.

The fron end looks something like this:

Country:
[Select a country]

Categories:
(x) category 1
( ) category 2
(x) category 3
...

Format:
( ) format 1
(x) format 2
( ) format 3
...

...

I'm implementing it with an AND between each filters.
I implemented it my way, because I didn't know it was called faceted search and couldn't find suggestions on how to do it, so I came up with something like this:

SELECT ...
FROM (products AS p)
LEFT JOIN countries AS c ON c.id=p.country_id
JOIN categories_products AS cp ON p.id=cp.product_id
JOIN categories AS cat ON cp.category_id=cat.id
LEFT JOIN formats AS f ON f.id=p.format_id
WHERE cat.id IN (9, 12)
AND f.id IN (3, 5, 6, 7)
AND c.id =  94
GROUP BY p.id
HAVING COUNT(DISTINCT(cat.id)) = 2;

Basically, a product has only one country, but can belong to any number of categories. Then there's the format and other filters which I'm not displaying here. Each one of this has its own table.
It's working the way it is, but today I read about implementations with a facet table where are stored facts. So, from what I understood, every row would represent every possible combination of options.

I'm filtering a table of about 7000 rows. Is my approach (that) bad from a perspective point view? Other cons?

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_id` int(11) DEFAULT NULL,
  `format_id` int(11) DEFAULT NULL,
  `featured` tinyint(1) DEFAULT '0',
  `is_limited` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `order` (`order`),
  KEY `photographer` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=33514 DEFAULT CHARSET=utf8;

This is the relevant part of the table. I omitted some fields like name, description and some others.
What I meant saying that "each one of these has its own table" was that each of these filters (categories, format, country) has its own table. So there's a categories table and a categories_products relation table; there's a countries table; a formats table and so on.

You should have indexes on more of the fields; it gets tedious scanning all 7K rows.

What is the datatype of order; can it be the PRIMARY KEY and get rid if id?

Recommend you use standard 2-letter country_code instead of an extra table.  And declare it CHAR(2) CHARACTER SET ascii.

If you expect to have a million rows, I would say that you will have lots of performance problems.  But...  1K/minute is exciting; 1K/second is challenging; 1K/year is nothing. -- So, performance is not an issue.


Importing True/False as tinyint failing when last field Here is a line from the file I am loading.

"dev"|"APSET3454"|"apset3454.xxx.xxx.xxx"|"TWS01"|31111|True|False|False|"1"|True|"Win2012R2 64 bit ESC:712482-2267174 IEMID:84520"|"2017-12-20T15:06:15.390+0000"|"WINDOWS"|"9.4.0.04"|"FTA"|"America/Chicago"|2602|"2019-02-27T13:15:08.000+0000"|30|0|""|True|True|False|False|"LINKED"|True

And here is the load command I am using.  Seems to work fine except that the last field is always comming in as a zero(false)

LOAD DATA LOCAL INFILE "/autohome/astools/Programs/FTA_api/Workdir//nm2.20190305103959.csv"
INTO TABLE  fta_status.fta_info COLUMNS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"' LINES TERMINATED BY '\n'
(TWSINSTANCE,CPUID,NODENAME,FTA_DOMAIN,TCPPORT,@AUTOLINK,@BEHINDFIREWALL,@FULLSTATUS,
SERVER,@INCLUDEINPLAN,DESCRIPTION,@MODIFIEDDATETIME,OS,VERSION,TYPE,TIMEZONE,
SYMRUNNUMBER,@TIMESTARTED,FTA_LIMIT,FENCE,ACCESSMETHOD,@TCPLINK,@JOBMANUP,@WRITERUP,
@WRITERDS,LINKSATUS,@WORKSTATIONINPLAN)
SET AUTOLINK = (@AUTOLINK = 'TRUE'),
BEHINDFIREWALL = (@BEHINDFIREWALL = 'TRUE'),FULLSTATUS = (@FULLSTATUS = 'TRUE'),
INCLUDEINPLAN = (@INCLUDEINPLAN = 'TRUE'),
MODIFIEDDATETIME = DATE_FORMAT(STR_TO_DATE(@MODIFIEDDATETIME,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s'),
TIMESTARTED = DATE_FORMAT(STR_TO_DATE(@TIMESTARTED,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s'),
TCPLINK = (@TCPLINK = 'TRUE'),JOBMANUP = (@JOBMANUP = 'TRUE'),
WRITERUP = (@WRITERUP ='TRUE'),WRITERDS = (@WRITERDS = 'TRUE'),
WORKSTATIONINPLAN = (@WORKSTATIONINPLAN = 'TRUE');

I do get warnings about the 2 datetime fields

  Level: Warning
    Code: 1292
    Message: Truncated incorrect datetime value: '2017-12-20T15:06:15.390+0000'

So if someone would like to help with the date_format STR_TO_DATE conversion as well :-)


SQL Server Cannot drop database  because it is currently in use... but no sessions displayed When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database.  I've also set the database to single_user mode with rollback immediate.

Why is this happening?

A session connected to another database might have an open transaction that also affects your database - sp_who2 will only show one database. It could also be something as simple as Object Explorer or Object Explorer Details open in SSMS, which again would only show one database in sp_who2.

Don't bother trying to find the session that is responsible; just kill them all with one statement (and make sure it isn't your copy of SSMS that is connected, e.g. another query window, Object Explorer, etc.):

USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Now you will be able to drop it, and do that using DDL, not the UI:

DROP DATABASE dbname;


Extended Event Session Keeps Stopping A few months ago I added an extended event to track blocked processes and deadlocks, with a file output to track occurrences.

Every now and again, I check the log file, but find that the extended event has stopped, when I "Start Session" again through SSMS, it picks up again and logs as expected.

How can I ensure that this runs 24/7?
Is it possibly failing somewhere and there's a log of errors that I'm unaware of?

It seems your Extended Event session is defined with STARTUP_STATE=OFF so it is not started automatically when SQL Server is restarted.

Change the session to STARTUP_STATE=ON to ensure it's always running (unless stopped manually):

ALTER EVENT SESSION [blocked_process] ON SERVER
    WITH (STARTUP_STATE=ON);


How to get an exclusive connection in AutoRestore job ( sp_RestoreGene)? I am using Paul Brewer's script for AutoRestore sp_RestoreGene on SQL Server 2008R2 box.

I created the stored procedure dbo.sp_RestoreGene on the Server 1 where the backup file is located.

I called the store procedure:

USE [DBA_]
DECLARE @RC int
EXECUTE @RC = [dbo].[sp_RestoreGene]
@Database = 'Production'
,@TargetDatabase = 'Test'
,@WithRecovery = 1
,@WithCHECKDB = 1
,@WithReplace = 1
,@FromFileFullUNC = '\\Server1\Restore_backup\Backup\FullBackupForTest\'
,@Log_Reference = 'Incident xyz'

GO

It generated the following Restore script.  I created a job which runs the script below in scheduled time on Server 2

;DECLARE @msg_Production VARCHAR(1000)
;RESTORE DATABASE [Test] FROM DISK = N'\\Server1\Restore_backup\Backup\FullBackupForTest\Production.bak'  WITH REPLACE, FILE = 2, NORECOVERY, STATS=10, MOVE N'Production' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf', MOVE N'Production_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_log.ldf'
;SET @msg_Production = 'Incident xyz';  RAISERROR (@msg_Production,0,0) WITH LOG;RESTORE DATABASE [Test] WITH RECOVERY
;SET @msg_Production = 'Incident xyz';  RAISERROR (@msg_Production,0,0) WITH LOG;DBCC CHECKDB('Test') WITH NO_INFOMSGS, ALL_ERRORMSGS

I got an error:

  Msg 3101, Level 16, State 1, Line 2 Exclusive access could not be
  obtained because the database is in use. Msg 3013, Level 16, State 1,
  Line 2  RESTORE DATABASE is terminating abnormally.

To get the exclusive connection when I restore manually I use the script below . It works perfect.

use master
Go
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
waitfor delay '00:00:05' -- 5 sec delay .. no other spid takes our space !
GO

Restore script

Go
ALTER DATABASE Test SET MULTI_USER
go

Can I include in the restore script the alter single user and alter set multi_user script? Or is there a better way to get exclusive connection for autorestore?

How to get exclusive connection for Auto restore ?

To get exclusive database access (provided it is not used in replicaiton or logshipping or any other HA/DR), OFFLINE it.

ALTER DATABASE DB_name
SET OFFLINE WITH ROLLBACK IMMEDIATE

Make this as a first step and then run the restore script that you generated.

BTW, its  Paul Brewer's restore gene script that you are using.


Delay between Primary and Secondary DB How to measure the delay between secondary node to catch up with the primary node?
I am using red gate to monitor the databases and this is what mentioned there
Guideline values: To estimate how long a secondary replica will take to catch up with the primary replica, divide the log send queue by the rate of log bytes received.

Based on this graph can you advise me how can I measure this delay ? it is not clear for me how to divide these 2 values
If there is another way to measure the delay I will appreciate to share

I'm not experienced with RedGate's monitoring tools, but SSMS has an Availability Group Dashboard out-of-the-box that I've always found particularly helpful with basic metrics like this. Specifically the metric in the dashboard that would be helpful to you is called the Estimated Recovery Time (seconds):

Indicates the time in seconds it takes to redo the catch-up time. The catch-up time is the time it will take for the secondary replica to catch up with the primary replica. This value is hidden by default.

I also found the Redo Queue Size (KB):

Indicates the number of log records in the log files of the secondary replica that have not yet been redone. This value is hidden by default.

And the Redo Rate (KB/sec) particularly helpful in monitoring the health of my secondary replicas:

Indicates the rate in KB per second at which the log records are being redone. This value is hidden by default.

If you want to access this information more natively so you can collect the metrics over time, the above Availability Group Dashboard just leverages the following DMVs:

sys.dm_hadr_database_replica_states
sys.availability_databases_cluster
sys.availability_groups
sys.availability_replicas

You can find more information on querying the DMVs for this information in SQLPerformance's Monitoring Availability Group Replica Synchronization. This is the query from the article (with one additional calculated column to get you the Estimated Recovery Time):
SELECT
    ar.replica_server_name,
    adc.database_name,
    ag.name AS ag_name,
    drs.is_local,
    drs.is_primary_replica,
    drs.synchronization_state_desc,
    drs.is_commit_participant,
    drs.synchronization_health_desc,
    drs.recovery_lsn,
    drs.truncation_lsn,
    drs.last_sent_lsn,
    drs.last_sent_time,
    drs.last_received_lsn,
    drs.last_received_time,
    drs.last_hardened_lsn,
    drs.last_hardened_time,
    drs.last_redone_lsn,
    drs.last_redone_time,
    drs.log_send_queue_size,
    drs.log_send_rate,
    drs.redo_queue_size,
    drs.redo_rate,
    drs.redo_queue_size / see.redo_rate AS EstimatedRecoveryTime -- Additional helpful calculated column
    drs.filestream_send_rate,
    drs.end_of_log_lsn,
    drs.last_commit_lsn,
    drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc
    ON drs.group_id = adc.group_id AND
    drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
    ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar
    ON drs.group_id = ar.group_id AND
    drs.replica_id = ar.replica_id
ORDER BY
    ag.name,
    ar.replica_server_name,
    adc.database_name;

You can create a SQL job to routinely log this information to a table so you have a historical comparison to achieve your goals with. (This is essentially what RedGate's Performance Monitor is likely doing under the hood.)
The above article also briefly mentions a third way to monitor these metrics via Perfmon Counters.
And finally a third and equally effective way to log your Availability Groups health metrics is via the built-in Extended Events.


Delete returns nothing in Psycopg2? I have a fairly simple delete query in a PostgreSQL database that I'm interfacing with via psycopg2.

Take the following minimal example:

def testDelete():
    db = DbInterface()
    cur = db.conn.cursor()
    cur.execute("DELETE FROM munamelist WHERE name='something'")
    print("Results = ", cur.fetchall())

Basically, the PostgreSQL documentation for DELETE states:

  On successful completion, a DELETE command returns a command tag of
  the form

  DELETE count

  The count is the number of rows deleted. Note that the number may be
  less than the number of rows that matched the condition when deletes
  were suppressed by a BEFORE DELETE trigger. If count is 0, no rows
  were deleted by the query (this is not considered an error).

However, psycopg2 raises a error when you try to fetch the results of the query:

Traceback (most recent call last):
  File "autoOrganize.py", line 370, in <module>
    parseCommandLine()
  File "autoOrganize.py", line 363, in parseCommandLine
    testDelete()
  File "autoOrganize.py", line 247, in testDelete
    print("Results = ", cur.fetchall())
psycopg2.ProgrammingError: no results to fetch

It doesn't matter if the item exists or not, you cannot fetch the results of the query. Does psycopg2 not return "command tags" for SQL?

If not, how can I retrieve the number of altered rows as would be returned in the console interface? Never mind, apparently cursor.rowcount is the number of rows modified in the last DML/DQL statement.

DELETE without RETURNING doesn't return a result set. So you can't use fetch... calls with it, like cursor.fetchall().

The affected row count is stored in cursor.rowcount for DML.


Creating a table in Oracle procedure I am trying to create a table inside a procedure but getting some issues related to quote identifier or may be something else.

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable';
END;

This's working fine however I need to create my table as

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable where col1='XXX' and col2='Y'';
END;

above statement is throwing errors.

I tried to change like this but this is also not working.

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable where col1="XXX" and col2="Y"';
END;

begin
  execute immediate 'create table t1 as select * from dba_users where username  =''BP''';
end;
/

SQL> select username from t1;

USERNAME
----------
BP

Double quotes (") have a different meaning than having mutliple single quotes after each other (''). Use this:

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable where col1=''XXX'' and col2=''Y''';
END;


Compare 2 columns in 2 different tables with non uniques values I want to compare and check whether the values in columnA of table A exist in column A of Table B. But the columnA consist of duplicate values in both the table.

Maybe this will help:  use analytics to count (the duplicate) values in each table, and use the results of these operations in a full outer join.  Example (see dbfiddle):

Tables

create table A ( columnA )
as
select 'a' from dual union all
select 'b' from dual union all
select 'b' from dual union all
select 'c' from dual union all
select 'c' from dual union all
select 'c' from dual union all
select 'e' from dual -- one 'e' in each table
;

-- one 'a', 'b', 'c' (respectively) missing here
-- 4 x 'd' (does not exist in the other table)
create table B ( columnA)
as
select 'b' from dual union all
select 'c' from dual union all
select 'c' from dual union all
select 'd' from dual union all
select 'd' from dual union all
select 'd' from dual union all
select 'd' from dual union all
select 'e' from dual -- one 'e' in each table
;

Write to 2 queries, one for each table, that look something like ...

-- count the instances of each value
select unique
  'table A ->' as tbl, columnA
, count(*) over ( partition by columnA ) as instances_a
from A

-- result
TBL         COLUMNA  INSTANCES_A
table A ->  a        1
table A ->  c        3
table A ->  b        2

If this works, then use the initial queries as "inline views", and write a full outer join, which will allow you to compare the value counts, eg

select
  *
from (
  select unique
    'table A ->' as tbl, columnA
  , count(*) over ( partition by columnA ) as instances_a
  from A
) A_
full join
(
  select unique
    'table B ->' as tbl, columnA
  , count(*) over ( partition by columnA ) as instances_b
  from B
) B_
on A_.columnA = B_.columnA
order by A_.columnA, B_.columnA
;

-- result
TBL         COLUMNA  INSTANCES_A  TBL         COLUMNA  INSTANCES_B
table A ->  a        1            NULL        NULL     NULL
table A ->  b        2            table B ->  b        1
table A ->  c        3            table B ->  c        2
NULL        NULL     NULL         table B ->  d        4

You can then modify this query, in order to find out whether values are missing from one of the tables etc.  Eg

select
  A_.tbl, A_.columnA, count_a
, B_.tbl, B_.columnA, count_b
, case
    when count_a = count_b then 'yes'
    else 'no'
  end as "column contents equal?"
from (
  select unique
    'table A ->' as tbl, columnA
  , count(*) over ( partition by columnA ) as count_a
  from A
) A_
full join
(
  select unique
    'table B ->' as tbl, columnA
  , count(*) over ( partition by columnA ) as count_b
  from B
) B_
on A_.columnA = B_.columnA
order by A_.columnA, B_.columnA
;

TBL         COLUMNA  COUNT_A  TBL         COLUMNA  COUNT_B  column contents equal?
table A ->  a        1        NULL        NULL     NULL     no
table A ->  b        2        table B ->  b        1        no
table A ->  c        3        table B ->  c        2        no
table A ->  e        1        table B ->  e        1        yes
NULL        NULL     NULL     table B ->  d        4        no

(Tested w/ Oracle 12c and 18c)


How do I find a long running query with process ID, process name , login time, user , start time and duration? Can anyone help me to find a below given details for long running query.
ProcessID, process name, database, host ,user ,process login time, query start time and query duration.

I am looking for a query or an SP which gives me this data.

As commented, best tool around is sp_whoIsActive by Adam Machanic. It can be used in several ways, to see what is running at the moment you launch the script or you can run it in loops to monitor some specific action, as slow queries for example.
To run in a loop take a look here: How to Log Activity Using sp_whoisactive in a Loop
To detect slow queries: How to Use sp_WhoIsActive to Find Slow SQL Server Queries
You can directly use DMV's to get your slowest queries and act from there. Check Glenn Berry's diagnostic queries.
And finally you can use this query to find most time consuming queries. You can play around with the dm_exec_query_stats to add more data or join with other ones to get more information. Be aware that dmv's gets flushed away and refreshed each time the server is restarted.
SELECT  creation_time
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

EDIT
A new option is available from some time already, First Reponder Kit. Is a set of scripts, freely provided under MIT license by BrentOzar team, that will help on various tasks, including the one asked by the OP. Mainly sp_BlitzFirst and sp_BlitzWho scripts for this case.


How many records should be deleted when batch delete is used? I am going to migrate a large amount of data from one table to other table. The target data source table has foreign key constrains, so I am not able to use DELETE with OUTPUT clause, but need to have a buffer table instead.

The data source table is not going to be read by other users, but the new table (in which I am inserting records will be frequently used during the operation) - so, I need to do the migration without blocking the other users activity.

The database is under FULL recovery model, SQL Server 2012 and transaction log backup is made on every 15 minutes (the transaction log growth during the operation is not an issue anyway).

I have the following T-SQL statement:

CREATE TABLE #DataBuffer
(
    ....
);

DECLARE @RowCount INT;

WHILE 1 = 1
BEGIN

    BEGIN TRY

          BEGIN TRANSACTION

            TRUNCATE TABLE #DataBuffer;

            DELETE TOP (4000)
            FROM [dbo].[OldSourceTable]
            OUTPUT [deleted]. ...
            INTO #DataBuffer (...)
            WHERE ... ;

            INSERT INTO [dbo].[NewSourceTable] (...)
            SELECT ...
            FROM #DataBuffer;

            SET @RowCount = @@ROWCOUNT;

            COMMIT TRANSACTION

            IF @RowCount = 0
            BEGIN
                BREAK
            END

    END TRY

    BEGIN CATCH

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION
        END

    END CATCH

END

and I am wondering which is the best value of rows to be deleted in a batch delete statement?

I have seen different variants and the only thing I have found as recommendation was from here:

  If you delete 4000 or more rows in a single command, the whole table
  will be locked (or the whole partition if you enabled that on
  partitioned table). DO NOT delete 10000 rows in a batch if the table
  needs to be available for other users! So, delete e.g. 2000 rows in a single delete...

But I was not able to find a confirmation in the MSDN or elsewhere about this.

The only correct answer here is "It Depends". There are so many factors that come into play that you will just have do many runs with different parameters try to find what are the optimal settings for your system, and you can also play with locking hints if your table has indexes that are optimal for the search condition.

There are some rules of thumb.. if you have a clustered index on an identity column, which is not fragmented and you are deleting records from a range thats old you can safely ask the engine to lock the extends that the old records are using and delete ~64Kb at a time without fearing of blocking readers. This can help you but is not exact science and offrow data will make this different:

SELECT OBJECT_NAME(i.OBJECT_ID) AS tableName
, i.name AS indexName
, SUM(p.row_count) AS rows
, SUM(p.in_row_data_page_count) AS dataPages
, SUM(p.row_count) / SUM(p.in_row_data_page_count) AS rowsPerPage
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS p
ON i.OBJECT_ID = p.OBJECT_ID
And i.index_id = p.index_id
WHERE OBJECT_NAME(i.OBJECT_ID) Not Like 'sys%' and i.type = 1
GROUP BY OBJECT_NAME(i.OBJECT_ID)
, i.name
, i.type_desc
HAVING SUM(p.in_row_data_page_count) > 100
ORDER BY rowsPerPage;

This will give you average number of rows in the clustered index. You are safe to lock on a single page which will be fast and give you good concurrency. That number times 8 will most likely give you an EXTENT lock with the same benefits and after that you'll have to play with the numbers to see when you will start to lock the table. Also it helps to have READ_COMMITED_SNAPSHOT active.