MS SQL Server supports a wide range of data types. There are a few more important data types that are included in the article. In this article, we will cover numeric SQL server data types and different date-time data types. Let’s discuss one by one.
- bit :
A bit is the smallest unit of a computer system. A bit can be either 0 or 1. The bit datatype can take NULL values as well.
A bit can take up to 8 bytes of storage while 2 bits can take up to 16 bits and the cycle continues.
- int :
A data type that can store integer values(positive and negative). The storage size is up to 8 bytes(-2⁶³ to 2⁶³-1). It is sub-categorized into tinyint, int, smallint, bigint. They can be used according to the number of bytes that can be stored.
- (i). bigint –
A numeric integer datatype that has the maximum storage of 8 bytes.(-2⁶³ to 2⁶³-1). It can store positive and negative values as well. It can be used for storing huge numbers.
- (ii). int –
A numeric integer datatype having a storage size of 4 bytes.
- (iii). smallint –
A numeric integer type that stores 2 bytes of data.
- (iv). tinyint –
A numeric datatype that stores 1 byte.
For example, a student roll number in a class table can be assigned as follows.
SQL server Numeric Data Types Table :
Numeric Data Type Integer size (In Bytes) bit Value(0, 1 or NULL) tinyint 1 smallint 2 int 4 bigint 8 decimal(p,s) 5 to 17 numeric(p,s) 5 to 17 smallmoney 4 money 8 float(n) 4 or 8 real 4
- (i). bigint –
- decimal :
A data type that can store decimal values. This datatype can be used for storing percentage values.
column_name decimal(precision, scale)
Precision is a term used for describing the number of digits to be stored from left to right while the scale is a term used for storing the number of digits after the decimal point.
Take the instance of e-commerce where the product delivery date and time are to be stored in the database. For such cases, there are few data types supported by MS SQL SERVER:
- date :
It stores the date in the format of yyyy-mm-dd.
- time :
It stores the time based on 24 hours clock.
- datetime2 :
It stores the date and time as well in the format of yyyy-mm-dd hh:mm: ss.
There are data types that can store money, unique identifiers, XML data, and much more. However, in the future versions of SQL Server, some data types will be removed for some reason. Make sure to use the data types that are available in the SQL Server. Different types of date-time data types table in the SQL server as follows:
Data type Size (In Bytes) datetime 8 datetime2 6 to 8 smalldatetime 4 date 3 time 3 to 5 datetimeoffset 8 to 10 timestamp Unique no.
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- SQL | Numeric Functions
- Mean and Mode in SQL Server
- Copy tables between databases in SQL Server using Import-and-Export Wizard
- Difference between MySQL and MS SQL Server
- Difference between Couchbase and MS SQL Server
- Create, Alter and Drop schema in MS SQL Server
- Difference between MS SQL Server and CouchDB
- Difference between MS SQL Server and IBM DB2
- Difference between Redis and MS SQL Server
- Full join and Inner join in MS SQL Server
- Left join and Right join in MS SQL Server
- Difference between Neo4j and MS SQL Server
- Self Join and Cross Join in MS SQL Server
- Union and Union All in MS SQL Server
- SIN() and COS() Function in SQL Server
- varchar, varchar(max) and nvarchar in MS SQL Server
- text, ntext, binary, varbinary and varbinary(max) in MS SQL Server
- Difference between char and nchar : MS SQL Server Datatypes
- Deterministic and Nondeterministic Functions in SQL Server
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.