Insertion order - GETDATE(), IDENTITY_INSERT, and others...

Question

Insertion order - GETDATE(), IDENTITY_INSERT, and others...

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

It would be truly impossible to ensure 100% that you have the exact order every single record is inserted into a table. You could devise more and more schemes but you can always get around them.

This is what I said... Void ab initio. 😉

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Dev (11/17/2011)
It would be truly impossible to ensure 100% that you have the exact order every single record is inserted into a table. You could devise more and more schemes but you can always get around them.
This is what I said... Void ab initio. 😉

Yes! I did not think about Identity Insert at that point either...sheesh. I never really ever considered the desire to be absolutely certain the order rows are inserted.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jared SSC Guru Points: 61793 More actions · Answer 3

Sean Lange (11/17/2011)
Dev (11/17/2011)
It would be truly impossible to ensure 100% that you have the exact order every single record is inserted into a table. You could devise more and more schemes but you can always get around them.
This is what I said... Void ab initio. 😉
Yes! I did not think about Identity Insert at that point either...sheesh. I never really ever considered the desire to be absolutely certain the order rows are inserted.

Assuming nobody turns of identity_insert and changes data... How can you say that a row with a lower identity was NOT inserted before a row with a higher identity? What would be the way around this?

Jared

Jared
CE - Microsoft

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

Assuming nobody turns of identity_insert and changes data... How can you say that a row with a lower identity was NOT inserted before a row with a higher identity? What would be the way around this?

You won't like my solution Jared... 😉

SELECT col1, col2... coln, Mod_Date

FROM XYZ

ORDER BY Mod_Date DESC -- Ultimately, you will land-up here.

Jared SSC Guru Points: 61793 More actions · Answer 5

Dev (11/17/2011)
Assuming nobody turns of identity_insert and changes data... How can you say that a row with a lower identity was NOT inserted before a row with a higher identity? What would be the way around this?
You won't like my solution Jared... 😉
SELECT col1, col2... coln, Mod_Date
FROM XYZ
ORDER BY Mod_Date DESC -- Ultimately, you will land-up here.

That's a mod date though, it does not guarantee when a row was inserted. Theoretically, I am not even interested in "when" a row was inserted, but its relationship in time to another. Is identity_insert a guarantee of this, and if not why? If it is, is it the only way?

Jared

Jared
CE - Microsoft

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

p-nut (11/17/2011)
Sean Lange (11/17/2011)
Dev (11/17/2011)
It would be truly impossible to ensure 100% that you have the exact order every single record is inserted into a table. You could devise more and more schemes but you can always get around them.
This is what I said... Void ab initio. 😉
Yes! I did not think about Identity Insert at that point either...sheesh. I never really ever considered the desire to be absolutely certain the order rows are inserted.
Assuming nobody turns of identity_insert and changes data... How can you say that a row with a lower identity was NOT inserted before a row with a higher identity? What would be the way around this?
Jared

Change the ddl for the table so the identity increment is negative. (this is tough to script on a temp table but you can set the next value to whatever you want and the increment CAN be negative)

Reseed the identity (it is not required to be unique).

create table #IdentityProof

(

RowID int identity not null,

SomeValue uniqueidentifier,

SysAddDate datetime not null

)

go

insert #IdentityProof select NEWID(), GETDATE()

go 10

DBCC CHECKIDENT (#IdentityProof, RESEED, 999)

go

insert #IdentityProof select NEWID(), GETDATE()

go 10

DBCC CHECKIDENT (#IdentityProof, RESEED, 0)

WAITFOR DELAY '00:00:03' --wait for 3 seconds to demonstrate time differences.

go

insert #IdentityProof select NEWID(), GETDATE()

go 10

select * from #IdentityProof order by RowID, SysAddDate

drop table #IdentityProof

See where this is going? You can do all sorts of things to circumvent some sort of ordering.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

From design perspective, I can always argue that I don't need Ins_Date column. Lowest of the Mod_Date should be the same. I understand there are scenarios where it won't be but take simple example for time being.

Now your questions:

Is identity_insert a guarantee of this, and if not why?

As long as nobody resets it.

If it is, is it the only way?

Nope. Sorted date column should suffice the requirements.

You may also have INSERT ONLY tables (Say SCD). Add one version identifier and for each Logical Update make Physical Insert.

Jared SSC Guru Points: 61793 More actions · Answer 8

Dev (11/17/2011)
From design perspective, I can always argue that I don't need Ins_Date column. Lowest of the Mod_Date should be the same. I understand there are scenarios where it won't be but take simple example for time being.
Now your questions:
Is identity_insert a guarantee of this, and if not why?
As long as nobody resets it.
If it is, is it the only way?
Nope. Sorted date column should suffice the requirements.
You may also have INSERT ONLY tables (Say SCD). Add one version identifier and for each Logical Update make Physical Insert.

So, we have 2 caveats: Identity_insert as long as the DDL does not change and the createdDate column (or whatever you want to call it) as long as the GETDATE() (or whatever is used to get the current datetime) is within the insert statement.

I think I have a good understanding after some of these examples and discussion. Thanks!

Jared

Jared
CE - Microsoft

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

p-nut (11/17/2011)
Sean Lange (11/17/2011)
Dev (11/17/2011)
It would be truly impossible to ensure 100% that you have the exact order every single record is inserted into a table. You could devise more and more schemes but you can always get around them.
This is what I said... Void ab initio. 😉
Yes! I did not think about Identity Insert at that point either...sheesh. I never really ever considered the desire to be absolutely certain the order rows are inserted.
Assuming nobody turns of identity_insert and changes data... How can you say that a row with a lower identity was NOT inserted before a row with a higher identity? What would be the way around this?
Jared

I presume you're ONLY dealing with one row at a time. Even then - it's going to be tough to say 100% of the time that it cannot ever happen. multi-threading inserts, alone with the precision of the datetime might possibly cause some wrinkles.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

One word - ROWVERSION - I don't think you can break this...

BEGIN TRAN

-- Let's have a test table with an identity (to be forced out of order)

-- and a varchar so we can see some other data in the rows

-- and a datetime defaulted to GETDATE() with a CHECK CONSTRAINT that looks like it will ensure insertion order is preserved but fails

-- and a ROWVERSION that will ensure insertion/update order is preserved.

CREATE TABLE #insertorder(

myId INT IDENTITY(100,1) PRIMARY KEY

,myChar VARCHAR(10) DEFAULT(LEFT(NEWID(),10))

,myDate datetime default(getdate()) CHECK (myDate=GETDATE())

,myVersion rowversion

);

-- Let's add a default row

INSERT #insertorder DEFAULT VALUES;

-- Let's add some more default rows - fairly quickly, but one by one (to break the CHECK constraint theory)

-- you should get an error or two here...

WHILE SCOPE_IDENTITY()<1000

INSERT #insertorder DEFAULT VALUES;

-- add some rows in a batch to show that they have the same datetime value but different ROWVERSION values

INSERT TOP(2) #insertorder (myChar) SELECT left(COLUMN_NAME,10) FROM INFORMATION_SCHEMA.COLUMNS ORDER BY newid();

-- update a row to show that its ROWVERSION is changed

UPDATE #insertorder SET myChar='TEST' WHERE myId = 293;

-- now let's shove an out of order identity value in - and see how the ROWVERSION preserves the order.

SET IDENTITY_INSERT #insertorder ON;

INSERT #insertorder(myId,myChar) VALUES(1,'TEST2');

SET IDENTITY_INSERT #insertorder OFF;

-- and finally find the "latest" five rows.

SELECT TOP(5) * FROM #insertorder

ORDER BY myVersion DESC

/* The result should look a bit like this: note how we get the latest inserts/updates as expected.

myIdmyCharmyDatemyVersion

1TEST22011-11-20 01:46:36.7830x0000000000007A2D

293TEST2011-11-20 01:46:36.4600x0000000000007A2C

1002id2011-11-20 01:46:36.7770x0000000000007A2B

1001myDec2011-11-20 01:46:36.7770x0000000000007A2A

1000807B1657-A2011-11-20 01:46:36.7630x0000000000007A29

*/

ROLLBACK

Here you can see that the identity column and the datetime column are both useless for finding the latest 5 changes, but if you can add a ROWVERSION - you have it.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 11

mister.magoo (11/19/2011)
One word - ROWVERSION - I don't think you can break this...
BEGIN TRAN
-- Let's have a test table with an identity (to be forced out of order)
-- and a varchar so we can see some other data in the rows
-- and a datetime defaulted to GETDATE() with a CHECK CONSTRAINT that looks like it will ensure insertion order is preserved but fails
-- and a ROWVERSION that will ensure insertion/update order is preserved.
CREATE TABLE #insertorder(
myId INT IDENTITY(100,1) PRIMARY KEY
,myChar VARCHAR(10) DEFAULT(LEFT(NEWID(),10))
,myDate datetime default(getdate()) CHECK (myDate=GETDATE())
,myVersion rowversion
);
-- Let's add a default row
INSERT #insertorder DEFAULT VALUES;
-- Let's add some more default rows - fairly quickly, but one by one (to break the CHECK constraint theory)
-- you should get an error or two here...
WHILE SCOPE_IDENTITY()<1000
INSERT #insertorder DEFAULT VALUES;
-- add some rows in a batch to show that they have the same datetime value but different ROWVERSION values
INSERT TOP(2) #insertorder (myChar) SELECT left(COLUMN_NAME,10) FROM INFORMATION_SCHEMA.COLUMNS ORDER BY newid();
-- update a row to show that its ROWVERSION is changed
UPDATE #insertorder SET myChar='TEST' WHERE myId = 293;
-- now let's shove an out of order identity value in - and see how the ROWVERSION preserves the order.
SET IDENTITY_INSERT #insertorder ON;
INSERT #insertorder(myId,myChar) VALUES(1,'TEST2');
SET IDENTITY_INSERT #insertorder OFF;
-- and finally find the "latest" five rows.
SELECT TOP(5) * FROM #insertorder
ORDER BY myVersion DESC
/* The result should look a bit like this: note how we get the latest inserts/updates as expected.
myIdmyCharmyDatemyVersion
1TEST22011-11-20 01:46:36.7830x0000000000007A2D
293TEST2011-11-20 01:46:36.4600x0000000000007A2C
1002id2011-11-20 01:46:36.7770x0000000000007A2B
1001myDec2011-11-20 01:46:36.7770x0000000000007A2A
1000807B1657-A2011-11-20 01:46:36.7630x0000000000007A29
*/
ROLLBACK
Here you can see that the identity column and the datetime column are both useless for finding the latest 5 changes, but if you can add a ROWVERSION - you have it.

The question was about inserts but not inserts+updates, so I don't see how ROWVERSION can be useful.

Alex Suprun

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 12

The actual discussion started here & the thumb rule here is NOT to use ORDER BY.

Last 5 rows

http://www.sqlservercentral.com/Forums/Topic1206622-391-1.aspx

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

Dev (11/20/2011)
The actual discussion started here & the thumb rule here is NOT to use ORDER BY.
Last 5 rows
http://www.sqlservercentral.com/Forums/Topic1206622-391-1.aspx

Actually there was a method presented that will work most of the time not using an order by. Jeff pointed out that the technique is not 100%. The ONLY way you can be certain of the order of rows is to use an order by.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

GSquared SSC Guru Points: 260824 More actions · Answer 14

mister.magoo (11/19/2011)
One word - ROWVERSION - I don't think you can break this...
BEGIN TRAN
-- Let's have a test table with an identity (to be forced out of order)
-- and a varchar so we can see some other data in the rows
-- and a datetime defaulted to GETDATE() with a CHECK CONSTRAINT that looks like it will ensure insertion order is preserved but fails
-- and a ROWVERSION that will ensure insertion/update order is preserved.
CREATE TABLE #insertorder(
myId INT IDENTITY(100,1) PRIMARY KEY
,myChar VARCHAR(10) DEFAULT(LEFT(NEWID(),10))
,myDate datetime default(getdate()) CHECK (myDate=GETDATE())
,myVersion rowversion
);
-- Let's add a default row
INSERT #insertorder DEFAULT VALUES;
-- Let's add some more default rows - fairly quickly, but one by one (to break the CHECK constraint theory)
-- you should get an error or two here...
WHILE SCOPE_IDENTITY()<1000
INSERT #insertorder DEFAULT VALUES;
-- add some rows in a batch to show that they have the same datetime value but different ROWVERSION values
INSERT TOP(2) #insertorder (myChar) SELECT left(COLUMN_NAME,10) FROM INFORMATION_SCHEMA.COLUMNS ORDER BY newid();
-- update a row to show that its ROWVERSION is changed
UPDATE #insertorder SET myChar='TEST' WHERE myId = 293;
-- now let's shove an out of order identity value in - and see how the ROWVERSION preserves the order.
SET IDENTITY_INSERT #insertorder ON;
INSERT #insertorder(myId,myChar) VALUES(1,'TEST2');
SET IDENTITY_INSERT #insertorder OFF;
-- and finally find the "latest" five rows.
SELECT TOP(5) * FROM #insertorder
ORDER BY myVersion DESC
/* The result should look a bit like this: note how we get the latest inserts/updates as expected.
myIdmyCharmyDatemyVersion
1TEST22011-11-20 01:46:36.7830x0000000000007A2D
293TEST2011-11-20 01:46:36.4600x0000000000007A2C
1002id2011-11-20 01:46:36.7770x0000000000007A2B
1001myDec2011-11-20 01:46:36.7770x0000000000007A2A
1000807B1657-A2011-11-20 01:46:36.7630x0000000000007A29
*/
ROLLBACK
Here you can see that the identity column and the datetime column are both useless for finding the latest 5 changes, but if you can add a ROWVERSION - you have it.

So long as you want the last five that were inserted or updated. Rowversion won't preserve insert sequence if rows are updated later. If they won't be updated, then it's fine for insert sequence.

Otherwise, rowversion plus audit logging to some sort of WORM media is the only way I can think of to guarantee the ability to validate insert sequence. Would have to be passive, t-log based auditing, and you'd have to make sure nobody had the rights necessary to set the database into Simple recovery.

C2 (or, better yet, Common Criteria) logging would help with that aspect of it, of course.

It really depends on how paranoid you need to be about insert sequence. In most cases, controlling the insert sequence and using an ID column will be adequate. It assumes nobody will deliberately TRY to violate the rules on it, but that's probably true in most businesses and most databases. If row-sequence has some critical legal compliance or security importance, then you need to look at Common Criteria compliance, and at keeping the raw data on WORM media for validation/audit trail purposes.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 15

Sean Lange (11/21/2011)
Dev (11/20/2011)
The actual discussion started here & the thumb rule here is NOT to use ORDER BY.
Last 5 rows
http://www.sqlservercentral.com/Forums/Topic1206622-391-1.aspx
Actually there was a method presented that will work most of the time not using an order by. Jeff pointed out that the technique is not 100%. The ONLY way you can be certain of the order of rows is to use an order by.

We know it but the RULEs for the GAME are already SET 😉