Rajendra Gupta

An overview of SQL Server data types

December 9, 2019 by

In this article, we will give an overview of various SQL Server data types.

Introduction

In SQL Server, a data type defines the type of data in a table column or variable. It is a mandatory and essential step in designing a table. A table with inappropriate data types can lead to various issues such as improper query optimization, performance issues, data truncation.

To understand SQL Server data types, let’s look at the following page to create a new Google account (for reference purpose only):

Google sign up page

It requires the following inputs:

  • First name and Last name: Suppose we require only alphabets in these fields
  • Username: We can use letter, numbers and periods in the user name
  • Password: We can use alphabets, numbers, and special characters

We can consider a few more examples:

  • Contact Number: It should be a numeric field
  • Pin Code: We might expect both alphabets and numeric fields in this column

We store all these columns data in a table of SQL Server. We can use various data types in SQL Server and use them as per the requirement. Let’s explore the SQL Server data types.

Categories of SQL Server data types

SQL Server supports the following data type’s categories:

  • Exact numeric: bit, tinyint, smallint, int, bigint, decimal, numeric, money and smallmoney
  • Approximate numeric: Read and float
  • Date and time: date, DateTime, datetime2, datetimeoffset, smalldatetime, time
  • Character strings:char, varchar, text
  • Unicode character strings: Nchar, Nvarchar, Ntext
  • Binary strings: Binary, image and varbinary
  • Other data types: Cursor, hierarchyid, sql_variant, table, rowversion, uniqueidentifier, XML, Spatial and geography

Categories of SQL Server Data Types

Note: SQL Server will deprecate ntext, text and image data type in future releases. We should avoid the use of these data types. You can use Nvarchar(max), varchar(max), and varbinary(max).

Exact numeric SQL Server data type

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 SQL Server data type

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 SQL Server Data types

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.

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

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.

Unicode character string SQL Server data types

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 SQL Server data types

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 data types

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

Conclusion

In this article, we explored SQL Server data types in brief. You should be aware of all these data types and use the appropriate data type for better storage, query processing, and data retrieval.

Rajendra Gupta
24,369 Views