SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using IDENTITY as a key column


Using IDENTITY as a key column

Author
Message
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16014 Visits: 11355
Toreador (4/21/2010)
...although I was (I think) the first person to comment on the answer to the second part...

Nope! :-D



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
pjdiller
pjdiller
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 291
Speaking (typing really) as someone who was initially annoyed that they got the question wrong, I'd like to now say that I'm happy with the overall result.

I think it's been demonstrated more than a few times (every time?) that the original question is best as a conversation-starter. Smile

Thanks first to Hugo and then to the rest!
rjv_rnjn
rjv_rnjn
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 430
pjdiller (4/21/2010)
I think it's been demonstrated more than a few times (every time?) that the original question is best as a conversation-starter. Smile

Thanks first to Hugo and then to the rest!
Agree {we need a smiley update, no "thumbs-up" Angry } Even for question where I get the answer right, going through the conversations make me realize certain intricacies involved that I didn't think through. Thanks to all for those conversations.
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3728 Visits: 4408
Hugo Kornelis (4/20/2010)
If SQL Server had an auto-tune feature that automatically adds indexes it beliefs will benefit performance, than you would be right. But no current version of SQL Server has such a feature (and based on recommendations I sometimes see from the DTA, that might as well be for the best).

Sometimes SQL Server automatically creates temporary indexes if the optimizer finds out that it will improve query performance.
Ref: Nonclustered Index Spool Showplan Operator http://msdn.microsoft.com/en-us/library/ms189611.aspx

Of course, the index spool operation will not happen in the statement 'SELECT * FROM Persons WHERE PersonID = @PersonID', but it may happen in some statement with many JOINs. And of course, it would be less effective than explicit index creation on PersonID and using that index :-)
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11128 Visits: 12004
vk-kirov (4/22/2010)
Hugo Kornelis (4/20/2010)
If SQL Server had an auto-tune feature that automatically adds indexes it beliefs will benefit performance, than you would be right. But no current version of SQL Server has such a feature (and based on recommendations I sometimes see from the DTA, that might as well be for the best).

Sometimes SQL Server automatically creates temporary indexes if the optimizer finds out that it will improve query performance.
Ref: Nonclustered Index Spool Showplan Operator http://msdn.microsoft.com/en-us/library/ms189611.aspx

Of course, the index spool operation will not happen in the statement 'SELECT * FROM Persons WHERE PersonID = @PersonID', but it may happen in some statement with many JOINs. And of course, it would be less effective than explicit index creation on PersonID and using that index :-)


Thanks, vk-kirov. I was not aware of this operator.

From the limited information I could find on it, it will be used if the same data is used at least twice in a query, the second needs only a subset of the rows needed for the first (otherwise a normal spool would be used), and the subset is based on a predicate that is sargable and selective enough to earn back the cost of creating the index.

Do you think you can post a repro script that actually uses this operator? Have you ever seen it used in your code?


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16014 Visits: 11355
Hugo Kornelis (4/22/2010)
Thanks, vk-kirov. I was not aware of this operator.

From the limited information I could find on it, it will be used if the same data is used at least twice in a query, the second needs only a subset of the rows needed for the first (otherwise a normal spool would be used), and the subset is based on a predicate that is sargable and selective enough to earn back the cost of creating the index.

Do you think you can post a repro script that actually uses this operator? Have you ever seen it used in your code?

You can generate an Index Spool easily with an unindexed Tally table:

SELECT N
INTO #Tally
FROM dbo.Numbers (50000);
GO
SELECT COUNT(*)
FROM #Tally T1
JOIN #Tally T2
ON T2.N < T1.N;



That uses an in-line TVF I keep handy to generate a temporary tally heap - you can use any method that generates a similar object.

Index spools are not all that rare, but the estimated cost of spooling the rows to a worktable, creating the index, and dropping it afterward is quite high, so the optimiser will often choose a different approach, purely on cost grounds (usually a hash join - the example above avoids that by joining on an inequality only).

There are two types of index spool - eager and lazy. The above example generates an eager spool - one that consumes all rows from its input before it can be used to seek with.

A lazy index spool is a bit more like a regular table spool - except it never truncates its worktable (a normal spool only ever caches one result - it truncates its worktable on every rebind).

A lazy index spool accumulates results on every rebind (when the correlated parameter changes). Personally, I'd love a way to be able to hint the use of lazy index spools, but that's another story.

Interestingly, you cannot trust the reported numbers for rebinds and rewinds for an index spool - the internals report a rewind to anything other than the last value seen as a rebind rather than the rewind it actually is. Sad, but true.





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Attachments
IndexSpool.gif (92 views, 6.00 KB)
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3728 Visits: 4408
Hugo Kornelis (4/22/2010)

Do you think you can post a repro script that actually uses this operator? Have you ever seen it used in your code?

Yes, sometimes I see this operator when I profile and optimize SQL queries. When I wrote that post, I was optimizing a long-running stored procedure, and there even were both lazy and eager index spools in one query. I tried to make a simplified example (the original query contained 3 tables, 1 view, and 2 subqueries), but all what I got was either hash join or table spool :-)
Thanks to Paul for posting a simple example!
Pooja-462303
Pooja-462303
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 45
I am really surprised,
I thought I was a newbie but now i have to call myself lower than newbie,
Can you pls give me an instance/how the identity column can have duplicate.


Thank you
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11128 Visits: 12004
Pooja-462303 (4/29/2010)
Can you pls give me an instance/how the identity column can have duplicate.

Sure!

Using SET IDENTITY_INSERT ON:
CREATE TABLE Demo1
(Id int NOT NULL IDENTITY,
Other varchar(20)
);
INSERT INTO Demo1 (Other)
VALUES ('First');
SET IDENTITY_INSERT Demo1 ON;
INSERT INTO Demo1 (Id, Other)
VALUES (1, 'Another first?');
SELECT * FROM Demo1;
DROP TABLE Demo1;



Using DBCC CHECKIDENT with RESEED:
CREATE TABLE Demo2
(Id int NOT NULL IDENTITY,
Other varchar(20)
);
INSERT INTO Demo2 (Other)
VALUES ('First');
DBCC CHECKIDENT (Demo2, RESEED, 0);
INSERT INTO Demo2 (Other)
VALUES ('Another first?');
SELECT * FROM Demo2;
DROP TABLE Demo2;




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3455 Visits: 1865
An iffy question IMHO. You need to make certain assumptions to come to the right/wrong answer.
Are the points being returned? :-)

"Keep Trying"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search