May 4, 2011 at 10:27 am
can these 2 views give same result and performance?
ALTER VIEW [dbo].[vwemp]
AS
Select * from employee_1
where empid in ('BOA','BOT')
union all
select * from employee_2
where empid in ('BOA','BOT')
and empno not in (select empno from emp_look where active=1)
ALTER VIEW [dbo].[vwemp]
AS
Select * from employee_1
union all
select * from employee_2
where empid in ('BOA','BOT')
and empno not in (select empno from emp_look where active=1)
May 4, 2011 at 10:39 am
No, the missing where in the first query will give different results... and performance is never an issue with wrong results :w00t:.
May 4, 2011 at 11:09 am
does this mean
and empno not in (select empno from emp_look where active=1)
applies to only employee_2 table but not for all the union results.
May 4, 2011 at 11:13 am
Only the order by can apply to the unioned set without using a derived table.
SELECT COUNT(*) FROM (
Select name from sys.columns where name like '%e%'
UNION ALL
SELECT name from sys.tables where name like '%e%'
) a
--120022
SELECT COUNT(*) FROM (
Select name from sys.columns --where name like '%e%'
UNION ALL
SELECT name from sys.tables where name like '%e%'
) a
--150027
SELECT COUNT(*) FROM (
Select name from sys.columns --where name like '%e%'
UNION ALL
SELECT name from sys.tables --where name like '%e%'
) a
where name like '%e%'
--120022
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply