May 22, 2019 at 5:53 pm
I have a UNION select that I would like to convert to a JOIN so I can create an indexed view on it.
Tables for an example:
create table T1(ID INT NOT NULL, col1 int NOT NULL, col2 int NOT NULL, col3 nvarchar(30))
create table T2(ID INT NOT NULL, col1 int NOT NULL, col2 int NOT NULL, col3 nvarchar(30))
INSERT INTO T1 (ID, col1, col2, col3) VALUES
(1,100,200,NULL),(1,101,212,NULL),(1,102,224,NULL),(1,103,236,NULL),
(1,104,248,NULL),(1,105,260,NULL),(1,106,272,NULL),(1,107,284,NULL),
(1,108,296,NULL),(1,109,308,NULL),(1,110,320,NULL),(1,111,332,NULL),
(2,100,201,NULL),(2,101,213,NULL),(2,102,225,NULL),(2,103,237,NULL),
(2,104,249,NULL),(2,105,261,NULL),(2,106,273,NULL),(2,107,285,NULL),
(2,108,297,NULL),(2,109,309,NULL),(3,100,200,NULL),(3,101,212,NULL)
INSERT INTO T2 (ID, col1, col2, col3) VALUES
(1,150,500,'abcde'),(2,150,500,'fghijk'),(3,150,500,'1234dsaf')
The query I would like to use as a basis for an indexed view (but can not because of the union) is:
SELECT ID, col1, col2, col3
FROM T1
UNION
SELECT ID, col1, col2, col3
FROM T2
Thanks in advance.
May 22, 2019 at 7:10 pm
There's no way I know of doing this.
Your likely alternatives would appear to be
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 22, 2019 at 7:31 pm
I don't see how this is possible. UNION is a combination of rows, provided that order and number of columns are the same and have compatible datatypes. JOIN is more like combination of columns, assuming that you want columns from both tables. So, if you do something like LEFT JOIN you'll get completely different resultset.
--Vadim R.
May 27, 2019 at 3:11 pm
SQL Server creates an hidden table for an indexed view. To duplicate that try creating a trigger on each of the tables that inserts or deletes rows in another table to maintain the data reflecting the results you would get from the union query.
May 28, 2019 at 11:23 am
I don't see how this is possible. UNION is a combination of rows, provided that order and number of columns are the same and have compatible datatypes. JOIN is more like combination of columns, assuming that you want columns from both tables. So, if you do something like LEFT JOIN you'll get completely different resultset.
No problem there see the solution below.
It is possible to 'convert' a UNION into a JOIN, see below, but this construction does not allow an indexed view either.
Maybe it is possible to form a SELECT which gives the same result AND can be used as an indexed view.
But there is a large number of limitations on indexed views and most are quite logical, so conversion of the select statement to another shape, does not 'change' the logic which prevents the indexed views.
--------------------------------------------------------------------------------------
-- ben brugman
-- 20190528
--
--------------------------------------------------------------------------------------
-- Both give the same result, both can not be indexed.
-- Query 1:
--
select * from T1
union
select * from T2
--------------------------------------------------------------------------------------
--
-- Query 2 (Avoiding a UNION) :
--
;WITH
L0 AS(SELECT 0 AS c UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), -- 4
L1 AS(select 0 as x from L0 A, L0 B, L0 C, L0 D), -- 4 ^4 = 256
L2 AS(select 0 as x from L1 A, L1 B, L1 C, L1 D), -- (4 ^ 4) ^4 = 4 Giga
L9 AS(Select row_number() OVER(PARTITION BY 1 order by x ) as P from L2) -- voeg rijnummers toe
, A AS(SELECT row_number() over (partition by 1 ORDER BY (SELECT (null)) )*2-1 AS R1, * FROM T1)
, B AS(Select row_number() over (partition by 1 ORDER BY (SELECT (null)) )*2 AS R2,* from T2)
, C AS(select P, coalesce (A.id, B.id) id, coalesce(A.col1, B.col1) col1,coalesce(A.col1, B.col1) col2,coalesce(A.col1, B.col1) col3 FROM
(l9 LEFT OUTER JOIN A ON A.R1 = P )
LEFT OUTER JOIN B ON B.R2 = P WHERE p<1000)
SELECT id,col1, col2, col3 FROM C where col1 is not null
--
-- P<1000 can be adjusted, the query performs better with a restriction on P.
-- It is not needed for the actual result, just for performance.
--
--------------------------------------------------------------------------------------
-- ben
--------------------------------------------------------------------------------------
Remarks the query is not totaly identical. The statement without the UNION behaves like a UNION all, but it is possible to exclude the 'doubles' it just makes the code more complex and does not add to the illustration and does not solve the non indexability.
Maybe somebody can build on the example and create a view which can be indexed, but I doubt it.
Ben
May 28, 2019 at 3:11 pm
If the question was just to convert union into join - I would have gone with full join with coalesce function -though this will not work in the view either
SELECT COALESCE(t1.id, t2.id) id ,
COALESCE(t1.col1, t2.col1) col1 ,
COALESCE(t1.col2, t2.col2) col2 ,
COALESCE(t1.col3, t2.col3) col3
FROM dbo.t1
FULL JOIN dbo.t2 ON t1.ID = t2.id
AND t2.col1 = t1.col1
AND t2.col3 = t1.col3
May 28, 2019 at 3:35 pm
If the question was just to convert union into join - I would have gone with full join with coalesce function -though this will not work in the view either
SELECT COALESCE(t1.id, t2.id) id ,
COALESCE(t1.col1, t2.col1) col1 ,
COALESCE(t1.col2, t2.col2) col2 ,
COALESCE(t1.col3, t2.col3) col3
FROM dbo.t1
FULL JOIN dbo.t2 ON t1.ID = t2.id
AND t2.col1 = t1.col1
AND t2.col3 = t1.col3
Clever solution.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 29, 2019 at 9:56 am
@Taps, simpler and therefore better.
Ben
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply