Understanding SQL Server Data Types

Understanding SQL Server Data Types

3 mins read284 Views Comment
clickHere
Updated on Sep 24, 2021 18:24 IST

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.

2021_09_SQL-Server-Data-Types.jpg

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.

About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio

Comments