In SQL Server, you can use the following query to determine the partitioning column for a partitioned table.
Continue readingCategory: DBMS
Database Management Systems
How to Fix “ALTER TABLE SWITCH statement failed”
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.
Continue readingCheck the Space Used by a Table in SQL Server
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.
Continue readingSwitch-In a Partition in SQL Server (T-SQL)
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).
Switch-Out a Partition in SQL Server (T-SQL)
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).
SQL Server ROWCOUNT_BIG()
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.
SQL Server SHOWPLAN_ALL
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.
SQL Server SHOWPLAN_TEXT
In SQL Server, you can use the SET SHOWPLAN_TEXT statement to return detailed information about how a T-SQL statement is executed.
Database Mail Tutorials
I’ve written quite a number of tutorials for using Database Mail, and I’ve created this article to list them all in one place.
I first included these links at the bottom of How to Send Emails from SQL Server (T-SQL), but I thought it might be helpful to move them to their own article as a quick-reference.
Continue readingWhat Do I Need to Run SQL?
I get it. You’re trying to learn SQL, but they won’t even tell you the most fundamental part – what you need to run SQL!
You’ve got the SQL code… but where do you actually run it? What software program do you need before you can run your SQL query against a database?
If that’s you, all is about to be revealed!
Continue reading