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
SELECT *
FROM orders
INTERSECT
SELECT *
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
SELECT *
FROM orders
FOR XML AUTO, ELEMENTS;
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
SELECT *
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)
WHEN MATCHED THEN
UPDATE SET target.total_amount = source.total_amount
WHEN NOT MATCHED THEN
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
AS FILETABLE
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.
