Select distinct question

Question

Select distinct question

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 1

Paul White (2/18/2010)
Dan Guzman - Not the MVP (2/18/2010)
Also, in this case, using the serial number does not guarantee the order of flower and apple, since the serial is 6 for both records.
I took that to be an error on the OP's part - corrected in the solution I posted earlier, after Lutz's 🙂
Come to think of it, both the original post and my script defined a PRIMARY KEY on the IDENTITY column, so the duplicated sizes must be an error - and the particular IDENTITY_INSERT value jcrawf02 used would produce an error too. Yay!
edit: added link

Good point, missed that. Still don't like depending on the identity for order, I can still force a value as long as it's not a duplicate, and then the identity value gets seeded with whatever I force in.

CREATE TABLE #temp (iRow int identity(1,1),something char(1) CONSTRAINT [PK_RECON_RECORD] PRIMARY KEY CLUSTERED ([iRow]) ON [PRIMARY] )

INSERT INTO #temp

SELECT 'a' UNION ALL

SELECT 'b' UNION ALL

SELECT 'c' UNION ALL

SELECT 'd'

SET IDENTITY_INSERT #temp ON

INSERT INTO #temp (iRow,something) VALUES (10000,'e')

SET IDENTITY_INSERT #temp OFF

INSERT INTO #temp

SELECT 'f' UNION ALL

SELECT 'g' UNION ALL

SELECT 'h' UNION ALL

SELECT 'i'

SELECT * FROM #temp

gives:

iRowsomething

1a

2b

3c

4d

10000e

10001f

10002g

10003h

10004i

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

Odd, when I force a negative value, it doesn't change the identity incremental value at all? Try the last code, but set to negative anything, I assumed it would then count up to zero, but it doesn't.

That's a better reason not to depend on it for ordering than just because it makes me uncomfortable 😛

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Paul White SSC Guru Points: 150457 More actions · Answer 3

Books Online - SET IDENTITY_INSERT
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

So, if you create the column with the identity property with a seed of -50000, and then add an explicit insert of -750, the next value will be -749 and it will 'count up' to zero.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 4

jcrawf02 (2/18/2010)
That's a better reason not to depend on it for ordering than just because it makes me uncomfortable 😛

To my mind it is rather a moot point, even if you can guarantee that on small tables with a clustered index will always return in order.

1) We surely cant be talking about non temporary tables, whos to say that in the future the clustered index will not be changed to a different column.

2) If there are a 'small' amount of rows presently, that can obviously change for some circumstances

The defensive programmer in me , dictates that i could gain a lot but lose nothing other than a few keystrokes by adding the ORDER BY clause.

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150457 More actions · Answer 5

Dave Ballantyne (2/19/2010)
...even if you can guarantee that on small tables with a clustered index will always return in order.

There is no guarantee of that - it's just very difficult to reproduce.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 6

jcrawf02 (2/18/2010)
Odd, when I force a negative value, it doesn't change the identity incremental value at all? Try the last code, but set to negative anything, I assumed it would then count up to zero, but it doesn't.

You did DBCC checkident to reseed to your new negative value ?

http://msdn.microsoft.com/en-us/library/ms176057.aspx

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150457 More actions · Answer 7

Dave,

I think you missed my earlier reply to that point about the negative reseed:

Books Online - SET IDENTITY_INSERT
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 8

Paul White (2/19/2010)
I think you missed my earlier reply to that point about the negative reseed:

Apologies , i didnt read i properly , must learn not to rush so much.

TBH , i didnt know that it did that, seems like that could be helpful but could be wrong at the same time.

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150457 More actions · Answer 9

Dave Ballantyne (2/19/2010)
TBH , i didnt know that it did that, seems like that could be helpful but could be wrong at the same time.

No worries. It is a bit of an odd duck, but I guess the SQL Server Development Team had to decide what to do about the current identity value after a IDENTITY_INSERT, and this is probably the best compromise. After all, if you forcibly insert a value that is earlier than the current value, you're likely to come to a point when the identity value catches up with itself again, and an error would result. A hard-to-trace-back error at that. If the forced value is higher than the current value, it seems relatively safe to reseed, and is probably what the INSERTee intended. Something like that - I guess you could look at it from many angles! It would make a good QOD don't you think?

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi