Category Archives: Tips/Tricks

How to find all the filtered indexes or all the tables having filtered indexes in Sql Server?

Scripts, Sql Server, Tips/Tricks, , , ,

We can write a query like below to get the name of all the filtered indexes or all the tables having filtered indexes in Sql Server:

SELECT DISTINCT T.Name 'Table Name',
  I.Name 'Filtered Index Name',
  I.Filter_Definition 'Filter Definition'
FROM sys.indexes I		
      INNER JOIN sys.tables T 
        ON I.object_id = T.object_id 
WHERE I.has_filter = 1
ORDER BY T.Name, I.Name

Let us see this with an example:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create a Table with a primary key column having 
a Clustered index, a Non-Clustered Filtered Index and 
a Non-Clustered Index without any filter.*/
CREATE TABLE dbo.Orders (
  OrderId int IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL,
  CustomerId int NOT NULL ,
  CreationDT DATETIME NOT NULL)
GO
CREATE NONCLUSTERED INDEX IX_FilteredIndex
	ON dbo.Orders(CreationDT) WHERE OrderId > 5000000
GO
CREATE NONCLUSTERED INDEX IX_NonFilteredIndex1
	ON dbo.Orders (CustomerId) 
GO

/*Let us create one more Table with clustered and 
a non clustered index without any filter.*/
CREATE TABLE dbo.Customers (
  CustomerId int IDENTITY (1, 1) 
           PRIMARY KEY CLUSTERED NOT NULL,
  FirstName Varchar(50),
  LastName Varchar(50))
GO
CREATE NONCLUSTERED INDEX IX_NonFilteredIndex2
  ON dbo.Customers(FirstName, LastName)
GO

Now let us run the query to get the list of all the filtered indexes or all the tables having filtered indexes in Sql Server and verify the result:
How_To_Find_All_Filtered_Indexes_Or_Tables_With_Filtered_Index

How to find all the indexes that have included columns in it and the name of the table to which the index belongs to?

Scripts, Sql Server, Tips/Tricks, , ,

We can write a query like below to get the name of all the indexes that have included columns in it and the name of the table to which the index belongs to:

SELECT DISTINCT T.Name 'Table Name',
		I.Name 'Index Name',
		I.type_desc 'Index Type',
		C.Name 'Included Column Name'
FROM sys.indexes I 
 INNER JOIN sys.index_columns IC 
  ON  I.object_id = IC.object_id AND I.index_id = IC.index_id 
 INNER JOIN sys.columns C 
  ON IC.object_id = C.object_id and IC.column_id = C.column_id 
 INNER JOIN sys.tables T 
  ON I.object_id = T.object_id 
WHERE is_included_column = 1
ORDER BY T.Name, I.Name

Let us see this with an example:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create a Table with a primary key column having 
a clustered index, a non clustered index with included columns 
and a non clustered index without any included columns.*/

CREATE TABLE dbo.TblIndexWithIncludedColumn (
	Col1 int IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL ,
	Col2 int NOT NULL ,
	Col3 int NOT NULL ,
	Col4 int NOT NULL )
GO
CREATE NONCLUSTERED INDEX IX_IndexWithIncludedColumn 
	ON dbo.TblIndexWithIncludedColumn(Col2) INCLUDE (Col3)
GO
CREATE NONCLUSTERED INDEX IX_IndexWithoutIncludedColumn1 
	ON dbo.TblIndexWithIncludedColumn (Col4) 
GO

/* Let us create one more Table with clustered and 
non clustered index without any included columns in it. */

CREATE TABLE dbo.TblIndexWithOutIncludedColumn (
	C1 int IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL ,
	C2 int NOT NULL)
GO
CREATE NONCLUSTERED INDEX IX_IndexWithoutIncludedColumn1 
	ON dbo.TblIndexWithOutIncludedColumn(C2)
GO

Now let us run the query to get the list of all the indexes that have included columns in it and the name of the table to which the index belongs to and verify the result:

How_To_Find_All_Indexes_With_Included_Column

How to get Quarter’s Start and End Date for a given date in Sql Server

Scripts, Sql Server, Tips/Tricks, , ,

We can use a query like below to get Quarter’s Start and End Date for a given date.

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
  DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                        AS 'Quarter Start Date',       
  DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0)) 
                        AS 'Quarter End Date'

Quarter_Start_End_Date_In_SqlServer

Note the Quarter End Date returned by the above query is without time part. If we need to generate a report for a quarter then we need to include all the transactions happening on the last date of the quarter till mid night. Then in the query instead of comparing with last date of the quarter we can check whether it is less than the next quarter start date.

Below query shows how to get Quarter’s Start Date and the Next Quarter Start Date for a given date.

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
	DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                               AS 'Quarter Start Date',       
	DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0) 
                               AS 'Next Quarter Start Date'

Quarter_Start_And_Next_Quarter_Start_Date