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

I have this simple table :

Table Users

  • SQL Server Login error: Login failed for user 'NT AUTHORITY\SYSTEM'
  • SSIS 2012 join table
  • Why is an index created more than one time in SQL via Entity Framework
  • Using If else in SQL Select statement
  • Create stored procedure with parameters which it has null values
  • How to use SQL Server stored procedures in Microsoft PowerBI?
  • 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 :

    • I think that Users should have userId column as primary key. why ? because it is used throug the join to the mapping table.
    • but I also need clustered index on the name column cause this table is heavily scanned by name.

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

    What is the best practice decision for this case ?

  • The backend version is not supported to design database diagrams or tables
  • Watch for a table new records in sql database
  • Load very big CSV-Files into s SQL-Server database
  • Is this date comparison condition SARG-able in SQL?
  • SQL Server Update Trigger, Get Only modified fields
  • Parts of stored procedure just not running
  • One Solution collect form web for “Putting clustered index on a join used column vs heavily scanned column?”

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.