SQL Server Performance Forum – Threads Archive

Null Column and Performance

Will a nullable column of a table improve the performance of inserting/loading data in a data warehouse environment? Does SQL Server check for not null contraint during inserting/loading and thus result in performance impact?

Hi,
This depends on your requirement.
If column has constraint for NOT NULL, it will be checked for all inserts.
If your table is happy with having NULL values or if you are adding checks for not inserting null values, then you can remove the constraint. also in your cube/dimension you need to specify way to deal with Null Processing option.

Thanks for your response. I’m actually more concerned about the cost of having a NOT NULL constraint. Will the NOT NULL constraint present a performance impact on the INSERT operstaion? If yes, by how much?

Depends on how you load the data. I have no idea how SSIS is handling this (I guess there is something similar), but BULK INSERT or BCP ignore by default any constraints during load to improve performance. Exceptions are CHECK constraints, but they need to be explicitely mentioned in your command to be enabled. They are also not enabled by default. A normal INSERT checks all constraints on a table. Of course, every check that has to be made hurts performance. How much this is, depends on your specific situation. Perhaps this will help you:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Is this really for SQL 2005 or any SQL 2000 instances are involved? http://sqljunkies.com/Article/96DC9AD5-AC20-4DDD-B073-CB0E2E67B1FA.scuk too. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

From your link <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Here is a quote from "Microsoft SQL Server 2000 Administrator’s Companion" (Microsoft Press): (p. 357) "You should use defaults on your columns instead of allowing null values because operations on nullable columns require more processing overhead than those on non-nullable columns." (p. 371) "You should use NOT NULL instead of NULL whenever possible because operations that deal with null values, such as comparisons, require more processing overhead. As mentioned earlier in this chapter, it is better to use a default, when possible, than to allow null values." <hr height="1" noshade id="quote"></blockquote id="quote"> How cute is that? Looks way too simplified to me. It’s always dangerous when you read such claims out-of-the-blue in books. Most people do not tend to scrutinise whatever is mentioned in a book. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Totally agree and this is why books always comes handy when you’re struck [<img src=’/community/emoticons/emotion-1.gif’ alt=’

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya Totally agree and this is why books always comes handy when you’re struck [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. <hr noshade size="1">Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing.</center> <hr height="1" noshade id="quote"></blockquote id="quote"> Partially. Not all book hold up to what they promise. Even the "best authors" do not know everything and something (more or less often) technical mistakes are not even catched by the technical editor. Reading a book does not free one from using his own brain. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Stupid suggestion – for instance you can only use a NULL to have a blank date. Any default date that you would use would mean you have to double-check the column everywhere so that this specific date is ignored. Less overhead? Not always.

And it’s is not only about the logical implications of that statement, but also the physical ones. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

]]>