Memory Crypt
Exploring basics of Microsoft SQL Server

Photo by Rubaitul Azad on Unsplash

Exploring basics of Microsoft SQL Server

Hemant Singh's photo
Hemant Singh
·

8 min read

Table of contents

Below are the basic concepts in SQL Server:

1. Stored Procedure

A stored procedure is a group of SQL statements that can be executed together as a single unit.

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT FirstName, LastName, Department
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

2. Function

A function is a reusable piece of code that returns a value.

CREATE FUNCTION CalculateTotalSalary
    (@EmployeeID INT)
RETURNS MONEY
AS
BEGIN
    DECLARE @TotalSalary MONEY;
    SELECT @TotalSalary = SUM(Salary)
    FROM SalaryDetails
    WHERE EmployeeID = @EmployeeID;
    RETURN @TotalSalary;
END;

Once the function is created, you can use it in your queries like this:

-- Use the function to calculate the total price for a product
DECLARE @UnitPrice MONEY = 10.50;
DECLARE @Quantity INT = 5;

SELECT ProductName, @UnitPrice AS UnitPrice, @Quantity AS Quantity,
       dbo.CalculateTotalPrice(@UnitPrice, @Quantity) AS TotalPrice
FROM Products
WHERE ProductID = 1;

3. Trigger

A trigger is a piece of code that automatically executes when a specific event (like an INSERT, UPDATE, DELETE) occurs on a table.

CREATE TRIGGER AuditEmployeeChanges
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO EmployeeAuditLog (EmployeeID, Action, ChangeDate)
    SELECT EmployeeID, 'UPDATE', GETDATE()
    FROM inserted;
END;

4. Common Table Expression (CTE)

A CTE is a temporary result set within a query that can be referenced within the same query.

WITH DepartmentCTE AS (
    SELECT DepartmentID, DepartmentName
    FROM Departments
    WHERE Location = 'New York'
)
SELECT E.EmployeeID, E.FirstName, E.LastName, D.DepartmentName
FROM Employees E
JOIN DepartmentCTE D ON E.DepartmentID = D.DepartmentID;

5. TRY...CATCH

The TRY...CATCH block is used to handle errors in a controlled manner.

BEGIN TRY
    -- Attempt an operation that might cause an error
    INSERT INTO NonExistentTable (ID, Name) VALUES (1, 'Example');
END TRY
BEGIN CATCH
    -- Handle the error
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;

You can use functions like ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_STATE(), etc., to access error information.

  1. ERROR_MESSAGE():

    • Returns the error message text for the error that occurred.

    • Provides a detailed description of the error.

    • Example:

        PRINT 'Error Message: ' + ERROR_MESSAGE();

  2. ERROR_NUMBER():

    • Returns the error number or code associated with the error.

    • Can help you identify the specific error type.

    • Example:

        PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS NVARCHAR(10));

  3. ERROR_SEVERITY():

    • Returns the severity level of the error.

    • The severity indicates the type of error and its impact.

    • Example:

        PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(10));

  4. ERROR_STATE():

    • Returns the state number of the error.

    • The state can provide additional information about the error context.

    • Example:

        PRINT 'Error State: ' + CAST(ERROR_STATE() AS NVARCHAR(10));

  5. ERROR_PROCEDURE():

    • Returns the name of the stored procedure or trigger where the error occurred (if applicable).

    • Useful for identifying the location of the error in your code.

    • Example:

        PRINT 'Error Procedure: ' + ERROR_PROCEDURE();

  6. ERROR_LINE():

    • Returns the line number in the batch or procedure where the error occurred.

    • Helps pinpoint the exact location of the error in the code.

    • Example:

        PRINT 'Error Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10));

  7. ERROR_RAISEERROR():

    • This function is used to re-throw the original error caught in the CATCH block.

    • You can use this to propagate the error to an outer level if needed.

    • Example:

        BEGIN CATCH
      -- Handle the error
      PRINT 'Error Message: ' + ERROR_MESSAGE();
      -- Re-throw the error
      THROW;
  END CATCH;

6. Index

indexes are used to improve the efficiency of data retrieval operations by providing faster access to the underlying data. There are two main types of indexes: clustered indexes and non-clustered indexes. Let's explore each type with an example and an explanation of when to use them.

1. Clustered Index:

A clustered index determines the physical order of data rows in a table. Each table can have only one clustered index because it defines the actual storage order of the data. Typically, the primary key of a table is used as the basis for the clustered index.

Example: Suppose you have a table named Customers with the following columns:

  • CustomerID (Primary Key)

  • FirstName

  • LastName

  • Email

You decide to create a clustered index on the CustomerID column:

CREATE CLUSTERED INDEX IX_CustomerID ON Customers (CustomerID);

In this case, the data rows in the Customers table will be physically ordered based on the values in the CustomerID column.

When to Use a Clustered Index:

  • Choose a clustered index when you want to physically order the data rows for fast retrieval of ranges of data.

  • Typically, the primary key is a good candidate for a clustered index because it's unique and often used in JOINs and WHERE clauses.

  • Be mindful that frequent updates to the clustered index can result in performance overhead due to page splits.

2. Non-Clustered Index:

A non-clustered index is a separate data structure that contains a copy of the indexed columns along with a reference to the corresponding rows in the table. Each table can have multiple non-clustered indexes.

Example: Continuing with the Customers table, you decide to create a non-clustered index on the LastName column:

CREATE NONCLUSTERED INDEX IX_LastName ON Customers (LastName);

In this case, the non-clustered index will contain a copy of the LastName column along with pointers to the actual data rows in the table.

When to Use a Non-Clustered Index:

  • Choose a non-clustered index when you want to improve the performance of specific queries that involve filtering, sorting, or joining.

  • Non-clustered indexes are useful for columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses.

  • Be aware that non-clustered indexes consume additional storage space and can have a slight overhead on data modification operations.

Deciding Between Clustered and Non-Clustered Index:

  • When choosing between a clustered and non-clustered index, consider the nature of your queries, the distribution of data, and the usage patterns of your application.

  • Clustered indexes work well for range-based queries and primary key access.

  • Non-clustered indexes are suited for specific queries that involve filtering, sorting, and joining.

Deciding which columns should have an index

Suppose you have two tables: Orders and Customers. You want to retrieve a list of customers who made orders in the last month, grouped by their city, and ordered by the total order amount.

SELECT
    C.City,
    SUM(O.TotalAmount) AS TotalOrderAmount
FROM
    Customers AS C
JOIN
    Orders AS O ON C.CustomerID = O.CustomerID
WHERE
    O.OrderDate >= DATEADD(MONTH, -1, GETDATE())
GROUP BY
    C.City
ORDER BY
    TotalOrderAmount DESC;

Here's how we can consider indexes for each part of the query:

  1. WHERE Clause (Filtering): The WHERE clause filters rows based on the OrderDate. An index on the OrderDate column in the Orders table can speed up the filtering process.

     CREATE NONCLUSTERED INDEX IX_OrderDate ON Orders (OrderDate);

  2. JOIN Clause (Joining): The JOIN involves joining the Customers and Orders tables using the CustomerID column. Indexing the CustomerID column in both tables can improve the performance of the join operation.

     CREATE NONCLUSTERED INDEX IX_CustomerID_Customers ON Customers (CustomerID);
 CREATE NONCLUSTERED INDEX IX_CustomerID_Orders ON Orders (CustomerID);

  3. GROUP BY Clause (Grouping): Since we're grouping by the City column, indexing the City column in the Customers table can help with grouping.

     CREATE NONCLUSTERED INDEX IX_City ON Customers (City);

  4. ORDER BY Clause (Sorting): The ORDER BY clause is used to sort the results by the calculated TotalOrderAmount. In this case, indexing won't directly impact the sorting operation, but the indexing decisions made for other parts of the query can indirectly affect sorting performance.

7. Transactions

Transactions in SQL Server are used to group a sequence of database operations into a single unit of work. Transactions ensure that a set of related operations are either completed successfully or rolled back entirely if an error occurs, maintaining data integrity and consistency. Transactions follow the ACID properties: Atomicity, Consistency, Isolation, and Durability.

Let's go through the concepts of transactions with examples:

Example 1: Basic Transaction Suppose we have a table named BankAccounts with columns AccountNumber and Balance, and we want to transfer money from one account to another within a transaction.

BEGIN TRANSACTION; -- Start the transaction
BEGIN TRY
    UPDATE BankAccounts
    SET Balance = Balance - 100
    WHERE AccountNumber = 'A123'; -- Deduct $100 from Account A

    UPDATE BankAccounts
    SET Balance = Balance + 100
    WHERE AccountNumber = 'B456'; -- Add $100 to Account B

    COMMIT; -- Commit the transaction if successful
END TRY
BEGIN CATCH
    ROLLBACK; -- Rollback the transaction if an error occurs
    PRINT 'Transaction failed: ' + ERROR_MESSAGE();
END CATCH;

In this example:

  • We start a transaction using BEGIN TRANSACTION.

  • We perform two UPDATE operations within the transaction to transfer $100 from Account A to Account B.

  • If both updates succeed, we commit the transaction using COMMIT.

  • If an error occurs (e.g., insufficient funds), we roll back the transaction using ROLLBACK.

Example 2: Nested Transactions SQL Server supports nested transactions, but only the outermost transaction needs to be explicitly committed or rolled back. Inner transactions are treated as savepoints within the outer transaction.

BEGIN TRANSACTION;
BEGIN TRY
    -- Outer transaction
    UPDATE BankAccounts SET Balance = Balance - 200 WHERE AccountNumber = 'C789';

    BEGIN TRANSACTION; -- Nested transaction
    UPDATE BankAccounts SET Balance = Balance + 200 WHERE AccountNumber = 'D012';

    -- Inner transaction can be committed or rolled back
    -- COMMIT; -- Uncomment to commit inner transaction
    -- ROLLBACK; -- Uncomment to rollback inner transaction

    COMMIT; -- Commit outer transaction
END TRY
BEGIN CATCH
    ROLLBACK; -- Rollback outer transaction if an error occurs
    PRINT 'Transaction failed: ' + ERROR_MESSAGE();
END CATCH;

In this example:

  • We start an outer transaction and perform an UPDATE operation.

  • We then start a nested transaction and perform another UPDATE operation.

  • Depending on whether the inner transaction is committed or rolled back, the outer transaction will be committed or rolled back accordingly.

Transactions ensure data consistency and help maintain the integrity of the database even when errors occur during complex operations. It's important to use transactions wisely and consider their impact on concurrency and performance.

SQL Server