This article is a list of commands and techniques for working with SQL Server, which are routine for advanced T-SQL developers. If you are interested in SQL basics, I recommend the SQL tutorials overview. STRING_SPLIT with the “ordinal” parameter for parsing text The newer version of the STRING_SPLIT function, available from SQL Server 2022, extends… Read More »
A temporary table with a hash (#) or double hash (##) is a temporary table stored in the system database tempdb. This table exists only for the duration of the user session ID that created it. There are two types of temporary tables: Local Temporary Table A local temporary table is accessible only within the… Read More »
What about upgrading GROUP BY clause by use of useful commands? GROUP BY is used in a clause with aggregate operations. When it is used, aggregation happens throughout all columns. Using this simple “grouping” does not enable us to do totals and subtotals. But there is plenty of operators which can easily query for totals.… Read More »
Some time ago, I published an article on the RAISERROR command, which raises an exception and allows us to influence whether the script will or will not continue based on severity. The THROW command is an alternative command introduced with SQL Server 2012. THROW Syntax in SQL Server The syntax is simpler compared to RAISERROR.… Read More »
In certain situations, it’s necessary to call an error within an SQL script and interrupt its execution. Typical scenarios include failing a data quality check or the need to invoke an error within a BEGIN /END TRY BEGIN/END CATCH construct. In such cases, you can use the RAISERROR command or the newer THROW command. In… Read More »
SQL TRY CATCH command is designed to control code flow in case that error situation occurs (error handling). This construction consists of blocks. It is also possible to use transaction (more in article on Transactions). If an error occurs in the first block – TRY..END like that is activated code in CATCH..END block. TRY CATCH… Read More »
Operator UNION ALL in SQL makes it possible to unify 2 query results and does not remove duplicities in queries. Operator returns records of both queries with no regard for existing duplicities. UNION ALL belongs to group called SET operators together with UNION, EXCEPT, INTERSECT SQL UNION ALL Syntax SELECT Column FROM dbo.Table WHERE Condition UNION… Read More »
T-SQL command IF is used to control flow of code mainly in operations where we somehow influence db (DDL, DML operations) – for example procedures. You can mix it up with CASE command quite simply. The common mistake is that we try to push sql IF into SELECT clause. Query in which IF is used… Read More »
SQL CTE, or Common Table Expression, is essentially a temporary result set represented in the form of an expression. Once declared using the WITH clause, it can be referenced in SELECT, INSERT, DELETE, or UPDATE scripts. SQL CTE (common table expression) Syntax WITH Alias_Query AS ( SELECT Column1, Column2 FROM dbo.Table ) SELECT * FROM Alias_Query; We… Read More »
OVER clause can belong to commands working with so-called Window functions in SQL. These types of functions enable us to look at the data not only in their aggregated form (using GROUP BY), but we can also look at functional operations via multiple other attributes. We, in fact, create windows of some sort and we… Read More »