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 have two tables with 6 columns in 1st table and 6 in the second one. I have to combine the result of these two tables in order to make them in one set. Below are the structures of my tables:

CREATE TABLE [Table1]
    (
      [ID1] INT IDENTITY ,
      [COL1] INT ,
      [COL2] INT ,
      [COL3] INT ,
      [JobId] INT ,
      [DateCreated] DATETIME
    )

GO

CREATE TABLE [Table2]
    (
      [ID2] INT IDENTITY ,
      [COL4] INT ,
      [COL5] INT ,
      [COL6] INT ,
      [JobId] INT ,
      [DateCreated] DATETIME
    )    

GO

Dummy data to fill them:

GO
INSERT INTO dbo.Table1
        ( COL1, COL2, COL3, JobId, DateCreated )
SELECT s1.number % 10 + 25, 
s1.number % 10 + 27, 
s1.number % 10 + 29,
s1.number % 10 + 26,
dateadd(month, -1 * abs(convert(varbinary, newid()) % (90 * 12)), getdate())
    FROM master.dbo.spt_values s1
GO 200

For second table:

GO
--INSERT DUMMY DATA
INSERT   INTO [dbo].[Table2]
        ( [COL4], [COL5], [COL6], [JobId],[DateCreated] )

SELECT s1.number % 10 + 28, 
s1.number % 10 + 35, 
s1.number % 10 + 32,
s1.number % 10 + 44,
dateadd(month, -1 * abs(convert(varbinary, newid()) % (90 * 12)), getdate())
    FROM master.dbo.spt_values s1
GO 200

Now I have to run this type of query:

 GO
SELECT  ID1 ,
        0 AS ID2 ,
        COL1 ,
        COL2 ,
        COL3 ,
        0 AS COL4 ,
        0 AS COL5 ,
        0 AS COL6,
        DateCreated
FROM    dbo.Table1
WHERE   JobId = 2 

UNION ALL  

SELECT  0 AS ID1 ,
        ID2 ,
        0 AS COL1 ,
        0 AS COL2 ,
        0 AS COL3 ,
        COL4 ,
        COL5 ,
        COL6 ,
        DateCreated
FROM    dbo.Table2
WHERE   JobId = 50
ORDER BY DateCreated desc

The output of above query will be like this:

--ID1         ID2         COL1        COL2        COL3        COL4        COL5        COL6        DateCreated
------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------------------
--0           489833      0           0           0           34          41          38          2011-07-01 19:12:19.510
--0           464111      0           0           0           34          41          38          2011-07-01 19:12:18.760
--0           465587      0           0           0           34          41          38          2011-07-01 19:12:18.760
--0           465877      0           0           0           34          41          38          2011-07-01 19:12:18.760

To make the execution of query fast, I have applied following index:

CREATE NONCLUSTERED INDEX [IX_Table1_JobId] ON [dbo].[Table1] 
(
[JobId] ASC,[DateCreated] DESC
)
INCLUDE (
ID1 ,
COL1 ,
COL2 ,
COL3 ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

GO

CREATE NONCLUSTERED INDEX [IX_Table2_JobId] ON [dbo].[Table2] 
(
[JobId] ASC ,[DateCreated] DESC
)
INCLUDE (
ID2 ,
COL4 ,
COL5 ,
COL6 ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


GO

When I run the individual queries like :

SELECT  ID1 ,
        0 AS ID2 ,
        COL1 ,
        COL2 ,
        COL3 ,
        0 AS COL4 ,
        0 AS COL5 ,
        0 AS COL6,
        DateCreated
FROM    dbo.Table1
WHERE   JobId = 50 
ORDER BY DateCreated DESC

and :

SELECT  0 AS ID1 ,
        ID2 ,
        0 AS COL1 ,
        0 AS COL2 ,
        0 AS COL3 ,
        COL4 ,
        COL5 ,
        COL6 ,
        DateCreated
FROM    dbo.Table2
WHERE   JobId = 50
ORDER BY DateCreated desc

The order by column is covered by index and the sort operation is performed via my index only. But when I run the following query:

 GO
SELECT  ID1 ,
        0 AS ID2 ,
        COL1 ,
        COL2 ,
        COL3 ,
        0 AS COL4 ,
        0 AS COL5 ,
        0 AS COL6,
        DateCreated
FROM    dbo.Table1
WHERE   JobId = 50 

UNION ALL  

SELECT  0 AS ID1 ,
        ID2 ,
        0 AS COL1 ,
        0 AS COL2 ,
        0 AS COL3 ,
        COL4 ,
        COL5 ,
        COL6 ,
        DateCreated
FROM    dbo.Table2
WHERE   JobId = 50
ORDER BY DateCreated desc

The order by column is not covered under index and 90% of the query processing is taken by sort operation only. Can you tell me how can I fix this so that my sort column can be covered by index?

share|improve this question

migrated from stackoverflow.com Feb 13 '12 at 22:04

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 0 down vote accepted

If not an indexed view...

You'd need a third table that contains DateCreated and a key on an IDENTITY and a SubTable column that contains 1 or 2 only. And an index on DateCreated

This 2-column key becomes the key of the other 2 tables too. You use a CHECK constraint on SubTable to ensure an IDENTITY value is used in one table only.

Each bit of the UNION is a JOIN using Table3. Then the index spans all DateCreated values as expected.

The indexes you have now are per table: so SQL Server needs to sort the 2 sets of data.

share|improve this answer
    
Hi @gbn, Can you let me know the script of what you mentioned above? I am a bit confused with what you said. –  Rocky Singh Jul 1 '11 at 14:51

Your Answer

 
discard

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