SQL SERVER – Solution to Puzzle – Simulate LEAD() and LAG() without Using SQL Server 2012 Analytic Function

Earlier I wrote a series on SQL Server Analytic Functions of SQL Server 2012. During the series to keep the learning maximum and having fun, we had few puzzles. One of the puzzle was simulating LEAD() and LAG() without using SQL Server 2012 Analytic Function. Please read the puzzle here…
Read More

SQL SERVER – Puzzle to Win Print Book – Explain Value of PERCENTILE_CONT() Using Simple Example

From last several days I am working on various Denali Analytical functions and it is indeed really fun to refresh the concept which I studied in the school. Earlier I wrote article where I explained how we can use PERCENTILE_CONT() to find median over here SQL SERVER – Introduction to PERCENTILE_CONT()…
Read More

SQL SERVER – Puzzle to Win Print Book – Write T-SQL Self Join Without Using LEAD and LAG

Last week we asked a puzzle SQL SERVER – Puzzle to Win Print Book – Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY . This puzzle got very interesting participation. The details of the winner is listed here. In this puzzle we received two very important feedback. This puzzle…
Read More

SQL SERVER – A Simple Quiz – T-SQL Brain Trick

Today we are going to have very simple and interesting question. Run following T-SQL Code in SSMS. There are total of five lines. Three T-SQL statements separated by two horizontal lines. SELECT MAX(OBJECT_ID) FROM sys.objects ______________________________________ SELECT MIN(OBJECT_ID) FROM sys.objects ______________________________________ SELECT COUNT(OBJECT_ID) FROM sys.objects Now when you execute individual…
Read More

Puzzle – Usage of New Index Hints – ForceSeek and ForceScan

Tomorrow is the weekend. I just thought, let us explore something new but a quick puzzle to explore about index hints.

SQL Server Denali has new Query Hint – FORCESCAN. In earlier version of SQL Server we already have Query Hint FORCESEEK but now the counter part also exists. The quick understanding is there will be cases when FORCESEEK or FORCESCAN will be helpful and improve the performance of the query.

Read More