Листинги и результаты их работы

Листинг 1. Пример использования CTE-выражения

USE AdventureWorks ;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT * 
FROM DirReps 
ORDER BY ManagerID;
GO

Листинг 2. Использование CTE для прохода по дереву

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
 SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
 FROM HumanResources.Employee
WHERE ManagerID IS NULL
    UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID 
)
SELECT * 
FROM DirectReports ;
GO

Листинг 3. Операция Pivot

SELECT [SalesPersonID], [776], [777], [774]
FROM 
(SELECT [OrderQty] ,[ProductID],[SalesPersonID]
  FROM [AdventureWorks].[Sales].[SalesOrderDetail] as od
JOIN [AdventureWorks].[Sales].[SalesOrderHeader] as o
ON o.[SalesOrderID] = od.[SalesOrderID] ) as p
PIVOT
(
COUNT ([OrderQty])
FOR [ProductID] IN ( [776], [777], [774])
) AS pvt

Листинг 4. Пример UNPIVOT

SELECT [SalesPersonID], ProductID, Quantity
FROM	(SELECT [SalesPersonID],[776] ,[777] ,[774]
		FROM [AdventureWorks].[Sales].[tmpUnpivot]) pvt
UNPIVOT (Quantity FOR ProductID IN ([776], [777], [774])
) unpvt

Листинг 5. Создание временной таблицы

DECLARE @MyTableVar  TABLE	(ScrapReasonID smallint, 
				Name varchar(50), 
				ModifiedDate datetime);

Листинг 6. Вставка данных в таблицу

INSERT INTO Production.ScrapReason
 OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar
VALUES (N?Operator error?, GETDATE());

Листинг 7. Просмотр содержимого таблицы

@MyTableVar
SELECT ScrapReasonID, Name, ModifiedDate 
FROM @MyTableVar;

Листинг 8. Создание табличной функции

CREATE FUNCTION Sales.MostRecOrders(@intCustomerID as int)
RETURNS TABLE AS
RETURN
	SELECT TOP (2) SalesOrderID, OrderDate
	FROM Sales.SalesOrderHeader
WHERE CustomerID = @intCustomerID
ORDER BY OrderDate DESC

Листинг 9. Запрос на CROSS-объединение

 SELECT Name, mro.*
FROM Sales.Store
CROSS APPLY Sales.MostRecOrders(CustomerID) as mro

Листинг 10. Запрос на OUTER-объединение

SELECT Name, mro.*
FROM Sales.Store
OUTER APPLY Sales.MostRecOrders(CustomerID) as mro

Листинг 11. Определение места товара внутри категории по его цене

SELECT 
ROW_NUMBER() OVER(PARTITION BY psc.Name ORDER BY p.ListPrice DESC) as ?Price Rank?,
psc.Name as Category, p.Name as Product, p.ListPrice
FROM Production.Product as p
INNER JOIN Production.ProductSubcategory psc
ON p.ProductSubcategoryID = psc.ProductSubcategoryID

Листинг 12. Использование функции NTILE()

SELECT 
NTILE(3) OVER(PARTITION BY psc.Name ORDER BY p.ListPrice DESC) as ?Price Band?,
psc.Name as Category, p.Name as Product, p.ListPrice
FROM Production.Product as p
INNER JOIN Production.ProductSubcategory psc
ON p.ProductSubcategoryID = psc.ProductSubcategoryID

Листинг 13. Пример использования TOP

USE AdventureWorks ;
DECLARE @var1 AS int, @var2 AS int
SET @var1 =  4
SET @var2 =  2
SELECT TOP(@var1*@var2) *
FROM HumanResources.Employee;

Листинг 14. Пример использования TABLESAMPLE

USE AdventureWorks ;
GO
SELECT *
FROM HumanResources.Employee 
TABLESAMPLE (10 ROWS); 
GO

Листинг 15. Хранимая процедура с новым обработчиком исключений

CREATE   PROCEDURE  usp_someproc
AS
BEGIN
   BEGIN  TRY
     SELECT   *   FROM titles
  END   TRY
    BEGIN CATCH 
     SELECT ERROR_NUMBER()
   END CATCH 
END GO

Листинг 16. Создадим пробную таблицу

USE [AdventureWorks]
GO
CREATE TABLE [Sales].[testDDLTrigger](
	[ID] int NULL,
	[Description] varchar(100) NULL
)
GO

Листинг 17. Создание триггера

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT ?Таблицы не могу быть модифицированы или удалены?;
   ROLLBACK TRAN;
GO

Листинг 18. Попытка удалить таблицу

DROP TABLE [Sales].[testDDLTrigger]