Questions tagged [sparse-column]

Sparse columns have optimized storage for null values at the cost of more overhead to retrieve non-null values. A table with sparse columns may have an extra column-set column containing an untyped XML representation of all the sparse column data in each row.

Filter by
Sorted by
Tagged with
10
votes
1answer
166 views

Sparse columns, cpu time & filtered indexes

Sparsing When doing some tests on sparse columns, as you do, there was a performance setback that I would like to know the direct cause of. DDL I created two identical tables, one with 4 sparse ...
0
votes
1answer
52 views

how to find tables with either sparse or a column set column?

from the error message below: A compressed index is not supported on table that contains sparse columns or a column set column as I was rebuilding an index and changing the data_compression to ...
2
votes
0answers
32 views

Quickest way to apply SPARSE on huge existing tables

I have a bunch (> 40) of columns in a huge table (> 27 m records) which I am attempting to set as SPARSE. Each column takes up to 40 minutes to be altered. Is there a way to do this more quickly? Is ...
1
vote
0answers
43 views

Storing a large, sparse matrix in Apache Hbase? [closed]

I'm currently testing out Apache Hbase for our Matrix analytics service. I'm using a managed cluster running on AWS EMR. The matrices are sparse, and we have 50,000 columns, and up to 10 million ...
2
votes
1answer
425 views

sparse column or DATA_COMPRESSION?

in one of my databases I have the following table: CREATE TABLE [app].[applicantSkill]( [ApplicantSkillID] [int] IDENTITY(1,1) NOT NULL, [applicantID] [int] NOT NULL, [skillID] [tinyint] ...
6
votes
3answers
1k views

How to query against exact values in XML column set

I have a table that contains 80+ sparse columns along with a column set column, this is a brief example: DROP TABLE IF EXISTS #ColumnSet GO CREATE TABLE #ColumnSet ( Id INT NOT ...
3
votes
2answers
4k views

Should I used varchar(max) or varchar(4000) SPARSE?

I have a "comment"-type column that is rarely used -- around 6% non-null in a population of 3 million records. The average length (when used) is 6 characters, and the max so far is around 3KB. A ...
1
vote
1answer
84 views

Can I use SPARSE somehow on a non-nullable bit column with mostly false values?

I have a table that stores the results of queries that are run at least once a day. There's a bit column that represents whether the row is from the most recent run of a particular query with a ...
1
vote
1answer
106 views

SPARSE Column in SQL Server

I have just learned about SPARSE columns in SQL Server. I have never used them; I've just read about it on the Internet. Can I ALTER an existing Nullable column in large transactional tables to ...
9
votes
1answer
1k views

Adding SPARSE made table much bigger

I have a generic log table, about 5m rows. There's a "strongly typed" field that stores event type, and a bunch of "losely typed" columns that contain data relevant to the event. That is, meaning of ...
5
votes
2answers
255 views

Is there some drawback to define SPARSE columns?

I have a table which has a lot of NULL values in columns. But some columns don't contain NULLs at all (although nullable). Is there some drawback to declare all of these columns as SPARSE?