0
CREATE NONCLUSTERED INDEX IX_AgentRegistration_Indx2 
ON [AgentRegistration] ([AgencyType], [IsFIT], [IsActive]) 
INCLUDE ([AgencyCode], [AgencyName])
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
          IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
          ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [SECONDARY_NEW]

The second one

CREATE NONCLUSTERED INDEX IX_AgentRegistration_Indx3 
ON [AgentRegistration] ([ExchAgentCode])
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
         IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
         ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [SECONDARY_NEW]
Veer
  • 1,575
  • 3
  • 16
  • 40
Sangam
  • 34
  • 4
  • 1
    And what exactly is the question?? Where's the `INCLUDE` in the second query??? – marc_s Sep 10 '13 at 05:33
  • Read [Create Indexes with Included Columns](http://technet.microsoft.com/en-us/library/ms190806.aspx) for an intro to what included columns are used for – marc_s Sep 10 '13 at 05:38
  • @Sangram Please edit the question and tell us more about it, I'm sure you will get a proper answer. – Vishwanath Dalvi Sep 10 '13 at 05:57
  • @mr_eclair Include is there in First Query and WITH is in second query now Hopefully you would have got what I meant – Sangam Dec 02 '13 at 13:02

1 Answers1

1

If you are looking for what the INCLUDE keyword means in the index creation, here is a short explanation:

  • if you create a composite index, then your index will contain both values on all levels of the index b-tree; this means, the query analyzer will also have the chance to use both values when making decisions, and this can support queries that specify both columns in a WHERE clause

  • if you create an index on one column and only include other columns, then your index will contain only indexed column values on all levels of the index b-tree, and only on the leaf level, the "last" level, there will also be the values of included columns. The included column values cannot be used in selecting the data - they're just present at the index leaf level for lookup.

Hope that helps.

Rajan

Raj
  • 10,653
  • 2
  • 45
  • 52