Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    9

    Put large field in a table of its own?

    I've got an SQL Server 2008 table of about 10k records with maybe 20 fields which are mostly integers and bits but has a description field that's varchar(max) and not called all that often compared to the rest of it.
    Is there any performance gain to be had in putting that field in a linked table of its own as opposed to leaving it all in one table and just not including it in select statements?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Depends on the actual data in the column, however if it is populated with lots of data for lots of rows then the answer is yes. You have basically described a technique called horizontal partitioning.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - empirical knowledge is always rewarding - why not try testing the two designs with sample data? Use REPLICATE() to create some large text values and use SET STATISTICS IO ON to see the effects.

  4. #4
    Join Date
    Jun 2010
    Posts
    9
    Thanks, I'll try that.
    I just wanted to know if I was wasting my time with something the database was already doing transparently.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No, you won't be. Your IO efficiency is essentially determined by how many rows you can fit on a page related to how much of the data in the rows you require to satisfy queries. If you have lots of text in each row then you have less rows per page. To read a row, SQL Server has to read the entire page irrespective of whether or not it actually needs that entire page's data.

    So for the scenario you are describing moving the VARCHAR data into another table and only reading it when necessary will allow more rows per page in the main table.

    Of course, if you are only retrieving single rows at a time then the this greatly reduces the advantage of this design. If you report on this data and return many rows, especially contiguous rows, then it is very efficient. I probably should have mentioned that too originally.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •