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 readingTag: mssql
3 Ways to Get the Schema of a Result Set in SQL Server
Sometimes 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 readingSQL Server SOME Operator Explained
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.
SQL Server ANY Operator Explained
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.
How sys.dm_exec_describe_first_result_set_for_object Works in SQL Server
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 readingHow sys.dm_exec_describe_first_result_set Works in SQL Server
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 readingHow sp_describe_first_result_set Works in SQL Server
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.
Continue readingSQL Server Error 111: “…must be the first statement in a query batch”
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.
Compress a Specific Partition within a Table in SQL Server (T-SQL)
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 readingCreate a Table with Compression in SQL Server (T-SQL)
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.