I'm currently writing an application that interfaces with a 3rd party vendor's SQL Server 2008 database that I only have read access to. We have a separate database that contains cross database views and stored procedures into the vendor's database for our custom applications.
One of the columns contains building and room data like this: DON-203, DON-303, SGRUE-102, EVN-1010
Basically, it's a set of letters (that indicates the building) followed by a hyphen followed by digits (which indicates the room number). I need to perform queries on just the room number (numeric) portion of the column.
- I can't use a precomputed column in the table, since I don't have write access to the original database.
- I can't create triggers in the original database
- Scalar functions will return the correct results, but seems incredibly inefficient for large datasets that will be queried very frequently
- As far as I know, I can't use an indexed view, since the view refers to a table in a different database.
- New buildings can be added to the program at any time, so I can't hardcode a list of all the building prefixes somewhere. They're also not stored anywhere.
Is there a decent way to handle this situation?
EDIT: Here's a sample schema (with all irrelevant data removed)
CREATE TABLE [dbo].[Room] ( [RoomID] [int] IDENTITY(0,1) NOT NULL, [Description] [varchar](30) NOT NULL, CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED ( [RoomID] ASC ) )
There is a non-unique index on Description in ascending order
RoomID Description 0 DON-101 1 DON-102 2 DON-103 ... 118 DON-427 119 DON-428 ... 124 EVN-107 125 EVN-108 ... 511 NGRI-1007 512 NGRI-1008 ... 564 NGRI-227 565 NGRI-228