SQL Server - Filtered Index

Summary on Filtered Index:

Filtered Index is used to improve performance of an query by applying filter on index thereby reducing maintenance and storage costs associated with index

Examples:

Create two Non-Clustered index one with filtered index and other without, as shown below:
 
CREATE NONCLUSTERED INDEX INDX ON Employee(EmpID) 


Creates an Non-Clustered Index on EmpID column within table Employee

CREATE NONCLUSTERED INDEX INDX2 ON Employee(EmpID)
WHERE Title = 'Manager'


Creates an Non-Clustered Index on EmpID column within table Employee with Filtered Index applied on Title column


Execution Results:


SELECT EmpID, Title FROM Employee WITH (INDEX(INDX))
WHERE Title = 'Manager'

 SELECT EmpID, Title FROM Employee WITH(INDEX(INDX2))
WHERE Title = 'Manager'

Drawing out an execution plan shows Query Cost associated with first select query is 54% and one with the second select query is 46% which depicts significant improvement using
filtered index
 

SQL Server - Precision and Scale for Numeric or Decimal DataType

Difference between Precision and Scale for Numeric or Decimal Datatype in SQL Server

Precision (P) - Precision signifies total number of digits which a Numeric OR
                     Decimal datatype can hold, 18 is the default precision

Scale (S) - Scale signifies number of digits to the right of Decimal (.) operator, scale varies
                from 0-n (where n - number defined by us)

Syntax - Numeric (Precision, Scale) OR Decimal (Precision, Scale)

E.g.  Numeric(38,2) : it says Numeric datatype can hold total of 38 digits
       of which 2 digits to the right of decimal (.) and remaining digits to the left of
       decimal (.)
       Decimal(14,2): it says Decimal datatype can hold total of 14 digits
       of which 2 digits to the right of decimal (.) and remaining digits to the left of
       decimal (.)      

SQL Server - Restrict number of rows retuned by SQL Server

Command to restrict number of rows returned by SQL Server

SET ROWCOUNT 100

Run above mentioned command in your SQL Query Analyzer, after which any SELECT
command executed will only return 100 rows

To reset the ROWCOUNT use below command

SET ROWCOUNT 0

Above command will make SQL Server to return all rows

SQL Server - Use of Identity

How to find identity values in SQL Server

1] Identity_Scope(): Returns last identity value inserted in the table under current session
    and scope i.e. it will return the last identity explicitly created on any table within current
    session, but if an trigger is enabled on that table which in turn inserts an identity in
    some other table Idenity_Scope() function won't trace the later inserted identity 

2] @@identity: Returns last identity value inserted in the table under current session but
    not restricted to current scope i.e. if an identity is explicitly created on a table which
    has an trigger enabled on it which in turn inserts an identity in other table,
    @@identity function will return the latest identity insert caused by trigger

3] Identity_Current('table_name'): Irrespective of the current session and scope,
    Identity_Current('table_name') will return the last identity inserted in the table
    mentioned,no matter what the current scope and session is. One point worth
    mentioning here is table name always needs to be enclosed within single quotes ('')

 How to explicitly reset identity values of a table

     DBCC CHECKIDENT (tablename, RESEED, 0) command can be used to reset any
    identity value within a table to zero, any identity inserts performed thereafter will
    start with 1

     for e.g.

    DBCC CHECKIDENT (tablename, RESEED, 15) command will reset the identity value
    to 15, so any identity getting inserted after that will start at 16 

SQL Server - Difference Between Truncate and Delete Statement

Truncate vs Delete Stmt

1. Truncate stmts are faster when compared with Delete stmts
2. Transaction logs are not maintained by truncate stmts whereas transaction logs are 
    maintained by delete stmts which makes delete operation slower when compared to
    truncate operation  
3. Filter conditions are applicable to delete stmts, below stmt is the correct syntax:
  
    DELETE from (tablename) t
    WHERE  t.name = 'abc'

    Filter conditions aren't applicable to truncate stmts, below stmt is incorrect:

    Truncate table (tablename)
    WHERE  (tablename).name = 'abc'

4. Triggers are invoked on DML stataments, for which delete stmts can have an trigger
    invoked on it whereas truncate stmts cannot

5. Truncate stmts will always reset the seed value of an identity column to 1, whereas
    delete stmts will maintain the last identity value of an identity column within the table 

Architecture of SSIS

As discussed in my earlier blog i hope by now we all have an brief idea about SSIS tool, lets have an quick look on SSIS Architecture:

Introduction to SSIS

Brief Summary:

SSIS Stands for SQL Server Integration Services, a product delivered by Microsoft. SSIS basically is an ETL (Extract, Transform, Load) application. ETL signifies extracting data from different sources which can be an SQL data source, excel source, flat file source, xml source etc.., after data extraction comes performing some transformations on the extracted data which means applying business logic involved within any project.., and finally come loading the cleansed data into different destination which can again be an SQL Server destination, excel destination, flat file destination or xml destination. 

License for SSIS:

No seperate license is required for SSIS which is an added advantage why SSIS can be preferred over any other ETL application, but ofcourse one should hold an licensed version of Microsoft SQL Server to avail the benefits of MSBI tools (SSIS, SSRS and SSAS) which comes along with Microsoft SQL Server