Please enable JavaScript.
Coggle requires JavaScript to display documents.
Data Types (Exact numerics (numeric (fixed precision and scale, valid…
Data Types
Exact numerics
bit
integer with either 0 or 1 value
takes the value of 1, 0 or NULL
TRUE and FALSE string values can be converted to bit values
1 byte
tinyint
integer from 0 - 255
1 byte
smallint
integer from -32,768 to 32,767 (-2^15 to 2^15-1)
2 bytes
int
integer from -2^31 to 2^31-1
4 bytes
bigint
integer from -2^63 to 2^63-1
8 bytes
numeric
fixed precision and scale
valid values range from -10^38+1 through 10^38-1
storage varies
numeric [(p[,s])]
decimal
fixed precision and scale
valid values range from -10^38+1 through 10^38-1
storage varies
decimal [(p[,s])]
smallmoney
monetary or currency values
from -214,748.3648 to 214,748.33647
4 bytes
money
monetary or currency values
from -922,337,203,685,477.508 to 922,337,203,685,477.5807
8 bytes
precision and scale
#
#
Precision (p) is the maximum total number of decimal digits that can be stored, both to the left and right of the decimal point.
the value must be a minimum of 1 and a maximum of 38
the default precision number is 18
Scale (s) reflects the maximum number of decimal digits that can be stored to the right of the decimal point
this must be a value from 0 through p, but it can only be specified if precision is also specified
the default scale is 0
Approximate numerics
datetime
defines a date that is combined with a time of day with fractional seconds based on a 24-hour clock
Range: January 1, 1753, through December 31, 9999
Accuracy: rounded to increments of .000, .003, or .007 seconds
8 bytes
smalldatetime
defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always (:00), meaning there are no fractional seconds
Range: 1900-01-01 through 2079-06-06.
Accuracy: 1 minute
4 bytes
date*
defines a date.
Range: 0001-01-01 through 9999-12-31
Accuracy: 1 day
3 bytes
time*
defines a time of day. This time is without time-zone awareness and is based on a 24-hour clock.
Range: 00:00:00.0000000 through 23:59:59.9999999
Accuracy: 100 nanoseconds
5 bytes
datetimeoffset
defines a date that is combined with a time of day that has time-zone awareness and is based on a 24-hour clock
Range: 0001-01-01 through 9999-12-31
Range: 00:00:00 through 23:59:59.9999999
Accuracy: 100 nanoseconds
10 bytes
datetime2
defines a date that is combined with a time of day that is based on a 24-hour clock
Range: 0001-01-01 through 9999-12-31
Range: 00:00:00 through 23.59.59.9999999
Accuracy: 100 nanoseconds
Storage varies
Unicode character strings
nchar
character data type with fixed length
storage varies
nvarchar
character data type with variable length
storage varies
ntext
this data type will be removed in future SQL releases; therefore, use nvarchar(max) instead
storage varies
Unicode means multilingual text is supported
Character strings
char
character data type with fixed length
storage varies
varchar
character data type with variable length
storage varies
text
this data type will be removed in future sql releases; therefore, use varchar(max) instead
storage varies
Binary strings
binary
binary data with fixed length
storage varies
varbinary
binary data with variable length
storage varies
image
this data type will be removed in future sql releases; therefore; use varbinary(max) instead
storage varies
When two expressions have different data types, collation, precision, scale or length, the characteristics of the results will be determined ass follows:
When two expressions (mathematical functions or comparison functions) have different data types, rules for data type precedence specify that the data type with the lower precedence is converted to the data type with higher precedence
Collation refers to a set of rules that determine how data is sorted and compared. By default, SQL Server has a predefined collation precedence. If you wish to override how data is being sorted, you must use a collation clause.
The precision, scale and length of the result depend on the precision of the same in the input expression. In other words, if you take several different values and perform a mathematical operation on those values, the precision, scale and length will be based on those values on which you are performing the mathematical operations.
IMPLICIT CONVERSIONS
when a conversion is done automatically, it is called an implicit conversion
some implicit conversions are not allowed
if you have a reason to force a conversion, you can use the Cast and Convert functions
Cast is compliant with ANSI standards which allow you to import and export to other dbms's
Convert is specific to T-SQL but is a little more powerful
Other data types
sql_variant
stores values of various SQL Server supported data types, except text, ntext, image, timestamp and sql_variant
storage varies
uniqueidentifier (UUID)
16-byte GUID
16 bytes
Each column, local variable, expression and parameter always has a related data type and each of the data types is an attribute.
When n is not specified within a data definition or variable declaration statement, the default length is 1. When n is not specified within the Cast function, the default length is 30.
SQL Server support only two character string types: Unicode and regular
#
#