 | Inserting Multiple Rows before SQL Server 2008
Prior to SQL Server 2008 it is not possible to provide multiple sets of raw data to an INSERT statement to create more than one row in a table. However, it is possible to insert a query's results into a table. This can be used to add several rows at once. |
| Inserting Multiple Rows in SQL Server 2008
Transact-SQL gained some enhancements with SQL Server 2008 and later versions. One of the improvements is the ability to insert a number of rows of data with a single INSERT statement, without executing a query to generate the new information. |
| Removing SQL Server Index Fragmentation
Over time, the indexes applied to tables in a SQL Server database can become fragmented because of row insertions, updates and deletions. This fragmentation can severely impact the performance of the database so should be rectified periodically. |
| Executing a Command Against Every Table in a Database
When managing a database it is common to need to execute the same function against all of the tables. This might be to perform a series of database checks for each table or perhaps to disable all triggers and constraints whilst making changes to the data. |
| Obtaining the Last Inserted Identity in Any Session
It is common to obtain the last inserted identity value for a SQL Server table for the current connection, with @@IDENTITY, or for the current scope, with SCOPE_IDENTITY. However, it is also possible to find the latest identity inserted by any session. |
| SQL Server Transaction Isolation Levels
Permitting concurrent transactions in database management systems is essential for performance but introduces the possibility of reading inconsistent data. To balance concurrency problems and performance, SQL Server has four transaction isolation levels. |
| SQL Server Common Table Expressions
Avoiding temporary tables and cursors for queries can lead to scripts that are easier to understand and maintain, and that provide better performance than the cursor-based alternative. One option for complex queries is the use of common table expressions. |
| SQL Server Derived Tables
Some result sets are difficult to extract from a database without performing multiple queries or using temporary tables and cursors, which can harm performance. Derived tables help with some problems by creating virtual tables within the scope of a query. |
| SQL Server Transaction Savepoints
Sometimes Transact-SQL (T-SQL) scripts require the ability to roll back some parts of a transaction whilst allowing other elements of a process to be committed to the database normally. This can be achieved with the use of transaction savepoints. |
| Truncating Tables in SQL Server
Deleting large amounts of data from a SQL Server table can be an expensive operation, in terms of transaction log space, memory usage and the creation of locks. When emptying a table completely, truncating the table can be a less expensive option. |