Unique Index for firstname and lastname SQL Server

Question

I've been trying to search for awhile regarding how SQL Server handles unique indexes cause will need to create a table named user the table will have a column first_name and last_name our application will have a search feature which will generally search for first_name or last_name but I'm wondering if I define a unique index with first_name and last_name defined on it if I create a statement like

SELECT * 
FROM user_table 
WHERE first_name LIKE 'a%' OR last_name LIKE 'a%';

Would this statement still hit the unique index I made for which includes the first_name and last_name? Or would it be better to define individual indexes for each to optimize the search?

I think I found the answer from this link https://stackoverflow.com/questions/179085/multiple-indexes-vs-multi-column-indexes. — Christopher Pelayo, Jul 16 '18 at 03:10

score 0 · Accepted Answer · answered Jul 16 '18 at 03:32

0

If you have an index on

(first_name, last_name)

then this index might be used by SQL Server:

if you use first_name and last_name in your WHERE clause
if you use first_name only in your WHERE clause
but NOT if you use only last_name in your WHERE clause

The "compound" index can be used if the N left-most columns of that index are being used in the WHERE clause.

answered Jul 16 '18 at 03:32

marc_s

732,580
175
1,330
1,459

Thanks for clarifying that so if it easily hits index so I have to consider columns that might have most number of common values like last_name and next is first_name so I could take advantage of the index in case of searching for particular values where this columns will be included on the where clause. – Christopher Pelayo Jul 16 '18 at 03:50
I just review your answer and I think I understand it better now the thing is if the arrangement of my unique index goes as first_name, last_name if I search with first_name alone this would definitely use the index in other words in this arrangement if I want the last_name to be indexed as well I also have to declare another index for the last_name alone so that would hit 2 columns one with unique index and one with an individual index which is the last_name even I search alone for any it ensures hitting the index in the absence of any columns in the where clause. – Christopher Pelayo Jul 16 '18 at 14:14

score 0 · Answer 2 · answered Jul 16 '18 at 11:36

If you want to optimize the query, then I would recommend writing it as:

SELECT * 
FROM user_table 
WHERE first_name LIKE 'a%' 
UNION ALL
SELECT *
FROM user_table
WHERE last_name LIKE 'a%' AND first_name NOT LIKE 'a%';

This query can take advantage of two indexes on (last_name) and (first_name) -- you can include more columns in the indexes if you want.

That said, the index will probably not be very selective, so this might not be much improvement on performance.

Unique Index for firstname and lastname SQL Server

2 Answers2