CODEWITHSUNDEEP
sql-serverperformance
Royi Namir
Royi Namir

Reputation: 148524

Putting clustered index on a join used column vs heavily scanned column?

I have this simple table :

Table Users

userId    |  name    
--------------------- 
 1             'a1'   
 2             'a2'    
 3             'a3'    
 4             'a4'    
 5             'a5'

Table Cities

cityId    |  name    
--------------------- 
 1             'c1'   
 2             'c2'    
 3             'c3'    
 4             'c4'    
 5             'c5'

Each user is can be in more than one city. :

So the mapping table is : 

userId    |  CityId    
------------------------------------
 1             4    
 1             4    
 1             4    
 2             5    
 5             6

Table users is heavily scanned by name .

Question :

For the mapping table I have no issues. both columns together are primary/clustered index.

But i'm struggling with myself about the first 2 tables :

(leave aside the unique problem. lets say all columns are unique)

What is the best practice decision for this case ?

Upvotes: 1

Views: 129

Answers (1)

Yan Brunet
Yan Brunet

Reputation: 4887

The best decision depends on how exactly you use the data returned by a query.

A clustered index means that the data in the page files are ordered based on this index.

A regular index will have it's own page files to order the index and a pointer to the physical row.

Thus a clustered index will serve better for theses queries that return a range of value instead of unique rows.

So, unless you do a lot of queries with like operations on the Name column, you would be better to keep your clustered index on the ID column, for this index will be constantly scanned and used to return recordsets to support your join operations.

Upvotes: 2

Related Questions