A. Stored Procedure
B. View
C. Inline Table Valued Function (ITVF)-UDF-Table Defined Function
D. Multi-statement Table Valued Function (MSTVF)-UDF-Table Defined Function
E. Scalar Function
F. Common Table Expression（CTE）
G. Derived Tables
H. Indexed View (Clustered Index)
I. Indexed View (Non-clustered Index)
1.
create procedure dbo.usp_GetClients (
@clientName nvarchar(max) = null
)
as
begin;
select *
from dbo.Clients as a
where ((a.ClientName = @clientName) or a.ClientName is null)
end;
EXEC dbo.usp_GetClients('beerneko')
2.
CREATE VIEW hiredate_view
AS
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate
FROM HumanResources.Employee e
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID;
SELECT * FROM hiredate_view
３．
CREATE VIEW product_master
WITH SCHEMABINDING
AS
SELECT
product_id,
product_name,
model_year,
list_price,
brand_name,
category_name
FROM
production.products p
INNER JOIN production.brands b
ON b.brand_id = p.brand_id
INNER JOIN production.categories c
ON c.category_id = p.category_id;
SELECT * FROM product_master
４．
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
SELECT * FROM Sales.ufn_SalesByStore (602);
５．
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT)
RETURNS @TREE TABLE
(
empid INT NOT NULL
,empname VARCHAR(25) NOT NULL
,mgrid INT NULL
,lvl INT NOT NULL
)
AS
BEGIN
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid
UNION all
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM Employees AS e
JOIN Employees_Subtree AS es
ON e.mgrid = es.empid
)
INSERT INTO @TREE
SELECT * FROM Employees_Subtree;
RETURN
END
GO
SELECT D.deptid,
D.deptname,
D.deptmgrid,
ST.empid,
ST.empname,
ST.mgrid
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;
６．
CREATE VIEW product_master
WITH SCHEMABINDING
AS
SELECT
product_id,
product_name,
model_year,
list_price,
brand_name,
category_name
FROM
production.products p
INNER JOIN production.brands b
ON b.brand_id = p.brand_id
INNER JOIN production.categories c
ON c.category_id = p.category_id;
SELECT * FROM product_master
７．
CREATE FUNCTION GetDiseasedPatientsNumber (@DiseaseCode INT)
RETURNS INT
AS
BEGIN
DECLARE @Number_of_Patients INT
SELECT @Number_of_Patients = COUNT(PatientID)
FROM [dbo].[Patients]
WHERE DiseaseCode = @DiseaseCode
RETURN @Number_of_Patients
END
GO
SELECT ProviderID
,[Provider Name]
,[Clinic Code]
,[dbo].[GetDiseasedPatientsNumber](DCode) AS 'Diabetes Patients Count'
FROM [dbo].[Provider]
８．
WITH cte_sales_amounts (staff, sales, year)
AS (
SELECT
first_name + ' ' + last_name,
SUM(quantity * list_price * (1 - discount)),
YEAR(order_date)
FROM
sales.orders o
INNER JOIN sales.order_items i ON i.order_id = o.order_id
INNER JOIN sales.staffs s ON s.staff_id = o.staff_id
GROUP BY
first_name + ' ' + last_name,
year(order_date)
)
SELECT
staff,
sales
FROM
cte_sales_amounts
WHERE
year = 2018;
９．
SELECT enter_month
FROM (
SELECT MONTH(enter_date) as enter_month
FROM wroks_on
) as m
GROUP BY enter_month
１０．
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW dbo.product_master
WITH SCHEMABINDING
AS
SELECT COL001
,COL002
,COL003
,COL004
FROM dbo.TABLE_A
CREATE UNIQUE CLUSTERED INDEX PK_idxProduct_master
ON dbo.product_master ( COL001,COL002 )
１１．
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW dbo.product_master
WITH SCHEMABINDING
AS
SELECT COL001
,COL002
,COL003
,COL004
FROM dbo.TABLE_A
CREATE NONCLUSTERED INDEX IDX_idxProduct_master
ON dbo.dbo.product_master ( COL003,COL004 )
１２．
CREATE FUNCTION GetDiseasedPatientsNumber (@DiseaseCode INT)
RETURNS INT
AS
BEGIN
DECLARE @Number_of_Patients INT
SELECT @Number_of_Patients = COUNT(PatientID)
FROM [dbo].[Patients]
WHERE DiseaseCode = @DiseaseCode
RETURN @Number_of_Patients
END
GO
SELECT ProviderID
,[Provider Name]
,[Clinic Code]
FROM [dbo].[Provider]
WHERE [dbo].[GetDiseasedPatientsNumber](DiseaseCode) >= 150