SQL SERVER FUNCTION

--傳回平均值

-- Average Product Review by Product
SELECT ProductID,
AVG(Rating) AvgRating
FROM Production.ProductReview
GROUP BY ProductID

-- Average DISTINCT Standard Cost
SELECT AVG(DISTINCT StandardCost) AvgDistinctStandardCost
FROM Production.ProductCostHistory

--傳回列數

SELECT Shelf,
COUNT(ProductID) ProductCount
FROM Production.ProductInventory
GROUP BY Shelf
ORDER BY Shelf

SELECT COUNT(DISTINCT Shelf) ShelfCount
FROM Production.ProductInventory

--找出運算式中的最低與最高值

SELECT MIN(Rating) MinRating,
MAX(Rating) MaxRating
FROM Production.ProductReview

--傳回資料值總和

SELECT AccountNumber,
SUM(TotalDue) TotalDueBySalesOrderID
FROM Sales.SalesOrderHeader
GROUP BY AccountNumber
ORDER BY AccountNumber

--使用統計彙總函數

SELECT VAR(TaxAmt) Variance_Sample,
VARP(TaxAmt) Variance_EntirePopulation
FROM Sales.SalesOrderHeader

SELECT STDEV(UnitPrice) StandDevUnitPrice,
STDEVP(UnitPrice)StandDevPopUnitPrice
FROM Sales.SalesOrderDetail

--數學函數的運用

SELECT POWER(10,2) Result

SELECT SQRT(100) Result

SELECT ROUND(3.22245, 3) RoundedNumber

SELECT RAND() RandomNumber

SELECT RAND(22) Result

--將字元資料值轉換成 ASCII, 再轉回字元

SELECT ASCII('H'), ASCII('e'), ASCII('l'), ASCII('l'), ASCII('o')

SELECT CHAR(72), CHAR(101), CHAR(108), CHAR(108), CHAR(111)

--傳回整數與字元 Unicode 資料值

SELECT UNICODE('G'), UNICODE('o'), UNICODE('o'), UNICODE('d'), UNICODE('!')

SELECT NCHAR(71), NCHAR(111), NCHAR(111), NCHAR(100), NCHAR(33)

--找出某字串內的另一個字串起始位置

SELECT CHARINDEX('String to Find', 'This is the bigger string to find something in.')

--使用萬用字元找出某字串內的另一個字串起始位置

SELECT AddressID,
AddressLine1
FROM Person.Address
WHERE PATINDEX('%olive%', AddressLine1) > 0

--判斷字串的相似度

SELECT SOUNDEX('Fleas'),
SOUNDEX('Fleece'),
SOUNDEX('Peace'),
SOUNDEX('Peas')

SELECT DIFFERENCE ( 'Fleas', 'Fleece')

SELECT DIFFERENCE ( 'Fleece', 'Peace')

--將字串的最左側或最右側部分取出

SELECT LEFT('I only want the leftmost 10 characters.', 10)

SELECT RIGHT('I only want the rightmost 10 characters.', 10)

-- Padding a number for business purposes
SELECT TOP 3
ProductID, RIGHT('0000000000' + CONVERT(varchar(10), ListPrice),10)
FROM Production.Product
WHERE ListPrice > 0

--判斷字串中的字元或位元數

SELECT LEN(N'She sells sea shells by the sea shore.')

SELECT DATALENGTH(N'She sells sea shells by the sea shore.')

--用某個字串取代另一個字串中的段落

SELECT REPLACE('Zenon is our major profit center. Zenon leads the way.',
'Zenon',
'Xerxes')

--將字串植入另一個字串當中

SELECT STUFF ( 'My cat''s name is X. Have you met him?',
18,
1,
'Edgar' )

--將字元值改成小寫、大寫, 或適當的字體

SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 4

SELECT LOWER(DocumentSummary)
FROM Production.Document
WHERE DocumentID = 4

SELECT UPPER(DocumentSummary)
FROM Production.Document
WHERE DocumentID = 4

CREATE FUNCTION udf_ProperCase(@UnCased varchar(max))
RETURNS varchar(max)
AS
BEGIN
SET @UnCased = LOWER(@UnCased)
DECLARE @C int
SET @C = ASCII('a')
WHILE @C <= ASCII('z')
BEGIN
SET @UnCased = REPLACE( @UnCased, ' ' + CHAR(@C), ' ' + CHAR(@C-32))
SET @C = @C + 1
END
SET @UnCased = CHAR(ASCII(LEFT(@UnCased, 1))-32) + RIGHT(@UnCased,
LEN(@UnCased)-1)
RETURN @UnCased
END
GO

SELECT dbo.udf_ProperCase(DocumentSummary)
FROM Production.Document
WHERE DocumentID = 4

--移除開頭與結尾的空白

SELECT LTRIM(' String with leading blanks.')

SELECT RTRIM('"' + 'String with trailing blanks ') + '"'

--將某個運算式重複 N 次

SELECT REPLICATE ('Z', 30)

--將空白重複 N 次

SELECT 'Give me some' + SPACE(6) + 'space.'

--將某個運算式顛倒輸出

SELECT TOP 1
GroupName,
REVERSE(GroupName) GroupNameReversed
FROM HumanResources.Department
ORDER BY GroupName

--傳回運算式的一小段

DECLARE @BankAccountNumber char(14)
SET @BankAccountNumber = '1424-2342-3536'
SELECT 'XXXX-' + SUBSTRING(@BankAccountNumber, 6,4) + '-XXXX' Masked_BankAccountNumber

--用其他資料值取代 NULL 資料值

SELECT LastName,
ISNULL(Title, 'UNKNOWN') Title
FROM Person.Contact
WHERE LastName LIKE 'Sa%'

--利用 ISNULL 進行彈性化搜尋

-- Local variables used for searches
DECLARE @ProductID int
DECLARE @StartDate datetime
DECLARE @StandardCost money

-- Only @ProductID is used
SET @ProductID = 711

SELECT ProductID, StartDate, StandardCost
FROM Production.ProductCostHistory
WHERE ProductID = ISNULL(@ProductID, ProductID) AND
StartDate = ISNULL(@StartDate, StartDate) AND
StandardCost = ISNULL(@StandardCost, StandardCost)

-- Local variables used for searches
DECLARE @ProductID int
DECLARE @MinStandardCost money
DECLARE @MaxStandardCost money

SET @MinStandardCost = 3.3963
SET @MaxStandardCost = 10.0000

SELECT ProductID, StartDate, StandardCost
FROM Production.ProductCostHistory
WHERE ProductID = ISNULL(@ProductID, ProductID) AND
StandardCost BETWEEN ISNULL(@MinStandardCost, StandardCost) AND
ISNULL(@MaxStandardCost, StandardCost)
ORDER BY StandardCost

--傳回一連串運算式的第一個非 NULL 值

DECLARE @Value1 int
DECLARE @Value2 int
DECLARE @Value3 int
SET @Value2 = 22
SET @Value3 = 955
SELECT COALESCE(@Value1, @Value2, @Value3)

--當兩個運算式相同時傳回 NULL 值；否則傳回第一個運算式

DECLARE @Value1 int
DECLARE @Value2 int
SET @Value1 = 55
SET @Value2 = 955
SELECT NULLIF(@Value1, @Value2)

DECLARE @Value1 int
DECLARE @Value2 int
SET @Value1 = 55
SET @Value2 = 55
SELECT NULLIF(@Value1, @Value2)

--傳回現在日期與時間

SELECT GETDATE(), -- Current Date and Time
CURRENT_TIMESTAMP, -- Current Date and Time
GETUTCDATE() -- Universal Time Coordinate or Greenwich Mean Time

--將日期資料值延後或提前

SELECT DATEADD(yy, -1, '4/2/2005')

SELECT DATEADD(q, 1, '4/2/2005')

SELECT DATEADD(mm, -6, '4/2/2005')

SELECT DATEADD(d, 50, '4/2/2005')

SELECT DATEADD(mi, -30, '2005-09-01 23:30:00.000')

--找出兩個日期的差距

-- Find difference in months between now and EndDate
SELECT ProductID,
EndDate,
DATEDIFF(m, EndDate, GETDATE()) MonthsFromNow
FROM Production.ProductCostHistory
WHERE EndDate IS NOT NULL

--顯示部分日期的字串值

-- Show the EndDate's day of the week
SELECT ProductID,
EndDate,
DATENAME(dw, EndDate) WeekDay
FROM Production.ProductCostHistory
WHERE EndDate IS NOT NULL

--使用 DATEPART 顯示部分日期的整數值

SELECT DATEPART(yy, GETDATE())

SELECT DATEPART(m, GETDATE())

--使用 YEAR、MONTH 與 DAY 顯示部分日期的整數值

 

SELECT YEAR(GETDATE())

SELECT MONTH(GETDATE())

SELECT DAY(GETDATE())

--轉換日期型態

SELECT 2000 + 'Cannot be concatenated'
GO

SELECT CONVERT(char(4), 2005) + ' Can now be concatenated!'

SELECT EmployeeID, CAST(SickLeaveHours AS char(4)) + ' Sick Leave Hours Left'
FROM HumanResources.Employee

--進行日期轉換

SELECT CONVERT(varchar(20), GETDATE(), 101)

SELECT CONVERT(datetime, CONVERT( varchar(11), '2005-08-13 20:37:22.570', 101))

--檢查運算式屬於日期或數字型態

-- Returns 0
SELECT ISDATE('1/1/20000')
-- Returns 1
SELECT ISDATE('1/1/2000')
-- Returns 0
SELECT ISNUMERIC('123ABC')
-- Returns 1
SELECT ISNUMERIC('123')

--使用遞增列號

-- Select the rows 255 through 260 in the middle of the result set
SELECT p.ProductID,
p.Name,
p.RowNumber
FROM
(SELECT ProductID,
Name,
ROW_NUMBER() OVER (ORDER BY Name) RowNumber
FROM Production.Product) p
WHERE p.RowNumber BETWEEN 255 AND 260

SELECT Shelf,
ProductID,
ROW_NUMBER() OVER
(PARTITION BY Shelf ORDER BY ProductID) RowNumber
FROM Production.ProductInventory

--根據名次傳回資料列

SELECT SalesPersonID,
SalesQuota,
RANK() OVER (ORDER BY SalesQuota DESC) as RANK
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota BETWEEN 266000.00 AND 319000.00

SELECT h.SalesPersonID,
s.TerritoryID,
h.QuotaDate,
h.SalesQuota,
RANK() OVER (PARTITION BY s.TerritoryID ORDER BY h.SalesQuota DESC) as RANK
FROM Sales.SalesPersonQuotaHistory h
INNER JOIN Sales.SalesPerson s ON
h.SalesPersonID = s.SalesPersonID
WHERE s.TerritoryID IN (5,6,7)

--依緊鄰名次傳回資料列

SELECT SalesPersonID,
SalesQuota,
DENSE_RANK() OVER (ORDER BY SalesQuota DESC) as DENSE_RANK
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota BETWEEN 266000.00 AND 319000.00

--使用 NTILE

SELECT SalesPersonID,
SalesQuota,
NTILE(4) OVER (ORDER BY SalesQuota DESC) as NTILE
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota BETWEEN 266000.00 AND 319000.00

--使用 SQL Server 的當週首日設定

SELECT @@DATEFIRST 'First Day of the Week'

--檢視現有連線所使用的語系
SELECT @@LANGID LanguageID,
@@LANGUAGE Language

--檢視並設定現有連線的鎖定逾時值

-- 1000 milliseconds, 1 second
SET LOCK_TIMEOUT 1000
SELECT @@LOCK_TIMEOUT
-- Unlimited
SET LOCK_TIMEOUT -1

--顯示現有預存程序 Context 的巢狀深度

-- First procedure
CREATE PROCEDURE usp_QuickAndDirty
AS
SELECT @@NESTLEVEL
GO

-- Second procedure
CREATE PROCEDURE usp_Call_QuickAndDirty
AS
SELECT @@NESTLEVEL
EXEC usp_QuickAndDirty
GO

-- Returns 0 nest level
SELECT @@NESTLEVEL
-- Returns 1 and 2 nest level
EXEC usp_Call_QuickAndDirty

--傳回現有 SQL Server 執行個體的名稱及 SQL Server 版本

SELECT @@SERVERNAME ServerName,
@@VERSION VersionInformation

--傳回現有連線的 Session ID(SPID)

SELECT @@SPID SPID

--傳回已開啟交易的數量

BEGIN TRAN t1
SELECT @@TRANCOUNT -- Returns 1
BEGIN TRAN t2
SELECT @@TRANCOUNT -- Returns 2
BEGIN TRAN t3
SELECT @@TRANCOUNT -- Returns 3
COMMIT TRAN
SELECT @@TRANCOUNT -- Returns 2
ROLLBACK TRAN
SELECT @@TRANCOUNT -- After ROLLBACK, always Returns 0!

--取得前一個敘述所影響的資料列數

SELECT TOP 3 ScrapReasonID
FROM Production.ScrapReason

--系統統計函數的運用

SELECT 'Connections' FunctionNM, @@CONNECTIONS
UNION
SELECT 'CPUBusy', @@CPU_BUSY
UNION
SELECT 'IDLE', @@IDLE
UNION
SELECT 'IOBusy', @@IO_BUSY
UNION
SELECT 'PacketErrors', @@PACKET_ERRORS
UNION
SELECT 'PackReceived', @@PACK_RECEIVED
UNION
SELECT 'PackSent', @@PACK_SENT
UNION
SELECT 'TimeTicks', @@TIMETICKS
UNION
SELECT 'TotalErrors', @@TOTAL_ERRORS
UNION
SELECT 'TotalRead', @@TOTAL_READ
UNION
SELECT 'TotalWrite', @@TOTAL_WRITE

--顯示資料庫與 SQL Server 的設定值

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation'),
DATABASEPROPERTYEX('AdventureWorks', 'Recovery'),
DATABASEPROPERTYEX('AdventureWorks', 'Status')

SELECT SERVERPROPERTY ('Collation'),
SERVERPROPERTY ('Edition')

--傳回現有資料庫的 ID 與名稱

SELECT DB_ID() DatabaseID, DB_NAME() DatabaseNM

--傳回資料庫物件名稱與 ID

SELECT OBJECT_ID('Production.Location'), OBJECT_NAME(1253579504)

--傳回現有使用者連線的應用程式及主機

SELECT APP_NAME() as 'Application', HOST_ID() as 'Host ID', HOST_NAME() as 'Host Name'

--回報現有使用者及登入(Login)的內容

SELECT SYSTEM_USER, -- Login
USER -- Database User

 

CREATE TABLE #TempExample
(ExampleColumn varchar(10) NOT NULL,
ModifiedByLogin varchar(55) NOT NULL DEFAULT SYSTEM_USER,
ModifiedByUser varchar(55) NOT NULL DEFAULT USER)
GO

INSERT #TempExample
(ExampleColumn)
VALUES ('Value A')

SELECT ExampleColumn, ModifiedByLogin, ModifiedByUser
FROM #TempExample

--檢視使用者連線選項
SELECT SESSIONPROPERTY ('ANSI_NULLS') ANSI_NULLS,
SESSIONPROPERTY ('ANSI_PADDING') ANSI_PADDING,
SESSIONPROPERTY ('ANSI_WARNINGS') ANSI_WARNINGS,
SESSIONPROPERTY ('ARITHABORT') ARITHABORT,
SESSIONPROPERTY ('CONCAT_NULL_YIELDS_NULL') CONCAT_NULL_YIELDS_NULL,
SESSIONPROPERTY ('NUMERIC_ROUNDABORT') NUMERIC_ROUNDABORT,
SESSIONPROPERTY ('QUOTED_IDENTIFIER') QUOTED_IDENTIFIER

--傳回最後一個識別值

SELECT IDENT_CURRENT('Production.Product') LastIdententityValue

-- Example insert, generates IDENTITY value in the table
INSERT HumanResources.Department
(Name, GroupName)
VALUES ('TestDept', 'TestGroup')

-- Last identity value generated for any table
-- in the current session, for the current scope
SELECT SCOPE_IDENTITY()

-- Last identity value generated for any table
-- in the current session, in any scope
SELECT @@IDENTITY

--傳回識別資料欄的種子值與遞增值

SELECT IDENT_INCR('Production.Product') IdentIncr,
IDENT_SEED('Production.Product') IdentSeed

--製作新的唯一識別值

SELECT NEWID()