I was just inquiring into what is the difference in performance or what is the best practice in creating tables in sql server. This is for considerably sized datasets.
SELECT <column names>
into <new_table_name>
from <table_name>VS
CREATE TABLE <table_name> as
(
column_name1 datatype(),
column_name2 datatype()
)
INSERT INTO <table_name>(column_name1, column_name2)
SELECT <column_names> FROM <table_name>;Thank you for any insight.
2条答案
按热度按时间bsxbgnwa1#
Marking as answered, thanks to dean. The better choice in my situation is to use the explicit create table. This gives me more control over everything; datatypes, filegroups, compression, keys, indexes, etc.
p5fdfcr12#
All though this is an old post, there did not seem to be much on this. So having dealt with this recently, and having established a tested answer in MS SQL I figured I would share the results.
The basic answer is if you are going for performance, it depends on the MS SQL Server's blackbox. Which means literally, you can only find out which one performs the best by clocking them. We have found that CREATE TABLE with INDEXes then INSERT INTO sometimes is faster than SELECT INTO then create INDEXes and sometimes it is not. Sometimes this difference is minor and sometimes it is fairly significant. We could not per se find a direct rhyme or reason. Further this goes for FullTables as well as #TempTables. Although as a Standard Practice, we never create a FullTable using SELECT INTO we only do that with #TempTables as they are throwaways and we do not need to track those in our GIT repository.
As for best practice, that depends on style and the purpose of the #TempTable. If you are not going to slap an Index on the table then go with the SELECT INTO otherwise you should test to see which one gives you the best performance. As you never know when that will become an issue. Just remember that while SELECT INTO is faster you have to factor in the cost of creating the INDEX(es) afterwards to get a true time cost of using this method.