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,
            [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
  • Can you give a brief example of what the table looks like, including the column of interest?
    – datagod
    Jun 21 '11 at 16:47

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.