Category Archives: Tips/Tricks

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

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?

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?

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

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

How to get Date Part only from DateTime in Sql Server

Many times we come across a scenario where we need to get Date Part only from DateTime in Sql Server. There are multiple ways of doing this, here I am listing out few of them:

1) Below approach works in Sql Server 2008 and above:

SELECT CONVERT (DATE, GETDATE()) 'Date Part Only'

RESULT:
Date Part Only
--------------
2013-07-14

2) Below approaches works in all the versions of Sql server

i) Get Date Part only from DateTime using CONVERT function
Example 1:

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) 'Date Part Only'

RESULT:
Date Part Only
--------------
20130714

Example 2:

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) 'Date Part Only'

RESULT:
Date Part Only
--------------
2013/07/14

The results of the above query is of type VARCHAR, if we want the result to be of type DATETIME we can write a query like below:

Example 1:

SELECT CONVERT(DATETIME,
			CONVERT(VARCHAR(10), GETDATE(), 112)) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

Example 2:

SELECT CONVERT(DATETIME,
			CONVERT(VARCHAR(10), GETDATE(), 111)) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

ii) Get Date Part only from DateTime using DateTime functions

From performance perspective this is the better approach instead of first converting DATETIME to VARCHAR and then VARCHAR to DATETIME.

Example 1:

SELECT DATEADD(dd, 0, 
        DATEDIFF(dd, 0, GETDATE())) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

Example 2:

DECLARE  @DatePartOnly DATETIME
SEt @DatePartOnly = DATEDIFF(DD, 0, GETDATE())
SELECT @DatePartOnly 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

iii) Get Date Part only from DateTime using FLOOR and CAST functions

As we know Sql Server internally stores DATETIME as two 4-byte integers. First 4-byte stores the elapsed number days since SQL Server’s DATETIME type’s start date 19000101.The Second 4-bytes Store the Time of Day i.e. clock-ticks since midnight. Each clock-tick is equivalent to 3.33 milliseconds.

So with above said internal storgae of the DATETIME, we can first convert the DATETIME to DECIMAL, then from decimal part ignore the fractional position and get only the integer part. Finally convert the integer to DATETIME as shown below:

SELECT CAST( -- Convert the integer to DATE
         FLOOR(-- Get largest Integer less than or equal to the decimal value
                CAST(GETDATE() AS DECIMAL(12, 5)) -- Convert DATETIME to DECIMAL
              ) 
         AS DATETIME) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

iv) Get Date Part only from DateTime using DATEPART and CONVERT functions

DECLARE @GETDATE AS DATETIME = GETDATE()
SELECT CONVERT(VARCHAR(4),DATEPART(YEAR, @GETDATE)) 
       + '/'+ CONVERT(VARCHAR(2),DATEPART(MONTH, @GETDATE)) 
       + '/' + CONVERT(VARCHAR(2),DATEPART(DAY, @GETDATE)) 
         'Date Part Only'

RESULT:
Date Part Only
--------------
2013/7/14

You may like to read the other popular articles on Date and Time: