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:
- SQL2000SampleDb for Northwind and pubs.
- AdventureWorksDBCI for AdventureWorks (Case Insensitive).
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.

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 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
  select * from ( select row_number() over (order by productname desc) as rownum, productid,productname from products ) O where rownum > 60 order by productid desc
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
  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'
  select * from tempdb..sysobjects where type ='u'
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
  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

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

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

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
create proc test2 as select * into #temp3 from northwind..products select * from #temp3 exec test2 drop proc test2
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
select * into ##temp2 from northwind..products -- You can try called this on another new query select * from ##temp2
Using Synonym.

Example
USE AdventureWorks SELECT * FROM person.contact CREATE synonym dbo.contact FOR person.contact SELECT * FROM dbo.contact
use AdventureWorks select * from person.contact create synonym dbo.contact for person.contact select * from dbo.contact

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

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

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

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

Variable and conversion.

Example
USE pubs -- Declare variable DECLARE @SQL nvarchar(500) SET @SQL = 'select * from titles' EXEC(@SQL)
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))
-- Convert to string select convert(nvarchar(40), getdate()) select cast(getdate() as nvarchar(4))

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'
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

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

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
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
-- Cube select stor_id,ord_num,sum(qty) as sumqty from sales group by stor_id, ord_num with cube

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Linglom.com

SQL Server 2005 Training, Day 1, Part 1