• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

SQL indexing question

I was wondering how SQL server handles Null values in an indexed column. For example, my database contains cellular call data records and when a call is made or received it puts the event time in the "seizure_time" column and Null on the "sm_delivery_time" column. When and SMS is sent or received it puts the event time in the "sm_delivery_time" column and Null on the "seizure_time" column. I would like to have these column indexed since I do ranged queries the data often. Any suggestions would be appreciated
  • 2
1 Solution
It cannot be a Unique index. Otherwise, shouldn't be a problem.
PceledonAuthor Commented:
But through an efficiancy point of view? I need to use those columns alot for quering but im worried since half the time they will be null. does it make sense to index these columns or is it worth the overhead to create a third column called [event_date] where if seizure_time is null insert sm_delivery_time and vice vera, and index that column for searches.
How many rows are you talking about. Indexes can come into play very efficiently when you are querying a lot of data with a large variance of data. That is the general rule of thumb.

However, if you have 10,000,000 rows of data and a column that the values can be only a, b or c, it doesn't seem to make sense to create an index on that column. However, if 99% of the values are a or b, and you are going to be generating a lot of queries that extract data based on the value being c, it might make sense to create an index (and you will most likely need to use an index hint because SQL Server will probably optimize to just do a table scan).

If you have a lot of data and are going to be doing queries based on those columns, I'd say try the indexes. Nice thing about indexes is you can benchmark without them, create them, benchmark again, and if there is no noticable improvement, drop them. nothing wasted other than your time but that results in a learning experience.
A table can contain max of 250 indexes.
1 Clustred index and 249 Non clustered indexes.
Clustered index will not allow null values.
Non clustreed index allowas null values.
The performance of your quesry will be dfnitely increased if you are providing a normal index to thse columns since they take part in the query very often.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now