Indexing in SQLServer

Question

Hi I am using SQLServer2008. I want to know what is index in SQLServer and how can i use it?

This is part of my query..how can i give index? Many Thanks..

DECLARE @TableMember TABLE
(
  BrokerId INT ,
  RankId INT ,
  MemberId INT ,
  InstallmentId INT ,
  PlanId INT ,
  IntroducerId INT ,
  Date DATETIME ,
  SelfAmount DECIMAL(18, 2) ,
  UnitAmount DECIMAL(18, 2) ,
  SpotAmount DECIMAL(18, 2) ,
  ORBPercentageSelf DECIMAL(18, 2) ,
  ORBPercentageUnit DECIMAL(18, 2) ,
  ORBAmountSelf DECIMAL(18, 2) ,
  ORBAmountUnit DECIMAL(18, 2) ,
  IsSelfBusiness BIT ,
  Mode VARCHAR(50) ,
  InstallmentNo INT ,
  PlanType VARCHAR(50) ,
  PlanName VARCHAR(50) ,
  CompanyId INT ,
  CscId INT ,
  Year VARCHAR(50) ,
  CreateDate DATETIME ,
  ModifideDate DATETIME
)

INSERT  INTO @TableMember
    ( BrokerId ,
      RankId ,
      MemberId ,
      InstallmentId ,
      PlanId ,
      IntroducerId ,
      Date ,
      SelfAmount ,
      UnitAmount ,
      SpotAmount ,
      ORBPercentageSelf ,
      ORBPercentageUnit ,
      ORBAmountSelf ,
      ORBAmountUnit ,
      IsSelfBusiness ,
      Mode ,
      InstallmentNo ,
      PlanType ,
      PlanName ,
      CompanyId ,
      CscId ,
      Year ,
      CreateDate ,
      ModifideDate  


    )
    ( SELECT    BrokerId ,
                RankId ,
                MemberId ,
                InstallmentId ,
                PlanId ,
                IntroducerId ,
                Date ,
                SelfAmount ,
                UnitAmount ,
                SpotAmount ,
                ORBPercentageSelf ,
                ORBPercentageUnit ,
                ORBAmountSelf ,
                ORBAmountUnit ,
                IsSelfBusiness ,
                Mode ,
                InstallmentNo ,
                PlanType ,
                PlanName ,
                CompanyId ,
                CscId ,
                Year ,
                CreateDate ,
                ModifideDate
      FROM      dbo.MemberBusiness AS mb
      WHERE     ( @CscId = 0
                  OR mb.CscId = @CscId
                )
                AND mb.Date >= @StartDate
                AND mb.Date <= @EndDate
                AND mb.RankId >= @FromRankId
                AND mb.RankId <= @ToRankId
    )

Answer 1

I would split this into two queries you don't want to try pulling all data or data from one id in the same stored proc.

( @CscId = 0 OR mb.CscId = @CscId)

The primary reason is you probably want a non-clustered index over CscID if you are looking for just say CscID = 104256 but if you are looking for all CscID you probably want an nonclusteredindexl over date column. I would also make sure you actually need a table variable it doesn't look from what you have like there is much of a good reason to toss one in randomly.

Answer 2

The example you have provided involves a "table variable". That means you are creating a Transact-SQL variable that can be used like a table in a SQL statement. You often don't need indexes on this sort of table because they are often small. But, if you do need an index, they can be created implicitly as the example shows below. You cannot create them explicitly. You can create a temporary table, however, and index that like any other table.

DECLARE @Employee TABLE
 (
     ID  INT PRIMARY KEY,
     NAME VARCHAR(50),
     UNIQUE (NAME,ID) -- ID is included to make the indexed value unique even if NAME is not
 )

Answer 3

First and foremost, an index allows a query to return results quickly. Most indexes provide a tree structure of some kind that allow a query to skip a lot of comparisons. Instead of checking each and every table row, the query checks whether a target value is greater or less than an index root value, then, if bigger, the query checks a bigger index entry, if smaller, it checks a smaller one, and so on. The beauty of this is that the query doesn't have to check many index entries before it finds out whether the target value exists, and, if so, where an occurance is in the data table.

It's sort of a "divide and conquer" strategy.

A developer or DBA tries to anticipate which table columns will be used in a lot of searches and creates indexes on those columns. The DB SW maintains the index. The DB adds and removes index entries as the underlying table is changed. The only thing the user should be aware of is faster response.

A simple index creation example would be

CREATE INDEX IX_EmployeeName ON EMPLOYEE(NAME);

Complete index creation syntax for Sqlserver 2008 R2 is available at

http://msdn.microsoft.com/en-us/library/ms188783(v=sql.105).aspx

Answer 4

I like this article http://www.mssqltips.com/sqlservertip/1206/understanding-sql-server-indexing/ I understood what is the difference between clustered and non-clustered from here

Answer 5

An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.

CREATE INDEX Example

The SQL statement below creates an index named "PIndex" on the "LastName" column in the "Persons" table:

CREATE INDEX PIndex
ON Persons (LastName)

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

Answer 6

Your index should be built depending on how your data is used. I would suggest reading this Indexing Best Practices as a start.