Datatypes - SQL Server Tutorials

Datatypes in SQL Server determine the type of data that can be stored in a column or variable. In this tutorial, we will discuss the different data types in SQL Server.

Syntax

The syntax for declaring a data type in SQL Server is as follows:

column_name datatype

Here, column_name is the name of the column you want to add and datatype is the type of data that the column should hold.

Example

Here's an example of creating a table with different data types:

CREATE TABLE Employee (
    Id INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Age INT,
    Salary MONEY
);

In this example, we created a table Employee with columns Id, FirstName, LastName, Age, and Salary. The Id column is of type INT and is the primary key. The FirstName and LastName columns are of type VARCHAR(50) and cannot be null. The Age column is of type INT and Salary column is of type MONEY.

Explanation

SQL Server supports several data types, including:

• CHAR and VARCHAR: Used for storing strings. CHAR has a fixed length, while VARCHAR has a variable length.
• INT, BIGINT, and TINYINT: Used for storing integer values. INT is the most common type and stores values from -2,147,483,648 to 2,147,483,647. BIGINT is used for larger values and TINYINT is used for smaller values.
• DECIMAL and NUMERIC: Used for storing decimal values. DECIMAL and NUMERIC are equivalent and store values with a fixed number of decimal places.
• FLOAT and REAL: Used for storing floating point numbers. FLOAT stores values with a precision of 15 digits, while REAL stores values with a precision of 7 digits.
• DATE, TIME, DATETIME, and DATETIME2: Used for storing date and time values. DATE stores only date values, TIME stores only time values, DATETIME stores both date and time values (with a precision of 3.33 milliseconds), and DATETIME2 stores both date and time values (with a higher precision).

Use

Choosing the correct data type is important for performance and accuracy. For example, using a CHAR column for storing a variable length string can waste disk space, while using a VARCHAR column for storing large amounts of text can cause performance issues.

You should also choose the data type based on the acceptable range of values. For example, if a column can only contain values between 1 and 100, then using an INT data type would be unnecessary and wasteful.

Important Points

• SQL Server supports several data types for storing different types of values.
• Choosing the right data type is important for performance and accuracy.
• Incorrect use of data types can cause performance issues and waste disk space.

Summary

In this tutorial, we discussed the different data types available in SQL Server. We covered the syntax, example, explanation, use, and important points of data types. It is important to choose the correct data type to ensure performance and accuracy in your SQL Server database.