Sunday, January 24, 2010

Fun with SQL (games, painting, puzzles)

Several days ago my question on StackOverflow was deleted by system: Fun with SQL (games, painting, puzzles). Since it was not a real question and would be deleted again if I repost it I decided to post it here on my own territory : )

If you think SQL is boring or dumb or limited, try this:

SQL Games

Drawing with SQL

SQL Puzzles

SQL Easter eggs

Songs about SQL

Other

Some SQL Techniques

1. Hierarchical tree formatting SELECT using CTE (MS SQL 2005)
Say you have some table with hierarchical tree structure (departments on example) and you need to output it in CheckBoxList or in Lable this way:
     Main Department
Department 1
Department 2
SubDepartment 1
Department 3

Then you can use such query:

WITH Hierarchy(DepID, Name, ParentID, Sort, Type) AS
(
-- First we will take the highest Department (Type = 1)
SELECT DepID, Name, ParentID,
-- We will need this field for correct sorting
Name + CONVERT(VARCHAR(MAX), DepID) AS Sort, 1 AS Type
FROM Departments WHERE Type = 1
UNION ALL
-- Now we will take the other records in recursion
SELECT SubDep.DepID, SubDep.Name, SubDep.ParentID,
CONVERT(VARCHAR(MAX), Sort) + SubDep.Name
+ CONVERT(VARCHAR(MAX), SubDep.DepID) AS Sort,
ParentDep.Type + 1
FROM Departments SubDep
INNER JOIN Hierarchy ParentDep ON SubDep.ParentID = ParentDep.DepID
)
-- Final select
SELECT DepID,
-- Now we need to put some spaces (or any other symbols) to make it
-- look-like hierarchy
REPLICATE(' ', Type - 1) + Name AS DepartmentName, ParentID, Sort
FROM Hierarchy
UNION
-- Default value
SELECT -1 AS DepID, 'None' AS DepartmentName, -2, ' ' AS Sort
-- Important to sort by this field to preserve correct Parent-Child hierarchy
ORDER BY Sort ASC

Other samples

Using stored procedure:
Working with hierarchical data in SQL Server databases
Plain select for limited nesting level:
More Trees & Hierarchies in SQL
Another one solution using CTE:
SQL Organization Chart

2. Last Date selection with grouping - using RANK() OVER
Imagine some Events table with ID, User, Date and Description columns. You need to select all last Events for each User. There is no guarantee that Event with higher ID has nearest Date.

What you can do is play around with INNER SELECT, MAX, GROUPING like this:

SELECT E.UserName, E.Description, E.Date
FROM Events E
INNER JOIN
(
SELECT UserName, MAX(Date) AS MaxDate FROM Events
GROUP BY UserName
) AS EG ON E.Date = EG.MaxDate

But I prefer use RANK OVER:

SELECT EG.UserName, EG.Description, EG.Date FROM
(
SELECT RANK() OVER(PARTITION BY UserName ORDER BY Date DESC) AS N,
E.UserName, E.Description, E.Date
FROM Events E
) AS EG WHERE EG.N = 1

It's more complicated, but it seems to be more correct for me.

3. Paging using TOP and NOT IN

DECLARE @RowNumber INT, @RecordsPerPage INT, @PageNumber INT
SELECT @RecordsPerPage = 6, @PageNumber = 7
SELECT TOP(@RecordsPerPage) * FROM [TableName]
WHERE ID NOT IN
(
SELECT TOP((@PageNumber-1)*@RecordsPerPage) ID
FROM [TableName]
ORDER BY Date ASC
)
ORDER BY Date ASC

4. Set variable values in dynamic SQL with REPLACE

Instead of ugly

SET @SELECT_SQL = 'SELECT * FROM [TableName]
WHERE Date < ' + CAST(@Date, VARCHAR) + ' AND Flag = ' + @Flag

It's more easy, safe and readable to use REPLACE:

DECLARE @VAR_SQL VARCHAR(3000), @SELECT_SQL VARCHAR(3000)
DECLARE @Id INT
SET @Id = 3
DECLARE @Flag VARCHAR(1)
SET @Flag = 'X'
DECLARE @Date DATETIME
SET @Date = GETDATE()
SET @VAR_SQL =
'DECLARE @Date DATETIME
SET @Date = CAST(:Date AS DATETIME)
'
SET @SELECT_SQL = 'SELECT * FROM [TableName]
WHERE Id > :Id AND Flag = :Flag AND Date < @Date'

SET @SELECT_SQL =
REPLACE(@SELECT_SQL, ':Flag', QUOTENAME(CONVERT(VARCHAR, @Flag),''''))
SET @SELECT_SQL = REPLACE(@SELECT_SQL, ':Id', CONVERT(VARCHAR, @Id))
SET @VAR_SQL =
REPLACE(@VAR_SQL, ':Date', QUOTENAME(CONVERT(VARCHAR, @Date),''''))

PRINT(@VAR_SQL + @SELECT_SQL)
EXEC(@VAR_SQL + @SELECT_SQL)

5. DROP before CREATE
There are some good practices for writing stored procedures or functions, one of them is to include IF EXISTS ... DROP block in procedure creation script.

IF EXISTS
(
SELECT 1 FROM sysobjects
WHERE id = OBJECT_ID(N'[ProcedureName]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [ProcedureName]
GO

IF EXISTS
(
SELECT 1 FROM sysobjects
WHERE id = OBJECT_ID(N'[ScalarFunctionName]')
AND OBJECTPROPERTY(id, N'IsScalarFunction') = 1
)
DROP FUNCTION [ScalarFunctionName]
GO

IF EXISTS
(
SELECT 1 FROM sysobjects
WHERE id = OBJECT_ID(N'[TableFunctionName]')
AND OBJECTPROPERTY(id, N'IsTableFunction') = 1
)
DROP FUNCTION [TableFunctionName]
GO

--Talking about temporary tables:
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP(ID INT, DATESTART DATETIME, DATEEND DATETIME)

6. Lot of dynamic sql, temp tables, and others on Erland Sommarskog's home page