March 17, 2008 at 4:11 pm
hai,
i have a question
suppose u hav a million row table
to this table u hav to add a new column with default constraint nd it is not null column
now is there any performance impact becoz of the usage of default
if present what is the impact and how can u overcome it
and if there are any changes taT can b made to the script wat r they?
March 17, 2008 at 10:58 pm
No real impact from the default constraint itself - it will be a lengthy operation because the column is not null, meaning that every row must be updated with the default value.
March 18, 2008 at 5:30 am
After it gets applied, any impact is dependent on the number of rows inserted or updated against which an assertion for this constraint must be made. The more rows immediately affected the greater the impact on that transaction. Also, the type of constraint will affect performance. A simple ISNUMERIC or default to GETDATE() or something like that will have very minimal impact. A complex query against other columns or tables could have very serious impact. Check your execution plan to see how much it affects operations. Probably it will be none to negligible, but testing is always a must.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 18, 2008 at 6:48 am
Sounds like another "Sr. Project".
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2008 at 10:03 am
thank u very much
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply