What is Data Type?

A Data Type in SQL server is defined as the type of data that any column or variable can store. It is a type of data that an object holds like integer, character, string, etc. While creating any table or variable, in addition to specifying the name, you also set the Type of Data it will store.

How to use MS SQL datatype

  • You need to define in advance, the type of data a column or variable can store. Determining data type also restricts the user from entering any unexpected or invalid data.
  • You can make efficient use of memory by assigning an appropriate data type to variable or column which will allocate only the required amount of system memory for the respective column's data.
  • MS SQL offers a broad category of basic data types in SQL as per user's needs like Date, binary images, etc.

Data type available in MS SQL Server

Here is MS SQL server data types list:

MS SQL server support following categories of Data type:

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


Exact Numeric Data Types in SQL

Exact numeric has nine types of sub data types in SQL server.

We use exact numeric data types for integer, decimal, and money. Each data type has its own lower, upper limit and memory requirements. We should use the smallest data type to save memory requirements as well. For example, we can use the bit data type for storing true (1) or false (0) values.

Data Type

Lower Range

Upper Range

Storage

Remarks

Bit

0

1

1 byte

We can also store NULL values in this.

tinyint

0

255

1 byte

We can store whole numbers up to 255 in this data type.

Smallint

-2^15 (-32,768)

2^15-1 (32,767)

2 bytes

We can store whole numbers between a lower and higher range.

Int

−2^31 (−2,147, 483,648)

2^31−1 (−2,147, 483,647

4 bytes

It also stores the whole number similar to a smallint but its lower and upper limits changes as defined.

Bigint

−2^63 (−9,223,372, 036,854,775,808)

2^63−1 (−9,223,372, 036,854,775,807)

8 bytes

We should use bigint data type if we cannot accommodate data in the integer data type.

Decimal

−10^38+1

10^381−1

It depends upon precision.

1 – 9 -> 5 bytes

10-19->9 bytes

20-28->13 bytes

29-28->17 bytes

We use decimal data type for

scale and fixed precision numbers.

Numeric

−10^38+1

10^381−1

It depends upon precision.

1 – 9 -> 5 bytes

10-19->9 bytes

20-28->13 bytes

29-28->17 bytes

Decimal and numeric are synonyms. We can use them interchangeably.

Smallmoney

-214,478.3648

+214,478.3647

4 bytes

We can use this data type for monetary or currency values.

Money

−922,337, 203, 685,477.5808

+922,337, 203, 685,477.5807

8 bytes

Approximate Numeric Data Types in SQL

SQL Approximate Numeric category includes floating point and real values. These datatypes in SQL are mostly used in scientific calculations.


Data Type

Lower Range

Upper Range

Storage

Remarks

Real

−3.40E+38

3.40E+38

4 bytes

We can use float924) as ISO synonym for real.

Float(n)

−1.79E+308

1.79E+308

Its storage depends upon value (n)

N(1-24) ->4 bytes

N(25-53)->8 bytes

It is an Approximate-number data types.

The default value of N is 53.

Date and Time SQL Server Data types

We stored date and time data along with time offset using these data types.

Data Type

Lower Range

Upper Range

Storage

Remarks

Date

0001-01-01

9999-12-31

3 bytes

1. It stores only dates in SQL Server.

2. Its default value is 1900-01-01.

3. It provides default format

YYYY-MM-DD.

Datetime

1753-01-01

9999-12-31

8 bytes

1. We can define a date along with time with fractional seconds.

2. The default value for this data type is 1900-01-01 00:00:00.

3.It provides accuracy in increments of .000, .003, or .007 seconds.

4. We should avoid using this data type. We can use Datetime2 instead.

Datetime2

0001-01-01 00:00:00

9999-12-31 23:59:59.9999999

6-8 bytes

1. Precision<3 -> 6 bytes

2.Precision 3 or 4-> 7 bytes

1. the default format for this is YYYY-MM-DD hh:mm: ss[.fractional seconds].

2. It provides precision from 0 to 7 digits, with an accuracy of 100ns.

2. The default precision for datetime2 is 7 digits.

Datetimeoffset

0001-01-01 00:00:00

9999-12-31 23:59:59.9999999

10 bytes

1. It is similar to a datetime2 data type but includes time zone offset as well.

2. Timezone offset is -14:00 through +14:00.

smalldatetime

1900-01-01 00:00:00

2079-06-06 23:59:59

4 bytes

1. It defines a date with the time of the day.

2. Its default value is 1900-01-01 00:00:00.

3. It provides an accuracy of one minute.

Time

00:00:00.0000000

23:59:59.9999999

5 bytes

1. We can use it for storing only time data.

2. Its default format is hh:mm:ss[.nnnnnnn].

3. It provides an accuracy of 100 nanoseconds.

Character Strings Data Types in SQL

This category is related to a character type. It allows the user to define the data type of character which can be of fixed and variable length. It has four kinds of data types. Below are the character string SQL server data types with examples.

Data Type

Lower Range

Upper Range

Storage

Remarks

Char(n)

0 characters

8000 characters

N bytes

1. It provides a fixed-width character data type.

2. It stores a maximum of 8,000 characters.

Varchar(n)

0 characters

8000 characters

n bytes + 2 bytes

1.It is a variable length character data type.

2.N defines the string size.

Varchar (max)

0 characters

2^31 chars

n bytes + 2 bytes ~ 2 GB

This is a character string with a variable width.

It stores a maximum of 1,073,741,824 characters.

We should avoid using this data type unless required due to its huge storage requirement.

Text

0 chars

2,147,483,647 chars

n bytes + 4 bytes

1. It is a variable-length character data type.

2. We should avoid using this data type as it might get deprecated in future versions of SQL Server.

3. It stores a maximum 2GB of text data.

Unicode Character Strings Data Types in SQL

This category store the full range of Unicode character which uses the UTF-16 character encoding.

Data Type

Lower Range

Upper Range

Storage

Remarks

Nchar

0 characters

4000 characters

2 times n bytes

It is a Unicode string of fixed width.

Nvarchar

0 chars

4000 Chars

2 times n bytes

Nvarchar is a Unicode string of variable width.

Ntext

0 chars

1,073,741,823 char

2 times the string length

1. It is a Variable-length Unicode data

2. We should avoid using this data type as it will be deprecated in future SQL releases.

Binary String Data Types in SQL

This category contains a binary string of fixed and variable length.

Data Type

Lower Range

Upper Range

Storage

Remarks

Binary

0 bytes

8000 bytes

N bytes

This data type is a fixed-width binary string.

varbinary

0 bytes

8000 bytes

Its storage is the actual length of string + 2 bytes.

Image

0 bytes

2,147,483,647 bytes

Avoid using this data type, as it will be deprecated in future SQL Server releases.

Other Datatypes in SQL

There are few data types as well that can be used as per the requirement:

  • Cursor: It is useful for variables or stored procedure OUTPUT parameter referencing to a cursor
  • Rowversion: It returns automatically generated, unique binary numbers within a database
  • Hierarchyid: it is a system data type with variable length. We use it to represent a position in a hierarchy
  • Uniqueidentifier: It provides 16 bytes GUID
  • XML: It is a special data type for storing the XML data in SQL Server tables
  • Spatial Geometry type: We can use this for representing data in a flat (Euclidean) coordinate system
  • Spatial Geography Types: We can use Spatial Geography type for storing ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. It represents data in a round-earth coordinate system
  • Table: It is a special data type useful for storing result set temporarily in a table-valued function. We can use data from this for processing later. It can be used in functions, stored procedures, and batches
Ref: