SQL Server 2005/2008 – its great

We upgraded our SQL server from 2000 to 2008 early this year and although I have been using 2005 for a while for Microsoft Dynamics GP, only the website really stretches my TSQL skills to the limit. I have a library of scripts that solve most problems or can be adapted to address most issues that occur in Dynamics GP developed in SQL server 2000 days.

This year I have been wallowing in

1. Common Table Expressions (CTE)
2. Availability of the TOP command in INSERT DELETES
3. Ranking expressions
4. VARCHAR(MAX) data types, and the exciting date types in SQL 2008
5. Outputting affected rows (INSERT/UPDATE/DELETE)
6. Enhancements in TOP to allow use to filter results in SProcs
7. Intelisense in Management Studio

These new friends almost eradicate cursors and triggers for all but the most complex scenarios.

I found that Programming Microsoft® SQL Server® 2008 (PRO-Developer) was great step up book to get from SQL 2000 experienced DBA to SQL 2008. No wasting time going over basics, very factual expecting you to pick up from where you left with SQL 2000.

Another book I still enjoy today is the The Guru's Guide to Transact SQL (Paperback), that is technically a little outdated but the way this book makes you think about SQL really gives you a leg up to a higher level of TSQL programming that you can still apply to the newer versions of SQL server.

Deduplication

I had a duplication problem to solve today. The usual issue where there is a plain text imported file that has issues. The import table had no primary key and many thousands of duplicate rows that needed removing. This could be done with SQL server 2000, it was a pain and never very pretty. Now the solution is beautiful.

Create the table, fill it with some duplicate lines.

    CREATE TABLE [ItemImages_Import](
        [ItemCode] [varchar](31) NULL,
        [Graphic] [varchar](255) NULL,
        [Position] [smallint] NULL
    ) ON [PRIMARY]
    GO

then goodness me all you have to do is run this script and your duplicates are no more!

    WITH VirtTable 
    as (select
     itemcode,graphic,ROW_NUMBER() 
        OVER (PARTITION BY itemcode  order by graphic
         as GroupCounter)
    from ItemImages_Import)
    Delete from VirtTable where GroupCounter>1

This uses the ranking expression ROW_NUMBER() to create a temporary “key” that is then used to delete the items after the “1”st instance in the table of the duplicate. This is great stuff.

Create Row number in a Grouping using TSQL

My next problem is that I wanted to renumber the position of the images in the table using the sort column, overwriting the existing values;

288-288 graphic1.jpg 1
288-288 graphic2.jpg 2
288-288 graphic3.jpg 3
288-999 graphic4.jpg 1
288-999 graphic5.jpg 2
289-100 graphic6.jpg 1
289-100 graphic7.jpg 2
289-100 graphic8.jpg 3
etc

Again its a doddle once you get your head around rankings.

    UPDATE ItemImages_Import
    set position=0;
    WITH VirtTable 
    as 
    (select *,
     ROW_NUMBER() 
        OVER (PARTITION BY itemcode 
         order by graphic)as GroupCounter
    from ItemImages_Import)
    UPDATE  VirtTable
     SET position=GroupCounter;
    select * from ItemImages_Import order by 
        ItemCode, Position

Just to prove it works I blank the column first with the update.

Happy TSQLing!