Save Space To Boost SQL Server Performance

Space Saving Hints

Consider the following suggestions for saving space:

• Use VARCHAR instead of CHAR, unless your data is almost always of a fixed length, or is very short.
• Using Unicode double-byte datatypes such as NCHAR and NVARCHAR take up double (Duh!) the space, so avoid them unless you really need them.
• Use SMALLINT and TINYINT to save one or three bytes a time if you do not need the big numbers, and use integers instead of Float or Numeric wherever suitable.
• Using SMALLDATETIME instead of DATETIME saves four bytes, if accuracy to the nearest minute is good enough.
• Avoid using GUID columns unless you really need them

These are just a few examples, and you should familiarize yourself with the whole range of datatypes in SQL Server, and choose from them very carefully. You might choose to use the SMALLMONEY data type instead of the MONEY type to save 4 bytes a time, but the values this data type can handle are comparatively small, especially if you are dealing with currencies like Japanese Yen or Italian Lira. If you choose a data type that you will eventually outgrow, then this will cause more problems than it’s worth.

Index Considerations

Remember that indexes also take up space, so if you keep your indexes small (create only indexes that you are going to use, use narrow columns, and refrain from using long compound indexes if possible) you can improve performance this way too.

Read up on the Fillfactor and Pad_Index options for indexes. In general, SQL Server leaves blank space in it’s indexes to allow for later additions, but if you are indexing a table that never, or very rarely, changes, then you can adjust the fill factor to save space and increase performance.

For tables that change more often, it’s important to do regular table and index maintenance to keep your data compact and efficiently accessible.

Other Benefits

Keeping your data as compact as possible does not only reduces the size of your data on disk, it provides other benefits too:

• You can fit more data into your cache RAM, increasing your cache hit ratio and reducing disk I/O even further.
• Smaller and faster backups.
• Less traffic when moving data over the network.
• Faster joins (short columns are easier to compare than long ones).

Further Reading

All the following subjects are well documented in the SQL Server Books Online.

• SQL Server Datatypes
• Estimating Space Usage
• Choosing Efficient Indexes
• Reading Query Execution Plans
• Table and Index Maintenance