CODEWITHSUNDEEP
sql-server-2008-r2indexing
Peter
Peter

Reputation: 351

SQL Server index performance

I'm interested in databases and have started to play around with SQL Server 2008, I've read that using appropriate Indexes on tables can help to improve the overall performance of the database.

I have two tables and auto generated 1 million rows in each table using SQL Data Generator, Table 1 is a customers table and Table 2 is a renters table, the designs are as follows:

Customer                                    Renters
CustomerID (PK)                             RentersID (PK)
ForeName (Non clustered index)              StartDate
SurName                                     EndDate
Email                                       RentalNights
                                            CustomerID (FK) (Non Clustered index)

I've read that placing a non clustered index on the most commonly used columns as well as foreign key columns will help to improve performance. I created a simple join query before using indexes and after using indexes, but for me I can't seen the increased performance when using indexes, can any body help me out? The images below are the execution plans before indexes and after using them.

Before Indexes: enter image description here

After Indexes: enter image description here

EDIT: this is the SQL syntax i am using 

SELECT cu.ForeName + ' ' + cu.SurName AS 'Name' 
FROM dbo.Customers cu
INNER JOIN dbo.Renters re ON re.CustomerID = cu.CustomerID
WHERE cu.ForeName = 'Daniel'

EDIT This is my index syntax using the ones posted in the reply below:

CREATE NONCLUSTERED INDEX [ix_Customer] ON [dbo].[Customers] ( [ForeName] ASC, [CustomerID] ASC ) INCLUDE ( [SurName]) 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) ON [PRIMARY] GO

Upvotes: 0

Views: 1894

Answers (1)

JNK
JNK

Reputation: 65147

Based on your query the best nonclustered indexes to build would be:

CREATE NONCLUSTERED INDEX ix_IndexA on dbo.Customers (Forename, CustomerID) 
     INCLUDE (SurName)

CREATE NONCLUSTERED INDEX ix_IndexB on dbo.Renters (CustomerID)

You want your key fields to be on your filter or JOIN columns, and your INCLUDE columns are at the leaf level to get returned in the SELECT.

Upvotes: 4

Related Questions