Index issue on order by column while applying Union all

Question

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?

Answer 1

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.

Answer 2

Create an indexed view that includes this query.

Besides an indexed view, there's no way I am aware of to index two tables, which is basically what you are asking to do.

The problem is that you have an index on date in table1, and in table2, but you are merging the results and asking SQL Server to order the combined results, for which there is no index.