 | SQL Server NOLOCK Hint
Where locking causes problems with queries it can be useful to execute those queries with the NOLOCK hint. This specifies that a query should ignore existing locks and create no data locks. However, the hint introduces the potential of data corruption. |
 | SQL Updates, Joins and Triggers
A common error when writing SQL triggers is to forget that an operation that affects multiple rows will cause a trigger to execute only once. One example of the problem can be solved using UPDATE statements that include joins. |
| SQL Server Multipart Names
SQL Server-based applications and systems may include multiple schemas, databases and linked servers, all of which can be accessible from scripts and stored procedures. To allow the correct database objects to be accessed requires the use of multipart nam |
| 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 adds 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 a common task 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 sess |
| 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. |