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.

2 Answers 2

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

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.

share|improve this answer
    
I appreciate your response. Can you let me know how can I create indexed view wrt my code? I have written all the scripts needed to reproduce my problem –  Rocky Singh Jul 1 '11 at 14:18
    
@Rocky - I'm not going to write it for you, and they are tricky. Check out the article I linked at the top. You will need to use Schema Binding on your view as well. It may not be worth it to do this but it's the only method I know of to index across views. The really great thing about indexed views is you don't need to call them directly, as the analyzer should use them "behind the scenes" if they have data that can be used in a query. –  JNK Jul 1 '11 at 14:20

Your Answer

 
discard

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