27

I have table that holds from very basic info. Just a title and a few date fields. There's one field called comments which is varchar(4000) Most of the time we leave it blank, but some times will enter a large amount of data here. Is this a really bad design? Or is this just slightly inefficient?

I would assume the creating a separate table for this column would be better.

note: this is sql server 2008

enter image description here

aron
  • 595
  • 2
  • 6
  • 8
  • 1
    Thanks for your feedback everyone! I decided to keep it simple and keep the column in the table and not put it into another table. However I used the SPARSE feature in SQL 2008 so the field does not use any space. –  Aug 16 '11 at 21:27
  • 2
    Just curious, what is "most of the time"? How many rows total, and what percentage has a value here? Just wondering if you're planning to do any space/performance comparisons using SPARSE and not using SPARSE... – Aaron Bertrand Aug 23 '11 at 00:14

5 Answers5

13

It takes minimal space when not used

  • one bit in the NULL bitmap
  • two bytes for length (which will be zero when NULL)

The overhead is minimal and optimisation will be premature.

Until you know you have an issue, just keep it in one table. You break KISS by introducing outer joins and add an overhead in querying the data.

See https://stackoverflow.com/questions/3793022/how-to-come-to-limits-of-8060-bytes-per-row-and-8000-per-varchar-nvarchar-valu/3793265#3793265 for more

Community
  • 1
gbn
  • 69,809
  • 8
  • 163
  • 243
11

I think a separate table would be better to improve page density and reduce fragmentation, especially if you don't always populate that field.

  • A data page holds around 8000 bytes
  • You have some rows with say 100 bytes and some rows with over 4000 bytes
  • Those long rows will be on a page by themselves, and the rest of the page is "wasted" space that your DB takes up but will likely never hold data
  • If you add data to that long field for a record on a mostly-full page, it will likely overrun the page and result in a pointer to the page with the rest of the record

All these empty pages and pointers lead to poor performance. Normalize that field if you can.

JNK
  • 17,956
  • 5
  • 59
  • 97
9

For more predictable performance (and to avoid having high variation of rows per page), I would lean to storing this data in a related table - especially if it is only populated a small percentage of the time, and especially if it is only retrieved in some of the queries. The rows where this value is NULL do contribute to space overhead, but this is minimal. More important will be how one page might only fit two rows and the next page can fit 500 rows - this can really impact statistics and you might be better off splitting this out so it is stored separately and doesn't impact all of your operations on the core table.

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
4

This question looks very similar: do extra empty columns affect sql table size significantly?

It looks like the answer is yes it does take up space, but there is a compression algorithm for columns with lots of null values.

As far as design, I think that having an external table linked to this would be a cleaner design. Having a column with frequent null values makes it harder on the users of the database since they could accidentally use a null value if they are not careful. Therefore, the code using the database would need to contain error checking and it just gets ugly from there.

Community
  • 1
  • 2
    To be explicit, the compression algorithm only applies to those columns explicitly defined as SPARSE, not just "columns with lots of null values." – Aaron Bertrand Aug 16 '11 at 18:05
2

You will be fine - it's already a varchar column, so it only uses space when it contains data. If you had a lot of nullable fixed-size columns like int, you might have issues of space usage.

As far as putting it in another table, I wouldn't bother. You could also look at using varchar(max) and the in/out of row options. Again, probably premature.

Community
  • 1
Cade Roux
  • 6,602
  • 1
  • 31
  • 54
  • 1
    Premature optimization can often be a real issue, but that depends on the cost of refactoring later. If you know today that only 1% of your rows will have data in this column, and you expect the table to grow large over time, what is the value in persisting that data in the current table only to suffer consequences as you scale? I'm all for avoiding premature optimization, but there is a point when I weigh the long-term effect of doing so. – Aaron Bertrand Aug 16 '11 at 19:29
  • 1
    @Aaron Bertrand Agreed. People ask performance questions here and it's easy to assume they may have an app which is millions of rows and they need to use every weapon in the toolkit and keep all that in mind. On the other hand, sometimes the user seems to be on the beginning of a learning curve and it's hard to ask them to commit time to something which should probably be lower on their priorities. Also, with varchar(max), you effectively can flick a switch to start storing out of row. I think the real answer here is "You haven't really given us enough information to give a definitive answer". – Cade Roux Aug 16 '11 at 19:38