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

SELECT STRING_AGG (name, ',') AS csv 
FROM master..spt_values
WHERE type = 'A'; 

SELECT t.name,STRING_AGG (c.name, ',') AS csv
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
ORDER BY 1

SELECT t.name,STRING_AGG (c.name, ',') AS csv
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
GROUP BY t.name
ORDER BY 1

CREATE TABLE t_bulkload (
accountkey int not null,
accountdescription varchar (max),
accounttype char(500),
AccountCodeAlternatekey int)
GO
CREATE CLUSTERED COLUMNSTORE index t_bulkload_cci on t_bulkload

• sp_spaceused is now supported for in-memory tables.
• sp_rename is now supported for native modules.
• CASE statements are now supported for native modules.
• The limitation of 8 indexes on in-memory tables has been removed.
• TOP (N) WITH TIES is now supported in native modules.
• ALTER TABLE against in-memory tables is now substantially faster in some cases.
• Transaction redo In-memory tables is now done in parallel. This substantially reduces the time to do failovers or in some cases restarts.
• In-memory checkpoint files can now be stored on Azure Storage. This provides equivalent capabilities to MDF compared to LDF files, which already have this capability.

sys.dm_os_host_info

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));