Posted on January 14, 2009 by Raghunath Bhandari
Difference with (nolock) vs (nolock) as a SQL Table Hint:
select sum(amount) from reward_expired (nolock)
difference was between using the table hint (nolock) and the table hint with (nolock).
Look at MSDN under SQL 2005,
“In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH [...]
Filed under: with (nolock) vs (nolock) | Tagged: Difference with (nolock) vs (nolock), FASTFIRSTROW, Microsoft SQL server, MSSQL, MSSQL server, NOEXPAND, NOLOCK, NOWAIT, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, SQL help, SQL Server, SQL Tips, SQL Tricks, T-SQL best way, Table Hints, TABLOCK, TABLOCKX, UPDLOCK, with (nolock) vs (nolock), XLOCK | Leave a Comment »
Posted on January 13, 2009 by Raghunath Bhandari
Table Hints (Transact-SQL)
Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query processing operation such as a table scan or index seek, or other options.
WITH ( <table_hint> [ [ , ]…n ] )
<table_hint> ::=
[ [...]
Filed under: Table Hints | Tagged: and NOEXPAND, FASTFIRSTROW, MSSQL Table Hints, NOLOCK, NOWAIT, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, SQL Locks, SQL Tips, SQL Tricks, Table Hints (Transact-SQL), TABLOCK, TABLOCKX, UPDLOCK, XLOCK | Leave a Comment »
Posted on January 13, 2009 by Raghunath Bhandari
Deadlocking and SQL best practices:
Deadlocking refers to the condition in which one resource is waiting on the action of a second, while that second action is waiting on the first. This is different from being blocked, or having to wait for a resource. Using the locks above, if a transaction had a shared lock, then [...]
Filed under: SQL Deadlocking | Tagged: MS SQL Deadlocks, NOLOCK, PAGLOCK, ROWLOCK, Solve Deadlocks, SQL Best practices and tips, SQL Deadlocking refers to the condition, SQL Deadlocks, SQL Locks, SQL Tips, SQL Tricks | Leave a Comment »