One of the most crucial components of any database system is security. As developers, we frequently overlook the fact that a single unsafe query or open permission can reveal private company information in favor of performance. With examples, explanations, and a straightforward flowchart, let's examine SQL Server Security Best Practices that all developers should adhere to in this post.

The Significance of Security
Critical data, such as user passwords, financial transactions, personal information, etc., is stored by modern applications.
If your SQL Server isn't set up securely, hackers can take advantage of weaknesses like:

• Injection of SQL
• Elevation of Privilege
• Information Leakage
• Unauthorized Entry

Security Layers in SQL Server
Before jumping into best practices, understand that SQL Server security has multiple layers :

• Authentication: Who can access the server
• Authorization: What actions they can perform
• Encryption: How data is protected in transit and at rest
• Auditing: Tracking who did what and when

Best Practices for Developers
Let’s break down the most essential security best practices step by step.

1. Use Parameterized Queries (Prevent SQL Injection)
Never concatenate user input directly in your SQL statements.

Vulnerable Example
string query = "SELECT * FROM Users WHERE Username = '" + userInput + "'";

Safe Example
SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE Username = @Username", conn);
cmd.Parameters.AddWithValue("@Username", userInput);

Why: Parameterized queries ensure that input is treated as data, not executable SQL, preventing SQL injection attacks.

2. Follow the Principle of Least Privilege
Grant only the permissions required — nothing more.

Don’t

• Use sa or system admin accounts for applications.
• Give db_owner role to every user. 

Do

• Create application-specific users with limited access.
• Assign roles like db_datareader or db_datawriter as needed.

3. Secure Connection Strings
Never store connection strings in plain text inside your source code.
Use Configuration Files or Secrets Manager:

• .NET Core: Store in appsettings.json and protect with User Secrets or Azure Key Vault.
• Windows: Use DPAPI or Encrypted Configuration Sections.

Example
"ConnectionStrings": {
  "DefaultConnection": "Server=myServer;Database=myDB;User Id=appUser;Password=***;"
}

4. Encrypt Sensitive Data
Use SQL Server encryption features to protect confidential data.

Transparent Data Encryption (TDE)
Encrypts the database files (.mdf, .ldf) at rest.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
ALTER DATABASE MyDB SET ENCRYPTION ON;

Column-Level Encryption
Encrypt specific columns like passwords or credit card numbers.

CREATE COLUMN MASTER KEY MyKey
WITH ALGORITHM = RSA_2048;

5. Avoid Hardcoding Credentials
Never hardcode usernames, passwords, or keys in your code.
Always use:

• Environment variables
• Secure configuration management
• Secret stores (e.g., Azure Key Vault, AWS Secrets Manager)

6. Enable Row-Level Security (RLS)
Row-Level Security restricts data visibility based on user or role.

Example
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE dbo.fnSecurityPredicate(UserID)
ON dbo.Sales WITH (STATE = ON);

This ensures each user can only see data they are authorized to view.

7. Implement Data Masking
Use Dynamic Data Masking to hide sensitive information from unauthorized users.
ALTER TABLE Customers
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

Result
Admin sees full email: [email protected]
Analyst sees masked: [email protected]

8. Regularly Patch and Update SQL Server
Always apply the latest SQL Server Service Packs and Cumulative Updates .
Outdated versions often contain known vulnerabilities that hackers exploit.

9. Use Secure Network Connections (SSL/TLS)
Enable encryption for data in transit.
Force Encryption = Yes

In the connection string:
Encrypt=True;TrustServerCertificate=False;

10. Audit and Monitor Database Activity
Enable SQL Server Audit to track actions such as login attempts, schema changes, or data access.
CREATE SERVER AUDIT Audit_LoginTracking
TO FILE (FILEPATH = 'C:\AuditLogs\')
WITH (ON_FAILURE = CONTINUE);

Then:
ALTER SERVER AUDIT Audit_LoginTracking WITH (STATE = ON);

You can later review logs to identify suspicious activities.

Flowchart: SQL Server Security Flow

Here’s a simple visualization of how SQL Server enforces security at multiple layers:

            ┌─────────────────────────┐
            │   User / Application    │
            └──────────┬──────────────┘
                       │
                       ▼
         ┌────────────────────────┐
         │ Authentication Layer   │
         │ (Login / Password / AD)│
         └─────────────┬──────────┘
                       │
                       ▼
         ┌────────────────────────┐
         │ Authorization Layer    │
         │ (Roles, Permissions)   │
         └─────────────┬──────────┘
                       │
                       ▼
         ┌─────────────────────────┐
         │ Row-Level / Data Access │
         │ (RLS, Masking, Filters) │
         └─────────────┬───────────┘
                       │
                       ▼
         ┌─────────────────────────┐
         │ Encryption Layer        │
         │ (TDE, SSL, Column Key)  │
         └─────────────┬───────────┘
                       │
                       ▼
         ┌─────────────────────────┐
         │ Auditing & Monitoring   │
         │ (Logs, Alerts, Reports) │
         └─────────────────────────┘

This layered approach ensures defense at every step of the data access process.

Final Checklist for Developers

Conclusion
Database security must be incorporated into the application from the start by developers, not only DBAs.

Together, authentication, authorization, encryption, and auditing make up a safe SQL Server configuration.
Recall:
"Security problems can ruin your company, but performance problems can harm your app."

Thus, make SQL Server security a habit rather than a checklist by adhering to these procedures constantly.

HostForLIFEASP.NET SQL Server 2022 Hosting

SQL Server continues to be one of the most potent relational database systems in today's data-driven world. However, the ability to write sophisticated SQL queries frequently distinguishes novices from pros. Business reports, analytics dashboards, and performance-driven apps are built on complex queries.

Joins, subqueries, window functions, CTEs, pivoting, and other real-world complex SQL Server queries will all be covered in this article with clear examples.

1. Using Subqueries for Conditional Data Retrieval
Scenario:
You need to find all employees whose salary is higher than the average salary of their department.
Query:
SELECT
    EmpName,
    DepartmentId,
    Salary
FROM Employees E
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentId = E.DepartmentId
);

Explanation:

• The inner query calculates the average salary per department.
• The outer query compares each employee’s salary to that departmental average.

This is a correlated subquery, as it depends on the outer query.

2. Combining Multiple Tables with JOINS
Scenario:
Retrieve all orders along with customer names and product details.
Query:
SELECT
    C.CustomerName,
    O.OrderId,
    P.ProductName,
    O.OrderDate,
    OD.Quantity,
    (OD.Quantity * OD.UnitPrice) AS TotalAmount
FROM Orders O
INNER JOIN Customers C ON O.CustomerId = C.CustomerId
INNER JOIN OrderDetails OD ON O.OrderId = OD.OrderId
INNER JOIN Products P ON OD.ProductId = P.ProductId
WHERE O.OrderDate >= '2025-01-01'
ORDER BY O.OrderDate DESC;

Explanation:
This query combines four tables using inner joins to give a comprehensive view of orders placed in 2025.

3. Ranking Data Using Window Functions
Scenario:
List top 3 highest-paid employees in each department.
Query:
SELECT
    DepartmentId,
    EmpName,
    Salary,
    RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS SalaryRank
FROM Employees
WHERE Salary IS NOT NULL

Then wrap it to filter top 3:
SELECT *
FROM (
    SELECT
        DepartmentId,
        EmpName,
        Salary,
        RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS SalaryRank
    FROM Employees
) AS Ranked
WHERE SalaryRank <= 3;

Explanation:
The RANK() function assigns ranking per department.
The outer query filters the top 3 salaries per department.

4. Simplifying Logic with Common Table Expressions (CTE)
Scenario:
Find employees who earn more than the average salary in their department (using CTE for clarity).
Query:
WITH DeptAverage AS (
    SELECT
        DepartmentId,
        AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentId
)
SELECT
    E.EmpName,
    E.DepartmentId,
    E.Salary,
    D.AvgSalary
FROM Employees E
JOIN DeptAverage D ON E.DepartmentId = D.DepartmentId
WHERE E.Salary > D.AvgSalary;

Explanation:
The CTE (DeptAverage) simplifies complex nested subqueries, making the query more readable and maintainable.

5. Transforming Rows to Columns with PIVOT
Scenario:
Show total sales by product across different months.
Query:
SELECT *
FROM (
    SELECT
        ProductName,
        DATENAME(MONTH, OrderDate) AS [Month],
        (Quantity * UnitPrice) AS TotalSales
    FROM OrderDetails OD
    JOIN Orders O ON OD.OrderId = O.OrderId
    JOIN Products P ON OD.ProductId = P.ProductId
) AS SourceTable
PIVOT (
    SUM(TotalSales)
    FOR [Month] IN ([January], [February], [March], [April], [May], [June])
) AS PivotTable;

Explanation:
This query pivots monthly sales data into columns, allowing easier visualization and reporting.

6. Recursive Queries with CTE
Scenario:
Retrieve a hierarchical list of employees under a specific manager.
Query:
WITH EmployeeHierarchy AS (
    SELECT
        EmpId, EmpName, ManagerId
    FROM Employees
    WHERE ManagerId IS NULL  -- Top-level managers

    UNION ALL

    SELECT
        E.EmpId, E.EmpName, E.ManagerId
    FROM Employees E
    INNER JOIN EmployeeHierarchy EH ON E.ManagerId = EH.EmpId
)
SELECT * FROM EmployeeHierarchy;

Explanation:
This recursive CTE walks through employee-manager relationships to display a full organizational hierarchy.

Performance Tip
When dealing with large datasets:

• Use indexes on join and filter columns.
• Avoid using SELECT *; only fetch required columns.
• Check execution plans using SQL Server Management Studio (SSMS).
• Use temp tables or CTEs for readability and modularization.

Conclusion
Complex SQL queries are powerful tools for solving real-world data problems — from business intelligence reports to analytics dashboards. By mastering subqueries, CTEs, window functions, and pivots, you can transform SQL Server from a data store into an insight engine.

Keep practicing on realistic datasets like AdventureWorks or Northwind — and soon, you’ll write elegant and efficient SQL like a pro!

Next Steps:

• Try rewriting one of your existing reports using a CTE.
• Use RANK() and PIVOT for analytics dashboards.
• Explore query optimization using SET STATISTICS IO, TIME ON.

SQL Authentication
SQL Authentication, also known as SQL Login, stores credentials (username and password) inside the SQL Server instance. It’s a database-managed authentication model, independent of Active Directory or Windows accounts.

Features

• Uses login name and password stored in SQL Server.
• Ideal for non-domain clients or cross-platform applications.
• Supports backward compatibility for legacy systems.

Advantages

• Works outside of domain networks.
• Simple to set up for service accounts or third-party tools.
• Enables easy app-level authentication control.

Disadvantages

• Passwords must be managed manually.
• No centralized policy enforcement (e.g., MFA).
• Slightly higher attack surface if passwords are weak.

Best Practices

• Use strong password policies and encrypt connections (TLS).
• Restrict login privileges.
• Periodically rotate credentials and audit logins.

Windows Authentication
Windows Authentication (also known as Integrated Security or Trusted Connection) uses the Windows or Active Directory identity of the user to authenticate.
It’s the most secure and preferred option for enterprise setups.

Features

• Uses Kerberos or NTLM protocols for verification.
• Credentials are not sent directly to SQL Server.
• Enables Single Sign-On (SSO) via Active Directory.

Advantages

• Centralized identity control via AD.
• Strong password and lockout policies automatically applied.
• Easy role-based access using AD groups.

Disadvantages

• Requires domain membership or trust relationships.
• Not ideal for external or Linux-based clients.

Best Practices

• Prefer Kerberos over NTLM for better security.
• Use AD groups for permission management.
• Audit AD memberships regularly.

Other Authentication Methods (Short Notes)

Summary Table

In order to generate a data backup from the current tables (Tables 1 and 2),

SELECT INTO and INSERT INTO are two popular SQL techniques that you can employ.

Whether or not backup tables are already in place determines the option.

1. Quick Backup with Table Creation using SELECT INTO
Using this method, data from the source table is directly copied into a new table.

SELECT * INTO table1_Backup FROM table1;
SELECT * INTO table2_Backup FROM table2;

• Note: This will fail if table1_Backup or table2_Backup already exist.
• Ideal for one-time backups or quick cloning.

 2. Using INSERT INTO – Backup into Existing Tables

Use this method when the backup tables are already created (i.e., the schema is predefined).

INSERT INTO table1_Backup SELECT * FROM table1;
INSERT INTO table2_Backup SELECT * FROM table2;

• No error if backup tables already exist.
• Useful for regular/daily backups where the structure is fixed.

Summary

Data is often spread across several SQL Servers or even distinct database systems (such as Oracle, MySQL, or PostgreSQL) in enterprise settings.  SQL Server provides a robust feature called Linked Server that enables you to access and manipulate data across several servers as if they were a single database, eliminating the need for manual data exporting and import.

Using real-world examples, this article describes what a linked server is, how to set it up, and how to query remote data.

What is a Linked Server?
A Linked Server in SQL Server allows you to connect to another database server instance (on the same network or remote) and execute distributed queries (SELECT, INSERT, UPDATE, DELETE) against OLE DB data sources outside of the local SQL Server.

It enables:

• Cross-server queries
• Centralized data access
• Remote procedure execution (EXECUTE AT)
• Joining tables from different servers

Setting Up a Linked Server
You can create a Linked Server via SQL Server Management Studio (SSMS) or T-SQL script.

Method 1: Using SSMS GUI
Open SSMS → Expand Server Objects → Right-click on Linked Servers → Choose New Linked Server

In the dialog box:

• Linked server: Enter an alias name (e.g., LinkedServer_Prod)
• Server type: Choose SQL Server or Other data source
• Provider: Select Microsoft OLE DB Provider for SQL Server
• Data source: Enter remote server name or IP

Go to the Security tab and configure credentials:

• Option 1: Use the current user’s security context
• Option 2: Specify a remote login and password

Click OK to create the Linked Server.

Method 2: Using T-SQL Script
Here’s how to create a Linked Server using SQL script:
EXEC sp_addlinkedserver
    @server = 'LinkedServer_Prod',
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = '192.168.1.100'; -- Remote Server IP or Name

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'LinkedServer_Prod',
    @useself = 'false',
    @locallogin = NULL,
    @rmtuser = 'sa',
    @rmtpassword = 'StrongPassword123';

Example: Querying Data from a Linked Server
Once the Linked Server is created, you can query it using four-part naming convention:
[LinkedServerName].[DatabaseName].[SchemaName].[TableName]

Example 1: Simple SELECT query
SELECT TOP 10 *
FROM LinkedServer_Prod.SalesDB.dbo.Customers;

Example 2: Joining Local and Remote Tables
SELECT
    a.OrderID,
    a.CustomerID,
    b.CustomerName
FROM LocalDB.dbo.Orders a
INNER JOIN LinkedServer_Prod.SalesDB.dbo.Customers b
    ON a.CustomerID = b.CustomerID;

Example 3: Executing Remote Stored Procedure
EXEC LinkedServer_Prod.SalesDB.dbo.sp_GetTopCustomers @TopCount = 5;

Updating Remote Data
You can even insert or update remote tables via Linked Server.

Example 4: Insert into remote table
INSERT INTO LinkedServer_Prod.SalesDB.dbo.Customers (CustomerName, City)
VALUES ('Peter', 'London');

Example 5: Update remote data
UPDATE LinkedServer_Prod.SalesDB.dbo.Customers
SET City = 'Udaipur'
WHERE CustomerID = 101;

Best Practices

• Use SQL authentication with strong passwords for remote login.
• Enable RPC and RPC Out only if needed.
• Use OPENQUERY() for performance optimization with complex joins.
• Limit access by creating specific database roles and permissions.

Using OPENQUERY (Performance Tip)
Instead of the four-part naming method, use OPENQUERY to push the query execution to the remote server:
SELECT *
FROM OPENQUERY(LinkedServer_Prod, 'SELECT CustomerName, City FROM SalesDB.dbo.Customers WHERE City = ''London''');

This approach reduces data transfer and often performs faster.

Removing a Linked Server
When you no longer need a Linked Server, remove it safely:
EXEC sp_dropserver 'LinkedServer_Prod', 'droplogins';

Conclusion
Linked Servers in SQL Server are a powerful way to integrate and access distributed data sources without complex ETL processes. With proper configuration, they can significantly improve data collaboration and reduce maintenance efforts across multiple systems.

However, always monitor performance and secure connections to prevent unauthorized access.

In this article I describe Check Constraints on a table, how to add a Check Constraint to a table, how to remove a Check Constraint from a table, and the limitations of a Check Constraint. Before explaining Check Constraints it is necessary to first explain what Domain Integrity is.

Domain Integrity
Domain Integrity ensures that values are valid for columns and prevents invalid values for columns within a database.

Check Constraint
A Check Constraint is a rule that identifies valid values for columns of data. A Check Constraint helps to enforce Domain Integrity. If the condition in a Check Constraint is not satisfied then it prevents the value from entering into the database.

Syntax
CREATE TABLE tableName (
    Column1 dataType CHECK (expression),
    Column2 dataType,
    ColumnN dataType
);

Example
CREATE TABLE emp (
    empId INT CHECK (empId > 10),
    empName VARCHAR(15)
);

Output

If we want to insert a record with less than 10 IDs then it shows the error.
INSERT INTO emp (empId, empName)
VALUES (8, 'd');

Output

Dropping the Check Constraint
First of all, we can determine the name of the constraint using the following command.
EXEC sp_help emp;

Output

Now execute the following command.
ALTER TABLE emp
DROP CONSTRAINT CK__emp__empId__1A14E395;

Output

Adding the Check Constraint
ALTER TABLE emp
ADD CHECK (empId > 15);

Output

Limitation
The Check Constraint rejects the values that are invalid or we can say which does not satisfy the Check Conditions. But in the case of a null, a Check Constraint will allow it to be insert into the database.

Insertion of Null value
INSERT INTO emp
VALUES (NULL, 'd');

Output

Summary
In this article, I described a Check Constraint in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.

1. Design of Schemas
Essential Ideas

• To cut down on redundancy, normalize
• Employ restrictions and foreign keys.
• For auditing, keep track of timestamps.

Schema Diagram
Customers ───< Orders ───< OrderItems >─── Products

Example Table: Customers

Checklist

• Primary & foreign keys defined
• Appropriate data types
• Default timestamps added

2. Indexing & Query Tuning
Scenario: Calculate total spending per customer.

Query
SELECT c.FirstName, c.LastName, SUM(oi.Quantity * oi.Price) AS TotalSpent
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
WHERE o.OrderDate >= '2025-01-01'GROUP BY c.FirstName, c.LastName
ORDER BY TotalSpent DESC;

Optimization: Add covering indexes to speed up joins.

3. ETL: Efficient Data Loading

Scenario: Update daily product prices from CSV.

Process Diagram
CSV File → Staging Table → Merge → Products Table

Example SQL
BULK INSERT ProductStaging FROM 'C:\Data\ProductPrices.csv'
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

MERGE Products AS target
USING ProductStaging AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN UPDATE SET Price = source.Price
WHEN NOT MATCHED BY TARGET THEN INSERT (Name, Price, Stock) VALUES (source.Name, source.Price, source.Stock);

Checklist

• Validate incoming data
• Only update changed records
• Log ETL results

4. Backup & Recovery
Backup Strategy Diagram
Full Backup → Differential Backup → Transaction Log Backups

Example SQL
BACKUP DATABASE ECommerceDB TO DISK = 'C:\Backups\ECommerceDB_FULL.bak' WITH INIT, COMPRESSION;
BACKUP LOG ECommerceDB TO DISK = 'C:\Backups\ECommerceDB_Log.trn';

Checklist

• Full backups scheduled
• Transaction logs backed up for minimal data loss
• Test restores regularly

5. Security: Controlling Access

Role-Based Access Table

SQL Example
CREATE ROLE ReadOnlyRole;
ALTER ROLE ReadOnlyRole ADD MEMBER [ReportingUser];
DENY INSERT, UPDATE, DELETE TO ReadOnlyRole;

6. Monitoring & Maintenance
Maintenance Flow Diagram
Index Rebuild → Statistics Update → Query Performance Monitoring → Alerts

Example SQL for Long-Running Queries
SELECT TOP 10
    qs.total_elapsed_time/1000 AS TotalMs,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset/2,
        (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY TotalMs DESC;

Checklist

• Monitor slow queries & deadlocks
• Rebuild fragmented indexes
• Update statistics regularly
• Set alerts for critical issues

The query optimizer, one of SQL Server's primary features, employs statistics to ascertain the query's most effective execution strategy. Statistics are used to calculate the most effective access path to the data and to estimate how many rows a query will return. In order to give the query optimizer the most recent data possible for precise estimations, we will go over how to update statistics in SQL Server in this post.

What are Statistics in SQL Server?
Update Statistics is a command or process in SQL Server that updates the statistical information about the distribution of data in one or more tables or indexes in a database. They consist of summary data that allows the query optimizer to make informed decisions about how to retrieve data efficiently. These statistics help SQL Server determine the most optimal query execution plans, which ultimately impacts query performance.

By default, SQL Server automatically updates statistics when a certain threshold of data changes has been reached. This threshold is known as the “auto-update” threshold. However, there are situations where you may want to update statistics manually to ensure that the query optimizer has the most accurate information to work with.

Why are Statistics Important?
Accurate statistics are critical for SQL Server to generate the most efficient execution plan for a query. If the statistics are outdated or inaccurate, the query optimizer may choose a suboptimal execution plan, resulting in slower query performance. Additionally, inaccurate statistics can lead to suboptimal index usage, which can cause excessive disk I/O and memory usage.

When to Update Statistics in SQL Server?
While SQL Server automatically updates statistics for you, there are certain scenarios where you may want to update statistics manually. These scenarios include:

• Large Data Changes: If a large percentage of data has been added, modified, or deleted from a table, you may want to update statistics manually to ensure that the query optimizer has the most accurate information.
• Stale Statistics: If the statistics for a table or indexed view are stale (out of date), you may want to update them manually to ensure that the query optimizer has the most accurate information. This can happen when data is added, modified, or deleted from a table or indexed view after the statistics were last updated.
• Performance Issues: If you are experiencing performance issues with a particular query, updating statistics may help to improve performance by providing the query optimizer with more accurate information.

How to Update Statistics in SQL Server SQL Server?
Automatic Updates - SQL Server automatically updates statistics when a threshold of data modifications occurs, or when the query optimizer determines that the statistics are out of date. This is the default behavior for SQL Server, and it works well for most databases.
Manual Updates - We can manually update statistics using the UPDATE STATISTICS statement.

Update the statistics for a single table or indexed view using the following command:
UPDATE STATISTICS [table_name]

Where [table_name] is the name of the table or indexed view for which statistics to be updated.

Update the statistics for all tables in a database using the following command:
EXEC sp_updatestats

When statistics getting updated, SQL Server automatically updates the statistics for all columns that have a density value that is out of date. This means that the update may not necessarily update all statistics for all columns in the table or indexed view.

It's important to note that updating statistics can be a resource-intensive operation, particularly for large tables. Therefore, it's important to carefully consider the frequency and timing of statistics updates to ensure they don't negatively impact system performance.
How to view the statistics property?

By using the below T-SQL command:
DBCC SHOW_STATISTICS

For more information, see DBCC SHOW_STATISTICS

We can use ‘sys.stats’ system catalog view, which provides information about statistics for all user-defined and internal tables and indexes in the current database by using following command:
SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.name AS StatisticName,
    s.auto_created AS IsAutoCreated,
    s.user_created AS IsUserCreated,
    STATS_DATE(s.object_id, s.stats_id) AS LastUpdated
FROM sys.stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
  AND s.stats_id = i.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY TableName, IndexName

This statement retrieves information about statistics for all user-defined tables and indexes in the database, including the table and index names, statistic name, whether the statistic was automatically or manually created, and the date and time the statistics were last updated.

Examine the “LastUpdated” column to determine whether the statistics are up-to-date. If the value in this column is relatively recent, the statistics are up-to-date. However, if the value is old or NULL, the statistics may be out-of-date and need to be updated.

Conclusion
Statistics are an important component of SQL Server's query optimization process. By providing the query optimizer with accurate information about the distribution of data in a table or indexed view, statistics can help to improve query performance. While SQL Server automatically updates statistics for you, there are certain scenarios where you may want to update them manually to ensure that the query optimizer has the most accurate information. By understanding when and how to update statistics in SQL Server, you can help to ensure that your queries are running as efficiently as possible.

HostForLIFEASP.NET SQL Server 2022 Hosting

Relational databases are accessed using SQL (Structured Query Language). It facilitates data storage, retrieval, updating, and deletion. Almost every business uses SQL, including banking systems and internet. The ability to use SQL is essential if you wish to work with data. For the most crucial SQL topics, this cheat sheet will provide you with concise and understandable definitions, code examples, and essential points.

1. SELECT Statement
Definition: Used to fetch data from a table.

Example
SELECT name, age FROM Students;

Key Point: Use * to select all columns.

2. WHERE Clause
Definition: Filters records based on a condition.

Example
SELECT * FROM Students WHERE age > 18;

Key Point: Works with operators like =, >, <, BETWEEN, IN, LIKE.

3. ORDER BY
Definition: Sorts the result in ascending or descending order.

Example
SELECT * FROM Students ORDER BY age DESC;

Key Point: Default sort is ascending (ASC).

4. INSERT INTO
Definition: Adds new records to a table.

Example
INSERT INTO Students (name, age) VALUES ('John', 20);

Key Point: Match the column order with the values.

5. UPDATE
Definition: Changes existing records.

Example
UPDATE Students SET age = 21 WHERE name = 'John';

Key Point: Always use WHERE to avoid updating all rows.

6. DELETE
Definition: Removes data from a table.

Example
DELETE FROM Students WHERE name = 'John';

Key Point: Without WHERE, all rows will be deleted.

7. CREATE TABLE
Definition: Creates a new table.
Example
CREATE TABLE Students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

Key Point: Define data types clearly.

8. ALTER TABLE
Definition: Changes the structure of a table.
Examples
    Add column:
    ALTER TABLE Students ADD email VARCHAR(100);

Drop column:
ALTER TABLE Students DROP COLUMN email;

9. DROP TABLE
Definition: Deletes the table and its data.

Example
DROP TABLE Students;

Key Point: This action cannot be undone.

10. JOINs
Definition: Combines rows from two or more tables.

Example (INNER JOIN)
SELECT Students.name, Marks.score
FROM Students
INNER JOIN Marks ON Students.id = Marks.student_id;

Types
    INNER JOIN: Only matching records.
    LEFT JOIN: All records from left + matches.
    RIGHT JOIN: All from right + matches.
    FULL JOIN: All records from both.

11. GROUP BY
Definition: Groups rows based on a column and applies an aggregate function.

Example:
SELECT age, COUNT(*) FROM Students GROUP BY age;

Key Point: Always use with aggregate functions.

12. HAVING
Definition: Filters groups created by GROUP BY.

Example:
SELECT age, COUNT(*)
FROM Students
GROUP BY age
HAVING COUNT(*) > 1;

Key Point: Use HAVING after GROUP BY.

13. Aggregate Functions
Definition: Perform calculations on multiple values.
Common ones: COUNT(), SUM(), AVG(), MIN(), MAX()

Example
SELECT AVG(age) FROM Students;

14. DISTINCT
Definition: Removes duplicate rows.

Example
SELECT DISTINCT age FROM Students;

15. BETWEEN, IN, LIKE
BETWEEN

SELECT * FROM Students WHERE age BETWEEN 18 AND 25;

IN
SELECT * FROM Students WHERE age IN (18, 21, 23);

LIKE
SELECT * FROM Students WHERE name LIKE 'A%';

16. LIMIT / TOP
Definition: Restricts number of rows returned.
SELECT * FROM Students LIMIT 5;

SELECT TOP 5 * FROM Students;

17. UNION
Definition: Combines results of two SELECT statements.

Example
SELECT name FROM Students
UNION
SELECT name FROM Teachers;

Key Point: Both queries must have the same number of columns.

18. Subquery
Definition: A query inside another query.

Example
SELECT name FROM Students
WHERE age = (SELECT MAX(age) FROM Students);

19. Views
Definition: A virtual table based on the result of a query.

Example
CREATE VIEW Teenagers AS
SELECT * FROM Students WHERE age BETWEEN 13 AND 19;

20. Indexes
Definition: Improve search performance on columns.

Example
CREATE INDEX idx_name ON Students(name);

Key Point: Indexes speed up SELECT but may slow down INSERT/UPDATE.

21. Constraints
Definition: Rules applied on columns.

Types
    PRIMARY KEY: Unique and not null.
    FOREIGN KEY: Links to another table’s primary key.
    UNIQUE: Ensures all values are different.
    NOT NULL: Prevents null values.
    CHECK: Validates a condition.

Example
CREATE TABLE Students (
  id INT PRIMARY KEY,
  age INT CHECK (age >= 0)
);

22. DEFAULT Constraint
Definition: Sets a default value for a column if none is provided.

Example
CREATE TABLE Students (
  id INT,
  name VARCHAR(50),
  status VARCHAR(10) DEFAULT 'active'
);

Key Point: Helps avoid NULL when no value is provided.

23. CASE Statement
Definition: Adds conditional logic inside a query.

Example
SELECT name,
       CASE
         WHEN age >= 18 THEN 'Adult'
         ELSE 'Minor'
       END AS age_group
FROM Students;

Key Point: Works like if-else in SQL.

24. EXISTS
Definition: Checks whether a subquery returns any row.

Example
SELECT name FROM Students s
WHERE EXISTS (
  SELECT 1 FROM Marks m WHERE m.student_id = s.id
);

Key Point: Faster than IN in many cases for large datasets.

25. NOT IN vs NOT EXISTS
Caution: NOT IN fails if any NULL exists in subquery. Use NOT EXISTS for safer logic.

Example
SELECT name FROM Students
WHERE NOT EXISTS (
  SELECT 1 FROM Graduates WHERE Students.id = Graduates.id
);

26. COALESCE and ISNULL
Definition: Replace NULL with a default value.

Example
SELECT name, COALESCE(email, 'Not Provided') FROM Students;

Key Point: COALESCE returns the first non-null value from a list.

27. Window Functions
Definition: Performs calculation across a set of rows related to the current row.
Common ones: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

Example
SELECT name, age,
       RANK() OVER (ORDER BY age DESC) AS age_rank
FROM Students;

Key Point: Unlike aggregate functions, window functions do not collapse rows.

28. Common Table Expressions (CTE)
Definition: A temporary named result set used in a query.

Example
WITH Teenagers AS (
  SELECT * FROM Students WHERE age BETWEEN 13 AND 19
)
SELECT * FROM Teenagers;

Key Point: Makes complex queries cleaner and readable.

29. Stored Procedures
Definition: A saved group of SQL statements that can be executed repeatedly.

Example
CREATE PROCEDURE GetAllStudents
AS
BEGIN
  SELECT * FROM Students;
END;

Execution
EXEC GetAllStudents;

30. Functions (User-Defined Functions)
Definition: Return a value based on input parameters.

Example
CREATE FUNCTION GetFullName (@first VARCHAR(50), @last VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
  RETURN (@first + ' ' + @last);
END;

31. Transactions
Definition: A set of SQL operations that execute together or not at all.

Example
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Key Point: Use ROLLBACK to undo changes if something fails.

32. ACID Properties
Definition: Ensures reliable processing of transactions.

• Atomicity: All or nothing
• Consistency: Valid state before/after
• Isolation: Transactions do not affect each other
• Durability: Changes are permanent

33. Normalization

• Definition: Organizing data to reduce redundancy.
• Common Forms: 1NF, 2NF, 3NF
• Key Point: Normalize to avoid data anomalies. Denormalize only when performance requires it.

34. Foreign Key
Definition: A column that links to another table’s primary key.

Example
CREATE TABLE Marks (
  id INT PRIMARY KEY,
  student_id INT,
  FOREIGN KEY (student_id) REFERENCES Students(id)
);

35. Triggers
Definition: SQL code that runs automatically on data change (INSERT, UPDATE, DELETE).

Example
CREATE TRIGGER LogDelete
AFTER DELETE ON Students
FOR EACH ROW
BEGIN
  INSERT INTO DeletedRecords (name) VALUES (OLD.name);
END;

36. SET Operators (UNION ALL, INTERSECT, EXCEPT)
UNION ALL: Includes duplicates.
INTERSECT: Returns common rows between two queries.
EXCEPT: Returns rows in first query but not in second.

Example
SELECT name FROM Students
INTERSECT
SELECT name FROM Teachers;

37. Backup and Restore
Backup Example (SQL Server)
BACKUP DATABASE School TO DISK = 'D:\backup\school.bak';

Restore Example
RESTORE DATABASE School FROM DISK = 'D:\backup\school.bak';

38. TEMPORARY Tables
Definition: Temporary tables exist only during the session or transaction.

Example (MySQL)
CREATE TEMPORARY TABLE TempStudents (
  id INT,
  name VARCHAR(50)
);

Key Point: Good for storing intermediate results. Automatically dropped at session end.

39. TABLE Variables (SQL Server)
Definition: A variable to temporarily hold table data.

Example
DECLARE @Temp TABLE (id INT, name VARCHAR(50));
INSERT INTO @Temp VALUES (1, 'John');

40. MERGE Statement (UPSERT)
Definition: Performs INSERT, UPDATE, or DELETE in one statement based on a match.

Example
MERGE INTO Students AS target
USING (SELECT 1 AS id, 'John' AS name) AS source
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET name = source.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (source.id, source.name);

Key Point: Use cautiously. Complex logic can lead to bugs if not tested well.

41. Recursive CTE
Definition: A CTE that refers to itself. Useful for hierarchical data (e.g., org charts, folder trees).

Example
WITH OrgChart AS (
  SELECT id, manager_id, name FROM Employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM Employees e
  JOIN OrgChart o ON e.manager_id = o.id
)
SELECT * FROM OrgChart;

42. JSON Data Handling
Definition: Querying and storing JSON in SQL columns (supported in PostgreSQL, MySQL, SQL Server).

Example (PostgreSQL)
SELECT data->>'name' AS name FROM users WHERE data->>'age' = '25';

Key Point: Useful when data structure is flexible or semi-structured.

43. PIVOT and UNPIVOT

Definition
    PIVOT: Converts rows to columns.
    UNPIVOT: Converts columns to rows.

Example (SQL Server):
SELECT * FROM
(SELECT subject, score FROM Marks) AS SourceTable
PIVOT (
  MAX(score) FOR subject IN ([Math], [Science])
) AS PivotTable;

Key Point: Makes reporting easier. Avoid overuse in core logic.

44. ROLLUP and CUBE
Definition: Extensions to GROUP BY for creating subtotals and grand totals.

ROLLUP
SELECT department, role, COUNT(*)
FROM Employees
GROUP BY ROLLUP (department, role);

CUBE
GROUP BY CUBE (department, role);

Key Point: Saves time when generating hierarchical reports.

45. WITH TIES
Definition: Returns additional rows that match the last value in an ORDER BY ... TOP query.

Example:
SELECT TOP 3 WITH TIES name, score
FROM Marks
ORDER BY score DESC;

Key Point: Useful when ranks are tied and you want to return all top scorers.

46. SEQUENCES
Definition: Auto-incrementing number generator independent of tables.

Example:
CREATE SEQUENCE student_seq START WITH 1 INCREMENT BY 1;
SELECT NEXT VALUE FOR student_seq;

Key Point: More flexible than IDENTITY in some databases.

47. IDENTITY vs SEQUENCE
IDENTITY: Tied to a table. Auto-increments per insert.
SEQUENCE: Independent object. Can be reused across tables or manually advanced.

48. LOCKING & ISOLATION LEVELS
Definition: Controls how transactions read/write shared data.

Levels

• READ UNCOMMITTED: Dirty reads allowed
• READ COMMITTED: Only committed data
• REPEATABLE READ: Prevents non-repeatable reads
• SERIALIZABLE: Strictest, full isolation

Key Point: Choose based on consistency vs performance trade-off.

49. DEADLOCK

• Definition: Two or more sessions wait forever for resources locked by each other.
• Fix: Reduce transaction size, always lock objects in the same order, use proper isolation levels.

50. EXECUTION PLAN

• Definition: Visual or textual explanation of how SQL Server/MySQL/PostgreSQL will execute the query.
• Key Point: Use EXPLAIN, SHOW PLAN, or right-click → “Display Estimated Execution Plan” in SQL Server Management Studio.

51. ANALYZE / UPDATE STATISTICS
Definition: Refreshes metadata so the query planner makes better decisions.

Example (PostgreSQL):
ANALYZE Students;

SQL Server:
UPDATE STATISTICS Students;

52. PARTITIONING

• Definition: Divides large tables into smaller, more manageable parts (partitions).
• Key Point: Boosts performance on very large tables. Query optimizer uses partition elimination.

53. Sharding

• Definition: Horizontal partitioning across databases or servers.
• Key Point: Needed for very high scale. Not supported by default in many RDBMS — requires custom implementation or external tools.

54. Temporal Tables (System-Versioned Tables)
Definition: Track historical changes automatically.

Example (SQL Server 2016+):
CREATE TABLE StudentsHistory (
  id INT,
  name VARCHAR(50),
  VALID_FROM DATETIME2 GENERATED ALWAYS AS ROW START,
  VALID_TO DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (VALID_FROM, VALID_TO)
) WITH (SYSTEM_VERSIONING = ON);

HostForLIFEASP.NET SQL Server 2022 Hosting

The Query Store: What is it?
With SQL Server 2016, a performance monitoring tool called the Query Store was added. It records query execution history, query plans, and performance metrics over time, much like a black box recorder. Because Query Store retains historical data, it is simpler to troubleshoot performance issues that have already occurred, in contrast to standard DMVs that only provide live statistics.

Why Use Query Store?

• Monitor how query performance changes over time.
• Detect and troubleshoot query regressions (when performance drops).
• Compare performance before and after deployments.
• Identify and force optimal plans for queries.
• Track resource usage (CPU, IO, duration) per query.

How to Enable Query Store?
Option 1. T-SQL
ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON;

Option 2. SSMS GUI

• Right-click the database → Properties
• Navigate to the Query Store page
• Set Operation Mode (Requested) to Read Write
• Adjust other settings like capture mode and retention as needed

What Data does Query Store Capture?

Internal Views for Query Store Analysis

• sys.query_store_query: Stores each normalized query
• sys.query_store_plan: Stores query execution plans
• sys.query_store_runtime_stats: Stores execution metrics
• sys.query_store_wait_stats: Captures wait types per query (SQL 2017+)

Built-in SSMS Reports
SSMS provides graphical reports for easier analysis.

• Right-click the database
• Select Reports → Standard Reports → Query Store
• Explore reports like.
  • Top Resource Consuming Queries
  • Tracked Queries
  • Query Plan Changes
  • Regressed Queries

Plan Forcing Example
If SQL Server chooses a suboptimal plan, you can force a better one.

Steps to Force a Plan via T-SQL
EXEC sp_query_store_force_plan
    @query_id = 102,
    @plan_id = 301;

To Unforce a Plan
EXEC sp_query_store_unforce_plan
    @query_id = 102,
    @plan_id = 301;

Note: Forced plans remain in use until manually unforced or if the plan becomes invalid due to schema changes.

How to Clean Query Store?
Remove Specific Plan or Query
EXEC sp_query_store_remove_plan @plan_id = 123;
EXEC sp_query_store_remove_query @query_id = 456;

Clear All Data from Query Store
ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE CLEAR;

Query Store Modes

Limitations and Considerations

• Slight overhead on heavily loaded systems (~1–5%)
• Disk space usage can grow—monitor data size regularly
• Not available in SQL Server 2014 or earlier
• Retention settings control how long historical data is kept

Conclusion
The Query Store is a powerful feature for any SQL Server environment focused on performance, stability, and transparency. It simplifies identifying performance problems and helps maintain consistent performance by allowing query plan control. Use it proactively in development, staging, and production environments to avoid surprises and ensure optimal performance.

HostForLIFEASP.NET SQL Server 2022 Hosting