CODEWITHSUNDEEP
sqlsql-serversql-tuning
Naxi
Naxi

Reputation: 2026

Alternative to using temp table/temp variables or CTE inside a view in SQL Server

I have a scenario where I have to create a view which has a bunch of UNIONS of various select statements.

SELECT DISTINCT ISNULL(ID,'ID') as Id, 
ISNULL(FIRST_NAME,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM [test].[emp].[OrgView]
  UNION
SELECT DISTINCT ISNULL(EMP_ID,'ID') as Id, 
ISNULL(LAST_NAME,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM [test].[emp].[OrgView]
  UNION
SELECT DISTINCT ISNULL(LICENSE,'ID') as Id, 
ISNULL(COMPANY,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM [test].[emp].[OrgView]
.
.
.
.
.
10 such selects

I try using temporary variable & temp tables to avoid making 10 different calls to the database but looks like they do not work inside a view like below.

Create View [test].[emp].[MainView]
AS
select * into #tempTable from [test].[emp].[OrgView]
SELECT DISTINCT ISNULL(ID,'ID') as Id, 
ISNULL(FIRST_NAME,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM #tempTable
  UNION
SELECT DISTINCT ISNULL(EMP_ID,'ID') as Id, 
ISNULL(LAST_NAME,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM #tempTable
  UNION
SELECT DISTINCT ISNULL(LICENSE,'ID') as Id, 
ISNULL(COMPANY,'unknown') + ':' + 'Unknown' AS label,
ISNULL(VALUE,'unknown') AS [value]
  FROM #tempTable

Cannot use common table expression(CTE) as well with above query as it will be available to just one lookup and not to the rest 9.

What is the better way of doing it in Sql Server ?

Upvotes: 0

Views: 1244

Answers (2)

K4M
K4M

Reputation: 952

When you you use a VIEW, it's a 1 call to the database regardless of what's inside the view. So there is no need to use temp tables or table variables etc. So, your original query would work just fine inside a VIEW and it would be a single SQL call.

CREATE VIEW [test].[emp].[MainView]
AS
SELECT .... FROM [test].[emp].[OrgView]
UNION
SELECT .... FROM [test].[emp].[OrgView]
....

It's efficiency is another matter and I can't comment on that as the complete logic is not posted in your questions. However, if all JOINs and WHERE clauses in all SELECT statements are similar/compatible with each other, then you could combine them with a CROSS APPLY as @gordon-linoff suggested but I see you're mentioning one of the queries is using a lookup not shared by other queries so that may or may not be possible (your post is missing the details).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

I think you want CROSS APPLY instead of UNION. I am a little unclear on where you want to put the logic, but here is the idea:

SELECT DISTINCT v.Id, 
       (COALESCE(FIRST_NAME, 'unknown') + ':' + 'Unknown') AS label,
       COALESCE(VALUE,'unknown') AS [value]
FROM #tempTable CROSS APPLY
     (VALUES (COALESCE(ID, 'ID')),
             (COALESCE(EMP_ID, 'ID')),
             (COALESCE(LICENCE_ID, 'ID')),
             . . .
     ) v(id)

Upvotes: 1

Related Questions