Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

T-SQL Expand / Collapse
Author
Message
Posted Monday, February 9, 2009 11:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Comments posted to this topic are about the item T-SQL



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #653439
Posted Tuesday, February 10, 2009 1:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #653463
Posted Tuesday, February 10, 2009 2:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 9:13 AM
Points: 1,614, Visits: 905
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.
Post #653477
Posted Tuesday, February 10, 2009 2:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #653483
Posted Tuesday, February 10, 2009 2:31 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:06 AM
Points: 593, Visits: 634
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
Post #653485
Posted Tuesday, February 10, 2009 3:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
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 !!!
www.GrowWithSql.com

Post #653512
Posted Tuesday, February 10, 2009 6:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 3, 2014 4:03 AM
Points: 446, Visits: 660
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
Post #653623
Posted Tuesday, February 10, 2009 6:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 9:13 AM
Points: 1,614, Visits: 905
Maybe we should start having questions phrased - "what is the most interesting answer..."?

;)
Post #653651
Posted Tuesday, February 10, 2009 7:16 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 1,469, Visits: 8,351
In my humble opinion, this was a poor choice of question and answer.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #653682
Posted Tuesday, February 10, 2009 7:20 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 8:50 PM
Points: 20,584, Visits: 9,623
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?
Post #653688
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse