locked
Data Type RRS feed

  • Question

  • Hi,

    Can you please suggest, If column contains multiple null values, what is the preffered datatype in SQL Server 2005.

    Regards,

    Shaun

    Thursday, December 26, 2013 1:03 PM

Answers

  • Based on the % of NULL values you can mark the column as SPARSE column.

    More details,

    http://msdn.microsoft.com/en-in/library/cc280604(v=sql.105).aspx

    http://letuslookintosqlserver.blogspot.in/2013/03/sparse-columns.html


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Fanny Liu Monday, January 6, 2014 9:51 AM
    Thursday, December 26, 2013 1:11 PM
    Answerer
  • Hi ,

    Like Sarat answered , Sparse columns are ordinary columns that have an optimized storage for null values , but it is available only from SQL Server 2008 .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Proposed as answer by Katherine Xiong Friday, December 27, 2013 7:24 AM
    • Marked as answer by Fanny Liu Monday, January 6, 2014 9:51 AM
    Thursday, December 26, 2013 4:53 PM
  • datatype choice has nothing to do with NULL value presence. All datatypes will support storing NULL values. But from storage perspective you can make column as SPARSE as others pointed out in which case its uses optimised storage for NULL values. You can also create a column set and use it for DML operations instead of using SPARSE columns separately

    See

    http://visakhm.blogspot.in/2010/03/sparse-columns-and-column-sets-in-sql.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Fanny Liu Monday, January 6, 2014 9:51 AM
    Thursday, December 26, 2013 6:32 PM
    Answerer

All replies

  • Based on the % of NULL values you can mark the column as SPARSE column.

    More details,

    http://msdn.microsoft.com/en-in/library/cc280604(v=sql.105).aspx

    http://letuslookintosqlserver.blogspot.in/2013/03/sparse-columns.html


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Fanny Liu Monday, January 6, 2014 9:51 AM
    Thursday, December 26, 2013 1:11 PM
    Answerer
  • Hi ,

    Like Sarat answered , Sparse columns are ordinary columns that have an optimized storage for null values , but it is available only from SQL Server 2008 .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Proposed as answer by Katherine Xiong Friday, December 27, 2013 7:24 AM
    • Marked as answer by Fanny Liu Monday, January 6, 2014 9:51 AM
    Thursday, December 26, 2013 4:53 PM
  • datatype choice has nothing to do with NULL value presence. All datatypes will support storing NULL values. But from storage perspective you can make column as SPARSE as others pointed out in which case its uses optimised storage for NULL values. You can also create a column set and use it for DML operations instead of using SPARSE columns separately

    See

    http://visakhm.blogspot.in/2010/03/sparse-columns-and-column-sets-in-sql.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Fanny Liu Monday, January 6, 2014 9:51 AM
    Thursday, December 26, 2013 6:32 PM
    Answerer
  • It's a good idea to test whether sparse columns will actually save you storage space, as they do add some 'housekeeping' overhead and some overhead to retrieving non null values. I was building a table recently that had a number of columns, many of which had the potential to be mostly null and I did some A-B testing with table sizes for sparse and non-sparse columns and did not find a lot of savings. Of course YMMV. (In the end page compression saved me much more space)

    Of course - neither of these options are available for SQL 2005 so the best option is to use the datatype that is most appropriate for your non-null values.


    Regards, Matt Bowler MCITP, My Blog


    Friday, December 27, 2013 1:17 AM
  • Hi,

    Can you please suggest, If column contains multiple null values, what is the preffered datatype in SQL Server 2005.

    Regards,

    Shaun

    One null is no difference from 2 or more nulls, as the column is still having a NULL value. What data type you need, is only depending on what you want to define for it and what you want to store inside that. Read

    Data type


    Many Thanks & Best Regards, Hua Min


    Friday, December 27, 2013 1:41 AM
    Answerer