February 10, 2009 at 1:57 am
I don't think the given "correct" answer is in fact "the best way", as was asked. I even doubt if it takes the least effort, as the answer key says.
If table XYZ is in fact a real table from a real database, and not just a throwaway table created for the purpose of doing a quick test, then there will be constraints on the table. There will be at least one PRIMARY KEY or UNIQUE constraint, probably one or two CHECK constraints, maybe a FOREIGN KEY constraint, possible a DEFAULT constraint or two, and of course NOT NULL constraints for most, if not all, columns. Of these constraints, only the NOT NULL constraints will be copied when you use SELECT INTO with an IDENTITY() function. There also will not be a UNIQUE or PRIMARY KEY constraint on the added identity column.
So if you use a SELECT INTO with an IDENTITY() column, you'll next have to run a series of ALTER TABLE statements to add back all these constraints. Each of these (except the DEFAULT constraints) requires a table scan to check if the existing doesn't violate the constraint. And unless you have the system in single user mode, other users might be able to sneak in some bad data before you have the last constraint added. Oops. With an explicit CREATE TABLE, you can add all the constraints before copying the data; the constraints will be checked while the data is copied (with no extra scans required), and other users have no chance of bypassing them. So this is definitely the better way.
It is also more efficient. I can use SSMS to generate a CREATE TABLE script for the existing table with all the constraints, use find and replace to change the table name, and then manually add the IDENTITY column, change the current PRIMARY KEY to UNIQUE (if required) and add a PRIMARYY KEY or UNIQUE constraint on the new column, and that's all I need to do. With the INSERT INTO method, I have to manually key in all the ALTER TABLE statements for the constraints, or generate them one by one (if that is even possible). So the CREATE TABLE / INSERT SELECT method required less typing, not more, making it more efficient for me. Since less scans of the data are needed (see above), it is also more efficient for SQL Server.
Bottom line - INSERT INTO is a nice quick method to get a copy of the data only in a temp table. For making a real copy of the table (presumably as a first step that will be followed by dropping the original and renaming the copy - for why else would I want a copy of the table instead of a view?), INSERT INTO falls terribly short.
February 10, 2009 at 2:13 am
I completely agree with Hugo. You said the "best" way, not the way with the least effort (and potentially not the best result). Besides which if you use Query Analyzer (or whatever you people who've moved off sql2000 have) to script the table creation from the original table - which will also script all of the constraints - then the first way is very little effort whatsoever.
February 10, 2009 at 2:31 am
I agree with the comments above, but on the other hand I understood why the question's "Right" answer had to be the Identity function - I had never heard of it and it is a nice simple solution to a problem that I have encountered many times, needing to create a temp table with an Identity column explicitly instead of simply using Select Into like I usually do when I (really) need a temp table.
The question could do with a little rewording / putting into context, but I learned something new and very interesting today 🙂
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
February 10, 2009 at 2:31 am
I go in line with Hugo's oppinion.
I would assume if anyone choose to use IDENTITY he/she wishes to use this for new rows as well after copying the table, but the question did not give sufficient information on the requirements.
brgds
Philipp Post
brgds
Philipp Post
February 10, 2009 at 3:12 am
Hi all,
First thank to All for your great response. i Just want to share sometime that i knew. Actually lots of time, i need a copy of data with identity column. And that time i use two statements (SELECT INTO, ALTER TABLE). Then i got IDENTITY Function.
So i post it to help other.
Hugo, You are right, I agree with you. But i think you got my point, why i was saying that "Best way".
Thanks for all
Vinay K 🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
February 10, 2009 at 6:27 am
I Agree with Vinay Kumar..
There are sometimes a scenario comes where we just need the data and there are lots other things to complete in a row.. in that case where there are limited resources these small thintgs really help.. I had gone into this situation 2 years back when I have to insert all the values only into another new table with an identity column..
And this was a knowledge what he shared.. and the Quiz is whether we know it or not.. thats all..
Thanks, 🙂
Harsha Bhagat
February 10, 2009 at 6:51 am
Maybe we should start having questions phrased - "what is the most interesting answer..."?
😉
February 10, 2009 at 7:16 am
In my humble opinion, this was a poor choice of question and answer.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 10, 2009 at 7:20 am
Alvin Ramard (2/10/2009)
In my humble opinion, this was a poor choice of question and answer.
Cool, when would now be the time to most "correct" questions to ask?
February 10, 2009 at 7:51 am
I have altered the question to say "easiest way" and I tend to agree with Hugo that a real table would have other constraints on it. However making a copy of a table isn't necessarily something that you worry about people adding data to. It would not have permissions and not likely be known to other people either.
February 10, 2009 at 8:28 am
Good change. Since a select into can cause some undesirable locking behavior it probably wouldn't be the best way to accomplish the task.
February 10, 2009 at 8:34 am
I think the question was clear enough. But Hugo is right about the variations on the definition of "best".
- 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
February 10, 2009 at 8:40 am
GSquared (2/10/2009)
I think the question was clear enough. But Hugo is right about the variations on the definition of "best".
That is why I said this was a poor choice of question and answer. What is "best" is too suggestive and may not have a clear cut answer.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 10, 2009 at 8:42 am
I find if you don't read too much into these questions that they are usually fairly easy to answer. Unfortunately it seems people add a lot of contraints and requirements that aren't even stated.
It was a good question as it brings to the front identity function that some people may not even know about. The QotD is a learning experience, not about best practices or what should or shouldn't be done in a production environment.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply