October 13, 2011 at 1:12 pm
enforcing not null is always good on a table, and peformance.
But if i code side always ensure that null never happens(and there is no other way it will ever happen lets say)
should i leave the constraint of "allow nulls" checked then it wont "check"
hence nulls are not in the table, and the db doesnt have to take the time to do this "check"
isnt that a better approach?
October 13, 2011 at 1:15 pm
xgcmcbain (10/13/2011)
enforcing not null is always good on a table, and peformance.But if i code side always ensure that null never happens(and there is no other way it will ever happen lets say)
should i leave the constraint of "allow nulls" checked then it wont "check"
hence nulls are not in the table, and the db doesnt have to take the time to do this "check"
isnt that a better approach?
Right up until the time that some process crapped out and you have to manually create the inserts and you forget about your business rule that says fieldx can't be null...
If it is a constraint of the data make the constraint checks as close to the source as possible. That means adding not null to your columns for columns that should not allow nulls. At best you might be saving a fraction of a tick on the processor but opening yourself up to much bigger concerns.
What happens when somebody changes the code and the field is no longer required in the front end???
_______________________________________________________________
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 13, 2011 at 1:16 pm
What if someone updates the table from outside the application? Now you have the possibility of nulls getting inserted.
If the column can never have a null value, specify NOT NULL in the definition of the column.
October 13, 2011 at 1:23 pm
what im doing is giving it a default so it seams checking not null is ambiguous
no outside application has access but myself
October 13, 2011 at 1:24 pm
xgcmcbain (10/13/2011)
enforcing not null is always good on a table, and peformance.
I'm not sure what you mean by this statement, but I want to make clear that enforcing NOT NULL is good when the business rules call for no nulls. However, it is wrong to say that you should never have NULLS allowed in a column. Also, not really sure that it will improve performance either way except for sargeabililty. So it will depend on how you filter your data. Applications should not be maintaining the integrity of the data, this is the job of the database.
Thanks,
Jared
Jared
CE - Microsoft
October 13, 2011 at 1:26 pm
xgcmcbain (10/13/2011)
what im doing is giving it a default so it seams checking not null is ambiguousno outside application has access but myself
Try not enforcing null (even with a default) and see what happens if you omit 'null' that column in an insert.
It doesn't matter that as of today no other application has access. In six months when you have to insert that data you will have forgotten all the details about not enforcing data constraints in the DB and you WILL make a mistake.
The performance change is less than minimal, probably closer to non-existent.
_______________________________________________________________
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 13, 2011 at 1:30 pm
jared-709193 (10/13/2011)
xgcmcbain (10/13/2011)
enforcing not null is always good on a table, and peformance.I'm not sure what you mean by this statement, but I want to make clear that enforcing NOT NULL is good when the business rules call for no nulls. However, it is wrong to say that you should never have NULLS allowed in a column. Also, not really sure that it will improve performance either way except for sargeabililty. So it will depend on how you filter your data. Applications should not be maintaining the integrity of the data, this is the job of the database.
Thanks,
Jared
I read, or heard long ago columns that do not have nulls in them perform faster then ones that do.
(also understanding not null also peforms for business rules and database integrity as well)
October 13, 2011 at 1:41 pm
xgcmcbain (10/13/2011)
jared-709193 (10/13/2011)
xgcmcbain (10/13/2011)
enforcing not null is always good on a table, and peformance.I'm not sure what you mean by this statement, but I want to make clear that enforcing NOT NULL is good when the business rules call for no nulls. However, it is wrong to say that you should never have NULLS allowed in a column. Also, not really sure that it will improve performance either way except for sargeabililty. So it will depend on how you filter your data. Applications should not be maintaining the integrity of the data, this is the job of the database.
Thanks,
Jared
I read, or heard long ago columns that do not have nulls in them perform faster then ones that do.
(also understanding not null also peforms for business rules and database integrity as well)
This is only the case when filtering on nulls such as WHERE ColumnA IS NULL. Research sargable.
Thanks,
Jared
Jared
CE - Microsoft
October 13, 2011 at 1:42 pm
xgcmcbain (10/13/2011)
what im doing is giving it a default so it seams checking not null is ambiguousno outside application has access but myself
You say that now. This could change. Also, try this in a sandbox database:
Create a table with a field that allows a null value and give it a default.
Now insert a null value into that column explicitly.
Query the table and see what you get.
create table dbo.testtab(
NullableCol varchar(10) null default 'empty'
);
insert into dbo.testtab(NullableCol)
values null;
select * from dbo.testtab;
drop table dbo.testtab;
October 13, 2011 at 1:42 pm
So if the business rule is NOT NULL, this table doesn't work because it does NOT support the business rule.
create table #NullTest
(
ID int identity,
NullVal varchar(10) default 'DefaultVal',
OtherVal varchar(10)
)
insert #NullTest default values
insert #NullTest (OtherVal) values ('NoVal')
insert #NullTest (NullVal, OtherVal) values (null, 'OtherVal')
select * from #NullTest
drop table #NullTest
Oops we got a null in our field that we don't want a null.
Now try this one.
create table #NullTest
(
ID int identity,
NullVal varchar(10) not null default 'DefaultVal',
OtherVal varchar(10)
)
insert #NullTest default values
insert #NullTest (OtherVal) values ('NoVal')
insert #NullTest (NullVal, OtherVal) values (null, 'OtherVal')
select * from #NullTest
drop table #NullTest
Boom! Crash! The insert fails. This prevented us from inserting a null into that column. The same will be true of updates on both examples.
_______________________________________________________________
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 13, 2011 at 1:45 pm
it will null, cause your implicity saying null, if i omit the column the default will fire
October 13, 2011 at 1:49 pm
Lynn Pettis (10/13/2011)
xgcmcbain (10/13/2011)
what im doing is giving it a default so it seams checking not null is ambiguousno outside application has access but myself
You say that now. This could change. Also, try this in a sandbox database:
Create a table with a field that allows a null value and give it a default.
Now insert a null value into that column explicitly.
Query the table and see what you get.
create table dbo.testtab(
NullableCol varchar(10) null default 'empty'
);
insert into dbo.testtab(NullableCol)
values null;
select * from dbo.testtab;
drop table dbo.testtab;
this is obvious, but i thanks for the time you spent, you gave it a value i would expect it to be null, im not that much of a rookie
October 13, 2011 at 1:50 pm
xgcmcbain (10/13/2011)
it will null, cause your implicity saying null, if i omit the column the default will fire
I 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.
October 13, 2011 at 1:56 pm
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?
October 13, 2011 at 2:02 pm
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
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply