Identity column jumping from 6 to 1000+ in SQL Server 2012

I was surprised to find out that overnight, a unique identifier had jumped from 6 to 1012 and upon investigation, it looks like SQL Server, when restarted, messes up the seed of the unique identifier to a new value (this is why you should never use ints for primary keys!)
Don’t expect identity values to be dense. For example, a rollback when inserting a row will cause an identity value to be “consumed” leaving a gap in the numbers.

I wrote a small SP which can be used to re-seeed the identity of a table in a all DBs on a server:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetOrderIdentityAfterRestart]
AS
BEGIN
begin TRAN
SET NOCOUNT ON;

DECLARE @db_name varchar(280), @SQL nvarchar(500);

PRINT '-------- DB Reseet Report --------';

DECLARE db_name_cursor CURSOR FOR
select DatabaseName + '.dbo.table' from tblClients WHERE ACTIVE = 1

OPEN db_name_cursor

FETCH NEXT FROM db_name_cursor
INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
print '----- Resetting count: ' + @db_name

-- Declare an inner cursor based
-- on vendor_id from the outer cursor.

SET @SQL = '
declare @id int = 0
SELECT @id = MAX(ID) FROM ' + @db_name + '

DBCC CHECKIDENT (''' + @db_name + ''', reseed, @id)
'

exec sp_executeSQL @SQL

FETCH NEXT FROM db_name_cursor
INTO @db_name
END
CLOSE db_name_cursor;
DEALLOCATE db_name_cursor;

Commit

END

Then add it in to Start up by using following syntax.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';

Restore all the backup files from inside a folder on SQL Server – TSQL Script

First, you will need to enable xp_cmdshell

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

Next, you will need to change the path to the directories:

USE Master; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 

-- 2 - Initialize variables 
SET @backupPath = 'F:\baks\' 

-- 3 - get list of files 
SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

SELECT 'RESTORE DATABASE [' + LEFT(backupFile, CHARINDEX('_', backupFile, 12)-1) + 
'] FROM DISK = '''  
       + @backupPath + backupFile + ''' WITH RECOVERY, REPLACE
	 GO'  from @fileList

Copy the generated script into a new window and voila! Your script is done. Just press F5 to run it.
Don’t forget to turn off xp_cmdshell when done.

How to order a result set in SQL based on the numeric part of the string value

There are occasions where you would like to search in a varchar column and order the results based on the numeric part of the field.

Imagine the following table:

ABC
ABC1
ABC2
ABC3 and so on...

ABC11
ABC12
ABC13 and so on..

ABC20
ABC21
ABC22 and so on..

So basically what I have is any string value (not always ABC, any string value) that can either be followed by the number or it may just be a string without the number.

When you run

select * from table order by my column asc

you get following results:

ABC
ABC1
ABC11
ABC12
ABC13
ABC2
ABC20
ABC21
ABC22
ABC3
ABC31
ABC32

In this scenario, you would need the results sorted by the numeric value and not alphabetically.
Eg:

ABC
ABC1
ABC2
ABC3
ABC11
ABC12
ABC13
ABC20
ABC21
ABC22
ABC31
ABC32

How can this be accomplished?

Continue reading “How to order a result set in SQL based on the numeric part of the string value”

How to re-order rows in a table with incorrect ordering in SQL

Capture

Imagine you have this scenario – the order of a table which relies on its order has been muddled up by some bad code and you wish to sort it out with the least effort possible.

You can write a quick SQL script which will reset the order to the right set (seen on the right).

To find out what your order should be, include the ROW_NUMBER() command as below:

select *
,ROW_NUMBER() OVER(ORDER BY Orders ASC) AS Row
from DM_ReportColumns where [conditions]

Then you can run an update based on your primary keys (in this scenario, I have ReportID, Username and ColumnID as keys.

UPDATE DM_ReportColumns SET Orders = x.Row 
from (
select *
,ROW_NUMBER() OVER(ORDER BY Orders ASC) AS Row
 from DM_ReportColumns where ReportID  = 'A01' and Username = 'user1'
) as x
WHERE x.ReportID = DM_ReportColumns.ReportID 
AND x.Username = DM_ReportColumns.Username 
AND x.ColumnID = DM_ReportColumns.ColumnID

After

Capture2

Script to recompile all stored procedures – T-SQL

In some cases, to improve performance, you might choose to re-compile all database objects and while you can use DBCC FREEPROCCACHE to clear the cache and force re-compilation, you can also use the following function to trigger a global re-compile.

This is especially useful if you are struggling to copy databases and old code error does not let you skip steps. Continue reading “Script to recompile all stored procedures – T-SQL”

How to script all indexes from a table (or an entire database)

SELECT ' CREATE ' +  
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +   
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX [' +    
    I.name  + '] ON '  +   
    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +  
    KeyColumns + ' )  ' +  
    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +  
    ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( ' +  
    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +  
    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +  
    -- default value  
    'SORT_IN_TEMPDB = OFF '  + ','  +  
    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +  
    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +  
    -- default value   
    --' DROP_EXISTING = ON '  + ','  +  
    -- default value   
    ' ONLINE = OFF '  + ','  +  
   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +  
   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +  
   DS.name + ' ] '  [CreateIndexScript]  
FROM sys.indexes I    
 JOIN sys.tables T ON T.Object_id = I.Object_id     
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid    
 JOIN (SELECT * FROM (   
    SELECT IC2.object_id , IC2.index_id ,   
        STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END 
    FROM sys.index_columns IC1   
    JOIN Sys.columns C    
       ON C.object_id = IC1.object_id    
       AND C.column_id = IC1.column_id    
       AND IC1.is_included_column = 0   
    WHERE IC1.object_id = IC2.object_id    
       AND IC1.index_id = IC2.index_id    
    GROUP BY IC1.object_id,C.name,index_id   
    ORDER BY MAX(IC1.key_ordinal)   
       FOR XML PATH('')), 1, 2, '') KeyColumns    
    FROM sys.index_columns IC2    
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables   
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4    
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id   
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id    
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id    
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id    
 LEFT JOIN (SELECT * FROM (    
    SELECT IC2.object_id , IC2.index_id ,    
        STUFF((SELECT ' , [' + C.name  + ']'
    FROM sys.index_columns IC1    
    JOIN Sys.columns C     
       ON C.object_id = IC1.object_id     
       AND C.column_id = IC1.column_id     
       AND IC1.is_included_column = 1    
    WHERE IC1.object_id = IC2.object_id     
       AND IC1.index_id = IC2.index_id     
    GROUP BY IC1.object_id,C.name,index_id    
       FOR XML PATH('')), 1, 2, '') IncludedColumns     
   FROM sys.index_columns IC2     
   WHERE IC2.Object_id = object_id('tblERPInvoiceData') --Comment for all tables    
   GROUP BY IC2.object_id ,IC2.index_id) tmp1    
   WHERE IncludedColumns IS NOT NULL ) tmp2     
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id    
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0  
AND I.Object_id = object_id('tblERPInvoiceData') --Comment for all tables  
--AND I.name = 'IX_Address_PostalCode' --comment for all indexes

Continue reading “How to script all indexes from a table (or an entire database)”