covering indexes

In lot of atricles regarding sql server performacne i read about "consider
using covering indexes" .
my question is How do u create a covering index, can we create it explicitly
or
sql server creates it automatically?
Thanks
SriHi Sri
I'm a bit surprised that you read lots of articles that suggest creating
covering indexes, but you've never read one that told you what a covering
index is.
A covering index is one that includes ALL the columns from a table that are
used in a query. Normally, all that is important for an index to be used is
that the index keys are referenced in your WHERE clause, but with a
covering index, ALL the columns (from the SELECT list, the GROUP BY and any
other clause of your query) are part of the nonclustered index. Because the
covering index has everything needed to satisfy your query, SQL Server never
needs to access the actual data pages; it can stay in the index to retrieve
all the results.
So you need to examine your queries that aren't using any of your indexes,
and see if you can create one or more indexes that cover them. SQL Server
will not create these indexes automatically (unless you think of
automatically as following suggestions of the Index Tuning Wizard.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
> explicitly
> or
> sql server creates it automatically?
>
> Thanks
> Sri
>
>|||"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
explicitly
> or
> sql server creates it automatically?
>
Further to Kalens excellent reply, it does not have to be just one
non-clustered index. SQL can combine multiple non-clustered indexes to
cover the query.
Also the key to row ratio affects performance. If there are 2 columns
referenced in the query but 50 in the underlying table it will be faster
covering it than if there are 40 columns referenced in the query. Try to
avoid SELECT *.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004

Counting

Consider the following:
Table setup:
ID Message-ID References
1 IDA
2 IDB IDA
3 IDC IDA IDB
4 IDD
Given the above table I am trying to select the ID, Message-ID along with
the References count. So the following will work and get the desired
results its just slow. I have already indexed References for full text
searching so I would like to utilize that for speed. However I cannot
figure out how to run a count using the full text indexing for speed.
SELECT [ID], [Message-ID], (SELECT Count(ID) FROM tblArticles WHERE
[References] LIKE '%' + a.[Message-ID] + '%') AS ReferenceCount FROM
tblArticles a
Hi John,
Sorry for the late reply... I got side-track on other issues. While the
T-SQL LIKE works, a similarly constructed query for SQL FTS CONTAINS will
never work. Previously, you had posted this query as an example:
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
While the BOL is not specific on this issue (it is a DOC bug, IMHO), the
CONTAINS syntax is as follows:
CONTAINS
( { column | * } , '< contains_search_condition >'
)
Specifically, the contains_search_condition cannot be an aliased column as
in your above example - "a.[Message-ID]". However, what may work for you is
to split the query into two parts and use either a cursor or temp table to
store an intermediate results, for example:
set nocount on
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
CREATE TABLE #authors_PK (author_pk char(11))
OPEN keyword_cursor
DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sql = 'insert into #authors_PK (author_pk) select au_id from
authors where contains(*, ''' + @.keyword + ''')'
exec(@.sql)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
select * from #authors_PK
drop table #authors_PK
You should modifiy the above query's DECLARE CURSOR FOR to "SELECT [ID],
[Message-ID] FROM tblArticles" and then inside the WHILE loop run the
"SELECT Count(ID) FROM tblarticles WHERE CONTAINS ([References],
@.reference_variable)" query.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:joBmc.16541$jU.962253@.twister.southeast.rr.co m...
> Consider the following:
> Table setup:
> ID Message-ID References
> 1 IDA
> 2 IDB IDA
> 3 IDC IDA IDB
> 4 IDD
>
> Given the above table I am trying to select the ID, Message-ID along with
> the References count. So the following will work and get the desired
> results its just slow. I have already indexed References for full text
> searching so I would like to utilize that for speed. However I cannot
> figure out how to run a count using the full text indexing for speed.
> SELECT [ID], [Message-ID], (SELECT Count(ID) FROM tblArticles WHERE
> [References] LIKE '%' + a.[Message-ID] + '%') AS ReferenceCount FROM
> tblArticles a
>
>