T-SQL

Managing String Content in View Definitions

26. 6. 2024

The hidden text (this_is_hidden_text_in_the_view) in the view definition is an intriguing addition. Anything after the SELECT * FROM sys.tables query that is not a valid SQL statement (like a comment or plain text) will not be executed or parsed by SQL Server. It essentially becomes invisible to the engine and…

Read more
T-SQL

Numbered Stored Procedures

12. 6. 2024
Numbered stored procedures are a lesser-known feature that allows multiple procedures to share the same name but are distinguished by a numeric suffix. This feature can be useful in certain scenarios, although it is not commonly used in modern database development. Numbered stored procedures allow you to create a series…
T-SQL

IDENTITY Cheat Sheet

13. 5. 2024
Today we will look at a brief overview of all important functions, global variables, and DBCC commands for working with IDENTITY values in SQL Server. Programmers often make mistakes regarding when to use each of them, particularly due to not understanding how the individual functions behave within the context in…
T-SQL

Generating Empty GUIDs

12. 4. 2024

In SQL Server, a Globally Unique Identifier (GUID) is a 16-byte binary data type that is commonly used to uniquely identify records in a database. The UNIQUEIDENTIFIER data type in SQL Server is equivalent to a GUID, and there are occasions where you might need to generate an empty GUID,…

Read more
T-SQL

Exploring the Power of the JSON_OBJECT() Function

31. 3. 2024
SQL Server 2022 enhances its JSON capabilities with the JSON_OBJECT() function, providing a streamlined approach to transforming SQL query results into JSON format directly within SQL queries. This functionality is invaluable for developers working in environments where SQL Server interacts with web services, applications, or systems that consume or produce…
T-SQL

Storing Hierarchical Data in SQL Server (Available Options)

20. 2. 2022
There are various options on how to save hierarchical data in SQL Server. This article is an overview of them demonstrating their usage on a simple dataset of employees and their managers. I will extend this article when a new option will be available or if you will advise it…
T-SQL

Converting Literal Path (node1/node2/...) to Parent-Child Table

11. 2. 2022

There isn’t built-in support for working with hierarchies in the SQL Server except for the hierarchyid data type. This means that we should solve most of the hierarchical data processing tasks using the old-school portfolio of standard T-SQL language. One of these tasks is a simple conversion of hierarchical paths…

Read more
T-SQL

Referencing Objects and the Maximum Number of Prefixes

26. 1. 2022
Objects like tables or views can be referenced using the dot (.) notation based on this pattern: [{server}].[{database}].[{schema}].[{object}], e.g. [MyServer1].[MyDatabase1].[dbo].[MyTable1]. One will expect that this sequence should go from right to left and parts can’t be skipped. But we can have much more fun with it in the reality. When…
T-SQL

Generate Array of Values in FOR JSON

23. 1. 2022
There is quite sustainable support for working with JSON format in SQL Server since the 2016 version. But it’s tricky time-to-time to do more complex things, especially building more complex JSON documents from tabular data format. One of these things is how to build an array of values, e.g. we…