sqlServer Index issue
I have an index we use for Oracle as follows:
CREATE UNIQUE INDEX X_AK9_SE30
ON S_ACCOUNT_E30 (to_char(ACCOUNT_NUMBER))
I cannot seem to construct a similar index for sqlServer. Can someone guide me.
ACCOUNT_NUMBER is defined as BIGINT NOT NULL.
Note, we already have an index on the Account_number as a bigint.
but the customer wants to search to work as if the number is a character (which works a bit differently).
[467 byte] By [
taaSarge] at [2007-11-11 8:49:13]
# 1 Re: sqlServer Index issue
Create a calculated column where you cast ACCOUNT_NUMBER to CHAR. Then create an index on the calculated column, and search against that.
Rune
# 2 Re: sqlServer Index issue
yes that was a solution we had implemented on our branch but when merging the change to the Main code base our Oracle DBA suggested the solution with the index. It works great for Oracle but I cannot get sqlServer to create then casted index...
# 3 Re: sqlServer Index issue
Nope, there's no such thing in SQL Server. As an aside, in my opinion the original solution is a misuse of the BIGINT data type. ACCOUNT_NUMBER should very likely be char to begin with. After all, when was the last time you calculated the sum of two account numbers?
Rune
