Question: How Does TOP WITH TIES Work in SQL Server?
Answer: This is an ancient question I just encountered in the recent Comprehensive Database Performance Health Check.
How to Drop All the User Created Statistics by SQL Server? – Interview Question of the Week #158
SQL Server creates statistics on its own, but DBAs and the Tuning Advisor add more. Here is a script to drop all user Created Statistics safely.
How to Find DISTINCT COUNT of Column Rows Without Using Distinct Count Keywords? – Interview Question of the Week #157
Only one of ten candidates solved this: find the DISTINCT COUNT of a column without using those keywords. Here are the traditional answer and the trick.
How to Get Top N Records Per Group? – Ranking Function – Interview Question of the Week #156
I gave 40 candidates this puzzle: get the top N records per group from a student marks table. Here is the simple answer using a Ranking Function.
How to List All the Nonclustered Indexes of Your Table? – Interview Question of the Week #155
How do you list all the clustered and nonclustered indexes of your table, including columnstore? Here is the script I use in my performance health checks.
How to List Queries With Memory Grant and Execution Plan? – Interview Question of the Week #154
A workshop customer suspected one of their queries used too much memory. Here is the script I gave them to list running queries with Memory Grant and plan.
How to List All ColumnStore Indexes with Table Name in SQL Server? – Interview Question of the Week #153
Question: How to List All ColumnStore Indexes with Table Name in SQL Server? As ColumnStore Indexes are getting more and more popular.