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

Blobs and covering indexes

Nowadays it is common to store/save pictures,documents in the databbase. Since SQL Server 2005 we use VARBINARY(MAX) datatype to store such data. The 'problem' I have seen recently at the client database is SELECT statement on very huge table that contain BLOB data and that data needs to be return to the client works pretty slowly. Run the below
SET STATISTICS IO ON

SELECT col1,col2, blobdata FROM tbl WHERE....

Table 'tbl'. Scan count 1, logical reads 8125, physical reads 0, read-ahead reads 0, lob logical reads 261521, lob physical reads 0, lob read-ahead reads 0.
CPU time = 1252 ms, elapsed time = 12632 ms.

We see high number of reads to return the data from index/blob pages.

Execution plan shows that there is Bookmark to return the BLOB data from Clustered Index Key because of our NCI(NonClustered Index) does not cover all columns in SELECT statement.
Ok, at first glance you would re-create NCI to INCLUDE blobdata column to 'cover' SELECT ...Well, I noticed that recreating NCI index takes long long time and LOG file was grown dramatically. I see that after INCLUDE blobdata column I reduced logical reads as shown below

Table 'tbl'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 421521, lob physical reads 0, lob read-ahead reads 0.
CPU time = 1622 ms, elapsed time = 16512 ms.

But what happened? BLOB page reads were done almost 1.8 times more with covering index and actually I have not seen much improvment in performance of the query.
So finally, if you create an index or INCLUDE the BLOBs SQL Server create copy of that index for every blob column and thus it takes time to create index and reading the data from index page. Well, you were able to save some IO by covering BLOB column but internally SQL Server works much hardly to return the data and maintain the index.

Sometimes it is important to choose right collation for the server

The title looks like a joke, am I right? Sometimes we just take care of collation for specific database. Well that may lead you to unpleasant situation and moreover getting wrong or no data at all. Recently I helped my friend to solve the problem for his new software. He created a database with "right" collation let say Hebrew_CI_AS and the program searches for the data that contains hebrew characters. Suddenly the program does not return the data at all. He showed me that the data he is looking for are presented in the table and point me to the stored procedure that performs the searching. You have already guessed what was the problem, right? The problem was because withing a stored procedure he used a temporary table where the programm stores the data for final result (VARCHAR(n) for search string) for some logic. As you know tempdb is a system database which inherits a server's collation (SQL_Latin1_General_CP1_CI_AS) and hence no data were returned. In that specific case we changed the data type to NVARCHAR(n) and there is no need to reinstall the entire server, but think about the program(I have seen many of such) that such technique (store unicode character in temporary tables) are used in hundred of stored procedures and change the datatype for all of them is very big headache.

Window Functions (OVER Clause) – Help Make a Difference

Hello friends

As you probably know those days SQL Server 2011 is being developed. Itzik Ben-Gan believes (me too) that having those new window functions and enhance existing ones will benefit all of us SQL Server developers in the future more complete support for the standard window functions in SQL Server. Please read and vote..

http://www.sqlmag.com/blogs/puzzled-by-t-sql/tabid/1023/entryid/13085/Window-Functions-OVER-Clause-Help-Make-a-Difference.aspx