MCSA SQL Server1／SARGBLE(サーガブル)とビルドイン・ファンクション

【Build-inFunctions（ビルドイン・ファンクション）】

USE AdventureWorks;
GO

/* logical functions -- CHOOSE & IIF */勝手にリターン値から値の変換をしてくれるんで助かります。
SELECT JobTitle, HireDate, MONTH(HireDate) AS HireMonth,
  CHOOSE(MONTH(HireDate),
  '1月','２月',３月',
  '４月','５月','6月',
  '7月','8月','9月',
  '10月','11月','12月') AS HireSeason,
  IIF(Gender='F', 'Female', 'Male') AS Gender
FROM HumanResources.Employee
ORDER BY HireMonth;

/* conversion functions -- CAST & CONVERT */日付は気を付けて値を出しましょう。
SELECT 'Today''s date is ' + GETDATE();これはエラーなる
SELECT 'Today''s date is ' + CAST(GETDATE() AS NVARCHAR);ISOのスタンダード
SELECT 'Today''s date is ' + CONVERT(NVARCHAR, GETDATE(), 101);「101」のフォーマット

 -- implicit　勝手にデータフォーマットをコンバートする
SELECT p.Name, pinv.Quantity, p.StandardCost, pinv.Quantity * p.StandardCost AS TotalCost
FROM Production.Product p
JOIN Production.ProductInventory pinv ON p.ProductID = pinv.ProductID
WHERE p.StandardCost > 0.00;

-- explicit　CASTでコンバートする
SELECT p.Name, pinv.Quantity AS OldQuantity, (pinv.Quantity * .5) AS OopsQuantity, CAST(pinv.Quantity * .5 AS SMALLINT) NewQuantity
FROM Production.Product p
JOIN Production.ProductInventory pinv ON p.ProductID = pinv.ProductID;

/* string functions */左から一つ目の文字をとります。
-- LEFT & RIGHT
SELECT FirstName, LastName, LEFT(FirstName, 1) + LEFT(LastName, 1) as Initials
FROM Person.Person;

-- CHARINDEX & PATINDEX　CHARINDEXは’city’を探して最初のインデックス値を数字で返します。マジックナンバーが頭に使えません。PATINDEXは使えます。
SELECT DISTINCT City, CHARINDEX('city', City) as CityStartsAt, PATINDEX('%town%', City) as TownStartsAt
FROM Person.Address WHERE CHARINDEX('city', City) > 0 OR PATINDEX('%town%', City) > 0;

-- SUBSTRING, REPLACE, REVERSE　インデックスは『0』から始まるので『１』を足します。『LEFT』『SUBSTRING』『REPLACE』『REVERSE』いろいろ調べてみてください。
SELECT ProductNumber, 
  LEFT(ProductNumber, 2) AS ProductCode,
  SUBSTRING(ProductNumber, CHARINDEX('-', ProductNumber, 0) + 1, 4) as PartialProductNumber,
  REPLACE(ProductNumber,'-','') as NewProductNumber,
  REVERSE(ProductNumber) as BackwardsProductNumber
FROM Production.Product;

/*  datetime functions */
-- DATENAME　日付
SELECT DISTINCT OrderDate, 
  DATENAME(YEAR, OrderDate) AS OrderYear, 
  DATENAME(MONTH, OrderDate) AS OrderMonth,
  DATENAME(WEEK, OrderDate) AS OrderWeek,
  DATENAME(DAY, OrderDate) AS OrderDay,
  DATENAME(DAYOFYEAR, OrderDate) AS OrderDayOfYear
FROM Sales.SalesOrderHeader;

-- DATEDIFF & DATEADD　5年以上働いているものを出力　サーガブルのためにDIFFDATEを使わないようにしましょう。
SELECT LoginID, HireDate, DATEDIFF(YEAR, HireDate, GETDATE()) YearsEmployed
FROM HumanResources.Employee
WHERE HireDate <=  DATEADD(YEAR, -5, GETDATE())
ORDER BY YearsEmployed DESC

/* system functions */テストでは、『ISNULL』と『IFNULL』を取り違えて騙そうとしてきます。
-- ISNULL　もしNULLだったら出力を’N/A’にする
SELECT Name, ISNULL(Color, 'N/A') AS Color FROM Production.Product;

-- NEWID
SELECT NEWID();

-- SUSER_SNAME, USER_NAME, HOST_NAME
SELECT SUSER_SNAME() AS SystemUserName, USER_NAME() AS DatabaseUserName, HOST_NAME() AS MachineName;

 

【SARGBLE（サーガブル）】

USE AdventureWorks;
GO

-- Not SARG-able :(　一つ一つのデータを読み込んで出力するのでたいへん！
SELECT SalesOrderID, OrderDate, DATEDIFF(YEAR, OrderDate, GETDATE()) as OrderAgeYears
FROM Sales.SalesOrderHeader
WHERE DATEDIFF(YEAR, OrderDate, GETDATE()) >= 5;

--SARG-able! :)　これは先に比較するものを作成してそこに照らし合わせるのでサーガブル
SELECT SalesOrderID, OrderDate, DATEDIFF(YEAR, OrderDate, GETDATE())as  OrderAgeYears
FROM Sales.SalesOrderHeader
WHERE OrderDate <= DATEADD(YEAR, -5, GETDATE());

-- Not SARG-able :(　インデックスを数えるのに時間がかかりすぎる
SELECT FirstName, LastName
FROM Person.Person
WHERE CHARINDEX('pow', LastName) > 0;

--SARG-able! :)　ライクはSEEKで探してくれるのでサーガブル
SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE LastName LIKE 'pow%';