Category: SQL Server Articles

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 :

• 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.

CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

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.

FACTS :

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 ;

IDENTITY vs UNIQUEIDENTIFIER COLUMN

IDENTITY

Sl No	Advantage	Disadvantage
1	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 : bigint 2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) smallint -2^15 (-32,768) to 2^15-1 (32,767) tinyint 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.
2	Numeric values are easier to understand for application users if they are displayed.

UNIQUEIDENTIFIER

Sl No	Advantage	Disadvantage
1	·        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.
2	·        Since it is unique across server , allows easy merging of records from different databases	More storage space is required than INT
3	·        Allows easy distribution of databases across multiple servers
4	·        You can generate IDs anywhere, instead of having to roundtrip to the database
5	Most replication scenarios require GUID columns anyway

IDENTITY  property

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

SYNTAX :

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.

FACTS :

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

CREATE TABLE Employee
(
 EmpNumn int IDENTITY(1,1),
 Name varchar (200),
 Email varchar(100)
);

2. Identity column using SQL Server Enterprise Manager

Create linked server : sp_addlinkedserver

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

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 :

SELECT *
FROM EmpCorp.EmpDB.dbo.Employee

FOR XML Clause – SQL SELECT statement

• 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 :

SELECT TOP 1 EmployeeNumber
FROM Employee Emp
FOR XML AUTO

Query returns results in XML

<Emp EmployeeNumber="100"/>

SQL SERVER – Get Current TimeZone Name in SQL Server

Use below sql script to get timezone of sql server :

DECLARE @TimeZone VARCHAR(50)

EXEC MASTER.dbo.xp_regread ‘HKEY_LOCAL_MACHINE’,’SYSTEMCurrentControlSetControlTimeZoneInformation’,
‘TimeZoneKeyName’,@TimeZone OUT

SELECT @TimeZone

Escape Single Quotes in SQL