0

I have a table containing an INT IDENTITY column, and a VARCHAR(10) column. I want to have a UNIQUE CLUSTERED INDEX on the IDENTITY column (so it's a primary key), and I also want to have a way of preventing duplicate values in the VARCHAR(10) column. However, the data should be ordered by the IDENTITY column in ascending order.

For example:

CREATE TABLE ref.currency (
    currency_id INT NOT NULL IDENTITY(1,1),
    currency_name VARCHAR(10) NOT NULL
);
GO
CREATE UNIQUE CLUSTERED INDEX ix_ref_currency_id ON ref.currency(currency_id);
GO
INSERT INTO ref.currency (
    currency_name
)
VALUES 
    ('Pounds'),
    ('Euros'),
    ('Dollars');

(I create a UNIQUE CLUSTERED INDEX instead of a PRIMARY KEY, as I was taught this, but didnt fully understand the reasons why one should be chosen over the other. I've stuck with it ever since.)

Question:

What type of index should I add to this table in order to prevent duplicate values being added to the currency_name column, that will not affect the order of the data?

I've tried adding a UNQIUE NONCLUSTERED INDEX, however this results in the data being ordered by currency_name, which I do not want.

Community
  • 1
  • 1
devklick
  • 2,000
  • 3
  • 30
  • 47
  • 2
    I would love to know who taught you that a unique clustered index is preferable over a primary key, and why. Internally there is very little difference but externally some tools (think ORMs) have problems when a primary key has not been defined. – Aaron Bertrand Apr 04 '19 at 22:00
  • 1
    Also, what do you mean by "the data being ordered"? Do you mean when you select data covered by the index and don't bother using an `ORDER BY` clause? Mechanically, SQL Server orders the data in order to easily spot duplicates (or find potential duplicates in an existing set). You can't create a unique index and say "order by random" or "order by other". Typically I would just create a unique constraint on that column. Internally that is the same as a unique index, but the difference is intent - constraints are used to enforce rules, indexes are used for performance. Generally, anyway. – Aaron Bertrand Apr 04 '19 at 22:03
  • @AaronBertrand, the guy has been a database developer for 30+ years, whereas I've only been working with databases for the past couple of years. I tend to take his advice as gospel. I'm not sure if his reasoning it's an oldschool thing, or personal preference. – devklick Apr 04 '19 at 22:05
  • You should share his reasoning and let others weigh in. Lots of old people are wrong about things from time to time. As proven by many famous and non-famous people, age/experience != intelligence. – Aaron Bertrand Apr 04 '19 at 22:13
  • @AaronBertrand so if I were to add a `UNIQUE CONSTRAINT` instead of some kind of index, I should be able to prevent duplicate values without affecting the default order. Or would this still affect the order? I know it shoudnt matter, but if it's at all possible, I'd rather try and achieve the desired default order. And yes, your right - experience is not always right. Would you suggest always using a `PRIMARY KEY` instead, because it's more compatible with ORM tools? – devklick Apr 04 '19 at 22:20
  • What “default order” are you talking/worried about? – Aaron Bertrand Apr 04 '19 at 22:30
  • @AaronBertrand When performing a select without an order clause, i.e. `SELECT * FROM ref.currency`, I'd like to see the data returned based on `currency_id ASC`, if possible (but still prevent duplicates in currency_name) – devklick Apr 04 '19 at 22:35
  • If you’d *like* to see the data ordered by a specific column, your query should say so. Period. Please read some SQL Server materials not written by your 30-yr veteran, who I think is giving you misconceptions about how the product works. [This answer](https://stackoverflow.com/a/26237450) has a good quote from Itzik about this specific ordering misconception. – Aaron Bertrand Apr 04 '19 at 22:38

0 Answers0