none
having null on index col is good or empty is good.

    Question

  • Dear all,

                 I have a col , which has few percentage of null, i want to create index on the col.

    is it good have empty in place of null or null better than empty for an index?

    yours sincerely

    Thursday, January 16, 2014 4:05 PM

Answers

  • SQL Server saves space in your database by using a bitmap in the database pages to indicate which columns are null.

    No it doesn't.

    Regardless of char/varchar, if you store an empty string, it will use just as much space in the table and in the index as compared to NULL. I just tested this (on SQL Server 2008) with 8 million row table.

    Having said that, I agree with everyone that storing NULL is better, if the value is missing or unknown.


    Gert-Jan

    Friday, January 17, 2014 5:48 PM

All replies

  • Huh? No, it's not. When a value is NULL, then it's NULL. Such an modification would change the semantics of your model. Consider using a filtered index.
    Thursday, January 16, 2014 4:08 PM
  • My Option will be NULL.

     

    1. If you add space then it’s unwanted data and unwanted space occupied in the database
    2. When you display the empty records in application it will looks bad..  
    3. Leave the column with NULL and create index.

    Thanks

    JKK
    Thursday, January 16, 2014 4:24 PM
  • I personally would set the column to null rather than setting it to an empty string ('').  SQL Server saves space in your database by using a bitmap in the database pages to indicate which columns are null.

    As others have stated, setting the column to an empty string will cause SQL Server to place an empty string in the index pages for your table which will consume more space, not a lot but more and that's unnecessary.  More space consumed in a page means more pages have to be read to satisfy a query.

    Fewer pages read results in better performance.

    In this case since the percentage of rows containing a null value is low you may not ever be able to perceive a difference but it's better to get into the habit of using nulls in your database than the other way around because next time the percentage of nulls in your table might be high rather than low.

    Thursday, January 16, 2014 6:31 PM
  • SQL Server saves space in your database by using a bitmap in the database pages to indicate which columns are null.

    No it doesn't.

    Regardless of char/varchar, if you store an empty string, it will use just as much space in the table and in the index as compared to NULL. I just tested this (on SQL Server 2008) with 8 million row table.

    Having said that, I agree with everyone that storing NULL is better, if the value is missing or unknown.


    Gert-Jan

    Friday, January 17, 2014 5:48 PM