August 6, 2014 at 4:05 pm
I ran across this and just can't understand why someone would do this?
(Table names changed to protect the innocent)
INSERT INTO dbo.Ouroboros
SELECT *
FROM dbo.Ouroboros AS o
I just don't understand why you would ever do this on purpose?
1. It just doubles all rows in the table.
2. Would it ever NOT do that? Would you ever get a batch scenario where you read maybe 85% of the tables rows and it begins inserting before it completes the read so it enters a sort of race condition?
August 7, 2014 at 1:01 am
Maybe just for enlarging the row count for performance testing?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 7, 2014 at 4:38 am
Yeah, that was all I could come up with, just wanting more rows, because on a production system it just seems like it would be pointless duplication of rows...
I just wondered if other than that there was a valid reason for it or some non-obvious effect it had. (I would get it if they ran it to copy data to a different table of course)
Edit: fixed typos due to posting from my phone
August 7, 2014 at 4:42 am
Maxer (8/6/2014)
1. It just doubles all rows in the table.
Yes
2. Would it ever NOT do that? Would you ever get a batch scenario where you read maybe 85% of the tables rows and it begins inserting before it completes the read so it enters a sort of race condition?
No. Not possible. It either doubles the rows in the table or does nothing (if it hits an error)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2014 at 7:44 am
Could it have been a before/after test for the addition of a primary key or unique index? It would fail if the table has either.
August 7, 2014 at 9:29 am
Would it "ever not do that"?
The INSERT could fail if it ran out of physical file space to hold the new rows.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 7, 2014 at 10:10 am
Good point!
I was wondering about odd scenarios with dirty read for that select or if it could ever fail to read 100% of the rows up front so it attempts to read the first 80%, inserts those and comes back for the rest or does a rolling insert where it basically never completes it all in the first pass.
Nothing comes to mind but it made me wonder. I played with a few things on my local but could never create a scenario where I didn't get a perfect doubling of rows each time the statement ran.
Which is good that is what should happen, was just curious about odd edge cases, etc... Times where maybe it needed to be wrapped in an explicit transaction with rollback and error handling, but it is a single statement so it should all be an implicit transaction I would think.
August 7, 2014 at 10:56 am
Maxer (8/7/2014)
I was wondering about odd scenarios with dirty read for that select or if it could ever fail to read 100% of the rows up front so it attempts to read the first 80%, inserts those and comes back for the rest or does a rolling insert where it basically never completes it all in the first pass.
Nope. It's the A in ACID. Atomic. The operation will either succeed (insert the rows) or fail (insert none)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply