数据库内核月报 － 2017 / 01

USE ColumnStoreDB
GO
SELECT DISTINCT
	table_name = object_name(part.object_id)
	,ix.name
	,part.data_compression_desc 
FROM sys.partitions as part
	INNER JOIN sys.indexes as ix
	ON part.object_id = ix.object_id
	AND part.index_id = ix.index_id
WHERE part.object_id = object_id('dbo.SalesOrder','U')
	AND ix.name = 'NCSIX_ALL_Columns'

USE ColumnStoreDB
GO
SELECT 
	table_name = object_name(part.object_id)
	,col.name
	,seg.segment_id
	,seg.min_data_id
	,seg.max_data_id
	,seg.row_count
FROM sys.partitions as part
	INNER JOIN sys.column_store_segments as seg
	ON part.partition_id = seg.partition_id
	INNER JOIN sys.columns as col
	ON part.object_id = col.object_id
	AND seg.column_id = col.column_id
WHERE part.object_id = object_id('dbo.SalesOrder','U')
AND seg.column_id = 1
ORDER BY seg.segment_id

-- Create testing database
IF DB_ID('ColumnStoreDB') IS NULL
	CREATE DATABASE ColumnStoreDB;
GO

USE ColumnStoreDB
GO
IF OBJECT_ID('dbo.AutoType', 'U') IS NOT NULL
BEGIN
	DROP TABLE dbo.AutoType
END
GO

-- create demo table autoType
CREATE TABLE dbo.AutoType
(
   AutoID INT IDENTITY(101,1) NOT NULL PRIMARY KEY, 
   Make VARCHAR(20) NOT NULL,
   Model VARCHAR(20) NOT NULL,
   Color VARCHAR(15) NOT NULL,
   ModelYear SMALLINT NOT NULL
);

-- data init
INSERT INTO dbo.AutoType
SELECT 'Ford', 'Explorer', 'white', 2003 UNION ALL
SELECT 'Satum', 'Lon', 'blue', 2003 UNION ALL
SELECT 'Lexus', 'GX460', 'gray', 2010 UNION ALL
SELECT 'Honda', 'CRV', 'blue', 2007 UNION ALL
SELECT 'Subaru', 'Legacy', 'green', 2008 UNION ALL
SELECT 'Honda', 'Civic', 'red', 1996 UNION ALL
SELECT 'Nissan', 'Sentra', 'silver', 2012 UNION ALL
SELECT 'Chevrolet', 'Tahoe', 'green', 1995 UNION ALL
SELECT 'Toyota', 'Celica', 'red', 1992 UNION ALL
SELECT 'BMW', 'X5', 'gray', 2002 UNION ALL
SELECT 'Subaru', 'Impreze', 'silver', 2011 UNION ALL
SELECT 'Volkswagen', 'Jetta', 'black', 1995 UNION ALL
SELECT 'Chevrolet', 'Impala', 'red', 2008 UNION ALL
SELECT 'Jeep', 'Liberty', 'gray', 2012 UNION ALL
SELECT 'Dodge', 'Dakota', 'blue', 2000 
;

-- Create PARTITION FUNCTION & SCHEMA
CREATE PARTITION FUNCTION pf_SalesYear (datetime) 
AS RANGE LEFT FOR VALUES 
('2013-01-01 00:00', '2014-01-01 00:00', '2015-01-01 00:00', '2016-01-01 00:00', '2017-01-01 00:00', '2018-01-01 00:00')
;
GO

CREATE PARTITION scheme ps_SalesYear
AS  PARTITION pf_SalesYear 
ALL TO ([PRIMARY])
;
GO

-- create demo table SalesOrder
IF OBJECT_ID('dbo.SalesOrder', 'U') IS NOT NULL
BEGIN
	DROP TABLE dbo.SalesOrder
END
GO
CREATE TABLE dbo.SalesOrder
(
	OrderID INT NOT NULL
	,AutoID INT NOT NULL
	,UserID INT NOT NULL
	,OrderQty INT NOT NULL
	,Price DECIMAL(8,2) NOT NULL
	,UnitPrice AS Price * OrderQty
	,OrderDate DATETIME NOT NULL
) ON ps_SalesYear(OrderDate);

-- data init for 5 M records.
;WITH a 
AS (
	SELECT * 
	FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a)
), RoundData
AS(
SELECT TOP(5000000)
	OrderID = ROW_NUMBER() OVER (ORDER BY a.a)
	,AutoIDRound = abs(checksum(newid()))
	,Price = a.a * b.a * 10000
	,OrderQty = a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h
)
INSERT INTO dbo.SalesOrder(OrderID, AutoID, UserID, OrderQty, Price, OrderDate)
SELECT 
	OrderID
	,AutoID = cast(ROUND((13 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 101), 0) as int)
	,UserID = cast(ROUND((500 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 10000), 0) as int)
	,OrderQty
	,Price = cast(Price AS DECIMAL(8,2))
	,OrderDate = dateadd(day, -cast(ROUND((1099 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 1), 0) as int) ,'2017-01-10')
FROM RoundData;
GO

SELECT Make, ModelYear FROM dbo.AutoType;

-- create regular B-Tree Clustered Index
CREATE UNIQUE CLUSTERED INDEX CIX_OrderID 
ON dbo.SalesOrder(OrderID, OrderDate) 
WITH (ONLINE = ON)
ON ps_SalesYear(OrderDate);

--create nonclustered columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_ALL_Columns 
ON dbo.SalesOrder(OrderID, AutoID, UserID, OrderQty, Price, OrderDate)
GO

/*--=====================================================
MS SQL Server 2012 columnstore index query performance compare
--=====================================================*/
USE ColumnStoreDB
GO

DBCC DROPCLEANBUFFERS
GO

SET STATISTICS TIME ON
SET STATISTICS IO ON

-- Columnstore index query
SELECT 
	AutoID
	,SalesQty = SUM(OrderQty)
	,SalesAmount = SUM(UnitPrice)
FROM dbo.SalesOrder WITH(INDEX=NCSIX_ALL_Columns)
GROUP BY AutoID

-- B-Tree Index query
SELECT 
	AutoID
	,SalesQty = SUM(OrderQty)
	,SalesAmount = SUM(UnitPrice)
FROM dbo.SalesOrder WITH (INDEX = CIX_OrderID)
GROUP BY AutoID

-- Batch mode processing will be used when INNER JOIN
SELECT 
	at.Model
	,TotalAmount = SUM(ord.UnitPrice)
	,TotalQty = SUM(ord.OrderQty)
FROM dbo.AutoType AS at
	INNER JOIN dbo.SalesOrder AS ord
	ON ord.AutoID = at.AutoID
GROUP BY at.Model

-- BUT row mode processing will be used when OUTER JOIN
SELECT 
	at.Model
	,TotalAmount = ISNULL(SUM(ord.UnitPrice), 0)
	,TotalQty = ISNULL(SUM(ord.OrderQty), 0)
FROM dbo.AutoType AS at
	LEFT OUTER JOIN dbo.SalesOrder AS ord
	ON ord.AutoID = at.AutoID
GROUP BY at.Model
ORDER BY ISNULL(SUM(ord.UnitPrice), 0) DESC

-- OUTER JOIN workaround
;WITH intermediateData
AS
(
	SELECT 
		at.AutoID
		,TotalAmount = SUM(ord.UnitPrice)
		,TotalQty = SUM(ord.OrderQty)
	FROM dbo.AutoType AS at
		INNER JOIN dbo.SalesOrder AS ord
		ON ord.AutoID = at.AutoID
	GROUP BY at.AutoID
)
SELECT 
	at.Model
	,TotalAmount = ISNULL(itm.TotalAmount, 0)
	,TotalQty = ISNULL(itm.TotalQty, 0)
FROM dbo.AutoType AS at
	LEFT OUTER JOIN intermediateData AS itm
	ON itm.AutoID = at.AutoID
ORDER BY itm.TotalAmount DESC

-- DEMO 2: IN & EXISTS both use row mode processing

IF OBJECT_ID('dbo.HondaAutoTypes', 'U') IS NOT NULL
BEGIN
	TRUNCATE TABLE dbo.HondaAutoTypes
	DROP TABLE dbo.HondaAutoTypes
END

SELECT *
	INTO dbo.HondaAutoTypes
FROM dbo.AutoType
WHERE make = 'Honda'

-- IN use row mode
SELECT 
		OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
		,TotalAmount = SUM(ord.UnitPrice)
		,TotalQty = SUM(ord.OrderQty)
FROM dbo.SalesOrder AS ord
WHERE ord.AutoID IN(SELECT AutoID FROM dbo.HondaAutoTypes)
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
ORDER BY 1 DESC

-- EXISTS use row mode too.
SELECT 
		OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
		,TotalAmount = SUM(ord.UnitPrice)
		,TotalQty = SUM(ord.OrderQty)
FROM dbo.SalesOrder AS ord
WHERE EXISTS(SELECT TOP 1 * FROM dbo.HondaAutoTypes WHERE AutoID = ord.AutoID)
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
ORDER BY 1 DESC


-- IN & EXISTS workaround using INNER JOIN
SELECT 
		OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
		,TotalAmount = SUM(ord.UnitPrice)
		,TotalQty = SUM(ord.OrderQty)
FROM dbo.SalesOrder AS ord
	INNER JOIN dbo.HondaAutoTypes AS hat
	ON ord.AutoID = hat.AutoID
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
ORDER BY 1 DESC

-- or we also can use IN(<list of constants>) to make it use batch mode.
SELECT 
		OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
		,TotalAmount = SUM(ord.UnitPrice)
		,TotalQty = SUM(ord.OrderQty)
FROM dbo.SalesOrder AS ord
WHERE ord.AutoID IN(104,106)
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
ORDER BY 1 DESC

-- DEMO 3: UNION ALL usually use row mode

IF OBJECT_ID('dbo.partSalesOrder', 'U') IS NOT NULL
BEGIN
	TRUNCATE TABLE dbo.partSalesOrder
	DROP TABLE dbo.partSalesOrder
END

SELECT TOP 100 *
	INTO dbo.partSalesOrder
FROM dbo.SalesOrder
WHERE OrderID < 2500000;

-- UNION ALL mostly use row mode
;WITH unionSalesOrder
AS
(
	SELECT *
	FROM dbo.SalesOrder AS ord
	UNION ALL
	SELECT *
	FROM dbo.partSalesOrder AS pord

)

SELECT 
	OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
	,TotalAmount = SUM(ord.UnitPrice)
	,TotalQty = SUM(ord.OrderQty)
FROM dbo.AutoType AS at
	INNER JOIN unionSalesOrder AS ord
	ON ord.AutoID = at.AutoID
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
ORDER BY 1 DESC

-- UNION ALL workaround
;WITH unionSaleOrders
AS(
	SELECT 
		OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
		,TotalAmount = SUM(ord.UnitPrice)
		,TotalQty = SUM(ord.OrderQty)
	FROM dbo.AutoType AS at
		INNER JOIN SalesOrder AS ord
		ON ord.AutoID = at.AutoID
	GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
), unionPartSaleOrders
AS
(
	SELECT 
		OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
		,TotalAmount = SUM(ord.UnitPrice)
		,TotalQty = SUM(ord.OrderQty)
	FROM dbo.AutoType AS at
		INNER JOIN dbo.partSalesOrder AS ord
		ON ord.AutoID = at.AutoID
	GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
), unionAllData
AS
(
	SELECT *
	FROM unionSaleOrders
	UNION ALL
	SELECT *
	FROM unionPartSaleOrders
)
SELECT 
	OrderDay
	,TotalAmount = SUM(TotalAmount)
	,TotalQty = SUM(TotalQty)
FROM unionAllData
GROUP BY OrderDay
ORDER BY OrderDay DESC

-- DEMO 4: Scalar Aggregates
SELECT COUNT(*)
FROM dbo.SalesOrder

-- workaround 
;WITH salesOrderByAutoId([AutoID], cnt)
AS(
	SELECT [AutoID], count(*)
	FROM dbo.SalesOrder
	GROUP BY [AutoID]
)
SELECT SUM(cnt)
FROM salesOrderByAutoId

-- END DEMO 4

-- DEMO 5: Multiple DISTINCT Aggregates
SELECT 
		OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
		,AutoIdCount = COUNT(DISTINCT ord.[AutoID])
		,UserIdCount = COUNT(DISTINCT ord.[UserID])
FROM dbo.AutoType AS at
	INNER JOIN dbo.SalesOrder AS ord
	ON ord.AutoID = at.AutoID
GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)

-- workaround
;WITH autoIdsCount(orderDay, AutoIdCount)
AS(
	SELECT 
			OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
			,AutoIdCount = COUNT(DISTINCT ord.[AutoID])
	FROM dbo.AutoType AS at
		INNER JOIN dbo.SalesOrder AS ord
		ON ord.AutoID = at.AutoID
	GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
), userIdsCount(orderDay, UserIdCount)
AS(
	SELECT 
			OrderDay = CONVERT(CHAR(10), ord.OrderDate, 120)
			,UserIdCount = COUNT(DISTINCT ord.[UserID])
	FROM dbo.AutoType AS at
		INNER JOIN dbo.SalesOrder AS ord
		ON ord.AutoID = at.AutoID
	GROUP BY CONVERT(CHAR(10), ord.OrderDate, 120)
)
SELECT
	auto.orderDay
	,auto.AutoIdCount
	,ur.UserIdCount
FROM autoIdsCount AS auto
	INNER JOIN userIdsCount AS ur
	ON auto.orderDay = ur.orderDay
-- END DEMO 5

/*--=====================================================
MS SQL Server 2012 Columnstore index limitations
--=====================================================*/

-- Just accept only one columnstore index
-- create another one
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder 
ON dbo.SalesOrder(OrderID,AutoID,OrderQty,Price,OrderDate);
GO

-- Does not support CLUSTERED columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX CCSIX_SalesOrder 
ON dbo.SalesOrder(OrderID,AutoID,OrderQty,Price,OrderDate);
GO

-- Does not support UNIQUE columnstore index
CREATE UNIQUE COLUMNSTORE INDEX UCSIX_SalesOrder 
ON dbo.SalesOrder(OrderID,AutoID,OrderQty,Price,OrderDate);
GO

-- Does not accept ASC/DESC
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder_OrderDate
ON dbo.SalesOrder(OrderDate ASC);
GO

-- Does not accept computed column
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder_OrderDate
ON dbo.SalesOrder(OrderDate);
GO

-- Does not support online build index
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder_OrderDate
ON dbo.SalesOrder(OrderDate)
WITH (ONLINE = ON);
GO

-- Does not support include action
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder_OrderDate_@OrderID
ON dbo.SalesOrder(OrderDate)
INCLUDE(OrderID);
GO

-- Does not accept data length more than 18 numeric
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_SalesOrder_UnitPrice
ON dbo.SalesOrder(UnitPrice);
GO

-- Doesn't allow ALTER INDEX REORGANIZE
ALTER INDEX NCSIX_ALL_Columns
ON dbo.SalesOrder REORGANIZE;

-- Doesn't support create base on view
IF OBJECT_ID('dbo.V_SalesOrder', 'V') IS NOT NULL
	DROP TABLE dbo.V_SalesOrder
GO
CREATE VIEW dbo.V_SalesOrder
AS
SELECT TOP 100 *
FROM dbo.SalesOrder
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_V_SalesOrder_ALL_Columns 
ON dbo.V_SalesOrder(OrderID,AutoID,OrderQty,Price,OrderDate);
GO

-- columnstore index table will be read only
UPDATE TOP (1) A
SET OrderQty = 100
FROM dbo.SalesOrder AS A

DELETE TOP(1) A
FROM dbo.SalesOrder AS A

INSERT INTO dbo.SalesOrder(OrderID, AutoID, OrderQty, Price, OrderDate)
SELECT TOP 1 OrderID = OrderID + 1, AutoID, OrderQty, Price, OrderDate
FROM dbo.SalesOrder

GO
-- There is no statistics for columnstore indexes
DBCC SHOW_STATISTICS('dbo.SalesOrder','NCSIX_ALL_Columns')

Msg 35339, Level 16, State 1, Line 97
Multiple nonclustered columnstore indexes are not supported.
Msg 35338, Level 15, State 1, Line 103
Clustered columnstore index is not supported.
Msg 35301, Level 15, State 1, Line 107
CREATE INDEX statement failed because a columnstore index cannot be unique. Create the columnstore index without the UNIQUE keyword or create a unique index without the COLUMNSTORE keyword.
Msg 35302, Level 15, State 1, Line 112
CREATE INDEX statement failed because specifying sort order (ASC or DESC) is not allowed when creating a columnstore index. Create the columnstore index without specifying a sort order.
Msg 35339, Level 16, State 1, Line 117
Multiple nonclustered columnstore indexes are not supported.
Msg 35318, Level 15, State 1, Line 124
CREATE INDEX statement failed because the ONLINE option is not allowed when creating a columnstore index. Create the columnstore index without specifying the ONLINE option.
Msg 35311, Level 15, State 1, Line 128
CREATE INDEX statement failed because a columnstore index cannot have included columns.   Create the columnstore index on the desired columns without specifying any included columns.
Msg 35341, Level 16, State 1, Line 134
CREATE INDEX statement failed. A columnstore index cannot include a decimal or numeric data type with a precision greater than 18.  Reduce the precision of column 'UnitPrice' to 18 or omit column 'UnitPrice'.
Msg 35326, Level 16, State 1, Line 139
ALTER INDEX statement failed because a columnstore index cannot be reorganized. Reorganization of a columnstore index is not necessary.
Msg 2714, Level 16, State 3, Procedure V_SalesOrder, Line 1 [Batch Start Line 145]
There is already an object named 'V_SalesOrder' in the database.
Msg 35305, Level 16, State 1, Line 152
CREATE INDEX statement failed because a columnstore index cannot be created on a view. Consider creating a columnstore index on the base table or creating an index without the COLUMNSTORE keyword on the view.
Msg 35330, Level 15, State 1, Line 157
UPDATE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the UPDATE statement, then rebuilding the columnstore index after UPDATE is complete.

-- DEMO: columnstore index table read_only fixing
USE ColumnStoreDB
GO

ALTER INDEX NCSIX_ALL_Columns
ON dbo.SalesOrder DISABLE;
GO

UPDATE TOP (1) A
SET OrderQty = OrderQty + 1
FROM dbo.SalesOrder AS A

GO
ALTER INDEX NCSIX_ALL_Columns
ON dbo.SalesOrder REBUILD;
GO

-- END DEMO

-- DEMO: columnstore index table read_only fixing using partion switch
IF OBJECT_ID('dbo.SalesOrder_staging', 'U') IS NOT NULL
BEGIN
	DROP TABLE dbo.SalesOrder_staging
END
GO
CREATE TABLE dbo.SalesOrder_staging
(
	OrderID INT NOT NULL
	,AutoID INT NOT NULL
	,UserID INT NOT NULL
	,OrderQty INT NOT NULL
	,Price DECIMAL(8,2) NOT NULL
	,UnitPrice AS Price * OrderQty
	,OrderDate DATETIME NOT NULL,
    CONSTRAINT check_OrderDate CHECK ([OrderDate] > '2018-01-01 00:00' and [OrderDate]<='2019-01-01 00:00')
) ON [PRIMARY];

-- data init for 5 M records.
DECLARE
	@OrderID INT
;
SELECT @OrderID =  max(OrderID) 
FROM dbo.SalesOrder;

;WITH a 
AS (
	SELECT * 
	FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a)
), RoundData
AS(
SELECT TOP(500000)
	OrderID = @OrderID + ROW_NUMBER() OVER (ORDER BY a.a)
	,AutoIDRound = abs(checksum(newid()))
	,Price = a.a * b.a * 10000
	,OrderQty = a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h
)
INSERT INTO dbo.SalesOrder_staging(OrderID, AutoID, UserID, OrderQty, Price, OrderDate)
SELECT 
	OrderID
	,AutoID = cast(ROUND((13 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 101), 0) as int)
	,UserID = cast(ROUND((500 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 10000), 0) as int)
	,OrderQty
	,Price = cast(Price AS DECIMAL(8,2))
	,OrderDate = dateadd(day, cast(ROUND((330 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 1), 0) as int) ,'2018-01-01')
FROM RoundData;
GO

-- create regular B-Tree Clustered Index
CREATE UNIQUE CLUSTERED INDEX CIX_OrderID
ON dbo.SalesOrder_staging(OrderID, OrderDate) 
WITH (ONLINE = ON)
ON [PRIMARY];
GO

--create nonclustered columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX NCSIX_ALL_Columns 
ON dbo.SalesOrder_staging(OrderID, AutoID, UserID, OrderQty, Price, OrderDate)
GO


ALTER PARTITION scheme ps_SalesYear NEXT used [PRIMARY];

--alter partition function pf_SalesYear() split range ('2019-01-01 00:00');
--go

ALTER TABLE dbo.SalesOrder_staging switch TO dbo.SalesOrder 
PARTITION $PARTITION.pf_SalesYear('2018-01-01 01:00');
GO