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!

Product Categories and TSQL

I’ve just spent a few hours this morning working on a drop down menu for the website. It works a little like the Microsoft Vista file browser address bar. Any part of the breadcrumb type trail can be clicked on to drop down the other categories at that level.

Since I’ve upgraded the website SQL server earlier in the year from 2000 to 2008 version of SQL server I now can utilise Common Table Expressions to get the solution (CTE). Used in a recursive manner you can do all sorts that needed cursors before – great stuff!

Here was my resulting script before packaging into a  stored procedure:

 

-- Script to get all the categories from a given category up over 
-- includes a mark against the path we are on and other categories at that level.
-- T.WAPPAT 20th July 2009
---
DECLARE @CatID Integer
SET @CatID=22141
WITH CategoryParents(CatID, ParentCatID, SortInLevel, 
                            CatLangCode, CatDesc, InPath,  Depth)
AS
(
--Anchor member
SELECT    CategoriesSibs.CatID as CatID, CategoriesSibs.ParentCatID,
        CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode, 
        CategoriesDesc.CatDesc, 
        CASE CategoriesSibs.CatID WHEN @CatID 
            THEN 1 ELSE 0 END as InPath,
        0 as Depth
FROM  MA_Categories AS Categories WITH (NOLOCK)   
      JOIN 
      MA_Categories as CategoriesChild WITH (NOLOCK) 
        ON CategoriesChild.ParentCatID=Categories.CatID
      JOIN 
      MA_Categories as CategoriesSibs WITH (NOLOCK) 
        ON CategoriesSibs.ParentCatID=Categories.CatID
      JOIN
      MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK) 
        ON CategoriesDesc.CatID = CategoriesSibs.CatID 
WHERE CategoriesChild.CatID=@CatID
UNION ALL
--Recursive member
select  Categories.CatID, Categories.ParentCatID, 
        CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode,
        CategoriesDesc.CatDesc, 
        CASE CategoriesSibs.CatID WHEN CategoryPArents.ParentCatID 
            THEN 1 ELSE 0 END as InPath,
        Depth+1 as Depth
FROM         CategoryParents AS CategoryParents 
             JOIN 
             MA_Categories AS Categories WITH (NOLOCK) 
                ON CategoryParents.ParentCatID=Categories.CatID  
             JOIN
             MA_Categories as CategoriesSibs WITH (NOLOCK) 
                ON CategoriesSibs.ParentCatID=Categories.ParentCatID
                JOIN
             MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK) 
                ON CategoriesDesc.CatID = CategoriesSibs.CatID  
)
-- execute CTE
select  distinct * from CategoryParents  ORDER BY Depth, SortinLevel

 

You may find this useful to hack around for your own purposes. The following are table definitions.

CREATE TABLE [dbo].[MA_Categories](
    [CatID] [int] NULL,
    [ParentCatID] [int] NULL,
    [SortInLevel] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[MA_CategoryDescs](
    [CatID] [int] NULL,
    [CatLangCode] [varchar](3) NULL,
    [CatDesc] [varchar](200) NULL
) ON [PRIMARY]

GP-Dynamics – Custom Programming a TSQL Collection

I stumbled upon this a new Dynamics GP site tonight, gp-dynamics.com. This site lists nearly one hundred useful SQL scripts to use when working with Microsoft Dynamics GP ERP system. I glanced down the list of scripts and dipped into some of them and it all looked like a good resource.

The site is orientated towards selling a technical manual on Dynamics GP, but this section is free. Of the scripts I looked at, some of them were naive, but not dangerous. Certainly enough to get out of jail free, I certainly would have relished such a resource back in 2001 when I started with GP, as it happens I have strikingly similar resource of scripts I’ve built up myself on my hard drive. A couple of examples from the site to give you the idea follow;

9.  Order Comments
Display all comments for a particular order.. (viewed 1490 times)

10.  How to unlock a Dynamics user
This is useful in solving Dynamics GP error: "User is already logged-in" when you are trying to log .. (viewed 3397 times)

GPDynamicscom

Technorati Tags: ,