Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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