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:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[ResetOrderIdentityAfterRestart]
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
FETCH NEXT FROM db_name_cursor
WHILE @@FETCH_STATUS = 0
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
Then add it in to Start up by using following syntax.
EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';
First, you will need to enable xp_cmdshell
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
-- To update the currently configured value for this feature.
Next, you will need to change the path to the directories:
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.
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:
ABC3 and so on...
ABC13 and so on..
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:
In this scenario, you would need the results sorted by the numeric value and not alphabetically.
How can this be accomplished?
Continue reading “How to order a result set in SQL based on the numeric part of the string value”
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”
When you are manipulating large quantities of data, you might have some records containing email addresses which slip through as invalid.
Following function checks these conditions:
No embedded spaces
‘@’ can’t be the first character of an email address
‘.’ can’t be the last character of an email address
There must be a ‘.’ somewhere after ‘@’
the ‘@’ sign is allowed
Domain name should end with at least 2 character extension
can’t have patterns like ‘.@’ and ‘..’
--print dbo.fnc_isValidEmail('Mi email@example.com')
CREATE FUNCTION fnc_IsValidEmail
DECLARE @Valid bit
SET @Valid = 0
SET @Valid = 1
CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0
AND LEFT(LTRIM(@email_address),1) <> '@'
AND RIGHT(RTRIM(@email_address),1) <> '.'
AND CHARINDEX('.',@email_address ,CHARINDEX('@',@email_address)) - CHARINDEX('@',@email_address ) > 1
AND LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email_address)))) >= 3
AND (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0)
SET @Valid = 1
How to call:
SELECT Email, dbo.fnc_IsValidEmail(Email) as Valid
If you have been using this:
SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col))
to get your zeroes removed from a string, you might be interested in a variation that only removes the leading zeroes. The T-SQL Function has been created to be called from either Stored Procedure code or Triggers.
--print dbo.ufn_TrimLeadingZeros ('0100 ATN652')
ALTER FUNCTION [dbo].[ufn_TrimLeadingZeros] ( @Input VARCHAR(50) )
--RETURN REPLACE(LTRIM(REPLACE(@Input, '0', ' ')), ' ', '0')
RETURN SUBSTRING(@Input, PATINDEX('%[^0]%', @Input+'.'), LEN(@Input))
Sometimes you will be asked to filter information and what better way of doing this than using “Like” filters in SQL Server.
Flexible and easy to use, the two types of filter (The percent sign (%) and The underscore (_)) can be used as placeholders for one or more characters.
Continue reading “How to search between two "Like" fields in SQL Server 2012”