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.
Continue readingIf 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.
Continue readingSQL 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.
Continue readingIn 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).
In SQL Server, you can use the ROWCOUNT_BIG() system function to return the number of rows affected by the last T-SQL statement.
It works exactly the same as @@ROWCOUNT, except that ROWCOUNT_BIG() returns its result as a bigint.
In SQL Server, you can use the SET SHOWPLAN_ALL statement to return detailed information about how a T-SQL statement is executed, as well as estimates of the resource requirements for the statements.
In SQL Server, you can use the SET SHOWPLAN_TEXT statement to return detailed information about how a T-SQL statement is executed.