Why SQL Server DataTypes are important to use?
This is an example of a signup page of a website.
There are three input fields, they are-
- First Name
- Last Name
- Contact number
Here, First and Last Names will always be alphabetic.
Contact will always be numeric.
From the above image, you can determine that “First and Last Name” are used as the character and “Contact Number” as an integer.
As you know, a name cannot be a number and a contact number cannot be a character, it is important to define “First and Last Name” as a character and “Contact Number” as an integer. It needs to be noted that every field in every application contains at least one sort of data. such as date, alphabetic, numeric, and many others.
Also, take note that different datatypes demand different amounts of memory. For effective memory usage, it makes more appropriate to specify the column or variable with the data type it would contain.
SQL Server Data Types
The three basic kinds of data types supported by Microsoft SQL Server are-
- String Data Types in SQL Server
- Numeric Data Types in SQL Server
- Date and Time Data Types in SQL Server
String Data Types in SQL Server
Names, addresses, descriptions, and any value containing both letters and numbers, including binary data, such as image or audio files, are typically stored in string data types.
| Data type |
Description |
| char(n) |
A fixed-length character string is stored. The maximum character count is 8,000. |
| varchar(n) |
Stores a character string of variable length. The maximum character count is 8,000. |
| varchar(max) |
Stores a character string of variable length. In this case, max denotes a maximum storage size of 2 GB. |
| text |
Stores a character string of variable length. 2 GB is the maximum storage size. |
| nchar |
A fixed-length Unicode string is stored. The maximum character count is 4,000. |
| nvarchar |
A variable-length Unicode string is stored. The maximum character count is 4,000. |
| nvarchar(max) |
A variable-length Unicode string is stored. In this case, max denotes a maximum storage size of 2 GB. |
| ntext |
A variable-length Unicode string is stored. 2 GB is the maximum storage size. |
| binary(n) |
Stores binary data of a fixed length. A maximum of 8,000 bytes may be stored. |
| varbinary(n) |
Stores binary data with variable length. A maximum of 8,000 bytes may be stored. |
| varbinary(max) |
Stores binary data with variable length. In this case, max denotes a maximum storage size of 2 GB. |
| image |
Stores binary data with variable length. The largest storage size is 8,000 bytes. |
Get 100% Hike!
Master Most in Demand Skills Now!
Numeric Data Types in SQL Server
Consider a numeric data type when you need to execute mathematical operations on SQL data. You can store numbers that you know will be utilized in a mathematical statement by using numeric data types.
To store data like price, salary, etc numeric data types are used.
| Data type |
Description |
| bit |
Enables the storage of a value of 1, 0, or NULL. |
| tinyint |
Contains integer data with a value range of 0 to 255. |
| smallint |
Contains integer values between -32,768 and 32,767. |
| int |
The storage of integer numbers between -2,147,483,648 and 2,147,483,647. |
| bigint |
Contains integer data between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. |
| decimal(p,s) |
Numbers with set precision and scale are stored. The range of acceptable values is 1038+1 to 1038-1. |
| numeric(p,s) |
Decimal is functionally equivalent to the numeric datatype. |
| smallmoney |
Gives you the ability to precisely store monetary or currency amounts between -214,748.3648 and 214,748.3647. |
| money |
Enables you to accurately store money or currency values between -922,337,203,685,477.5808 and 922,337,203,685,477.5807. |
| float(n) |
Keeps floating-point numerical values. The acceptable ranges are 0 and 2.23E-308 to 1.79E+308, respectively. |
| real |
Stores floating-point numerical values. The range of acceptable values is 3.40E + 38 to -1.18E – 38, 0, and 1.18E – 38 to 3.40E + 38. |
Date and Time Data Types in SQL Server
When storing dates and exact times, date or time-based data types are utilized. These data types were created with the purpose of storing a date or date and time.
Date
Select the Date data type if you need to record dates without a specific time associated with them. The format of the date type is YYYY-MM-DD, which holds the year, month, and day.
Datetime
Use the DateTime data type if you must hold both a date and an hour. In addition to storing dates in the same format as dates (YYYY-MM-DD), the Datetime data type also includes times in the format HH:MM:SS:MS, down to the millisecond (MS).
| Data type |
Description |
| date |
Stores a date value between January 1 (0001-01-01) and December 31 (9999-12-31). (December 31, 9999). |
| time |
A day’s worth of time is kept with a precision of 100 nanoseconds. The range of possible values is 00:00:00.0000000 to 23:59:59.9999999. |
| datetime |
Stores a time and date value that are 3.33 milliseconds accurate. Datetime is only valid for dates between 1753-01-01 (January 1, 1743) and 9999-12-31. (December 31, 9999). |
| datetime2 |
The datetime2 data type is an expansion of the datetime data type with a wider date range. Datetime2’s valid date range is from 0001-01-01 (January 1, 1) to 9999-12-31. (December 31, 9999). |
| smalldatetime |
Stores a time and date value that is accurate to the minute. Smalldatetime accepts dates between 1900-01-01 (January 1, 1900) and 2079-06-06. (June 6, 2079). |
| datetimeoffset |
Similar to datetime2, except with the time zone offset added. The standard format is hh:mm:ss[.nnnnnn] [+|-hh:mm]. The time zone offset has a suitable range of -14:00 to +14:00. |
| timestamp |
The row version data type in SQL Server, which creates distinct binary integers automatically within a database, is referred to as a timestamp. Typically, table rows are version-stamped using the row version. |