MS SQL Server allows using the TSQL hint ‘WITH (NOLOCK)’ to the joins of SELECT statements. At times the WITH (NOLOCK) hint can be justified and useful, but it comes with inherent dangers that are often ignored or not understood. The WITH (NOLOCK) hint is often overused or is used when it should not be.…

Temporary tables and table variables in SQL Server actually perform quite differently under different circumstances. Temporary tables are decelared as: CREATE Table #TableName ( id INT ) Table variables are declared as: DECLARE @TableName TABLE ( id INT ) Temporary tables are transaction bound whereas table variables are not. This means that if a transaction in…

Sometimes it is useful to be able to search for the number of occurrences of a particular word or phrase within a table column. Although one can use a cursor and calculate the word count in a loop it much more efficient to do the calculations within a single statement. Here is the Transact-SQL query…