Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2007-03-17 : 11:56:08
|
| The following query was asked in an Interview. Whcih was better?CREATE TABLE T1 ()INSERT T1SELECT * FROM T2 OR SELECT * INTO T2 FROM T1 Can any one plaese explain the difference?------------------------I think, therefore I am - Rene Descartes |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-17 : 13:14:59
|
SELECT * INTO T2 FROM T1This runs the risk of locking the "Create table" task for the duration of the SELECT part. particularly important for a #TEMP table (which could otherwise block other Create Table actions in TEMPDB)Also an issue, when creating a #TEMP table, which might be using a different collation. I suppose folk MIGHT put COLLATE statements on all the Char/Varchar columns in the SELECT, but that seems unlikely! Whereas it is much easier to spot the Char/Varchar data types in a CREATE TABLE statement (our house rules REQUIRE that all Char/Varchar in Create Table have a COLLATE statement - in case our database is installed on a system with a different default collation)Pre-create the table is more likely to cause you to add an appropriate PK, and allows the optimiser to "see" the structure of the table before it is used, and usually leads to better query plans.Some of these issues may have been mitigated in SQL2005 though ...1) Did I get the job?2) Does it pay well?  Kristen |
 |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-03-17 : 14:06:39
|
| Thank you Kristen! You would have got the job. I have asnwered similarly in the interview, waiting for the result.------------------------I think, therefore I am - Rene Descartes |
|
|
|
|
|
|
better query