T-SQL

Managing String Content in View Definitions

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
SQL Server

SET STATISTICS PROFILE

SQL Server provides various tools and commands to help database administrators and developers analyze and optimize query performance. One of these commands is SET STATISTICS PROFILE. This command is particularly useful for obtaining a detailed execution plan along with the runtime statistics of a query, which can be invaluable for…
T-SQL

Numbered Stored Procedures

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

Temporary Stored Procedures

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

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

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

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

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

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

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