SQL Server Index and Statistics Maintenance performance question I tried to use IndexOptimize stored procedure on a database with a very high number of indexes (> 250.000). The initial step where stored procedure collects data what needs to be processed takes hours, even if I set @Indexes parameter to narrow down the work.

  SQL Server Maintenance Solution Version: 2019-02-10 10:40:47 SQL
  Server 2017 Standard Edition with latest CU14 installed.

At a customer I saw a database with > 500.000 indexes. After 12 hours data collection step was still running.

I would expect if I set @Indexes to a single index execution should be started immediately.

Here is an example of my stored procedure call.

EXECUTE dbo.IndexOptimize
@Databases = 'db',
@Indexes =  'db.dbo.'
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60

Can somebody share with me his experience in using IndexOptimize for a database with a very high number of indexes ?

Can somebody share with me his experience in using IndexOptimize for a
  database with a very high number of indexes ?

You are right, Ola's script first gets data from all the index related DMV's and inserts them inside of @tmpIndexesStatistics, even if one table or one index is specified.
For a lot of indexes, it gets stuck on this first command, which is a fairly huge query. See below for the query and even further below for a possible fix for this.

TL;DR

In one of the queries, the dynamic management views are called a couple of times each with multiple filters.

All this results in a fairly big execution plan.

Creating temp tables and storing the data from the DMV's prior to running the query is a workaround. You could optimize further with indexes on these temp tables / Rewrites.

Test data: >100K empty tables + >200k empty indexes

USE Test2
GO
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
DECLARE @i int = 0

DECLARE @counter int = 1000
WHILE @i <+ @counter
BEGIN
SET @SQL = N'CREATE TABLE dbo.'+QUOTENAME(cast(@i as nvarchar(20)))+'( id int, val varchar(255));'
EXEC(@sql)
SET @SQL = N'CREATE INDEX IX_'+cast(@i as nvarchar(20))+ ' ON '+QUOTENAME(cast(@i as nvarchar(20)))+'(id) '
EXEC(@sql)
SET @SQL = N'CREATE INDEX IX_'+cast(@i as nvarchar(20))+ '_2 ON '+QUOTENAME(cast(@i as nvarchar(20)))+'(val) '
EXEC(@sql)
set @I +=1

IF @i = @counter
BEGIN

  IF @counter < 100000
  BEGIN
        SET @counter += 1000;
  END
END
END

select count(*) from sys.tables;
--100731

select count(*) from sys.indexes  where index_id != 0;
--201614

When changing the parameters to  work on all the indexes, and printing instead of executing the statement ( @Execute='N')

EXECUTE MNGDB.dbo.IndexOptimize
@Databases = 'test2',
@Indexes =  'test2.dbo.',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60,
@Execute='N';

This huge query comes into play

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
USE [test2];
SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, AllowPageLocks, IsImageText, IsNewLOB, IsFileStream, IsColumnStore, IsComputed, IsTimestamp, OnReadOnlyFileGroup, ResumableIndexOperation, StatisticsID, StatisticsName, NoRecompute, IsIncremental, PartitionID, PartitionNumber, PartitionCount, [Order], Selected, Completed
FROM (SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, tables.is_memory_optimized AS IsMemoryOptimized, indexes.index_id AS IndexID, indexes.[name] AS IndexName, indexes.[type] AS IndexType, indexes.allow_page_locks AS AllowPageLocks,
CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = objects.object_id AND types.name IN('image','text','ntext')) THEN 1 ELSE 0 END AS IsImageText,
CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE columns.[object_id] = objects.object_id AND (types.name IN('xml') OR (types.name IN('varchar','nvarchar','varbinary') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) THEN 1 WHEN indexes.[type] = 2
            AND EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.[object_id] = columns.[object_id]
            AND index_columns.column_id = columns.column_id
            INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND (types.[name] IN('xml') OR (types.[name] IN('varchar','nvarchar','varbinary') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1)))
            THEN 1 ELSE 0 END AS IsNewLOB,
CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns WHERE columns.[object_id] = objects.object_id  AND columns.is_filestream = 1) THEN 1 ELSE 0 END AS IsFileStream,
CASE WHEN EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.[object_id] = objects.object_id AND [type] IN(5,6)) THEN 1 ELSE 0 END AS IsColumnStore,
CASE WHEN EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.object_id = columns.object_id AND index_columns.column_id = columns.column_id WHERE (index_columns.key_ordinal > 0 OR index_columns.partition_ordinal > 0) AND columns.is_computed = 1 AND index_columns.object_id = indexes.object_id AND index_columns.index_id = indexes.index_id) THEN 1 ELSE 0 END AS IsComputed,
CASE WHEN EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN sys.types types ON columns.system_type_id = types.system_type_id
WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND types.[name] = 'timestamp') THEN 1 ELSE 0 END AS IsTimestamp,
CASE WHEN EXISTS (SELECT * FROM sys.indexes indexes2 INNER JOIN sys.destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN sys.filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes2.[object_id] = indexes.[object_id] AND indexes2.[index_id] = indexes.index_id AND destination_data_spaces.destination_id = partitions.partition_number) THEN 1 WHEN EXISTS (SELECT * FROM sys.indexes indexes2 INNER JOIN sys.filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = indexes2.[object_id] AND indexes.[index_id] = indexes2.index_id) THEN 1 WHEN indexes.[type] = 1 AND EXISTS (SELECT * FROM sys.tables tables INNER JOIN sys.filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = objects.[object_id]) THEN 1 ELSE 0 END AS OnReadOnlyFileGroup, CASE WHEN EXISTS(SELECT * FROM sys.index_resumable_operations index_resumable_operations WHERE state_desc = 'PAUSED' AND index_resumable_operations.object_id = indexes.object_id AND index_resumable_operations.index_id = indexes.index_id AND (index_resumable_operations.partition_number = partitions.partition_number OR index_resumable_operations.partition_number IS NULL)) THEN 1 ELSE 0 END AS ResumableIndexOperation, stats.stats_id AS StatisticsID, stats.name AS StatisticsName, stats.no_recompute AS NoRecompute, stats.is_incremental AS IsIncremental, partitions.partition_id AS PartitionID, partitions.partition_number AS PartitionNumber, IndexPartitions.partition_count AS PartitionCount, 0 AS [Order], 0 AS Selected, 0 AS Completed FROM sys.indexes indexes INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] LEFT OUTER JOIN sys.tables tables ON objects.[object_id] = tables.[object_id] LEFT OUTER JOIN sys.stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id] LEFT OUTER JOIN sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id LEFT OUTER JOIN (SELECT partitions.[object_id], partitions.index_id, COUNT(DISTINCT partitions.partition_number) AS partition_count FROM sys.partitions partitions GROUP BY partitions.[object_id], partitions.index_id) IndexPartitions ON partitions.[object_id] = IndexPartitions.[object_id] AND partitions.[index_id] = IndexPartitions.[index_id] WHERE objects.[type] IN('U','V') AND objects.is_ms_shipped = 0 AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0

UNION

SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, tables.is_memory_optimized AS IsMemoryOptimized, NULL AS IndexID, NULL AS IndexName, NULL AS IndexType, NULL AS AllowPageLocks, NULL AS IsImageText, NULL AS IsNewLOB, NULL AS IsFileStream, NULL AS IsColumnStore, NULL AS IsComputed, NULL AS IsTimestamp, NULL AS OnReadOnlyFileGroup, NULL AS ResumableIndexOperation, stats.stats_id AS StatisticsID, stats.name AS StatisticsName, stats.no_recompute AS NoRecompute, stats.is_incremental AS IsIncremental, NULL AS PartitionID, dm_db_incremental_stats_properties.partition_number AS PartitionNumber, NULL AS PartitionCount, 0 AS [Order], 0 AS Selected, 0 AS Completed FROM sys.stats stats INNER JOIN sys.objects objects ON stats.[object_id] = objects.[object_id] INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] LEFT OUTER JOIN sys.tables tables ON objects.[object_id] = tables.[object_id] OUTER APPLY sys.dm_db_incremental_stats_properties(stats.object_id, stats.stats_id) dm_db_incremental_stats_properties WHERE objects.[type] IN('U','V') AND objects.is_ms_shipped = 0 AND NOT EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.[object_id] = stats.[object_id] AND indexes.index_id = stats.stats_id)) IndexesStatistics

Now, when we try to find what triggers this command inside [dbo].[IndexOptimize]

IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX')

With @ActionsPreferred corresponding to the fragmentationlevel actions and @UpdateStatistics being update statistics. Meaning that it will run whenever we do a stat update or a index rebuild / reorganize.

no filtering is applied, the query runs over all the indexes in the database.

As a result, at the moment we will have to go through all that trouble, even if we specify one table.

EXECUTE MNGDB.dbo.IndexOptimize
@Databases = 'test2',
@Indexes =  'test2.dbo.[83631]',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60,
@EXECUTE= 'N'

The actual index filtering happen after the the huge command runs:

IF @Indexes IS NULL
      BEGIN
        UPDATE tmpIndexesStatistics
        SET tmpIndexesStatistics.Selected = 1
        FROM @tmpIndexesStatistics tmpIndexesStatistics
      END
      ELSE
      BEGIN
...

and updates the tmpIndexesStatistics table accordingly, as to execute index operations on these indexes.

This query was running over 60 minutes on my small dev server before killing it.

What could possibly be a fix for this?

Well, you would have to change ola's script, but a solution with temp tables worked for me, and executed in 1 minute. Without a doubt, it could be optimized further:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
USE [test2];

select *
INTO #Indexes
from sys.indexes;

select *
INTO #Index_columns
FROM sys.index_columns;

SELECT *
INTO #columns
FROM SYS.columns;

SELECT *
INTO #types
FROM
sys.types;

SELECT *
INTO #destination_data_spaces
FROM sys.destination_data_spaces;

SELECT *
INTO #filegroups
FROM sys.filegroups;

SELECT *
INTO #stats
FROM sys.stats;

SELECT *
INTO #objects
FROM sys.objects;

SELECT *
INTO #partitions
FROM sys.partitions;

SELECT *
INTO #tables
FROM sys.tables;

SELECT *
INTO #index_resumable_operations
FROM sys.index_resumable_operations

SELECT *
INTO #schemas
FROM sys.schemas

+ rerun the query with temp tables instead (char limit)

To change this in the index optimization procedure

Sorry Ola this will hurt to look at

When I scripted the proc, this was from line 1430 Until 1537

 IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
    AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)
    AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') = 'READ_WRITE'
    BEGIN

      -- Select indexes in the current database
      IF (EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IS NOT NULL) AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)
      BEGIN
        SET @CurrentCommand01 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;'
                              + 'USE ' + QUOTENAME(@CurrentDatabaseName) + ';
                                select *
                                INTO #Indexes
                                from sys.indexes;
                                select *
                                INTO #Index_columns
                                FROM sys.index_columns;
                                SELECT *
                                INTO #columns
                                FROM SYS.columns;
                                SELECT *
                                INTO #types
                                FROM
                                sys.types;
                                SELECT *
                                INTO #destination_data_spaces
                                from sys.destination_data_spaces;
                                SELECT *
                                INTO #filegroups
                                FROM sys.filegroups;
                                SELECT * kill 64
                                INTO #stats
                                from sys.stats;
                                SELECT *
                                INTO #objects
                                from sys.objects;
                                SELECT *
                                INTO #partitions
                                from sys.partitions;
                                SELECT *
                                INTO #tables
                                FROM sys.tables;
                                SELECT *
                                INTO #index_resumable_operations
                                FROM sys.index_resumable_operations;
                                SELECT *
                                INTO #schemas
                                FROM sys.schemas; '
                              + ' SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, AllowPageLocks, IsImageText, IsNewLOB, IsFileStream, IsColumnStore, IsComputed, IsTimestamp, OnReadOnlyFileGroup, ResumableIndexOperation, StatisticsID, StatisticsName, NoRecompute, IsIncremental, PartitionID, PartitionNumber, PartitionCount, [Order], Selected, Completed'
                              + ' FROM ('

        IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX')
        BEGIN
          SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID'
                                                    + ', schemas.[name] AS SchemaName'
                                                    + ', objects.[object_id] AS ObjectID'
                                                    + ', objects.[name] AS ObjectName'
                                                    + ', RTRIM(objects.[type]) AS ObjectType'
                                                    + ', ' + CASE WHEN @Version >= 12 THEN 'tables.is_memory_optimized' ELSE '0' END + ' AS IsMemoryOptimized'
                                                    + ', indexes.index_id AS IndexID'
                                                    + ', indexes.[name] AS IndexName'
                                                    + ', indexes.[type] AS IndexType'
                                                    + ', indexes.allow_page_locks AS AllowPageLocks'

                                                    + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM #columns columns INNER JOIN #types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = objects.object_id AND types.name IN(''image'',''text'',''ntext'')) THEN 1 ELSE 0 END AS IsImageText'

                                                    + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM #columns columns INNER JOIN #types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE columns.[object_id] = objects.object_id AND (types.name IN(''xml'') OR (types.name IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) THEN 1'
                                                    + ' WHEN indexes.[type] = 2 AND EXISTS(SELECT * FROM #Index_columns index_columns INNER JOIN #columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN #types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND (types.[name] IN(''xml'') OR (types.[name] IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) THEN 1 ELSE 0 END AS IsNewLOB'

                                                    + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM #columns columns WHERE columns.[object_id] = objects.object_id  AND columns.is_filestream = 1) THEN 1 ELSE 0 END AS IsFileStream'

                                                    + ', CASE WHEN EXISTS(SELECT * FROM #Indexes indexes WHERE indexes.[object_id] = objects.object_id AND [type] IN(5,6)) THEN 1 ELSE 0 END AS IsColumnStore'

                                                    + ', CASE WHEN EXISTS(SELECT * FROM #Index_columns index_columns INNER JOIN #columns columns ON index_columns.object_id = columns.object_id AND index_columns.column_id = columns.column_id WHERE (index_columns.key_ordinal > 0 OR index_columns.partition_ordinal > 0) AND columns.is_computed = 1 AND index_columns.object_id = indexes.object_id AND index_columns.index_id = indexes.index_id) THEN 1 ELSE 0 END AS IsComputed'

                                                    + ', CASE WHEN EXISTS(SELECT * FROM #Index_columns index_columns INNER JOIN #columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN #types types ON columns.system_type_id = types.system_type_id WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND types.[name] = ''timestamp'') THEN 1 ELSE 0 END AS IsTimestamp'

                                                    + ', CASE WHEN EXISTS (SELECT * FROM #Indexes indexes2 INNER JOIN #destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN #filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes2.[object_id] = indexes.[object_id] AND indexes2.[index_id] = indexes.index_id' + CASE WHEN @PartitionLevel = 'Y' THEN ' AND destination_data_spaces.destination_id = partitions.partition_number' ELSE '' END + ') THEN 1'
                                                    + ' WHEN EXISTS (SELECT * FROM #Indexes indexes2 INNER JOIN #filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = indexes2.[object_id] AND indexes.[index_id] = indexes2.index_id) THEN 1'
                                                    + ' WHEN indexes.[type] = 1 AND EXISTS (SELECT * FROM #tables tables INNER JOIN #filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = objects.[object_id]) THEN 1 ELSE 0 END AS OnReadOnlyFileGroup'

                                                    + ', ' + CASE WHEN @Version >= 14 THEN 'CASE WHEN EXISTS(SELECT * FROM #index_resumable_operations index_resumable_operations WHERE state_desc = ''PAUSED'' AND index_resumable_operations.object_id = indexes.object_id AND index_resumable_operations.index_id = indexes.index_id AND (index_resumable_operations.partition_number = partitions.partition_number OR index_resumable_operations.partition_number IS NULL)) THEN 1 ELSE 0 END' ELSE '0' END + ' AS ResumableIndexOperation'

                                                    + ', stats.stats_id AS StatisticsID'
                                                    + ', stats.name AS StatisticsName'
                                                    + ', stats.no_recompute AS NoRecompute'
                                                    + ', ' + CASE WHEN @Version >= 12 THEN 'stats.is_incremental' ELSE '0' END + ' AS IsIncremental'
                                                    + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'partitions.partition_id AS PartitionID' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionID' END
                                                    + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'partitions.partition_number AS PartitionNumber' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionNumber' END
                                                    + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'IndexPartitions.partition_count AS PartitionCount' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionCount' END
                                                    + ', 0 AS [Order]'
                                                    + ', 0 AS Selected'
                                                    + ', 0 AS Completed'
                                                    + ' FROM #Indexes indexes'
                                                    + ' INNER JOIN #objects objects ON indexes.[object_id] = objects.[object_id]'
                                                    + ' INNER JOIN #schemas schemas ON objects.[schema_id] = schemas.[schema_id]'
                                                    + ' LEFT OUTER JOIN #tables tables ON objects.[object_id] = tables.[object_id]'
                                                    + ' LEFT OUTER JOIN #stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id]'
          IF @PartitionLevel = 'Y'
          BEGIN
            SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN #partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id'
                                                      + ' LEFT OUTER JOIN (SELECT partitions.[object_id], partitions.index_id, COUNT(DISTINCT partitions.partition_number) AS partition_count FROM #partitions partitions GROUP BY partitions.[object_id], partitions.index_id) IndexPartitions ON partitions.[object_id] = IndexPartitions.[object_id] AND partitions.[index_id] = IndexPartitions.[index_id]'
          END

          SET @CurrentCommand01 = @CurrentCommand01 + ' WHERE objects.[type] IN(''U'',''V'')'
                                                    + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END
                                                    + ' AND indexes.[type] IN(1,2,3,4,5,6,7)'
                                                    + ' AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0'
        END

When testing and rerunning the procedure with one table,it executed in one minute:

EXECUTE dbo.IndexOptimize
@Databases = 'test2',
@Indexes =  'test2.dbo.[83631]',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60,
@EXECUTE= 'N';

Duration      00:01:18

Earlier filters / temp tables with indexes / ... could be used to improve the query further.


How to LEFT JOIN a date table onto each customer (GROUP BY) in a customer purchase table? Can a JOIN be done for dates after initial purchase? I am stuck on implementing a left join on dates for each individual customer.

I have a table which stores customer purchases:

SELECT purchase_date, customer
FROM purchases

I would like to create a similar table however one that includes ALL dates and not just purchase dates, for example:

SELECT date, customer, purchase_made

Where purchase_made is a BOOLEAN column. I understand that I need to use a LEFT JOIN however I am stuck at making sure that EACH customer has a full set of dates. For example if no customers made a purchase on 2016-01-01, and there are 2000 customers in the database, then 2000 rows of 2016-01-01 should be added (with each row attributed to a customer).

So far I've tried:

WITH dates AS (
    SELECT GENERATE_SERIES('2016-01-01'::TIMESTAMP, '2016-01-15'::TIMESTAMP, '1 day'::INTERVAL) AS date
    ),

SELECT date, customer
FROM purchases
LEFT JOIN dates ON purchases.date=dates.date;

How to implement the BOOLEAN column?

Also is it possible to add the date rows only AFTER a customers first purchase? So for example only insert 2016-01-01 for customers who made a purchase prior to 2016-01-01.

To follow on from my comment. I'd recommend a date table.

Sample Customer Purchase Data

IF OBJECT_ID('tempdb..#CustomerPurchases') IS NOT NULL DROP TABLE #CustomerPurchases
GO
CREATE TABLE #CustomerPurchases (PurchaseDate date, CustomerName varchar(20))
INSERT INTO #CustomerPurchases (PurchaseDate, CustomerName)
VALUES
 ('2016-07-04','Jon Snow')
,('2016-07-06','Jon Snow')
,('2016-07-07','Jon Snow')
,('2016-07-07','Jon Snow')
,('2016-07-07','Jon Snow')
,('2016-07-05','Daenerys Targaryen')
,('2016-07-06','Daenerys Targaryen')
,('2016-07-09','Daenerys Targaryen')
,('2016-07-09','Daenerys Targaryen')
,('2016-07-10','Daenerys Targaryen')

Sample Date Table

IF OBJECT_ID('tempdb..#DateTable') IS NOT NULL DROP TABLE #DateTable
GO
CREATE TABLE #DateTable (DateList date)
INSERT INTO #DateTable (DateList)
VALUES
 ('2016-07-04')
,('2016-07-05')
,('2016-07-06')
,('2016-07-07')
,('2016-07-08')
,('2016-07-09')
,('2016-07-10')

You could cross join the list of dates to then get a full list of all customers and all dates. Then join to the actual sales data to return the Boolean value you're after.

SELECT
dt.DateList
,cu.CustomerName
,(CASE WHEN cp.PurchaseDate IS NULL THEN 0 ELSE 1 END) PurchaseMade
FROM #DateTable dt
CROSS JOIN  (
                SELECT DISTINCT
                CustomerName
                FROM #CustomerPurchases
            ) cu
LEFT JOIN   (
                SELECT DISTINCT
                CustomerName
                ,PurchaseDate
                FROM #CustomerPurchases
            ) cp
    ON dt.DateList = cp.PurchaseDate
    AND cu.CustomerName = cp.CustomerName

Results would look like this

DateList    CustomerName        PurchaseMade
2016-07-04  Daenerys Targaryen  0
2016-07-05  Daenerys Targaryen  1
2016-07-06  Daenerys Targaryen  1
2016-07-07  Daenerys Targaryen  0
2016-07-08  Daenerys Targaryen  0
2016-07-09  Daenerys Targaryen  1
2016-07-10  Daenerys Targaryen  1
2016-07-04  Jon Snow            1
2016-07-05  Jon Snow            0
2016-07-06  Jon Snow            1
2016-07-07  Jon Snow            1
2016-07-08  Jon Snow            0
2016-07-09  Jon Snow            0
2016-07-10  Jon Snow            0

If you were to do this then your date table would obviously be much wider than the week example I've done. You could then just use date parameters to restrict to the date range you're after. Something like this;

DECLARE @StartDate date; SET @StartDate = '2016-07-05'
DECLARE @EndDate date; SET @EndDate = '2016-07-08'
SELECT
dt.DateList
,cu.CustomerName
,(CASE WHEN cp.PurchaseDate IS NULL THEN 0 ELSE 1 END) PurchaseMade
FROM #DateTable dt
CROSS JOIN  (
                SELECT DISTINCT
                CustomerName
                FROM #CustomerPurchases
            ) cu
LEFT JOIN   (
                SELECT DISTINCT
                CustomerName
                ,PurchaseDate
                FROM #CustomerPurchases
            ) cp
    ON dt.DateList = cp.PurchaseDate
    AND cu.CustomerName = cp.CustomerName
WHERE dt.DateList BETWEEN @StartDate AND @EndDate
ORDER BY CustomerName, DateList

Which would give these results

DateList    CustomerName        PurchaseMade
2016-07-05  Daenerys Targaryen  1
2016-07-06  Daenerys Targaryen  1
2016-07-07  Daenerys Targaryen  0
2016-07-08  Daenerys Targaryen  0
2016-07-05  Jon Snow            0
2016-07-06  Jon Snow            1
2016-07-07  Jon Snow            1
2016-07-08  Jon Snow            0


Restore Read Only FileGroups from File I have a database DB in simple recovery mode with filegroups PRIMARY, A, B, and C. Each filegroup has a single file, PRIMARY.MDF, A.NDF, B.NDF, and C.NDF, respectively.

First, I run:

ALTER DATABASE [DB] MODIFY FILEGROUP [A] READ_ONLY
ALTER DATABASE [DB] MODIFY FILEGROUP [B] READ_ONLY

Next, I run:

BACKUP DATABASE [DB] READ_WRITE_FILEGROUPS TO DISK = N'C:\DB.BAK'

I copy the backup file to a separate server and perform a partial restore with:

RESTORE DATABASE [DB]
FILEGROUP = 'PRIMARY', FILEGROUP = 'C'
FROM DISK = 'C:\DB.BAK'
WITH PARTIAL, RECOVERY

The database is now online on the second server, and I can query data from filegroups PRIMARY and C. The database is aware of filegroups A and B, but they are marked as offline. Is there a way to restore the remaining two read only filegroups directly from the .NDF files?

Because you backed up only the read write files of the database with the command READ_WRITE_FILEGROUPS, you will not be able to restore A or B from the backup. Ideally, you should go perform a full backup of the database and restore all of the files you want from it. This is the recommended method for many reasons

I was thinking of when or why you wouldn't do this. I'm thinking the last ditch effort you would copy and paste ndf files A or B to the location of SQL Server to try and attach with the restore (which may be out of sync and may not work anyway) is if your only backup was the one you posted about. (Only read write databases, all read only lost.) But what would be that last ditch effort?

Corrupted database? SANS blew up? Server completely offline? In all of those circumstances you're not going to be able to grab A and B anyway. Perhaps there's an automated process that copies and clones the SANS to a backup server (but why not the backup files then?) and that piece is operational while the database itself is down, then maybe I can see where you can try to grab the NDF files and attempt a reattachment, but I feel time would be better spent getting the server running and restoring those copies of the files back over.

But here is what I'd suggest as a last ditch effort if you are persistent and intent on doing this for whatever reasons. I will have a few articles at the end related to this.

If you are able to copy and paste files A and B into the folder where SQL can access them, you can attempt to do an attachment on the DB you restored. I suspect that you will need to go to the primary server to find files A and B or possibly find another source. It may be that the files are out of sync and cannot be attached. If there happened to be no activity since the backup, you may have a chance.

Here are some examples of attaching the files:

Stack Overflow on how to attach files.

Stack Overflow on users having problems attaching MDF files.

Aaron Bertrand of Sentry One suggesting to just take a full backup and restore. Bad habits of MDF files.


SQL Server Database Mail failing to send emails with error Sender address rejected: Access denied The environment is a 2 node 2008 SQL Cluster.  The first node was running fine but after failover to the second node Database Mail no longer can send emails.  I am using Basic authentication.

The full error message is: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 0 (2012-04-10T15:26:56). Exception Message: Cannot send mails to mail server. (Transaction failed. The server response was: 5.7.1 : Sender address rejected: Access denied). )

This appears to be a SMTP issue but I have validated it is not by writing a vbscript with CDO using the same credentials as Database Mail configuration and the emails go through fine on the same box.

I have recreated the Database Mail account on the second node just to make sure the credentials are correct but that didn't fix the problem.

Does anyone know how to get this working again?

Perhaps the IP of your second node is not allowed to relay.


MySQL Subquery count I need to find how many resources are available in depot when transit arrives. I need to have transit name and resource count on result. I'm using MySQL. I wrote this but it gives me error when adding columns to upper select.

select j.name, m.rescount
    (select count(*) as rescount
    from depot_resource as m
    where j.scheduled_time between m.actual_start and m.actual_end)
from transit as j

You can't reference depot_resource in the outer query, it is out of scope.
You need to join the tables, and group by name to get the aggregate.
Since you didn't provide DDL and sample data, we can only guess, but maybe you need something like this?

SELECT j.name, COUNT(*) AS rescount
FROM transit as j
     INNER JOIN
     depot_resource as m
     on j.scheduled_time between m.actual_start and m.actual_end
GROUP BY j.name

Something seems odd here as there is no correlation to j.name in depot_resource, but without the table structure and the expected result we can't really tell.

HTH


MYSQL: Creating a table with same columns on remote machine I am trying to create a table on my local machine which has the same description as some other table on a remote machine. I just want to create the table with same columns, don't worry about the row data.

The table has around 150 columns, so its very tedious to write the CREATE TABLE command. Is there an elegant way of doing this?

On Remote Machine :

Open command prompt
Log in to mysql :   mysql -uusername -p -h
fire : show create table TABLE_NAME
copy whole
exit

On local machine's command prompt, type :

mysql -uusername -p

paste there and press enter


How do you convert IBM DB2's recursive increment to MySQL version? DB2 Queries:

    WITH
    TABLE1 (YEAR_END) as( VALUES
    (2011))
    ,
    TABLE2
    (YEAR_END) AS ( VALUES(2011))
    ,
    TABLE3 (YEARS) AS ( SELECT 0+2008 FROM TABLE1 T1, TABLE2 T2
    union all
    select YEARS+1 from TABLE1 T1, TABLE2 T2, TABLE3 T3
    WHERE  YEARS < 2011
    )

select YEARS from TABLE3

Result:

2008
2009
2010
2011

how to change above DB2 statement to Mysql statement?

Recursive SQL Constructs of this nature do not exist in MySQL.

I know something like ths can be done in SQL Server 2005.

The only WITH operator in MySQL is the WITH ROLLUP modifier clause in GROUP BY functions.


Weird case , how do I delete a cluster under these circumstances I would like to know how to delete a cluster with this type of case.

To explain, I created a cluster for two nodes and there was a bug so I had to stop the service on the first node. However after recovering the server, I can no longer see the cluster suddenly like that as if by magic in the failover manager, and when I ping the cluster is always available somewhere.

How can I delete this cluster and redo a new one? Knowing that when I try to create one,  I am told that my second server is already in a cluster while the cluster in question is no longer visible.

Windows Server 2012 R2

Use the Remove-Cluster PowerShell command with -Force.


What does WITH FORMAT do on a DIFFERENTIAL backup? I could find a lot of questions about this. This is a really good question, exactly what i'm asking here.

Down there, a guys asks the question with:

  you can have compressed and uncompressed on the same drive, just not
  the same media set (file).

I planning to create a simple backup routine where There's a full backup at night, and a differential backup each hour. But to use COMPRESSION in a differential backup, I need to use FORMAT. What does this mean?

  Use FORMAT carefully. Formatting any volume of a media set renders the
  entire media set unusable. For example, if you initialize a single
  tape belonging to an existing striped media set, the entire media set
  is rendered useless.

I can't understand this. Every backup has a different name, and they're going to stay in the same drive. What am I going to format with this option?

Can I have:

FULL.BKP
DIFF1.DIFF( With compression,format)
DIFF2.DIFF( With compression,format)
DIFF3.DIFF( With compression,format)
DIFF4.DIFF( With compression,format)
DIFF5.DIFF( With compression,format)

in the same disk? Thanks.

But to use COMPRESSION in a differential backup, I need to use FORMAT.

I'm not sure why you think you need to use FORMAT at all. This works just fine, I end up with two different differential backup files, each about 80kb:

BACKUP DATABASE db TO DISK = 'c:\bk\dif1.bak' WITH COMPRESSION, DIFFERENTIAL;
GO
BACKUP DATABASE db TO DISK = 'c:\bk\dif2.bak' WITH COMPRESSION, DIFFERENTIAL;

As does writing both differentials to the same file (though I don't know why people like doing this), I end up with a single backup file that contains two differential backups, about 165kb:

BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH COMPRESSION, DIFFERENTIAL;
GO
BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH COMPRESSION, DIFFERENTIAL;

As does overwriting the same file each time, I end up with a single differential backup representing the most recent differential backup I took, about 80kb:

BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH INIT, COMPRESSION, DIFFERENTIAL;
GO
BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH INIT, COMPRESSION, DIFFERENTIAL;

All of these result in compressed differential backups, and I didn't use FORMAT once.

I don't have a 2008 R2 instance to test, and it might be the case that it is impossible in that version to - say - capture multiple diffs to the same backup file (what the documentation might call a media set, but that was more relevant back in that decade when we used tape drives) and take the first diff compression (or encryption, etc.) and the second one without. Compression on and off is fine in modern versions, though I didn't test encryption. In any case, you won't be in that situation if you make each backup its own file in the first place, which I think is what you should do.

This makes your question rather academic. But to answer it, for all intents and purposes, FORMAT on a differential backup effectively does the same thing as INIT - it wipes out any previous backups in the file. So with this:

BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH INIT, COMPRESSION, DIFFERENTIAL;
GO
BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH FORMAT, COMPRESSION, DIFFERENTIAL;

I end up with the same, single 80kb backup as if I had used INIT in both statements. All older backups in that file, regardless of format, except the last one, are removed.


SQL Server 2014 SSIS Package Ignores Script Task when running as AGENT Alright, so here's what I have going on. SSIS Package that queries data from our production system and drops them into CSVs. Have a VB script task that uploads them to a third party. Needed a multi-post HTTP post with authentication and a couple specific fields sent in the header for the 3rd party so had to do a custom VB script instead of using the web destinations.

I have a ForEach loop that goes through, uploads each file via the script and then deletes it. Works great in BIDS. But when I upload it to the SSIS Package store the upload portion doesn't work. But doesn't throw an error anything since the files are still deleted.

The Query portion works, spits out the CSVs. The Script Task does nothing but the files are deleted.

Threw on all sorts of SSIS logging into the package and the script. Threw a "Hello World" log into the first line of the script and it's never fired.

What I've done:

Created Proxies with full access, linked it to new Creds and SQL logins. Nothing. Made the SQL login have rights to everything, nothing. Run the package as the new Proxy execution account and as the service account. Even changed the account the SQL AGENT service runs on (New SQL server, allowed to mess it up right now). Ran the package as 32-bit nothing. Thought it was tied to the ScriptTask reference being set as "Copy Local = True," but changed that and nothing.

Works A-OK in BIDS AND I've used this same script at another client's environment and it worked fine there. I'm not sure what else to try. Any ideas? I can upload code portions if need be.

SQL Help history

That occurred because VS 2015 was built to support only SQL server 2016 and you probably deployed the package under SQL Server 2014 or lower(unsupported versions).

With the latest version of Data tools in VS 2015, you can chose the targeted SQL server version in the package to allow SSDT to chose the correct .NET libraries used in the script component.

See this link for details


Change the owner of an offline database without bringing it online If it is possible I would like to know how to do it via Sql Server Management Studio.

When a database is online I can do it in the files option of the db properties. I don't see that option when the database is offline.

I'm using SQL Server 2008 R2.

This can't be done without bringing the database online, and this is not just a GUI problem.

ALTER AUTHORIZATION ON DATABASE::test TO sa;

Yields:

  Msg 942, Level 14, State 4, Line 1
  Database 'test' cannot be opened because it is offline.

So, as one of the comments suggested, bring it online, set it to single_user if you need to, change the owner, then take it back offline.

ALTER DATABASE test SET ONLINE;
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER AUTHORIZATION ON DATABASE::test TO sa;
ALTER DATABASE test SET MULTI_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE;

Though I have to wonder, why do you care who the owner is until you've brought the database online?


Get Sale Info By Employee I need a way to get detailed sale information for each employee in the StoreInfo table.  Each employeename in that table has it's own table with data structure mirroring the table information below.  I thought maybe a cursor would be the best bet, but it is taking roughly 25 minutes to aggregate all of this data.  Below is my sample DDL - how can I aggregate this data w/o it taking this extend period of time?

Create Table stores
(
  storeID int NOT NULL IDENTITY (1,1) PRIMARY KEY
  ,storename varchar(500)
)

Insert Into stores Values ('WS'), ('RBG'), ('CY'), ('MF')

Create Table storeinfo
(
  storeinfoID int NOT NULL IDENTITY (1,1) PRIMARY KEY
  ,storeID int FOREIGN KEY REFERENCES stores(storeID)
  ,employeename varchar(500)
  ,employeecity varchar(100)
  ,employeestate varchar(100)
)

INSERT INTO storeinfo(storeID, employeename, employeecity, employeestate) VALUES
('1', 'BobGodfrey', 'Mine', 'WV'), ('1', 'RoyBlue', 'Merk', 'SC'), ('1', 'GreenState', 'Merk', 'SC'),
('2', 'PinkSox', 'Yellow', 'CA'), ('2', 'BlueHoodie', 'Yellow', 'CA'), ('3', 'PurpleCow', 'Ridata', 'NY'),
('3', 'StinkySoles', 'Ridata', 'NY'), ('4', 'MetalJacket', 'Bells', 'MN'),   ('4', 'DimensionalData', 'Bells', 'NY')

Create Table BobGodfrey
(
  bobgodfreyID int NOT NULL IDENTITY (1,1) PRIMARY KEY
  ,storeID int FOREIGN KEY REFERENCES stores(storeID)
  ,itemsold varchar(500)
  ,datesold date
)

Insert Into BobGodfrey (storeID, itemsold, datesold) VALUES
('1', 'pants', '02/01/2015'), ('1', 'pants', '02/01/2015'), ('1', 'pants', '02/01/2015'),
('1', 'shirt', '02/01/2015'), ('1', 'pants', '02/01/2015'), ('1', 'pants', '02/01/2015')

Declare @empname varchar(500), @inlinesql varchar(max)

Create Table holdingtable
(
  employeename varchar(500)
  ,saledate date
  ,itemsold varchar(500)
  ,totalsales int
)

DECLARE db_cursor CURSOR FOR
SELECT employeename
FROM storeinfo

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @empname

WHILE @@FETCH_STATUS = 0
BEGIN

   set @inlinesql = 'Select '+@empname+' As employeename, saledate, itemsold, COUNT(itemsold) As [totalsales] '
                    +'INTO holdingtable '
                    +'FROM '+@empname+' '
   Exec(@inlinesql)

   FETCH NEXT FROM db_cursor INTO @empname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Select * from holdingtable

Declare @SelectSql nvarchar(max), @InsertSql nvarchar(max);

SELECT @SelectSql = COALESCE(@SelectSql + ' UNION ALL ', '') +
                'Select '''+employeename+''' As employeename, datesold, itemsold, COUNT(itemsold)  As [totalsales] '
                +'FROM '+employeename+' '
                FROM storeinfo;
-- print @SelectSql;
-- Exec(@inlinesql);

SET @InsertSql = 'Insert into holdingtable ' + @SelectSql;

print @InsertSql;
exec (@InsertSql);

Not sure wheather it will help with performance, but surely is more elegant than cursor.

Edit (as requested by Andriy):

If it takes so long, consider aggregating data more often, I mean divide the work to smaller chunks. For example you can every hour take only the data that was added during this time, aggregate, add to the table. This would require more work. Maybe you will need include some table for pre-aggregations -> aggregate every hour to HourlyHoldingTable, then every nigth aggregate this much smaller table to the DailyHodlingTable. Or only add a comumn with a number of rows aggregated so far, to enable correct update. Many solutions possible here. The general idea is to split the big task to more smaller tasks.

Or you could have an indexed view with aggregation for each table.

The problem is, we don't have enough information - the query you pasted is not complete (missing group by), so it is hard to tell wether problem is in grouping huge number of rows, or in inserting loads of rows.


How do you delete a row that contains a certain value from a specific column across all tables in a database? I'm trying to create a query that searches the database for tables that have the column "GtNumber" that has a row numeric value of "2". I want to then remove these rows so that there are no rows within the database that contain a value of 2 for GtNumber.

Is there any way to get this all done with one query?

I don't think there is a way to do it with a single query but you could query the system tables for any table that has that column name and then use that to create the delete scripts for you.

Select from those tables:

select 'select * from ' + t.name + ' where GtNumber = 2'
from sys.tables t inner join sys.columns c on t.object_id=c.object_id
where c.name='GtNumber'

Delete from those tables:

select 'delete from ' + t.name + ' where GtNumber = 2'
from sys.tables t inner join sys.columns c on t.object_id=c.object_id
where c.name='GtNumber'


Select on several {name,value} pairs I have a this schema:

CREATE TABLE Pairs(
   id    VARCHAR(8)  NOT NULL
   ,name  VARCHAR(40) NOT NULL
   ,value VARCHAR(1000) NOT NULL
   ,PRIMARY KEY (id,name));

I want to write a query to retrieve the id which is present in several rows with several constraints on name and value like:

name like 'forname%' AND value like 'CAMI%'
name like 'projet%'  AND value like '%gociation'
name ... AND  value ...
...

The cardinality of the constraints is unknown at compile time, it comes from a GUI.

I wrote this query:

select * from (
      SELECT * FROM Pairs WHERE ( name   =  'projet'   ) AND ( value like '%gociation' )
union SELECT * FROM Pairs WHERE ( name like 'forname%' ) AND ( value like 'CAMI%' )
) as u order by id

the result is:

id      name    value
AEMIMA  projet  renegociation
AENABE  projet  renegociation
AEREDH  projet  renegociation
AGOGLY  projet  renegociation
AHOGAL  projet  renegociation
AHSPID  projet  renegociation       <<<<<<<<<<<<
AHSPID  fornameE    CAMILLE         <<<<<<<<<<<<
AIOSAP  projet  renegociation
AIPNEU  projet  renegociation

Only the <<<<<<<<<<<< marked lines are good and the id I want is AHSPID.

How can I eliminate the lines which doesn't match ALL the constraints?

I've found answer around Key/Value pairs terms, and the capacity involved is the inner-join:

SELECT DISTINCT
   Prospects.id,
   Prospects.prenom,
   Prospects.nom,
   Prospects.date,
   Prospects.departement
FROM
    Prospects
    INNER JOIN Pairs AS p1 ON Prospects.id = p1.id
    INNER JOIN Pairs AS p2 ON Prospects.id = p2.id
    INNER JOIN Pairs AS p3 ON Prospects.id = p3.id
    INNER JOIN Pairs AS p4 ON Prospects.id = p4.id
    INNER JOIN Pairs AS p5 ON Prospects.id = p5.id
    INNER JOIN Pairs AS p6 ON Prospects.id = p6.id
WHERE TRUE
AND (( p1.name like 'name%'    ) AND ( p1.value LIKE '%XXX%'))
AND (( p2.name like 'forname%' ) AND ( p2.value LIKE '%CAMILLE%'))
AND (( p3.name = 'codePostal'  ) AND ( p3.value = 'XXXXX'))
AND (( p4.name = 'email'       ) AND ( p4.value LIKE '%XXXX%' ))
AND (( p5.name like 'tel_%'    ) AND ( p5.value LIKE '%XXXXXX%'))
AND (( p6.name = 'projet'      ) AND ( p6.value LIKE '%gociat%' ))
AND ( etape IN (-1,3,8 ))

The lines INNER JOIN...id and AND (( p... )) are generated by analysis of the request dynamically made by the GUI. The keyword TRUE is added to make all AND... lines identical, the first isn't different from the others.


How can I have flexible PIVOT column names? Following the example in https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/, I have a PIVOT that works but is not very flexible. It is basically

SELECT * FROM
(SELECT
...) AS raw
PIVOT (
AGGREGATE(aColumn)
FOR x IN ( [c1], [c2], ...) AS pvt;

But the columns I want in my pivot table are listed in another table.  What I'd really like to do is something like:

SELECT * FROM
(SELECT
...) AS raw
PIVOT (
AGGREGATE(aColumn)
FOR x IN ( (SELECT cName FROM otherTable) ) AS pvt;

But SELECT there ends up being a syntax error.  Is there any work around for this?  I really don't want to have to edit this query every time I add another field that should be a column in the output.

You'll need dynamic SQL for this. Something like (untested with your columns and queries because you left those pretty vague):

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';

SELECT @columns += N', ' + QUOTENAME(cName) FROM dbo.OtherTable;

SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  ...
) AS raw
PIVOT
(
  AGGREGATE(aColumn) FOR x IN ('
  + STUFF(@columns, 1, 1, '')
  + ')
) AS p;';

PRINT @sql;

EXEC sys.sp_executesql @sql;

Note that this is adapted from an article of mine on the same site as the link from your question:

Script to create dynamic PIVOT queries in SQL Server


How to verify (CHECKSUM or MD5) dataset you received over a network? I will remotely query mysql database and I want to check that the received dataset matches the table in the host computer. How do you do that? I am fairly new to sql database. I know of Checksum and md5, but how do you use them to verify the received dataset is not corrupted on the local machine.

Why?

If there is a hardware error in the machine, all sorts of things will be dying, not just your one query.

If you are concerned about the network, see all the checksums, etc, involved in TCP/IP and Ethernet.  You don't need a layer of checks on top of that.

If you are asking about consistency between Master and Slave in replication, then say so; that gets into a different area of things.

Bottom line:  Don't worry.  Resultsets are corrupted about as often as you get hit by a meteorite.


How to grant permission for am administrator account in SQL Server 2012? I installed SQL Server 2012 on my laptop. I am trying to run a script to create a simple database.

SQL Server version: 11.0.3128

SSMS starting parameters:

server type: database engine
server name: BOGLENETWORK\SQLEXPRESS
Authentication: Windows Authentication
user name:boglenetwork\admin121

I did some online search and it seems like The account I used doesn't have proper permission to write to the folder in which .mdf and .ldf file should be held. I would like to know do I check the account permission and make necessary modification the the file can be created in the destination folder. Thank for you advise and help!

forum post on a similar issue.

I received the follow message:

  Msg 5133, Level 16, State 1, Line 1
  Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLEXPRESS\MSSQL\DATA\saledat.mdf" failed with
  the operating system error 3(The system cannot find the path specified.).
  Msg 1802, Level 16, State 1, Line 1
  CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Here is the code I am using to create the database:

USE master;
GO
CREATE DATABASE sales
ON
( NAME = sales_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLEXPRESS\MSSQL\DATA\saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = sales_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLEXPRESS\MSSQL\DATA\salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO

I found out that I mis-typed the path name in this case.
The correct folder name is MSSQL11.SQLEXPRESS instead of MSSQL11.MSSQLEXPRESS.

After correcting the folder path, I was able to create the database. Thanks!


PostgreSQL Upsert not working on Partitioned Table Have a table like this:

CREATE TABLE aggregated_master (
  "user"       BIGINT,
  type         TEXT,
  date         TIMESTAMP,
  operations   BIGINT,
  amount       NUMERIC,
  PRIMARY KEY ( "user", type, date )
);

This table is the master from which a lot of partitions inherit.
The partitions are done by MONTH in the DATE field.
For example: Partition for Aug-2017 would be agg_201708 and it's PK would be pk_agg_201708
There is the usual trigger BEFORE INSERT to redirect the insert to the proper partition.

The thing is that I want to do an UPSERT into this table.
The DO CONFLICT part is not working.

The code first was like this

INSERT INTO aggregated_master (user, type, date, oeprations, amount)
SELECT user, type, date, SUM(ops), SUM(amt)
FROM ...
WHERE ...
GROUP BY USER, TYPE, DATE
ON CONFLICT ON CONSTRAINT pk_aggregated
DO UPDATE SET operations = EXCLUDED.operations
          ,   amount = EXCLUDED.amount

But then I noticed that the constraint (pk_aggregated) is the one on the master table, and not on the child table where the insert will really be performed, due to the trigger.

I changed the clause CONFLICT to:

ON CONFLICT (user, type, date)

Which are the fields of the PK, but this doesn't work either.

Any idea how to make this work ?

Upsert on partitioned tables is not implemented in versions earlier than Postgres 11.

In Postgres 9.6:

  INSERT statements with ON CONFLICT clauses are unlikely to work as expected, as the ON CONFLICT action is only taken in case of unique violations on the specified target relation, not its child relations.

Declarative partitioning does not resolve the problem, Postgres 10:

  Using the ON CONFLICT clause with partitioned tables will cause an error, because unique or exclusion constraints can only be created on individual partitions. There is no support for enforcing uniqueness (or an exclusion constraint) across an entire partitioning hierarchy.

Workaround

create unique indexes ("user", type, date) on all child tables,
create and use a function for insert/update based on the Example 42.2. Exceptions with UPDATE/INSERT described in the documentation.

In Postgres 11 you can use ON CONFLICT on partitioned tables, see lad2025's answer.


Where should I look for details about 'access denied' errors? This question derives from Deploying a new database on a new server, an msbuild ssdt rookie issue.

Since I have a permission denied error, I expect to find which user is failing to access so I can grant it.

Is there a place in the Event Viewer (Windows 7) where I can look for it?

You need to look in SQL Server's error log, which is kind of a catch-22, since you need access to the SQL Server in order to see the error log.

You don't need direct access to SQL Server to read the log; if you have physical access to the machine, you can find the log in something like:

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Log\ERRORLOG

Right-click, open with Notepad, and scroll to the bottom, you'll see things like:

  2013-05-23 16:34:56.29 Logon       Error: 18456, Severity: 14, State: 5.
  2013-05-23 16:34:56.29 Logon       Login failed for user 'whatever'. Reason: Could not find a login matching the name provided. [CLIENT: ]

This is, of course, for security purposes. Similar to the reason why these details are not revealed to the end user when they hit the login failure.


How to Replicate data between a hosted database and a local database I have a Cloud hosted Database, maintained by a different company.  Here is my scenario:

Cloud Hosted Database SQL Server 2012 - Need to connect through a VPN.
We setup a local Server with with Dual NIC cards.  This allows us to have the    VPN connection, and the local network connection.
I would like to have a local copy of the cloud hosted database on our server.
I cannot setup replication inside SQL server, I do not have permissions on the cloud server.
I cannot do a back-up and restore of the database - I do not have permissions on the cloud server.
I cannot use triggers
I cannot use logs.
I cannot use back-ups

How can I bring down the entire database, schema and data, and keep my Local database updated real-time?

Edit:  I was able to get a script run from the hosting company so I could create the database along with the schema and initial data.

The most important question is how can I keep the data synchronized real-time?  Right now, i'm thinking of doing a series of Jobs in SQL to query the Linked Server database with something like this:

insert into local_table_1
select * from linkedserver_table1
except
select * from local_table_1

The problem is, it's not efficient, and it will not pull any of the updated records.  We do have an "Updated_date" field.  How can I update the local server, selecting all records from the Linked server, and update the local database?

There's no replication or out-of-the-box solution to your problem, particularly since you don't have that type of access to the remote machine. As other commenters here have noted, you're probably going to have to build your own ETL logic, perhaps in one of the following ways:

A linked server and stored procedures using OPENQUERY() or OPENROWSET(),
An SSIS package, or
A custom-built local application.

The "last updated" columns in the remote tables will help you considerably, in that you won't have to download the entire database's contents every time you run the ETL process. Without being able to install or modify anything on the remote server, there's no practical way to synchronize in "real time". The frequency and speed that you can synchronize the databases will probably come down to:

Indexing on the remote database (how efficiently you can retrieve new records)
Workload on the remote server (locks, I/O speed, available CPU)
Link speed (how fast your VPN is)

I would set a reasonable expectation of synchronizing the data a few times per day, for example early morning, lunch, late afternoon and/or evening. Make sure to plan those executions so they don't collide unneccessarily with running backup jobs (locally, and if you can, remote). Involve the remote party so you don't overload their servers.

T-SQL example

Supposing that you use stored procedures and linked servers, here's an idea on how to go about it. First off, if existing data never changes, i.e. rows are only added (say, like accounting transactions or something):

DECLARE @lastUpdated   datetime2(3),
        @sql           varchar(max);

--- What's the most recent row we've downloaded?
SELECT @lastUpdated=MAX(lastUpdated)
FROM dbo.tableName;

--- Build dynamic SQL
SET @sql='
    INSERT INTO dbo.tableName (x, y, z, lastUpdated)
    SELECT x, y, z, lastUpdated
    FROM OPENQUERY(linkedServerName, ''
        SELECT x, y, z, lastUpdated
        FROM databaseName.dbo.tableName
        WHERE lastUpdated>={ts '''''+CONVERT(varchar, @lastUpdated, 121)+'''''}
        '');
    ';

--- Execute dynamic SQL
EXECUTE sys.sp_executesql @sql;

... and repeat this process for each table. You may run into problems with foreign key constraints, so you may have to arrange the order of the tables carefully, but in the end you may be better off not having any foreign key constraints at all in the local database.

If your existing rows can change, the SQL script looks similar, except you download the data to a temp table first, assign a unique clustered primary key to the temp table, then MERGE ("upsert") the contents of the temp table to your local table. If you don't like or trust MERGE, you can obviously use INSERT and UPDATE the old fashion way. Note that you won't catch rows that have been deleted from the remote table this way.

Using SSIS

With SSIS (or if you decide to build your own local application), the work is the same, but you won't need linked servers, and instead of using sp_executesql, you'll dynamically build SQL statements and run them through an ADODB or similar database connection.

I've included the "build your own application" option for completeness; I wouldn't go down that road.

Indexing considerations

For the remote query to be as efficient as possible, you may want to investigate if you can get the remote DBA to set up an index on the "last updated" column of each table.

Backup option

In the end, I would probably consider Aaron Bertrand's suggestion and try to set up a solution where you download a backup of the remote database (perhaps a full backup, once per night), restore that backup locally and perform the sync operation locally on your server.

Real-time

Returning to the real-time issue - the driving parameter here has to be the business. If management needs up-to-the-second accuracy in your database, somebody is going to have to accept the considerable increase in work and costs, not to mention maintenance. In the end, you may find that a high-speed leased line may be a lot cheaper, and that you can archive the data with a giant download once a month or so instead.


What are the considerations when using SERLIALIZABLE over READ COMMITTED isolation level in Oracle? What are the considerations when using SERIALIZABLE isolation level over the default READ COMMITTED in an Oracle database?

Given the concurrency system used within Oracle (using transaction logs to determine the state of the database at a point in time),  I understand that using a SERIALIZABLE transaction does not decrease the availability of the database over READ COMMITTED isolation.

One consideration is that there must be enough space allocated for undo segments to allow the database to store enough history to accommodate the implied extra time required to support transaction-level over statement-level read consistency. If there is not enough undo data, there is a risk of error ORA-01555 Snapshot Too Old (although this is still an risk with a READ COMMITTED query).

One point that I am not clear on is if there are any performance implications of serializable isolation. Is there any extra cost associated with, for example, having to go potentially further back in the undo data to return the records?

Finally, as I am talking about a read-only transaction the assumption is that there is no risk of a ORA-08177: Cannot serialize access for this transaction from writing to data which has since been updated.

Is there any extra cost associated with, for example, having to go potentially further back in the undo data to return the records?

Example:

You have a table getting one insert every minute.

If a SERIALIZABLE transaction started at 4:00pm, and queried that table at 4:15pm, then it will have to read at least 15 undo records to return to the state of the table when your transaction started. If there had been no activity on the table, then there would be no additional workload.

Ultimately it would depend on how long the serializable transaction last, and what changes are made to the data it looks at during that time.

I'd add that it is rare to see SERIALIZABLE used. The same mechanisms support DBMS_FLASHBACK with the advantage that you can specify the exact time for which you want to view the data (eg Closing balance at 5pm).


How to pass data and loop in procedure Oracle I have a procedure in an Oracle database which I use to insert users into a table. But now, I need to implement a loop, so that I use that same procedure to insert many users.

I have little knowledge of pl/sql so I don't know which is the best way to do this. Should I pass the data through a varray?

And if so, how can I implement a loop in my procedure?

CREATE OR REPLACE PROCEDURE insert_users(
     p_username     IN xx_users.username%TYPE,
     p_pass         IN xx_users.pass%TYPE,
     p_role         IN VARCHAR2
     )

IS

l_id_role    NUMBER;
l_role       VARCHAR2(200);

l_user       VARCHAR2(200);
l_pass       VARCHAR2(200);

BEGIN

l_user := p_user;
l_pass := p_pass;
l_role := p_role;

SELECT id_role
INTO l_id_role
FROM xx_roles
WHERE role_name = l_role;

INSERT INTO xx_users (id_user,
                        username,
                        pass,
                        id_role
                        ) VALUES (xx_users_id.nextval,
                                 l_user,
                                 l_pass,
                                 l_id_role
                                 );

COMMIT;
END;

I was thinking that maybe I could create another procedure which takes an array and for each element, calls the procedure insert_users but I don't think that is the most effective way to do it.

Suppose you have the xx_roles and xx_users tables, and (instead of an array) a third table new_users, which contains the new users' names and temporary ids, something like :

-- table for roles
create table xx_roles (
  id_role number primary key
, role_name varchar2(32) unique );

begin
  for i in 65 .. 90  loop -- ASCII A-Z
    insert into xx_roles (id_role, role_name)
      values (i, 'role_' || chr(i));
  end loop;
end;
/
-- temporary table containing names of NEW users
-- (everything can be DELETEd once xx_users is populated)
create table new_users(
  tempid number
, username varchar2(32) );

begin
  for i in 97 .. 122 loop -- ASCII a-z
    insert into new_users values (i, 'user_' || chr(i));
  end loop;
end;
/
-- "final" users table: this table will be populated via the procedure
create table xx_users(
  id_user number primary key
, username varchar2(64)
, pass varchar2(128)
, role_name varchar2(32) references xx_roles(role_name)
);

create sequence xxuserid_seq start with 1000 increment by 1;

After executing all the code above, the xx_users table is empty:

SQL> select * from xx_users;
no rows selected

Now, you could use a procedure that looks similar to this, utilising an "cursor for loop" (CAUTION: this will need exception handling, eventually!):

create or replace procedure populate(
  p_startid  new_users.tempid%type
, p_endid    new_users.tempid%type
, p_roleid   xx_roles.id_role%type
)
is
  role xx_roles.role_name%type ;
begin
  select role_name into role from xx_roles where id_role = p_roleid ;
  for rec in (
    select tempid, username from new_users
    where tempid between p_startid and p_endid )
  loop
    insert into xx_users values(
        xxuserid_seq.nextval
      , rec.username
      , standard_hash( rec.tempid )
      , role );
  end loop;
end populate;
/

Testing:

-- populate the xx_users table, using the procedure
-- new users' and temporary ids taken from the new_users table
-- eg temporary ids 100 - 110, id_role 65 -> role_A

begin
  populate(100, 110, 65);
  populate(111, 115, 82);
  populate(120, 122, 90);
end;
/

SQL> select * from xx_users;
ID_USER  USERNAME  PASS                                      ROLE_NAME
1039     user_d    4C6B9619DED0EA1E8A802D85DED8E6A751D27A65  role_A
1040     user_e    15848F7CECB65A6B6C6DD43A7F135B23C3C0B5FC  role_A
1041     user_f    2DA6C40C74BA92AA966BBB3791DD1147BD273305  role_A
1042     user_g    ADD9483D8920D56BF313B60A2F6EB0CDBE0E8785  role_A
1043     user_h    CA7EB2084F5046E3BFA61003174E5042A5B7F3CC  role_A
1044     user_i    48087C2C84F074B09F6611658B6FF1E9535E669F  role_A
1045     user_j    90ACB83C29B5EF24BE0598C59BF5863DD0E96A4B  role_A
1046     user_k    F55EF9C7E3465999FA1FFA6563BF87F4F700F453  role_A
1047     user_l    27E35BDEBFD7EB16D4BE7FD4E2D08D406A94BADD  role_A
1048     user_m    36DEEF8699FDAD1BE9216E91438419D1D3679EEB  role_A
1049     user_n    12EB0BC8F5C5D878120B59481136049D42407BC3  role_A
1050     user_o    F6576DF67EB05A22F36A2841B027E9986D009E7C  role_R
1051     user_p    C9D87CF97D4C3CD5315D487505E28B611123D351  role_R
1052     user_q    600A35A1CC089A880A03296F2ED51700B31CCC93  role_R
1053     user_r    345FA3C5E123079850898D43FD794E4BD5CFC53E  role_R
1054     user_s    65B1606205328C61B7749C18956F7224264A6F5B  role_R
1055     user_x    FB4DF69CC20EF71B716182B0F16A0FA9A6047E00  role_Z
1056     user_y    9DB01E75E374EDAB7AEB5DE2B51571896BB4DB47  role_Z
1057     user_z    58F5BA70289B1C028DB82C153D9DB94B97DAA892  role_Z

Update - As @miracle173 has pointed out, it may be a better idea to use a single INSERT in this case. Example:

insert into xx_users
select
  xxuserid_seq.nextval
, U.username
, standard_hash( U.tempid )
, R.role_name
from xx_roles R, new_users U
where ( ( U.tempid between 100 and 110 ) and R.id_role = 65 )
   or ( ( U.tempid between 111 and 115 ) and R.id_role = 82 )
   or ( ( U.tempid between 120 and 122 ) and R.id_role = 90 ) ;


query SUM function issue/ grouping issue I am running into an issue where using the Sum() function is not working as it should be. I am running this query.

SELECT        tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
FROM          AdjustUserRoleRequestStatusLookup
CROSS JOIN    tServiceJobCodes
INNER JOIN    tServiceHours
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode
INNER JOIN    tServiceReports
ON            tServiceHours.ReportNo = tServiceReports.ReportNo
INNER JOIN    tServiceReps
ON            tservicereports.employee = tservicereps.repid
INNER JOIN    tRegions
ON            tServiceReports.Region = tRegions.RegionCode
where         Date between '2017-10-03' and '2017-10-03'
and           EmployeeNumber = '10245'
GROUP BY      tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
order by      EmployeeNumber asc

Gives the below output

EmployeeNumber  JobCode                 Date           Hours
10245             82         2017-10-03 00:00:00.000    0.50
10245             82         2017-10-03 00:00:00.000    1.50
10245            215         2017-10-03 00:00:00.000    2.50
10245            215         2017-10-03 00:00:00.000    3.00

When I add the sum() to hours I get weird results back.

SELECT        tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              sum(tServiceHours.Hours) as Hourz
FROM          AdjustUserRoleRequestStatusLookup
CROSS JOIN    tServiceJobCodes
INNER JOIN    tServiceHours
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode
INNER JOIN    tServiceReports
ON            tServiceHours.ReportNo = tServiceReports.ReportNo
INNER JOIN    tServiceReps
ON            tservicereports.employee = tservicereps.repid
INNER JOIN    tRegions
ON            tServiceReports.Region = tRegions.RegionCode
where         Date between '2017-10-03' and '2017-10-03'
and           EmployeeNumber = '10245'
GROUP BY      tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
order by      EmployeeNumber asc

I get the below faulty output

EmployeeNumber  JobCode            Date          Hourz
10245             82    2017-10-03 00:00:00.000  17.50
10245            215    2017-10-03 00:00:00.000  27.50

from what I gather, the grouping is not working properly, which is probably what is causing the Sum() to go haywire. Any help is appreciated.

Use your main query as a subquery:

SELECT EmployeeNumber,
       JobCode,
       [Date],
       SUM(Hours) as [Hours]
FROM
(
SELECT        tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
FROM          AdjustUserRoleRequestStatusLookup
CROSS JOIN    tServiceJobCodes
INNER JOIN    tServiceHours
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode
INNER JOIN    tServiceReports
ON            tServiceHours.ReportNo = tServiceReports.ReportNo
INNER JOIN    tServiceReps
ON            tservicereports.employee = tservicereps.repid
INNER JOIN    tRegions
ON            tServiceReports.Region = tRegions.RegionCode
where         Date between '2017-10-03' and '2017-10-03'
and           EmployeeNumber = '10245'
GROUP BY      tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
) ctHours
GROUP BY EmployeeNumber, JobCode, [Date];


What is an effective way of labeling columns in a database? I used to label columns in my databases like this:

user_id
user_name
user_password_hash

To avoid conflicts when joining two tables, but then I learnt some more on how to alias tables, and I stopped doing this.

What is an effective way of labeling columns in a database? Why?

In your case, the prefix user is redundant. We (the devs in charge) know that this is the table user, so why add user_ prefix in front of every field?

What I would suggest to you is to do it with a more natural approach.

What are the characteristics of a Person: Last Name, First Name, Birthdate, Nationality, etc...

What are the characteristics of a Car: Model, Year, Color, Energy, etc...

Your column should be named as natural as possible, it would make the schema more clear for everybody, for you and the ones coming after you. This is also called the Maintenance phase, and anything you can do to make the maintenance easier is usually worth the effort.


How can I hide a column using a Select statement? declare @bid varchar(4) = '1007'

select sr.release_date,
    sr.customer_id,
    sr.customer_name,
    sr.item_id,
    sr.item_desc,
    sr.order_no,
    sr.qty_on_hand,
    sr.release_qty,
    sr.qty_after_released,
    sr.extended_sell,
    sr.enough,
    sr.route_description

from sri_schedule_releases_view as sr

where
    ((case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'John')
    end = @bid) or
    (case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Jane')
    end = @bid) or
    (case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Doe')
    end = @bid) or
    (sr.contact_id = @bid))

order by sr.item_id asc

This query works fine. But I want to display sr.route_description only who has the sr.route_code PM 01, PM 03 and PM 05.

Are there any possibilities to do this?

You just need to add a case statement to your select list:

declare @bid varchar(4) = '1007'

select sr.release_date,
    sr.customer_id,
    sr.customer_name,
    sr.item_id,
    sr.item_desc,
    sr.order_no,
    sr.qty_on_hand,
    sr.release_qty,
    sr.qty_after_released,
    sr.extended_sell,
    sr.enough,
    case when sr.route_code in ('pm 01','pm 03','pm 05') then sr.route_description else '' end as route_description
from sri_schedule_releases_view as sr
where
    ((case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'John')
    end = @bid) or
    (case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Jane')
    end = @bid) or
    (case
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Doe')
    end = @bid) or
    (sr.contact_id = @bid))
order by sr.item_id asc


What can I do with the new json field? PostgreSQL 9.2 introduced the json field type.  Why and when should I use it?  What benefits does it have over a text field?

I thought there were new query options available, however I haven't seen any.  Am I missing something?

Postgres 9.2

The benefit of the new feature is two-fold. Columns of type json verify the validity of its content so that what's in the column is automatically valid JSON and you get an error if you try to write anything else to it.
And you have basic functions to create valid JSON on the fly from rows or arrays - which is a very common use-case.

I quote Andrew Dunstan on the pgsql-hackers list:

  At some stage there will possibly be some json-processing (as opposed
  to json-producing) functions, but not in 9.2.

I used that quote before under this related question on SO.

Postgres 9.3

.. finally brings a number of functions and operators. Check out the manual page for JSON functions.

Related answer on SO:

How to query for a specific element of json field.
How to support that with an index.

@Will put up blog post. See comment below.

Postgres 9.4

Be sure to check out the new jsonb type with a host of new functionality.

Above all, the decomposed, binary storage allows for smaller storage on disk and an equality operator for jsonb (unlike json), which makes a number of additional operations possible (like DISTINCT or a UNIQUE index).

Yet more functions have been added for both json and jsonb. json_to_record(), json_to_recordset() etc. More in the release notes.


Index on JSON field with dynamic keys I'm on PG 9.5 and I have a table Visitors(id, data::json)

Example:

Visitor(id: 1, data: {name: 'Jack', age: 33, is_user: true })

I'd like to perform queries like

Give me all visitors named Jack and age > 25
Give me all visitors who are users, but where name is unspecified
(key not in json)

The keys inside the data column user-specified and as such are dynamic.

Which index makes the most sense in this situation?

Alter your JSON column to be jsonb and try a gin index on the data field like:

create index ginner on Visitor using gin(data);

They can be slow to create and big but will allow arbitrary queries. Here's a demonstration:

https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/


Will Derby, H2, or SQLite give faster load time and/or smaller file size than HSQL? I have some flat files with the following columns; 3 integers, 3 reals, and 1 varchar(20). For querying I need an index that contains both 1 of the integer columns and the varchar column.  Each file is around 1.8GB in size with around 38 million rows.

Currently I am using a HSQL(Standalone) database to load a file for processing; one database per file. It is very slow to load (120+ min) the file and results in a 4.7GB database file when the database is created with the following options.

"Properties" -> {
  "check_props" -> "true",
  "shutdown" -> "true",
  "hsqldb.default_table_type" -> "cached",
  "sql.syntax_mss" -> "true",
  "hsqldb.log_data" -> "false",
  "hsqldb.inc_backup" -> "false"
  }

The file read in batches of 100k records.  The read is very fast (almost instant) so I do not think it is the read that is slowing things down.  It also takes a very long time to close the connection to the database.

I have the option to use Derby, H2, or SQLite.  Will any of these result in faster load time and/or smaller database file size in this scenario?  If so what are the connection string options that should be used to achieve this?  Alternatively, are there different connection string options I can use with HSQL(Standalone) that will reduce the load time and/or database file size?

Driver information added.

JDBCDriver[
  "Name" -> "HSQL(Standalone)",
  "Driver" -> "org.hsqldb.jdbcDriver",
  "Protocol" -> "jdbc:hsqldb:file:",
  "Version" -> 3.1,
  "Description" -> "HSQL Database Engine (In-Process Mode) - Version 2.3.3 -  This ...",
  "Location" -> "C:\... "]

Driver information for the other options available to me.

Derby

JDBCDriver[
  "Name" -> "Derby(Embedded)",
  "Driver" -> "org.apache.derby.jdbc.EmbeddedDriver",
  "Protocol" -> "jdbc:derby:",
  "Version" -> 3.1,
  "Description" -> "Derby Database Engine (Embedded Mode) - Version 10.12.1.1 - This...",
  "Location" -> "C:\... "]

H2

JDBCDriver[
  "Name" -> "H2(Embedded)",
  "Driver" -> "org.h2.Driver",
  "Protocol" -> "jdbc:h2:",
  "Version" -> 3.1,
  "Description" -> "H2 Database Engine (Embedded Mode) - Version 1.3.176 - This...",
  "Location" -> "C:\... "]

SQLite

JDBCDriver[
  "Name" -> "SQLite",
  "Driver" -> "org.sqlite.JDBC",
  "Protocol" -> "jdbc:sqlite:",
  "Version" -> 3.1,
  "Description" -> "SQLite using Zentus-derived JDBC Driver - Version 3.8.11.2",
  "Location" -> "C:\..."]

Additional variants include the below. However, I need it all to run on the client's computer. I believe this excludes server and webserver modes.

{"Derby(Embedded)", "Derby(Server)", "H2(Embedded)", "H2(Memory)",
 "H2(Server)", "HSQL(Memory)", "HSQL(Server)", "HSQL(Standalone)",
 "SQLite", "SQLite(Memory)"}

You need a larger Java memory allocation and a larger HSQLDB memory cache size for faster loading of data into large tables.

Add hsqldb.cache_rows=4000000 and hsqldb.cache_size=1000000 to the startup configuration for a new database.

http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#dpc_db_file_mem


Install standalone instance on cluster server with SQL Server I've a cluster instance in 2016 SP1 version on Windows 2016 server. All work fine.
I want to install a new instance in standalone mode (version 2008) on one server of this cluster. The data disk of this new instance will be localdisk.

I want to know if the standalone installation will work without broke or impact cluster instance.

It is a hell of a bad idea, but it might work. I have done something similar in the past because it was the only possible solution and I can confirm that it works.

The only thing to be careful about is the versions: you will probably get warnings about installing sql server 2008 on Windows 2016. Also, keep in mind that it's not supported (which doesn't mean it won't work).

However, this is something I would never do in production: do yourself a favour and find a different machine for that SQL Server 2008 standalone instance: at least, you won't get shared features incompatibilities and the like.


SQL SERVER: Find and Drop Invalid View I have a SQL Server 2012 database which contains a number of views which have become invalid. The underlying tables the views referenced were migrated to another schema for archiving. What's the best way to find the now invalid views and drop them

You can use sp_refreshview to do it.  I've put this little script together that will select all views in a database, run sp_refreshview against it and tell you which ones had a problem.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refreshview-transact-sql?view=sql-server-2017

DECLARE @Objects TABLE
    (
    OID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , FQN NVARCHAR(256) NOT NULL
    , IsIssue BIT NOT NULL DEFAULT (CONVERT(BIT, 0))
    )

DECLARE @SQLCommand NVARCHAR(4000)
DECLARE @VName NVARCHAR(256)
DECLARE @OID INT

INSERT INTO @Objects
    (FQN)
SELECT QUOTENAME(S.name) + '.' + QUOTENAME(V.name)
FROM sys.views AS V
    INNER JOIN sys.schemas AS S ON S.Schema_id = V.schema_id

DECLARE curLoop CURSOR LOCAL STATIC FORWARD_ONLY
FOR SELECT OID, FQN
    FROM @Objects AS O

OPEN curLoop

FETCH NEXT FROM curLoop
INTO @OID, @VName

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQLCommand = N'EXEC sp_refreshview N' + QUOTENAME(@VName, '''')

    BEGIN TRY

        EXEC sp_executesql @SQLCommand

    END TRY
    BEGIN CATCH

        UPDATE @Objects
        SET IsIssue = 1
        WHERE OID = @OID

    END CATCH

    FETCH NEXT FROM curLoop
    INTO @OID, @VName

END

CLOSE curLoop
DEALLOCATE curLoop

SELECT OID, FQN, IsIssue
FROM @Objects
WHERE IsIssue = 1


Dont know why you have a subselect at all, that query can be reduced to this:

SELECT * FROM shift AS a
LEFT JOIN shift AS b ON a.replaced_id = b.id
LEFT JOIN schedule AS s ON a.schedule_id = s.id AND s.date >= '2015-12-05'
left JOIN shift_info AS si ON s.shift = si.initial AND si.date <= '2015-12-05'
WHERE a.user_id = 58;

Generally you want join conditions applied in the ON clause, particularly when they affect the actual join, as is the case with LEFT JOIN. I think this fulfills what you are asking as well (since the schedule and shift_info start at the same start time).


what does I/O affinity automatically do when cpu affinity is manually set I have a 2016 Enterprise db server with 4 processors and 24 cores which is CAL licensed. So only the first 20 cores are used. Now I would like to set affinity so SQL Server will not use the first 20 cores (6 of the first 3 processors and 2 of the 4th processor), but will use 5 cores per processor. What do I do with I/O affinity? Leave it on 'automatically'? When both CPU affinity and I/O affinity are not set to 'automatically', you have to choose between CPU and I/O. When I manually select 20 of 24 cores for CPU affinity, will the 'automatically' setting for I/O affinity mean that only the remaining 4 cores will be used for I/O?

Been a long time since I have played around with this setting.

The point of setting up CPU affinity is so you can manually load balance the CPUs and make sure certain task always have resources and do not compete with each other system loads.

If the affinity is set to automatic MSSQL CPU scheduler will pick a CPU for the load.  This has the advantage that if a CPU becomes loaded and other CPUs are unloaded it can switch to an unloaded CPU

But if the affinity is set MSSQL scheduler will not switch the load to another process unless its in the affinity list.

IO and CPU both have an affinity list and can be controlled independently.  Setting one to Automatic and the other to a specific list means the MSSQL scheduler can move one around automatically

Key point do not set both OS affinity and MSSQL it can cause interesting problems

FROM the help files

  When specifying the affinity I/O mask option, you must use it in connection
  with the affinity mask configuration option. Do not enable the same CPU in
  both the affinity mask switch and the affinity I/O mask option. The bits
  corresponding to each CPU should be in one of these three states:

  0 in both the affinity mask option and the affinity I/O mask option.

  1 in the affinity mask option and 0 in the affinity I/O mask option.

  0 in the affinity mask option and 1 in the affinity I/O mask option.

It also suggested by MS and many others not to configure this unless there is a clear need to exclude CPU's and/or manually load balance resources.


CRM Address table and it relation Hi I want to ask about I have this DBD for my CRM I am trying to have the addresss in one table(customer  and Employee Address) and both customerID  and Employee ID are guid I have in address table FK is associate_ID to store the id (customer.ID or Employee.ID) just want to ask if is ok to do it like that ?

A customer is a role played by a Party, which is an Individual or Organization (or Automated Agent).

Same with employee.

A party can have zero or more addresses, parties can share the same address, and addresses can exist without parties.

Your data model should look like this:

PARTIES -< ROLES

PARTIES -< PARTY_ADDRESSES >- ADDRESSES

create table parties (
  party_id bigint primary key,
  type text not null, --this should point to a party_type table in real life
  name text not null,
  ...party-type-specific into
);

create table party_roles (
  party_role_id bigint primary key,
  type text not null, --this should point to a party_role_type table in real life
  party_id bigint not null references parties(party_id),
  ...role-type-specific info...
);

create table addresses (
  address_id bigint primary key,
  type text not null, --this should point to a address_type table in real life
  ...address-type-specific info...
);

create table party_addresses (
  party_id bigint references parties(party_id),
  address_id bigint references addresses(address_id),
  role text not null, --this should point to a party_address_role table in real life

  primary key (party_id, address_id, role_id)
);

--demo data:

insert into parties values
(1, 'INDIVIDUAL', 'Neil'),
(2, 'ORGANIZATION', 'Acme, Inc');

insert into party_roles values
(1, 'EMPLOYEE', 1),
(2, 'CUSTOMER', 2);

insert into addresses values
(1, 'MAILING', '123 Main Street'),
(2, 'MAILING', '456 Bay Street');

insert into party_addresses values
(1, 1, 'HOME'),
(2, 2, 'HEADQUARTERS');


Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios From what I can tell, there are three possible ways of backing up your SQL Server database

Full backup
Differential backup
Log Shipping

What are the pros and cons of each strategy and in what situations should they be employed?

Log shipping is not a backup scenario. It's a semi high availabiliy scenario.
For backups there's full, differential and transaction log backups.
They should all be used together. Your SLA dictates how you use them.
Most typical scenarios are full backup say at midnight, diff backup at noon and transaction log backups every 30 or 15 minutes.

And remember: you don't have a valid backup until you restore from it to test if it's ok.


On Elasticsearch, what is the required response for a directory URL of a HTTP URL repository? The official document of Elasticsearch says we can use URL as a setting of Read-only repository. The document also says several protocols, including HTTP, is supported for the URL. (refer: https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-snapshots.html#_read_only_url_repository )

I understand that the snapshots are stored in the repository as a directory structure which contains Lucene index and some meta data.

Given above conditions, I think GET request to a directory URL of a HTTP URL repository need to return a list of files/directories inside, in a certain format, for the snapshot API to restore a snapshot. At least, I thought some sort of ls operation on the directory containing Lucene indices were needed.

Questions:

Is my understanding correct?
If so, what is the required format for the directory URL? (What is the required response for HTTP request to the directory which makes snapshot API's restore work?)

I also asked this question in Japanese SO and got an answer.

The snapshot, when stored, generates some files which stores the names to the data files. Namely: index, metadata-THESNAPSHOTNAME, snapshot-THESNAPSHOTNAME (note snapshot-THESNAPSHOTNAME files are generated in several directories) . From these files the relative paths of the files are generated; thus no need for ls-ing to the directory URL.

So the answer to my questions would be:

No, my assumption was wrong
No need for any response to the directory URL: URLs are constructed by following the specific file's content.


Extracting data from extremely large csv files I have a 40gb csv file with over 60 million rows for data analysis. Each row has a unique identifier (some numbers). For example, the first row's unique identifier will repeat approximately 150,000 rows later.

I would like to have a method to run through the entire file, and extract rows with the same identifier and write them into new csv files. Is there a good, automated way to do that? Please note that the file is very large and excel has problems opening it.

Powershell to the rescue!

A CSV file is just a text file structured in a certain way. It doesn't have to open up in Excel, that's just what your default settings are configured to do at the moment. Use Powershell to protect yourself from nasty conversions that Excel does under the hood.

Assuming my.csv is saved in your c:\temp folder and looks like this when you open it in Notepad...

uuid,foo,bar
00000000-0000-0000-0000-000000000001,bing,bong
00000000-0000-0000-0000-000000000002,bork,björk
... lots more rows
00000000-0000-0000-0000-000000000001,blat,splunge

...then you can extract the duplicate values with the following script.

Set-Location C:\temp
$myCsv = Import-Csv ./my.csv
$dupes = ($myCsv | Group-Object uuid | Where-Object Count -gt 1).Name

$myDupes = $myCsv | Where-Object uuid -in $dupes
$myOkays = $myCsv | Where-Object uuid -notin $dupes

$myDupes | Export-Csv myDupes.csv
$myOkays | Export-Csv myOkays.csv

Hey presto! You've now got one

Your original file for comparison
A file containing all the duplicates
A file containing just the uniques records

You may want to do some more work to determine which of the duplicate records to keep and possibly append back into your "good" file, but the exact details of that are probably best left for another question. Possibly over on StackOverflow if you're gonna keep flexing those Powershell skillz.


Why is sql server waiting for acknowledgment? I have an application that often request a big set of rows [about 20K] from a sql view, in the mean time other applications can't update the table from where the data comes from, the update query's gets blocked by the select from the other application.
The wait code is "ASYNC_NETWORK_IO" which I found to be described as exactly whats happening by me, I quote:

This is usually where SQL Server is waiting for a client to finish
consuming data.   It could be that the client has asked for a very
large amount of data or just that it’s consuming it really slowly
because of poor programming.
Clients often process one row at a time, instead of caching the data on the client and
acknowledging to SQL Server immediately.

My question is why is sql server waiting for the client to finish processing that select job, before allowing updates to occur on the table? is that a setting or a configuration that I could change?

SQL Server by default adheres to ACID database rules.  The A in ACID stands for atomic, meaning that the whole transaction happens, or none of it does.  The I in ACID stands for isolation, meaning that transactions are isolated from one another, so either all the results from one transaction are visible to another transaction, or none of them are.  This is accomplished in SQL Server by using locks for both reads and writes (at least by default).  Reads (SELECTs) obtain a read lock, which prevents any write (UPDATE or INSERT) operations on those items (or pages, or the whole table depending on what SQL's black magic lock escalation algorithm decides on).  In order to preserve Isolation, read locks for queries must be held until all results have been sent or buffered.  This prevents the writes from obtaining their locks (or rather causes them to wait to obtain them), unless the writes are otherwise isolated (see other answer about snapshot isolation).  If not, some of the results from a subsequent write could end up in the results of the query that occurred before the write.

There are several solutions for this, each with trade-offs:

Buffer results on the client side (read all rows immediately as quickly as possible and do further processing later).  This may require lots of client-side memory if there are lots of results, and will still block updates until the client is done buffering (which is why it must be completely buffered before any subsequent processing).
Buffer results on the server-side.  You can do this by selecting the data into a temp table and then returning the temp table instead of the original query.  This will require server-side memory and/or disk and could therefore interfere with other server-side operations.
Use WITH (NOLOCK) on the SELECT of the read operation.  This tells SQL Server to ignore isolation for this read, which means that you could get the original version of a row at the beginning of the results and a modified version of another row later in the results when both rows were modified by the same transaction and the versions returned were never in the database at the same time (ie. the rows were modified during the query, after the first row was returned but before the second row was).  This answer has the least performance cost, but produces non-ACID results by breaking Isolation and/or Atomicity (depending on how you look at it).
Use snapshot isolation (see other answer--I'm not an expert with snapshot isolation).  Note that I've had significant performance issues with snapshot isolation, and it will require plenty of server-side resources as well.

There may be other solutions as well, but these are the most common.