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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have these two Microsoft SQL Server 2005 tables:

ObjectLicenses (ObjectLicenseID [PK]; ObjectID; LicenseType; LicenseNumber)

Objects (ObjectID [PK])

Table ObjectLicenses has a primary key on column ObjectLicenseID. Table Objects has a primary key on column ObjectID.

I want to return a list of all LicenseType's, by Object ID. However, this takes a long time, possibly due to lack of index(es)...currently, I use a "FOR XML PATH" query to do this:

SELECT      O.ObjectID

            ,STUFF( (SELECT      '; ' + 
                                 CONVERT(VARCHAR(12), ROW_NUMBER() OVER 
                                 (ORDER BY OL.ObjectLicenseID)) + 
                                 ') ' + ISNULL(OL.LicenseType, '')

                       FROM      ObjectLicenses OL

                      WHERE      O.ObjectID = OL.ObjectID
                      FOR XML PATH(''), TYPE
                    ).value('.', 'VARCHAR(MAX)')
                    , 1, 2, ''
                  ) AS 'LicenseType'

  FROM      Objects O

How might I index or otherwise change this model/query to speed it up?

share|improve this question
Is there any reason you need both tables involved in the query? Seems to me you could avoid some operations by just querying against ObjectLicenses (unless you also want to expose cases where an Object has no licenses assigned). Also, what indexes are on the ObjectLicenses table (and no, the foreign key doesn't count). – Aaron Bertrand Jun 28 '12 at 20:04
Column ObjectLicenseID represents the primary key and the only index on table ObjectLicenses. How might I get the desired results (for example, ObjectID|LicenseType = 1|A, A, B) without referencing table Objects? – iokevins Jun 28 '12 at 22:50
Well, ObjectID exists in ObjectLicenses. Why exactly do you need to go to Objects to get it? – Aaron Bertrand Jun 28 '12 at 22:51
Aaron, please excuse my denseness: do you mean to suggest changing the reference to table Objects to table ObjectLicenses and adding a GROUP BY ObjectID clause? Or does MSSQL have a way to get the same result without the outer FROM clause and the inner WHERE clause? Thanks! – iokevins Jun 28 '12 at 23:20

The relationship seems fine; the problem seems to be in the presentation. If you want this query to work faster, just return the join to the client, and perform the looping / concatenating on the client.

This sounds like a tongue-in-cheek answer, but I'm 100% serious. Why does SQL Server have to do the work of transposing your rows to a concatenated column? Your client language is certainly much better equipped to do that (loop through the joined results, ordered by ObjectID, and start a new concat whenever a new ObjectID is encountered). It may be less efficient at the very high end (say, thousands of license types per ObjectID) but I suspect you're not at an edge case.

share|improve this answer
Thanks for thinking out-of-the-box. We currently feed the SQL Server results directly to end users via Microsoft Access ODBC links, for simplicity. So, no query post-processing occurs. Long-term, we plan to migrate this data to a ASP.NET web app, which would allow this. – iokevins Jun 28 '12 at 18:37

Your Answer


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.