Name of CTE and referenced table should be different

Just got the below example from the public forum.

CREATE TABLE Batch (Batch CHAR(1),Status INT)

INSERT INTO Batch VALUES ('A',1)
INSERT INTO Batch VALUES ('B',2)
INSERT INTO Batch VALUES ('C',3)

WITH Batch AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Batch, Status) AS RowNum
FROM Batch
)
DELETE FROM Batch
WHERE RowNum=1

As you can see , running the query SQL Server throws the error.
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'Batch' does not contain a top-level UNION ALL operator.

SQL Server "thinks" that the CTE referenced to itself but there is no UNION ALL clause.In the blow example CTE named EmpCTE referenced within to itself to join with Employees table.

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(

-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 7
UNION ALL

-- Recursive Member (RM)
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

Finally,in order to resolve the problem you need that CTE and user table have different names. Something like that

WITH Batch_cte AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Batch, Status) AS RowNum
FROM Batch
)
DELETE FROM Batch_cte
WHERE RowNum=1

MVP Award for 2011 year.

I just got my MVP renewal email from MS, so this is my 5th award for SQL MVP since 2007.
I would say thanks for all support and good communication with all MVP around the world and hopefully it could make me more motivated to give more contribution for the community

To someone who specializes in SQL Server performance tuning

Must read Conor's blog
http://blogs.msdn.com/b/conor_cunningham_msft/

Alias issue in T-SQL or defensive programming

Recently I have talked to our developer who wanted to delete TOP x rows from the table. I pointed him to the below artcile http://blogs.msdn.com/b/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx where a tip – a view with ORDER BY.

As alternative he wanted using a derived table but cannot understand why all rows are deleted from the table instead of TOP(x). See the below demo.

create table #t (c int)
insert into #t values (1)
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (3)

delete #t from (select top (2) c
from t order by c) t

How does DELETE extension in T-SQL work?. The FROM clause after the DELETE specifies the target table to delete. The second optional FROM clause specifies the qualifying rows. But if I change 't' alias to '#t' as original name of the temporary table that would work...

delete #t from (select top (2) c
from t order by c) #t

Now, SQL Server 'sees' that derived table has the same name as a target and thus deletes only TOP(x) rows

There is no goal of this post to get into a discussion about how to write correlated subquery to perform such operations, I just wanted you to pay attention on if you choose using derived tables to perform deletion please make sure that alias you specify for derived table is the same as a target table..

PS. If you are testing and not sure about the result please use BEGIN TRAN... before executing the script.If you see that rows affected by the script is too many issue ROLLBACK TRAN to back to original data.

Dedup on huge table

Hi friends
At this time I would like to share with you my experience to delete duplicates in very large table(800 million of rows).
A very general method is to use ROW_NUMBER function to PARTITION ON desired columns and then filter out only unique data.

WITH cte
AS
(
SELECT ,ROW_NUMBER() OVER (PARTITION BY ORDER BY ) rn
FROM tbl
) SELECT * FROM cte WHERE rn=1

As you imagine on huge table it will take too long. In order to optimize that query I used batch processing (divided that transaction into small chunks)


DECLARE @x INT
SET @x = 1
WHILE @x < 44,000,000 -- Set appropriately
BEGIN

;WITH cte
AS
(
SELECT ,ROW_NUMBER() OVER (PARTITION BY ORDER BY ) rn
FROM tbl WHERE ID BETWEEN @x AND @x + 10000
)SELECT * FROM cte WHERE rn=1

SET @x = @x + 10000
END
Ok ,it worked much faster as we have a clustered index on ID column such as SQL Server uses it to get the data based on defined range.However, we have another problem that data we are getting back is not actually unique.You see that for specific range ,say (from 1 to 10000) I get the data based on required partition and filter out for rn=1, BUT it is possible that the same row will occur in the next chunk (from 10001 to 20000) and we will also get it back because SQL Server does not recognize it as duplicate we have already got from the first chunk.

More reliable solution is checking on entire table and not to base on ranges.
SELECT ,COUNT(*) rn FROM
tbl GROUP BY
HAVING COUNT(*)>1

As you can see it could take long time ,so I also tried to create a Dedupt table with a key IGNORE_DUP_KEY option (thanks to Hugo) but insert into the table was pretty slow as well. Peter Larsson a fellow MVP has suggested the below technique that worked pretty well

CREATE TABLEe #unique (id primary key clustered)
INSERT INTO #temp (id) SELECT MIN(ID) AS ID
FROM tbl
GROUP BY Col1, col2, col3... (here you decide the uniqueness)

--And then insert into batches for

SET @id = 0
WHILE @id < (800 million or more)
BEGIN
SELECT t1.ID, t1.Col
FROM dbo.Table1 AS T1
INNER JOIN #unique as u ONu.id = t1.id
AND u.id BETWEEN @id AND @id +99999

SET @id += 100000
END

It would be great it put back here testing results.

What business says does not mean what businees wants

This great sentence I learned form my exerience being a consultant

What business says does not mean what businees wants

TechEd 2010 in Eilat

Hi
I am going to attend TechEd 2010 in Eilat next week. It is great opportunity to learn new things , meet new and old friends.It is my second TechEd and I am will be focusing on Data Platform direction and BI. Hope to see you there.

http://www.microsoft.com/israel/TechEd2010/Tracks/BI.aspx