Scribd
Upload
14 views5 pages

Chapter28 Examples

The document contains SQL examples for various database operations including setting configurations, creating indexes, and defining functions in the AdventureWorks database. It demonstrates how to manipulate and query data, such as counting sales orders and categorizing customers based on sales. Additionally, it shows how to alter database compatibility levels and enable advanced options for performance tuning.

Uploaded by

riyasathsafran
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
14 views5 pages

Chapter28 Examples

The document contains SQL examples for various database operations including setting configurations, creating indexes, and defining functions in the AdventureWorks database. It demonstrates how to manipulate and query data, such as counting sales orders and categorizing customers based on sales. Additionally, it shows how to alter database compatibility levels and enable advanced options for performance tuning.

Uploaded by

riyasathsafran
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Example 28.

1
ALTER DATABASE SCOPED CONFIGURATION SET
BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Example 28.2
USE AdventureWorks;
SELECT [Link], COUNT([Link]) AS Cnt,
SUM([Link]) AS Sum,
AVG([Link]) AS Avg
FROM [Link] AS history
JOIN [Link] AS product
ON [Link] = [Link]
GROUP BY [Link], [Link];

Example 28.3
USE AdventureWorks;
CREATE NONCLUSTERED COLUMNSTORE INDEX csi_history
ON [Link](ProductID,Quantity,ActualCost);

Example 28.4
USE AdventureWorks;
GO
CREATE FUNCTION GetLastShipped()
RETURNS @CustomerOrder TABLE
(SaleOrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderQty INT NOT NULL)
AS
BEGIN
INSERT @CustomerOrder
SELECT [Link], [Link], [Link], [Link]
FROM [Link] a
INNER JOIN [Link] b
ON [Link] = [Link]
INNER JOIN [Link] c
ON [Link] = [Link]
WHERE [Link] = ( Select Max([Link])
FROM [Link] As SH1
WHERE [Link] = [Link])
RETURN
END

Example 28.5
USE AdventureWorks;
SELECT C = COUNT_BIG(*)
FROM GetLastShipped() C

Example 28.6
ALTER DATABASE SCOPED CONFIGURATION SET
INTERLEAVED_EXECUTION_TV = ON;

Example 28.7
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 4;
GO
RECONFIGURE WITH OVERRIDE;
GO
Example 28.8
USE sample;
CREATE NONCLUSTERED INDEX nCLI_IFactSales
ON [Link]
(OrderDateKey, CustomerKey, SalesAmount) ;

Example 28.9
USE sample;
SELECT [Link],
[Link],
SUM([Link]) TotalSalesByCommuteDistance
FROM [Link] as f
INNER JOIN [Link] as c ON
[Link] = [Link]
INNER JOIN [Link] d ON
[Link] = [Link]
GROUP BY [Link], [Link];

Example 28.10
USE AdventureWorksDW;
SET STATISTICS TIME ON;
SELECT count(DISTINCT(SalesOrderNumber))
FROM FactInternetSales;
SELECT APPROX_COUNT_DISTINCT(SalesOrderNumber)
FROM FactInternetSales;

Example 28.11

ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 140;


GO
USE AdventureWorks;
GO
CREATE FUNCTION dbo.CustomerRate14 (@CustomerID INT)
RETURNS CHAR(10) AS
BEGIN;
DECLARE @sales DECIMAL (18,2);
DECLARE @category CHAR(10);
SET @sales = (SELECT SUM(Subtotal)
FROM [Link] WHERE CustomerID = @CustomerID);
IF @sales < 500000
SET @category = 'REGULAR';
ELSE IF @sales < 1000000
SET @category = 'GOLD';
ELSE
SET @category = 'PLATINUM';
RETURN @category;
END

Example 28.12

ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 140;


GO
USE AdventureWorks;
GO
CREATE FUNCTION dbo.CustomerRate14 (@CustomerID INT)
RETURNS CHAR(10) AS
BEGIN;
DECLARE @sales DECIMAL (18,2);
DECLARE @category CHAR(10);
SET @sales = (SELECT SUM(Subtotal)
FROM [Link] WHERE CustomerID = @CustomerID);
IF @sales < 500000
SET @category = 'REGULAR';
ELSE IF @sales < 1000000
SET @category = 'GOLD';
ELSE
SET @category = 'PLATINUM';
RETURN @category;
END

You might also like