Field size versus actual data size

  • When designing a table is the size of the field relevant to database performance, or is it more dependent on the actual size of the resulting data which ends up in the field?

    Example.. if you set the datatype for a field as Varchar (1024) but the longest data is only 19 characters long, what is the impact on performance of the field size setting?

  • AFAIK, varchars under (8000) chars are handled the same; no performance difference when they are included with the rest of the data you are selecting...unless you are SEARCHING tha column with a WHERE statement;

    if you had to search for a value in the column, it will require a tablescan every time. THAT slows things down.

    to improve that issue, you would want to add an index, right> and that is where you need to make the smart decision:

    the max width of an index is ~900 chars...so that means you can create an index on a varchar(900), but not one on a varchar(1024);

    so if the column is ever going to be used in a WHERE statement, you want to do your best to keep them small (or add full text indexing, and all that implies)

    keep in mind that indexes based on smaller columns are more tightly packed into the pages of memory, so they tend to be faster...instead of reading 10 pages, you might read only 1 or two for a specific value that was indexed...if the index was based on varchar(20) instead of varchar(900)

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks....

  • When designing a table is the size of the field relevant to database performance?

    Yes but rather than repeat all of the concerns, do a internet search for

    "sql server" "data type" performance site:sqlblog.com

    Recommend you start with these two posts:

    "Bad habits to kick : choosing the wrong data type" by Aaron Bertrand

    http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/bad-habits-to-kick-using-the-wrong-data-type.aspx

    "Match those types!" by Tibor Karaszi

    http://sqlblog.com/blogs/tibor_karaszi/archive/2009/04/28/match-those-types.aspx

    SQL = Scarcely Qualifies as a Language

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply