You are here
Home > Sql Server > SQL DATA TYPES

SQL DATA TYPES

SQL Data Types

Each column in a database has a data type.

A data type is a set of values. There are different categories for data types:

Approximate numeric: It stores approximations of the numbers based on IEEE 754 standard.

  • float(N): Values in the range from -1.79E+308 to 1.79E+308.
  • real: Values in the range from -3.40E+38 to 3.40E+38.

Exact numeric: It stores number based data.

  • bit: It has domain of 0,1, or NULL.
  • smallint: Integers between -32,768 to 32,767.
  • tinyint: Integers between 0 to 255.
  • int: 4 bytes
  • bigint: 8 bytes
  • decimal or numeric: All numbers between -10^38-1 to 10^38-1.
  • smallmoney: Values from -214,748.3648 to 214,748.3647.
  • money: 8 bytes

Character strings: It is used to store text values.

  • char(N): Fixed length character data with maximum length of 8,000 characters.
  • varchar(N): Variable length character data with maximum length of 8,000 characters.
  • varchar(max): Variable length character data up to (2^31)-1 bytes.

Unicode character strings: It is used to store text values. Unicode is a double byte (and in some case triple byte) character set that allows for more than 256 characters at a time.

  • nvarchar, nvarchar(max), nchar: Unicode equivalents of varchar, varchar(max), char.

Binary strings: Strings of bits used for storing things like encrypted values, files etc.

  • binary(N): Fixed length binary data with maximum length of 8,000 bytes.
  • varbinary(N): Variable length binary data with maximum length of 8,000 bytes.
  • varbinary(max): Variable length binary data up to (2^31)-1 bytes.

Date and Time: It stores values that deal storing a point in time.

  • date: It represents a date. Format: yyyy-mm-dd.
  • time(N): It stores a time of day.
  • datetime: It points in time from January 1, 1753 to December 31, 9999.
  • smalldatetime: It points in time from January 1, 1900 to June 6, 2079.
  • datetime2(N): It stores a point in time from January 1, 0001 to December 31, 9999.
  • datetimeoffset: It is same as datetime2. It includes an offset for time zone offset.

Other data types:

  • sql_variant: It stores nearly any data type other than CLR based ones like spatial types,hierarchyId.
  • uniqueidentifier: It stores a globally unique identifier(GUID).
  • rowversion: It’s synonym is timestamp. It is used for optimistic locking to version stamp in row.
  • XML: It allows you to store an XML document in a column value.
  • cursor: Reference to a cursor object.
  • Spatial types: It is used for storing spatial data like for maps, lines, shapes etc.
  • table: It stores a result set for later processing.
  • hierarchyId: It is used to store data about hierarychy.

 

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

 

Leave a Reply

Top