Indexing or changing one-to-many relationship I have these two Microsoft SQL Server 2005 tables:

ObjectLicenses (ObjectLicenseID [PK]; ObjectID; LicenseType; LicenseNumber)
1|1|A|000001
2|1|A|000002
3|1|B|000003
4|2|C|000004

Objects (ObjectID [PK])
1
2
3


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?
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.