You are here

SQL: Data Type

sql

SQL: DATA TYPE

When we defined columns in tables, parameters in procedures and functions, variables in T-SQL batches, we have to choose appropriate data type for those.

  • The data type contains the data that is supported. SQL server has an extensive set of data type that we can choose to match any need.
  1. Exact Numeric Data type
  2. Approximate Numeric Data type
  3. Date and Time Data Type
  4. Non-Unicode character string Data type
  5. Unicode character string Data type
  6. Binary Data type
  7. Other Data type
  • The important aspect for selecting the proper data type is to remember that a type is a constraint. It has a certain domain of supported values and does not allow values outside the domain.

Exact Numeric Data Type: The exact numeric data types are bit, tinyint, smallint, int, bigint, decimal, money, smallmoney.

  • Bit: It can take a value of 1, 0, NULL. Usually used as a pseudo-boolean by  using 1=true, 0=false, NULL=unknown. It’s size in bytes is 1 byte.
  • Tinyint: It’s range is between 1 to 255 and size is 1 byte.
  • Smallint: It’s range is between -32,768 to 32,768 and size is 2 bytes.
  • Int : It’s range is between -2,147,483,648 to 2,147,483,648 and size is 4 bytes.
  • Bigint: It’s range is between -9,222,372,036,854,775,808 to 9,222,372,036,854,775,807 and size is 8 bytes.
  • Decimal(or Numeric which are the same but decimal is standard): decimal(p, s) where p is precision and s is scale. it’s size is between 5 and 17 bytes.

    Example:   

    Declare @d as decimal(6,3)

    set @d=21.36

    select @d

    output:

    21.360

    Explanation: decimal(6,3) is a number that has 3(p-s) digits before the decimal and 3 digit after the decimal.

  • Money: It’s range is between -922,337,203,685,477 ,5808 through 922,337,203,685,477,5808 and size is 8 bytes.
  • Smallmoney: It’s range is between -214,748,3648 through 214,748,3648 and size is 4 bytes.

Approximate Numeric Data type: The approximate numeric data type contains float, real.it allows to store large range of values with a high amount of precision.

  • Float(n): floating point numeric data. N is the number of bits that is used to store the mantissa of the float number. The range of n is 1 to 53. The default value of n is 53.
  • Real: The real is an ISO synonym for float(24) data type and it’s size is 4 bytes and range is -3.40E+308 through 3.4+308.    

Date and Time Data type:The Data and time data type contains date, time, datetime2, datetimeoffset, smalldatetime, datetime.

  • Date: The date data type contains only date not time. The range of date is January 1,0001, to December 31,9999. And the size of date is 3 bytes.
  • Time: It contains time of day only with fractional part of second that can be stored. And size is 3 to 5 bytes. time(6) is HH:MM:SS.000001.
  • Datetime2: This type store a date with time. The range is from January 1,0001, to December 31,9999. And size is 6 to 8 bytes.
  • Smalldatetime: The range is from January 1,1900, to June 6,2079 with accuracy to 1 minute. It’s size is 4 bytes.
  • Datetimeoffset: it is same as datetime2, but it also includes an offset for timezone. It’s size is 8 to 10 bytes.
  • Datetime: The range is from January 1,1753, to December 31,9999 and accuracy to 3.33 minute and size is 8 bytes.

Binary Data:Strings of bits used for storing files, encrypted values etc.. It contains Binary, varbinary, varbinary(max).

  • Binary(N): it store fixed length binary data with maximum value of N is 8000.
  • Varbinary(N): it store variable length binary data with maximum value of N is 8000.
  • Varbinary(max): it store variable length binary data up to(2^31)-1 bytes long.

Character(or string) data: String are used to store text values. Storage is specified in number of characters in the string. The character includes char(n), varchar(n), varchar(max), nchar, nvarchar, nvarchar(max).

  • Char(N): fixed-length character data up to 8000 characters. When using fixed length data type, it is best if the values in the column are the same.
  • Varchar(N): Variable-length character data up to 8000.
  • Varchar(max): Variable-length character data type up to(2^31) -1 bytes long.
  • nchar, nvarchar, nvarchar(max): It stores Unicode equivalents of char, varchar and varchar(max).

other data type: It contains some more data type like sql_variant, rowversion (timestamp), uniqueidentifier.

  • sql_variant: stores nearly any data type, other than CLR based like heirachyId, spatial types.
  • rowversion(timestamp): used for optimistic locking to version-stamp in a row. The values are based on data type changes on every modification of the rows.
  • uniqueidentifier: stores a globally unique identifier(GUID)value. GUID is a commonly used data type for artificial key.
  • XML: allows to store xml document in a column value. The xml datatype gives a rich set of functionality.
  • Spatial types: used for storing spatial data like shapes, maps, lines etc..
  • heirarchyId: used to store data about a hierarchy along with methods for manipulating the hierarchy.

Keep Visiting TechwithR.com for more upcoming top technical articles.

Detail course about DBA visit TechNet Consultancy

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top