Wednesday, September 3, 2008

SQL Server 2005 ranking functions

SELECT SalesOrderID, SubTotal,
ROW_NUMBER() OVER(ORDER BY SubTotal DESC) AS RowNumber,
RANK() OVER(ORDER BY SubTotal DESC) AS Rank,
DENSE_RANK() OVER(ORDER BY SubTotal DESC) AS DenseRank,
NTILE(3) OVER(ORDER BY SubTotal DESC) AS NTile
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN (51885, 51886, 52031, 56410,
56411, 72585, 65101, 65814, 73976)
ORDER BY SubTotal DESC;


------------------------------------------------------------------------------------------

- ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }
- OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }
- ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }
- Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }
- What's New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }
- AdventureWorks - SQL Server 2005 Samples and Sample Databases (July 2006) { http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en }
- WITH common table expression { http://msdn2.microsoft.com/en-us/library/ms175972.aspx }

PIVOT Query

USE AdventureWorks;
GO
CREATE PROC MonthlyPurchaseOrder @Year int
AS
BEGIN
DECLARE @MonthlyPurchaseOrders TABLE
(VendorName nvarchar(50),
OrderMonth int,
TotalDue Money)
INSERT @MonthlyPurchaseOrders
SELECT v.Name, DATEPART(Month, OrderDate), TotalDue
FROM Purchasing.Vendor v
JOIN Purchasing.PurchaseOrderHeader poh
ON v.VendorID = poh.VendorID
WHERE v.Name like ('[c-p]%')
AND YEAR(OrderDate)=@Year
SELECT VendorName, Jan='$'+convert(varchar,[1],1),
Feb='$'+convert(varchar,[2],1), Mar='$'+convert(varchar,[3],1), Apr='$'+convert(varchar,[4],1), May ='$'+convert(varchar,[5],1),
Jun='$'+convert(varchar,[6],1), Jul='$'+convert(varchar,[7],1), Aug='$'+convert(varchar,[8],1), Sep ='$'+convert(varchar,[9],1),
Oct='$'+convert(varchar,[10],1), Nov='$'+convert(varchar,[11],1), Dec='$'+convert(varchar,[12],1)
FROM @MonthlyPurchaseOrders
PIVOT (SUM(TotalDue) FOR OrderMonth In (
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS CrossTab
END
GO
EXEC MonthlyPurchaseOrder 2004
GO

What is CTE ?

Create Table tblCategories
(
CategoryID Int Constraint PK_tblCategories_CategoryID Primary Key,
CategoryName VarChar(100),
ParentCategoryID Int Constraint FK_tblCategories_ParentCategoryID References tblCategories(CategoryID)
)
------------------------------------------------------------------
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(1,'Languages',Null)
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(2,'Networking',Null)
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(3,'Databases',Null)
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(4,'Visual Basic',1)
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(5,'C#',1)
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(6,'Java',1)
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(7,'VB.Net',4)
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(8,'VB 6.0',4)
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(9,'Desktop Application Development with VB.Net',7)
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(10,'Web Application Development with VB.Net',7)
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(11,'ActiveX Objects and VB 6.0',8)
Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(12,'Network Security',2)
------------------------------------------------------------------
Select * From tblCategories Where CategoryID = 1
------------------------------------------------------------------
With cteCategories
AS (
Select CategoryID,CategoryName,ParentCategoryID
From tblCategories
Where CategoryID=1
Union All
Select C.CategoryID,C.CategoryName,C.ParentCategoryID
From tblCategories As C Inner Join cteCategories As P On C.ParentCategoryID = P.CategoryID)
Select CategoryID,CategoryName,ParentCategoryID From cteCategories

Tuesday, August 19, 2008

INSTEAD OF INSERT Triggers

CREATE TABLE BaseTable
(PrimaryKey int PRIMARY KEY IDENTITY(1,1),
Color nvarchar(10) NOT NULL,
Material nvarchar(10) NOT NULL,
ComputedCol AS (Color + Material)
)
GO
--Create a view that contains all columns from the base table.
CREATE VIEW InsteadView
AS SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable
GO
--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
--Build an INSERT statement ignoring inserted.PrimaryKey and
--inserted.ComputedCol.
INSERT INTO BaseTable
SELECT Color, Material
FROM inserted
END
GO

DDL TRIGGER

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK ;
---------------------------
create table mytable
(
id int,
nam char(10)
)
---------------------------
drop table mytable
---------------------------
drop trigger safety on database
-----------------------------
drop table mytable

Monday, August 18, 2008

DBCC SHRINKFILE (Transact-SQL)

A. Shrinking a data file to a specified target size

The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

B. Shrinking a log file to a specified target size

The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO

C. Truncating a data file

The following example truncates the primary data file in the AdventureWorks database. The sys.database_files catalog view is queried to obtain the file_id of the data file.

USE AdventureWorks;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D. Emptying a file

The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.

USE AdventureWorks;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks
ADD FILE (
NAME = Test1data,
FILENAME = 'C:\t1data.ndf',
SIZE = 5MB
);
GO
-- Empty the data file.
DBCC SHRINKFILE ('Test1data', EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks
REMOVE FILE Test1data;
GO