What are Data Types and Their Usage in SQL Server Tables?

We realize tables are database objects that are utilized to store data in a table. These data can be of date, strings, and numbers. These data are stored in various fields of the table. But, how SQL Server does arrange and comprehends what sort of information it is storing in the table. That is where data types come into play. SQL Server allows the developer to indicate which column in a table will hold what sort of data. For instance, if you have made a table called the employee in a database and you have a column called name in it, you can specify SQL Server to store just string or character information in that column. These details are called data types. In the following write-ups we will get to know the following-

• What are SQL Data Types?
• Different types of SQL Data Types

We will also understand the requirement of having such a significant number of sub-data types under the major data types and the process of memory management related to it.

Read More: SQL Server Tutorial Guide for Beginner

What are SQL Data Types?

SQL Data Types portray the TYPE OF VALUE that can be stored in a column of a table. For instance, if we want a SQL table column to store just integer values, then we can characterize the information type as int.

SQL data types can be broadly divided into the following categories:

• Numeric data types such as int, tinyint, bigint, float, real etc.
• Date and Time data types such as Date, Time, Datetime, etc.
• Character and String data types such as char, varchar, text etc.
• Unicode character string data types, for example, nchar, nvarchar, ntext etc.
• Binary data types such as binary, varbinary etc.
• Miscellaneous data types – clob, blob, xml, cursor, table etc.

Numeric data types

Numeric data types are numbers stored in database columns.

There are two types of numeric data types-

Exact Numeric

SQL's exact numeric data types comprise of NUMERIC (p,s) and DECIMAL(p,s) subtypes. They are exact, and we characterize them by precision (p) and scale (s). Precision is an integer that represents the total number of digits permitted in this column. These digits are in a specific radix, or number base – ie binary (base-2) or decimal (base-10). They are generally characterized by a decimal point. The scale, also an integer value represents the number of decimal spaces to the left (if positive) or right (if negative; this is once in a while utilized) of the decimal point.

Numeric(p,s)

CREATE TABLE test_int (

accountNo integer,

balance numeric(8,2)

);

Output

Decimal (P,S)

CREATE TABLE test_sql_server_decimal (

dec_col DECIMAL (4, 2),

);

Output

Difference Between Decimal And Numeric Datatypes

Both decimal and numeric are actually the equivalent. They are the same thing with an alternate name.

SMALLINT, INT, TINYINT, INT

Datatype	Range	Storage
BIGINT	-263 (-9,223,372,036,854,775,808) to 263-1 (9,223,372,036,854,775,807)	8 Bytes
INT	-231 (-2,147,483,648) to 231-1 (2,147,483,647)	4 Bytes
SMALLINT	-215 (-32,768) to 215-1 (32,767)	2 Bytes
TINYINT	0 to 255	1 Bytes

CREATE TABLE sql_server_integers

(

bigint_col bigint,

int_col INT,

smallint_col SMALLINT,

tinyint_col tinyint

);

Output

Approximate 

These are values where the exactness should be saved and the scale can be floating. The approximate numeric types are DOUBLE PRECISION and FLOAT.

Double Precision and Float

Create table floatanddouble_test

(

double_eg DOUBLE PRECISION,

float_eg float(2)

)

Output

Date and Time Datatype

Read: SQL Server on the Cloud - It is not that Cloudy

The date and time data types store date and time data, and the date time offset. Examples of date and time data types are datetime, smalldatetime, date, time, datetime2, and datetimeoffset.

Date

To store the date, data in the SQL Server table we utilize the SQL Server Date data type.

CREATE TABLE Date_Example

(

valid_from DATE NOT NULL,

valid_to DATE NOT NULL,

)

Output

Time

The SQL Server Time data type defines a time of a day based on a 24-hour clock.

CREATE TABLE Time_Test

(

start_at TIME(0),

)

Output

Datetime

The data type characterizes a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

create table testdatetime

(

datetimevar datetime

)

Output

SmallDatetime

This data type characterizes a date that is combined with a time of day.

create table smalldatetimeeg

(

smalldatetimetest smalldatetime

)

Output

Difference Between Datetime And Smalldatetime

The datetime variable rounds up the fractional seconds part. This is because datetime consistently rounds to increments of .000, .003, or .007 seconds. The smalldatetime variable on the other hand, rounds up the minutes part. Not simply that, the seconds part is set to zero.

datetime2

The data type characterizes a date that is combined with a time of day that is based on a 24-hour clock.

create table datetime2eg

(

testdattime datetime2

)

Output

Datetimeoffset

The Datetimeoffset enables you to control any single point in time, which is a datetime value, along with an offset that specifies how much that datetime differs from UTC.

create table datetimeoffseteg

(

testdattimeoffset DATETIMEOFFSET

)

Output

Character and String data types

Read: Different Types of SQL Server & SQL Database Functions

There are four diverse character data types that store character strings: char, varchar, varchar(max), and text

Char

CHAR is a fixed length string data type, so any residual space in the field is cushioned with blanks. CHAR takes up 1 byte per character. So, a CHAR (100) field (or variable) takes up 100 bytes on disk, regardless of the string it holds

create table chartest

(

testchar char(10)

)

Output

Varchar(n)

A varchar(n) or Variable Character Field is a set of character data of uncertain length. The term varchar refers to a data type of a field (or column) in a Database Management System which can hold letters and numbers.

create table varchartest

(

testchar varchar(50)

)

Output

Varchar(Max)

When we store data to a VARCHAR (MAX) column, behind the screen the data is handled as a TEXT value. So there is some extra processing required when dealing with a VARCHAR (MAX) value.

create table varcharmaxtest

(

testchar varchar(MAX)

)

Output

Difference Between Varchar(N) And Varchar(Max)

Varchar(n)  is a variable, you can assign a value to it, it can receive an int from 1 to 8000 or max.

varchar(max) is a constant, it has a value of max.

Unicode character string data types

In SQL, Unicode character string data types are utilized in a circumstance where we required storing a huge data. In Unicode character string, we have an alternate type of string data types available, those are nchar,nvarchar,nvarchar(max).

Nchar

Nchar is a fixed length character string and we can store maximum 4000 characters.

create table tablenchartest

(

testchar nchar(500)

)

Output

Nvarchar(n)

Nvarchar(n) is a variable length character string and we can store maximum 4000 characters.

create table tablenvarchartest

(

testchar nvarchar(500)

)

Output

Nvarchar(max)

Nvarchar(max) is a variable length character string and we can store maximum 2^30-1 characters (upto 2 GB)

Read: SQL Server Reporting Service: All You Need to Know about Parameterized Reports

create table tablenvarcharmaxtest

(

testchar nvarchar(max)

)

Output

Difference Between Nvarchar(N) And Nvarchar(Max)

Nvarchar(n) is used for storing a variable-length unicode string. The variable, n, – for the mathematically inclined – denotes the length of the string data and this can be anything between 1 and 4,000.

The “max” in nvarchar(max) denotes a maximum storage size of 2 GB. Values stored in a nvarchar(N) are physically stored in the same way. However for the nvarchar(max) datatype , the values are treated as a TEXT value thus some additional processing is required, particularly when the size surpasses 8000.

Binary data types

In sql, binary data types are used to store any kind of binary data like images, word files, text files, etc. in table. In binary data types we have an option like allow users to store fixed length or variable length of bytes based on necessities.

Binary(n)

Binary(n) is a fixed length binary data and we can store a maximum of 8000 bytes

create table tablebinarytest

(

testbinary binary(1000)

)

Output

Varbinary

Varbinary or variable length binary data and we can store maximum 8000 bytes

create table tablevarbinarytest

(

testvarbinary varbinary(1000)

)

Output

varbinary(max)

Varbinary (max) is a variable length character string and we can store maximum 2GB data

create table tablevarbinarymax

(

testvarbinary varbinary(max)

)

Output

Miscellaneous data types

In sql, we have an alternate data types which will not come under string data types, binary data types, date and time and numeric data types those will be called miscellaneous or other data types.

Example of miscellaneous data types are cursor,xml,table etc

SQL Data Types and memory management

The different data type has a particular memory necessity. Therefore, it makes more sense to define the column or variable with the data type it will hold for efficient utilization of memory. For instance, if you have a table called Employee where you have a column called Gender which determine whether the employee is male or female, ie M or F ,there is no point in declaring a column with a datatype varchar(50) which would use up lot of unnecessary space. Instead of declaring the column as char will be sufficient. Similarly, for a field that would contain age, datatype int will be enough.

Summary

The above writeup is an extensive study of every data type that is available in SQL Server. This would give the reader a fare beat of an idea about these data types as well as the syntax to use them while creating tables. Happy querying!

FaceBook Twitter Google+ LinkedIn Pinterest Email