SQL indexing question

Posted on 2007-08-08
Last Modified: 2010-03-19
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
Question by:Pceledon
    LVL 15

    Expert Comment

    It cannot be a Unique index. Otherwise, shouldn't be a problem.

    Author Comment

    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.
    LVL 15

    Accepted Solution

    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.
    LVL 5

    Expert Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now