Category Archives: Scripts

How to get all HEAP Tables or Tables without Clustered Index in Sql Server?

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

A Table that doesn’t have a Clustered Index is referred to as a HEAP Table. We can write a query like below to get all the HEAP Tables or tables that doesn’t have Clustered Index:

SELECT T.Name 'HEAP TABLE'
FROM sys.indexes I		
	INNER JOIN sys.tables T 
		ON I.object_id = T.object_id 
WHERE I.type = 0 AND T.type = 'U'

Let us understand this with an example:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create a Non-HEAP Table Customers Table 
with clustered and Non-clustered index.*/
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_Customers
	ON dbo.Customers(FirstName, LastName)
GO
/*Let us create a HEAP Table 
(i.e. table without any Clustered Indexes)*/
CREATE TABLE dbo.Orders (
	OrderId int IDENTITY (1, 1) NOT NULL ,
	CustomerId int NOT NULL ,
	CreationDT DATETIME NOT NULL)
GO

Now let us run the query to get all the HEAP Tables or tables that doesn’t have Clustered Index and verify the result:

List_all_HEAP_Tables_or_Tables_without_Clustered_Index

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