3.2 Tables and Data Types

 

3.2 Tables and Data Types

In SQL Server, tables are fundamental components for organizing and storing data. Each table consists of rows and columns, where columns define the type of data that can be stored. This section will guide you through creating tables and understanding various data types available in SQL Server.

3.2.1 Creating Tables

To create a table, you'll typically use the CREATE TABLE statement. Let's create a simple table for managing employee information.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    BirthDate DATE,
    Salary DECIMAL(10, 2)
);

In this example:

• EmployeeID is an integer and serves as the primary key.
• FirstName and LastName are variable character strings.
• BirthDate is a date data type.
• Salary is a decimal number with a precision of 10 digits and a scale of 2.

3.2.2 Common Data Types

Here are some commonly used data types in SQL Server:

• INT: Integer data type for whole numbers.
• NVARCHAR(n): Variable-length Unicode character string with a maximum length of n characters.
• DATE: Date without a time component.
• DECIMAL(p, s): Fixed-point decimal number with p total digits and s digits after the decimal point.
• BIT: Binary data type representing true or false values.
• VARCHAR(n): Variable-length character string with a maximum length of n characters.

3.2.3 Constraints

Constraints are rules applied to a column to enforce data integrity. In the table creation example, PRIMARY KEY is a constraint that ensures the uniqueness of the EmployeeID. Other common constraints include UNIQUE, CHECK, and FOREIGN KEY.

Example using constraints:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(50) UNIQUE,
    ManagerID INT,
    CONSTRAINT FK_Manager FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

Here, UNIQUE ensures the uniqueness of DepartmentName, and FOREIGN KEY establishes a relationship with the Employees table.

3.2.4 Adding and Modifying Columns

You can alter existing tables to add or modify columns.

-- Adding a new column
ALTER TABLE Employees
ADD Email NVARCHAR(100);
-- Modifying the data type of an existing column
ALTER TABLE Employees
ALTER COLUMN Salary DECIMAL(12, 2);

3.2.5 Dropping Tables

To remove a table and its data:

DROP TABLE Employees;

Caution: Be careful when using DROP TABLE as it permanently deletes the table and all its data.

3.2.6 Conclusion

Creating and managing tables is a foundational skill in SQL Server. Understanding data types and constraints is crucial for designing efficient and reliable databases. In the next tutorial sections, we'll explore querying data from tables and manipulating the data within them.