June 10, 2015 at 3:19 am
Can we use a sql function() in create index as below is giving error , what would be work around if cannt use the function in below scenario
CREATE NONCLUSTERED INDEX [X_ADDRESS_ADDR1_UPPER] ON [dbo].[ADDRESS]
(
UPPER([ADDR_LINE_1]) ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
Thanks in advance
June 10, 2015 at 3:24 am
You can't. Indexes are created on columns, not expressions.
You can, if you need to do this, create a computed column and index that. However that will only be useful if the app can be changed to refer to the computed column.
btw, is your database case sensitive?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2015 at 4:15 am
Thanks for that quick responce.
I think DB will be case insenstive, as I am working on a migration project of oracle to sql, SSMA was not converting
the above Index in oracle which can use function in index. So i am looking some alternative way of creating index in that manner.
June 10, 2015 at 4:20 am
Computed column's the only way, but that'll be useless if the queries don't use the computed column. Also, don't assume that the DB is be case sensitive, check whether it is.
With default collations, SQL databases aren't case sensitive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply