In SQL Server, you can use the SET STATISTICS IO statement to generate detailed information about the amount of disk activity generated by a T-SQL statement.
In graphical tools like SSMS and Azure Data Studio, you can view this information in the Messages tab.
Continue readingThe SQL Server BETWEEN operator allows you to test between a range of values in your queries. For example, you could test that a value is between two numbers.
The BETWEEN operator is inclusive, meaning that it includes the values that you specify within the range. That is, it includes values that are greater than or equal to the lower value, and values that are less than or equal to the higher value. Therefore it’s like using >= and <=.
In SQL Server, you can use SET ROWCOUNT to limit the rows returned by a query.
The way it works is that it causes SQL Server to stop processing the query after the specified number of rows are returned.
It’s similar to the TOP() clause, but with the difference that SET ROWCOUNT is set outside of the query, and will affect all subsequent queries.
In SQL Server, you can use the SOME logical operator to compare a scalar value with a single-column set of values returned by a subquery.
It can be used with subqueries that have a result set of one column.
The SOME operator is the equivalent of the ANY logical operator.
In SQL Server, you can use the ANY logical operator to compare a scalar value with a single-column set of values returned by a subquery.
It can be used with subqueries that have a result set of one column.
The ANY operator is the equivalent of the SOME logical operator.
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 readingIn 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 readingIn SQL Server, the sp_describe_first_result_set system stored procedure returns the metadata for a result set.
More specifically, it returns the metadata for the first possible result set of a T-SQL batch.
It accepts three parameters, the first of which is the T-SQL statement/s you’re analysing.
Continue readingSQL 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 readingIn SQL Server, you can use the SET STATISTICS XML statement to generate detailed information about how a T-SQL statement was executed.
This information is provided in the form of a well formed XML document.
Once SET STATISTICS XML is set to ON, each subsequent statement is reflected in the output by a single XML document.
Each XML document contains the text of the statement, followed by the details of the execution steps.
The output shows run-time information such as the costs, accessed indexes, and types of operations performed, join order, the number of times a physical operation is performed, the number of rows each physical operator produced, and more.
Continue reading