Search on part of a column in a SQL Server cross-database view

Question

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

Answer 1

Are you allowed to replicate that table in another database? Using transactional replication, for latest data access. If yes, then I'd test a more cumbersome process like replicating the needed table, building another table or materialized view with a computed column as you need.

If replication is not an option, I'd say that you need to establish a scheduled process to take that table in your database and continue locally to process the data. Exactly like the first suggestion, but using your own scripts and schedule.

That, or I think you're stick to the scalar functions that aren't really the best option from performance point of view.

Answer 2

With a selection like this you could split your Description-varchar into a string and integer column:

SELECT SUBSTRING(Description,0,CHARINDEX('-',Description)) AS Building,
       CONVERT(INT, SUBSTRING(Description,CHARINDEX('-',Description)+1,LEN(Description))) AS RoomNumber
FROM   Room

I'm not sure what your final goal is, so i don't know if this really helps.