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?

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

Sample data:

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