Should I care about physical columns in SQL Server?

Question

So far, I only ever looked at the logical columns of my table but never at the physical columns. My motto was : "let SQL Server deal with it".

After reading that article, I wonder if I should not pay some attention to it.

By now, our 5 column table has actually 15 columns in the physical storage format, 10 dropped columns and 5 usable columns. The table uses 412 bytes of fixed space for the 3 fixed length columns that have a total length of only 112 bytes. The variable length columns that now store the first_name and last_name are stored in the record after these 412 reserved bytes for fixed columns that are now dropped. Since the records always consume all the reserved fixed size, this is quite wasteful. How do we reclaim it? Rebuild the table:

Physical columns seems to take useless space but can it also affect performance?

Answer 1

Actually, this is a great article. Life would be a way easier if every developer would ask similar questions. Nowadays almost nobody cares about that but to tell the truth the correct answer is: It depends on.

If you have a relatively small software with several million of records, you will be fine with that. Maybe I could recommend you to create a weekly job that does an online rebuild on each table every Sunday to take care of your tables and indexes (of course, please use to Online=On hint or you will have some surprise :D)

If you have a big software with several million of users and plenty of rows, you must ask yourself similar questions.

If you store a row in 400 bytes instead of 100, it does not affect your performance directly. But indirectly, let imagine: you have larger rows, the pages are filled sooner, you will have more page split that takes time and IO. And, of course, your server has to read more pages to get the same amount of rows because each row is larger. So indirectly, it affects your performance.

I think if you keep some rules in your mind, you will avoid the major problems:

• every fixed length column takes a place. If you have a nullable int column, it will take 4 bytes. If you have a char(10) column, it will take 10 bytes and so on.
• variable length columns take a same amount of bytes as their length multiple by the storage size of one unit. A varchar(10) that stores 'apple' takes 5 bytes (plus some additional info). An nvarchar(10) that stores 'apple' takes 10 bytes.
• Very big rows are stored out of the row data (check the Inside SQL Server books).

So my advice is simple:

• plan your columns well
• avoid to delete or alter a column
• maintain your tables

Answer 2

In brief: yes.

SQL Server moves data around in 8k chunks known as "pages." This includes disk IO. The more cruft there is in a page, the lower the amount of useful information, since the page size is fixed. Therefore to process a certain number of rows will require more IO in a cruft-filled table than in a cruft-free one. This will be slower.

Even if the data is permanently in memory and never updated the management overhead of the extra pages will be fractionally higher.

Similar consideration should be given to choosing data types. A tinyint will use less space than int, which is less than bigint. The difference is only a few bytes but may add up to a considerable amount at data warehouse scales. Blindly defining all strings as char(255) is just plain bad.