SQL Server Code,Tips and Tricks, Performance Tuning

SELECT FORMATMESSAGE('Signed int %i, %i', 50, -50) 
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50); 

DECLARE @Val int = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %s is incorrect!',
 CONVERT(VARCHAR(100),@Val));

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %I64d is incorrect!', @Val);

USE [master]
RESTORE DATABASE [WideWorldImporters] 
FROM DISK = N'C:\WideWorldImporters-Full.bak' WITH FILE = 1, 
MOVE N'WWI_Primary' TO N'C:\SQL\DATA\WideWorldImporters.mdf', 
MOVE N'WWI_UserData' TO N'C:\SQL\DATA\WideWorldImporters_UserData.ndf', 
MOVE N'WWI_Log' TO N'C:\SQL\Log\WideWorldImporters.ldf', 
MOVE N'WWI_InMemory_Data_1' TO N'C:\SQL\DATA\WideWorldImporters_InMemory_Data_1', 
NOUNLOAD, REPLACE, STATS = 5
GO

USE WideWorldImporters
GO
select [Website].[CalculateCustomerPrice](1,number,'20160101')
from master..spt_values
where type = 'p'
select [Website].[CalculateCustomerPrice](customerid,1,'20160101')
from Sales.customers

SELECT TOP 10 d.object_id, d.database_id, 
OBJECT_NAME(object_id, database_id) AS 'function name', 
 d.cached_time, d.last_execution_time, d.total_elapsed_time, 
 d.total_elapsed_time/d.execution_count AS [avg_elapsed_time], 
 d.last_elapsed_time, d.execution_count 
FROM sys.dm_exec_function_stats AS d 
ORDER BY [total_worker_time] DESC; 

--old way of dropping a proc then having a create script
IF OBJECT_ID('procTest') is not null
DROP PROCEDURE procTest
GO
CREATE PROCEDURE procTest
AS
BEGIN
 PRINT (1)
END;
GO

--another way is to have a dummy proc created,
--that way your alter part is always the same
IF OBJECT_ID('dbo.procTest') IS NULL
 EXEC ('CREATE PROCEDURE dbo.procTest AS RETURN 0;')
GO
ALTER PROCEDURE procTest
AS
BEGIN
 PRINT (1)
END;
GO

-- the easier way in sql server 2016 SP1 and up
CREATE OR ALTER PROCEDURE procTest
AS
BEGIN
 PRINT (1)
END;
GO

-- works the same with functions
CREATE OR ALTER FUNCTION fnTest()
RETURNS INT
AS
BEGIN
 RETURN(1)
END;
GO

-- also works with views
CREATE OR ALTER VIEW vwTest
AS
 SELECT 1 AS col;
GO

-- first we need a table for the trigger
IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = 'BooFar')
CREATE TABLE BooFar(id int)
GO
-- works with triggers
CREATE OR ALTER TRIGGER trTest
ON BooFar
AFTER INSERT, UPDATE
AS
 RAISERROR ('Hello from trigger', 1, 10);
 GO
 -- just a test to make sure the trigger works
 INSERT BooFar values(1)
 -- you should see this in the message tab
 /*
Hello from trigger
Msg 50000, Level 1, State 10
(1 row(s) affected)
*/

CREATE OR ALTER TRIGGER safety 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
 PRINT 'You must disable Trigger "safety" to drop or alter tables!' 
 ROLLBACK; 

-- drop everything by using
-- DROP object IF EXISTS
DROP TABLE IF EXISTS BooFar
DROP PROCEDURE IF EXISTS procTest
DROP VIEW IF EXISTS vwTest

CREATE TABLE SalesPartitioned(
 YearCol SMALLINT NOT NULL,
 OrderID INT NOT NULL, 
 SomeData UNIQUEIDENTIFIER DEFAULT newsequentialid())
GO

INSERT SalesPartitioned (YearCol,OrderID)
SELECT 2013,number
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2014,number + 2048
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2015,number + 4096
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2016,number + 6144
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2017,number + 8192
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2018,number + 10240
FROM master..spt_values
WHERE type = 'P'

CREATE PARTITION FUNCTION pfFiscalYear(SMALLINT)
AS RANGE LEFT FOR VALUES(2013,2014,2015,2016,2017)
GO

CREATE PARTITION SCHEME psFiscalYear
AS PARTITION pfFiscalYear ALL TO ([PRIMARY])
GO

ALTER TABLE dbo.SalesPartitioned ADD CONSTRAINT
 PK_Sales PRIMARY KEY CLUSTERED (YearCol,OrderID)
ON psFiscalYear(YearCol)
GO

SELECT partition_number,rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('SalesPartitioned')

SELECT YearCol, $PARTITION.pfFiscalYear(YearCol) AS Partition,
COUNT(*) AS [COUNT] FROM SalesPartitioned
GROUP BY $PARTITION.pfFiscalYear(YearCol),YearCol
ORDER BY Partition;
GO

TRUNCATE TABLE SalesPartitioned
WITH (PARTITIONS (2));
GO

SELECT partition_number,rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('SalesPartitioned')

SELECT YearCol, $PARTITION.pfFiscalYear(YearCol) AS Partition,
COUNT(*) AS [COUNT] FROM SalesPartitioned
GROUP BY $PARTITION.pfFiscalYear(YearCol),YearCol
ORDER BY Partition;
GO

TRUNCATE TABLE SalesPartitioned
WITH (PARTITIONS (4 TO 6));
GO

SELECT partition_number,rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('SalesPartitioned')

SELECT YearCol, $PARTITION.pfFiscalYear(YearCol) AS Partition,
COUNT(*) AS [COUNT] FROM SalesPartitioned
GROUP BY $PARTITION.pfFiscalYear(YearCol),YearCol
ORDER BY Partition;
GO

TRUNCATE TABLE SalesPartitioned 
WITH (PARTITIONS (12));