March 8, 2010 at 9:35 pm
Does re-indexing of a table change the IDENTITY Seed value on a row? I believe that the value stays the same unless the IDENTITY is re-incremented.
The Wall
March 8, 2010 at 9:52 pm
The Wall (3/8/2010)
Does re-indexing of a table change the IDENTITY Seed value on a row? I believe that the value stays the same unless the IDENTITY is re-incremented.
No, reindexing does not change the IDENTITY seed value on a row.
March 8, 2010 at 9:59 pm
Lynn, I answered the same thing on ASK....
Double posting.
March 8, 2010 at 10:07 pm
I don't visit AskSSC very often. Hard to tell what questions need answering or not.
March 8, 2010 at 10:11 pm
Yeah, know what you mean...
😀
March 9, 2010 at 4:00 am
Thank you. I did not need to know this answer as I much as I need to prove it in writing, so that little time is spent discussing it. I have never even seen this documented anywhere before my question.
This person insists that re-indexing changes the Identity seed value on a row, so please any links to documentation about this subject would be much appreciated. I want to prove and end this discussion.
Thank you for any assistance.
The Wall
March 9, 2010 at 4:21 am
The Wall (3/9/2010)
Thank you. I did not need to know this answer as I much as I need to prove it in writing, so that little time is spent discussing it. I have never even seen this documented anywhere before my question.This person insists that re-indexing changes the Identity seed value on a row, so please any links to documentation about this subject would be much appreciated. I want to prove and end this discussion.
On the basis that it doesn't do it you might be hard-pressed to find something that specifically states it doesn't re-seed the indexes. The two aren't really connected.
It may be more interesting for the other person to provide the proof that it does. Why should you spend your time running around after this?
BrainDonor.
March 9, 2010 at 4:34 am
On the AskSSC forum @Fatherjack has added some additional notes with regards to the ordering of the ID's (in relation to indexes).
Reindexing DEFINATELY doesnt not change the seed values.
Only other thing I can think that changes seeds is TRUNCATE TABLE...
March 9, 2010 at 5:10 am
The Wall (3/9/2010)
This person insists that re-indexing changes the Identity seed value on a row, so please any links to documentation about this subject would be much appreciated. I want to prove and end this discussion.
It's pretty trivial to prove that it doesn't change identity values for a row or next allocated identity value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2010 at 6:14 am
You are right, but this person calls himself our system administrator/ dba. It's kinda like helping Haiti with a twist.
The Wall
March 9, 2010 at 6:31 am
The Wall (3/9/2010)
You are right, but this person calls himself our system administrator/ dba.
Regardless of what he is, it's hard to argue with tests that prove what you're saying, unless he can come up with a test that proves it does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2010 at 6:32 am
Scary. Get him to run this on a TEST* instance:
-- Using tempdb for demo purposes only
USE tempdb;
GO
-- Test table
CREATE TABLE dbo.IdentityTest
(
identity_column INTEGER IDENTITY PRIMARY KEY,
data INTEGER NOT NULL,
);
GO
-- Add 50,000 rows of well-distributed data values
WITH Numbers (n)
AS (
SELECT TOP (50000)
ROW_NUMBER() OVER (
ORDER BY (SELECT(0)))
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3
)
INSERT dbo.IdentityTest
(data)
SELECT CHECKSUM(n)
FROM Numbers;
GO
-- Create a non-clustered index, (In addition to the
-- clustered primary key index).
CREATE INDEX RebuildMe ON dbo.IdentityTest (data ASC);
GO
-- Show the current identity value (50,000)
SELECT current_identity_value = IDENT_CURRENT(N'dbo.IdentityTest')
GO
-- Rebuild all the indexes
ALTER INDEX
ALL ON dbo.IdentityTest
REBUILD;
GO
-- Show that the identity value HAS NOT CHANGED
SELECT current_identity_value = IDENT_CURRENT(N'dbo.IdentityTest')
GO
-- Tidy up
DROP TABLE dbo.IdentityTest;
Paul
* = Do not let him near production systems, ever 😉
edit: added comments
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 9, 2010 at 6:37 am
Why dont you set up a small test in your staging evironment???
Show this "so called" dba that the likes of @Gail (extremely well respected in the SQL community!) say he's wrong...
Sorry Paul, just seen your post! Perfect!
March 9, 2010 at 6:43 am
sql_lock (3/9/2010)
Sorry Paul, just seen your post! Perfect!
I like to help where I can 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 9, 2010 at 6:48 am
sql_lock (3/9/2010)
Show this "so called" dba that the likes of @Gail (extremely well respected in the SQL community!) say he's wrong...
http://en.wikipedia.org/wiki/Argument_from_authority
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply