Understanding SQL Server Data Types

An SQL data type is like a tag or a label that defines what kind of value a column in a database table can contain. Data types describe how the programmer intends to use the data. With the knowledge of data types, it becomes easier for developers to specify data in a particular column of the table more accurately. SQL offers various data types to give developers more flexibility over how they store data. This blog will give you an overview of various SQL Server data types such as numeric, string, and date and time.

Explore popular Databases Courses

SQL Server Data Types

Every column in a database table needs to have a name and a data type. Data types tell how SQL will interact with the stored data and the operations that can be performed on the data. SQL data types define the kind, size, and range of data that an object can store. Here are the most important data types in SQL.

To learn about databases, read our blog – What is a Database?

Numeric Data Types

Numeric data types in SQL store exact or decimal numeric data. They include bit, tinyint, int, and bigint. A bit stores a small number while bigint is for storing large numbers.

Exact Numeric Data Types

Data Type	Description	Range
bit	Stores integers – 0, 1, or NULL	0 to 1
tinyint	Stores very small integers	0 to 255
smallint	It stores small integers	-2^15 (-32,768) to 2^15-1 (32,767)
int	Stores a medium integer	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
bigint	Stores large integers	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
numeric(p,s)	Represents a numeric value (‘p’ denotes precision value and ‘s’ denotes scale value)	-10^38 +1 to 10^38 -1
decimal(p,s)	Represents a decimal value (‘p’ denotes precision value and ‘s’ denotes scale value)	-10^38 +1 to 10^38 -1
smallmoney	Specifies monetary or currency data	-214,748.3648 to 214,748.3647
money	Specifies monetary or currency data	-922,337,233,685,477.5808 to 922,337,203,685,477.5807

Approximate Numeric Data Types

Data Type	Description	Range
float	Specifies a floating point number	-1.79E + 308 to 1.79E + 308
real	Floating precision number data	-3.40E + 38 to 3.40E + 38

Check out the best SQL Courses

String or Character Data Types

String or Character data types in SQL store strings such as names and locations. They can also store large text files.

Data Type	Description	Maximum Size
text	it stores a variable-length character string	2GB of text data
char	Stores a character string of fixed length and is represented as char [ ( n ) ] where n defines the string size in bytes	8,000 characters
varchar	Stores a variable-length character string	8,000 characters

Unicode Character Strings

Data Type	Description	Maximum Size
nchar	A fixed-length Unicode string	4,000 characters
nvarchar	Variable length Unicode string	4,000 characters
ntext	A variable-length Unicode string	2 GB of text data

Also Read: Knowledge Discovery in Databases (KDD) in Data Mining

Date and Time Data Types

This data type allows you to store a date as a date/time object instead of a string. This makes it easy for SQL developers to work with data later.

Data Type	Description	Range
date	Stores date in YYYY-MM-DD format	0001-01-01 to 9999-12-31
time	Stores time in hh:mm:ss format	00:00:00.0000000 to 23:59:59.9999999
datetime	It stores date and time in YYYY-MM-DD hh:mm:ss format with an accuracy of 0.00333 second	1753-01-01 to 9999-12-31
smalldatetime	Stores date and time in YYYY-MM-DD hh:mm:ss format with an accuracy of 1 minute	1900-01-01 to 2079-06-06
datetime2	Stores date and time in YYYY-MM-DD hh:mm:ss format with an accuracy of 100 nanoseconds	0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999
datetimeoffset	Similar to datetime2, also contains a time zone offset in YYYY-MM-DD hh:mm:ss [{+|-}hh:mm] format	0001-01-01 to 9999-12-31

Binary Data Types

Data Type	Description	Maximum Size
binary	Fixed-length binary data with a length of n bytes	8,000 bytes
varbinary	Variable-length binary data	8,000 bytes
varbinary(max)	Variable-length binary string	2^31-1 bytes
image	Variable-length binary data	2,147,483,647 bytes

Also Read: Top Database Interview Questions and Answers

Other Data Types

Data Type	Description
cursor	Stores a reference to a cursor object
sql_variant	Stores values of different SQL Server supported data types [exceptions: text, ntext, and timestamp]
xml	It stores XML formatted data
table	Stores a result-set for later processing
uniqueidentifier	Stores a GUID (globally unique identifier)

Conclusion

In this article, we learned about different data types in SQL. This information will help you choose the right data type for different situations while creating tables in the database. Careful selection of the data types will enable your database to function correctly and be optimized.