October 13, 2011 at 2:03 pm
Defaults and not null should be declared in the schema itself.
October 13, 2011 at 2:08 pm
Also, going back to your very first question... It is not "checking" to see if it is null or not. It simply won't allow a null. It is not the same as checking a range constraint. So either way it will not affect your performance. However, if you DO allow nulls it "MAY" affect your performance if you have to start using ISNULL() or WHERE ColumnA IS NULL.
Thanks,
Jared
Jared
CE - Microsoft
October 13, 2011 at 2:14 pm
jared-709193 (10/13/2011)
xgcmcbain (10/13/2011)
Lynn Pettis (10/13/2011)
xgcmcbain (10/13/2011)
it will null, cause your implicity saying null, if i omit the column the default will fireI think you are missing the point. If the column cannot be null, specify it in the definition of the column.
You may be the only one with external access to the tables right now, but are you going to be the only one with that access from now until the database (and its application) are retired?
Protect the data.
actually in this case nulls wouldnt kill the process or any app using it, the old one im upgrading allowed anything.
Im trying understand that peformance issue. Business or data is not an issue in this instance, but i know how you all think and understand how you responded thinking omg a null can still come in.(which i explained is not the end of the world)
the topic is peformance
i wanted to know
1) how much in a table of millions of records, and 50 columns , how much "not null" costs
vs
2) how much "default" costs
vs
3) triggers have to fire on both these tables for other reasons, is it better to assign the defaults and enforce not null there, then in the schema itself?
omg? really? You still don't get it. Defaults don't enforce the not null, they assign a default. The "check" for NULL when you have a column set to NOT NULL will do nothing to your performance. Default will do nothing to your performance. If nulls should not be allowed, then set it to not null on the column. If there IS a reason to insert nulls, then make it nullable.
Jared
Omg you still dont get it. I understand defaults do not enforce null. THE DISCUSSION IS PERFORMANCE WHICH SOMEBODY ANSWERED THANKFULLY.
But if i know, nulls will not get in because i only have access. One simple stored procedure is executing omitting these columns, i just wanted to talk performance, not database freaking integrity i said it 3 freaking times, being polite twice.
Mod close the thread, you have somebody doing controlled substances in this thread.
October 13, 2011 at 2:18 pm
jared-709193 (10/13/2011)
Also, going back to your very first question... It is not "checking" to see if it is null or not. It simply won't allow a null. It is not the same as checking a range constraint. So either way it will not affect your performance. However, if you DO allow nulls it "MAY" affect your performance if you have to start using ISNULL() or WHERE ColumnA IS NULL.Thanks,
Jared
finallly, you get the question, which happens to be, the very first question!
October 13, 2011 at 2:20 pm
xgcmcbain (10/13/2011)
jared-709193 (10/13/2011)
Also, going back to your very first question... It is not "checking" to see if it is null or not. It simply won't allow a null. It is not the same as checking a range constraint. So either way it will not affect your performance. However, if you DO allow nulls it "MAY" affect your performance if you have to start using ISNULL() or WHERE ColumnA IS NULL.Thanks,
Jared
finallly, you get the question, which happens to be, the very first question!
Umm... I actually answered your question in my first response saying that there would not be a performance issue either way except for sargable filters. You just seem to like to argue your bad database design against our suggestions, which are meant to not only help you, but others who read this post.
Jared
Jared
CE - Microsoft
October 13, 2011 at 2:23 pm
xgcmcbain (10/13/2011)
But if i know, nulls will not get in because i only have access.
How do you know nulls will never get in? Will you always be the only one with access to the database from outside the application?
You have to consider database integrity and performance. It isn't an either or proposition. If a column cannot have a null value, specify that in the declaration of the column. If it can be null, specify that as well.
If a not nullable column is not included in an insert statement declare a default value for the column.
If a nullable column is not included in an insert statement and there should be a default value, declare that as well.
October 13, 2011 at 2:45 pm
finallly, you get the question, which happens to be, the very first question!
The very first person who responded to your question...
At best you might be saving a fraction of a tick on the processor but opening yourself up to much bigger concerns.
It seems that the bigger concerns have lasted into the third page of responses. Seems to me it is pretty obvious, it does not improve performance at all. It has at least a potential of decreasing your performance, and has a very high risk of not enforcing business rules and/or referential integrity.
You seem dead set on doing what everyone in this thread says is a bad approach. Go for it. You will not increase your performance at all and it is you who has to deal with the fallout of bad data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 7:46 am
Sean Lange (10/13/2011)
finallly, you get the question, which happens to be, the very first question!
The very first person who responded to your question...
At best you might be saving a fraction of a tick on the processor but opening yourself up to much bigger concerns.
It seems that the bigger concerns have lasted into the third page of responses. Seems to me it is pretty obvious, it does not improve performance at all. It has at least a potential of decreasing your performance, and has a very high risk of not enforcing business rules and/or referential integrity.
You seem dead set on doing what everyone in this thread says is a bad approach. Go for it. You will not increase your performance at all and it is you who has to deal with the fallout of bad data.
nope from the responses, i actually dont worry about the "tick" and it understand now it would hurt it more if a null came in. I wasnt dead set on anything.
Just everyone didnt understand, null would never get it, by my own design and security and rules and using the jedi force, just wanted to know the cost of not null and default etc and i got the answer. Others went off on tandoms, and twice i responded politely,understanding people missed my statement and did not understand the jedi force is not null enforcment.
October 14, 2011 at 7:54 am
xgcmcbain (10/14/2011)
[snip] ...people missed my statement and did not understand the jedi force is not null enforcment.
I guess the real deal here is that we all have bitten by the jedi force trying to enforce null. It just doesn't work in the real world. There is ALWAYS something unforeseen that crushes the force with data integrity.
Sorry if we abducted your thread into a diatribe of the proper way to enforce not null. We tend to be people that don't just simply answer the question but look at the big picture. It is what we do. We are honestly just trying to share our experience and knowledge to help others avoid the same painful mistakes we made.
Short answer to your original question: There is no performance gain and imho you will be better off with your constraints in the database.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply