Обзор типов и их размерностей
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://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
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
