Шпаргалка по типам данных

Обзор типов и их размерностей

DATATYPE	MIN	MAX	STORAGE	NOTES
Bit	0	1	1 to 8 bit columns in the same table requires a total of 1 byte, 9 to 16 bits = 2 bytes, etc...
Decimal	-10^38+1	10^38–1	Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes	The Decimal and the Numeric data type is exactly the same. Precision is the total number of digits. Scale is the number of decimals. For both the minimum is 1 and the maximum is 38.
Numeric	same as Decimal	same as Decimal	same as Decimal
Money	От -922 337 203 685 477,5808 до	922 337 203 685 477,5807	8 bytes
Smallmoney	От -214 748,3648	214 748,3647	4 bytes
Float(n)	-1.79E + 308	1.79E + 308	4 bytes when precision is less than 25 8 bytes when precision is 25 through 53	Precision is specified from 1 to 53.
Real	-3.40E + 38	3.40E + 38	4 bytes	Precision is fixed to 7 digits.
Datetime	1753-01-01 00:00:00.000	9999-12-31 23:59:59.997	8 bytes	If you are running SQL Server 2008 or later and need milliseconds precision, use datetime2(3) instead to save 1 byte.
Smalldatetime	1900-01-01 00:00	2079-06-06 23:59
Date	0001-01-01	9999-12-31	3 bytes
Time(n)	00:00:00.0000000	23:59:59.9999999	Presicion 0-2 = 3 bytes Presicion 3-4 = 4 bytes  Presicion 5-7 = 5 bytes	Specifying the precision is possible. TIME(3) will have milliseconds precision. TIME(7) is the highest and the default precision. Casting values to a lower precision will round the value.
Datetime2(n)	0001-01-01 00:00:00.0000000	9999-12-31 23:59:59.9999999	Presicion 1-2 = 6 bytes precision 3-4 = 7 bytes precision 5-7 = 8 bytes	Combines the date datatype and the time datatype into one. The precision logic is the same as for the time datatype.
Datetimeoffset(n)	0001-01-01 00:00:00.0000000 -14:00	9999-12-31 23:59:59.9999999 +14:00	Presicion 1-2 = 8 bytes precision 3-4 = 9 bytes precision 5-7 = 10 bytes	Is a datetime2 datatype with the UTC offset appended

  

Собственные типы данных -- домены 

Allocation_Units

Datetime vs Datetime2

http://sqlhints.com/tag/datetime-vs-datetime2/

http://stackoverflow.com/questions/1334143/sql-server-datetime2-vs-datetime

Decimal vs Numeric

Money vs Decimal vs Float


Nvarchar(max) cautions
http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits

Nvarchar(max) vs Nvarchar(255)
http://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4d9c6504-496e-45ba-a7a3-ed5bed731fcc/varcharmax-vs-varchar255?forum=sqlgetstarted


Implicit convertions

Работа с данными

http://msdn.microsoft.com/en-us/library/ff848728.aspx

Функции по просмотру метаданных типов и колонок

SELECT DATALENGTH(@variable)

SELECT

    SQL_VARIANT_PROPERTY(@variable, 'BaseType'),

    SQL_VARIANT_PROPERTY(@variable, 'TotalBytes'),

    SQL_VARIANT_PROPERTY(@variable, 'MaxLength')

;with cte
(
select * from t
)
select * from cte