SQL Data Types

Overview

SQL Data Types-In SQL server columns, variables, and parameters hold the value of the defined datatype. The SQL server data type defines the type of data a particular database object can store. In database design, the assignment of datatype over database objects plays an important role in terms of the performance and efficiency of the database. In T-SQL you can define your own datatype.

Various databases may have variations in data types or they can have some additional supporting data types. Learning how about data types and their significance in databases is important

SQL (Structured Query Language) is a language used to interact with relational and non-relational databases.
The data type defines the type of data that can be stored in a database column or variable.

When the operator combines two expressions with distinct data types, length, collations, precision, and scale the characteristics of the result are defined by:

  • The resultant data type is determined based on data type precedence to the SQL data types of the input expressions
  • the rules of collation precedence determine the collation of the resultant data type when its char, text, varchar, next, etc.
  • The input expressions define the length precision, and scale of the SQL data types

SQL Data Types

 

 

Data Type Category:

In SQL Server based on storage characteristics, some SQL data types are designed in the following groups

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

The following Table contains the data type category

CategoryData Types
Exact numerics decimal, numeric, money, smallmoney,bit, tinyint, smallint, int, bigint
Approximate numericsReal, Float
Date & Timedate, datetime2, datetimeoffset, time,smalldatetime, datetime
Character stringstext,char, varchar
Unicode Character stringsnchar, nvarchar, ntext
Other data typescursor, hierarchyid,spatial Geography types, rowversion, uniqueidentifier, xml, table, sql_variant, spatial Geometry types

Numeric Data Types

The Exact numeric sql data types store numeric values such as integer, decimal, or monetary amounts, bit.

  • The int, bigint, smallint, and tinyint data types store integer data values and do not store negative integer values.
  • The bit store either 0,1 or NULL values.
  • The decimal and numeric data type store numbers which have fixed precision and scale. The decimal data type can store Negative values
  • The smallmoney and money data type stores currency values.

The below table provides details about numeric data types

Data Type limitMemory
tinyint0 to 2551 byte
bit0, Null, 11 byte
decimal−10^38+1 to 10^381−15 to 17 bytes
numeric−10^38+1 to 10^381−15 to 17 bytes
money−922,337, 203, 685,477.5808 to +922,337, 203, 685,477.58078 bytes
smallmoney−214,478.3648 to +214,478.36474 bytes
bigint−2^63 (−9,223,372, 036,854,775,808) to 2^63−1 (−9,223,372, 036,854,775,807)8 bytes
int-2,147,483,648 to 2,147,483,6474 bytes
smallint-32,768 to 32,767 2 bytes

Date and Time Data Types

The date & time sql data datatypes store the date and time values. A couple of date and time data type offer to store timezone also such as datetimeoffset.

Data TypeStorage sizeLower RangeUpper Range
date3 bytes0001-01-019999-12-31
time5 bytes00:00:00.000000023:59:59.9999999
datetimeoffset10 bytes0001-01-019999-12-31
datetime26 bytes0001-01-019999-12-31
datetime8 bytes1753-01-019999-12-31
smalldatetime4 bytes1900-01-012079-06-06

Approximate numeric data types

This SQL data types stores floating-point numeric data, which is used in various business applications

Data TypeLower limitStorage Memory
float(n)−1.79E+308 to 1.79E+308Depends on the value of n
real−3.40E+38 to 3.40E+384 bytes

Character strings data types

Character string SQL data types store fixed and variable length data and text data types can store non-Unicode data.

Data TypeLower RangeUpper RangeMemory
varchar (max)0 2^31 n bytes + 2 bytes
text0 2,147,483,647 n bytes + 4 bytes
char0 8000 n bytes
varchar0 8000 n bytes + 2 bytes

Unicode character string data types

Unicode character string SQL data types store fixed and variable length  Unicode data

Data TypeRangeMemory
ntext0 to 1,073,741,823 2 time the string length
nchar0 to 40002 time n byte
nvarchar0 to 40002 time n byte+ 2 byte

Binary string data types

Binary string SQL data types store variable and fixed-length data

Data TypeValueDescription
binary1 to 8000 bytesn bytes
varbinary1 to 8000Actual length of string + 2 bytes
Image0 to 2,147,483,647

Other data types

Data TypeUsage Description
sql_variantused to store values of other data types
XMLused to store XML data in a column
Spatial Geometry typeUsed to represent data in a flat coordinate system.
Spatial Geography typeIt store ellipsoidal data, such as GPS latitude and longitude coordinates.
tablethe table store a result temporarily for further processing
cursorfor stored procedure OUTPUT parameter or variables that consits a reference to a cursor
rowversionGenerates unique binary numer in database
hierarchyidvalue represents the position in a tree hierarchy
uniqueidentifier16-byte GUID

Define a Datatype

SQL Data Types are defined at the time of table creation or variable declarations. We have to define the data type and size while creating a table or declaring variables.

create table userdetails
(
username varchar(50),
userid int
);

 

 

In the above  example, we have created user details table  in which we have defined username, userid columns which have datatype varchar(50), int sequentially

The characteristics of data type

  • The size of datatypes may be fixed or variable
  • The required storage space is defined based on the size of the datatype
  • We can perform datatype conversion using TSQL inbuilt functions

We can do efficient memory allocation by assigning appropriate data types to the columns.

Note– Whenever we pass string values to the select statements we must enclose the data in the single quote (”). Numeric data type need not pass in a single quote.

For more details please follow the below link

sqldatatype

 

 

1 thought on “SQL Data Types”

Comments are closed.