Category Archives: columnstore

In Pursuit of Batch Mode on Rowstore

In her excellent blog post entitled “Batch Mode Hacks for Rowstore Queries in SQL Server“, Kendra Little b|t pays homage to Itzik Ben-Gan, Niko Neugebauer, and others.

The solutions she details will indeed result in batch mode for rowstore queries. I had already seen the solution proposed by Mr. Ben-Gan, and as is typically the case, a simple example is given to illustrate the concept, and these types of examples are almost always single-threaded.

I have a client that used Itzik Ben-Gan’s solution of creating a filtered nonclustered columnstore index to achieve batch mode on a rowstore (in fact I proposed that the client consider it). They have an OLTP system, and often perform YTD calculations. When they tested, processing time was reduced by 30 to 50 percent, without touching a single line of application code. If that ain’t low hanging fruit, I don’t know what is —

However, during testing, I noticed some intermittent blocking that didn’t make sense to me. But I couldn’t nail it down, and they went live with the “filtered nonclustered columnstore index” solution.

Once they deployed – and there was a lot of concurrency – I could see what had eluded me during my proof of concept: blocking in tempdb.

The repro is super-simple:

Create a table, and insert some sample data

Create a stored procedure that does the following:
SELECT from that table into a #temp table
Create a filtered nonclustered columnstore index on the #temp table, using a filter that cannot possibly be true, i.e. IDcolumn < 0 and IDcolumn > 0
SELECT from the #temp table (return results)

From the first connection, issue a BEGIN TRAN execute the stored procedure. Note the spid for this connection. Then open a separate connection, issue a BEGIN TRAN and execute the stored procedure. Note the spid for this connection.

You’ll notice that the first connection has no issues, but when you execute the proc in the second connection, it gets blocked.

When you peel back the layers, you can see that the first connection requests and obtains a schema modification lock on the #temp table (Sch-M).

The second connection requests a schema stability lock on the same object_id, and is blocked (Sch-S).

To be clear, what’s happening here is that separate connections are placing incompatible locks on the same temporary object in tempdb, which is supposed to be impossible (but in fact the object_id is the same). My gut tells me that this is perhaps related to metadata when creating the NCCI, but I couldn’t prove that.

It should be noted that if you remove the filter on the NCCI, there is no blocking, and also if you use a regular filtered nonclustered index (not columnstore), this issue persists. Of course, in the real world, removing the filter is not an option because what we’re interested in speed, and if there’s one thing that columnstore indexes are not fast at, it’s being created.

Hopefully if/when Microsoft fixes this, it will be back ported to earlier versions of SQL Server.

I can reproduce this on SQL 2016 and 2017 (and even 2019, but that’s not really fair, cause it’s not RTM yet…)

If you think that Microsoft should fix this, please upvote my Azure User Voice entry here.

Repro code:

/*

Ned Otter 
Repro for incompatible lock types when two connections both call the same procedure, 
and a filtered nonclustered columnstore index is created on a #temp table (for batch mode).

If you remove the filter from the NCCI, there is no blocking, but also if you use a regular filtered nonclustered index (not columnstore), this issue persists. 

Version tested against:
SQL 2016/SP2/CU2
SQL 2017 RTM CU5
SQL 2019 CTP2.1

*/



/*
##################################

    Setup: Create table and insert rows

##################################
*/

DROP TABLE IF EXISTS dbo.SourceTable 
GO
CREATE TABLE dbo.SourceTable 
(
     col1 INT NOT NULL
    ,col2 INT NOT NULL
    ,col3 DATETIME 
)

INSERT dbo.SourceTable
(
    col1
   ,col2
   ,col3
)
VALUES
(   12345
   ,6789
   ,'2018-09-01 00:00:00.000'
)
GO

/*
##################################

    Setup: Create procedure

##################################
*/

CREATE OR ALTER PROCEDURE [dbo].[Proc_GetRows]
AS

SELECT *
INTO #TempTable
FROM dbo.SourceTable

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_#TempTable ON #TempTable(col1)
WHERE (col1 < 0 AND col1 > 0)

SELECT col1
      ,col2
      ,col3
FROM #TempTable

GO


/*
##################################

    Execute the following statements in two separate connections

##################################
*/

SELECT @@SPID
GO

SET XACT_ABORT ON
SET NOCOUNT ON 
BEGIN TRANSACTION 
    EXEC [dbo].[Proc_GetRows]

--ROLLBACK

/*
##################################

    Verify locking/blocking

##################################
*/



DROP TABLE IF EXISTS #locks
GO

CREATE TABLE #locks
(
     spid	smallint	
    ,dbid	smallint	
    ,ObjId	int	       
    ,IndId	smallint	
    ,Type	nchar(4)	
    ,Resource nchar(32)	
    ,Mode	nvarchar(8)
    ,Status	nvarchar(5)
)

INSERT #locks
EXEC sp_lock

SELECT DISTINCT 
 '1' AS ConnectionNumber
 ,*
FROM #locks
WHERE spid = <SPID_from_Connection1>
AND Type = 'TAB'
AND ObjId < 0

SELECT DISTINCT
 '2' AS ConnectionNumber
 ,*
FROM #locks
WHERE spid = <SPID_from_Connection2>
AND Type = 'TAB'
AND ObjId < 0

DECLARE @TableID INT = 
(
    SELECT DISTINCT ObjId
    FROM #locks
    WHERE spid = <SPID_from_Connection1>
      AND dbid = 2
      AND Type = 'TAB'
      AND ObjId < 0
)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

Ned Otter

Repro for incompatible lock types when two connections both call the same procedure,

and a filtered nonclustered columnstore index is created on a #temp table (for batch mode).

If you remove the filter from the NCCI, there is no blocking, but also if you use a regular filtered nonclustered index (not columnstore), this issue persists.

Version tested against:

SQL 2016/SP2/CU2

SQL 2017 RTM CU5

SQL 2019 CTP2.1

##################################

Setup: Create table and insert rows

##################################

DROP TABLE IF EXISTS dbo.SourceTable

CREATE TABLE dbo.SourceTable

(

col1 INT NOT NULL

,col2 INT NOT NULL

,col3 DATETIME

)

INSERT dbo.SourceTable

(

col1

,col2

,col3

)

VALUES

( 12345

,6789

,'2018-09-01 00:00:00.000'

)

##################################

Setup: Create procedure

##################################

CREATE OR ALTER PROCEDURE [dbo].[Proc_GetRows]

SELECT *

INTO #TempTable

FROM dbo.SourceTable

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_#TempTable ON #TempTable(col1)

WHERE (col1 < 0 AND col1 > 0)

SELECT col1

,col2

,col3

FROM #TempTable

##################################

Execute the following statements in two separate connections

##################################

SELECT @@SPID

SET XACT_ABORT ON

SET NOCOUNT ON

BEGIN TRANSACTION

EXEC [dbo].[Proc_GetRows]

--ROLLBACK

##################################

Verify locking/blocking

##################################

DROP TABLE IF EXISTS #locks

CREATE TABLE #locks

(

spid smallint

,dbid smallint

,ObjId int

,IndId smallint

,Type nchar(4)

,Resource nchar(32)

,Mode nvarchar(8)

,Status nvarchar(5)

)

INSERT #locks

EXEC sp_lock

SELECT DISTINCT

'1' AS ConnectionNumber

FROM #locks

WHERE spid = <SPID_from_Connection1>

AND Type = 'TAB'

AND ObjId < 0

SELECT DISTINCT

'2' AS ConnectionNumber

FROM #locks

WHERE spid = <SPID_from_Connection2>

AND Type = 'TAB'

AND ObjId < 0

DECLARE @TableID INT =

(

SELECT DISTINCT ObjId

FROM #locks

WHERE spid = <SPID_from_Connection1>

AND dbid = 2

AND Type = 'TAB'

AND ObjId < 0

)

Options for smaller backups

1 Reply

I have a client that is running SQL 2016 Enterprise, and wants to get a full backup offsite every day. They’ve been doing it for over 5 years, and are now seeing scalability issues.

In researching this blog post, I found a lot of useful information written by Dmitri Korotkevitch, who blogged about “Size does matter: 10 ways to reduce the database size and improve performance in SQL Server”. There is some overlap between his post and mine, but those who are interested in this topic will probably want to read both.

SPARSE columns

IF a column contains mostly NULLs, then depending on the data type, you can achieve space savings by using the SPARSE property (documentation here). SPARSE columns can be used with filtered indexes to theoretically reduce storage space and increase query performance. But there are a boatload of gotchas, such as issues with query plan caching (filtered indexes), and the fact that if you use SPARSE columns, neither the table or indexes can have any form of compression (the documentation is clear about not supporting table compression, but does not mention index compression being an issue – but it is).

As the documentation clearly states, when converting a column from non-sparse to sparse, the following steps are taken:

Adds a new column to the table in the new storage size and format
For each row in the table, updates and copies the value stored in the old column to the new column
Removes the old column from the table schema
Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column

For large tables with even a few columns that you wanted to convert to SPARSE, this process would take forever, because you must do this for each column you want to convert.

In 2016+, if the conditions are right, we can get minimal logging plus parallelism for INSERT statements (see this CAT team blog post for more information). You might do something like:

create a new table, adding SPARSE to the relevant columns
use INSERT <newtable> WITH (TABLOCK)/SELECT FROM <originaltable>
recreate indexes
drop original table

In my case, I decided to not use SPARSE columns, because of the restrictions related to using other forms of compression on tables/indexes.

Data and/or index Compression

Compressing rowstore data and/or indexes used to be an Enterprise-only feature, but that’s changed since SQL 2016/SP1. However, to get any real benefit from doing this (especially for an OLTP system), you need to use some form of partitioning (see below), which can be a monumental task. Some have stated that when attempting to use compression on very wide tables (500+ columns), compression can fail, and in that case, SPARSE columns are your only option, assuming you can’t use other features described in this post.

COMPRESS() and DECOMPRESS()

ROW and PAGE compression only work with in-row data. However, SQL 2016 introduced the ability to compress off-row data with the COMPRESS() function. Depending on how much off-row data your databases contain, you might get storage savings when using this, although it will require some form of application change to decompress the relevant column(s) when required.

CLUSTERED COLUMNSTORE

Another formerly Enterprise-only feature, again included in other editions since SQL 2016/SP1. For the right type of workload, i.e. not too write intensive, you might consider replacing a rowstore with a clustered columnstore. I want to be clear that when I write about clustered columnstore indexes replacing a rowstore, I’m referring to on-disk tables only. There’s a lot of confusion about this because memory-optimized tables also support clustered columnstore, but in that case, the columnstore does not replace the rowstore (please refer to my blog post on the differences between columnstore for on-disk vs. in-mem here). When using partitioning with data compression, you can decide which partitions are compressed, if any, and what form of compression to deploy – the supported options are PAGE, ROW, and NONE. Columnstore is “all or nothing at all”, even when used with table partitioning. You can choose between ARCHIVAL and non-archival columnstore compression, but there is no way to designate specific partitions as uncompressed, as is the case with data compression. The deltastore (where inserts initially land) is an uncompressed rowstore.

One potential problem when using clustered columnstore is that you can’t deploy it on a table that has triggers. Also, LOB types (NVARCHAR(MAX)) are not supported for clustered columnstore indexes until SQL 2017.

Separating clustered and PRIMARY KEY

If you have an existing clustered rowstore that’s defined as a CONSTRAINT (for example with CREATE/ALTER TABLE), and you want to replace it with a clustered columnstore, then you’ll have to drop the constraint before creating the columnstore. That’s because the DROP_EXISTING = ON syntax is not supported for ALTER TABLE.

And because the key columns of a clustered index are also stored in every nonclustered index, it might be faster to drop nonclustered indexes before dropping a constraint that’s also the clustering key.

Keep in mind that even though a clustered columnstore contains the word “clustered” – which in the rowstore world means that it’s physically ordered – clustered columnstore indexes have no order. To achieve the best rowgroup elimination, you would first have to physically order your data using a regular clustered index, and then create the clustered columnstore with DROP_EXISTING = ON.

Data types

Violating the fundamentals of database design can have far reaching effects, long after the original designers have moved on. Common mistakes are using MAX for VARCHAR/NVARCHAR columns that don’t need it, like FirstName/LastName/Address, etc., and using DATETIME when you don’t need the time tick values, like for a check date. You’re not likely to see the negative effects of this for a long time, but those who come after you will be left with headaches that are difficult to fix. Let’s say that you had a CheckDate column on a table with billions of rows, and the CheckDate column was part of the clustering key. All nonclustered indexes store the clustering key internally, so instead of storing 3 bytes for a CheckDate column based upon the DATE datatype, each nonclustered index will store an extra 5 bytes (total of 8 bytes) for the DATETIME datatype.

Other solutions

If you want to optimize the size of your backups, what’s been discussed to far can help. But eventually, you’ll probably hit some type of time and/or size constraint when doing backups, even if using compression. One solution to this issue is to use some form of partitioning, be it partitioned tables and/or partitioned views.

With partitioned tables, you can mark filegroups as readonly, back them up once, and from that point on do only full and differential filegroup backups. Even CHECKDB can be run for specific filegroups. But be forewarned – table partitioning was introduced in SQL 2005, and there hasn’t been a lot of investment in this feature in recent years. Partitioned views solve a lot of the problems that exist with partitioned tables, but they have their own gotchas, such as not being able to insert through a partitioned view if the any of the base tables have columns that use the IDENTITY property.

As is often the case, choosing the best solution includes balancing requirements with feature limitations.

The subtleties of In-Memory OLTP Indexing

1 Reply

For this post, I wanted to cover some of the indexing subtleties for memory-optimized tables, with an accent on columnstore indexes

Let’s create a memory-optimized table:

CREATE TABLE [dbo].[InMem]
(
    [PK] [INT] IDENTITY(1, 1) NOT NULL
   ,[col1] [CHAR](100) NOT NULL
   ,PRIMARY KEY NONCLUSTERED HASH ([PK]) WITH (BUCKET_COUNT = 1000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

CREATE TABLE [dbo].[InMem]

(

[PK] [INT] IDENTITY(1, 1) NOT NULL

,[col1] [CHAR](100) NOT NULL

,PRIMARY KEY NONCLUSTERED HASH ([PK]) WITH (BUCKET_COUNT = 1000)

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Now, let’s attempt to create a NONCLUSTERED COLUMNSTORE INDEX:

ALTER TABLE dbo.InMemADD 
ADD INDEX NCCI_InMem NONCLUSTERED COLUMNSTORE (col1);

1 2	ALTER TABLE dbo.InMemADD ADD INDEX NCCI_InMem NONCLUSTERED COLUMNSTORE (col1);

Msg 10794, Level 16, State 76, Line 76
The feature ‘NONCLUSTERED COLUMNSTORE’ is not supported with memory optimized tables.

It fails because we can only create a CLUSTERED columnstore index (CCI). For 25 years, Microsoft SQL Server differentiated between indexes that physically ordered data on storage (CLUSTERED) and those that did not (NONCLUSTERED). Unfortunately, they chose to ignore that pattern when creating the syntax for memory-optimized tables; using the word CLUSTERED is required when creating a columnstore index on memory-optimized tables.

Can we create a clustered columnstore index on a memory-optimized table that is defined as SCHEMA_ONLY?

Only one way to find out:

DROP TABLE IF EXISTS [dbo].[InMem];
GO

CREATE TABLE [dbo].[InMem]
(
    [PK] [INT] IDENTITY(1, 1) NOT NULL
   ,[col1] [CHAR](100) NOT NULL
   ,PRIMARY KEY NONCLUSTERED HASH ([PK]) WITH (BUCKET_COUNT = 1000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

ALTER TABLE dbo.InMem ADD INDEX CCI_InMem CLUSTERED COLUMNSTORE

DROP TABLE IF EXISTS [dbo].[InMem];

CREATE TABLE [dbo].[InMem]

(

[PK] [INT] IDENTITY(1, 1) NOT NULL

,[col1] [CHAR](100) NOT NULL

,PRIMARY KEY NONCLUSTERED HASH ([PK]) WITH (BUCKET_COUNT = 1000)

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

ALTER TABLE dbo.InMem ADD INDEX CCI_InMem CLUSTERED COLUMNSTORE

Msg 35320, Level 16, State 1, Line 39
Column store indexes are not allowed on tables for which the durability option SCHEMA_ONLY is specified.

That won’t work, so let’s create our table with SCHEMA_AND_DATA:

DROP TABLE IF EXISTS [dbo].[InMem];
GO
CREATE TABLE [dbo].[InMem]
(
    [PK] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY NONCLUSTERED
   ,[col1] [CHAR](100) NOT NULL,
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

DROP TABLE IF EXISTS [dbo].[InMem];

CREATE TABLE [dbo].[InMem]

(

[PK] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY NONCLUSTERED

,[col1] [CHAR](100) NOT NULL,

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Now, let’s create a clustered columnstore index:

ALTER TABLE dbo.InMem ADD INDEX CCI_InMem CLUSTERED COLUMNSTORE

1	ALTER TABLE dbo.InMem ADD INDEX CCI_InMem CLUSTERED COLUMNSTORE

Success! Let’s attempt to create a NONCLUSTERED index….

ALTER TABLE dbo.InMem ADD INDEX IX_Index1 (col1);

1	ALTER TABLE dbo.InMem ADD INDEX IX_Index1 (col1);

Msg 10794, Level 16, State 15, Line 117
The operation ‘ALTER TABLE’ is not supported with memory optimized tables that have a column store index.

Ooops – no can do. Once you add a clustered columnstore index to a memory-optimized table, the schema is totally locked down.

What about if we create the CCI and nonclustered index inline?

DROP TABLE IF EXISTS [dbo].[InMem];
GO
CREATE TABLE [dbo].[InMem]
(
    [PK] [INT] IDENTITY(1, 1) NOT NULL
   ,[col1] [CHAR](100) NOT NULL
   ,PRIMARY KEY NONCLUSTERED HASH ([PK]) WITH (BUCKET_COUNT = 1000)
   ,INDEX CCI_InMem CLUSTERED COLUMNSTORE
   ,INDEX IX_InMem1 (col1)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

DROP TABLE IF EXISTS [dbo].[InMem];

CREATE TABLE [dbo].[InMem]

(

[PK] [INT] IDENTITY(1, 1) NOT NULL

,[col1] [CHAR](100) NOT NULL

,PRIMARY KEY NONCLUSTERED HASH ([PK]) WITH (BUCKET_COUNT = 1000)

,INDEX CCI_InMem CLUSTERED COLUMNSTORE

,INDEX IX_InMem1 (col1)

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Awesome! We’ve proven that we can create both clustered columnstore and nonclustered indexes, but we must create them inline.

Now that we’ve got our indexes created, let’s try to add a column:

ALTER TABLE dbo.InMem ADD col2 INT NULL;

1	ALTER TABLE dbo.InMem ADD col2 INT NULL;

Msg 12349, Level 16, State 1, Line 68
Operation not supported for memory optimized tables having columnstore index.

Hey, when I said that the schema is locked down once you add a clustered columnstore index, I mean it!

What type of index maintenance is possible for indexes on memory-optimized tables?

For HASH indexes there is only one possible type of index maintenance, and that’s to modify/adjust the bucket count. There is zero index maintenance for RANGE/NONCLUSTERED indexes.

Let’s create a memory-optimized table with a HASH index, and verify the syntax for rebuilding the bucket count.

DROP TABLE IF EXISTS [dbo].[InMem];
GO
CREATE TABLE [dbo].[InMem]
(
    [PK] [INT] IDENTITY(1, 1) NOT NULL
   ,[col1] [CHAR](100) NOT NULL
   ,PRIMARY KEY NONCLUSTERED HASH ([PK]) WITH (BUCKET_COUNT = 1000)
   ,INDEX IX_InMem1 HASH (col1) WITH (BUCKET_COUNT = 1000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

DROP TABLE IF EXISTS [dbo].[InMem];

CREATE TABLE [dbo].[InMem]

(

[PK] [INT] IDENTITY(1, 1) NOT NULL

,[col1] [CHAR](100) NOT NULL

,PRIMARY KEY NONCLUSTERED HASH ([PK]) WITH (BUCKET_COUNT = 1000)

,INDEX IX_InMem1 HASH (col1) WITH (BUCKET_COUNT = 1000)

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Here’s the syntax for rebuilding the bucket count for a HASH INDEX:

ALTER TABLE dbo.InMem 
ALTER INDEX IX_InMem1 
REBUILD WITH(BUCKET_COUNT = 1001)
GO

ALTER TABLE dbo.InMem

ALTER INDEX IX_InMem1

REBUILD WITH(BUCKET_COUNT = 1001)

We can add a column, as long as we don’t have a CCI in place:

ALTER TABLE dbo.InMem ADD col2 INT NULL;
GO

1 2	ALTER TABLE dbo.InMem ADD col2 INT NULL; GO

How about trying to rebuild the bucket count if we created the memory-optimized table with inline CCI and HASH indexes?

DROP TABLE IF EXISTS [dbo].[InMem];
GO

CREATE TABLE [dbo].[InMem]
(
    [PK] [INT] IDENTITY(1, 1) NOT NULL
   ,[col1] [CHAR](100) NOT NULL
   ,PRIMARY KEY NONCLUSTERED HASH ([PK]) WITH (BUCKET_COUNT = 1000)
   ,INDEX CCI_InMem CLUSTERED COLUMNSTORE
   ,INDEX IX_InMem1 HASH (col1) WITH (BUCKET_COUNT = 1000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

ALTER TABLE dbo.InMem ALTER INDEX IX_InMem1 REBUILD WITH(BUCKET_COUNT = 1001)
GO

DROP TABLE IF EXISTS [dbo].[InMem];

CREATE TABLE [dbo].[InMem]

(

[PK] [INT] IDENTITY(1, 1) NOT NULL

,[col1] [CHAR](100) NOT NULL

,PRIMARY KEY NONCLUSTERED HASH ([PK]) WITH (BUCKET_COUNT = 1000)

,INDEX CCI_InMem CLUSTERED COLUMNSTORE

,INDEX IX_InMem1 HASH (col1) WITH (BUCKET_COUNT = 1000)

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

ALTER TABLE dbo.InMem ALTER INDEX IX_InMem1 REBUILD WITH(BUCKET_COUNT = 1001)

Msg 10794, Level 16, State 13, Line 136
The operation ‘ALTER TABLE’ is not supported with memory optimized tables that have a column store index.

You can’t rebuild that index if you also have a columnstore index on the table. We would have to drop the columnstore index, reconfigure the bucket count for the HASH index, and then recreate the columnstore index. Both the drop and the create of the columnstore index will be fully logged, and executed serially. Not a huge problem if the amount of data is not too large, but it’s a potentially much larger problem if you’ve got a lot of data.

We can create a clustered columnstore index on a #temp table (on-disk):

DROP TABLE IF EXISTS #tempgo
GO

CREATE TABLE #temp
(
    col1 INT NOT NULL
);
GO

-- succeeds
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_InMem ON #temp (col1);

--OR clustered, which also succeeds
CREATE CLUSTERED COLUMNSTORE INDEX NCCI_InMem ON #temp;

DROP TABLE IF EXISTS #tempgo

CREATE TABLE #temp

(

col1 INT NOT NULL

);

-- succeeds

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_InMem ON #temp (col1);

--OR clustered, which also succeeds

CREATE CLUSTERED COLUMNSTORE INDEX NCCI_InMem ON #temp;

We can create multiple indexes with a single command:

DROP TABLE IF EXISTS [dbo].[InMemLOB];
GO
CREATE TABLE [dbo].[InMemLOB]
(
    [OrderId]      [INT]           IDENTITY NOT NULL
   ,[StoreID]      INT             NOT NULL
   ,[Notes1]       [VARCHAR](8000) NULL
   ,[ValidFrom]    [DATETIME2](7)  NOT NULL
   ,[ValidTo]      [DATETIME2](7)  NOT NULL
   ,CONSTRAINT [PK_InMemLOB_OrderID] PRIMARY KEY NONCLUSTERED (OrderId)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

ALTER TABLE [InMemLOB]
 ADD 
 INDEX IX_1 (OrderId) 
,INDEX IX_2 (OrderId) 
,INDEX IX_3 (OrderId) 
,INDEX IX_4 (OrderId) 
,INDEX IX_5 (OrderId) 
,INDEX IX_6 (OrderId) 
,INDEX IX_7 (OrderId)

DROP TABLE IF EXISTS [dbo].[InMemLOB];

CREATE TABLE [dbo].[InMemLOB]

(

[OrderId] [INT] IDENTITY NOT NULL

,[StoreID] INT NOT NULL

,[Notes1] [VARCHAR](8000) NULL

,[ValidFrom] [DATETIME2](7) NOT NULL

,[ValidTo] [DATETIME2](7) NOT NULL

,CONSTRAINT [PK_InMemLOB_OrderID] PRIMARY KEY NONCLUSTERED (OrderId)

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

ALTER TABLE [InMemLOB]

ADD

INDEX IX_1 (OrderId)

,INDEX IX_2 (OrderId)

,INDEX IX_3 (OrderId)

,INDEX IX_4 (OrderId)

,INDEX IX_5 (OrderId)

,INDEX IX_6 (OrderId)

,INDEX IX_7 (OrderId)

Can we create a columnstore index on a memory-optimized table variable?

DROP TYPE IF EXISTS dbo.typeTableMem
GO

CREATE TYPE dbo.typeTableMem AS TABLE
(
    [PK] INT NOT NULL PRIMARY KEY NONCLUSTERED
   ,[col1] NVARCHAR(255) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON);
GO

DECLARE @Table1 dbo.typeTableMem;
-- fails--Msg 102, Level 15, State 1--Incorrect syntax near '@Table1'.
ALTER TABLE @Table1 ADD INDEX CCI_InMem CLUSTERED COLUMNSTORE

DROP TYPE IF EXISTS dbo.typeTableMem

CREATE TYPE dbo.typeTableMem AS TABLE

(

[PK] INT NOT NULL PRIMARY KEY NONCLUSTERED

,[col1] NVARCHAR(255) NOT NULL

)

WITH (MEMORY_OPTIMIZED = ON);

DECLARE @Table1 dbo.typeTableMem;

-- fails--Msg 102, Level 15, State 1--Incorrect syntax near '@Table1'.

ALTER TABLE @Table1 ADD INDEX CCI_InMem CLUSTERED COLUMNSTORE

Create a table that includes a LOB column with a MAX datatype, then add a clustered columnstore index:

DROP TABLE IF EXISTS [dbo].[InMemLOB];
GO
CREATE TABLE [dbo].[InMemLOB]
(
    [OrderId] [INT] IDENTITY NOT NULL
   ,[StoreID] INT NOT NULL
   ,[CustomerID] INT NOT NULL
   ,[OrderDate] [DATETIME] NOT NULL
   ,[DeliveryDate] DATETIME NULL
   ,[Amount] FLOAT NOT NULL
   ,[Notes] [NVARCHAR](MAX) NULL
   ,[ValidFrom] [DATETIME2](7) NOT NULL
   ,[ValidTo] [DATETIME2](7) NOT NULL
   ,CONSTRAINT [PK_InMemLOB_OrderID] PRIMARY KEY NONCLUSTERED (OrderId)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

ALTER TABLE dbo.[InMemLOB] 
ADD INDEX CCI_InMemLOB CLUSTERED COLUMNSTORE

DROP TABLE IF EXISTS [dbo].[InMemLOB];

CREATE TABLE [dbo].[InMemLOB]

(

[OrderId] [INT] IDENTITY NOT NULL

,[StoreID] INT NOT NULL

,[CustomerID] INT NOT NULL

,[OrderDate] [DATETIME] NOT NULL

,[DeliveryDate] DATETIME NULL

,[Amount] FLOAT NOT NULL

,[Notes] [NVARCHAR](MAX) NULL

,[ValidFrom] [DATETIME2](7) NOT NULL

,[ValidTo] [DATETIME2](7) NOT NULL

,CONSTRAINT [PK_InMemLOB_OrderID] PRIMARY KEY NONCLUSTERED (OrderId)

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

ALTER TABLE dbo.[InMemLOB]

ADD INDEX CCI_InMemLOB CLUSTERED COLUMNSTORE

Msg 35343, Level 16, State 1, Line 22 The statement failed. Column ‘Notes’ has a data type that cannot participate in a columnstore index. Omit column ‘Notes’.

Msg 1750, Level 16, State 1, Line 22 Could not create constraint or index. See previous errors.

For memory-optimized tables, LOB columns prevent creation of a clustered columnstore index.

Now let’s try creating a table using CHAR(8000). Astute readers will notice that the following table would create rows that are 32,060 bytes wide – this would fail for on-disk tables, but is perfectly valid for memory-optimized tables:

DROP TABLE IF EXISTS [dbo].[InMemLOB];
GO
CREATE TABLE [dbo].[InMemLOB]
(
    [OrderId] [INT] IDENTITY NOT NULL
   ,[StoreID] INT NOT NULL
   ,[CustomerID] INT NOT NULL
   ,[OrderDate] [DATETIME] NOT NULL
   ,[DeliveryDate] DATETIME NULL
   ,[Amount] FLOAT
   ,[Notes1] [CHAR](8000) NULL
   ,[Notes2] [CHAR](8000) NULL
   ,[Notes3] [CHAR](8000) NULL
   ,[Notes4] [CHAR](8000) NULL
   ,[ValidFrom] [DATETIME2](7) NOT NULL
   ,[ValidTo] [DATETIME2](7) NOT NULL
   ,CONSTRAINT [PK_InMemLOB_OrderID] PRIMARY KEY NONCLUSTERED (OrderId)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

DROP TABLE IF EXISTS [dbo].[InMemLOB];

CREATE TABLE [dbo].[InMemLOB]

(

[OrderId] [INT] IDENTITY NOT NULL

,[StoreID] INT NOT NULL

,[CustomerID] INT NOT NULL

,[OrderDate] [DATETIME] NOT NULL

,[DeliveryDate] DATETIME NULL

,[Amount] FLOAT

,[Notes1] [CHAR](8000) NULL

,[Notes2] [CHAR](8000) NULL

,[Notes3] [CHAR](8000) NULL

,[Notes4] [CHAR](8000) NULL

,[ValidFrom] [DATETIME2](7) NOT NULL

,[ValidTo] [DATETIME2](7) NOT NULL

,CONSTRAINT [PK_InMemLOB_OrderID] PRIMARY KEY NONCLUSTERED (OrderId)

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Msg 41833, Level 16, State 1, Line 29 Columnstore index ‘CCI_InMemLOB’ cannot be created, because table ‘InMemLOB’ has columns stored off-row.
Columnstore indexes can only be created on memory-optimized table if the columns fit within the 8060 byte limit for in-row data.
Reduce the size of the columns to fit within 8060 bytes.

Create a table with non-MAX LOB columns, but they are stored on-row, then add a clustered columnstore index:

DROP TABLE IF EXISTS [dbo].[InMemLOB];
GO
CREATE TABLE [dbo].[InMemLOB]
(
    [OrderId] [INT] IDENTITY NOT NULL
   ,[StoreID] INT NOT NULL
   ,[Notes1] [CHAR](8000) NULL
   ,[ValidFrom] [DATETIME2](7) NOT NULL
   ,[ValidTo] [DATETIME2](7) NOT NULL
   ,CONSTRAINT [PK_InMemLOB_OrderID] PRIMARY KEY NONCLUSTERED (OrderId)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

-- works
ALTER TABLE InMemLOB ADD INDEX IndOrders_StoreID (StoreID);
GO

-- works
ALTER TABLE dbo.[InMemLOB] ADD INDEX CCI_InMemLOB CLUSTERED COLUMNSTORE
GO

--Add data to table:
SET NOCOUNT ON
GO
INSERT dbo.[InMemLOB]
SELECT
    3 AS [StoreID]
   ,'ABC' AS [Notes1]
   ,GETDATE() AS [ValidFrom]
   ,GETDATE() AS [ValidTo]
GO 1000

SELECT *
FROM dbo.[InMemLOB]

DROP TABLE IF EXISTS [dbo].[InMemLOB];

CREATE TABLE [dbo].[InMemLOB]

(

[OrderId] [INT] IDENTITY NOT NULL

,[StoreID] INT NOT NULL

,[Notes1] [CHAR](8000) NULL

,[ValidFrom] [DATETIME2](7) NOT NULL

,[ValidTo] [DATETIME2](7) NOT NULL

,CONSTRAINT [PK_InMemLOB_OrderID] PRIMARY KEY NONCLUSTERED (OrderId)

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- works

ALTER TABLE InMemLOB ADD INDEX IndOrders_StoreID (StoreID);

-- works

ALTER TABLE dbo.[InMemLOB] ADD INDEX CCI_InMemLOB CLUSTERED COLUMNSTORE

--Add data to table:

SET NOCOUNT ON

INSERT dbo.[InMemLOB]

SELECT

3 AS [StoreID]

,'ABC' AS [Notes1]

,GETDATE() AS [ValidFrom]

,GETDATE() AS [ValidTo]

GO 1000

SELECT *

FROM dbo.[InMemLOB]

Let’s create a natively compiled module that selects from this table:

DROP PROCEDURE IF EXISTS dbo.InMem_native_sp
GO
CREATE PROCEDURE dbo.InMem_native_sp
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT
        [OrderId]
       ,[StoreID]
       ,[Notes1]
       ,[ValidFrom]
       ,[ValidTo]
    FROM dbo.[InMemLOB];
END;
GO

DROP PROCEDURE IF EXISTS dbo.InMem_native_sp

CREATE PROCEDURE dbo.InMem_native_sp

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

SELECT

[OrderId]

,[StoreID]

,[Notes1]

,[ValidFrom]

,[ValidTo]

FROM dbo.[InMemLOB];

END;

ENABLE “Actual Plan” and SELECT – which index is used?

SELECT
    [OrderId]
   ,[StoreID]
   ,[Notes1]
   ,[ValidFrom]
   ,[ValidTo]
FROM dbo.[InMemLOB];

SELECT

[OrderId]

,[StoreID]

,[Notes1]

,[ValidFrom]

,[ValidTo]

FROM dbo.[InMemLOB];

Now highlight the EXEC statement, and click “Estimated Plan” – which index is used?

EXEC dbo.InMem_native_sp

1	EXEC dbo.InMem_native_sp

The SELECT statement uses the columnstore index, but the natively compiled procedure does not (that’s because natively compiled procedures ignore columnstore indexes).

Summing up

In this post, we’ve covered some of the finer points of indexing memory-optimized tables. Never know when they might come in handy….

SQL 2017 In-Memory roundup

1 Reply

SQL Server 2017 includes enhancements to many features, and some of those enhancements include In-Memory OLTP.

Microsoft states that ALTER TABLE for memory-optimized tables is now “usually substantially faster”. I asked for clarity about that – if it means that ALTER TABLE is faster for the same events that were able to be executed in parallel and minimally logged in SQL 2016, or if there are new ALTER TABLE statements which now execute in parallel. They replied that there is no change to the set of operations that executed in parallel. So the ALTER TABLE commands that executed fast now (theoretically) execute faster.
Up to and including SQL 2016, the maximum number of nonclustered indexes on a memory-optimized table was eight, but that limitation has been removed for SQL 2017. I’ve tested this with almost 300 indexes, and it worked. With this many supported indexes, it’s no wonder they had to….
Enhance the index rebuild performance for nonclustered indexes during database recovery. I confirmed with Microsoft that the database does not have be in SQL 2017 compatibility mode (140) to benefit from the index rebuild enhancement. This type of rebuild happens not only for database restore and failover, but also for other “recovery events” – see my blog post here.
In SQL 2017, memory-optimized tables now support JSON in native modules (functions, procedures and check constraints).
Computed columns, and indexes on computed columns are now supported
TSQL enhancements for natively compiled modules include CASE, CROSS APPLY, and TOP (N) WITH TIES
Transaction log redo of memory-optimized tables is now done in parallel. This has been the case for on-disk tables since SQL 2016, so it’s great that this potential bottleneck for REDO has been removed.
Memory-optimized filegroup files can now be stored on Azure Storage, and you can also backup and restore memory-optimized files on Azure Storage.
sp_spaceused is now supported for memory-optimized tables
And last but definitely not least, drum roll, please…….we can now rename memory-optimized tables and natively compiled modules

While Microsoft continues to improve columnstore indexes for on-disk tables, unfortunately columnstore for memory-optimized tables gets left further and further behind. Case in point would be support for LOB columns for on-disk tables in SQL 2017, but no such support for memory-optimized tables. And my good friend Niko Neugebauer (b|t) just reminded me that computed columns for on-disk CCI are supported in SQL 2017, but they are not supported for in-memory CCI. For an in-depth comparison of columnstore differences between on-disk and memory-optimized tables, see my post here.

In addition to what’s listed above, I tested the following functionality for natively compiled stored procedures:

STRING_AGG()

This works, but you can’t use character functions, such as CHAR(13):

CREATE PROCEDURE dbo.Proc_VehicleRegistration
WITH NATIVE_COMPILATION, SCHEMABINDING AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    SELECT
        STRING_AGG(VehicleRegistration, CHAR(13)) AS csv – fails
    FROM Warehouse.VehicleTemperatures
    WHERE Warehouse.VehicleTemperatures.VehicleTemperatureID BETWEEN 65190 AND 65200
END;
GO

CONCAT_WS()

CREATE OR ALTER PROCEDURE dbo.Proc_VehicleTemperatures
WITH NATIVE_COMPILATION, SCHEMABINDING AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    SELECT CONCAT_WS( ' - ', VehicleTemperatureID, VehicleRegistration) AS DatabaseInfo
    FROM Warehouse.VehicleTemperatures

END;
GO

EXEC dbo.Proc_VehicleTemperatures
GO

TRIM()

CREATE OR ALTER PROCEDURE dbo.Proc_TrimTest
WITH NATIVE_COMPILATION, SCHEMABINDING AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    SELECT TRIM(VehicleRegistration) AS col1
    FROM Warehouse.VehicleTemperatures

END;
GO
EXEC dbo.Proc_TrimTest
GO

TRANSLATE()

CREATE OR ALTER PROCEDURE dbo.Proc_TranslateTest
WITH NATIVE_COMPILATION, SCHEMABINDING AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');
END;
GO
EXEC dbo.Proc_TranslateTest
GO

sys.dm_db_stats_histogram()

CREATE STATISTICS stat_VehicleTemperatures ON Warehouse.VehicleTemperatures(VehicleRegistration)

SELECT s.object_id, OBJECT_NAME(s.object_id), hist.step_number, hist.range_high_key, hist.range_rows, 
    hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE OBJECT_NAME(s.object_id) = 'VehicleTemperatures'

STRING_AGG()

This works, but you can’t use character functions, such as CHAR(13):

CREATE PROCEDURE dbo.Proc_VehicleRegistration

WITH NATIVE_COMPILATION, SCHEMABINDING AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')

SELECT

STRING_AGG(VehicleRegistration, CHAR(13)) AS csv – fails

FROM Warehouse.VehicleTemperatures

WHERE Warehouse.VehicleTemperatures.VehicleTemperatureID BETWEEN 65190 AND 65200

END;

CONCAT_WS()

CREATE OR ALTER PROCEDURE dbo.Proc_VehicleTemperatures

WITH NATIVE_COMPILATION, SCHEMABINDING AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')

SELECT CONCAT_WS( ' - ', VehicleTemperatureID, VehicleRegistration) AS DatabaseInfo

FROM Warehouse.VehicleTemperatures

END;

EXEC dbo.Proc_VehicleTemperatures

TRIM()

CREATE OR ALTER PROCEDURE dbo.Proc_TrimTest

WITH NATIVE_COMPILATION, SCHEMABINDING AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')

SELECT TRIM(VehicleRegistration) AS col1

FROM Warehouse.VehicleTemperatures

END;

EXEC dbo.Proc_TrimTest

TRANSLATE()

CREATE OR ALTER PROCEDURE dbo.Proc_TranslateTest

WITH NATIVE_COMPILATION, SCHEMABINDING AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');

END;

EXEC dbo.Proc_TranslateTest

sys.dm_db_stats_histogram()

CREATE STATISTICS stat_VehicleTemperatures ON Warehouse.VehicleTemperatures(VehicleRegistration)

SELECT s.object_id, OBJECT_NAME(s.object_id), hist.step_number, hist.range_high_key, hist.range_rows,

hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows

FROM sys.stats AS s

CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist

WHERE OBJECT_NAME(s.object_id) = 'VehicleTemperatures'

My wish list for the In-Memory OLTP feature is huge, but it’s great to see Microsoft continually improve and extend it.

Clustered columnstore: on-disk vs. in-mem

4 Replies

This post will highlight the fairly wide gap in functionality between clustered columnstore indexes for on-disk tables compared to memory-optimized tables, for SQL 2016.

The first thing to note – which is not specific to columnstore for either type of table – is that Microsoft chose a really poor name for this type of index.

Why do I say that?

For the following reasons:

1. Traditional/regular clustered indexes physically sort data, but data within a columnstore index is unordered (that’s true for clustered and nonclustered columnstore indexes).

2. For a traditional/regular clustered index, the key columns are added to all nonclustered indexes. Columnstore indexes don’t have a “key” so nothing about them gets copied to any nonclustered indexes.

Now that we’ve established the issues with the name – and the potential confusion as a result – let’s get to the differences between clustered columnstore indexes for on-disk tables compared to memory-optimized tables.

The only type of columnstore index you are allowed to create on a memory-optimized table is a clustered columnstore. But as you’ll find out in a moment, that naming convention has it’s own set of issues when discussing memory-optimized tables…..

1. Primary data source

On-disk: When you create a clustered columnstore index on a disk-based table, it actually replaces the rowstore, and becomes the primary data source. Columnstore compression typically reduces the storage footprint greatly (often by ~90% or so), depending on the specific data types represented in the table.

Memory-optimized: When you create a clustered columnstore index on a memory-optimized table, the rows in memory remain the primary data source. Creating a “clustered columnstore” index on a memory-optimized table actually increases the storage footprint (same for nonclustered HASH and RANGE indexes ) by about 10%. That’s why I say that all indexes on memory-optimized tables are nonclustered (because they are). Keep in mind that “storage footprint” in this case means “additional memory”. Despite what you may have read or heard elsewhere, clustered columnstore indexes on memory-optimized tables are persisted to disk, so that also take up space on your persistent storage. This reason this is done is to reduce the impact on RTO (see my blog post here for more info).

2. Schema changes

On-disk: After adding a clustered columnstore index to an on-disk table, you can use ALTER TABLE to modify the schema, adding or dropping both columns and nonclustered indexes.

Memory-optimized: After adding a clustered columnstore index to an memory-optimized table, you can not use ALTER TABLE to modify the schema. The schema becomes read-only, but of course you can still perform DML on the table.

3. LOB data

On-disk: You can create a clustered columnstore index on an on-disk table that has LOB data in vNext (not SQL 2014 or 2016).

Memory-optimized: As of vNext RC1, LOBs are not supported for memory-optimized tables, although this might change by the time we get to RTM.

(Update: 27 Aug 2017 – SQL 2017 does not support LOB columns for CCI on memory-optimized tables)

4. NC indexes

On-disk: After you create a clustered columnstore index on an on-disk table, you can use ALTER TABLE to create nonclustered indexes.

Memory-optimized: After you create a clustered columnstore index on a memory-optimized table, you can not use ALTER TABLE to create nonclustered indexes, but they can be created inline as part of the CREATE TABLE statement.

5. Parallelism

On-disk: When you query an on-disk table that has a columnstore index, the database engine can use parallelism to process the results more quickly.

Memory-optimized: When you query a memory-optimized table that has a columnstore index, the database engine can use parallelism to process the results more quickly, BUT, that statement is only true if you use interop. Natively compiled modules are always executed serially.

6. Archival compression

On-disk: There are two compression options for on-disk tables: columnstore and columnstore archival.

Memory-optimized: There is only one compression option for memory-optimized tables: columnstore.

7. Index maintenance

On-disk: You can use REBUILD or REORGANIZE for clustered columnstore indexes for on-disk tables.

Memory-optimized: REORGANIZE is not supported for memory-optimized tables, but in order to migrate rows from the virtual delta rowgroup into compressed rowgroups, you can use: sys.sp_memory_optimized_cs_migration. REBUILD is supported, but only for changing the bucket count for HASH indexes.

8. Filtered indexes

On-disk: For on-disk tables, it’s possible to create a nonclustered columnstore index with a filter.

Memory-optimized: For memory-optimized tables, it’s not possible to create a clustered columnstore index with a filter (and you cannot create a nonclustered columnstore index).

9. Temporary structures

On-disk: I’m not sure why you’d want to do it, but it’s possible to create a clustered columnstore index on a #temp table.

Memory-optimized: The equivalent of #temp tables would be memory-optimized table variables. It’s not possible to create clustered columnstore indexes on them, and besides, memory-optimized table variables are only able to be created with the body of a natively compiled module, and as we discussed earlier in this post, natively compiled modules cannot make use of columnstore indexes.

10. Other items to note

There are a few more wrinkles in the memory-optimized universe for columnstore indexes…..

Durability: The memory-optimized table must be durable (created with DURABILITY = SCHEMA_AND_DATA)

Off-row: You cannot create a clustered columnstore index on a memory-optimized table that has off-row columns

11. (special thanks to Niko Neugebauer)

On-disk: Columnstore contains Vertipaq optimisations (this changes for CCI when we build secondary nonclustered rowstore indexes).

Memory-optimzed: Columnstore does not contain Vertipaq optimisations.

12. Computed columns (special thanks to Niko Neugebauer)

On-disk: CCI supports computed columns

Memory-optimzed: CCI does not support computed columns (but memory-optimized tabes support computed columns and indexes on computed columns)

Ned Otter Blog

SQL Server DBA and Musician

Category Archives: columnstore

In Pursuit of Batch Mode on Rowstore

Options for smaller backups

The subtleties of In-Memory OLTP Indexing

SQL 2017 In-Memory roundup

Clustered columnstore: on-disk vs. in-mem