In 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.
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.
In 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.
In SQL Server, you can use the SET SHOWPLAN_XML statement to return the query plan for a T-SQL statement in the form of a well-defined XML document. The query plan contains detailed information about how the statement will be executed.
It’s similar to SHOWPLAN_ALL, except that SHOWPLAN_ALL returns its data set of rows that form a hierarchical tree.
You can set SHOWPLAN_XML to either ON or OFF.
When SHOWPLAN_XML is ON, all subsequent T-SQL statements are not executed. Instead SQL Server returns execution information for the statement (without executing it).
You might be familiar with the ALL option in SQL Server. Perhaps you’ve used it along with the UNION operator to include any duplicates that might be returned in the result set.
But did you know that ALL can also be used in two other contexts?
ALL can be used in the following three contexts:
SELECT clause.UNION clause.Examples of each of these contexts follows.
In SQL Server, the ALL operator can be used with a subquery to compare a scalar value with a single-column set of values returned by the subquery.
It’s also true that the SELECT clause and UNION operator both accept an ALL argument, although this usage has a different purpose (allows duplicates in the result set).
Below are examples of using the ALL operator with a subquery.
Comparison operators are an important part of most programming languages.
Comparison operators are used to compare two expressions. The result is either true or false. It could also be unknown. This could also be represented by either 1, 0, or NULL, depending on the language. These are typically known as “Boolean expressions”.
When used with databases, comparison operators can be used inside your SQL queries to filter data to a certain criteria.
You can use the T-SQL EXCEPT operator in SQL Server to return distinct rows from the left input query that aren’t output by the right input query.
In SQL Server, you can use the T-SQL INTERSECT operator to return distinct rows that are output by both the left and right input queries.
In PostgreSQL, you can use the OVERLAPS operator to test for overlapping time periods.
The function returns true when two time periods (defined by their endpoints) overlap, and false when they do not overlap.