Sparse (SQL)
In Microsoft SQL, sparse denotes both a special column configuration and the argument by means of which the column is configured accordingly.
Regardless of their data type (such as int, varchar or date ), columns in a database can also contain null values , depending on the configuration . The zero value does not simply result from the lack of a value, but must be saved explicitly as a zero value. In Microsoft SQL, this is done using a so-called zero bitmap. It is assumed in a column that they mostly zero values is included, it can by means of the argument SPARSE NULL to a sparse column (engl. Sparse Column ) to be configured. A sparse column really doesn’t need any memory space to store null values, but it does need more memory if a value other than the null value is stored.
example
A property management creates a table for its tenants for a new property. The property contains 200 apartments, but only 5 parking spaces. In this example, when creating the table, you can say in advance that the parking lot number column will contain a minimum of 195 times the zero value if all parking spaces have been rented out. It is therefore worthwhile to configure the parking space number column as a sparse column in order to save storage space.
CREATE TABLE dbo.MieterLiegenschaftXYZ
(Vorname varchar(50) PRIMARY KEY,
Nachname varchar(50),
Wohnungsnummer int,
Mietzins money,
Parkplatznummer int SPARSE NULL ) ;
restrictions
The use of sparse columns has a number of limitations. Including the following:
- Sparse columns cannot be configured as IDENTITY, ROWGUIDCOL, or NOT NULL at the same time . The latter would make no sense as such.
- Sparse columns cannot be applied to columns of data type text, ntext, image, timestamp, geometry, geography, or user-created data types.
- No cluster index or primary key index can be applied to sparse columns