Let’s discuss about SQL here, the Introduction & datatypes that we use in databases to store data.
SQL: Structured Query Language
- A database computer language designed for the retrieval and management of data in a relational database
- Used to access and manipulate/manage databases
- SQL is the standard language for many commonly used Relational Database Management Systems (RDBMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server.
Some Important notations/points:
- Semicolon (;) is suggested to use after each query, it generally denotes the end of a statement/query. Used necessarily, if multiple statements/queries are there on worksheet and have to be executed at once.
- SQL keywords are not case-sensitive, it means ‘select’ is as same as ‘SELECT’ or ‘Select’.
- NULL – Denotes a field with no value or unknown data, but not a field having values like zero(0) or space( ).
- CONSTRAINTS – Rules that used to limit the type of data into a column, e.g., unique value, restricting null value, etc.
- INDEX- Basically, a Search keyused to retrieve data from the database more quickly, it speeds up searches/queries.
SQL Data types define the type of input/value that can be stored in a column or field.
Data types can be divided broadly into various categories-
- Numeric Data types
- Non-Unicode Character String Data types
- Unicode Character String Data types
- Date and Time Data types
- Binary Data types
- Miscellaneous Data types
NOTE: As we know, there are many database management systems that use SQL as their standard languages, so Not all data types are supported for all.
e.g., Oracle doesn’t support DATETIME & My SQL doesn’t support CLOB data type.
We will mainly discuss the commonly used datatypes of “SQL server” here…
Numeric Data types-
bit – Integer either 0 or 1, storage space is of 1 bit only.
tinyint – Allows whole numbers from 0 to 255, storage space is of 1 byte(8 bits).
smallint – Allows Integers from -32,768 to 32,767 & storage space is of 2 bytes.
int – Allows Integers from -2,147,483,648 to 2,147,483,647 & storage space is of 4 bytes.
bigint – Allows Integers from -9,223,372,036, 854,775,808 to 9,223,372,036, 854,775,807 & storage space is of 8 bytes.
decimal(p,s) – Allow numbers from -1038 +1 to 1038 -1 & storage of 5-17 bytes.
Here, p represents ‘precision’ i.e., total length of characters/digits in the number(before & after decimal) & s represents ‘scale’ i.e., length of characters/digits after decimal only.
numeric(p,s) – Allow numbers from -1038 +1 to 1038 -1 & storage of 5-17 bytes.
Float(n) – Floating precision number from -1.79E+308 to 1.79E+308
Here, n specifies its size & precision.
For n=1-24, its precision is 7 digits and size is 4 bytes, & For n=25-53, its precision is 15 digits and size is 8 bytes.
Real – Floating precision number from -3.40E+38 to 3.40E+38 & storage of 4 bytes.
Money – Monetary data from -922,337,203,685,477.5808 to +922,337,203,685,477.5807 with storage space of 8 bytes
smallmoney – Monetary data from -214,748.3648 to 214,748.3647 with storage space of 4 bytes
Non-Unicode Character String Data types-
- Used for local languages, like English.
Char(n) – Fixed width with maximum length of 8,000 characters(can contain letters, numbers, and special characters) with defined storage.
Varchar(n) – Variable width with maximum length of 8,000 characters(can contain letters, numbers, and special characters) having storage of 2 bytes + number of characters.
Varchar(max) – Variable width with maximum length of 1,073,741,824 characters(can contain letters, numbers, and special characters) having storage of 2 bytes + number of characters
Not suppoted in MY SQL.
Text – Variable width with maximum length of 2 GB of text data having storage of 4 bytes + number of characters
Unicode Character String Data types- (Not supported in MYSQL)
- Used for multiple international languages like Hindi, English, etc.
nchar(n) – Fixed width with maximum length of 4,000 characters(can contain letters, numbers, and special characters) in Unicode string
nvarchar(n) – Variable width with maximum length of 4,000 characters(can contain letters, numbers, and special characters) in Unicode string
nvarchar(max) – Variable width with maximum length of 536,870,912 characters(can contain letters, numbers, and special characters) in unicode string
ntext – Variable width with maximum length of 1 GB of text data in Unicode string
Date & Time Data types-
datetime – From 1st January, 1753 to 31st December, 9999 having accuracy of 3.33 milliseconds with storage space of 8 bytes
datetime2 – From 1st January, 0001 to 31st December, 9999 having accuracy of 100 nanoseconds with storage space of 6-8 bytes
smalldatetime – From 1st January, 1900 to 6th June, 2079 having accuracy of 1 minute with storage space of 4 bytes
date – Date only, from 1st January, 0001 to 31st December 9999 with storage space of 3 bytes
time – Time only, having accuracy of 100 nanoseconds with storage space of 3-5 bytes
Binary Data types-
- Data which cannot easily be stored using character or numeric data types, such as Graphic images, pdf files.
binary – Fixed length binary data having maximum length of 8,000 bytes.
varbinary – Variable length binary data having maximum length of 8,000 bytes.
Varbinary(max) – Variable length binary data having maximum length of 2 GB. It can store image/word/pdf files.
image – Variable length binary data having maximum length of 2 GB. It stores only image files(BMP,TIFF,GIF,JPEG).
Miscellaneous Data types-
sql_variant – Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier – Stores a globally unique identifier GUID, a 16 byte binary SQL Server data type that is globally unique across tables, databases, and servers
xml – stores XML formatted data with maximum storage space of 2 GB
cursor – Reference to a cursor object used for database operations table – stores a result set for later processing
We are done with the basic introduction of SQL, and datatypes in different database management systems.
If you liked this article, or It helped you to understand it better, Please do like it and share your views in the comment section.
Stay tuned with me for such topics & keep learning by Subscribing to my Website.