Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- SQL Server Blogs -

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.

What are SQL Data Types?

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

Exact Numeric

Decimal (P,S)


CREATE TABLE test_sql_server_decimal (

dec_col DECIMAL (4, 2),

);

Output

Exact Numeric

Difference Between Decimal And Numeric Datatypes

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

What are SQL Data Types?

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

Datatypes

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

Datatypes

Date and Time Datatype

Read: Different Type Of SQL Functions And Their Uses

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

Datatypes

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

Datatypes

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

Datatypes

SmallDatetime

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


create table smalldatetimeeg

(

smalldatetimetest smalldatetime

)

Output

Datatypes

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

Datatypes

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

Datatypes

Character and String data types

Read: RDBMS Interview Questions

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

Datatypes

Varchar(n)

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

Datatypes

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

Datatypes

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

Datatypes

Nvarchar(n)

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


create table tablenvarchartest

(

testchar nvarchar(500)

)

Output

Datatypes

Nvarchar(max)

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

Read: How To Start Your Career As MSBI Developer?

create table tablenvarcharmaxtest

(

testchar nvarchar(max)

)

Output

Datatypes

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 sqlbinary 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

Datatypes

Varbinary

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


create table tablevarbinarytest

(

testvarbinary varbinary(1000)

)

Output

Datatypes

varbinary(max)

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


create table tablevarbinarymax

(

testvarbinary varbinary(max)

)


Output

Datatypes

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!




    Sanchayan Banerjee

    I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

14 days 12 Jun 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

6 days 04 Jun 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

10 days 08 Jun 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

4 days 02 Jun 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

1 day 30 May 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

3 days 01 Jun 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 03 Jun 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

10 days 08 Jun 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews