It’s always been important in SQL Server to specify the exact length and precision of number data types. This is because SQL Server has always had just fixed length internal storage for numbers.
For example, if you have a whole number to store but it can only been one or two digits long then declare it as SMALLINT.
Recently I converted some tables from Oracle to SQL Server for benchmark testing. Initially I left all the data type lengths the same as they were in Oracle (Oracle only has variable internal number storage).
It seemed to take too long to load data into this table from a file, so I changed the data types of numeric fields to be just big enough for the data. My load time halved! (and so did the space I was using for the table).
Then I remembered that SQL Server 2005 introduced variable number storage. The trick is you have to turn it on. So I changed the table to have decimal data types for all numbers and turned on variable number storage by running the following:
- sp_db_vardecimal_storage_format 'DatabaseName', 'ON'
- sp_tableoption 'TableName', 'vardecimal storage format', 'ON'
I ran the load again and the table did take up a lot less space in the database, but the load time had increased. I can only assume that the cost of calculating the size of the column somehow slowed things down…