right data type quote? RRS feed

  • Question

  • Hi experts,

    I remember on one of the Microsoft books, there was one line that said something like this:

    "Choosing the right data type is the single most important performance decision that you will ever make".

    I am trying to find it, but I can't find it anywere...

    1- did microsoft removed that advice/statement?

    2- did I make that quote up? I do remember reading something like that!

    Any help? thanks...

    Friday, January 29, 2016 11:02 AM


All replies

  • Hello,

    That's still a valid statement for best practice in database design, see e.g. MSDN Pattern & Practice Chapter 14 — Improving SQL Server Performance

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, January 29, 2016 11:12 AM
  • 1- did microsoft removed that advice/statement? - No.

    2- did I make that quote up? I do remember reading something like that! - Hope you got your answer by Mr. Olaf.

    Below link contain tips on "Optimize T-SQL data types in SQL Server"

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Tuesday, February 2, 2016 11:10 AM
  • ha, sorry to be so insistent... but, I couldn't find the exact quote!!


    Thursday, February 11, 2016 3:35 PM
  • I don't see the exact quote, but this seems relevant:

    Devote the Appropriate Resources to Schema Design

    Too many organizations design tables at the last minute when the tables are needed for their queries. Take the time and devote the resources that are needed to gather the business requirements, to design the right data model, and to test the data model. Make sure that your design is appropriate for your business and that the design accurately reflects the relationships between all objects. Changing a data model after your system is already in production is expensive, time consuming, and inevitably affects a lot of code.

    and then

    Choose the Most Appropriate Data Type

    Choose the most appropriate data type, with the appropriate size and nullability. Consider each of the following when you are choosing a data type:

    • Try to choose the smallest data type that works for each column. Also, choose the most appropriate type because both explicit and implicit conversions may be costly in terms of the time that it takes to do the conversion. They also may be costly in terms of the table or index scans that may occur because the optimizer cannot use an index to evaluate the query.
    • Try to avoid nullable foreign key columns to limit the amount of outer joins that might need to be written. Outer joins tend to be more expensive to process than inner joins. If there are cases where the foreign key value may not be known, consider adding a row in the other table that would be the unknown case. Some database architects use one row for the unknown case, one row for the case that is not applicable, and one row for the case that is not determined yet. This approach not only allows for inner joins rather than outer joins, but it provides more information about the actual nature of the foreign key value.
    • Columns that use the text data type have extra overhead because they are stored separately on text/image pages rather than on data pages. Use the varchar type instead of text for superior performance for columns that contain less than 8,000 characters.
    • The sql_variant data type allows a single column, parameter, or variable to store data values of different data types like int and nchar. However, each instance of a sql_variant column records the data value and additional metadata. The metadata includes the base data type, maximum size, scale, precision, and collation. While sql_variant provides flexibility, the use of sql_variant affects performance because of the additional data type conversion.
    • Unicode data types like nchar and nvarchar take twice as much storage space compared to ASCII data types like char and varchar. The speed factors specific to SQL Server are discussed in the article referenced in the following "More Information" section. However, note that strings in the Microsoft .NET Framework and in the Microsoft Windows 2000 kernel are Unicode. If you need or anticipate needing Unicode support, do not hesitate to use them.

    That text is a little bit dated.

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    My TechNet articles

    Thursday, February 11, 2016 6:02 PM