SQL Server - Data Types

Data Types

 In this post I have collected the information from micro soft site and keep all the same in a single page. Please find the details

   Data types are the heart of the SQL language, as developer or designer we need to see the every data as data type, how we can store the data in the database.

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.

In SQL Server, based on the characteristic data types grouped as below categories

Exact Numerics	Unicode character strings
Approximate Numerics	Binary strings
Date and time	Other data types
Character strings

 

           

 

 

 

 

Exact Numerics

a.     bigint

b.     numeric

c.      bit

d.     smallint

e.     decimal

f.      smallmoney

g.     int

h.     tinyint

i.      money

Approximate numerics

a.     float

b.     real

Date and Time

a.     date

b.     datetimeoffset

c.      datetime2

d.     smalldatetime

e.     datetime

f.      time

Character Strings

a.     char

b.     varchar

c.      text

Unicode Character Strings

a.     nchar

b.     nvarchar

c.      ntext

Binary Strings

a.     binary

b.     varbinary

c.      image

Other Data Types

a.     cursor

b.     rowversion

c.      hierarchyid

d.     uniqueidentifier

e.     sql_variant

f.      xml

g.     Spatial Geometry Types

h.     Spatial Geography Types

i.       table

int, bigint, smallint, and tinyint

Exact-number data types that use integer data. To save space in the database, use the smallest data type that can reliably contain all possible values. For example, tinyint would be sufficient for a person's age because no one lives to be more than 255 years old. But tinyint would not be sufficient for a building's age because a building can be more than 255 years old.

Data type	Range	Range expression	Storage
bigint	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	-2^63 to 2^63-1	8 Bytes
int	-2,147,483,648 to 2,147,483,647	-2^31 to 2^31-1	4 Bytes
smallint	-32,768 to 32,767	-2^15 to 2^15-1	2 Bytes
tinyint	0 to 255	2^0-1 to 2^8-1	1 Byte

Remarks

The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.

bigint fits between smallmoney and int in the data type precedence chart.

Functions return bigint only if the parameter expression is a bigint data type. SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

decimal and numeric 

Numeric data types that have fixed precision and scale. Decimal and numeric are synonyms and can be used interchangeably.

Arguments

decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally identical to decimal.

p (precision)
The maximum total number of decimal digits to be stored. This number includes both the left and the right sides of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)
The number of decimal digits that are stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. Scale must be a value from 0 through p, and can only be specified if precision is specified. The default scale is 0 and so 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Precision	Storage bytes
1 - 9	5
10-19	9
20-28	13
29-38	17

Converting decimal and numeric data

For decimal and numeric data types, SQL Server considers each combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

Converting from decimal or numeric to float or real can cause some loss of precision. Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow.

By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. Conversely, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow occurs. Loss of only precision and scale isn't sufficient to raise an error.

Prior to SQL Server 2016 (13.x), conversion of float values to decimal or numeric is restricted to values of precision 17 digits only. Any float value less than 5E-18 (when set using either the scientific notation of 5E-18 or the decimal notation of 0.000000000000000005) rounds down to 0. This is no longer a restriction as of SQL Server 2016 (13.x).

money and smallmoney

Data types that represent monetary or currency values.

Data type	Range	Storage
money	-922,337,203,685,477.5808 to 922,337,203,685,477.5807 (-922,337,203,685,477.58 to 922,337,203,685,477.58 for Informatica. Informatica only supports two decimals, not four.)	8 bytes
smallmoney	- 214,748.3648 to 214,748.3647	4 bytes

The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent. For Informatica, the money and smallmoney data types are accurate to a one-hundredth of the monetary units that they represent.Use a period to separate partial monetary units, like cents, from whole monetary units. For example, 2.15 specifies 2 dollars and 15 cents.These data types can use any one of the following currency symbols.

Currency or monetary data does not need to be enclosed in single quotation marks ( ' ). It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value.

Converting money data

When you convert to money from integer data types, units are assumed to be in monetary units. For example, the integer value of 4 is converted to the money equivalent of 4 monetary units.

The following example converts smallmoney and money values to varchar and decimal data types, respectively.

float and real

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. The ISO synonym for real is float(24).

Syntax

float [ (n) ] Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.

n value	Precision	Storage size
1-24	7 digits	4 bytes
25-53	15 digits	8 bytes

The SQL Server float[(n)] data type complies with the ISO standard for all values of n from 1 through 53. The synonym for double precision is float(53).

Remarks

Data type	Range	Storage
float	- 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308	Depends on the value of n
real	- 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38	4 Bytes

Converting float and real data

Values of float are truncated when they are converted to any integer type.

When you want to convert from float or real to character data, using the STR string function is usually more useful than CAST( ). This is because STR enables more control over formatting. For more information, see STR (Transact-SQL) and Functions (Transact-SQL).

Prior to SQL Server 2016 (13.x), conversion of float values to decimal or numeric is restricted to values of precision 17 digits only. Any float value less than 5E-18 (when set using either the scientific notation of 5E-18 or the decimal notation of 0.000000000000000005) rounds down to 0. This is no longer a restriction as of SQL Server 2016 (13.x).

date 

Defines a date in SQL Server.

date description

Property	Value
Syntax	date
Usage	DECLARE @MyDate date CREATE TABLE Table1 ( Column1 date )
Default string literal format (used for down-level client)	YYYY-MM-DD For more information, see the "Backward Compatibility for Down-level Clients" section that follows.
Range	0001-01-01 through 9999-12-31 (1582-10-15 through 9999-12-31 for Informatica) January 1, 1 CE (Common Era) through December 31, 9999 CE (October 15, 1582 CE through December 31, 9999 CE for Informatica)
Element ranges	YYYY is four digits from 0001 to 9999 that represent a year. For Informatica, YYYY is limited to the range 1582 to 9999. MM is two digits from 01 to 12 that represent a month in the specified year. DD is two digits from 01 to 31, depending on the month, that represents a day of the specified month.
Character length	10 positions
Precision, scale	10, 0
Storage size	3 bytes, fixed
Storage structure	1, 3-byte integer stores date.
Accuracy	One day
Default value	1900-01-01 This value is used for the appended date part for implicit conversion from time to datetime2 or datetimeoffset.
Calendar	Gregorian
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

Supported string literal formats for date

The following tables show the valid string literal formats for the date data type.

Numeric	Description
mdy [m]m/dd/[yy]yy [m]m-dd-[yy]yy [m]m.dd.[yy]yy myd mm/[yy]yy/dd mm-[yy]yy/dd [m]m.[yy]yy.dd dmy dd/[m]m/[yy]yy dd-[m]m-[yy]yy dd.[m]m.[yy]yy dym dd/[yy]yy/[m]m dd-[yy]yy-[m]m dd.[yy]yy.[m]m ymd [yy]yy/[m]m/dd [yy]yy-[m]m-dd [yy]yy-[m]m-dd	[m]m, dd, and [yy]yy represent month, day, and year in a string with slash marks (/), hyphens (-), or periods (.) as separators. Only four- or two-digit years are supported. Use four-digit years whenever possible. To specify an integer from 0001 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years, use the Configure the two digit year cutoff Server Configuration Option. Note: For Informatica, YYYY is limited to the range 1582 to 9999. A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year greater than the last two digits of the cutoff year is in the century that comes before the cutoff year. For example, if the two-digit year cutoff is the default 2049, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. The default date format is determined by the current language setting. You can change the date format by using the SET LANGUAGE and SET DATEFORMAT statements. The ydm format isn't supported for date.
Alphabetical	Description
mon [dd][,] yyyy mon dd[,] [yy] mon yyyy [dd] [dd] mon[,] yyyy dd mon[,][yy]yy dd [yy]yy mon [dd] yyyy mon yyyy mon [dd] yyyy [dd] mon	mon represents the full month name or the month abbreviation given in the current language. Commas are optional and capitalization is ignored. To avoid ambiguity, use four-digit years. If the day is missing, the first day of the month is supplied.

 

ISO 8601	Description
YYYY-MM-DD YYYYMMDD	Same as the SQL standard. This format is the only format defined as an international standard.
Unseparated	Description
[yy]yymmdd yyyy[mm][dd]	The date data can be specified with four, six, or eight digits. A six- or eight-digit string is always interpreted as ymd. The month and day must always be two digits. A four-digit string is interpreted as year.

 

ODBC	Description
{ d 'yyyy-mm-dd' }	ODBC API specific.
W3C XML format	Description
yyyy-mm-ddTZD	Supported for XML/SOAP usage. TZD is the time zone designator (Z or +hh:mm or -hh:mm): - hh:mm represents the time zone offset. hh is two digits, ranging from 0 to 14, that represent the number of hours in the time zone offset. - MM is two digits, ranging from 0 to 59, that represent the number of additional minutes in the time zone offset. - + (plus) or - (minus) the mandatory sign of the time zone offset. This sign indicates that, to obtain the local time, the time zone offset is added or subtracted from the Coordinated Universal Times (UTC) time. The valid range of time zone offset is from -14:00 to +14:00.

ANSI and ISO 8601 compliance

date complies with the ANSI SQL standard definition for the Gregorian calendar: "NOTE 85 - Datetime data types will allow dates in the Gregorian format to be stored in the date range 0001-01-01 CE through 9999-12-31 CE."

The default string literal format, which is used for down-level clients, complies with the SQL standard form that is defined as YYYY-MM-DD. This format is the same as the ISO 8601 definition for DATE.

datetime2 

Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

Property	Value
Syntax	datetime2 [ (fractional seconds precision) ]
Usage	DECLARE @MyDatetime2 datetime2(7) CREATE TABLE Table1 ( Column1 datetime2(7) )
Default string literal format (used for down-level client)	YYYY-MM-DD hh:mm:ss[.fractional seconds] For more information, see the "Backward Compatibility for Down-level Clients" section that follows.
Date range	0001-01-01 through 9999-12-31 January 1,1 CE through December 31, 9999 CE
Time range	00:00:00 through 23:59:59.9999999
Time zone offset range	None
Element ranges	YYYY is a four-digit number, ranging from 0001 through 9999, that represents a year. MM is a two-digit number, ranging from 01 to 12, that represents a month in the specified year. DD is a two-digit number, ranging from 01 to 31 depending on the month, that represents a day of the specified month. hh is a two-digit number, ranging from 00 to 23, that represents the hour. mm is a two-digit number, ranging from 00 to 59, that represents the minute. ss is a two-digit number, ranging from 00 to 59, that represents the second. n* is a zero- to seven-digit number from 0 to 9999999 that represents the fractional seconds. In Informatica, the fractional seconds will be truncated when n > 3.
Character length	19 positions minimum (YYYY-MM-DD hh:mm:ss ) to 27 maximum (YYYY-MM-DD hh:mm:ss.0000000)
Precision, scale	0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.
Storage size 1	6 bytes for precision less than 3. 7 bytes for precision 3 or 4. All other precision require 8 bytes.2
Accuracy	100 nanoseconds
Default value	1900-01-01 00:00:00
Calendar	Gregorian
User-defined fractional second precision	Yes
Time zone offset aware and preservation	No
Daylight saving aware	No

¹ Provided values are for uncompressed rowstore. Use of data compression or columnstore may alter storage size for each precision. Additionally, storage size on disk and in memory may differ. For example, datetime2 values always require 8 bytes in memory when batch mode is used.

² When a datetime2 value is cast to a varbinary value, an additional byte is added to the varbinary value to store precision.

For data type metadata, see sys.systypes (Transact-SQL) or TYPEPROPERTY (Transact-SQL). Precision and scale are variable for some date and time data types. To obtain the precision and scale for a column, see COLUMNPROPERTY (Transact-SQL), COL_LENGTH (Transact-SQL), or sys.columns (Transact-SQL).

datetime 

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

 Note

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Description

Property	Value
Syntax	datetime
Usage	DECLARE @MyDatetime datetime CREATE TABLE Table1 ( Column1 datetime )
Default string literal formats (used for down-level client)	Not applicable
Date range	January 1, 1753, through December 31, 9999
Time range	00:00:00 through 23:59:59.997
Time zone offset range	None
Element ranges	YYYY is four digits from 1753 through 9999 that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.
Character length	19 positions minimum to 23 maximum
Storage size	8 bytes
Accuracy	Rounded to increments of .000, .003, or .007 seconds
Default value	1900-01-01 00:00:00
Calendar	Gregorian (Does include the complete range of years.)
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

Supported string literal formats for datetime

The following tables list the supported string literal formats for datetime. Except for ODBC, datetime string literals are in single quotation marks ('), for example, 'string_literaL'. If the environment isn't us_english, the string literals should be in the format N'string_literaL'.

Numeric	Description
Date formats: [0]4/15/[19]96 -- (mdy) [0]4-15-[19]96 -- (mdy) [0]4.15.[19]96 -- (mdy) [0]4/[19]96/15 -- (myd) 15/[0]4/[19]96 -- (dmy) 15/[19]96/[0]4 -- (dym) [19]96/15/[0]4 -- (ydm) [19]96/[0]4/15 -- (ymd) Time formats: 14:30 14:30[:20:999] 14:30[:20.9] 4am 4 PM	You can specify date data with a numeric month specified. For example, 5/20/97 represents the twentieth day of May 1997. When you use numeric date format, specify the month, day, and year in a string that uses slash marks (/), hyphens (-), or periods (.) as separators. This string must appear in the following form: number separator number separator number [time] [time] When the language is set to us_english, the default order for the date is mdy. You can change the date order by using the SET DATEFORMAT statement. The setting for SET DATEFORMAT determines how date values are interpreted. If the order doesn't match the setting, the values aren't interpreted as dates. Out-of-order dates may be misinterpreted as out of range or with wrong values. For example, 12/10/08 can be interpreted as one of six dates, depending on the DATEFORMAT setting. A four-part year is interpreted as the year.
Alphabetical	Description
Apr[il] [15][,] 1996 Apr[il] 15[,] [19]96 Apr[il] 1996 [15] [15] Apr[il][,] 1996 15 Apr[il][,][19]96 15 [19]96 apr[il] [15] 1996 apr[il] 1996 APR[IL] [15] 1996 [15] APR[IL]	You can specify date data with a month specified as the full month name. For example, April or the month abbreviation of Apr specified in the current language; commas are optional and capitalization is ignored. Here are some guidelines for using alphabetical date formats: 1) Enclose the date and time data in single quotation marks ('). For languages other than English, use N' 2) Characters that are enclosed in brackets are optional. 3) If you specify only the last two digits of the year, values less than the last two digits of the value of the Configure the two digit year cutoff Server Configuration Option configuration option are in the same century as the cutoff year. Values greater than or equal to the value of this option are in the century that comes before the cutoff year. For example, if two digit year cutoff is 2050 (default), 25 is interpreted as 2025 and 50 is interpreted as 1950. To avoid ambiguity, use four-digit years. 4) If the day is missing, the first day of the month is supplied. The SET DATEFORMAT session setting isn't applied when you specify the month in alphabetical form.

 

ISO 8601	Description
YYYY-MM-DDThh:mm:ss[.mmm] YYYYMMDD[ hh:mm:ss[.mmm]]	Examples: 1) 2004-05-23T14:25:10 2) 2004-05-23T14:25:10.487 To use the ISO 8601 format, you must specify each element in the format, including the T, the colons (:), and the period (.) that are shown in the format. The brackets indicate that the fraction of second component is optional. The time component is specified in the 24-hour format. The T indicates the start of the time part of the datetime value. The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.
Unseparated	Description
YYYYMMDD hh:mm:ss[.mmm]

 

ODBC

Description

{ ts '1998-05-02 01:23:56.123' } { d '1990-10-02' } { t '13:33:41' }

The ODBC API defines escape sequences to represent date and time values, which ODBC calls timestamp data. This ODBC timestamp format is also supported by the OLE DB language definition (DBGUID-SQL) supported by the Microsoft OLE DB provider for SQL Server. Applications that use the ADO, OLE DB, and ODBC-based APIs can use this ODBC timestamp format to represent dates and times. ODBC timestamp escape sequences are of the format: { literal_type 'constant_value' }: - literal_type specifies the type of the escape sequence. Timestamps have three literal_type specifiers: 1) d = date only 2) t = time only 3) ts = timestamp (time + date) - 'constant_value' is the value of the escape sequence. constant_value must follow these formats for each literal_type. d : yyyy-mm-dd t : hh:mm:ss[.fff] ts : yyyy-mm-dd hh:mm:ss[.fff]

Rounding of datetime fractional second precision

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

User-specified value	System stored value
01/01/98 23:59:59.999	1998-01-02 00:00:00.000
01/01/98 23:59:59.995 01/01/98 23:59:59.996 01/01/98 23:59:59.997 01/01/98 23:59:59.998	1998-01-01 23:59:59.997
01/01/98 23:59:59.992 01/01/98 23:59:59.993 01/01/98 23:59:59.994	1998-01-01 23:59:59.993
01/01/98 23:59:59.990 01/01/98 23:59:59.991	1998-01-01 23:59:59.990

ANSI and ISO 8601 compliance

datetime isn't ANSI or ISO 8601 compliant.

Converting date and time data

When you convert to date and time data types, SQL Server rejects all values it can't recognize as dates or times. For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL).

Converting other date and time types to the datetime data type

This section describes what occurs when other date and time data types are converted to the datetime data type.

When the conversion is from date, the year, month, and day are copied. The time component is set to 00:00:00.000. The following code shows the results of converting a date value to a datetime value.

datetimeoffset 

Defines a date that is combined with a time of a day based on a 24-hour clock like datetime2, and adds time zone awareness based on UTC (Universal Time Coordinate or Greenwich Mean Time).

datetimeoffset description

Property		Value
Syntax		datetimeoffset [ (fractional seconds precision) ]
Usage		DECLARE @MyDatetimeoffset datetimeoffset(7) CREATE TABLE Table1 ( Column1 datetimeoffset(7) )
Default string literal formats (used for down-level client)		YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm] For more information, see the "Backward Compatibility for Down-level Clients" section that follows.
Date range		0001-01-01 through 9999-12-31 January 1, 1 CE through December 31, 9999 CE
Time range		00:00:00 through 23:59:59.9999999
Time zone offset range		-14:00 through +14:00
Element ranges		YYYY is four digits, ranging from 0001 through 9999, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. hh is two digits that range from -14 to +14. mm is two digits that range from 00 to 59.
Character length		26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)
Precision, scale		See the table below.
Storage size		10 bytes, fixed is the default with the default of 100ns fractional second precision.
Accuracy		100 nanoseconds
Default value		1900-01-01 00:00:00 00:00
Calendar		Gregorian
User-defined fractional second precision		Yes
Time zone offset aware and preservation		Yes
Daylight saving aware		No
Specified scale	Result (precision, scale)		Column length (bytes)	Fractional seconds precision
datetimeoffset	(34,7)		10	7
datetimeoffset(0)	(26,0)		8	0-2
datetimeoffset(1)	(28,1)		8	0-2
datetimeoffset(2)	(29,2)		8	0-2
datetimeoffset(3)	(30,3)		9	3-4
datetimeoffset(4)	(31,4)		9	3-4
datetimeoffset(5)	(32,5)		10	5-7
datetimeoffset(6)	(33,6)		10	5-7
datetimeoffset(7)	(34,7)		10	5-7

 

smalldatetime 

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

smalldatetime description

Property	Value
Syntax	smalldatetime
Usage	DECLARE @MySmalldatetime smalldatetime CREATE TABLE Table1 ( Column1 smalldatetime )
Default string literal formats (used for down-level client)	Not applicable
Date range	1900-01-01 through 2079-06-06 January 1, 1900, through June 6, 2079
Time range	00:00:00 through 23:59:59 2007-05-09 23:59:59 will round to 2007-05-10 00:00:00
Element ranges	YYYY is four digits, ranging from 1900, to 2079, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. Values that are 29.998 seconds or less are rounded down to the nearest minute. Values of 29.999 seconds or more are rounded up to the nearest minute.
Character length	19 positions maximum
Storage size	4 bytes, fixed.
Accuracy	One minute
Default value	1900-01-01 00:00:00
Calendar	Gregorian (Doesn't include the complete range of years.)
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

 

time

            Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.

time Description

Property	Value
Syntax	time [ (fractional second scale) ]
Usage	DECLARE @MyTime time(7) CREATE TABLE Table1 ( Column1 time(7) )
fractional seconds scale	Specifies the number of digits for the fractional part of the seconds. This can be an integer from 0 to 7. For Informatica, this can be an integer from 0 to 3. The default fractional scale is 7 (100ns).
Default string literal format (used for down-level client)	hh:mm:ss[.nnnnnnn] for Informatica) For more information, see the Backward Compatibility for Down-level Clients section.
Range	00:00:00.0000000 through 23:59:59.9999999 (00:00:00.000 through 23:59:59.999 for Informatica)
Element ranges	hh is two digits, ranging from 0 to 23, that represent the hour. mm is two digits, ranging from 0 to 59, that represent the minute. ss is two digits, ranging from 0 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. For Informatica, n* is zero to three digits, ranging from 0 to 999.
Character length	8 positions minimum (hh:mm:ss) to 16 maximum (hh:mm:ss.nnnnnnn). For Informatica, the maximum is 12 (hh:mm:ss.nnn).
Precision, scale (user specifies scale only)	See the table below.
Storage size	5 bytes, fixed, is the default with the default of 100ns fractional second precision. In Informatica, the default is 4 bytes, fixed, with the default of 1ms fractional second precision.
Accuracy	100 nanoseconds (1 millisecond in Informatica)
Default value	00:00:00 This value is used for the appended time part for implicit conversion from date to datetime2 or datetimeoffset.
User-defined fractional second precision	Yes
Time zone offset aware and preservation	No
Daylight saving aware	No
Specified scale	Result (precision, scale)	Column length (bytes)	Fractional seconds precision
time	(16,7) [(12,3) in Informatica]	5 (4 in Informatica)	7 (3 in Informatica)
time(0)	(8,0)	3	0-2
time(1)	(10,1)	3	0-2
time(2)	(11,2)	3	0-2
time(3)	(12,3)	4	3-4
time(4) Not supported in Informatica.	(13,4)	4	3-4
time(5) Not supported in Informatica.	(14,5)	5	5-7
time(6) Not supported in Informatica.	(15,6)	5	5-7
time(7) Not supported in Informatica.	(16,7)	5	5-7

 

char and varchar

 Character data types that are either fixed-size, char, or variable-size, varchar. Starting with SQL Server 2019 (15.x), when a UTF-8 enabled collation is used, these data types store the full range of Unicode character data and use the UTF-8 character encoding. If a non-UTF-8 collation is specified, then these data types store only a subset of characters supported by the corresponding code page of that collation.

Arguments

char [ ( n ) ]

Fixed-size string data. n defines the string size in bytes and must be a value from 1 through 8,000. For single-byte encoding character sets such as Latin, the storage size is n bytes and the number of characters that can be stored is also n. For multibyte encoding character sets, the storage size is still n bytes but the number of characters that can be stored may be smaller than n. The ISO synonym for char is character. For more information on character sets, see Single-Byte and Multibyte Character Sets.

varchar [ ( n | max ) ]

Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000, or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB). For single-byte encoding character sets such as Latin, the storage size is n bytes + 2 bytes and the number of characters that can be stored is also n. For multibyte encoding character sets, the storage size is still n bytes + 2 bytes but the number of characters that can be stored may be smaller than n. The ISO synonyms for varchar are charvarying or charactervarying. For more information on character sets, see Single-Byte and Multibyte Character Sets.

Remarks

A common misconception is to think that with char(n) and varchar(n), the n defines the number of characters. However, in char(n) and varchar(n), the n defines the string length in bytes (0 to 8,000). n never defines numbers of characters that can be stored. This is similar to the definition of nchar(n) and nvarchar(n).

The misconception happens because when using single-byte encoding, the storage size of char and varchar is n bytes and the number of characters is also n. However, for multibyte encoding such as UTF-8, higher Unicode ranges (128 to 1,114,111) result in one character using two or more bytes. For example, in a column defined as char(10), the Database Engine can store 10 characters that use single-byte encoding (Unicode range 0 to 127), but fewer than 10 characters when using multibyte encoding (Unicode range 128 to 1,114,111). For more information about Unicode storage and character ranges, see Storage differences between UTF-8 and UTF-16.

When n isn't specified in a data definition or variable declaration statement, the default length is 1. If n isn't specified when using the CAST and CONVERT functions, the default length is 30.

Objects that use char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The collation controls the code page that is used to store the character data.

Multibyte encodings in SQL Server include:

·       Double-byte character sets (DBCS) for some East Asian languages using code pages 936 and 950 (Chinese), 932 (Japanese), or 949 (Korean).

·       UTF-8 with code page 65001.

Applies to: SQL Server 2019 (15.x) and later versions.

If you have sites that support multiple languages:

·       Starting with SQL Server 2019 (15.x), consider using a UTF-8 enabled collation to support Unicode and minimize character conversion issues.

·       If using a previous version of the SQL Server Database Engine, consider using the Unicode nchar or nvarchar data types to minimize character conversion issues.

If you use char or varchar, we recommend that you:

·       Use char when the sizes of the column data entries are consistent.

·       Use varchar when the sizes of the column data entries vary considerably.

·       Use varchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 8,000 bytes.

If SET ANSI_PADDING is OFF when either CREATE TABLE or ALTER TABLE is executed, a char column that is defined as NULL is handled as varchar.

ntext, text, and image 

Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the Unicode UCS-2 character set.

 

All three data types deprecated in latest version. Same will be removed in future versions of SQL server

Arguments

ntext

Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered. The ISO synonym for ntext is national text.

text

Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

image

Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

Remarks

The following functions and statements can be used with ntext, text, or image data.

Functions	Statements
DATALENGTH	READTEXT
PATINDEX	SET TEXTSIZE
SUBSTRING	UPDATETEXT
TEXTPTR	WRITETEXT
TEXTVALID

 

nchar and nvarchar

Character data types that are either fixed-size, nchar, or variable-size, nvarchar. Starting with SQL Server 2012 (11.x), when a Supplementary Character (SC) enabled collation is used, these data types store the full range of Unicode character data and use the UTF-16 character encoding. If a non-SC collation is specified, then these data types store only the subset of character data supported by the UCS-2 character encoding.

Arguments

nchar [ ( n ) ]

Fixed-size string data. n defines the string size in byte-pairs, and must be a value from 1 through 4,000. The storage size is two times n bytes. For UCS-2 encoding, the storage size is two times n bytes and the number of characters that can be stored is also n. For UTF-16 encoding, the storage size is still two times n bytes, but the number of characters that can be stored may be smaller than n because Supplementary Characters use two byte-pairs (also called surrogate-pair). The ISO synonyms for nchar are national char and national character.

nvarchar [ ( n | max ) ]

Variable-size string data. n defines the string size in byte-pairs, and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 characters (2 GB). The storage size is two times n bytes + 2 bytes. For UCS-2 encoding, the storage size is two times n bytes + 2 bytes and the number of characters that can be stored is also n. For UTF-16 encoding, the storage size is still two times n bytes + 2 bytes, but the number of characters that can be stored may be smaller than n because Supplementary Characters use two byte-pairs (also called surrogate-pair). The ISO synonyms for nvarchar are national char varying and national character varying.

Remarks

A common misconception is to think that with nchar(n) and nvarchar(n), the n defines the number of characters. However, in nchar(n) and nvarchar(n), the n defines the string length in byte-pairs (0-4,000). n never defines numbers of characters that can be stored. This is similar to the definition of char(n) and varchar(n).

The misconception happens because when using characters defined in the Unicode range 0 to 65,535, one character can be stored per each byte-pair. However, in higher Unicode ranges (65,536 to 1,114,111) one character may use two byte-pairs. For example, in a column defined as nchar(10), the Database Engine can store 10 characters that use one byte-pair (Unicode range 0 to 65,535), but fewer than 10 characters when using two byte-pairs (Unicode range 65,536 to 1,114,111). For more information about Unicode storage and character ranges, see Storage differences between UTF-8 and UTF-16.

When n isn't specified in a data definition or variable declaration statement, the default length is 1. When n isn't specified with the CAST function, the default length is 30.

If you use nchar or nvarchar, we recommend that you:

·       Use nchar when the sizes of the column data entries are consistent.

·       Use nvarchar when the sizes of the column data entries vary considerably.

·       Use nvarchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 4,000 byte-pairs.

sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it isn't nullable. sysname is used to reference database object names.

Objects that use nchar or nvarchar are assigned the default collation of the database unless a specific collation is assigned using the COLLATE clause.

SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF doesn't apply to the nchar or nvarchar data types.

Prefix a Unicode character string constants with the letter N to signal UCS-2 or UTF-16 input, depending on whether an SC collation is used or not. Without the N prefix, the string is converted to the default code page of the database that may not recognize certain characters. Starting with SQL Server 2019 (15.x), when a UTF-8 enabled collation is used, the default code page is capable of storing the Unicode UTF-8 character set.

When prefixing a string constant with the letter N, the implicit conversion will result in a UCS-2 or UTF-16 string if the constant to convert doesn't exceed the max length for the nvarchar string data type (4,000). Otherwise, the implicit conversion will result in a large-value nvarchar(max).

ntext, text, and image 

Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the Unicode UCS-2 character set.

The ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Arguments

ntext

Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered. The ISO synonym for ntext is national text.

text

Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

image

Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

Remarks

The following functions and statements can be used with ntext, text, or image data.

Functions	Statements
DATALENGTH	READTEXT
PATINDEX	SET TEXTSIZE
SUBSTRING	UPDATETEXT
TEXTPTR	WRITETEXT
TEXTVALID

 

binary and varbinary

Binary data types of either fixed length or variable length.

Arguments

binary [ ( n ) ]

Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

varbinary [ ( n | max ) ]

Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The ANSI SQL synonym for varbinary is binary varying.

Remarks

The default length is 1 when n isn't specified in a data definition or variable declaration statement. When n isn't specified with the CAST function, the default length is 30.

Data type	Use when ...
binary	the sizes of the column data entries are consistent.
varbinary	the sizes of the column data entries vary considerably.
varbinary(max)	the column data entries exceed 8,000 bytes.

Convert binary and varbinary data

When converting data from a string data type to a binary or varbinary data type of unequal length, SQL Server pads or truncates the data on the right. These string data types are:

·       char

·       varchar

·       nchar

·       nvarchar

·       binary

·       varbinary

·       text

·       ntext

·       image

When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.

Converting data to the binary and varbinary data types is useful if binary data is the easiest way to move around data. At some point, you might convert a value type to a binary value of large enough size and then convert it back. This conversion always results in the same value if both conversions are taking place on the same version of SQL Server. The binary representation of a value might change from version to version of SQL Server.

You can convert int, smallint, and tinyint to binary or varbinary. If you convert the binary value back to an integer value, this value will be different from the original integer value if truncation has occurred. For example, the following SELECT statement shows that the integer value 123456 is stored as a binary 0x0001e240: