Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

15 questions
11
votes
1 answer

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 columns and one with no sparse columns. --Non Sparse…
Randi Vertongen
  • 16,593
  • 4
  • 36
  • 64
9
votes
1 answer

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 those "losely typed" columns depends on the type…
GSerg
  • 1,353
  • 1
  • 17
  • 29
7
votes
3 answers

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?
jrara
  • 5,393
  • 20
  • 58
  • 65
7
votes
3 answers

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 NULL , Value1 VARCHAR(100) SPARSE NULL ,…
mhep
  • 750
  • 7
  • 22
3
votes
2 answers

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 maximum of 4000 characters is reasonable for this…
richardtallent
  • 352
  • 2
  • 9
3
votes
1 answer

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 bytes char(6): 6 bytes sparse float: 12 bytes x…
tim654321
  • 133
  • 3
2
votes
1 answer

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 (so only few articles record are read each…
Skary
  • 368
  • 2
  • 11
2
votes
0 answers

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 there some metadata I can modify to quickly set…
sciron
  • 21
  • 1
2
votes
0 answers

Storing a large, sparse matrix in Apache Hbase?

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 rows. The values are integer values The main…
gacharya
  • 21
  • 1
2
votes
1 answer

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] NOT NULL, [skillDetails] [varchar](500) NULL, …
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
1
vote
1 answer

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 particular set of arguments. (Yes, it's a functional…
richardtallent
  • 352
  • 2
  • 9
1
vote
1 answer

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 take advantage of the SPARSE property? Would it be…
Ravi Chawla
  • 11
  • 2
1
vote
2 answers

Given that they require schema modification locks, why are sparse columns considered an alternative to EAV?

Pro SQL Server Relational Database Design and Implementation: Best Practices for Scalability and Performance is a very reliable source of good database design ideas. As an alternative to Entity-Attribute-Value it suggests using dynamic SQL to allow…
J. Mini
  • 1,161
  • 8
  • 32
1
vote
1 answer

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 whatever reason). Scenario 2 is when the data is…
Raymond Hoang
0
votes
1 answer

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 page This was after making good use of sparse column…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320