Author: Ian

How sys.dm_exec_describe_first_result_set_for_object Works in SQL Server

Posted on by Ian

In SQL Server, the sys.dm_exec_describe_first_result_set_for_object dynamic management function returns the metadata of the first result set for a given module.

It takes an @object_id as a parameter and describes the first result metadata for the module with that ID.

It uses the same algorithm as the sp_describe_first_result_set system stored procedure and the sys.dm_exec_describe_first_result_set function, and does pretty much the same thing, except that it’s limited to just stored procedures and triggers.

If you pass the ID of a different object type (such as a view, function, table, etc) then it will return an error.

Continue reading →

How sys.dm_exec_describe_first_result_set Works in SQL Server

Posted on by Ian

In SQL Server, the sys.dm_exec_describe_first_result_set dynamic management function returns the metadata of the first result set for a given T-SQL statement or statements.

This function uses the same algorithm as the sp_describe_first_result_set system stored procedure, and does pretty much the same thing.

It accepts three parameters, the first of which is the T-SQL statement/s you’re analysing.

Continue reading →

SQL Server Error 111: “…must be the first statement in a query batch”

Posted on by Ian

In SQL Server, a batch is a group of one or more T-SQL statements sent at the same time from an application to SQL Server for execution.

If you encounter an error like this:

Msg 111, Level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch.

It’s probably because you’re combining the statement with other statements in the same batch, which is not allowed in batches.

The first part of the error message will depend on the actual statement that you’re using in your batch. In my case it’s CREATE VIEW, but it could just as easily be CREATE PROCEDURE, CREATE FUNCTION, etc if those are the statements you’re using.

Continue reading →

Compress a Specific Partition within a Table in SQL Server (T-SQL)

Posted on by Ian

When you compress a partitioned table in SQL Server, you can compress all partitions, some, or just one partition.

To do this, use the REBUILD PARTITION syntax within the ALTER TABLE statement.

When doing this, you can rebuild just the specified partition or all partitions. Alternatively, you can rebuild all partitions, while compressing only a specific partition or list of partitions.

Continue reading →

Create a Table with Compression in SQL Server (T-SQL)

Posted on by Ian

When you create a table in SQL Server, you have the option of using data compression.

Data compression helps reduce the size of the database. It can also help improve performance of I/O intensive workloads due to the data being stored in fewer pages, therefore reducing the number of pages that queries need to read from disk.

To do this, use the DATA_COMPRESSION option when creating the table.

Continue reading →

Estimate Data Compression Savings in SQL Server

Posted on by Ian

SQL Server has a system stored procedure called sp_estimate_data_compression_savings, which allows you to check an object’s size and its estimated size with various levels of compression.

If the object is already compressed, you can use this procedure to estimate its size when recompressed.

Objects can be compressed by using row, page, columnstore or columnstore archive compression.

Compression can be evaluated for whole tables or parts of tables. This includes heaps, clustered indexes, nonclustered indexes, columnstore indexes, indexed views, and table and index partitions.

Continue reading →