May 1, 2003 at 11:14 am
How do NULL values affect the performance of a nonclustered index?
I was just told the theory that there should never be NULL values in transaction tables because it degrades the performance of the indexes. I can't imagine that SQL Server would have a hard time dealing with such a common occurence.
I'll run some tests this afternoon, but wanted to get opinions in the meantime.
Rick Todd
May 1, 2003 at 11:32 am
I have seen no such issue myself. However many don'ts are holdovers from the 16Bit processor days, 32bit processors made considerable leaps in the world of data comparison. In fact used to be that comparing Numeric values were faster than Char values and it was suggested to always use Numeric. That has since changed. However, if anyone has a specific case of NULLS being an issue I can test it and look at the index pages directly to see what is occurring there.
May 1, 2003 at 11:42 am
quote:
In fact used to be that comparing Numeric values were faster than Char values and it was suggested to always use Numeric. That has since changed.
Hmm...I guess I'm still stuck in the 16-bit days, eventhough I never worked with a 16-bit proc. I thought it was always better to compare int's as opposed to varchars. Is this incorrect?
Rick Todd
May 1, 2003 at 4:41 pm
I will try to remember but me and another programmer got into a conversation about this a few months back (we both work with C). When the 16bit processor was in effect character data was compared differently than it is on a 32bit processor (of course app methods can affect this too). ANyway before I spout of what I think was the answer we had documentation on this on the web from MS which he pointed me too. I will ask him on Monday as I am out tomorrow (am truely gone fishin) and get to you.
May 1, 2003 at 5:21 pm
I think I remember now. It was actually discussed at a session at the PASS conference in Seattle last year, with the 2 owners of this site debating indentities vs. not-identities for PKs. IIRC, one of the things mentioned in the argument is the fact that character data now fits into the 32-bit processor in one cycle. This depends on the size of the character data, of course.
Rick Todd
May 2, 2003 at 5:58 am
I've always tried to steer clear of indexing varchar columns, is this a good practice or am I stuck in the 16bit processor days?
May 2, 2003 at 8:25 am
If a column needs an index, you should index it whether it's varchar or int, or whatever.
Even if it's not as efficient to use a varchar(55) than an int, it's still more efficient for SQL Server to read the data from the indexed page (where it's essentially only that column's data and a pointer) than to read it from the whole row, because it can store more data on each page, so you need less I/O.
Hope this helps.
Rick Todd
May 2, 2003 at 8:54 am
Really the major issue there is the fact the number of rows within the index can vary based on the data length of each record. This does cause a bit of overhead but CPUs are so fast these days it is hard to see how much the overhead is.
May 6, 2003 at 6:26 am
NULLs are not indexed.. they there are cause a table scan to be performed.
So, if you have a date field that is 'Date of Death' and search it for everyone that hasn't died, you'll always cause a table scan--which may or may not matter to you, depending on your business requirements.
Alternative would be an agreed upon date to indicate death, or preferrable, another column (bit field) which indicates if death has orrurred.
May 6, 2003 at 7:34 am
I disagree with Chief78CJ7. Nulls are indexed.
You can prove this by creating a simple table, inserting say 1000 non-null rows and a single null row. Add an index and look at the query plan when doing a select on the null row - SQL Server uses the index.
The issue with indexing a column that can contains nulls is down to the number of rows with nulls in the column(s) being indexed.
This is no different to indexing a non-nullable column. If every other row is going to be null, then SQL Server won't bother using the index. It is also likely to cause page splits when inserting or updating the data.
If only a small percentage of rows are going to have nulls, then SQL Server will use the index.
May 8, 2003 at 12:38 am
nulls are indexed. create a unique index on a nullable column and then try to insert 2 rows with null for that column. In that case null = null.
I to took the knowledge for granted that a search on "is null" was non-sargeble and therefor the index might not be used. Recently someone pointed it out to me that that was nolonger the case.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply