Optimize for SQL queries and data operations

sku product_description color
1 Ball Red
2 Bike Blue
3 Tent NULL

SELECT C.color color
FROM Colors AS C Black
WHERE C.color NOT IN (SELECT P.color Blue
FROM Products AS P); Green
Red

color
----------

(0 row(s) affected)

/* EXISTS */
SELECT C.color
FROM Colors AS C
WHERE NOT EXISTS(SELECT *
FROM Products AS P
WHERE C.color = P.color);

/* IS NOT NULL in the subquery */
SELECT C.color
FROM Colors AS C
WHERE C.color NOT IN (SELECT P.color
FROM Products AS P
WHERE P.color IS NOT NULL);

/* EXCEPT */
SELECT color
FROM Colors
EXCEPT
SELECT color
FROM Products;

/* LEFT OUTER JOIN */
SELECT C.color
FROM Colors AS C
LEFT OUTER JOIN Products AS P
ON C.color = P.color
WHERE P.color IS NULL;

SELECT customer_name
FROM Customers
WHERE LEFT(customer_name, 1) = 'L';

SELECT SUM(sale_amount) AS total_sales
FROM Sales
WHERE DATEPART(YEAR, sale_date) = 2009
AND DATEPART(MONTH, sale_date) = 1;

/* Exception: SQL Server 2008 only */
SELECT SUM(sale_amount) AS total_sales
FROM Sales
WHERE CAST(sale_date AS DATE) = '20090101';

SELECT customer_name
FROM Customers
WHERE customer_name LIKE 'L%';

SELECT SUM(sale_amount) AS
total_sales
FROM Sales
WHERE sale_date >= '20090101'
AND sale_date < '20090201';

calendar_date holiday_name
2009-01-01 New Year’s Day
2009-01-02 NULL
2009-01-03 NULL
2009-01-04 NULL
2009-01-05 NULL

sale_date sale_amount
2009-01-01 120.50
2009-01-02 115.00
SELECT sale_date, sale_amount
FROM Sales AS S 2009-01-03 140.80
WHERE sale_date IN 2009-01-04 100.50
(SELECT sale_date
FROM Calendar AS C
WHERE holiday_name IS NOT NULL);

SELECT sale_date, sale_amount
FROM Sales AS S
WHERE sale_date IN
(SELECT C.calendar_date
FROM Calendar AS C
WHERE C.holiday_name IS NOT NULL);

CREATE TABLE Customers (
customer_nbr INT NOT NULL PRIMARY KEY,
first_name VARCHAR(35) NOT NULL,
last_name VARCHAR(35) NOT NULL);

SELECT first_name, last_name
FROM Customers
WHERE last_name = N'Brown';

/*
StmtText
-----------------------------------
|--Clustered Index Scan(OBJECT:(
[dbo].[Customers].[PK_Customer]),
WHERE:(CONVERT_IMPLICIT(nvarchar(35),
[dbo].[Customers].[last_name],0)=[@1]))
*/

SELECT first_name, last_name
FROM Customers
WHERE last_name = 'Brown';

account_nbr account_type account_reference
1 Personal abc
2 Business Basic 101
3 Personal def
4 Business Plus 5

SELECT account_nbr, account_reference AS account_ref_nbr
FROM Accounts
WHERE account_type LIKE 'Business%'
AND CAST(account_reference AS INT) > 20;

SELECT account_nbr, account_ref_nbr
FROM (SELECT account_nbr,
CAST(account_reference AS INT)
AS account_ref_nbr
FROM Accounts
WHERE account_type LIKE 'Business%') AS A
WHERE account_ref_nbr > 20;

SELECT account_nbr,
account_reference AS account_ref_nbr
FROM Accounts
WHERE account_type LIKE 'Business%'
AND CASE WHEN account_reference NOT LIKE '%[^0-9]%'
THEN CAST(account_reference AS INT)
END > 20;

SELECT C.customer_name,
SUM(COALESCE(O.order_amt, 0)) AS total_2009
FROM Customers AS C
LEFT OUTER JOIN Orders AS O
ON C.customer_nbr = O.customer_nbr
WHERE O.order_date >= '20090101'
GROUP BY C.customer_name;

SELECT C.customer_name, O.order_amt, D.qty
FROM Customers AS C
INNER JOIN OrderDetails AS D
ON D.order_nbr = O.order_nbr
AND D.sku = 101;

-- Correct predicate in join conditions
SELECT C.customer_name,
SUM(COALESCE(O.order_amt, 0)) AS total_2009
FROM Customers AS C
AND O.order_date >= '20090101'
GROUP BY C.customer_name;

-- Correct with OUTER join
SELECT C.customer_name, O.order_amt, D.qty
FROM Customers AS C
LEFT OUTER JOIN OrderDetails AS D
ON D.order_nbr = O.order_nbr
AND D.sku = 101;

SELECT sku, product_description,
(SELECT plant_nbr
FROM ProductPlants AS B
WHERE B.sku = A.sku) AS plant_nbr
FROM Products AS A;

SELECT A.sku, A.product_description, B.plant_nbr
FROM Products AS A
JOIN ProductPlants AS B
ON A.sku = B.sku
WHERE B.sku =
(SELECT C.sku
FROM NewProducts AS C
WHERE C.product_description LIKE '%guitar%');

SELECT A.sku,
A.product_description,
B.plant_nbr
FROM Products AS A
JOIN ProductPlants AS B
ON A.sku = B.sku;

INSERT INTO PlasticProducts
SELECT *
FROM Products
WHERE material_type = 'plastic';

CREATE VIEW MetalProducts
AS
SELECT *
FROM Products
WHERE material_type = 'metal';

INSERT INTO PlasticProducts
(sku, product_description, material_type)
SELECT sku, product_description, material_type
FROM Products
WHERE material_type = 'plastic';

CREATE FUNCTION dbo.GetTotalSales(@sku INT)
RETURNS DECIMAL(15, 2)
AS
BEGIN
RETURN(SELECT SUM(sale_amount)
FROM Sales
WHERE sku = @sku);
END

SELECT sku,
product_description,
dbo.GetTotalSales(sku) AS total_sales
FROM Products;

-- direct calculation in the query
SELECT P.sku, P.product_description,
SUM(S.sale_amount) As total_sales
FROM Products AS P
JOIN Sales AS S
ON P.sku = S.sku
GROUP BY P.sku, P.product_description;

-- table-valued function
CREATE FUNCTION dbo.GetTotalSales(@sku INT)
RETURNS TABLE
AS
RETURN(SELECT SUM(sale_amount) AS total_sales
FROM Sales
WHERE sku = @sku);

-- query using table-valued function with CROSS APPLY
SELECT sku, product_description, total_sales
FROM Products AS P
CROSS APPLY dbo.GetTotalSales(P.sku) AS S;

DECLARE PriceUpdates
CURSOR LOCAL
FORWARD_ONLY
STATIC
READ_ONLY
FOR SELECT sku, price
FROM NewPrices;

OPEN PriceUpdates;

FETCH NEXT FROM PriceUpdates
INTO @sku, @price;

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE ProductPrices
SET price = @price,
effective_start_date = CURRENT_TIMESTAMP
WHERE sku = @sku;
FETCH NEXT FROM PriceUpdates
INTO @sku, @price;

END

UPDATE ProductPrices
SET price = (SELECT N.price
FROM NewPrices AS N
WHERE N.sku = ProductPrices.sku),
effective_start_date = CURRENT_TIMESTAMP
WHERE EXISTS(SELECT *
FROM NewPrices AS N
WHERE N.sku = ProductPrices.sku);

Optimize for SQL queries and data operations

Optimize for SQL queries and data operations

Recommended

Recommended

More Related Content

What's hot

What's hot (19)

Viewers also liked

Viewers also liked (9)

Similar to Optimize for SQL queries and data operations

Similar to Optimize for SQL queries and data operations (20)

Recently uploaded

Recently uploaded (20)

Optimize for SQL queries and data operations