SQL Data types and SmallInt/TinyInt vs Regular Integer

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.

The benefit of using the proper type is the MEMORY that SQL Server uses to store data of this or that type.

1. TinyInt - 1 byte (can store integer values 0-255)
2. SmallInt - 2 byte (can store integer values -32,768 - 32,767)
3. Int - 4 byte (can store integer value -2^31 - 2^31 -1)
4. BigInt - 8 byte (can store integer value -2^63 - 2^63 -1)

5. Varchar(n) - can store strings with less than or equal n symbols. It will use 1 byte for every single symbol in your string.

   DECLARE @String varchar(100) = 'Hi' -- 2bytes
   DECLARE @String varchar(100) = 'Hello' -- 5bytes
   

6. char(n) - can store strings with less than or equal n symbols. It will use n bytes of memory no mater how many symbols you put in it.

   DECLARE @String char(100) = 'Hi' -- 100bytes
   DECLARE @String char(100) = 'Hello' -- 100bytes
   

You can read differences about float and decimal data types here

It definitely matters even these days even with modern computing. My guess it is going to matter for a very long time. Using proper datatypes can save you MBs. Even GBs sometimes.

Have a look at Aaron Bertrand article here.

It will clarify things for you.