SQL Server

Temporary Stored Procedures

14. 5. 2024

Few people know that it is possible to create temporary stored procedures in SQL Server. I often ask about this when recruiting new candidates, and 9 out of 10 ask if I meant temporary tables, or if they start talking about them right away. So yes, we can indeed create…

Read more
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…
SQL Server

Maximum Number of Columns in SELECT Statements

13. 4. 2024
SQL Server, like many relational database management systems, has specific limitations that developers need to be aware of when designing and implementing database solutions. One such limitation is the maximum number of expressions that can be specified in the SELECT list of a query. This limit is set at 4096…
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
SQL Server

Understanding Sparse Columns

9. 4. 2024
Sparse columns in SQL Server are an intriguing feature designed to optimize storage for columns that contain a significant amount of NULL values. Introduced in SQL Server 2008, sparse columns offer a way to store nulls efficiently, consuming no storage space for NULL values in a column. This feature can…
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…
DBA

Unraveling the Mystery of the Suspect Pages Table

23. 3. 2024

In the vast world of SQL Server, data integrity is paramount. One critical component in maintaining this integrity is the Suspect Pages Table, a feature many database administrators (DBAs) may encounter but not fully understand. This table, an often-overlooked aspect of SQL Server’s system databases, plays a vital role in…

Read more
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…
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…

Read more