-1

If I have a UNIQUE index over multiple columns like this:

CREATE UNIQUE INDEX idx_name ON Tablename(column1, column2, column3)

When I run this SQL statement:

SELECT *
FROM Tablename 
WHERE column1 = 'example'

My question: does the index for WHERE work in this example? Or must a create another index?

OR the same with JOIN if I have:

SELECT * 
FROM Tablename 
JOIN Tablename2 ON Tablename.column1 = Tablename2.othercolumn

Is this column now index at the right way for JOIN and WHERE?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sametcey
  • 141
  • 1
  • 6
  • 9
    SQL Server will answer your question in the *execution plan*. Are these the only 3 columns in the table? If not because of your `select *` whether the optimizer decides to use your index will depend on the size of the table, the data cardinality and the cost estimate of a seek with n lookups vs cost of a scan. – Stu Aug 10 '23 at 14:06
  • If your table is large enough for the query optimizer to use the index, it will use it with a query like this. – Zohar Peled Aug 10 '23 at 14:08
  • 1
    Here is a great place to start. https://www.sqlservercentral.com/stairways/stairway-to-sql-server-indexes – Sean Lange Aug 10 '23 at 14:09
  • yes i cannot find a answer because, i put three column to one index, an on the where or Join i only Join one of the Column. So my question is did the Index than work right? – Sametcey Aug 10 '23 at 14:12
  • *"So my question is did the Index than work right?"* The index work to stop duplicate values, yes. If it's used to help the `SELECT` isn't a question we can answer with the information you've given; we don't have the definition of your table. This is why it was suggested you look at the execution plan; SQL Server *will* tell you if that index is helpful or not for that query, as it either will or won't be using it. – Thom A Aug 10 '23 at 14:21
  • 1
    If you have a small table it will very likely ignore the index because a scan won't be any slower. – Sean Lange Aug 10 '23 at 14:29

0 Answers0