Unknown Concepts in MS SQL Server with Code Snippets

3 min readDec 24, 2023

Microsoft SQL Server, a robust and feature-rich relational database management system, offers a plethora of functionalities that might be unknown or overlooked by many.

In this blog post, we’ll delve into some lesser-known concepts in MS SQL Server, providing code snippets and explanations to demystify these aspects.

Whether you’re a fresher or an experienced developer, expanding your knowledge of these hidden gems can significantly enhance your SQL Server proficiency.

Embark on a journey of continuous learning and exploration with DotNet-FullStack-Dev. https://dotnet-fullstack-dev.blogspot.com/

1. Table-Valued Parameters (TVPs)

Concept: Table-Valued Parameters (TVPs) allow you to pass a table as a parameter to a stored procedure or a function. This can be especially handy when dealing with multiple rows of data.

-- Create a user-defined table type
CREATE TYPE dbo.EmployeeTableType AS TABLE  
(  
    EmployeeID INT,  
    EmployeeName NVARCHAR(100)
);

-- Use TVP in a stored procedure
CREATE PROCEDURE InsertEmployees
    @Employees dbo.EmployeeTableType READONLY
AS  
BEGIN  
    INSERT INTO Employees (EmployeeID, EmployeeName)
    SELECT * FROM @Employees;
END;

2. Window Functions

Concept: Window functions perform a calculation across a set of rows that are related to the current row. These functions are applied to a “window” of rows related to the current row based on the specified OVER clause.

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgSalaryByDept
FROM
    Employees;

3. Common Table Expressions (CTEs)

Concept: CTEs provide a way to create a temporary result set that can be referred to within the context of a SELECT, INSERT, UPDATE, or DELETE statement.

WITH EmployeeCTE AS (
    SELECT
        EmployeeID,
        EmployeeName,
        Salary,
        ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
    FROM
        Employees
)
SELECT
    EmployeeID,
    EmployeeName,
    Salary
FROM
    EmployeeCTE
WHERE
    RowNum <= 10;

4. Filtered Indexes

Concept: Filtered Indexes are a type of index that includes only a subset of rows in the table based on a filter predicate. This can be useful for improving query performance on specific subsets of data.

-- Create a filtered index
CREATE NONCLUSTERED INDEX IX_SalaryHigh
ON Employees (EmployeeID)
WHERE Salary > 50000;

5. SEQUENCE Objects

Concept: SEQUENCE objects provide a way to generate a sequence of numeric values according to a defined specification. They are especially useful when you need to generate unique identifiers.

-- Create a sequence
CREATE SEQUENCE EmployeeSeq
    START WITH 1
    INCREMENT BY 1;

-- Use the sequence in a table
ALTER TABLE Employees
ADD EmployeeSeqID INT DEFAULT NEXT VALUE FOR EmployeeSeq;

6. JSON Support

Concept: SQL Server has native support for JSON, allowing you to store and query JSON data. You can create JSON objects, parse JSON strings, and query JSON data using JSON functions.

-- Create a JSON object
DECLARE @json NVARCHAR(MAX) = '{"name": "John", "age": 30, "city": "New York"}';

-- Extract values from JSON
SELECT
    JSON_VALUE(@json, '$.name') AS Name,
    JSON_VALUE(@json, '$.age') AS Age,
    JSON_VALUE(@json, '$.city') AS City;

7. Temporal Tables

Concept: Temporal Tables provide a way to track changes to data over time. They automatically maintain historical versions of a table’s rows.

-- Create a temporal table
CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    Salary INT,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

Conclusion

Exploring these lesser-known concepts in MS SQL Server can open new possibilities for optimizing queries, improving performance, and handling data in more sophisticated ways.

As you continue your journey with SQL Server, keep expanding your knowledge and experimenting with these features to become a more proficient database developer.

Embark on a journey of continuous learning and exploration with DotNet-FullStack-Dev. Uncover more by visiting our https://dotnet-fullstack-dev.blogspot.com reach out for further information.

Happy Querying!