Creating a Reverse Index on MS SQL Server

Question

Creating a Reverse Index on MS SQL Server

caffeine

SSC Veteran

Points: 229
More actions
April 19, 2011 at 2:52 pm

#249564

Hello all,
Newbie here. I have a MS SQL query that looks at the last 2 characters of a LoanID field:
[font="Courier New"]SELECT *
FROM Loans_Chars
WHERE LoanID Like '%17'[/font]
So I'd like to create an index of the LoanID in reverse to speed up the query. Possibly even an index of only the first two characters of the reversed LoanID. This works (of course):
[font="Courier New"]CREATE NONCLUSTERED INDEX INDEX_LOANID_REVERSED
ON Loans_Chars
( LoanID )[/font]
However, I can't get MS SQL to accept the reversed field:
[font="Courier New"]CREATE NONCLUSTERED INDEX INDEX_LOANID_REVERSED
ON Loans_Chars
( REVERSE(LoanID) )[/font]
MS SQL says there is a syntax error around "(LoanID" right after the REVERSE statement, and there's a pop-up window that says "Incorrect syntax near 'LoanID'. Expecting SELECT, or '('"
How can I build an index of the reversed LoanID column?
Is it possible to index just the first two characters of the reversed LoanID?
Thanks!
Pete

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

First you need to add a computed column to the table, expression being RIGHT(LoanID,2). Then you can index that computed column.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

caffeine SSC Veteran Points: 229 More actions · Answer 2

Babylon 5 was the only TV show that made me weep. The episode when Sheridan died was ... heart breaking... 🙂

Did it. Hooray! Thank you!!!! 🙂

Pete 🙂

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

caffeine (4/20/2011)
Babylon 5 was the only TV show that made me weep. The episode when Sheridan died was ... heart breaking... 🙂

Z'ha'dum or Sleeping in Light?

Z'ha'dum is one of my favourites for use of music and silence and emotion. The music leading up to Sheridan's jump, the silence immediately after followed shortly by G'Kar's poignant words...

Did it. Hooray! Thank you!!!! 🙂

My pleasure.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

caffeine SSC Veteran Points: 229 More actions · Answer 4

Sleeping in Light. After such monumental changes -- wars, deaths, politics, love -- Sheridan and Delenn felt like such a unit. One couldn't exist without the other. They were such an awesome couple, and the actors and JMS did a great job in creating a believable love and bond. When he left and you see Delenn in the empty bed, rolling over to his side and clutching his pillow... I'm getting a little choked up even thinking about that scene!

I think Bab5 might be the best sci-fi series ever. To me, it shows science fiction done right. As an art form, not just a fun/interesting series of unrelated episodes.