A data type is an attribute that specifies the type of data that these objects can store. It can be an integer, character string, monetary, date and time, and so on.
BIT data type is an integer data type that can take a value of 0, 1, or NULL.If you insert a string value of True into the bit column, SQL server converts it to bit 1
Similarly, SQL Server converts a string value of false to bit 0:
SQL Server converts any nonzero value to bit 1
INT Data Type:
SQL Server support standard SQL integer types including BIGINT, INT, SMALLINT, and TINYINT. The following table illustrates the range and storage of each integer type:
| Data type | Range | Storage |
|---|---|---|
| BIGINT | -263 (-9,223,372,036,854,775,808) to 263-1 (9,223,372,036,854,775,807) | 8 Bytes |
| INT | -231 (-2,147,483,648) to 231-1 (2,147,483,647) | 4 Bytes |
| SMALLINT | -215 (-32,768) to 215-1 (32,767) | 2 Bytes |
| TINYINT | 0 to 255 | 1 Byte |
Decimal Data Type: To store numbers that have fixed precision and scale, you use the DECIMAL data type.
p is the precision which is the maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point.
s is the scale which is the number of decimal digits that will be stored to the right of the decimal point.
The NUMERIC and DECIMAL are synonyms, therefore, you can use them interchangeably.
char: In this syntax, n specifies the string length which ranges from 1 to 8,000.
When you insert a string value into a CHAR column. If the length of the string value is less than the length specified in the column, SQL Server will add trailing spaces to the string value to the length declared in the column. However, when you select this string value, SQL Server removes the trailing spaces before returning it.
In SQL Server, LEN function returns the number of characters in a specified column that excludes the trailing blanks and the DATALENGTH function returns the number of bytes.
To store fixed-length, Unicode character string data in the database, you use the SQL Server NCHAR data type:
n specifies the string length that ranges from 1 to 4,000. The storage size of a NCHAR value is two times n bytes.
SQL Server VARCHAR data type is used to store variable-length, non-Unicode string data. The
In this syntax, n defines the string length that ranges from 1 to 8,000. If you don’t specify n, its default value is 1.
varchar(max)
In this syntax, max defines the maximum storage size which is 231-1 bytes (2 GB).
In general, the storage size of a VARCHAR value is the actual length of the data stored plus 2 bytes.
SQL Server NVARCHAR data type is used to store variable-length, Unicode string data.
In general, the actual storage size in bytes of a NVARCHAR value is two times the number of characters entered plus 2 bytes.
Date Time :To store both date and time in the database, you use the SQL Server DATETIME2 data type.
Date:To store the date data in the database, you use the SQL Server DATE data type.
Time: The SQL Server TIME data type defines a time of a day based on 24-hour clock. The syntax of the TIME data type is as follows:
A GUID is guaranteed to be unique across tables, databases, and even servers.
In SQL Server, GUID is 16-byte binary data type, which is generated by using the NEWID() function:
Using GUID as the primary key of a table brings the following advantages:
- GUID values are globally unique across tables, databases, and even servers. Therefore, it allows you to merge data from different servers with ease.
- GUID values do not expose the information so they are safer to use in public interface such as a URL. For example, if you have the URL
https://www.example.com/customer/100/URL, it is not so difficult to find that there will have customers with id 101, 102, and so on. However, with GUID, it is not possible:https://www.example.com/customer/F4AB02B7-9D55-483D-9081-CC4E3851E851/
Besides these advantages, storing GUID in the primary key column of a table has the following disadvantages:
- GUID values (16 bytes) takes more storage than
INT(4 bytes) or evenBIGINT(8 bytes) - GUID values make it difficult to troubleshoot and debug, comparing
WHERE id = 100withWHERE id = 'F4AB02B7-9D55-483D-9081-CC4E3851E851'.