SQL Server Reference

This section provides a comprehensive reference for Transact-SQL (T-SQL) commands, system functions, and objects used with Microsoft SQL Server.

Transact-SQL (T-SQL) Commands

Data Definition Language (DDL)

Commands used to define and modify database structures.

• CREATE: Creates database objects (e.g., tables, views, stored procedures).
• ALTER: Modifies existing database objects.
• DROP: Deletes database objects.
• TRUNCATE TABLE: Removes all rows from a table.
• RENAME: Renames a database object.

Data Manipulation Language (DML)

Commands used to manage data within database objects.

• SELECT: Retrieves data from one or more tables.
• INSERT: Adds new rows of data to a table.
• UPDATE: Modifies existing data in a table.
• DELETE: Removes rows of data from a table.
• MERGE: Performs INSERT, UPDATE, or DELETE operations on a target table based on a join condition with a source table.

Data Control Language (DCL)

Commands used to manage permissions and access to data.

• GRANT: Grants permissions to users or roles.
• REVOKE: Removes permissions from users or roles.
• DENY: Denies permissions to users or roles.

Transaction Control Language (TCL)

Commands used to manage transactions.

• BEGIN TRANSACTION: Starts a transaction.
• COMMIT TRANSACTION: Saves all changes made during a transaction.
• ROLLBACK TRANSACTION: Undoes all changes made during a transaction.
• SAVE TRANSACTION: Creates a save point within a transaction.

System Stored Procedures

Predefined stored procedures that perform administrative and diagnostic tasks.

Examples include:

• sp_help: Returns information about a database object.
• sp_configure: Configures server options.
• sp_monitor: Displays server activity information.

For a complete list and detailed descriptions, please refer to the specific documentation for SQL Server system stored procedures.

Common T-SQL Syntax Examples

Creating a Table

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

Selecting Data with a Filter

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE DepartmentID = 101;
            

Inserting Data

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate, DepartmentID)
VALUES (1001, 'John', 'Doe', '2023-01-15', 101);
            

Updating Data

UPDATE Employees
SET DepartmentID = 102
WHERE EmployeeID = 1001;
            

Deleting Data

DELETE FROM Employees
WHERE EmployeeID = 1001;
            

System Functions

SQL Server provides a rich set of built-in functions for various purposes, including string manipulation, date/time operations, aggregate calculations, and more.

Aggregate Functions

• COUNT(): Returns the number of rows.
• SUM(): Returns the sum of values.
• AVG(): Returns the average of values.
• MIN(): Returns the minimum value.
• MAX(): Returns the maximum value.

Scalar Functions

• GETDATE(): Returns the current database system date and time.
• LEN(): Returns the length of a string.
• CONCAT(): Concatenates two or more strings.
• ISNULL(): Replaces NULL with a specified replacement value.

Explore the Built-in Functions section for a comprehensive list and detailed explanations.

Database Objects

SQL Server supports various database objects that help in organizing, accessing, and managing data.

• Tables: The primary structures for storing data.
• Views: Virtual tables based on the result-set of a stored query.
• Stored Procedures: Precompiled sets of T-SQL statements.
• Functions: Reusable blocks of code that perform specific tasks and return a value.
• Indexes: Data structures that improve the speed of data retrieval operations.
• Triggers: Special types of stored procedures that execute automatically in response to certain events on a table or view.

Key System Tables

SQL Server maintains system catalog views that provide metadata about the database.

• sys.tables: Information about tables.
• sys.columns: Information about columns in tables.
• sys.procedures: Information about stored procedures.
• sys.views: Information about views.

Understanding these system objects is crucial for database administration and development.