SQL Server : duplicate key from creating unique index

Question

I have a table of stock quotes, and dates:

StockID QuoteID QuoteDay    QuoteClose
---------------------------------------
5         95    2018-01-03    1.080
5         96    2018-01-04    1.110
5         97    2018-01-05    1.000
5         98    2018-01-06    1.030
5         99    2018-01-07    1.010
5        100    2018-01-08    0.899
5        101    2018-01-09    0.815

I create a clustered index to manipulate the data but am running into duplicate key errors with the index

CREATE UNIQUE CLUSTERED INDEX MACD_IDX ON #TBL_MACD_LOOP (StockId, QuoteId)

Different combinations of StockID and QuoteID will result in the same output:

For example (StockID, QuoteID) of (5, 11) and (51, 1) both produce an index of 511.

My solution is to add "-" between StockId and QuoteId.

Now (5, 11) produces 5-11 and (51, 1) produces 51-1.

How do I combine strings with values?

The full error message you will include the values SQL Server finds as a duplicate key. I expect you will find there is more than one row when you run SELECT * FROM #TBL_MACD_LOOP WHERE StockID = <value1> AND QuoteID = <value2>; — Dan Guzman
– Dan Guzman, Commented Apr 29, 2018 at 20:50

marc_s · Accepted Answer · 2018-04-29 20:30:57Z

1

No, you are definitely mistaken.

The combinations for (StockId, QuoteId) of (5, 11) and (51, 1) are two DISTINCTLY different pairs of values.

They are NOT combined into a single value (of 511 as you assume) when creating the index entry. Those are two different values and therefore can co-exist in that table - no problem.

To prove this - just run this INSERT statement:

INSERT INTO #TBL_MACD_LOOP(StockId, QuoteId, QuoteDay, QuoteClose)
VALUES (5, 11, '20180505', 42.76), (51, 1, '20180505', 128.07)

Even with your unique index in place, this INSERT works without any trouble at all (assuming you don't already have one of these two pairs of values in your table, of course)

answered Apr 29, 2018 at 20:30

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Coding_Newbie Over a year ago

Since I'm getting the duplicate key error, what's the best way to check for duplicates?

marc_s Over a year ago

@Coding_Newbie: if inserting one pair of values fails - just run SELECT * FROM #TBL_MACD_LOOP WHERE StockId = xx AND QuoteId = yy and find your culprit

Collectives™ on Stack Overflow

SQL Server : duplicate key from creating unique index

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related