SQL Data Types

Published in

baakademi

5 min readOct 25, 2020

This blog will try to sum up sql data types. SQL Data Type is an attribute that indicates the type of data of any object. According to your requirements for the project or database you can specify a data type for a table column. This is a mandatory and essential step in designing table in order not to encounter a problem in the process.

Numerics (Exact or Approximate)

bit : It is an integer data type of 1 byte size. In order to save space in the database, the string values TRUE and FALSE usually can be converted to bit values as 1 and 0.

tinyint : It is an integer data type with a size of 1 byte, used for integer data between 0 and 255 values.

smallint : It is an integer data type with a size of 2 bytes, which can take values between -32.768 and 32.768.

int or integer : It is the primary integer data type in SQL Server. It is used for between -2.147.483.648 and 2.147.483.647 values; takes up 4 bytes size.

bigint : It is intended for use when integer values might exceed the range that is supported by the int data type. It takes up 8 bytes and takes values between -9.223.372.036.854.775.808 and 9.223.372.036.854.775.807.

decimal or dec : In this data type, the number of digits of the number and scale to be stored can be defined. The size depends upon precision. The number of precision digits can be defined between 1 and 38.

numeric : Decimal and numeric are functionally identical and can be used interchangeably.

float : Used for floating-point numbers which are approximate numeric values. And the size varies according to the parameter it takes. Between -1.79E + 308 and 1.79E + 308 values it takes.

real : The ISO synonym for real is float(24). Between -3.40E + 38 and
3.40E + 38 values it takes. No parameters are required when declaring this type, takes up 4 bytes.

money : Data types that represent monetary or currency values. It stores data of decimal type sensitive up to 4 digits. It takes up 8 bytes

smallmoney : Same as money type only takes up 4 bytes.

Date and time

date : Its format is YYYY-MM-DD and takes up 3 bytes.

time : Its default format is hh:mm:ss[.nnnnnnn] and takes up between 3 to 5 bytes.

datetime : It defines a combined data type date with a time of day with fractional seconds(.nnnnnnn).It takes up 8 bytes.

smalldatetime : It is used date and time values only between 1900–01–01 00:00:00 and 2079–06–06 23:59:59. It takes up 4 bytes.

timestamp : It generates automatically binary numbers, unique in the database, used mostly to the rows identification.

datetimeoffset : It is used for keeping the time difference that varies according time zones.

Character strings

varchar(n) or character varying(n) : It can contain numbers, letters, and special characters. It takes up as much as the size data.

char(n) or character(n) : They can have values up to 8000 characters. It is a character string with a fixed width.
It means if you assign a value to a character column containing fewer characters than the defined length, the remaining space is filled with blanks characters.

nchar : Same as char additionally supports Unicode.

CLOB [(length)] : A Character Large OBject (or CLOB) is a collection of character data in a database management system, usually stored in a separate location that is referenced in the table itself.

nvarchar(max) or nvarchar(n) : national character varying type is the same as varchar except that it holds standardized multibyte characters or Unicode characters. If max is specified, the maximum number of characters is 2GB.(max length : 4000 characters)

text : Even if a value less than the specified value is entered, it takes up to its size. It does not support Unicode.

ntext : It takes up as much as the entered character size of data, supports Unicode.

What is Unicode?

According to unicode.org “Unicode provides a unique number for every character, no matter what the platform, no matter what the program, no matter what the language.
Unicode is required by modern standards such as XML, Java, ECMAScript (JavaScript), LDAP, CORBA 3.0, WML, etc., and is the official way to implement ISO/IEC 10646.
Unicode enables a single software product or a single website to be targeted across multiple platforms, languages and countries without re-engineering. It allows data to be transported through many different systems without corruption.”

When the data types start with n in order to support Unicode. They will take twice as much space in the database.(2*n)

Binary string

BLOB(n) : It is used for BLOBs (Binary Large Objects). It can take up to 65,535 bytes.

binary : It presents 1 and 0 with fixed length. The maximum length can be up to 8000 bytes.

varbinary : It is a binary string of variable width. It takes up maximum of 8,000 bytes.

image : It is a binary string of variable width up to 2³¹-1 (2,147,483,647) bytes.

Other data types

boolean : Stores TRUE or FALSE values. It is fundamentally different from bit.

cursor : For storing the reference to cursors in a variable or in a procedure (not for “create table”).

sql_variant : It is used to store values with different data types such as number, text, binary. It is a kind of blended data type.

geometry : It represents data in a flat coordinate system.

geography : It is used for GPS data and geographic coordinates defined by the Open Geospatial Consortium (OGC) standard .

hierarchyid : This datatype represents a position in the hierarchy.

xml : It is a special data type for storing the XML data in SQL Server tables. The size is variable.

uniqueidentifier : It stores 16 bytes long globally unique identifier (GUID).

We should be aware that not all data types are supported by every relational database vendors. For instance, Oracle database doesn’t support datetime and MySQL doesn’t support CLOB data type.