tsql-enhancements-2014-2016.sql

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php
Get WideWorldImporters-Full.bak from Microsoft at:
https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
******************************************************************************/

USE master;
GO

IF DB_ID('WideWorldImporters') IS NOT NULL
BEGIN
    ALTER DATABASE WideWorldImporters SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
GO
RESTORE DATABASE WideWorldImporters FROM DISK=
	'S:\MSSQL\Backup\WideWorldImporters-Full.bak'  
	WITH REPLACE,
	MOVE 'WWI_Primary' to 'S:\MSSQL\Data\WideWorldImporters.mdf',
	MOVE 'WWI_UserData' to 'S:\MSSQL\Data\WideWorldImporters_UserData.ndf',
	MOVE 'WWI_Log' to 'S:\MSSQL\Data\WideWorldImporters.ldf',
	MOVE 'WWI_InMemory_Data_1' to 'S:\MSSQL\Data\WideWorldImporters_InMemory_Data_1';
GO

USE WideWorldImporters;
GO

CREATE OR ALTER PROCEDURE Warehouse.StockItemsByColor
    @ColorName NVARCHAR(20)
AS
    SELECT
        si.StockItemName
    FROM Warehouse.StockItems AS si
    JOIN Warehouse.Colors AS c on si.ColorID=c.ColorID
    WHERE c.ColorName = @ColorName;
GO

DROP PROCEDURE IF EXISTS Warehouse.StockItemsByColor, Warehouse.StockItemsBySize;
GO

SELECT
    SYSDATETIMEOFFSET() AS instance_time,
    SYSDATETIMEOFFSET() AT TIME ZONE 'UTC' AS utc_time;
GO

/* Time zone list */
--select * from sys.time_zone_info

CREATE TABLE dbo.indexes_inline (
    i int identity not null,
    j nvarchar(10),
    k bigint,
    l int,
    index nc_j_k (j, k) 
);
GO

SELECT *
FROM string_split('split,this,string',',');
GO

TRUNCATE TABLE Purchasing.SupplierTransactions WITH (PARTITIONS (1, 5));
GO