Data Types in SQL

Divyansh Jain
Last Updated: May 13, 2022

Introduction

In an SQL Server table, each parameter, column, local expression, and local variable has a corresponding data type to be stored. A data type is an attribute that describes the sort of data that an object can carry, such as integers, characters, money, dates and times, binary strings, and so on.

In simple words, the type of value that can be stored in a table column is defined by SQL Data Types. 
For example: Consider if we want a column to contain only float values, then we can assign its data type as a float. 

SQL Data Types

In a database table, each column is required to have a name and a data type. A data type belonging to a column determines what type of value it can store: integer, character, binary, date and time, and so forth. The data type is a guideline for SQL to comprehend what type of data should be anticipated in each column, as well as how SQL should interact with the stored data.

When an operator combines two expressions with distinct data types, collations, precision, scale, or length, the following character traits of the result are determined:

  1. The result's data type is decided by applying the data type precedence rules to the input expressions' data types.
  2. When the result data type is char, nchar, nvarchar, varchar, text, or ntext, the collation is determined by the rules of collation precedence.
  3. The precision, size, and length of the output expressions are determined by the input expressions' precision, scale, and length.


The following categories can be used to classify SQL data types:

 

  1. Exact-Numeric data types such as bigint, tinyint, smallint, etc.
  2. Approximate-Numeric data types such as float and real.
  3. Date and Time data which includes Date, Time, smalldatetime, etc.
  4. Character Strings data types such as char, varchar, text, etc.
  5. Unicode character Strings data types such as nchar, nvarchar, ntext, etc.
  6. Binary Data types such as binary, varbinary, image.
  7. Miscellaneous data types - sql_variant, xml, cursor, etc.

 

Exact Numeric Data Type

These are values when precision and scale must be maintained INTEGER, BIGINT, DECIMAL, NUMERIC, NUMBER, and MONEY are the exact numeric kinds.

Data Type

Description

Lower limit

Upper limit

Memory

bigint

It saves whole numbers in the specified range.

−2^63 (−9,223,372, 036,854,775,808)

2^63−1 (−9,223,372, 036,854,775,807)

8 bytes

int

It saves whole numbers in the specified range.

−2^31 (−2,147, 483,648)

2^31−1 (−2,147, 483,647)

4 bytes

smallint

It saves whole numbers in the specified range.

−2^15 (−32,767)

2^15 (−32,768)

2 bytes

tinyint

It saves whole numbers in the specified range.

0

255

1 byte

bit

It can take 0, 1, or NULL values.

0

1

1 byte/8bit column

decimal

Scale and fixed precision numbers are used.

−10^38+1

10^381−1

5 to 17 bytes

numeric

Scale and fixed precision numbers are used.

−10^38+1

10^381−1

5 to 17 bytes

money

Used monetary data

−922,337, 203, 685,477.5808

+922,337, 203, 685,477.5807

8 bytes

smallmoney

Used monetary data

−214,478.3648

+214,478.3647

4 bytes

 

 

Example:

DECLARE @Exact_Data_type_Decimal DECIMAL (3,2) = 2.31
PRINT @Exact_Data_type_Decimal

Output:

2.31

 

Approximate-Numeric Data Type

These are values where precision must be maintained but the scale can be arbitrary DOUBLE PRECISION, FLOAT, and REAL are the approximate numeric types.

Data Type

Description

Lower limit

Upper limit

Memory

Precision

float

A floating precision number is represented by this symbol.

−1.79E+308

1.79E+308

Depends on the value of n

7 Digit

real

A floating precision number is represented by this symbol.

−3.40E+38

3.40E+38

4 bytes

15 Digit

 

Note: The n parameter shows out whether the field should hold 4 or 8 bytes. A 4-byte field is stored in float(24), and an 8-byte field is stored in float(53).  And the default value of n is set to be 53.

 

Example :

DECLARE @approx_Data_type_Float FLOAT(24) = 22.1234
PRINT @approx_Data_type_Float

Output:

22.1234

 

Date & Time Data Type

This section covers the data types that Snowflake supports for maintaining dates, times, and timestamps (combined date + time). It also explains the supported formats for string constants used in date, time, and timestamp manipulation.

Data Type

Description

Storage size

Accuracy

Lower Range

Upper Range

DateTime

From January 1, 1753 to December 31, 9999, this format is used to specify a date and time. It has a 3.33 millisecond precision.

8 bytes

Rounded to increments of .000, .003, .007

1753-01-01

9999-12-31

smalldatetime

From January 1, 0001 through December 31, 9999, this value is used to represent a date and time. It is accurate to within 100 nanoseconds.

4 bytes, fixed

1 minute

1900-01-01

2079-06-06

date

Only dates from January 1, 0001 to December 31, 9999 were stored.

3 bytes, fixed

1 day

0001-01-01

9999-12-31

time

Only time values with a precision of 100 nanoseconds are stored.

5 bytes

100 nanoseconds

00:00:00.0000000

23:59:59.9999999

datetimeoffset

It's similar to datetime, but with a  time zone offset.

10 bytes

100 nanoseconds

0001-01-01

9999-12-31

datetime2

From January 1, 0001 through December 31, 9999, this number is used to denote a date and time.

6 bytes

100 nanoseconds

0001-01-01

9999-12-31

 

Example:

DECLARE @date_Data_type_Date DATE'2030-01-01'
PRINT @date_Data_type_Date

Output:

‘2030-01-01’

 

Character Strings Data Type

Strings of letters, numbers, and symbols are stored. Character string types are made up of the data types CHARACTER (CHAR) and CHARACTER VARYING (VARCHAR), and the values of character string types are called character strings.

Fixed-length or variable-length strings can be used to hold character data. Variable-length strings are not extended; fixed-length strings are right-extended with spaces on output.

In SQL statements, string literals must be enclosed in single quotes.

Data Type

Description

Lower limit

Upper limit

Memory

char

It's a character string with a predetermined length. It can hold up to 8,000 characters.

0 chars

8000 chars

n bytes

varchar

This is a variable-width character string.

0 chars

8000 chars

n bytes + 2 bytes

varchar (max)

This is a variable-width character string. It can hold up to 1,073,741,824 characters.

0 chars

2^31 chars

n bytes + 2 bytes

text

This is a variable-width character string. It can hold up to 2GB of text data.

0 chars

2,147,483,647 chars

n bytes + 4 bytes

 

Example  :

DECLARE @char_Data_type_Char VARCHAR(30) = 'This is Character Datatype'
PRINT @char_Data_type_Char

Output:

This is Character Datatype

 

Unicode Character Strings Data Type

The nchar and nvarchar Unicode data types are used to store Unicode data in UTF-16 format. They behave in the same way that char and varchar do. The strings are encoded as single-byte or multibyte forms dependent on the value of the II CHARSETxx variable in the case of char and varchar. The char and varchar strings are encoded as UTF-8 strings if II CHARSETxx is set to UTF8. Unicode data is represented by UTF-8 and UTF-16 encoded strings.

Data Type

Description

Lower limit

Upper limit

Memory

     nchar

It's a fixed-width Unicode string.

0 chars

4000 chars

2 times n bytes

nvarchar

It's a variable-length unicode string.

0 chars

4000 chars

2 times n bytes + 2 bytes

ntext

It's a variable-length unicode string.

0 chars

1,073,741,823 char

2 times the string length

 

 

Example :

DECLARE @unic_Data_type_nChar VARCHAR(30) = 'This is nCharacter Datatype'
PRINT @unic_Data_type_nChar

Output:

This is a nCharacter Datatype

 

Binary Data Type

Up to 65000 bytes of raw byte data, such as IP addresses, can be stored. Types of data Binary string types include BINARY and BINARY VARYING (VARBINARY), and binary string values are known as binary strings. A binary string is an octet or byte sequence.

Data Type

Description

Lower limit

Upper limit

Memory

binary

It's a binary string with a fixed length. It has a maximum storage capacity of 8,000 bytes.

0 bytes

8000 bytes

n bytes

varbinary

This is a variable-length binary string. It can hold up to 8,000 bytes.

0 bytes

8000 bytes

The actual length of data entered + 2 bytes

image

This is a variable-length binary string. It has a maximum storage capacity of 2GB.

0 bytes

2,147,483,647 bytes

 

 

Example :

DECLARE @binary_Data_type BINARY(2) = 12;
PRINT @binary_Data_type

Output:

0x000C

 

Misc Data Type

Different data types that do not fall into the categories of string data types, binary data types, date and time, or numeric data types are referred to as miscellaneous or other data types in SQL.

Data Type

Description

Cursor

It produces an sp cursor list and sp describe cursor column as output.It returns the cursor variable's name.

Row version

It stamps table rows in this version.

Hierarchyid

This data type represents a level in the Conversion 

Uniqueidentifier

From a character expression, convert.

Sql_variant

It saves the values of Datatypes that are supported by SQL Server.

XML

It uses a column to hold XML data.

Spatial Geometry type

It uses a flat coordinate system to represent data.

Spatial Geography type

It is a coordinate system that represents data in the round-earth coordinate system.

table

It saves a set of results for later use.

 

Some points to remember

Some data types in SQL Server are classified into the following classes based on their storage characteristics:

  • Large value data types: nvarchar(max) and varchar(max)
  • Large object data types: varbinary(max), text, ntext, image, and xml
     

Note: sp_help returns -1 as the length for the xml and large-value data types.

 

Frequently Asked Questions

  • Why do we use Approximate numeric data types to store floating-point values?
    To represent large values, the system saves the location of the decimal point individually. Because real and double-precision numeric data types are stored compactly, approximate numeric types can be an alternative for space considerations, but they can result in imprecise rounding during computations.
     
  • Why Character strings the most commonly used data types?
    Any sequence of letters, numerals, punctuation, and other legal characters can be stored in them.
    Names, descriptions, and mailing addresses are examples of common character strings. Although any value can be stored in a character string, you should only use it when other data types aren't suitable. Other data types offer better data validation and storage efficiency.
     
  • What is the use of Uniqueidentifier DataType in SQL Server?
    The uniqueidentifier data types in SQL are used to store globally unique identifiers (GUID).
     
  • How do Unicode character strings act differently than character strings?
    They perform similarly to the char, varchar, and long varchar character types, with the exception that each Unicode character is typically 16 bits long. nchar types, like their local character counterparts, have a fixed length, whereas nvarchar and long nvarchar have variable lengths.

Key Takeaways

To summarise the subject, we looked at how and why data types are so important when it comes to SQL Server, and how all types of data types work differently in their domain. And the list does not stop here, MySQL has various interesting topics to read, which you can refer to in our other articles.

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Was this article helpful ?
0 upvotes

Comments

No comments yet

Be the first to share what you think