Sort order in a unique constraint
-
Good morning,
I have a question about the meaning of "sort order" in a Unique Constraint". If the constraint is non-clustered, is there any meaning to setting the sort order of each column in the constraint?
Here is the constraint I am trying to understand:
ALTER TABLE [dbo].[tTradeData] ADD CONSTRAINT [Unique_Trades] UNIQUE NONCLUSTERED
(
[iTransactionNbr] ASC,
[sSecurityID] ASC,
[iTradeType] ASC,
[dTradeDate] ASC,
[nTradeAmount] ASC,
[sTraderName] ASC
) ON [PRIMARY]
The purpose of the constraint is to prevent our vendor's data feed from entering duplicate trade data. If the sort order is actually meaningful, it seems that a descending sort would make more sense (Recent transactions have a higher transaction number). But if the constraint is nonclustered (because the primary key is already clustered), what difference does the sort order make.
Thanks for your ideas,
Elliott
-
Constraints in MS SQL Server are enforced by using indexes. With Sort order you tell SQL how do you want the index created. Unfortunately I see no reason other than explicit documetation to specify "ASC" because it is the default. This techique is useful whe you need two indexes on the same column(s) but with different sort orders.
On the other hand what is really important in composite indexes is the order of the columns!!
Cheers,
* Noel -
Noel, thanks.
We have the correct ordering of the columns, I was just wondering if changing the sort order to Desc would speed up the insertion process because the current transactions will have data at or near the top of the range.
But, i'm not clear on how the data engine would use the sort order. If every existing value of the subject columns must be "read" to assure that the candidate insert is truly unique, then how does a sort help?
Elliott
-
The sort order is used on insertion only to determine existence! All values are not read because the index data is saved in BTree format. If you had no index then all rows have to be searched (that's called a table scan).
The index helps in identifiying quickly the precence of a possible duplicates but the main purpose of the index is for "reads" not for "writes".
There are some cases in which descending order help to speedup "reads"
Cheers,
* Noel -
Got it. thanks.
I'll probably try changing the sort to descending, since the values are almost always going to be at that end of the range.
Elliott
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply