Development

The short answer: in the real world, only the first column works. When SQL Server needs data about the second column, it builds its own stats on that column instead (assuming they don’t already exist), and uses those two statistics together – but they’re not really correlated. For the longer answer, let’s take a large…

Normally, when SQL Server updates statistics on an object, it invalidates the cached plans that rely on that statistic as well. That’s why you’ll see recompiles happen after stats updates: SQL Server knows the stats have changed, so it’s a good time to build new execution plans based on the changes in the data. However,…

Nah, not necessarily. SQL Server’s query optimizer behavior keeps changing with every freakin’ version. Let’s illustrate it with a simple query against the Stack Overflow Users table: Transact-SQL CREATE INDEX Location ON dbo.Users(Location); GO SELECT COUNT(*) FROM dbo.Users WHERE Location = N'Ahmadabad, India' OPTION (RECOMPILE); 12345 CREATE INDEX Location ON dbo.Users(Location);GOSELECT COUNT(*) FROM dbo.Users    WHERE Location…