Reset Identity Seed

Sometimes the need arises to reseed the identity row within an MS-SQL table. One might want to do this because records have been deleted at the end of the table, or a delete * instead of a truncate has been performed on the table (which doesn’t reseed the identity row back to 0).

 

Within SQL Server, there is a built in function called DBCC CHECKIDENT, and it allows you to reseed the identity row to start at a specified number. Lets say that a table (lets call it TableA) has rows in it ranging from row 1 through 100, and for some reason rows 90 through 100 need to be deleted. Clearance is received from the auditors that these records can be deleted, and voila, a few minutes later 10 rows have been removed from the table. As time goes by an application inserts new rows into the table, however when the table is inspected you notice that the new rows have been inserted with identity rows starting at 101 upwards, and that the identity didn’t continue from 90 as expected. Because of the way MS-SQL handles Row IDs, it remembers the last Identity Row number per table, and just continues from that number forward when new data is inserted into that particular table, regardless if some or all of the data has been deleted from it.

 

To correct this, you need to determine the row id that you would like new rows to start at (in this case it’s 90), subtract one from it (making it 89), and then you need to run the following code:

 

DBCC CHECKIDENT (TableA, reseed, 89).

 

This will tell SQL that the last identity record for the specified table is 89, and so SQL will add one (1) to this value when inserting new data into the table. Of course the identity rows don’t need to start off exactly from the previous row. If you want the identity rows to start at 150, then the same code as above can be run, however the 89 would need to be replaced by 149.

 

There are a few exceptions to the use of the code, so for more information on these and other uses please visit the MSDN website. Thanks to Pinal Dave for the reference to this piece of code.

 

God bless.

Colin

Retrieve SQL Run Time Information

Within our company, one of the requirements we have is to log statistics about what happens within stored procedures / SSIS packages while they are executing for audit purposes. Examples of the required statistics are:

  • Process Name *1
  • Process GUID ID
  • Process Start Time
  • Process End Time
  • Process Duration
  • Source and Destination table names
  • Rows written successfully *2
  • Rows rejected *3
  • Row ID for record inserted into Audit table *4
Within SSIS retrieving the required audit values are easy enough as there are toolbox items such as the Row Count component one can use as well as other system variables that come stock with SSIS. Within a stored procedure a little more work is required. I particularly wanted to retrieve the items above marked with an asterisk from the executing stored procedure.

Process Name (*1)

To retrieve the name of the running stored procedure, one needs to use the built in function “OBJECT_NAME”. The exact syntax to use is:

 

SELECT OBJECT_NAME(@@PROCID).

The output of this query can be written as an output from the procedure, or written to a variable and used later on. Thanks to “Hunchback” for providing the answer on the MSDN forum.

Rows Written and Rejected (*2 & *3)

In order to retrieve the rows affected in particular table (whether by inserts, updates or deletes) one needs to use the T-SQL function @@ROWCOUNT directly after the statement executed that affected a table. So for instance, if I have three statements, one an insert, one an update, and one a delete (all on the same table), and I only want to log the rows that were inserted, then I would use the @@ROWCOUNT T-SQL function as follows:
DECLARE @RowCount bigint;
-- INSERT
INSERT INTO TableA (Column01, Column02, Column03)
VALUES ('10', 'Antibacterial Soap', '7.99');
SET @@RowCount = (SELECT @@ROWCOUNT);

-- UPDATE
UPDATE TableA
   SET TableA.Column02 = 'Sunlight Liquid'
WHERE TableA.Column02 = 'Sonlight Liquid'

DELETE FROM Table A
WHERE Column02 IS NULL
  AND Column03 < 0;
You will notice that I didn’t use the @@ROWCOUNT function after the Update or Delete statements, because every time a table is affected by a T-SQL operation the value stored by SQL in the @@ROWCOUNT variable is altered.

Row ID

We have created an Audit table that stores the run time information for our SSIS packages and SQL Stored Procedures. The way we use this table is that each process that runs inserts a record into the Audit table as its first step, runs the rest of the code, and then updates the Audit record with the correct run time information after the process has completed successfully. The same process can be run a few times (such as when it is run within a loop or a few times in sequence).
Because the same process can be run, it is important to update the correct values in the audit table. The way to do this is to retrieve the Row ID from the Audit Table (if it has one) after a record has been written to it by a particular process, and then to store this ID in a variable for later use. The safest way to retrieve this information is to use the T-SQL function Scope_Identity().
An example of it’s use can be seen below:
DECLARE @RowID BIGINT
CREATE TABLE Audit
(RowID    BIGINT IDENTITY(1,1)
,Column01 INT
,Column02 NVARCHAR(100)
,Column03 MONEY
)
INSERT INTO TableA (Column01, Column02, Column03)
VALUES ('10', 'Antibacterial Soap', '7.99');
SET @ETLPerformanceImportID = CAST(Scope_Identity() AS bigint)
NOTE: I cast the results from the function to a BIGINT because the RowID in the audit table was set as a BIGINT.

Conclusion

I hope this article assists you in getting a bit more out of your procedures than you used to.
God bless.
Colin

MS-SQL – Write Stored Procedure Results to Table

I browsed the web looking for a way to write the results from one of my stored procedures to a table. There were quite a few ways to do it, however this one worked best for me:

IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL
BEGIN
    DROP TABLE #Temp
END
CREATE TABLE #Temp
          (Date_Flag          [varchar](12)
          ,Account_Code       [varchar](20)
          ,Calendar_Month     [datetime]
          ,Last_Receipt_Month [date]
          ,Last_Receipt_Date  [date]
          ,Last_Receipt_AMT   [money])
INSERT INTO #Temp
EXEC        [Database].[dbo].[Procedure];
SELECT * FROM #Temp
GO

I hope this helps.

God bless,
Colin