Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Is there any database setting that affects CHECKIDENT(RESEED) Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 9:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 2,044, Visits: 3,062
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1568559
Posted Wednesday, May 7, 2014 10:25 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 1,788, Visits: 4,677
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.
Post #1568589
Posted Wednesday, May 7, 2014 10:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 8:13 PM
Points: 18, Visits: 107
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.
Post #1568609
Posted Wednesday, May 7, 2014 11:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 1,788, Visits: 4,677
What about dropping and recreating the constraints?
Post #1568610
Posted Wednesday, May 7, 2014 11:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 8:13 PM
Points: 18, Visits: 107
Could do that, prefer this approach.
Post #1568611
Posted Wednesday, May 7, 2014 2:44 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 1,788, Visits: 4,677
I normally prefer to script the creation of all constraints and use something like this procedure to drop them when needed

CREATE PROCEDURE [dbo].[DROP_SCHEMA_CONSTRAINTS]
(
@TABLE_SCHEMA NVARCHAR(128)
)
AS
/*
DECLARE @TABLE_SCHEMA NVARCHAR(128) = N'myschema'
EXEC dbo.DROP_SCHEMA_CONSTRAINTS @TABLE_SCHEMA
*/

DECLARE @SQL_STR NVARCHAR(MAX) = N'';

SELECT @SQL_STR = (SELECT N'ALTER TABLE '
+ T.TABLE_SCHEMA + N'.'
+ T.TABLE_NAME + N' DROP CONSTRAINT '
+ T.CONSTRAINT_NAME + N' -- ' + T.CONSTRAINT_TYPE + N'
'
FROM
(
SELECT OBJECT_NAME(OBJECT_ID) AS CONSTRAINT_NAME,
SCHEMA_NAME(schema_id) AS TABLE_SCHEMA,
OBJECT_NAME(parent_object_id) AS TABLE_NAME,
type_desc AS CONSTRAINT_TYPE
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
AND type_desc NOT LIKE 'PRIMARY_KEY%'
) AS T
WHERE T.TABLE_SCHEMA = @TABLE_SCHEMA
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)');
EXEC (@SQL_STR);

Post #1568694
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse