November 17, 2011 at 7:44 am
In another thread we were discussing determining the order of insertion. This brought some questions to mind for me. I guess the main question is this; is is possible to guarantee a result set while querying to return the most recent 5 rows inserted? For example, I may assume that when I have a createdDate column that inserts GETDATE() at the time of insertion that if I ORDER BY createdDate I would get these rows in order of the time at which they were inserted. However, my limited knowledge makes me wonder if a row could be inserted with an earlier datetime versus another based on other factors. Another thought I had was what happens with an IDENTITY_INSERT value when the transaction is rolled back? I will begin playing with some of these scenarios in a test environment, but I figured some gurus may have some answers or thoughts of their own on this.
What is the utility of knowing the exact order of when the row was written to a table irrespective of when the command was initiated? I don't know yet... this may simply be theoretical, but I don't want to assume.
Again, this is not the order of storage or anything like that. It is simply knowing the order of when the data was committed on an insert.
Thanks,
Jared
Jared
CE - Microsoft
November 17, 2011 at 7:54 am
p-nut (11/17/2011)
In another thread we were discussing determining the order of insertion. This brought some questions to mind for me. I guess the main question is this; is is possible to guarantee a result set while querying to return the most recent 5 rows inserted? For example, I may assume that when I have a createdDate column that inserts GETDATE() at the time of insertion that if I ORDER BY createdDate I would get these rows in order of the time at which they were inserted. However, my limited knowledge makes me wonder if a row could be inserted with an earlier datetime versus another based on other factors. Another thought I had was what happens with an IDENTITY_INSERT value when the transaction is rolled back? I will begin playing with some of these scenarios in a test environment, but I figured some gurus may have some answers or thoughts of their own on this.What is the utility of knowing the exact order of when the row was written to a table irrespective of when the command was initiated? I don't know yet... this may simply be theoretical, but I don't want to assume.
Again, this is not the order of storage or anything like that. It is simply knowing the order of when the data was committed on an insert.
Thanks,
Jared
I wasn't part of the discussion before and am not a guru, so this may be stupid, but. . . 🙂
Combine CHECK and DEFAULT.
BEGIN TRAN
CREATE TABLE test (
id INT IDENTITY,
someStuff VARCHAR(100),
insertionDate DATETIME DEFAULT (GETDATE())
CHECK (insertionDate=GETDATE())
)
INSERT INTO test (someStuff)
SELECT 'testing'
SELECT * FROM test
INSERT INTO test (someStuff, insertionDate)
SELECT 'testing something else', GETDATE()
SELECT * FROM test
INSERT INTO test (someStuff, insertionDate)
SELECT 'testing something current', CURRENT_TIMESTAMP
SELECT * FROM test
INSERT INTO test (someStuff, insertionDate)
SELECT 'testing something new', DATEADD(second,1,GETDATE())
SELECT * FROM test
ROLLBACK
Allows me to ORDER on "insertionDate" and know it's accurate because you can't insert anything other than current datetime into it.
November 17, 2011 at 7:55 am
Just one question Jared. Why do you want to discuss on this? Do you see any business requirement / Use Case where it could be useful?
The thread you referred to is void, ab initio.
November 17, 2011 at 8:07 am
Dev (11/17/2011)
Just one question Jared. Why do you want to discuss on this? Do you see any business requirement / Use Case where it could be useful?The thread you referred to is void, ab initio.
I don't currently have a Use Case for this, however I think it important to understand theory in order to be a good DBA or developer. I could see an advantage of understanding or using this in a debugging sense; i.e. If I know that an ORDER BY createdDate is not in sync from actual insertion order, I may be able to backtrack to a server, application, or some code that is causing unnoticed errors or bottlenecks. Again, not sure there is a business case or use case, but sometimes we don't find the use for something until we understand it completely or learn to utilize it. I think the amazing Tally Table is an example of that :hehe:
Jared
Jared
CE - Microsoft
November 17, 2011 at 8:09 am
Cadavre (11/17/2011)
p-nut (11/17/2011)
In another thread we were discussing determining the order of insertion. This brought some questions to mind for me. I guess the main question is this; is is possible to guarantee a result set while querying to return the most recent 5 rows inserted? For example, I may assume that when I have a createdDate column that inserts GETDATE() at the time of insertion that if I ORDER BY createdDate I would get these rows in order of the time at which they were inserted. However, my limited knowledge makes me wonder if a row could be inserted with an earlier datetime versus another based on other factors. Another thought I had was what happens with an IDENTITY_INSERT value when the transaction is rolled back? I will begin playing with some of these scenarios in a test environment, but I figured some gurus may have some answers or thoughts of their own on this.What is the utility of knowing the exact order of when the row was written to a table irrespective of when the command was initiated? I don't know yet... this may simply be theoretical, but I don't want to assume.
Again, this is not the order of storage or anything like that. It is simply knowing the order of when the data was committed on an insert.
Thanks,
Jared
I wasn't part of the discussion before and am not a guru, so this may be stupid, but. . . 🙂
Combine CHECK and DEFAULT.
BEGIN TRANCREATE TABLE test (
id INT IDENTITY,
someStuff VARCHAR(100),
insertionDate DATETIME DEFAULT (GETDATE())
CHECK (insertionDate=GETDATE())
)
INSERT INTO test (someStuff)
SELECT 'testing'
SELECT * FROM test
INSERT INTO test (someStuff, insertionDate)
SELECT 'testing something else', GETDATE()
SELECT * FROM test
INSERT INTO test (someStuff, insertionDate)
SELECT 'testing something current', CURRENT_TIMESTAMP
SELECT * FROM test
INSERT INTO test (someStuff, insertionDate)
SELECT 'testing something new', DATEADD(second,1,GETDATE())
SELECT * FROM test
ROLLBACK
Allows me to ORDER on "insertionDate" and know it's accurate because you can't insert anything other than current datetime into it.
This is true in 1 connection, but what about other servers executing the same query against the same database? To complicate this even more this even more, what about 2 different SPs writing to the same table? Am I thinking about this right?
Jared
Jared
CE - Microsoft
November 17, 2011 at 8:12 am
p-nut (11/17/2011)
This is true in 1 connection, but what about other servers executing the same query against the same database? To complicate this even more this even more, what about 2 different SPs writing to the same table?Jared
What difference would any of that make? Admittedly I'd want to test this assumption before it went anywhere near a production environment, but surely the CHECK is done on the server that holds the table.
November 17, 2011 at 8:26 am
Cadavre (11/17/2011)
p-nut (11/17/2011)
This is true in 1 connection, but what about other servers executing the same query against the same database? To complicate this even more this even more, what about 2 different SPs writing to the same table?Jared
What difference would any of that make? Admittedly I'd want to test this assumption before it went anywhere near a production environment, but surely the CHECK is done on the server that holds the table.
Ok, so in this case we are forcing the statement to fail if it is not the current date. If we take out the date check and only allow the identity_insert we still can allow the datetime insertion out of sync with the insertion, however the identity column will still guarantee the order of insertion. Is there any possible way that the identity column, in this case, could not represent insertion order and how would we know? My guess is no, but that's why I want to understand if there is a case because it would teach me something new and probably important about the workngs of the engine.
Jared
Jared
CE - Microsoft
November 17, 2011 at 8:35 am
p-nut (11/17/2011)
Cadavre (11/17/2011)
p-nut (11/17/2011)
This is true in 1 connection, but what about other servers executing the same query against the same database? To complicate this even more this even more, what about 2 different SPs writing to the same table?Jared
What difference would any of that make? Admittedly I'd want to test this assumption before it went anywhere near a production environment, but surely the CHECK is done on the server that holds the table.
Ok, so in this case we are forcing the statement to fail if it is not the current date. If we take out the date check and only allow the identity_insert we still can allow the datetime insertion out of sync with the insertion, however the identity column will still guarantee the order of insertion. Is there any possible way that the identity column, in this case, could not represent insertion order and how would we know? My guess is no, but that's why I want to understand if there is a case because it would teach me something new and probably important about the workngs of the engine.
Jared
The check constraint will ensure this under most situations without any issue at all. If having the order is absolutely 99.9% required to be correct then use an instead of insert trigger and make the DateCreated = CURRENT_TIMESTAMP. That way no insert will fail because of somebody trying to backdate it. Of course the .1% is still there because the trigger could be disabled for a certain insert. :hehe:
_______________________________________________________________
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/
November 17, 2011 at 8:43 am
Sean Lange (11/17/2011)
p-nut (11/17/2011)
Cadavre (11/17/2011)
p-nut (11/17/2011)
This is true in 1 connection, but what about other servers executing the same query against the same database? To complicate this even more this even more, what about 2 different SPs writing to the same table?Jared
What difference would any of that make? Admittedly I'd want to test this assumption before it went anywhere near a production environment, but surely the CHECK is done on the server that holds the table.
Ok, so in this case we are forcing the statement to fail if it is not the current date. If we take out the date check and only allow the identity_insert we still can allow the datetime insertion out of sync with the insertion, however the identity column will still guarantee the order of insertion. Is there any possible way that the identity column, in this case, could not represent insertion order and how would we know? My guess is no, but that's why I want to understand if there is a case because it would teach me something new and probably important about the workngs of the engine.
Jared
The check constraint will ensure this under most situations without any issue at all. If having the order is absolutely 99.9% required to be correct then use an instead of insert trigger and make the DateCreated = CURRENT_TIMESTAMP. That way no insert will fail because of somebody trying to backdate it. Of course the .1% is still there because the trigger could be disabled for a certain insert. :hehe:
How would this check constraint work in the situation where the insert contains a large subquery or something like that? Does the GETDATE() get generated at the time of insertion, or when the query begins? Probably a simple question that I should already know... 😛
Jared
Jared
CE - Microsoft
November 17, 2011 at 9:00 am
For a single insert they will all have the exact same value. This is easy enough to test. Here is a simple 10 million row table. The insert takes about 10 seconds on my machine.
create table #DateTest
(
DateValue datetime,
ValueField varchar(100)
)
insert #DateTest
select top 10000000 getdate(), a.name from syscolumns a, syscolumns b
where a.name <> ''
select MIN(DateValue), max(DateValue), COUNT(*) from #DateTest
drop table #DateTest
--edit fat fingers
_______________________________________________________________
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/
November 17, 2011 at 9:48 am
Sean Lange (11/17/2011)
For a single insert they will all have the exact same value. This is easy enough to test. Here is a simple 10 million row table. The insert takes about 10 seconds on my machine.
create table #DateTest
(
DateValue datetime,
ValueField varchar(100)
)
insert #DateTest
select top 10000000 getdate(), a.name from syscolumns a, syscolumns b
where a.name <> ''
select MIN(DateValue), max(DateValue), COUNT(*) from #DateTest
drop table #DateTest
--edit fat fingers
So it seems that it is impossible to have a lower identity value and a later date when GETDATE() is in the insert statement. Got it! Good info and thanks for the great example. 🙂
Jared
Jared
CE - Microsoft
November 17, 2011 at 9:52 am
So it seems that it is impossible to have a lower identity value and a later date when GETDATE() is in the insert statement. Got it! Good info and thanks for the great example. 🙂
Aren't you forgetting Identity Inserts?
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }
November 17, 2011 at 9:57 am
Dev (11/17/2011)
So it seems that it is impossible to have a lower identity value and a later date when GETDATE() is in the insert statement. Got it! Good info and thanks for the great example. 🙂
Aren't you forgetting Identity Inserts?
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }
Sorry... I was speaking in general, not necessarily in reference to the exact query given.
Jared
Jared
CE - Microsoft
November 17, 2011 at 9:59 am
How would this check constraint work in the situation where the insert contains a large subquery or something like that? Does the GETDATE() get generated at the time of insertion, or when the query begins? Probably a simple question that I should already know... 😛
Jared
I was trying to answer the question of the value of getdate() or CURRENT_TIMESTAMP for your question on the value when the insert is from a long running query. 😉
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.
_______________________________________________________________
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/
November 17, 2011 at 10:04 am
p-nut (11/17/2011)
Dev (11/17/2011)
So it seems that it is impossible to have a lower identity value and a later date when GETDATE() is in the insert statement. Got it! Good info and thanks for the great example. 🙂
Aren't you forgetting Identity Inserts?
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }Sorry... I was speaking in general, not necessarily in reference to the exact query given.
Jared
Even I am saying in general. It's possible.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply