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 |
|
QueMark
Starting Member
5 Posts |
 Posted - 2003-03-05 : 20:34:35
|
| Just a simple question. Any performance difference between:INSERT wages VALUES(NULL, NULL, 14000, 4)andINSERT wages (a, b, c, d) VALUES(NULL, NULL, 14000, 4)TIA,Adam |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-05 : 20:40:23
|
| Performance, no. However it's a good habit to always specify the columns you want to insert values into. It makes it perfectly clear which values are going where, and on the off chance the table structure gets changed so that different columns appear, or the order of the columns is changed, the INSERT will still work. This is especially true as far as Nulls are concerned. |
 |
|
|
QueMark
Starting Member
5 Posts |
Posted - 2003-03-05 : 22:23:07
|
| Thanks a lot rob.I agree with specifying the values, it was more of just a nerd debate thing. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-05 : 22:29:56
|
Oh man, you shoulda told me! I didn't see the <nerd debate> tags and just figured it was a standard question!  <nerd debate>Well, no, the performance won't make a difference, except that the optimizer will not have to evaluate the order of the column structure if you specify them explicitly, therefore it will save approximately 0.00000000000001% in CPU cycles per INSERT statement. This can be a huge savings in time if you are inserting more than 100,000,000,000,000 rows in a single transaction. Of course you'll have to set the recovery model to simple, and increase the size of the transaction log, and set it to autogrow, or just put the values into a text file and BULK INSERT it in...</nerd debate> |
|
|
|
QueMark
Starting Member
5 Posts |
Posted - 2003-03-05 : 22:37:20
|
LOL There, that's what I wanted to hear! I'll remember to use the <nerdDebate> tags next time. I knew I was right... |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-05 : 22:45:23
|
| <nerd debate continued>However .... if the SQL is compiled, then there is no performance difference other than the time to parse and compile the SQL the very first time ....</nerd debate continued>- Jeff |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-03-06 : 05:32:45
|
| Why oh why oh why must people post this pseudo-XML that doesn't even have well-formed tags – Spaces in element names, attributes in closing tags, unquoted attribute values... It really isn't good enough! <wink/> |
|
|
|
|
|
|
|
|
INSERT no fields