Creating index on two columns

I have a SQL Server 2000 table with the following structure:
TABLE Personal (
UserID (PK),
Name,
Surname,
DateOfBirth,
Citizen,
BranchCode,
RegionCode,
Division
)
and I use the following stored procedure to do a SELECT which I use on
an asp page:
CREATE PROCEDURE dbo.SelectLevel1
AS
BEGIN
SET NOCOUNT ON
SELECT UserID, Name, Surname
FROM Personal
WHERE Citizen = 'Yes'
ORDER BY Surname ASC, Name ASC
END
I'd like to add an index to the Personal table to maximize the
performance of the SELECT statement.
What index should I create on this table?
My first thought was to index the Name and Surname columns, but how?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Andy,
Yes Surname and Name columns might be good ones to use. Is this a
commonly executed query? I would also look at placing an index on the
Citizen column - how selective is that column?
Your best bet is to try different indexing and look at how efficient
each of them are. Try three or four different approaches with Surname,
Name, Citizen.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> I have a SQL Server 2000 table with the following structure:
> TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
> and I use the following stored procedure to do a SELECT which I use on
> an asp page:
> CREATE PROCEDURE dbo.SelectLevel1
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
> END
> I'd like to add an index to the Personal table to maximize the
> performance of the SELECT statement.
> What index should I create on this table?
> My first thought was to index the Name and Surname columns, but how?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Citizen is not important. Surname and Name are the only ones that are
"selective".
Would it be better to create two indexes - one on the Name column and
another on the Surname column, or would one index on both columns be
better?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Andy,
My hunch is that you should create the index on Surname, Firstname, but
why not try it out and find out how many logical reads are required in
each case? You will know for sure which is best then.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> Citizen is not important. Surname and Name are the only ones that are
> "selective".
> Would it be better to create two indexes - one on the Name column and
> another on the Surname column, or would one index on both columns be
> better?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||On Fri, 21 Jan 2005 03:19:54 -0800, Andy wrote:

>I have a SQL Server 2000 table with the following structure:
>TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
>and I use the following stored procedure to do a SELECT which I use on
>an asp page:
>CREATE PROCEDURE dbo.SelectLevel1
>AS
>BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
>END
>I'd like to add an index to the Personal table to maximize the
>performance of the SELECT statement.
>What index should I create on this table?
>My first thought was to index the Name and Surname columns, but how?
Hi Andy,
That index would only be useful if you would make it the clustered index
and if you define the columns in the order (Surname, Name) - not the other
way aroound.
If it's a nonclustered index, simply scanning the table (or clustered
index), then sorting the rows with the correct Citizen value would be
faster than using the index on (Surname, Name), so SQL Server should
choose that execution plan.
It would be useful if you'd make it into a covering index. Assuming that
UserID is the clustered index (for the PK), your best index for this
specific query would be (Citizen, Surname, Name) - and (Surname, Name,
Citizen) would also be quite good.
If most of the rows in Personal have Citizen not equal to 'Yes', then you
might also gain by using an index on Citizen only. But only if this index
would actually discard almost all rows from your table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)