-------------------------------------------------
-- Batches
-- Create a table and a view
-------------------------------------------------
--This causes an error
USE
tempdb;
CREATE
TABLE dbo.Test
(
ID
TestName
);
int NOT NULL, varchar(50) NOT NULL
CREATE
VIEW dbo.vwTest
AS
SELECT TestName FROM dbo.Test;
--Creating multiple dependent objects requires a GO separator.
USE
tempdb;
CREATE
TABLE dbo.Test
(
ID
TestName
);
int NOT NULL, varchar(50) NOT NULL
GO
CREATE
VIEW dbo.vwTest
AS
SELECT TestName FROM dbo.Test;
GO
------------------
-- Using Variables
------------------
Use
Northwind;
DECLARE
@local char(13);
SET
@local = 'Local Phone: ';
-- Or --
SELECT
@local = 'Local Phone: ';
SELECT
@local AS Value;
SELECT
LastName, FirstName, @local + HomePhone AS Phone
FROM
dbo.Employees
ORDER
BY LastName, FirstName;
-------------------------
-- Using CAST and CONVERT
-------------------------
--This will give you an error
DECLARE
@msg varchar(20);
SELECT
@msg = 'The result is: ' + (2+2);
PRINT
@msg;
--Fix the problem using Cast or Convert
DECLARE
@msg varchar(20);
SELECT
@msg = 'The result is: ' +
CAST((2+2) AS varchar(1));
PRINT
@msg;
DECLARE
@msg varchar(20);
SELECT
@msg = 'The result is: ' +
CONVERT(varchar(1), (2+2));
PRINT
@msg;
-- This result may surprise you
DECLARE
@msg varchar(20);
SELECT
@msg = '1' + (2+2);
PRINT
@msg;
---------------------------
-- Using CONVERT with style
---------------------------
SELECT
GETDATE();
SELECT
CONVERT(varchar(20), GETDATE(), 1);
SELECT
CONVERT(varchar(20), GETDATE(), 101);
SELECT
CONVERT(varchar(20), GETDATE(), 102);
SELECT
CONVERT(varchar(20), GETDATE(), 126);
----------------------------------
-- Using STR to convert and format
----------------------------------
SELECT
UnitPrice, STR(UnitPrice, 6, 1) AS Formatted
FROM
dbo.Products
ORDER
BY UnitPrice DESC;
--==========================
-- Using Built-in Functions
--==========================
---------------------
-- Working with NULLs
---------------------
-- This returns no rows
-- unless you run:
-- SET ANSI_NULLS OFF
--------------------------
SELECT
CompanyName, City
FROM
dbo.Suppliers
WHERE
Region = NULL;
-- But this works reliably
--------------------------
SELECT
CompanyName, City
FROM
dbo.Suppliers
WHERE
Region IS NULL;
--------------------------------------
-- Using ISNULL to Replace Null Values
--------------------------------------
SELECT
CompanyName, City, ISNULL(Region, '???') AS Region
FROM
dbo.Suppliers;
----------------------------------
-- Using NULLIF to Convert Null Values
----------------------------------
SELECT
AVG(NULLIF(UnitPrice, 0)) FROM dbo.Products;
-- INSTEAD OF --
SELECT
AVG(UnitPrice) FROM dbo.Products;
----------------------------------
-- Using COALESCE to find
-- the first non-NULL value
-- in a series of expressions
----------------------------------
SELECT
COALESCE(3+NULL, 2*NULL, 5*2, 7);
SELECT
CompanyName,
City
+ ', ' + COALESCE(Region, Country)
AS LOCATION
FROM
dbo.Suppliers;
------------------
-- Using IsNumeric
------------------
SELECT
ISNUMERIC(PostalCode), PostalCode
FROM
dbo.Customers;
-- Some non-numeric characters will pass
SELECT
ISNUMERIC('123e4'), ISNUMERIC('123d4'),
CAST ('123e4' AS float), CAST ('123d4' AS float)
-------------------------
-- Using RAND
-------------------------
-- With an automatic random seed,
-- different numbers every time.
SELECT
RAND(), RAND(), RAND();
-- With a fixed seed,
-- the same series every time.
SELECT
RAND(456), RAND(), RAND();
------------------
-- Using ROUND
------------------
SELECT
UnitPrice, ROUND(UnitPrice, 0) AS RoundedDollars, ROUND(UnitPrice, 0, 1) AS TruncatedDollars, ROUND(UnitPrice, 1) AS ToTensOfCents,
ROUND(UnitPrice, -1) AS ToTensOfDollars
FROM
dbo.Products;
-- Combining RAND and ROUND
DECLARE
SET
@minID int, @maxID int @minID=(SELECT MIN(EmployeeID) FROM dbo.Employees)
SET
@maxID=(SELECT MAX(EmployeeID) FROM dbo.Employees)
SELECT
EmployeeID AS LuckyID, LastName AS LuckyName
FROM
dbo.Employees
WHERE
EmployeeID = ROUND(@minID + (RAND()*(@maxID-@minID)),0);
----------------
-- Using REPLACE
----------------
SELECT
QuantityPerUnit, REPLACE(QuantityPerUnit, '12 ', 'twelve ') AS Twelve
FROM
dbo.Products;
--------------
-- Using STUFF
--------------
SELECT
STUFF('123456', 3, 2, 'xxxx');
----------------------------
-- Using LEN, LEFT and RIGHT
----------------------------
SELECT
ProductName, LEFT(ProductName, LEN(ProductName) -3) AS Lefty, RIGHT(ProductName, LEN(ProductName) -3) AS Righty
FROM
dbo.Products;
------------------
-- Using SUBSTRING
------------------
SELECT
SUBSTRING(FirstName, 1, 1) + '. ' + LastName
FROM
dbo.Employees;
------------------
-- Using CHARINDEX
------------------
SELECT
HomePhone, CHARINDEX(')', HomePhone) AS StartPos
FROM
dbo.Employees;
SELECT
HomePhone, LEFT(HomePhone, CHARINDEX(')', HomePhone)) AS AreaCode,
SUBSTRING(HomePhone, CHARINDEX(')', HomePhone)+2, LEN(HomePhone)-CHARINDEX(')', HomePhone)+1) AS Number
FROM
dbo.Employees;
SELECT
ProductName AS TofuProducts
FROM
dbo.Products
WHERE
CHARINDEX('tofu', ProductName) > 0;
------------------
-- Using PATINDEX
------------------
-- PATINDEX supports wildcards.
SELECT
ProductName, QuantityPerUnit
FROM
dbo.Products
WHERE
PATINDEX('24 - % g pkgs.', QuantityPerUnit) > 0;
SELECT
ProductName, QuantityPerUnit
FROM
dbo.Products
WHERE
PATINDEX('24 - __ g pkgs.', QuantityPerUnit) > 0;
-- Using bracketed values
SELECT
PostalCode
FROM
dbo.Customers
WHERE
PATINDEX('%[^0-9]%',PostalCode)=0;
-- PATINDEX can be used with text, ntext and image columns
SELECT
CategoryName, Description, PATINDEX('%sweet%', Description) AS SweetFoundAt
FROM
dbo.Categories
WHERE
PATINDEX('%sweet%', Description) > 0;
----------------------------
-- Using SPACE
-- (display results as text)
----------------------------
SELECT
FirstName + SPACE(15-LEN(FirstName)) + LastName AS NameInColumns
FROM
dbo.Employees;
--------------
-- Using CHAR
--------------
SELECT
CHAR(71)+CHAR(13)+CHAR(72) AS CharactersWithCarriageReturn;
--------------
-- Using ASCII
--------------
SELECT
ASCII('G') as AsciiG;
------------------------
-- Using LOWER and UPPER
------------------------
SELECT
UPPER(FirstName) AS FIRSTNAME, LOWER(LastName) AS lastname
FROM
dbo.Employees
-- Use for case-insensitive filtering
-- of case-sensitive data.
SELECT
CompanyName
FROM
dbo.Customers
WHERE
CHARINDEX(UPPER('the '),UPPER(CompanyName))>0;
-- (The most efficient way to perform
-- case-sensitive or case-insensitive
-- searches is to specify a collation for the query.)
SELECT
CompanyName
FROM
dbo.Customers
WHERE
CompanyName LIKE '%the %'
COLLATE
SQL_Latin1_General_CP1_CS_AS;
SELECT
CompanyName
FROM
dbo.Customers
WHERE
CompanyName LIKE '%the %'
COLLATE
SQL_Latin1_General_CP1_CI_AI;
------------------------
-- Using LTRIM and RTRIM
------------------------
SELECT
LTRIM(RTRIM(' Hello ')) + 'There' AS Trimmed;
----------------
-- Using GETDATE
----------------
SELECT
GETDATE() AS RightNow;
----------------------------
-- Using MONTH, DAY and YEAR
----------------------------
SELECT
MONTH(GETDATE()) AS ThisMonth,
DAY(GETDATE()) AS ThisDay,
YEAR(GETDATE()) AS ThisYear;
-----------------
-- Using DATEPART
-----------------
SELECT
DATEPART(dy, GETDATE()) AS DayOfYear,
DATEPART(dd, GETDATE()) AS DayNum,
DATEPART(ww, GETDATE()) AS WeekNum,
DATEPART(dw, GETDATE()) AS Weekday,
DATEPART(hh, GETDATE()) AS Hour,
DATEPART(mi, GETDATE()) AS Minute,
DATEPART(ss, GETDATE()) AS Seconds;
------------------------------
-- Using DATENAME and DATEPART
------------------------------
SELECT
DATENAME(qq, GETDATE()) AS Quarter,
DATENAME(mm, GETDATE()) AS Month,
DATENAME(dw, GETDATE()) AS Weekday,
DATENAME(hh, GETDATE()) AS Hour,
DATENAME(mi, GETDATE()) AS Minute,
DATENAME(ss, GETDATE()) AS Seconds;
-----------------
-- Using DATEADD
-----------------
SELECT
DATEADD(yy, 1, GETDATE()) AS AddYear,
DATEADD(mm, 1, GETDATE()) AS AddMonth,
DATEADD(dd, 1, GETDATE()) AS AddDay;
-----------------
-- Using DATEDIFF
-----------------
SELECT
OrderDate
, RequiredDate, ShippedDate, DATEDIFF(dd, OrderDate, RequiredDate) AS LeadTime,
DATEDIFF(dd, OrderDate, ShippedDate) AS DaysToShip,
DATEDIFF(dd, ShippedDate, RequiredDate) AS DaysEarly
FROM
dbo.Orders;
-------------------------
-- Last day of the month
-------------------------
DECLARE
SET
@date datetime @date='2006-03-07'
SELECT
DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date))
AS LastDayOfMonth
-------------------
-- Using @@ROWCOUNT
-------------------
SELECT
CompanyName
FROM
dbo.Customers
WHERE
City = 'Madrid'
SELECT
@@ROWCOUNT AS MadridCustomerCount;
--------------------------------
-- Using @@IDENTITY, @@TRANCOUNT
--------------------------------
BEGIN
TRAN
INSERT INTO dbo.Categories (CategoryName)
VALUES ('New Category');
SELECT @@IDENTITY AS NewCategoryID;
SELECT SCOPE_IDENTITY() AS NewCategoryID;
SELECT IDENT_CURRENT('dbo.Categories') AS NewCategoryID;
SELECT @@TRANCOUNT AS TRANCOUNT;
ROLLBACK
TRAN
SELECT @@TRANCOUNT AS TRANCOUNT;
----------------
-- Using @@ERROR
----------------
UPDATE
dbo.Categories SET CategoryName = NULL
WHERE CategoryID=1;
SELECT
@@ERROR AS Error;
--===============================
-- Controlling Flow of Execution
--===============================
------------------
-- Using IF...ELSE
------------------
IF
(SELECT COUNT(*) FROM dbo.Products WHERE UnitPrice > 100) > 0SELECT 'Found' AS HighPrice
ELSE
SELECT 'Not Found' AS HighPrice
--------------------
-- Using BEGIN...END
--------------------
-- This also uses a more
-- efficient subquery.
IF
EXISTS(SELECT * FROM dbo.Products WHERE UnitPrice > 100)
BEGIN
UPDATE dbo.ProductsSET UnitPrice = UnitPrice - .10WHERE UnitPrice > 100SELECT 'Ten cents removed from high prices' AS Message
ELSE
END
BEGIN
UPDATE dbo.ProductsSET UnitPrice = UnitPrice + .10SELECT 'Ten cents added to all prices' AS Message
END
-------------
-- Using GOTO
-------------
IF
(3 = 3) GOTO JumpToHere;
PRINT
'The expression is not true';
JumpToHere
:
PRINT
'The expression is true';
---------------
-- Using RETURN
---------------
IF
(3 = 2) GOTO JumpToHere;
PRINT
'The expression is not true';
RETURN
JumpToHere
:
PRINT
'The expression is true';
IF
(SELECT COUNT(*) FROM dbo.Products WHERE UnitPrice > 100) > 0
RETURN
UPDATE
dbo.Products
SET
UnitPrice = UnitPrice + .10
SELECT
'Ten cents added to all prices' AS Message
-------------
-- Using CASE
-------------
-- Using an input expression
-- and simple equality comparisons
SELECT
CategoryName, Category =
CASE CategoryNameWHEN 'Meat/Poultry' THEN 'Protein'
WHEN 'Seafood' THEN 'Protein'
WHEN 'Grains/Cereals' THEN 'Carbs'
WHEN 'Confections' THEN 'Carbs'
WHEN 'Produce' THEN 'Carbs'
WHEN 'Condiments' THEN 'Seasonings'
ELSE 'Other'
END,
Description
FROM
dbo.Categories
ORDER
BY Category;
-- Using no input expression and
-- Boolean expressions for each WHEN
SELECT
CategoryName, Category =
CASE WHEN CategoryName IN('Meat/Poultry','Seafood') THEN 'Protein'
WHEN CategoryName IN('Grains/Cereals','Confections','Produce')
THEN 'Carbs'
WHEN CategoryName = 'Condiments'
THEN 'Seasonings'
ELSE 'Other'
END,
Description
FROM
dbo.Categories
ORDER
BY Category;
-----------------------------------------------------------
-- Using CASE to replace the VB Immediate If function (IIF)
-- or the C# conditional operator (?:)
-----------------------------------------------------------
DECLARE
SELECT
@Avg money @Avg = AVG(UnitPrice) FROM dbo.Products
SELECT
ProductName, UnitPrice, Ranking =
CASE
WHEN UnitPrice > @Avg THEN 'above average'
ELSE 'below average'
FROM
END dbo.Products;
SELECT
Address = City +
FROM
CASE WHEN Region IS NULL THEN ' ' + PostalCodeELSE ', ' + Region + ' ' + PostalCodeEND dbo.Customers;
--------------
-- Using WHILE
--------------
WHILE
(SELECT AVG(UnitPrice) FROM dbo.Products) <= 12BEGIN
UPDATE dbo.ProductsSET UnitPrice = UnitPrice * 1.01IF (SELECT MAX(UnitPrice) FROM dbo.Products) > 150BREAK
ELSE
CONTINUE
END
----------------
-- Using WAITFOR
----------------
--Pause for ten seconds
WAITFOR
DELAY '000:00:10'
PRINT
'Done'
--Pause until a certain time
WAITFOR
TIME '12:00:00'
PRINT
'It is noon'
--------------------------
-- Using Ranking Functions
--------------------------
-- Ranked by UnitPrice and listed by UnitPrice.
SELECT
ProductName, UnitPrice,
ROW_NUMBER
() OVER (ORDER BY UnitPrice DESC) AS RowNumber,
RANK
() OVER (ORDER BY UnitPrice DESC) AS Rank,
DENSE_RANK
() OVER (ORDER BY UnitPrice DESC) AS DenseRank,
NTILE
(50) OVER (ORDER BY UnitPrice DESC) AS NTILE50
FROM
dbo.Products
ORDER
BY UnitPrice DESC;
-- Ranked by UnitPrice but listed by ProductName
SELECT
ProductName, UnitPrice,
ROW_NUMBER
() OVER (ORDER BY UnitPrice DESC) AS RowNumber,
RANK
() OVER (ORDER BY UnitPrice DESC) AS Rank,
DENSE_RANK
() OVER (ORDER BY UnitPrice DESC) AS DenseRank,
NTILE
(50) OVER (ORDER BY UnitPrice DESC) AS NTILE50
FROM
dbo.Products
ORDER
BY ProductName;
-- Ranked within each category.
SELECT
ProductName, CategoryID, UnitPrice,
DENSE_RANK
() OVER (PARTITION BY CategoryID ORDER BY UnitPrice DESC) AS DenseRank
FROM
dbo.Products
ORDER
BY CategoryID, UnitPrice DESC;
-- Ranked by category and overall, listed alphabetically
SELECT
ProductName, CategoryID, UnitPrice,
DENSE_RANK
() OVER (PARTITION BY CategoryID ORDER BY UnitPrice DESC) AS DenseRankByCategory,
DENSE_RANK
() OVER (ORDER BY UnitPrice DESC) AS OverallDenseRank,
DENSE_RANK
() OVER (ORDER BY UnitsOnOrder DESC) AS OverallByUnitsOnOrder
FROM
dbo.Products
ORDER
BY ProductName;