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 readingIn 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.
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 readingWhen 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.
You can use the ALTER TABLE statement to enable compression on an existing table in SQL Server.
To do this, you need to use the REBUILD WITH option, while specifying your desired compression type.
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