{"id":79,"date":"2018-12-05T08:58:09","date_gmt":"2018-12-05T08:58:09","guid":{"rendered":"https:\/\/barkhane.com\/sql\/2018\/12\/05\/sql-data-types\/"},"modified":"2024-03-03T18:06:02","modified_gmt":"2024-03-03T18:06:02","slug":"sql-data-types","status":"publish","type":"post","link":"https:\/\/barkhane.com\/sql\/sql-data-types\/","title":{"rendered":"SQL Data Types"},"content":{"rendered":"

SQL Data Types : The SQL data type defines a kind of value that a column can contain. In a database table, every column is required to have a name and a data type.<\/p>\n

SQL Server offers six categories of data types for your use which are listed below<\/p>\n

Exact Numeric Data Types<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n
DATA TYPE<\/th>\nFROM<\/th>\nTO<\/th>\n<\/tr>\n
bigint<\/td>\n-9,223,372,036,854,775,808<\/td>\n9,223,372,036,854,775,807<\/td>\n<\/tr>\n
int<\/td>\n-2,147,483,648<\/td>\n2,147,483,647<\/td>\n<\/tr>\n
smallint<\/td>\n-32,768<\/td>\n32,767<\/td>\n<\/tr>\n
tinyint<\/td>\n0<\/td>\n255<\/td>\n<\/tr>\n
bit<\/td>\n0<\/td>\n1<\/td>\n<\/tr>\n
decimal<\/td>\n-10^38 +1<\/td>\n10^38 -1<\/td>\n<\/tr>\n
numeric<\/td>\n-10^38 +1<\/td>\n10^38 -1<\/td>\n<\/tr>\n
money<\/td>\n-922,337,203,685,477.5808<\/td>\n+922,337,203,685,477.5807<\/td>\n<\/tr>\n
smallmoney<\/td>\n-214,748.3648<\/td>\n+214,748.3647<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Approximate Numeric Data Types<\/h2>\n\n\n\n\n\n
DATA TYPE<\/th>\nFROM<\/th>\nTO<\/th>\n<\/tr>\n
float<\/td>\n-1.79E + 308<\/td>\n1.79E + 308<\/td>\n<\/tr>\n
real<\/td>\n-3.40E + 38<\/td>\n3.40E + 38<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Date and Time Data Types<\/h2>\n\n\n\n\n\n\n\n
DATA TYPE<\/th>\nFROM<\/th>\nTO<\/th>\n<\/tr>\n
datetime<\/td>\nJan 1, 1753<\/td>\nDec 31, 9999<\/td>\n<\/tr>\n
smalldatetime<\/td>\nJan 1, 1900<\/td>\nJun 6, 2079<\/td>\n<\/tr>\n
date<\/td>\nStores a date like June 30, 1991<\/td>\n<\/tr>\n
time<\/td>\nStores a time of day like 12:30 P.M.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Note<\/b>\u00a0\u2212 Here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.<\/p>\n

Character Strings Data Types<\/h2>\n\n\n\n\n\n\n\n
DATA TYPE<\/th>\n\u00a0Description<\/th>\n<\/tr>\n
char<\/b><\/td>\nMaximum length of 8,000 characters.( Fixed length non-Unicode characters)<\/td>\n<\/tr>\n
varchar<\/b><\/td>\nMaximum of 8,000 characters.(Variable-length non-Unicode data).<\/td>\n<\/tr>\n
varchar(max)<\/b><\/td>\nMaximum length of 2E + 31 characters, Variable-length non-Unicode data (SQL Server 2005 only).<\/td>\n<\/tr>\n
text<\/b><\/td>\nVariable-length non-Unicode data with a maximum length of 2,147,483,647 characters.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Unicode Character Strings Data Types<\/h2>\n\n\n\n\n\n\n\n
DATA TYPE<\/th>\n\u00a0Description<\/th>\n<\/tr>\n
nchar<\/b><\/td>\nMaximum length of 4,000 characters.( Fixed length Unicode)<\/td>\n<\/tr>\n
nvarchar<\/b><\/td>\nMaximum length of 4,000 characters.(Variable length Unicode)<\/td>\n<\/tr>\n
nvarchar(max)<\/b><\/td>\nMaximum length of 2E + 31 characters (SQL Server 2005 only).( Variable length Unicode)<\/td>\n<\/tr>\n
ntext<\/b><\/td>\nMaximum length of 1,073,741,823 characters. ( Variable length Unicode )<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Binary Data Types<\/h2>\n\n\n\n\n\n\n\n
DATA TYPE<\/th>\n\u00a0 Description<\/th>\n<\/tr>\n
binary<\/b><\/td>\nMaximum length of 8,000 bytes(Fixed-length binary data )<\/td>\n<\/tr>\n
varbinary<\/b><\/td>\nMaximum length of 8,000 bytes.(Variable length binary data)<\/td>\n<\/tr>\n
varbinary(max)<\/b><\/td>\nMaximum length of 2E + 31 bytes (SQL Server 2005 only). ( Variable length Binary data)<\/td>\n<\/tr>\n
image<\/b><\/td>\nMaximum length of 2,147,483,647 bytes. ( Variable length Binary Data)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Misc Data Types<\/h2>\n\n\n\n\n\n\n\n\n\n
<\/th>\nDATA TYPE & Description<\/th>\n<\/tr>\n
sql_variant<\/b><\/td>\nStores values of various SQL Server-supported data types, except text, ntext, and timestamp.<\/td>\n<\/tr>\n
timestamp<\/b><\/td>\nStores a database-wide unique number that gets updated every time a row gets updated<\/td>\n<\/tr>\n
uniqueidentifier<\/b><\/td>\nStores a globally unique identifier (GUID)<\/td>\n<\/tr>\n
xml<\/b><\/td>\nStores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).<\/td>\n<\/tr>\n
cursor<\/b><\/td>\nReference to a cursor object<\/td>\n<\/tr>\n
table<\/b><\/td>\nStores a result set for later processing<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

SQL Data Types : The SQL data type defines a kind of value that a column can contain. In a […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[2],"tags":[],"class_list":["post-79","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts\/79","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/comments?post=79"}],"version-history":[{"count":0,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts\/79\/revisions"}],"wp:attachment":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/media?parent=79"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/categories?post=79"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/tags?post=79"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}