Category: Queries

Divide the rows in equals batches

Here is a simple and practical use-case of the NTILE function. We've used it to divide the rows of sys.columns into N groups. N is the number of groups. We've used the number of groups as 7. So the entire rows will be aligned equally to any of the group between 1 to 7. When … Continue reading Divide the rows in equals batches →

Get the month end dates of last N years

Here is a simple T-SQL script that may come handy if you need the month end dates of last N years. N is the number of years. /* Assign the dynamic number of years here. You can write a procedure / table-valued function to accept this value as a parameter. */ DECLARE @Last_N_Years INT = … Continue reading Get the month end dates of last N years →

Get the month end dates of last N months

Here is a simple T-SQL script that may come handy if you need the month end dates of last N months. N is the number of months. /* Assign the dynamic number of months here. You can write a procedure / table-valued function to accept this value as a parameter. */ DECLARE @Last_N_Months INT = … Continue reading Get the month end dates of last N months →

Query to find Nonclustered Index clashing with Clustered Index

It's being quite a long time when I made the last post. Actually, I was lacking a topic that should trigger me to write. Finally, I got one when I was working on one of the Performance Tuning assignments. Performance Tuning is a mix of wisdom and exploring the unknown, especially when you have a … Continue reading Query to find Nonclustered Index clashing with Clustered Index →

Query to list all the procedures and triggers not having SET NOCOUNT ON defined

It is a tiny query in size but can play a crucial role to avoid overhead, caused due to missing SET NOCOUNT ON definition in the stored procedures and triggers. Read more about SET NOCOUNT here. Here are few useful articles talking about why it's important to add SET NOCOUNT ON. SET NOCOUNT ON Improves … Continue reading Query to list all the procedures and triggers not having SET NOCOUNT ON defined →