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.
14
questions
3
votes
1
answer
112
views
Row greater than max size (8060) but I can't see why
I have a table that has 483 columns.
A date not null, a char(6) not null, and then 481 x float sparse null.
By my maths, even if every sparse float is non-null, the row should still only be:
date: 3 ...
- 133
1
vote
1
answer
43
views
Database Schema design - Sparse matrix; when does it make sense?
I was exploring the different reasoning for schema designs. I came across the following scenario. Scenario 1 has a data schema that has a bunch of columns, but the data is sparse mostly nulls (for ...
Raymond Hoang
2
votes
1
answer
140
views
SQL Server JOIN vs sparse table
I am in the need to query a relation between an entity called article (it's a product sold in a shop) and it's characterstics (a set of attributes). The result will always presented in a paged manner (...
- 358
11
votes
1
answer
535
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 ...
- 16.4k
0
votes
1
answer
450
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 ...
- 15.8k
2
votes
0
answers
130
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 ...
- 21
2
votes
0
answers
163
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 ...
- 21
2
votes
1
answer
1k
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] ...
- 15.8k
6
votes
3
answers
2k
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 ...
- 720
3
votes
2
answers
6k
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 ...
- 352
1
vote
1
answer
99
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 ...
- 352
1
vote
1
answer
201
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 ...
- 11
9
votes
1
answer
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 ...
- 1,342
6
votes
3
answers
456
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?
- 5,333