Filtered Index

Filtered Index

An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

When to use Filtered Index – Example

You manage a database named Customers, which includes a table named Orders. The Orders table is frequently queried, but only orders with a sales total of more than $1000.00 are required in the query. You can create an filtered index to speed up these types of queries at the same time, ensuring the index is as small as possible.

Advantages :

Filtered indexes can provide the following advantages over full-table indexes:
  • Improved query performance and plan quality
    A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.
  • Reduced index maintenance costs
    An index is maintained only when data manipulation language (DML) statements affect the data in the index. A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.
  • Reduced index storage costs
    Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

Example with syntax to create Filtered Index:

For example, the products listed in the Production.Product table are each assigned to a ProductSubcategoryID, which are in turn associated with the product categories Bikes, Components, Clothing, or Accessories. These categories are heterogeneous because their column values in the Production.Product table are not closely correlated. For example, Color, ReorderPoint, ListPrice, Weight, Class, and Style have unique characteristics for each product category. Suppose that there are frequent queries for Accessories which have subcategories 27-36. You can improve the performance of queries for Accessories by creating a filtered index on the Accessories subcategories.

    ON Production.Product (ProductSubcategoryID, ListPrice)
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;

SQL Server : SQL Query to get schema of columns in a SQL table

SQL Server : SQL Query to get schema of columns in a SQL table

You can use system table information_schema.COLUMNS to fetch schema details of a SQL table


select *

FROM information_schema.COLUMNS WHERE table_name = ’employee’ — Table Name

ORDER BY ordinal_position

Column Names returned using above query :


SQL Server : What is calculated or computed column in SQL

Calculated or computed column in SQL

Calculated or Computed column in table uses expression which uses constant and other columns in table. This column is not physically stored and is virtual column unless persistence property is specified.


1. Calculated or computed column cannot be specified with DEFAULT constraints.
2. Calculated or computed column cannot be specified with NOT NULL constraints
3. Calculated or computed column cannot be specified with FOREIGN KEY constraints

Add a Calculated or computed column to a table

CREATE TABLE dbo.CustomerStocks
CustomerID int NOT NULL , StockID int NOT NULL
  , NoofStocks int
, UnitPrice money
, Amount AS NoofStocks * UnitPrice

-- Insert values into the table.
INSERT INTO dbo.CustomerStocks (CustomerID ,StockID NoofStocks , UnitPrice)
VALUES (25, 1 , 10 2.00);

-- Display the rows in the table.
SELECT CustomerID ,StockID NoofStocks , UnitPrice, Amount
FROM dbo.CustomerStocks ;

SQL Server : User Defined Functions in SQL

User Defined Functions in SQL

User Defined Functions are routines which performs calculation or operation to return a value or result set. It can accept input parameters too.

Types of User Defined Functions in SQL

  1. Scalar Function – Returns a single data value of the type defined in the RETURNS clause. The return type can be any data type except text,ntextimagecursor, and timestamp
  2. Table-Valued Function – Returns a table data type. 

SQL Server : Identity vs UniqueIdentifier columns



Sl No
Identity column stores numeric values. Numeric value column have better performance in joins, indexes and conditions.
Range of values stored in identity column based on data type :
2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
-2^15 (-32,768) to 2^15-1 (32,767)
0 to 255
If table size is large, it is quite possible it will run out of it and after some numeric value there will be no additional identity to use.
Numeric values are easier to understand for application users if they are displayed.


Sl No
·        Unique across server
Uniqueidentifier column has string value i.e. {BAE7DF4-DDF-3RG-5TY3E3RF456AS10}. String values are not as optimal as integer values for performance when used in joins, indexes and conditions.
·        Since it is unique across server , allows easy merging of records from different databases
More storage space is required than INT
·        Allows easy distribution of databases across multiple servers
·        You can generate IDs anywhere, instead of having to roundtrip to the database
Most replication scenarios require GUID columns anyway

How can a column in SQL table made as Identifier column

IDENTITY  property

Identified column can be implemented using IDENTITY property. While specifying IDENTITY column, you specify SEED and ARGUMENT.


IDENTITY [ (seed , increment) ]

When inserting rows into table with IDENTITY column , SQL Server automatically generates next identity value by adding increment to previous identity value.


  1. Only one column in a SQL table can be defined with IDENTITY property.
  2. IDENTITY column can be defined on decimal , int , numeric , smallint , bigint or tinyint data type column.
  3. Default increment value is 1
  4. Identity value is assigned in ascending order by default
1. Create a table with IDENTITY column using create table script

EmpNumn int IDENTITY(1,1),
Name varchar (200),
Email varchar(100)

2. Identity column using SQL Server Enterprise Manager

SQL Server : How to create linked server

Create linked server : sp_addlinkedserver

sp_addlinkedserver ( Stored Procedure ) is used to created linked servers in SQL server

Syntax :

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ]
     [ , [ @location= ] 'location' ]
     [ , [ @provstr= ] 'provider_string' ]  
     [ , [ @catalog= ] ‘catalog’ ]  

If SQL Server, provider_name, data_source, location, provider_string, and catalog do not have to be specified.

How to retrieve information from SQL table in Linked SQL Server

Retrieve information from SQL table in Linked SQL Server

SELECT FROM Sql Statement can be used to retrieve information from SQL table in Linked SQL Server. We need to specify below in front of SQL table name :

1. Name of Linked Server
2. Name of Database
3. Database owner name

Example :

Employee table exists in EmpDB database in Linked Server named “EmpCorp”

Employee information can be retrieved by below Select SQL query :

FROM EmpCorp.EmpDB.dbo.Employee

FOR XML Clause – SQL SELECT statement

FOR XML Clause – SQL SELECT statement

SQL SELECT query fetches results as a rowset.  By specifying FOR XML clause in SQL Select query , one can retrieve results as XML instead of rowset. 
In a FOR XML clause, you specify one of these modes:
  • RAW – generates a single <row> element per row in the rowset
  • AUTO – generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified
  • EXPLICIT – allows more control over the shape of the XML
  • PATH – together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner

Example :

SQL Query with FOR XML clause with AUTO mode
SELECT TOP 1 EmployeeNumber
FROM Employee Emp
Query returns results in XML
<Emp EmployeeNumber="100"/>