Hidden Gems for Developers in SQL Server

Aman Raiyyani
Hidden Gems for Developers in SQL Server

SQL Server has many features and functionalities that may not be well-known or widely used by many. Here are a few hidden gems in SQL Server that you may find useful:

The OUTPUT clause: This clause allows you to return the values of the inserted, deleted, or updated rows when you perform an INSERT, UPDATE, or DELETE statement. It can be useful when you need to track the changes made to a table.

— Using the OUTPUT clause

UPDATE orders

SET total_amount = total_amount + 100

OUTPUT deleted.order_id, deleted.total_amount, inserted.total_amount

WHERE order_id = 123;

The APPLY operator: This operator allows you to join a table-valued function to a table. It can be useful when you need to retrieve data from a function in the same way as you would from a table.

— Using the APPLY operator

SELECT orders.*, customer_info.*

FROM orders

CROSS APPLY dbo.fn_get_customer_info(orders.customer_id) AS customer_info

The EXCEPT and INTERSECT operators: These operators allow you to perform set-based comparisons between two tables, returning only the present rows in one table but not in the other.

— Using the INTERSECT operator


FROM orders



FROM orders_archive;

The TRY_CONVERT function: This function allows you to convert an expression to a specified data type and returns null if the conversion fails. It can be useful when you need to handle data type conversion errors in a more elegant way.

— Using the TRY_CONVERT function

SELECT TRY_CONVERT(INT, ‘123’) AS ‘Integer Value’,

       TRY_CONVERT(INT, ‘abc’) AS ‘Integer Value’

The FOR XML clause: This clause allows you to retrieve data in XML format. It can be useful when you need to work with XML data or when you need to integrate with other systems that use XML.

— Using the FOR XML clause


FROM orders


The PIVOT and UNPIVOT operators: These operators allow you to rotate rows into columns or columns into rows, respectively. They can be useful when you need to change the structure of your data to make it more readable or to perform aggregate calculations.

— Using the PIVOT operator


FROM orders

PIVOT (SUM(total_amount) FOR order_month IN ([Jan],[Feb],[Mar]))

The RANK, DENSE_RANK, and ROW_NUMBER functions: These functions allow you to assign a unique number to each row within a result set based on one or more columns. They can be useful when you need to assign a unique identifier to each row or to perform ranking calculations.

— Using the RANK function

SELECT customer_id, order_date, RANK() OVER (PARTITION BY customer_id ORDER BY order_date) AS ‘Customer Rank’

FROM orders

The T-SQL Debugger: This is an integrated debugging tool that allows you to step through your T-SQL code line by line, set breakpoints, and view variable values. It can be useful when you need to troubleshoot and optimize your T-SQL code.

The MERGE statement: The MERGE statement allows you to perform multiple operations (INSERT, UPDATE, DELETE) on a target table based on the results of a join with a source table. It can be useful when you need to synchronize data between two tables or when you need to perform complex data manipulation tasks in a single statement.

— Using the MERGE statement

MERGE INTO orders AS target

USING updated_orders AS source

ON (target.order_id = source.order_id)


    UPDATE SET target.total_amount = source.total_amount


    INSERT (order_id, customer_id, total_amount)

    VALUES (source.order_id, source.customer_id, source.total_amount);

The HIERARCHYID data type: This data type allows you to represent hierarchical data, such as tree structures, in a table. It provides a set of built-in functions to navigate and query hierarchical data, and it can be useful when you need to store and work with hierarchical data in your database.

— Using the HIERARCHYID data type

CREATE TABLE employee (

    employee_id INT PRIMARY KEY,

    name NVARCHAR(50),

    hierarchy HIERARCHYID


The FileTable feature: This feature allows you to store and manage files and documents in a special kind of table, called a FileTable. It provides a file system-like interface to the data, and it can be useful when you need to store and manage unstructured data in your database.

— Using the FileTable feature

CREATE TABLE dbo.myFileTable


WITH (FILESTREAM_ON [filestream_filegroup_name]);

These are some of the lesser-known features in SQL Server, but they can be powerful tools when used correctly, They can help you to improve your database development, performance and administration tasks.

Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…