July 26, 2011 at 8:41 am
Hello everyone,
why are SELECT INTOs bad? My manager asks the developers to use an explicit CREATE TABLE command instead of a SELECT INTO. Can someone please explain the reason behind this?
Thanks,
Sunny.
July 26, 2011 at 8:51 am
They're not "bad". They might come with some undesired side effects when used improperly. The major one is the data types for the fields are determined by the data being inserted. You just have more control over an explicit CREATE TABLE statement.
For the quick and dirty testing or quick population of a temp table, with care they are fine. For database design and scripting, I'd go with CREATE statements.
July 26, 2011 at 8:58 am
In addition to the benefits already mention the following list some pros and cons.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 26, 2011 at 9:07 am
They're not bad. They used to be bad back in SQL 7 (or maybe 6.5), that's where the story that they are bad originates. It's not true any longer, hasn't been for over 10 years.
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
July 26, 2011 at 9:24 am
Most people who think they're bad are acting on old data (as Gail mentioned). They can be bad if the underlying tables routinely have schema changes, and you need those to NOT come through in the code that's doing the Select Into or Create Insert Select.
The main advantage/disadvantage to them these days is that data types and sizes are carried forward by Select Into, and aren't by Create Insert Select.
So, if you have to expand a varchar field in an underlying table to take a new, larger value, Select Into will automatically increase the size in the resulting table, while Create Insert Select will need to have the code refactored to handle the larger data.
The older datum, no longer true in versions of SQL Server newer than version 7, was that Select Into would take a schema lock on the database where the table was being created (often tempdb), and that would interfere with all kinds of things. That was fixed in SQL 2000, but a lot of people don't know that.
- 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply