Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table :

[sch].[Parent]
(
     ID Identity [INT] (PK),
     Name VARCHAR(100),
     Email VARCHAR(250),
     Active TINYINT,
     InsertedBy VARCHAR(100),
     InsertedDTM DATETIME,
     UpdatedBy VARCHAR(100),
     UpdatedDTM DATETIME,
)

This table has PK on the ID column and a UNIQUE NONCLUSTERED CONSTRAINT on Name.

When I query a normal SQL without a WHERE clause:

SELECT ID, Name 
FROM sch.Parent 

This query uses a Index Scan and this is the case when I use a INNER JOIN with another CHILD table which has an FK in another table

What covering index should I use so that there is Index Seek in case of normal query and INNER JOIN with another table as well?

SELECT
     b.Id AS 'ParentsChildId'
    , b.[Name] AS 'ParentsChildName'
    , a.Id AS 'ParentId'
    , a.[Name] AS 'ParentName' 
FROM 
    [sch].[ParentsChild] b 
INNER JOIN 
    [sch].[Parent] a 
ON  a.Id = b.Id 
WHERE 
    a.Active = 1 AND b.Active = 1
share|improve this question
4  
Covering indexes cover queries. You haven't shown us the query with the Join. For the query without a WHERE you always get a scan as there is nothing to seek on. –  Martin Smith Dec 16 '12 at 15:53
    
SELECT b.Id AS 'ParentsChildId', b.[Name] AS 'ParentsChildName', a.Id AS 'ParentId', a.[Name] AS 'ParentName' FROM [sch].[ParentsChild] b INNER JOIN [sch].[Parent] a ON a.Id = b.Id WHERE a.Active = 1 AND b.Active = 1 –  SATISD9X Dec 16 '12 at 16:26
1  
you should add that query to your question, instead of leaving a comment. –  Max Vernon Dec 16 '12 at 17:39
    
In SQL server the primary key is selected as the clustered key if you don't say otherwise. There is no need to add another index to handle your query if the primary key is the clustered key. –  Mikael Eriksson Dec 16 '12 at 20:33
add comment

1 Answer

(I'm assuming SQL Server in this answer, please clarify which RDBMS you're using if it's not SQL Server)

The index you want is:

CREATE INDEX [IX_Parent_Id]
ON [sch].[Parent] (Id) INCLUDE (Name)

You use INCLUDE when a column isn't being filtered on (isn't in the WHERE clause) but is being selected. INCLUDE means that the column will only be included at the leaf level of the index (it's a small efficiency saving, essentially). Also as noted, if you have a SELECT statement without a where clause, indexes will never be used (faster to just scan the table rather than muck about with an index). Index seek is only used when you're returning a comparatively small number of rows, for your sample query if most of your ParentsChild and Parents rows are marked as Active then the optimiser may well decide to index scan instead of seek. A query that would be more likely to get an index seek instead of scan would be something like:

SELECT
     b.Id AS 'ParentsChildId'
    , b.[Name] AS 'ParentsChildName'
    , a.Id AS 'ParentId'
    , a.[Name] AS 'ParentName' 
FROM 
    [sch].[ParentsChild] b 
INNER JOIN 
    [sch].[Parent] a 
ON  a.Id = b.Id 
WHERE 
    a.Active = 1 AND
    b.Active = 1 AND
    a.Id = 2

(getting the children of Parent ID 2)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.