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.
If you get error message 7707 in SQL Server, it’s because you’re trying to create a partition scheme that doesn’t specify enough filegroups to match the partition function.
Fortunately, this is easy to fix.
Continue readingSometimes when you run a query in SQL Server, you might want to know what the underlying data type of each column is, its precision, length, whether or not its nullable, etc.
If you’re querying just one table, you can get this sort of data with procedures such as sp_columns. But if your query runs across many tables, this could get unwieldy very quickly.
Fortunately there are several ways you can go about getting such metadata for a result set in SQL Server.
Continue readingIn 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 reading