SQL Hints (WITH NOLOCK for example)

SAMPLE SELECT SQL- USING HINTS TABLE WITH (NOLOCK)

A record that was inserted in the table in a concurrent transaction is automatically read even if the concurrent transaction has not yet ended, when using the SQL tips presented here.

NOLOCK
Is equivalent to READUNCOMMITTED

READUNCOMMITTED
Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).

READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.

READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

IMPORTANT:
Note that we are using the EncodeSql () function to guarantee the ordering of the table without risk of security for the operation in question.
Example of dynamic sorting in Advanced Query
Use of the EncodeSql () function to guarantee the security of the application.

NamelnkSort	Account NumberlnkSort	BalancelnkSort	Is ActivelnkSort
No accounts to show...

0 records

SAMPLE SELECT SQL- USING HINTS TABLE WITH (READUNCOMMITTED)

View Docs on MSDN

A record that was inserted in the table in a concurrent transaction is automatically read even if the concurrent transaction has not yet ended, when using the SQL tips presented here.

NOLOCK
Is equivalent to READUNCOMMITTED

READUNCOMMITTED
Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).

READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.

READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

IMPORTANT:
Note that we are using the EncodeSql () function to guarantee the ordering of the table without risk of security for the operation in question.
Example of dynamic sorting in Advanced Query
Use of the EncodeSql () function to guarantee the security of the application.

NamelnkSort	Account NumberlnkSort	BalancelnkSort	Is ActivelnkSort
No accounts to show...

0 records

SAMPLE SELECT SQL- USING HINTS TABLE WITH (READCOMMITTED)

View Docs on MSDN

READCOMMITTED
Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using either locking or row versioning.
If the database option READ_COMMITTED_SNAPSHOT is OFF, the Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed.
If the database option READ_COMMITTED_SNAPSHOT is ON, the Database Engine does not acquire locks and uses row versioning.

IMPORTANT:
Note that we are using the EncodeSql () function to guarantee the ordering of the table without risk of security for the operation in question.
Example of dynamic sorting in Advanced Query
Use of the EncodeSql () function to guarantee the security of the application.

NamelnkSort	Account NumberlnkSort	BalancelnkSort	Is ActivelnkSort
No accounts to show...

0 records

SAMPLE SELECT SQL- USING HINTS QUERY OPTION (MAXDOP)

View Docs on MSDN

MAXDOP <N>

Applies to: SQL Server (starting with SQL Server 2008) and Azure SQL Database.

Overrides the max degree of parallelism configuration option of sp_configure. Also overrides the Resource Governor for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure. If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see Configure the max degree of parallelism Server Configuration Option.

IMPORTANT:
<N>: Where N indicates the number of processor cores you want for the query in question. This number varies between 0 ( ZERO ) and the maximum number your SQL server has. If informed a number higher than the maximum of your server, SQL will respect the maximum. If entered 0, SQL will decide how many processor cores to use in the query.

Note that we are using the EncodeSql () function to guarantee the ordering of the table without risk of security for the operation in question.
Example of dynamic sorting in Advanced Query
Use of the EncodeSql () function to guarantee the security of the application.

NamelnkSort	Account NumberlnkSort	BalancelnkSort	Is ActivelnkSort
No accounts to show...

0 records

SAMPLE SELECT SQL- USING HINTS QUERY OPTION (MAXRECURSION)

View Docs on MSDN

MAXRECURSION <N>

Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32,767. When 0 is specified, no limit is applied. If this option isn't specified, the default limit for the server is 100.

When the specified or default number for MAXRECURSION limit is reached during query execution, the query ends and an error returns.

Because of this error, all effects of the statement are rolled back. If the statement is a SELECT statement, partial results or no results may be returned. Any partial results returned may not include all rows on recursion levels beyond the specified maximum recursion level.

IMPORTANT:
This query hint makes sense to use when your query uses recursive CTEs.
We created an exclusive topic for CTEs..

Note that we are using the EncodeSql () function to guarantee the ordering of the table without risk of security for the operation in question.
Example of dynamic sorting in Advanced Query
Use of the EncodeSql () function to guarantee the security of the application.

NamelnkSort	Account NumberlnkSort	BalancelnkSort	Is ActivelnkSort
No accounts to show...

0 records