none
Insert or Default Faster? RRS feed

  • Question

  • Hey guys,
    Was wondering if anyone could impart some advice as to what was faster - either including a value in my insert statement or creating a default for the column in the sql server table and leaving the insert statement void of that particular column so as to let SQL Server use the default value set for the column.  The value will not change, which is the reason I'm considering just setting the sql server default as opposed to in my code; the upside is that if I ever need to change the value, I can just do it once in SQL Server rather than in several different insert statements in my application, I just don't want to pay a penalty in terms of speed in doing so.

    Your thoughts would be appreciated!

    Thanks!
    Aj
    If at first you don't succeed, skydiving is definitely not for you!
    • Moved by Xinwei Hong - MSFT Tuesday, September 22, 2009 9:36 PM (From:SQL Server Data Access)
    Monday, September 21, 2009 4:21 PM

All replies

  • I would not expect any visible performance difference between the two options. However, if the value you are going to insert is the same value as defined with the DEFAULT constraint, then it may be better to omit that column from the insert statement. (it results in passing less BYTES of data to the server and SQL server will have one less value to validate before performing the insert operation)
    Beyond Relational
    Tuesday, September 22, 2009 3:06 AM
    Moderator
  • I would not expect any visible performance difference between the two options. However, if the value you are going to insert is the same value as defined with the DEFAULT constraint, then it may be better to omit that column from the insert statement. (it results in passing less BYTES of data to the server and SQL server will have one less value to validate before performing the insert operation)
    Beyond Relational
    Perhaps I stated it poorly but I was looking for a contrast between - an insert statement omitting the column that is set in sql server as a default versus inserting the value via from code via a sql command.  Though there may not be a visible difference on 1 or 10 records, I'm concerned that when I go to insert 100,000-300,000 (which will happen from time to time) that the insignificant difference will stack up...I'll run the profiler and see what I get and post if there was any significant difference.  Was just hoping someone had already tested the theory...

    Thanks for the feedback~
    Aj

    If at first you don't succeed, skydiving is definitely not for you!
    Tuesday, September 22, 2009 3:58 AM
  • Aj,

    Did you find out anything from running the profiler?  Your question is the exact question I have. 
    Monday, September 28, 2009 6:33 PM
  • I haven't had time to get to it yet, been buried with other things.  I will post it as soon as I find out...if you beat me to it, please do the same.



    If at first you don't succeed, skydiving is definitely not for you!
    Tuesday, September 29, 2009 12:05 AM