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
Best Answer
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.