June 1, 2010 at 2:48 pm
I'm seeing a lot of code like
; with EMP_CTE(EMPID, NAME) AS (SELECT EMPID, NAME FROM EMPLOYEE WHERE STARTDATE > '4/25/1968')
SELECT EMPID, NAME FROM EMP_CTE WHERE MGRID = 4;
Apparently some developers got curious and excited about CTEs and decided to slip some into production code. I'm trying to decide if it's harmful. I'm guessing not, but I thought I'd put it out there.
Do the two WHEREs make an 'AND'? Does one of the wheres have any advantage over the other?
June 1, 2010 at 6:05 pm
You should check this in your particular environment ...
I don't think you will see a difference in the execution plan between
with EMP_CTE(EMPID, NAME)
AS
(
SELECT EMPID,
NAME
FROM EMPLOYEE
WHERE STARTDATE > '4/25/1968'
)
SELECT EMPID,
NAME
FROM EMP_CTE
WHERE MGRID = 4;
and
with EMP_CTE(EMPID, NAME)
AS
(
SELECT EMPID,
NAME
FROM EMPLOYEE
WHERE STARTDATE > '4/25/1968'
AND MGRID = 4
)
SELECT EMPID,
NAME
FROM EMP_CTE;
and
SELECT EMPID,
NAME
FROM EMPLOYEE
WHERE STARTDATE > '4/25/1968'
AND MGRID = 4
It should use the same index, even if the best index is on MGRID, even in the case of the original example and copied as my first example above.
June 1, 2010 at 6:54 pm
For the most part, CTEs are just a "pre-defined sub-query". The exception is recursive CTEs, where the CTE actually references itself.
So, your query is equal to:
SELECT EMPID, NAME
FROM (SELECT EMPID, NAME FROM EMPLOYEE WHERE STARTDATE > '4/25/1968') EMP_CTE
WHERE MGRID = 4;
IMO, using CTEs is easier to read than all the sub-queries.
An example of a recursive CTE would be (note that this will generate a max recursion limit reached error!):
WITH CTE (N) AS
(
SELECT 1
UNION ALL
SELECT N FROM CTE
)
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM CTE
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 2, 2010 at 7:51 am
Ion Freeman (6/1/2010)
I'm seeing a lot of code like
; with EMP_CTE(EMPID, NAME) AS (SELECT EMPID, NAME FROM EMPLOYEE WHERE STARTDATE > '4/25/1968')
SELECT EMPID, NAME FROM EMP_CTE WHERE MGRID = 4;
Apparently some developers got curious and excited about CTEs and decided to slip some into production code. I'm trying to decide if it's harmful. I'm guessing not, but I thought I'd put it out there.
Do the two WHEREs make an 'AND'? Does one of the wheres have any advantage over the other?
Not harmful but I'd have to say that particular CTE is pretty much overly complex. bteraberry's last bit of code would be the "proper" way to do it especially for simplicity and readability sake. While CTE's can certainly make code more readable, it's gross overkill here.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply