Tag Archives: Sql Server

A-Z of Filtered Indexes with examples in Sql Server

Performance Optimization, Sql Server, SQL Server 2008 What's New, , , , , , , , , , , , , , , , , , ,

Filtered Index (i.e. Index with where clause) is one of the new feature introduced in Sql Server 2008. It is a non-clustered index, which can be used to index only subset of the records of a table. As it will have only the subset of the records, so the storage size will be less and hence they perform better from performance perspective compared to the classic non-clustered indexes.

Before using filtered index I strongly recommend everyone to go through the article INSERT/UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Basically, adding a filtered index on a table may cause the existing working stored procedure to fail if the stored procedure was not created with a setting which doesn’t meet the filtered index prerequisites.

Let us understand filtered index with examples:

Example 1:

Let us first create a customer table with hundred thousand records as shown in the below image by the following script. Note Customer tables Country and CountryCopy column type and values are same. And also every tenth record has the Country/CountryCopy column value as United States and rest of the records column value as India. Also the age column value between 18 to 75 years.

FilteredIndex Example 1

CREATE DATABASE SqlHintsFilteredIndexDemo
GO
USE SqlHintsFilteredIndexDemo
GO
CREATE TABLE dbo.Customer(
CustomerId      INT NOT NULL PRIMARY KEY IDENTITY(1,1),
FirstName       VARCHAR(50) ,
LastName        VARCHAR(50),
Country         VARCHAR(50),
CountryCopy     VARCHAR(50),
Age				INT)
GO
SET NOCOUNT ON
GO
--Populate 100K records, where every 10th record record 
--has country as United States and rest of the 
--records have country as India
DECLARE @i INT = 1, @istring VARCHAR(20), @Country VARCHAR(50)
WHILE(@i<=100000)
BEGIN
 IF(@i % 10 =0) 
  SET @Country = 'United States'   
 ELSE
  SET @Country = 'India'
     
 SET @istring = CAST(@i AS VARCHAR(20))
 
 INSERT INTO dbo.Customer(FirstName, LastName, 
                  Country, CountryCopy, Age)
 VALUES ('FN' + @istring, 'LN' + @istring,@Country , @Country,
 ROUND(RAND(convert(varbinary, newid()))*57+18,0))--Age 18 to 75
 SET @i = @i +1
END
GO

Let us now create a classic non-clustered index on the Country column

CREATE NONCLUSTERED INDEX IX_Customer_Country
ON dbo.Customer(Country)
GO

Let us now create a filtered non-clustered index on the CountryCopy column

CREATE NONCLUSTERED INDEX IXF_Customer_CountryCopy
ON dbo.Customer(CountryCopy)
WHERE CountryCopy = 'United States'
GO

Now table has three indexes one is a clustered index created created on the CustomerId column as result of it being a primary key column, the Country column has classic non-clustered index IX_Customer_Country on it and the CountryCopy column has filtered non-clustered index IXF_Customer_CountryCopy on it. Let us now see how many number of rows each of these indexes has and also the size of these indexes using the below script:

SELECT I.name [Index Name],i.type_desc [Index Type],
 PS.row_count [Number of rows],
 PS.used_page_count [Used page count],
 PS.reserved_page_count [Reserved page count]
FROM sys.indexes I
 INNER JOIN sys.dm_db_partition_stats ps
  ON I.object_id = ps.object_id AND I.index_id = ps.index_id
WHERE I.object_id = OBJECT_ID('Customer')

RESULT:
Filtered Indexes Require Less Storgae

So, from the above result it is clear that the number of records in the filtered index is equal to the number records in the table which matches to the filter criteria, so the filtered indexes requires less storage space and they perform better from performance perspective.

Now let us check by running below two queries and see how filtered and classic index perform from performance perspective:

--Filtered Index
SELECT COUNT(1) FROM dbo.Customer WITH(NOLOCK)
WHERE CountryCopy = 'United States'
GO
--Classic regular Index
SELECT COUNT(1) FROM dbo.Customer WITH(NOLOCK)
WHERE Country = 'United States'
GO

RESULT:
FilteredIndexPerformance

From the above result it is clear that the query which uses filtered index has a cost of 37% whereas the query which uses classic index has a cost of 63%. From this result it is clear that the query which uses filtered index performs better.

Let us drop the two non-clustered indexes created in this example by the following script:

DROP INDEX IX_Customer_Country ON dbo.Customer
GO
DROP INDEX IXF_Customer_CountryCopy ON dbo.Customer
GO

Example 2: This example explains the columns in the filter index’s filter criteria/expression doesn’t need to be a key column in the filtered index definition

If we know that the look-up on the Customer tables record by FirstName and LastName is always for the United States customers. Then in such a scenario, a filtered index like below is more suitable than having a regular non-clustered index.

CREATE INDEX IXF_CUSTOMER_FirstName_LastName
ON dbo.Customer(FirstName,LastName)
WHERE Country = 'United States'
GO

This index will only index the records whose country is ‘United States’ by FirstName and LastName. And also observe that the Country column is used in the filter criteria, but it is not a key column of the index.

Note: This index will not be used if country of the customer is not ‘United States’.

Example 3: Filter Criteria need to be part of the queries WHERE clause to force the usage of the Filtered Index

Let us try executing the below query and see whether it is using filtered index created in the previous example (i.e. example 2).

Note: For the customer with FirstName = ‘FN90000’ AND LastName = ‘LN90000’ the Country/CountryCopy column value is ‘United States’

SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE FirstName = 'FN90000' AND LastName = 'LN90000'

RESULT
FilterIndex Not Used 1

From the above result it is clear that query is not using the filtered index. Now let us add the filter index’s filter criteria in the queries WHERE clause and verify whether it is using the filtered index:

SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE Country = 'United States'
 AND FirstName = 'FN90000' AND LastName = 'LN90000'

RESULT:
ForcingFilteredIndex

From the above examples it is clear that the filter index’s filter expression need to be part of the queries WHERE clause to force it’s usage.

Below result depicts the performance comparison of the above two queries (i.e. one which doesn’t use the filtered index and another one which uses the filtered index):
FilteredIndexPerformanceComparision

EXAMPLE 4: Whether I can specify the index hint for the query to force filtered index usage instead of writing the filter expression of the filtered index in the queries WHERE clause?

While going through Example 3, you may have thought instead of writing filter index expression in the queries WHERE clause, we would have specified the INDEX hint in the query to force it’s usage. Let us see whether this thought works:

SELECT *
FROM dbo.Customer WITH(NOLOCK, 
        INDEX(IXF_CUSTOMER_FirstName_LastName))
WHERE FirstName = 'FN90000' AND LastName = 'LN90000'

RESULT:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

So, from the above result it is clear that we can’t force a filtered index usage by specifying the index hint, instead of it as explained in example 3 the WHERE clause of the query need to have filtered index’s filter expression.

EXAMPLE 5: Whether using a local variable instead of the constant of the filter expression of the filtered index’s in the queries WHERE clause still results in the usage of the filtered index?

As per Example 3, to force the filter index usage we need to write the filter index’s filter expression in the queries WHERE clause (i.e. Country = ‘United States’). Instead of directly specifying the country name ‘United States’ directly in the query, whether we can declare a local variable and assign it the country name and then use the local variable in the query. The below example demonstrates this use case scenario:

--Query with filter index's filter expressions value 
--as a local variable in the WHERE clause 
DECLARE @CountryName VARCHAR(50) = 'United States'
SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE Country = @CountryName 
 AND FirstName = 'FN90000' AND LastName = 'LN90000'

RESULT:
Filtered Index Local Variable as Filter Expression

From the above query result it is clear that we can’t replace the constant of the filter expression of the filtered index in the queries where clause by a local variable.

Drop the index IXF_CUSTOMER_FirstName_LastName created in example 2 by the following script:

DROP INDEX IXF_CUSTOMER_FirstName_LastName ON dbo.Customer

EXAMPLE 6: Whether the WHERE clause of the query need to have the same constant expression as specified in the Filtered Index’s filter expression to force the filtered index usage.

Let us create a filtered index on the FirstName and LastName column with filter criteria as Age > 60.

CREATE INDEX IXF_CUSTOMER_FirstName_LastName
ON dbo.Customer(FirstName,LastName)
WHERE Age > 60
GO

Note: For the customer with FirstName = ‘FN88002’ AND LastName = ‘LN88002’ the age column value is 72.

Let us check whether the below query which has the same constant expression in the WHERE clause as in the filtered index’s filter expression forces the usage of the filtered index

SELECT *
FROM dbo.Customer WITH(NOLOCK)
WHERE Age > 60 
 AND FirstName = 'FN88002' AND LastName = 'LN88002'

RESULT:
Filtered Index Usage Example 5

So, from the above example it is clear that if the queries WHERE clause has the same constant expression as the filtered index’s filter expression, then the filtered index is used.

Let us change the WHERE clauses Age > 60 condition to Age > 65 and see whether the query is still forcing the usage of filtered index.

SELECT FirstName, LastName
FROM dbo.Customer WITH(NOLOCK)
WHERE Age > 65
 AND FirstName = 'FN88002' AND LastName = 'LN88002'

RESULT:
Filtered Index Usage Example 6

From the above result the query is still using the filtered index when the WHERE clause Age > 60 condition is changed to Age > 65.

Now let us try to change the WHERE clauses Age > 65 condition to Age > 55 and see whether the query is still forcing the usage of filtered index.

SELECT FirstName, LastName
FROM dbo.Customer WITH(NOLOCK)
WHERE Age > 55
 AND FirstName = 'FN88002' AND LastName = 'LN88002'

RESULT:
Filtered Index Usage Example 6 2

From the above result it is clear that the query is not using the filtered index when the WHERE clause Age > 65 condition is changed to Age > 55.

So, the conclusion is: the constant expression specified in the queries WHERE clause should be same or subset of the filtered index’s filter expression to force the filtered index usage.

Will be continued with couple of more examples…

Truncate all/all except few/specified Tables of a Database in Sql Server

Scripts, Sql Server, , , , , ,

This article presents how we can generate a script to truncate all tables/all tables except few specified tables/specified tables of a Database in Sql Server.

One of major the problem with table truncation is, we need to remove if there are any foreign key’s defined in other tables which references the columns in the table to be truncated. So to truncate a table we need to first remove all the foreign key references then truncate the table and finally add back the removed foreign key constraints.

Let us first create a demo database with sample tables as shown in the below image by the by the following script:

TruncateAllDemoDB

--Create demo database with tables having foreign
--key relations between them and sample data in it
SET NOCOUNT ON
GO
CREATE DATABASE SqlhintsTruncateDBDemo
GO
USE SqlhintsTruncateDBDemo
GO
CREATE TABLE [dbo].[Customer] (
    [CustID] [int] NOT NULL ,
    CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED 
    ( [CustID] ) 
) 
GO
CREATE TABLE [dbo].[Order] (
    [OrderID] [int] NOT NULL ,
    [CustID] [int] NOT NULL ,
    CONSTRAINT [PK_Order] PRIMARY KEY  CLUSTERED 
    ( [OrderID] ),
    CONSTRAINT [FK_Order_Customer] FOREIGN KEY
    ( [CustID] ) REFERENCES [dbo].[Customer] (CustID) 
) 
GO
CREATE TABLE [dbo].[OrderItem] (
    [OrderItemId] [int] NOT NULL ,
    [OrderId] [int] NOT NULL ,
    CONSTRAINT [PK_OrderItem] PRIMARY KEY  CLUSTERED 
    ( [OrderItemId] ),
    CONSTRAINT [FK_OrderItem_Order] FOREIGN KEY
    ([OrderId]) REFERENCES [dbo].[Order] ([OrderId]) 
) 
GO

We can use the below script to generate the script to truncate all the tables of a database.

USE SqlhintsTruncateDBDemo
GO
SET NOCOUNT ON
GO
--Get the list of all the tables to be truncated
 DECLARE @TablesToBeTruncated AS TABLE
 (Id INT IDENTITY(1,1),TableObjectId INT, TableName SYSNAME,
	SchemaId INT)
INSERT INTO @TablesToBeTruncated
 SELECT ST.object_id,ST.name,ST.schema_id
 FROM sys.Tables ST
 WHERE ST.type = 'U' AND ST.NAME NOT LIKE '#%' 
 AND ST.name <> 'sysdiagrams'
 --AND ST.NAME NOT IN ('') -- Specify here the comma separated table names for which truncation is not required
 --AND ST.NAME IN ('') -- Specify here the comma separated table names which needs to be truncated

 --Generate the foreignkeys drop and create back script 
DECLARE @CreateScript AS NVARCHAR(MAX), @DropScript AS NVARCHAR(MAX)
SELECT 
	------------DROP SCRIPT--------------------
	@DropScript = ISNULL(@DropScript,'') + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
	 + QUOTENAME(OBJECT_NAME(FKey.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(FKey.name)
	 + CHAR(10),
	 -----------CREATE BACK SCRIPT-------------
	@CreateScript = ISNULL(@CreateScript,'') + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
	 + QUOTENAME(OBJECT_NAME(FKey.parent_object_id)) + ' ADD CONSTRAINT ' + QUOTENAME(FKey.name)
	 + ' FOREIGN KEY ' + '(' + STUFF(( -- Get the list of columns
				 SELECT ',' + QUOTENAME(COL_NAME(FKeyCol.parent_object_id, FKeyCol.parent_column_id))
				 FROM SYS.FOREIGN_KEY_COLUMNS FKeyCol
				 WHERE FKey.OBJECT_ID = FKeyCol.constraint_object_id
				 ORDER BY FKeyCol.constraint_column_id
				 FOR XML PATH('')),1,1,'') + ')'
	 + ' REFERENCES ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.' 
				+ QUOTENAME(OBJECT_NAME(FKey.referenced_object_id)) + ' (' + STUFF(( -- Get the list of columns
				SELECT ',' + QUOTENAME(COL_NAME(FKeyCol.referenced_object_id, FKeyCol.referenced_column_id))
				FROM SYS.FOREIGN_KEY_COLUMNS FKeyCol
				WHERE FKey.OBJECT_ID = FKeyCol.constraint_object_id
				ORDER BY FKeyCol.constraint_column_id
				FOR XML PATH('')),1,1,'') + ') '
	 + CASE WHEN update_referential_action_desc = 'CASCADE' THEN ' ON UPDATE CASCADE'
	        WHEN update_referential_action_desc = 'SET_DEFAULT' THEN ' ON UPDATE SET DEFAULT'
	        WHEN update_referential_action_desc = 'SET_NULL' THEN ' ON UPDATE SET NULL'
	        ELSE '' 
	   END
	 + CASE WHEN delete_referential_action_desc = 'CASCADE' THEN ' ON DELETE CASCADE'
			WHEN delete_referential_action_desc = 'SET_DEFAULT' THEN ' ON DELETE SET DEFAULT'
			WHEN delete_referential_action_desc = 'SET_NULL' THEN ' ON DELETE SET NULL'
			ELSE ''
	   END  + CHAR(10)
 FROM @TablesToBeTruncated Tlist
			INNER JOIN SYS.FOREIGN_KEYS FKey
				ON Tlist.TableObjectId = FKey.referenced_object_id

--PRINT THE TRUNCATION SCRIPT
IF LEN(ISNULL(@DropScript,'')) > 0
 BEGIN
	 PRINT CHAR(10) + ' GO ' + CHAR(10) + '--------DROP FOREIGN KEY CONSTRAINTS SCRIPT--------'
	 PRINT @DropScript + CHAR(10) + ' GO ' + CHAR(10)
 END

PRINT '--------TRUNCATE TABLES SCRIPT--------'
--TRUNCATE TABLES
DECLARE @id INT,@truncatescript NVARCHAR(MAX)
SELECT @id = MIN(Id)FROM @TablesToBeTruncated
WHILE @id is not null
 BEGIN
	 SELECT @truncatescript = 'TRUNCATE TABLE ' + QUOTENAME(SCHEMA_NAME(SchemaId)) + '.' + QUOTENAME(TableName) 
	 FROM @TablesToBeTruncated WHERE Id = @id
	 PRINT @truncatescript
	 SELECT @id = MIN(Id)FROM @TablesToBeTruncated WHERE Id > @id
 END

IF LEN(ISNULL(@CreateScript,'')) > 0
 BEGIN
	 PRINT CHAR(10) + ' GO ' + CHAR(10) + '--------CREATE BACK THE FOREIGN KEY CONSTRAINTS SCRIPT--------'
	 PRINT CAST((@CreateScript + CHAR(10) + ' GO ' + CHAR(10)) AS NTEXT)
 END
 GO

Below is the result of executing the above script:

TruncateAllTableDataResult

Disclaimer: As TRUNCATE table removes all the records from the table permanently. So, think twice before executing the truncate table statement.

By default the above script generates the script to truncate all the tables of a database. If need is to truncate only the specified tables then uncomment the line number 15 (i.e.–AND ST.NAME IN (”)) in the above script and mention the list of tables to be truncated. And if requirement is to truncate all the tables except few tables, then uncomment only the line number 14 (i.e. –AND ST.NAME NOT IN (”)) in the above script and mention the list of tables which shouldn’t be considered for truncation.

Cannot truncate table ‘xyz’ because it is being referenced by a FOREIGN KEY constraint – Sql Server

Errors, Scripts, Sql Server, , , , , , , , , , , ,

In this article we will discuss on when we get the error like below in Sql Server and how to resolve it.

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table ‘Customer’ because it is being referenced by a FOREIGN KEY constraint.

To demonstrate this error let us first create a demo db ‘SqlhintsTruncateDemo’, two tables Customer and Order.

CREATE DATABASE SqlhintsTruncateDemo
GO
USE SqlhintsTruncateDemo
GO
CREATE TABLE [dbo].[Customer] (
	[CustID] [int] IDENTITY (1, 1) NOT NULL ,
	CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED 
	(
		[CustID]
	) 
) 
GO
CREATE TABLE [dbo].[Order] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustID] [int] NOT NULL ,
	CONSTRAINT [PK_Order] PRIMARY KEY  CLUSTERED 
	(
		[OrderID]
	),
	CONSTRAINT [FK_Order_Customer] FOREIGN KEY 
	(
		[CustID]
	) REFERENCES [dbo].[Customer] (
		[CustID]
	) 
) 
GO

[ALSO READ] Truncate all/all except few/specified Tables of a Database in Sql Server

In the above script we have created a foreign key constraint FK_Order_Customer on the CustID column of the Order table which is referring to the CustID primary key column of the Customer table.

Disclaimer: As TRUNCATE table removes all the records from the table. Be careful before issuing this command.

Now try to truncate the Order Table

TRUNCATE TABLE [dbo].[Order]

RESULT:
Truncate Table Successful

As per the above result truncation of the Order table is successful. Now try to truncate the Customer Table

TRUNCATE TABLE [dbo].[Customer]

RESULT:
Truncate Table Failure

As per the result the truncation of the Customer table is failing, because the
CustID column of the Customer table is referenced by the CustID column of the Order Table.

If we still want to Truncate the table, then we have to drop all the foreign key
constraints which are referring to the table to be truncated.

So now drop the foreign key constraint FK_Order_Customer and then try truncating
the Customer table.

ALTER TABLE [dbo].[Order]
DROP CONSTRAINT FK_Order_Customer
GO
TRUNCATE TABLE [dbo].[Customer]
GO

RESULT:
Truncate Table Successful After Dropping of Foreign Key Constraint

As per the above result it is clear that now the table truncation is successful after dropping all the foreign key constraints which are refering to the table to be truncated.

We can use script like below to identify whether a Table is referenced by another Tables foreign key constraints in Sql Server.

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table',
OBJECT_NAME(FK.parent_object_id) 'Referring Table',
FK.name 'Foreign Key',
COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
INNER JOIN sys.foreign_key_columns AS FKC
ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Enter Table Name'

In the above script replace the string ‘Enter Table Name’ with the table name for which you want to find out the referencing tables and the refering foreign key constraint name.

[ALSO READ] Truncate all/all except few/specified Tables of a Database in Sql Server