Questions tagged [columnstore]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
10 views

Joining table with non clustered columnstore index to OPENJSON

I have a table defined which looks like this: CREATE TABLE [STAGING].[place]( [place_id] [bigint] IDENTITY(0,1) NOT NULL, [modified_at] [datetime2](7) NOT NULL, PRIMARY KEY CLUSTERED ( [...
4
votes
1answer
77 views

MSSQL - How to support aggregate pushdown on indexed view with SUM(decimal)

I have an indexed view with non-clustered columnstore index, e.g. CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS SELECT BusinessDate, ItemId ,...
9
votes
2answers
172 views

How to Prevent Partitioned Columnstore Deadlocks on SELECT

I have three Clustered Columnstore Index (CCI) tables in SQL Server 2016. All of these CCIs are in the same partitioning scheme, based on Tenant ID. Lately, and inconsistently, I am getting deadlocks ...
6
votes
1answer
285 views

Slide Partition from Rowstore to Columnstore

I have a rowstore table which contains ~10 billion records. The table is partitioned on an INT field and has about 1,400 active partitions. I also have a clustered columnstore table built on the same ...
2
votes
1answer
77 views

Storing Social Security Number as DECIMAL(10,9) for Columnstore

I am working on a VERY large database (10B + rows) that performs matching on SSN and BirthDate to try and find duplicate records. The table is using columnstore compression (SQL SERVER 2016) and it ...
1
vote
3answers
105 views

Database solution for a large matrix/very wide table

Suppose I have these two data frames (simplified for my question): users +---------+ | user_id | +---------+ | 1 | | 2 | | ... | +---------+ articles +------------+------------+ | ...
0
votes
1answer
141 views

Performance Metrics: Columnstore Indexes vs SSAS Cubes [closed]

What has faster performance speed: Columnstore Indexes or SSAS Cubes? Are Columnstore indexes proven to be faster than SSAS cubes (SQL Analysis Services)? Have their been any performance studies ...
10
votes
3answers
360 views

Filter condition not correctly applied to Clustered Columnstore index

Using the below example, the predicates are the same, however the top statement (correctly) returns 0 rows, the bottom statement returns 1 - even though the predicates do NOT match: declare @barcode ...
0
votes
0answers
43 views

MS SQL 2017: Generate estimate execution plan take more than 3 min

Sometimes generate Execution Plan (estimated) for simple query take ~3min Query: SELECT MAX(field) FROM table_name WHERE Org = x and PartKey = y The table is columnstore cluster index SQL Server ...
2
votes
1answer
71 views

Does Batch Mode only apply to Agg Functions on ColumnStore Index

Azure SQL Database - Standard Edition (S3 Service Tier) Why does Batch Mode only come into effect when using Aggregate Functions? DROP TABLE IF EXISTS dbo.TransCS CREATE TABLE dbo.TransCS ( ...
5
votes
1answer
211 views

Columnstore index for Key/Value Pair table on SQL Server

I need to build a database for storing assessments. Each assessment can have an indefinite number of questions, and an indefinite number of responses. Responses for each assessment can grow up to ...
-1
votes
1answer
79 views

Should I place Kimball Data Warehouse in Microsoft Columnstore Index on Premise?

Are Columnstore tables now the vendor default method for Kimball Data warehouse tables with Dim and Facts for On Premise? I know in the cloud, Microsoft automaticaly recommends Azure Data warehouses ...
0
votes
1answer
29 views

MariaDB Columnstore unexplained behaviour using CASE

We were updating column value based on col 'string_hash_id' value, but we couldn't get through: Error Code: 1815. Internal error: CAL0002: Update Failed: TreeNode::getDecimalVal: non-support ...
2
votes
1answer
62 views

Microsoft Column Store Database?

Using Microsoft technologies/products is it possible to create a column store database with column family sets? That is each row being able to have a different number and type of columns. Colmun ...
3
votes
1answer
200 views

SELECT from Clustered Columnstore results in LOB Reads

I am performing a SELECT from the following Clustered Columnstore Index which contains 5.2 Billion Records. When I review the output of STATISTICS IO, I am seeing LOB Logical reads. My question is ...
7
votes
2answers
171 views

How many partitions should I make for my clustered columnstore index tables? Should I partition the rowstore tables also?

I have a data warehouse comprised of four clustered columnstore index tables (CCI) and nine rowstore tables. These tables are used only for analytics and the CCI data is inserted from staging tables ...
-2
votes
1answer
114 views

Is there any Performance Issue joining Row based Table to Clustered Columnstore Index Table [closed]

Is there any Performance Degradation joining Row based Table to Clustered Columnstore Index Table? Does SQL Server have any guidance for or against this? We have a table that is 2 million rows, and ...
1
vote
1answer
546 views

How Large Table before Using Clustered Columnstore Index

At what size should we consider applying Columnstore Index on a table? Is there a SQL Server storage or design guidance? Our Data Warehouse is 5TB total. Additionally, we have tables which are 100GB ...
2
votes
1answer
244 views

Difference between Clustered Columnstore Index Table in Azure SQL and Table in Azure Data Warehouse

What is the essential difference between a Clustered Columnstore Index table in Regular Azure SQL, and a table in Azure Data Warehouse? They both have columnar storage, no foreign keys, no primary ...
1
vote
2answers
918 views

Can a table have Both a Clustered Columnstore Index and Nonclustered Columnstore Index?

Can I a table have both Clustered Columnstore Index and Nonclustered Columnstore Index? I am seeing example strategies to use Clustered Columnstore Indexes with only Nonclustered Binary row indexes. ...
11
votes
1answer
171 views

Why does batch mode window aggregate yield arithmetic overflow?

The following query performs a windowed SUM over a columnstore table with 1500 total rows, each of which has the value 0 or 1, and it overflows the INT data type. Why is this happening? SELECT a, p, ...
2
votes
2answers
184 views

Performance Implications of encrypted data in a Clustered Columnstore Index

I am using SQL Server 2016 and expecting some details on below points regarding performance point of view: Is there any performance gain or additional benefit on creating a primary key - unique ...
4
votes
1answer
210 views

How to drop an in-memory temporal columnstore

Consider the following: CREATE DATABASE [Foo] ALTER DATABASE [Foo] ADD FILEGROUP XTP CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE [Foo] ADD FILE (NAME=XTP,FILENAME='C:\Program Files\Microsoft SQL ...
3
votes
1answer
316 views

How Ola Hallengrens Index maintenance scripts handles columnstore indexes?

I'd like to know how Ola's script handles columnstore indexes. Couln't find any information about that, only in the version history it was mentioned that script does have a support. My experience is ...
1
vote
2answers
59 views

Will table partitions help me?

I have 10 tables. dbo.Table2008 dbo.Table2009 ... dbo.Table2018 Each table holds approximately 500,000,000 records which are 20 columns wide (if that matters). And each table has a clustered ...
6
votes
1answer
483 views

Clustered columnstore index space usage

I have a simple table with a clustered columnstore index: ID INT NOT NULL, Hash BINARY(20) NOT NULL This table has some billion rows and according to sp_spaceused, sys.allocation_units and SSMS ...
1
vote
2answers
78 views

Why are column store databases the new thing now? [closed]

Column store databases have several advantages over traditional databases - most notably compression and faster seek. Column store seem to be the new fad for databases in the past few years, with HANA ...
15
votes
2answers
285 views

Columnstore Index in read_only filegroup prevents CheckDB

It appears setting a filegroup to read_only prevents dbcc checkdb for the entire database if the filegroup contains a columnstore index. When attempting to run checkdb or checkfilegroup (for any ...
1
vote
1answer
35 views

select runs in batch mode, but CTAS runs in row mode

I have the same query that shows as running in batch mode when run as a select statement, but row mode when the same query is the select part of a CTAS operation. Both tables (plus the resulting table)...
3
votes
2answers
103 views

Can I artificially limit the Columnstore cache size in SQL Server?

I am trying to evaluate the potential performance of a data science workload on a large-ish dataset (~200GB). We have had excellent results using columnstore indexes in SQL Server 2017. However, the ...
0
votes
0answers
310 views

Using clustered columnstore indexes in conjunction with non-clustered columnstore indexes

Table A has approximately 500 million records in it and is of the form similar to the following | ID | FirstName | LastName | MiddleName | ------------------------------------------ | 1 | John |...
3
votes
1answer
499 views

When are dimension tables still needed using clustered columnstore indexing?

I am using MS SQL Server 2016 Clustered Columnstore Indexing (let's call it CCI) in my reporting database. In initial designs I was thinking star schema but then I started playing with CCI. Now I ...
1
vote
2answers
349 views

How to install columnstore in MariaDB on Mac OS X with Homebrew

I would analyse time-series data using MariaDB ColumnStore but error encountered MariaDB [(none)]> CREATE DATABASE forex; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> USE forex; ...
2
votes
2answers
2k views

Creating non-clustered index on large table fills transaction logs

Using SQL Server 2016, I'm trying to create a non-clustered covering index on a table in my database that is of the structure int, bigint, int, varchar(20), varchar(4000). The size is ~13,241,928 rows....
1
vote
0answers
51 views

What does column family-oriented datastore mean in practice

I was reading this article about HBase. Within it, the storage format of HBase is described: HBase is referred to as a column family-oriented data store. It’s also row-oriented: each row is indexed ...
2
votes
2answers
1k views

What to do when you need a cursor and you have a columnstore clustered index

I have a very large SQL Server table with a ColumnStore clustered index. The table is very fast to query, so this is good. However I need to do some updates to a large portion of records in the ...
0
votes
1answer
56 views

Columnstore Aggregate Pushdown doesn´t work

Recently I started investigating columnstore indexes for some warehouse-behaving part of our system. What I can´t achieve yet is to make aggregate pushdown work. I've checked data types and all ...
4
votes
2answers
172 views

Columnstore index on multi-tenant database

I have a sql azure database that is used only for analytics. It is constantly being updated with data pushed from the OLTP system. The OLTP inserts/updates/deletes are pushed via webhooks to a ...
1
vote
0answers
60 views

Why are pages being removed from the buffer cache

I have an test environment with SQL Server 2016 SP1 installed. My machine has 32GB of RAM and I give 30GB to SQL Server. I have 2 databases in my server - BGD and, BGD_Mem. The second one contains ...
2
votes
0answers
199 views

Table design pattern that can support partitioning, clustered columnstore index, and spatial data?

I am designing a spatial database schema for SQL Server 2016 with a table that is going to hold a large amount of rows (50+ billions, increasing by 50+ millions per day). Requirements: Spatial ...
7
votes
2answers
550 views

Do BIT columns offer any performance advantages for CCIs?

Do BIT columns offer any performance advantages when used in Clustered Columnstore Indexes? I am interested in any performance benefits gained from defining a column in a CCI as BIT instead of BIGINT, ...
3
votes
1answer
331 views

Table partitioning and future values

I have a table with approximately 1.4 bn rows; currently just one partition - but I would like to split the data into multiple partitions. I haven't been using partitions before, so I am not quite ...
6
votes
1answer
275 views

Columnstore Aggregate Pushdown doesn't work for float/real data types

I am having an issue with Aggregate Pushdown for float/real data types. According to the documentation, Aggregate Pushdown is supported for "Any datatype <= 64 bits" or 8 bytes (): ...
1
vote
1answer
129 views

Clustered columnstore indexes - Do I need to create a non clustered index on a column that's used in a join? [closed]

I have two very large tables that need to be joined to produce a data extract. Both tables have clustered column store indexes and are partitioned. DW Sentry advises me in the plan view that it ...
0
votes
1answer
396 views

What is an efficient storage method to store Graph Data from GraphX (Spark)?

I generate graph data using Spark's GraphX library and I need an efficient way to store graph data. I have access to Apache Cassandra and ideally I want to store the graph data in there but the issue ...
1
vote
1answer
2k views

Adding new columns to Table with Clustured Columnstore Index

I have a daily facts table 'FactsTable1' with 240 columns on which I am exploring 'Clustured Columnstore Index'(CCI). I want to know, What happens when I add another ~50 columns to the table? Will ...
1
vote
1answer
316 views

Columnstore index query performance based on number of columns in SELECT statement

I have a table 'FactsTable1' which is horizontally partitioned on basis of date & it also has clustered columnstore index on it. I am running following queries on it. Query 1 : SELECT SUM([...
1
vote
1answer
157 views

Why does my columnstore index not fill the entire rowgroup?

I'm using [vColumnstoreDensity] to monitor the health of my columnstore indexes. I've noticed that I've got one index that looks strange. As far as my understanding goes, the columnstore index will ...
20
votes
3answers
552 views

Why can it take up to 30 seconds to create a simple CCI rowgroup?

I was working on a demo involving CCIs when I noticed that some of my inserts were taking longer than expected. Table definitions to reproduce: DROP TABLE IF EXISTS dbo.STG_1048576; CREATE TABLE dbo....
3
votes
1answer
110 views

SQL Server columnstore metadata (pushdown) filter

AFAIK columnstore indexes store min/max values for segments (1 million rows), so this information can be used to prune not needed data. This feature seems to work with equality predicates, but not ...