Sql-server – SQL Data types and SmallInt/TinyInt vs Regular Integer

database-designdatatypessql server

What are the SQL Server benefits of using proper data types?

Example:

  1. SmallInt/TinyInt vs Regular Integer
  2. Decimal(2) vs float
  3. Varchar(100) vs char(100)

Will it really matter these days, with modern computing?

Thanks,

Best Answer

What are the SQL Server benefits of using proper data types?

If you use the right datatype your database will better match your model and is more likely to be efficient both in terms of space & speed. You are at risk of this question being closed as "too broad" because it can be quite a wide subject.

To touch your more specific points:

SmallInt/TinyInt vs Regular Integer

Smaller types take less space so more rows fit in a page resulting in less space taken both on disk and in memory. Of course, if you choose too small a type your application will fail. Consider also BIGINT for when values larger than 2,147,483,648 (or smaller than -2,147,483,647) might be needed.

The difference is multiplied up when you consider indexes: the larger types end up in any index that covers that covers the columns too.

Decimal(2) vs float

There is a huge difference here. DECIMAL (and its synonym NUMERIC) are actually scaled integers that are guaranteed to have the precision you specify. Floating point numbers have their uses but be very careful with them as they are only approximations due to rounding issues converting between binary and decimal forms - the most common example is 0.1+0.2 will usually not equal 0.3. See http://floating-point-gui.de/ amongst many other references for details and run this to see the effect first hand:

SELECT CAST(0.1 AS FLOAT        ) + CAST(0.2 AS FLOAT        ), CASE WHEN CAST(0.1 AS FLOAT        ) + CAST(0.2 AS FLOAT        ) = 0.3 THEN 'OK' ELSE 'oops' END
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)), CASE WHEN CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)) = 0.3 THEN 'OK' ELSE 'oops' END

Essentially: don't use FLOAT unless you know it is what you need.

Varchar(100) vs char(100)

Variable-length types are far more efficient space wise, but less efficient to process CPU-wise. If you use (MAX) they are likely to be stored off-page which is less efficient again and stops compression taking effect if you use that on your tables. If you use row or page compression your fixed length types become variable length anyway but you keep the built-in validation of length which may be useful for your model.

Will it really matter these days, with modern computing?

Short answer: Yes.
Long answer: Yyyyyyyyeeeeeeeeeeeeesssssssssssss!

The considerations can be a little different with modern CPUs - they can be much faster relative to storage and network transfer than they used to be so it is more often a good optimisation to save space at the expense of extra CPU time, but correct type choice very much still matters.

Though type choice matter most for matters other than optimisation: correct types better model your data which should be your first priority.

Related Question