May 6, 2014 at 2:00 pm
Greetings, all.
I am using CHECKIDENT(RESEED) in a Visual Studio 2013 script that clears numerous test data tables & repopulates them afresh, whenever we deploy a revised build of the database project. The proper syntax (as per Microsoft) to restart the key-count at #1, is RESEED,1 for truncated tables & RESEED,0 for tables from which rows were deleted (due to foreign keys) rather than truncated.
When the TFSBuild process is run on my PC (SQL Server 2012), all behaves as expected. The same is true when another developer runs the build to his PC. However, when we run the (very same) build process to our Dev (network) server, the RESEED,0 populates those tables starting with key #0, rather than key #1. B/c some of the test data tables are related or populate specifically to certain key #'s, this causes problems for us. I have to continually change the SQL script mentioned, to use RESEED,1 for the deleted-from tables, to get the test data on the Dev server correct, then change it back for anyone else's build.
All SQL Servers appear to be running the same SQL Server version & service pack levels. Is there any setting, that anyone is aware of, that could cause such behavior? It is 100% consistent, every time the build process is run. (Or do you think this is somehow some setting related to Visual Studio?)
Thanks,
Randy
May 6, 2014 at 2:35 pm
No, don't think any other settings affect that.
Is there maybe something in the process where that table is truncating rather than deleting? Perhaps a missing foreign key constraint causing the process to do something different on that instance?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 6, 2014 at 3:10 pm
May 6, 2014 at 3:57 pm
Thanks. I installed the script you suggested, on both my PC & the network server. There were only three settings that were different, none of which seems relevant:
My PC = IsFullTextInstalled = 1, IsIntegratedSecurityOnly = 1, IsFullTextEnabled = 0
Network server = IsFullTextInstalled = 0, IsIntegratedSecurityOnly = 0, IsFullTextEnabled = 1
May 7, 2014 at 12:19 am
Quick question, is the recovery model the same and is there any difference in the connections, ie. isolation?
😎
May 7, 2014 at 8:12 am
Recovery model for both is "Full". Not quite sure what you mean on the other setting.
May 7, 2014 at 9:10 am
rwitt 95744 (5/7/2014)
Recovery model for both is "Full". Not quite sure what you mean on the other setting.
That is the transaction isolation level, configurable ie. within the connection parameters. Simplest way to check is to run
DBCC USEROPTIONS
GO
on each system and check the output.
😎
May 7, 2014 at 9:15 am
Running the above command, all 13 rows of settings are identical between the two servers.
I'm starting to think the issue is not in SQL Server but that somehow the Visual Studio build process being used on the two servers isn't the same, even if theoretically it's supposed to be.
May 7, 2014 at 9:17 am
Can you run the DBCC statement from Visual Studio?
😎
May 7, 2014 at 9:41 am
I think placing this in my test data script will resolve the issue (or rather, cover it, for both situations):
DECLARE @DELETEDFROM_<tablename> bit = 1
IF (SELECT IDENT_CURRENT('<tablename>') = 1 AND (SELECT COUNT(*) FROM <tablename>) = 0
SET @DELETEDFROM_<tablename> = 0-- New table, has never contained any rows
-- Need to run the above prior to clearing the table
-- Run this after clearing the table
IF @DELETEDFROM_<tablename> = 0
BEGIN
-- New table, has never contained rows; CHECKIDENT(RESEED) treats it as a TRUNCATEd table
DBCC CHECKIDENT ('<tablename>', reseed, 1)
END
ELSE
BEGIN
-- Table exists & was DELETEd FROM; need to use different RESEED value
DBCC CHECKIDENT ('<tablename>', reseed, 0) -- Needed b/c DELETEd rather than TRUNCATEd this table
END
May 7, 2014 at 9:54 am
rwitt 95744 (5/7/2014)
I think placing this in my test data script will resolve the issue (or rather, cover it, for both situations):DECLARE @DELETEDFROM_<tablename> bit = 1
IF (SELECT IDENT_CURRENT('<tablename>') = 1 AND (SELECT COUNT(*) FROM <tablename>) = 0
SET @DELETEDFROM_<tablename> = 0-- New table, has never contained any rows
-- Need to run the above prior to clearing the table
-- Run this after clearing the table
IF @DELETEDFROM_<tablename> = 0
BEGIN
-- New table, has never contained rows; CHECKIDENT(RESEED) treats it as a TRUNCATEd table
DBCC CHECKIDENT ('<tablename>', reseed, 1)
END
ELSE
BEGIN
-- Table exists & was DELETEd FROM; need to use different RESEED value
DBCC CHECKIDENT ('<tablename>', reseed, 0) -- Needed b/c DELETEd rather than TRUNCATEd this table
END
For efficiency, rather than counting all the rows, simply check to see if one exists:
IF (SELECT IDENT_CURRENT('<tablename>') = 1 AND NOT EXISTS(SELECT TOP (1) * FROM <tablename>)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 7, 2014 at 10:25 am
Thinking back, I cannot remember ever using 0 as seed value for a CHECKIDENT if the initial seed value was <> 0, regardless of which method was used of emptying the table.
😎
May 7, 2014 at 10:58 am
CHECKIDENT(RESEED) is quirky, based on TRUNCATE/DELETE & whether the table is a brand-new (never-contained-any-rows) table or an already-existing table.
CHECKIDENT(RESEED,0) acts differently, based on the above. If it's a brand-new table, then, yes, it will start the key count at key = 0 (1st actually assigned key will be 0.) However, if it's an existing table that's been deleted from (not truncated), it will actually start the key count at key = 1 (1st actually assigned key will be 1.)
I need to delete from (not truncate) the table, due to foreign keys. So, the 2nd example (CHECKIDENT(RESEED,0)) restarts my keycount at 1.
May 7, 2014 at 11:02 am
What about dropping and recreating the constraints?
😎
May 7, 2014 at 11:04 am
Could do that, prefer this approach.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply