Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [columnstore]

For questions about column-oriented storage and access mechanisms in databases. Although the term "columnstore" itself is used by Microsoft and MariaDB for their respective implementations, you should use this tag for questions about other vendors' technologies (e.g. IBM Db2 column-organized tables) and for questions about the technology in general, not specific to a particular vendor.

Filter by
Sorted by
Tagged with
-1 votes
3 answers
65 views

How are columnar databases faster for querying?

Why do so many people prefer columnar databases? To my understanding, people prefer columnar databases because they are faster for querying due to the fact that the cpu would be able to cache the next ...
KoalaMaybe's user avatar
0 votes
0 answers
30 views

Calculating min/max values of a columnstore table with sys.column_store_segments

I need to fetch the max and min values of an arbitrary column on a table. The table has a clustered columnstore-index, billions of rows, and hundreds of columns. I'd like to leverage that fact that ...
Derek's user avatar
  • 15
0 votes
1 answer
91 views

Moving data to new partitioned table

I have one large table in SQL Server (around 5,5 billion rows) and I intend to create new partitioned table for that data, probably by year, with filegroup for each year for better management. This is ...
Ludijak's user avatar
1 vote
0 answers
125 views

Convert table to partition and columnstore index on different key

I have a table with the following characteristics: 200 million rows A clustered primary key that is an auto-incrementing BIGINT column An NVARCHAR(MAX) column consisting of raw JSON data, which we ...
Greg's user avatar
  • 574
1 vote
0 answers
55 views

Huge difference in terms of duration between delete of columnstore and rowstore big table

We have sap table PRCD_ELEMENTS which is: Partitioned (on KNUMV column) Have PK aligned with partition schema Have non clustered index on rowid column which is aligned with partition schema Table has ...
adam.g's user avatar
  • 355
-2 votes
1 answer
106 views

MariaDB InnoDb & Columnstore

I have a DB mariadb with innodb tables with one, two million records. I checked online and saw that Columnstore is faster. I created a Columnstore table in my DB and put all the contents of the Innodb ...
kali's user avatar
  • 7
0 votes
1 answer
137 views

Reducing Fragmentation on a clustered columstore index

I have a database with a clustered columnstore index on it. The table has 1,685,123,846 records in it. I use the ola hallengre script monthly to rebuild the index, but it takes so long to run before ...
Ross Dargan's user avatar
0 votes
1 answer
240 views

Best solution for storing metadata and data from documents?

First of all do I want to mention that I'm fairly new to databases and big data. Background: I'm working for a large company who wished to unify all their documentation. At it's core of my project am ...
nklsla's user avatar
  • 101
0 votes
2 answers
122 views

Why Are Many Open Delta Rowgroups Created On A SQL Server Columnstore Table When Simultaneously Trickle Feeding Rows And Running Select Queries?

SQL Server 2022 Express (16.0.1000.6) on Windows Server 2016 Standard. I have a table with two indexes. An ordered columnstore index (ordered by a smalldatetime column, and then an int column). A ...
Roj's user avatar
  • 109
-1 votes
1 answer
66 views

Nonclustered columstore Index memory allocation

We are planning to use a non-clustered columnstore index on 30 nvarchar(100) columns. The table can have rows ranging from a few hundreds to millions. These columns are selected during query execution ...
padma's user avatar
  • 1
1 vote
1 answer
105 views

Should I use a single column with many dimensions with columnar data?

I'm using Apache Iceberg which performs queries on Parquet files. The data saves a lot of types of events and their respective numeric values. I'm hesitating whether it's more efficient to have one ...
hitchhiker's user avatar
8 votes
1 answer
480 views

Why are statistics slower to build on clustered columnstore?

Building statistics on a clustered columnstore table always seems to read the whole table, even if I ask for a small sample. Why is this?
Paul White's user avatar
  • 85.9k
1 vote
1 answer
374 views

Deadlock with Reorganize Index

I was running alter index reorganize on some columnstore indexes in SQL Server 2016 and got a deadlock message. Will reorganize always be the victim? Because I do not want to cause other jobs to fail ...
xhr489's user avatar
  • 807
0 votes
1 answer
35 views

Synapse column store datetime encoding

I have a table in an Azure Synapse database. This table has a clustered columnstore index. It has a datetime column. The minimum value in this column is 2001-03-27 00:00:00 and the maximum is 2022-12-...
Michael Green's user avatar
4 votes
3 answers
769 views

Trim_reason = DICTIONARY_SIZE on Clustered ColumnStore index

On a partitioned table with 17 billion rows, I'm getting a very high number of row_groups. I've heard that I should try to get 1.048.576 rows in each rowgroup. How do I achieve that? The trim_reason ...
Henrik Staun Poulsen's user avatar
0 votes
1 answer
19 views

How to make the first match in a group a different group

I need to group by a certain column and to make the first occurrence into another group. select * from table; id currency amount 1 USD 1 2 USD 2 3 EUR 3 4 IL 4 5 EUR 5 6 USD 6 So something ...
Nir's user avatar
  • 533
26 votes
1 answer
2k views

Why MERGE doesn't insert more than 277 records into a table which is configured with temporal table and a non-clustered index on history table

I found again an issue with SQL Server and MERGE statement and need some confirmation. I can reproduce my issue constantly on a Azure Database (but not on a on premise SQL Server 2017/2019). Please ...
Daniel C.'s user avatar
  • 363
0 votes
0 answers
82 views

Setting up an HTAP Environment

I've had success in the past with setting up a CIX/PK on an identity column and then setting up supporting NCIX and NCCsIX on tables to help support an HTAP environment. I'm curious, going forward ...
Miguel Sanchez's user avatar
7 votes
1 answer
1k views

Why is statistics update slower on a clustered columnstore than on a rowstore?

I've moved several large tables (each with >10^9 rows and a couple of dozens columns) from clustered rowstore to clustered columnstore indexes on a SQL Server 2014 instance and noticed that ...
Andrej Kuklin's user avatar
4 votes
1 answer
423 views

Columnstore index predicate is not used in index scan

We are using a clustered columnstore index table with ~50M records and experiencing big performance degradation when running on GCP cloud sql vs. running locally using the same db schema and data (...
Eyal Shulman's user avatar
4 votes
1 answer
490 views

Why significantly worse Column Store performance on Read-only AG secondary DB?

We have a pair of SQL 2019 Enterprise instances hosted in identically spec'd and configured VMs in Azure. They for a simple primary/secondary availability group array, containing a small number of ...
Mark's user avatar
  • 899
0 votes
0 answers
75 views

Adding new column to clustered column store indexed table

I designing clustered column store indexed fact tables. Later on many new columns might be added to these currently ~90 columns tables. How adding new nullable column is affected by the size (number ...
Avi's user avatar
  • 265
0 votes
1 answer
113 views

How to change default compression mode in mariaDB Columnstore?

In mariaDB there is a way to disable columnstore compression in session level (and set the disable flag in each session). Is there any way to change the default compression mode?
ssavva05's user avatar
0 votes
0 answers
401 views

Is a columnstore index appropriate when all columns are (mostly) unique?

I have a longish table ( 60M rows ) where there are only 7 columns. One is a unique ID, two are datetimes, and two are notes and descriptions. There the notes and descriptions are very regular, except ...
Clay's user avatar
  • 101
3 votes
2 answers
640 views

Columnstore: terrible execution plan - filter instead of seek

See the execution plan on https://www.brentozar.com/pastetheplan/?id=SyLQIPDtF (SQL 2016 Enterprise) I have a datawarehouse table peak_reporting_data which tracks the activity per day and hour and ...
Thomas Franz's user avatar
0 votes
0 answers
617 views

UPDATE on a clustered COLUMNSTORE table with 150 columns and 30 mln records just stuck

I have a table TABLE_A which is having 150 columns (with 5 lob type) and 30 millions. User were facing read performance issue, so I created CLUSTERED COLUMNSTORE on the table. After I converted the ...
Rajesh Ranjan's user avatar
0 votes
1 answer
393 views

Clustered Column Store Index - does it have max row size limitation?

Updatable Clustered Column Store Index (SQL Server 2016+) - does it have max row size (8060) limitation, or you can put any row size you want in there ? In other words, is it possible to get below ...
Aleksey Vitsko's user avatar
0 votes
1 answer
117 views

Error loading data in MariaDB Columnstore

I have a MariaDB Columnstore database running on the cloud in Linux. I'm trying to export a CSV file from my Windows machine to the database. I created the file with DBeaver, setting the new line as \...
ps0604's user avatar
  • 51
0 votes
1 answer
230 views

Optimizer Not using Non Clustered Column Store Index for Update / Delete Statements

My Table tbl_FieldeData_ColStore Data Example: /NonClustred Column Store Index on Table/ CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON tbl_FieldeData_ColStore ( PDID,FieldID ) /Below Select was ...
gopikrishna's user avatar
0 votes
2 answers
211 views

Columnar DBMS and issue understanding multiple where constraints

I read a lot about columnar DMBS and perfectly understand that they are great for researches on a column. This question has a partial answer. Now, say I have a DMBS with 3 columns A, B and C. Doing a ...
Kroma's user avatar
  • 101
0 votes
0 answers
98 views

"Error opening column file" in MariaDB Columnstore data cpimport

I'm trying to import a large file, around 2GB, into MariaDB Columnstore. Other files that are smaller worked fine, so I suspect it's a size issue. I create the table with engine Columnstore with no ...
ps0604's user avatar
  • 51
0 votes
0 answers
44 views

Database Page Compression and ColumnStore Archive - is data compressed when in buffer pool and when sent to client? [duplicate]

One of benefits of Page Compression or ColumnStore Archive is reduced storage space requirements Here I have two questions: When compressed data is read from disk to buffer pool, in the buffer pool ...
Aleksey Vitsko's user avatar
0 votes
0 answers
321 views

Clustered Columnstore Index alignment on multiple columns

I have a fact table which is a clustered columnstore index. It is also: -partitioned by a single data date column. -has a nonclustered, aligned, composite unique index (PK) on 6 INT columns, plus the ...
Avi's user avatar
  • 265
0 votes
2 answers
421 views

Storing Live Timeseries Data in SQL Server Azure Database

I have an SQL Server Azure Database and am trying to store time-series data that I get from multiple sensors. Data Source:- 5-minute data is obtained via an API. Current table structure: - Timestamp |...
Spaceguy152's user avatar
1 vote
1 answer
842 views

MariaDB Column Store Suitability for Data Warehouse

We want to build a data warehouse to distribute valid information across the organization (reporting) and get some insights into our data (analytics). After some research we found MariaDB using Column ...
CFM's user avatar
  • 13
1 vote
1 answer
789 views

What is the most performant way to group by and join high cardinality columns in column based table?

I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The ...
alex's user avatar
  • 111
0 votes
1 answer
95 views

Optimize BETWEEN Query in SQL Server Azure

I have a table that stores time-series (5-minute) data and am trying to run a query that has a BETWEEN clause included in it. Below is the table structure:- Timestamp | ComponenentID | Parameter1 | ...
Spaceguy152's user avatar
0 votes
1 answer
271 views

Is columnstore a good option for UPDATES with WHERE clauses?

I am developing a database project on SQL Server and I am thinking whether using columnstore index is a good idea. The project consists of a table (A) that will hold a large number of rows, with many ...
Alexandre's user avatar
  • 103
3 votes
2 answers
892 views

What is the correct and performant pattern for loading (and querying) a large clustered columnstore table?

I am doing some test with clustered columnstore tables - both partitioned and not. In my research, I've seen flavors of this pattern: -- step 1: make new table SELECT TOP 0 * INTO [Fact].[Order_CCI] ...
Chris Harrington's user avatar
5 votes
2 answers
641 views

SQL Server 2019 column store indexes - maintenance

I have a clustered columned stored index on table used for logging - only inserts (but not bulk inserts). The current table stats are: 3541 millions rows 6.6 GB reserved space I see this morning the ...
gotqn's user avatar
  • 4,026
1 vote
1 answer
737 views

Slow inserts in mariadb columnstore

I have just started on researching the feasibility of using MariaDB's columnstore for OLAP, and I find inserts are very slow. This is MariaDB 10.5 on a debian 10 system, just an elderly desktop with ...
j4nd3r53n's user avatar
  • 231
3 votes
2 answers
1k views

How does a column-oriented DBMS filter on multiple columns?

I'm learning how column-oriented DBMS / "columnars" work for OLAP situations. Let's say we have a log of millions of transactions with 3 columns: timestamp, shop, product, and we want to ...
Basj's user avatar
  • 171
5 votes
2 answers
1k views

Clustered columnstore index on small tables

Clustered column store indexed tables in general are useful for large tables. Ideally with million of rows. And also useful with queries, which selects only the subset of available columns in such ...
Avi's user avatar
  • 265
1 vote
1 answer
156 views

Could database with compat level 100 support columnstore index?

I have an instance which runs on SQL Server 2017 Developer Edition instance. I restored database ContosoRetailDW which had compatibility level 100. After restore operation, I run the following query: ...
Rauf Asadov's user avatar
  • 1,273
0 votes
1 answer
90 views

Does binary logging apply to ColumnStore?

When binary logging is enabled, does MariaDB's binary logging also record ColumnStore events, and not just InnoDB events? For example, does it log an UPDATE made to a ColumnStore table row? I've ...
rdguam's user avatar
  • 103
0 votes
2 answers
350 views

Why am I getting error 35353 when enabling CDC on a table with a CCI?

I'm trying to enable Change Data Capture on a table with a clustered columnstore index, a unique non-clustered index, and a non-unique non-clustered index in SQL Server Standard 2019. The command ...
Avyncentia's user avatar
1 vote
1 answer
419 views

Eliminate filter operator before columnstore index scan operator

I have a large fact table with millions of rows called MyLargeFactTable, and its a clustered column store table. There is a composite primary key constraint on it there as well (customer_id,...
Avi's user avatar
  • 265
3 votes
1 answer
522 views

Row vs Columnar DB for queries with multiple OR statements

I understand columnar databases are great for speed queries where you don't use many fields to lookup against, but what if you only used OR statements? For instance, I want all the records that have (...
a11hard's user avatar
  • 133
10 votes
2 answers
3k views

Does it make sense to add indexes to a clustered columnstore index table?

Question is similar to this one, but the answer doesn't seem to answer this question. My understanding of a clustered columnstore table (and please correct me if I am wrong) is that each column is ...
Jeremy Holovacs's user avatar
1 vote
1 answer
700 views

Azure tempdb filling up while trying to convert a large table to columnstore... how to mitigate?

I have a large reporting table (approx 6B rows) taking up roughly 400GB of storage on an azure sql db. I've been trying to convert it to a columnstore table using the following commands: insert Logs(...
Jeremy Holovacs's user avatar