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 «««34567»»

Using IDENTITY as a key column Expand / Collapse
Author
Message
Posted Wednesday, April 21, 2010 4:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
Toreador (4/21/2010)
...although I was (I think) the first person to comment on the answer to the second part...

Nope!




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #907593
Posted Wednesday, April 21, 2010 7:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, March 15, 2014 1:45 PM
Points: 405, Visits: 286
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. :)

Thanks first to Hugo and then to the rest!
Post #907740
Posted Wednesday, April 21, 2010 8:22 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 25, 2014 8:31 AM
Points: 513, 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. :)

Thanks first to Hugo and then to the rest!
Agree {we need a smiley update, no "thumbs-up" } 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.
Post #907757
Posted Thursday, April 22, 2010 1:59 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Post #908376
Posted Thursday, April 22, 2010 2:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 5,975, Visits: 8,233
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
Post #908392
Posted Thursday, April 22, 2010 5:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
IndexSpool.gif (83 views, 6.63 KB)
Post #908472
Posted Tuesday, April 27, 2010 1:47 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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!
Post #910863
Posted Thursday, April 29, 2010 3:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 3, 2011 9:42 PM
Points: 64, 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
Post #913304
Posted Thursday, April 29, 2010 4:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 5,975, Visits: 8,233
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
Post #913308
Posted Monday, May 3, 2010 5:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
An iffy question IMHO. You need to make certain assumptions to come to the right/wrong answer.
Are the points being returned?


"Keep Trying"
Post #914592
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse