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.

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

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


Example


select *


FROM information_schema.COLUMNS WHERE table_name = 'employee' -- Table Name


ORDER BY ordinal_position


Column Names returned using above query :


TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
COLUMN_DEFAULT
IS_NULLABLE
DATA_TYPE
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_PRECISION_RADIX
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME

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.

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 ;

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,ntext, image, cursor, and timestamp. 
2. Table-Valued Function - Returns a table data type. 

SQL Server : Identity vs UniqueIdentifier columns

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

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.

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

EXAMPLE :

1. Create a table with IDENTITY column using create table script

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

2. Identity column using SQL Server Enterprise Manager

What is use of Show Server Trace option in SQL Query Analyzer

Use Show Server Trace option in SQL Query Analyzer to analyze and tune stored procedures.

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 :

SELECT *
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
FOR XML AUTO

Query returns results in XML

<Emp EmployeeNumber="100"/>

SQL SERVER – Get Current TimeZone Name in SQL Server

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','SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT

SELECT @TimeZone

Escape Single Quotes in SQL

Escape Single Quotes in SQL

Single Quotes in Select , Insert and Update SQL statement can be escaped by adding another single quote in front.

Example :

1. Select 'My baby''s name is Naman';

Note : Notice extra single quote in baby''s

2. Insert into Employee ( Id , Name ) values ( 1,'O''Connor' )

Note : Inorder to insert name O'Connor in SQL table an extra single quote was added ( O''Connor )

Conditional Split in SSIS

Conditional Split in SSIS

This transformation lets you route your data flow to different outputs, based on criteria defined within the transformation’s editor.

What is default value of DelayValidation property in SSIS

The default value of DelayValidation property in SSIS is false

What does DelayValidation property mean in SSIS

DelayValidation Property

DelayValidation property indicates whether validation of the task is delayed until run time.

What are different task colour shown when SSIS package exceutes ? What each colour mean ?

Different task colours shown on task during SSIS package execution are :

1. Yellow - means task is executing
2. Green - means task has executed successfully
3. Red - means task has failed

What are various tabs in SSIS Package Design window ?

Various tabs in SSIS Package Design window :

1. Control Flow
2. Data Flow
3. Event Handlers
4. Package Explorer

How XML can be stored in relational database ?

How XML can be stored in relational database ?

There are three ways XML can be stored in relational database.

1. XML can stored as text . XML can be stored in text and varchar colums of table. But , it will lose much of value of XML representation. This can be used with any version of SQl server

2. XML can decomposed and stored in multiple relational tables. This alows use of relational technologies.

3. XML can be stored in XML datatype. XML datatype was introduced  in SQL Server 2000. Hence, this cannot be used in prior version of SQL 2005.

SQL Questions : What is normalization ?

What is normalization ?

Normalization is a method to seperate data into multiple tables and relate these multiple tables. Some benefits of normalized database :

1) Removes redundancy of data

2) Faster sorting

SSIS : What is difference between Control Flow and Data Flow in SSIS ?

Difference between Control Flow and Data Flow in SSIS are as follows :

1. The smallest unit in Control Flow is task whereas smallest unit in Data Flow is component
2. In Control FLow , Tasks require completion (success, failure or completion) before moving to next task. In Data Flow , one component will not wait for other component to finish, all of them will work together in processing and managing data in streaming way.