NOLOCK without WITH and without parentheses is not a hint, because SQL Server reads it as a table alias. This post proves it with a lock test, shows why the compatibility level doesn’t change it, and gives a query that finds the bad syntax in your code. It ends with the safer ways to write the hint.
SQL SERVER – Error Due to Database Context – Error 208 – Invalid Object Name
Stuck on Error 208, Invalid Object Name, in SSMS? Often the object is fine and you are simply in the wrong database. Here is how database context causes it.
What are Forwarded Records in SQL Server? – Interview Question of the Week #145
What are forwarded records in SQL Server? It is an old concept, yet few new DBAs know it. Here is a short answer about heap rows that moved to a new page.
Create a Global Temporary Table in SQL Server
A global temporary table starts with two # signs, lives in tempdb, and any session can read it. This post creates one, reads it from a second window, and shows when SQL Server drops it. It also covers name collisions and the cases where a different table type is the better choice.
Optimize for Ad Hoc Workloads: When to Turn It On
Optimize for Ad Hoc Workloads shrinks the memory that one-time queries take in the plan cache. This post builds a demo of single-use plans, measures their share of the cache, and shows how to read the result. It also covers the cost of the setting and why parameterized queries are the better fix.
Stored Procedure Execution Count and Average Elapsed Time
To find a stored procedure execution count, query sys.dm_exec_procedure_stats and divide the total elapsed time by the count. This post builds a demo, shows the query, and explains why the numbers start again when a plan leaves the cache. It also fixes the object name lookup that returns NULL for other databases.
SQL SERVER – How to Create Table Variable and Temporary Table?
A Temporary table uses CREATE TABLE, while a table variable uses DECLARE. I use one row in each beginner example.