SQL Database Objects - MSDN Documentation

Understanding SQL Database Objects

Database objects are the fundamental building blocks of any relational database. They define the structure, organization, and behavior of data within a SQL Server instance. Understanding these objects is crucial for effective database design, development, and administration.

This documentation provides an overview of the most common SQL Server database objects, their purpose, and how they are used.

Tables

Tables are the primary structures for storing data in a relational database. They are organized into rows (records) and columns (attributes).

Key Concepts:

Columns: Define the data types and characteristics of the data stored in each attribute.
Rows: Represent individual records or instances of an entity.
Primary Key: A column or set of columns that uniquely identifies each row in a table.
Foreign Key: A column or set of columns in one table that refers to the primary key in another table, establishing relationships.

Example: Creating a simple table

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Department VARCHAR(100),
    HireDate DATE
);
                

Views

Views are virtual tables based on the result-set of a stored SQL query. They do not store data themselves but present data from one or more underlying tables in a specific way.

Benefits:

Simplify complex queries.
Enhance security by restricting access to specific columns or rows.
Provide a consistent data interface.

Example: Creating a view

CREATE VIEW EmployeeContactInfo AS
SELECT
    EmployeeID,
    FirstName,
    LastName,
    Department
FROM
    Employees
WHERE Department = 'IT';
                

Stored Procedures

Stored procedures are a set of Transact-SQL statements that are compiled and stored on the database server. They can accept input parameters and return output parameters.

Advantages:

Improved performance through pre-compilation.
Enhanced security and data integrity.
Reduced network traffic.
Modular code development.

Example: Creating a stored procedure

CREATE PROCEDURE AddNewEmployee (
    @EmployeeID INT,
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @Department VARCHAR(100) = NULL,
    @HireDate DATE
)
AS
BEGIN
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, HireDate)
    VALUES (@EmployeeID, @FirstName, @LastName, @Department, @HireDate);
END;
                

Functions

Functions are T-SQL routines that perform a specific operation and return a single value. They can be used in SQL statements like expressions.

Types:

Scalar Functions: Return a single data value.
Table-Valued Functions: Return a table (either inline or multi-statement).

Example: Creating a scalar function

CREATE FUNCTION GetEmployeeFullName (@EmployeeID INT)
RETURNS VARCHAR(101)
AS
BEGIN
    DECLARE @FullName VARCHAR(101);
    SELECT @FullName = FirstName + ' ' + LastName
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
    RETURN @FullName;
END;
                

Triggers

Triggers are special stored procedures that automatically execute in response to certain events (DML or DDL statements) on a table or view.

Use Cases:

Enforcing complex business rules.
Auditing data changes.
Maintaining data integrity across related tables.

Example: Creating a trigger

CREATE TRIGGER TR_Employee_Audit
ON Employees
AFTER UPDATE
AS
BEGIN
    IF UPDATE(Department)
    BEGIN
        INSERT INTO AuditLog (TableName, Operation, RecordID, ChangeDetails)
        SELECT 'Employees', 'UPDATE', i.EmployeeID, 'Department changed'
        FROM inserted i
        WHERE i.Department <> (SELECT Department FROM deleted d WHERE d.EmployeeID = i.EmployeeID);
    END
END;
                

Schemas

Schemas are logical containers for database objects. They help in organizing and managing database objects, and can also be used to control permissions.

Benefits:

Namespace management.
Security and access control.
Application isolation.

Example: Creating a schema

CREATE SCHEMA Sales;
GO

ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Sales_Employee
FOREIGN KEY (EmployeeID) REFERENCES Sales.Employees(EmployeeID);
                

Constraints

Constraints enforce rules on the data in tables, ensuring data integrity and accuracy.

Common Constraint Types:

PRIMARY KEY: Uniquely identifies each record.
FOREIGN KEY: Ensures referential integrity between tables.
UNIQUE: Ensures all values in a column are different.
CHECK: Ensures that all values in a column satisfy a specific condition.
DEFAULT: Inserts a default value if no value is specified.
NOT NULL: Ensures that a column cannot have a NULL value.

Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval operations. They are similar to an index in the back of a book.

Types:

Clustered Index: Determines the physical order of data in the table. A table can have only one clustered index.
Nonclustered Index: A separate structure that contains index key values and pointers to the actual data rows. A table can have multiple nonclustered indexes.

Example: Creating an index

CREATE INDEX IX_Employees_LastName
ON Employees (LastName);
                

Synonyms

Synonyms are alternative names for database objects (tables, views, stored procedures, etc.). They can be used to provide a layer of abstraction, allowing you to rename objects without modifying dependent code.

Example: Creating a synonym

CREATE SYNONYM dbo.EmployeeData
FOR Production.Employees;
                

Sequences

Sequence objects generate a sequence of numeric values according to the specification supplied at the time of creation. They are often used for generating unique primary key values.

Example: Creating a sequence

CREATE SEQUENCE dbo.Seq_EmployeeID
    START WITH 1000
    INCREMENT BY 1;
                