Create indexed view
My table structure is below :
MyTable (ID Int, AccID1 Int, AccID2 Int, AccID3 int) ID AccID1 AccID2 AccID3 ---- -------- -------- -------- 1 12 2 NULL 2 4 12 1 3 NULL NULL 5 4 7 NULL 1
I want to create indexed view with below output :
ID Level Value ---- ----- ------- 1 1 12 1 2 2 2 1 4 2 2 12 2 3 1 3 3 5 4 1 7 4 3 1
My table is very huge and I want to have above output. I can Get my query such as below :
Select ID, Case StrLevel When 'AccID1' Then 1 When 'AccID2' Then 2 Else 3 End AS [Level], AccID as Value From ( Select A.ID, A.AccID1, A.AccID2, A.AccID3 From MyTable A )as p UNPIVOT (AccID FOR [StrLevel] IN (AccID1, AccID2, AccID3)) AS unpvt
Select * from ( select MyTable.ID, num.n as [Level], Case Num.n When 1 Then MyTable.AccID1 When 2 Then MyTable.AccID2 Else MyTable.AccID3 End AS AccID from myTable cross join (select 1 union select 2 union select 3)Num(n) )Z Where Z.AccID IS NOT NULL
Select A.ID, 2 AS [Level], A.AccID1 AS AccID From MyTable A Where A.AccID1 IS NOT NULL Union Select A.ID, 2 AS [Level], A.AccID2 From MyTable A Where A.AccID2 IS NOT NULL Union Select A.ID, 3 AS [Level], A.AccID3 From MyTable A Where A.AccID3 IS NOT NULL
But Above query is slow and I want to have indexed view to have better performance. and in indexed view I can't use UNION or UNPIVOT or CROSS JOIN in indexed view.
What if you created a Numbers table to essentially do the work of your illegal CROSS JOIN?
Create Table Numbers (number INT NOT NULL PRIMARY KEY) Go Insert Numbers Select top 30000 row_number() over (order by (select 1)) as rn from sys.all_objects s1 cross join sys.all_objects s2 go Create view v_unpivot with schemabinding as Select MyTable.ID, n.number as [Level], Case n.number When 1 Then MyTable.AccID1 When 2 Then MyTable.AccID2 Else MyTable.AccID3 End AS AccID From dbo.Mytable Join dbo.Numbers n on n.number BETWEEN 1 AND 3 go Create unique clustered index pk_v_unpivot on v_unpivot (ID, [Level]) go Select ID, [Level], AccID From v_unpivot with (noexpand) Where AccID IS NOT NULL Order by ID, [Level]
The WHERE AccID IS NOT NULL must be part of the query because derived tables are not allowed in indexed views.