Database Management Systems
If you’ve previously created a partitioned table in SQL Server, and you now want to know how many rows are being stored in each partition, here are three queries you can use.
In particular, you can:
sys.dm_db_partition_stats viewsys.partitions view$PARTITION function in a queryBelow are examples of all three.
SQL Server has the UPDATE() function that you can use within your DML triggers to check whether or not a specific column has been updated.
While this function only accepts one column, there’s nothing to stop you from including multiple UPDATE() clauses with AND or OR to test for multiple column updates.
In SQL Server, you can query the sys.partitions system catalog view to find out whether or not a partition has been compressed.
In particular, the data_compression column tells you whether it’s compressed or not. The data_compression_desc column tells you what type of compression it uses. If it isn’t compressed, it returns NONE.
SQL Server error 120 occurs when you don’t specify enough columns in your INSERT list when using a SELECT list for the values to insert.
To be more specific, it happens when you use a SELECT list in your INSERT statement, but the SELECT list doesn’t return as many columns as you’re specifying with the INSERT.
This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.
When you create a partitioned table in SQL Server, you specify which values go into each partition.
This is done when you create the partition function. When you create the partition function, you specify boundary values, which determine which values go into each partition.
Once you’ve created your partitioned table, and you’ve inserted data, you can run a normal SELECT statement to return data, just as you would with a non-partitioned table (actually, even non-partitioned tables have one partition).
But did you know that you can also specify which partition you want data from?
You can do this with the help of the $PARTITION system function in your WHERE clause.
In SQL Server, you can use the following query to determine the partitioning column for a partitioned table.
If you’re getting error 4939 while trying to switch a partition in SQL Server, it’s probably because you’re trying to switch to a partition that uses a different filegroup.
One of the requirements of switching partitions is that both the source table or partition, and the target table or partition, must be located in the same filegroup.
SQL Server has a system stored procedure called sp_spaceused that enables you to check the size of a table in a database.
You can use it to check the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or the disk space reserved and used by the whole database.
In SQL Server, you can switch partitions in and out of a partitioned table.
You can do this with the ALTER TABLE statement. Basically, it goes like this:
ALTER TABLE OldTable
SWITCH TO NewTable PARTITION xThis switches the partition for OldTable into partition x of NewTable (where x is the partition number).
In SQL Server, partition switching allows you to load large amounts of data in or out of a table very quickly. This saves you from having to run delete or insert statements, and can be very useful when working with large data sets.
You can use the ALTER TABLE statement to switch a partition in or out of a table.
To switch a partition out of a table, the code goes like this:
ALTER TABLE Table1
SWITCH PARTITION x TO Table2This switches partition x from Table1 to Table2 (where x is the partition number).