After so many years of writing SQL queries, I cannot believe I never came across a QUALIFY query clause. It feels like discovering a treasure in the depths of the database ocean.Even ChatGPT was not aware of it!QUALIFY query clause was…
Did you know, that when you use READ UNCOMMITTED isolation level or WITH (NOLOCK) hint inside the query, it is still places Sch-S (schema stability) locks during compilation and execution and can be blocked by any concurrent trancastion…
Are you familiar with Trace Replay feature SQL Profiler? It's not really popular but actually can do a lot for you.Replay is the ability to save a trace and replay it later. This functionality lets you reproduce activity captured in a…
If you ever wondedered what king of locks your selct query ever issues and for how long, run it in QA environment with isolation level Repeatable Read, which holds all locks you select issues till the end of transaction. Default isolation…
It appears that Registered servers are not being stored in the registry now. There is a file here C:\Documents and Settings\{you}\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml that can be moved from server to…
Today I have played with partition merging. The target of this was to see how long it can take, which locks are being held etc.I see that even if partions are empty, the minimum time that it takes is 30 sec ( 2x2.66 CPU 7Gb RAM ). Most of…
Yes. Even if constraint checks partitioning column and "bad" data can exist in one partition only. See for yourself: I create table natively partitioned on integer field with another char field to fill more pages (full script at the end…
