SQL Server 2005 Training, Day 1, Part 1

SQL Server 2005 Training, Day 1, Part 1

I have been trained SQL Server 2005 several months ago but I haven’t time to summarize yet so I’m going to write as much as possible. The training course concerns about using MS SQL Server 2005 with MS Visual Studio Visual Basic.NET so some part may contains source code in VB.NET. Most of contents will express using example and figure if you need reference or more detail of functions, you can try search at MSDN, Microsoft.com. I hope that these may be useful for programmers to review.

The training consumes 5 days so I’ll categorize this by days and also separate each day into 2 parts because it’s quite long.

Set up Environment

This is the environment which was set up for the training.

  • SQL Server 2005 Express edition with SQL Server Management Studio Express.
  • Northwind, pubs and AdventureWorks (Case Insensitive) databases, you can download at Microsoft.com (moved to http://codeplex.com/SqlServerSamples) or at here:
  • Microsoft Visual Studio 2005 for coding VB.NET

Topics in this post

1. Using row_number()
2. Using local temporary table
3. Using CTE
4. Using rank, dense_rank, ntile
5. Technique of WITH
6. Using temp table in a store procedure
7. Using Global temp table
8. Using Synonym
9. Forward cursor
10. Backward cursor
11. Update record using cursor
12. Using Case
13. Variable and conversion
14. Using store procedure
15. Compute Max, Min, Avg
16. Roll up , cube -> use with aggregate

Let’s start

  • Open SQL Server Management Studio Express and connect to the SQL Server.
  • Open New Query. Before any query, I’ll type use “DatabaseName�? to select the database.
  1. Using row_number()
    Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

    Example

    • Show row number start from last productname as 1 and so on. And return the result ordering by ascending productname.

      use Northwind

      SELECT ROW_NUMBER() OVER (ORDER BY productname DESC) AS rownum,
      productid,productname FROM products 
      ORDER BY productname ASC

    • Select only rownum > 60 from the above example and return the result ordering by descend productid.
      SELECT * FROM (
      SELECT ROW_NUMBER() OVER (ORDER BY productname DESC) AS rownum, 
      productid,productname FROM products ) O
      WHERE rownum > 60
      ORDER BY productid DESC

  2. Using local temporary table to store result set. The table will be deleted automatically after close the connection.

    Example

    • Select some records and store into table named ‘temp1’.
      USE Northwind
       
      SELECT ROW_NUMBER() OVER (ORDER BY productname DESC) AS rownum, 
      productid,productname INTO #temp1 FROM products

    • View current temp table.
      SELECT * FROM tempdb..sysobjects WHERE TYPE ='u'

  3. Using CTE (Common Table Expression) as temporary result set.

    Example

    • Select some records and keep into m1 result set.
      USE Northwind
       
      WITH m1 AS (
      SELECT ROW_NUMBER() OVER (ORDER BY productname DESC) AS rownum, 
      productid,productname FROM products
      )
       
      SELECT * FROM m1 WHERE rownum > 50

  4. Using rank, dense_rank, ntile

    rank() – returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

    dense_rank() – returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

    Ntile() – distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

    Example

    USE AdventureWorks
     
    SELECT ROW_NUMBER() OVER (ORDER BY city) AS rownum, 
    rank() OVER (ORDER BY city) AS rank1,
    dense_rank() OVER (ORDER BY city) AS denserank1,
    Tile1 = NTile(4) OVER (ORDER BY city),
    city FROM person.address INNER JOIN
    person.stateprovince AS stateprov ON
    address.stateprovinceid = stateprov.stateprovinceid
    WHERE countryregioncode = 'us' 
    AND stateprovincecode = 'az'
    ORDER BY city

  5. Technique of WITH.
    By using WITH, you can make your query more readable as the example below. Both queries return the same result but notice that the second one is easier to read.

    Example

    USE AdventureWorks
     
    SELECT * FROM production.product AS P
       INNER JOIN (SELECT productmodelid, avg(listprice) AS avgprice
       FROM production.product GROUP BY productmodelid) AS C
       ON P.Productmodelid = C.ProductmodelID AND P.ListPrice > C.Avgprice;
     
     
    WITH C AS (
    SELECT productmodelid, avg(listprice) AS avgprice
       FROM production.product GROUP BY productmodelid
    )
    SELECT * FROM production.product AS P INNER JOIN C
    	ON P.Productmodelid = C.ProductmodelID AND P.ListPrice > C.Avgprice

  6. Using temp table in a store procedure.

    Example

    CREATE proc test2 AS
    SELECT * INTO #temp3 FROM northwind..products
    SELECT * FROM #temp3
     
    EXEC test2
     
    DROP proc test2

  7. Using Global temp table.
    By define as a global, you can called it anywhere until you disconnect from the SQL Server.

    Example

    SELECT * INTO ##temp2 FROM northwind..products
     
    -- You can try called this on another new query
    SELECT * FROM ##temp2

  8. Using Synonym.

    Example

    USE AdventureWorks
     
    SELECT * FROM person.contact
    CREATE synonym dbo.contact FOR person.contact
    SELECT * FROM dbo.contact

  9. Forward cursor.
    • Server-side cursor.
    • Cursor, fetch data each record.
    • Save memory -> forward only, can’t backward.
    • Suit for store procedure.

    Example

     
    USE Northwind
     
    -- Create new cursor
    DECLARE product_a cursor FOR 
    SELECT productid,productname,unitprice FROM products 
    ORDER BY productname
     
    -- Open a cursor
    OPEN product_a 
     
    -- Query records from cursor
    DECLARE @productid INT,@productname nvarchar(60),@unitprice money
    fetch NEXT FROM product_a INTO @productid,@productname,@unitprice
    SELECT @productid,@productname,@unitprice

    /* Get only 10 record, declare @i for loop */
    DECLARE @i INT 
    SET @i = 0
    while @i < 10 BEGIN
    DECLARE @productid INT,@productname nvarchar(60),@unitprice money
    fetch NEXT FROM product_a INTO @productid,@productname,@unitprice
    SELECT @productid,@productname,@unitprice
    SET @i = @i + 1
    END

    /* Fetch all record, using @@fetch_status to check end of query */
    DECLARE @i INT
    DECLARE @productid INT, @productname nvarchar(60), @unitprice money
    SET @i = 1
    fetch NEXT FROM product_a INTO @productid, @productname, @unitprice
    while @@fetch_status = 0 BEGIN
    SELECT @i AS Number1,@productid, @productname, @unitprice
    fetch NEXT FROM product_a INTO @productid, @productname, @unitprice
    SET @i = @i +1
    END

    /* close cursor */
    close product_a
     
    /* dispose cursor object */
    deallocate product_a
  10. Backward cursor.
    cursor backward – use fetch back … to get previous record.

    Example

     
    USE Northwind
     
    DECLARE product_a scroll cursor FOR 
    SELECT productid,productname,unitprice FROM products 
    ORDER BY productname
     
    OPEN product_a 
     
    /* fetch first ,get first record */
    DECLARE @productid INT, @productname nvarchar(60), @unitprice money
    fetch FIRST FROM product_a INTO @productid, @productname, @unitprice
    SELECT @productid, @productname, @unitprice

    /* fetch last ,get last record */ 
    DECLARE @productid INT, @productname nvarchar(60), @unitprice money
    fetch LAST FROM product_a INTO @productid, @productname, @unitprice
    SELECT @productid, @productname, @unitprice

    /* fetch absolute 10 ,get a record 10th*/
    DECLARE @productid INT, @productname nvarchar(60), @unitprice money
    fetch absolute 10 FROM product_a INTO @productid, @productname, @unitprice
    SELECT @productid, @productname, @unitprice

    /* fetch relative 10 ,get a record relative next 10*/
    DECLARE @productid INT, @productname nvarchar(60), @unitprice money
    fetch relative 10 FROM product_a INTO @productid, @productname, @unitprice
    SELECT @productid, @productname, @unitprice

  11. Update record using cursor.

    Example

    /* Create table productB from Northwind.products with add field price2  */
     
    USE Northwind
     
    SELECT * INTO productB FROM products
    ALTER TABLE productB ADD CONSTRAINT abc1 PRIMARY KEY (productid)
    ALTER TABLE productB ADD price2 money
    SELECT * FROM productB

    -- Create cursor2
    DECLARE cursor2 cursor FOR 
    SELECT productid,unitprice, price2 FROM productB
    FOR UPDATE
     
    OPEN cursor2
     
    /* No need to declare productid, but the table should have a primary key  */
    -- Update field price2 on next record
    DECLARE @productid INT, @unitprice money, @price2 money
    fetch NEXT FROM cursor2 INTO @productid , @unitprice , @price2
    UPDATE productB SET price2 = 50 WHERE CURRENT OF cursor2
     
    SELECT * FROM productB

    -- Create cursor3
    DECLARE cursor3 cursor FOR 
    SELECT unitprice, price2 FROM productB
    FOR UPDATE
     
    OPEN cursor3
     
    -- Update field price2 by compute difference value with the previous record
    DECLARE @price3 money
    SET @price3 = 0
    DECLARE @unitprice money, @price2 money
    fetch NEXT FROM cursor3 INTO @unitprice , @price2
    while @@fetch_status = 0 BEGIN
    	UPDATE productB SET price2 = @unitprice - @price3 WHERE CURRENT OF cursor3
    	SET @price3 = @unitprice
    	fetch NEXT FROM cursor3 INTO @unitprice , @price2
     
    END
     
    SELECT * FROM productB

    -- Close and deallocate cursors
    close cursor2
    deallocate cursor2
     
    close cursor3
    deallocate cursor3
  12. Using Case.

    Example

    USE pubs
     
    SELECT * FROM employee
     
    -- Set NewLevel base on job_lvl
    SELECT lname, CASE WHEN job_lvl < 100 THEN 'xxx'
    					WHEN job_lvl < 200 THEN 'yyy'
    					ELSE 'zzzz'
    					END AS NewLevel
    FROM employee

    -- Another case example
    SELECT title_id, SUM(CASE YEAR(ord_date) WHEN 1992 THEN qty ELSE 0 END) AS Y1992,
    	SUM(CASE YEAR(ord_date) WHEN 1993 THEN qty ELSE 0 END) AS Y1993,
    	SUM(CASE YEAR(ord_date) WHEN 1994 THEN qty ELSE 0 END) AS Y1994 ,
    	SUM(qty) AS Total 
    FROM sales
    GROUP BY title_id

  13. Variable and conversion.

    Example

    USE pubs
     
    -- Declare variable
     
    DECLARE @SQL nvarchar(500)
    SET @SQL = 'select * from titles'
    EXEC(@SQL)

    -- Convert to string
    SELECT CONVERT(nvarchar(40), getdate())
    SELECT CAST(getdate() AS nvarchar(4))

  14. Using store procedure.

    Example

    • Create a procedure test_a
      USE pubs
       
      -- Create Procedure
      CREATE proc test_a (@Year1 nvarchar(4), @Year2 nvarchar(4)) AS
      BEGIN
      DECLARE @SQL nvarchar(4000)
      SET @SQL = 'select title_id '
      DECLARE @i INT, @j INT, @k INT
      SET @i = CONVERT(INT,@Year1)
      SET @j = CONVERT(INT,@year2)
      SET @k = @i
      while @k < = @j BEGIN
        SET @SQL = @SQL + ',sum(case year(ord_date) when ' + CONVERT(nvarchar(40),@k) + ' then qty else 0 end) as Y' + CONVERT(nvarchar(40),@k)
        SET @k = @k +1
      END
       
      SET @SQL = @SQL + ' from sales group by title_id with rollup'
      EXEC (@SQL)
      END
    • Execute procedure test_a
      EXEC test_a '1992','1994'

    • Create a procedure test_b with return value
      CREATE proc test_b (@Year1 nvarchar(4), @Year2 nvarchar(4)) AS
      BEGIN
      DECLARE @SQL nvarchar(4000)
      SET @SQL = 'select title_id '
      DECLARE @i INT, @j INT, @k INT
      SET @i = CONVERT(INT,@Year1)
      SET @j = CONVERT(INT,@year2)
      SET @k = @i
      while @k < = @j BEGIN
        SET @SQL = @SQL + ',sum(case year(ord_date) when ' + CONVERT(nvarchar(40),@k) + ' then qty else 0 end) as Y' + CONVERT(nvarchar(40),@k)
        SET @k = @k +1
      END
       
      SET @SQL = @SQL + ' from sales group by title_id with rollup'
      EXEC (@SQL)
      RETURN @@rowcount-1
      END
       
      -- Execute procedure test_b
      DECLARE @q INT
      EXEC @q = test_b '1992','1994'
      SELECT @q

    • Multiple return values

      Example

      CREATE proc test_c (@Year1 nvarchar(4), @Year2 nvarchar(4),@sum1 INT output,@avg1 INT output) AS
      BEGIN
      DECLARE @SQL nvarchar(4000)
      SET @SQL = 'select title_id '
      DECLARE @i INT, @j INT, @k INT
      SET @i = CONVERT(INT,@Year1)
      SET @j = CONVERT(INT,@year2)
      SET @k = @i
      while @k < = @j BEGIN
        SET @SQL = @SQL + ',sum(case year(ord_date) when ' + CONVERT(nvarchar(40),@k) + ' then qty else 0 end) as Y' + CONVERT(nvarchar(40),@k)
        SET @k = @k +1
       
      END
       
      DECLARE @k3 INT
      SET @SQL = @SQL + ' from sales group by title_id'
      EXEC (@SQL)
      SET @k3 = @@rowcount - 1
      SELECT @sum1 = SUM(qty), @avg1 = avg(qty) FROM sales WHERE YEAR(ord_date) BETWEEN
      	CONVERT(INT, @year1) AND CONVERT(INT,@year2)
      RETURN @k3
      END
       
      DECLARE @a INT,@b INT,@c INT
      EXEC @a = test_c '1992','1994',@b output, @c OUT
      SELECT @a,@b AS sum1,@c AS avg1

  15. Compute Max, Min, Avg.

    Example

    SELECT stor_id, ord_num, qty FROM sales ORDER BY stor_id
    compute MAX(qty), MIN(qty), avg(qty)

    -- Compute by – use with order by
     
    SELECT stor_id, ord_num, qty FROM sales ORDER BY stor_id
    compute MAX(qty), MIN(qty), avg(qty) BY stor_id

  16. Roll up , cube -> use with aggregate

    Example

    SELECT stor_id,ord_num,SUM(qty) AS sumqty FROM sales
    GROUP BY stor_id, ord_num
    WITH rollup

    -- Cube
    SELECT stor_id,ord_num,SUM(qty) AS sumqty FROM sales
    GROUP BY stor_id, ord_num
    WITH cube

One Response

  1. ptanh December 2, 2007

Leave a Reply

%d bloggers like this: