Data types in SQL Server

6. What's the difference between NUMERIC and DECIMAL data types and when do I use them?

There is no difference between NUMERIC and DECIMAL data types. They are synonymous to each other and either one can be used. DECIMAL/NUMERIC data types are numeric data types with fixed precision and scale. DECIMAL (p [, s ]) NUMERIC (p [, s ]) In declaring a DECIMAL or NUMERIC data type, p, which is the precision, specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The s is the scale and it specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale, which defaults to 0 if not specified, must be a value from 0 to the precision value. The following table specifies the storage size required based on the precision specified for the NUMERIC or DECIMAL data type: Precision Storage Size 1 - 9 5 bytes 10- 19 9 bytes 20-28 13 bytes 29-38 17 bytes

7. What's the difference between FLOAT and REAL data types and when do I use them?

FLOAT and REAL data types are both approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented. The differences between these 2 data types are in the minimum and maximum values each can hold as well as the storage size required, as specified in the following table: Data Type n Minimum Value Maximum Value Precision Storage Size float [(n)] 1-24 -1.79E + 308 1.79E + 308 7 digits 4 bytes 25-53 -1.79E + 308 1.79E + 308 15 digits 8 bytes real -3.40E + 38 3.40E + 38 7 digits 4 bytes For FLOAT data type, the n is the number of bits used to store the mantissa in scientific notation and thus dictates the precision and storage size and it must be a value from 1 through 53. If not specified, this defaults to 53. In SQL Server, the synonym for REAL data type is FLOAT(24). If your data requires only a maximum of 7 digits precision, you can either use the REAL data type or FLOAT data type with 24 as the parameter (FLOAT(24)).

SQL SERVER – 2005 Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types

TEXT, NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in future version of SQL Server, SQL Server 2005 provides backward compatibility to data types but it is recommanded to use new data types which are VARHCAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX).
Maximum size supported by TEXT and Varchar(max) are same.
Maximum size supported by NTEXT and NVarchar(max) are same.
Maximum size supported by Image and Varbinary(max) are same.