SQL SERVER – Errors and Limitations of Working with SPARSE Columns in SQL Server

I make a visit to the local user group whenever I get and keeping the community juices up and running for me. I get an opportunity to share some of the learnings and meet tons of new people. In our recent meetup at the SQLBangalore UG, I met a lot of young talent joining the IT field. Many take time to walk up to me and share a moment now and then. It is in this continued journey that inspired me to write this blog.

When I was at the UG meet, I said I write on a lot of topics and showed up this blog. It is rarely that someone fails to stumble onto this space. But one kid walked up to me and asked, what is SPARSE columns? After I explained what it was, he immediately asked me – is there any catch to it? Are there restrictions and limitations? Though there were few that I mentioned, I thought I will write few as part of this blog.

NULL Effect

Sparse columns MUST be nullable. Trying to create a sparse column as not NULL will fail with an error.

CREATE TABLE noNullSparse (Name VARCHAR(20) SPARSE NOT NULL)

Msg 1731, Level 16, State 1, Line 1

Cannot create the sparse column ‘Name’ in the table ‘noNullSparse’ because an option or data type specified is not valid. A sparse column must be nullable and cannot have the ROWGUIDCOL, IDENTITY, or FILESTREAM properties. A sparse column cannot be of the following data types: text, ntext, image, geometry, geography, or user-defined type.

Though the error is not explicit, you can see that the columns marked as SPARSE cannot be NOT NULL fields.

Datatypes to Watch

The above error gives away a lot of information on what are the datatypes that will cause you possible errors if marked as SPARSE.

  • geography
  • geometry
  • image
  • ntext
  • text
  • timestamp
  • user-defineddatatypes(UDT)

The other attributes like IDENTITY, FILESTREAMS and ROWGUIDs are also not allowed.

SPARSE with Computed Column

Lesser known is that SPARSE column’s cannot be used with Computed Columns. But, we can use a SPARSE a column inside a Computed Column. Below is a classic example:

-- Trying to mark a computed column as SPARSE will fail with incorrect syntax error
CREATE TABLE Sales1 (MRP INT, Loss TINYINT, Profit AS (MRP - Loss) SPARSE)
-- However, including an existing SPARSE column in a computed column is allowed
CREATE TABLE Sales2 (MRP INT, Loss TINYINT SPARSE, Profit AS (MRP - Loss))
-- Cleanup
DROP TABLE Sales2

Final Note

The other times we are likely to get an error is to mark a SPARSE column with default values, Primary key, clustered Index, partition key, user defined table type etc. These are some of the restrictions when working with SPARSE columns and will raise an error if used.

Would be great to know if anyone uses SPARSE columns in your designs anywhere? What are the scenario’s you found it useful? Let me know via your comments as it would be a great learning for all.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Error Msg 511 using SQL Server Sparse columns

When I wrote the previous post around SQL SERVER – CREATE TABLE Failed Because Column ‘C1024′ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns, I thought it was a simple concept. Lesser did I know that there can be something wrong. One of my blog reader said they were getting some sort of an error when adding a sparse column to a table. I was surprised to why one should get an error? I asked for further details because this was strange.

The mail stated they are getting:

Msg 511, Level 16, State 1, Line 6
Cannot create a row of size 10019 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.

Just like a strange error gets me going. I started to dig into why are they were getting this error. This lead to an interesting learning for me with sparse columns.

Using sparse columns reduces the maximum size of a row from 8060 bytes to 8018 bytes because of the additional overhead used when storing non-NULL values. However, for most of the normal scenarios this should not be a concern, since by definition, most of the sparse columns in a row will contain NULL values. Therefore, size reduction obtained by not storing any data for NULL values in a sparse column easily compensates for this fixed overhead. This still doesn’t tell us why we are getting the above error. Let us understand the basics.

When you convert a nonsparse column to a sparse column, the sparse column will consume more space for nonnull values. Therefore, when the row size is near 4,009 bytes, the operation can fail. The reason is when we modify an existing table to add our space column, the data pages need to have enough space to make a copy of the original row, make changes and then delete the original row. The error is because of insufficient space on the data pages. Let us do a simple script to get the error:

--Create a column of size 6000 (note this is NCHAR)
CREATE TABLE sparse_tbl (c1 NCHAR(3000))
INSERT INTO sparse_tbl VALUES (REPLICATE ('z',100))
--Modify the column to sparse
ALTER TABLE sparse_tbl ALTER COLUMN c1 ADD SPARSE

msg511 sparse 01 SQL SERVER   Error Msg 511 using SQL Server Sparse columns

A similar error is encountered if you go ahead and remove a sparse column from an existing table with data where the rows have more than 4009 bytes. In case of a new table, this is not encountered in general because there is no data to work with or move.

So the next question was, how can I alter or add or remove sparse columns which have data more than 4009 bytes then? In this case:

  • Create a new table for the operation
  • Insert into new table using the old table
  • Delete the old table and rename the new table

If we follow a process to do the same, there is nothing difficult. I have always felt there are ways to work with data and work around things. I am hope in future versions Microsoft might change this behavior but this is the current workaround. Hope you have never encountered this error before in your code. Let me know your thoughts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – CREATE TABLE Failed Because Column ‘C1024′ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns

Sometimes the best way to learn is by taking some of the capabilities to the extreme. This blog post is one of those explorations which I would like to share. A very long time ago I wrote a blog on this feature which was introduced in SQL Server 2008 called “Sparse Columns”. You can read below blogs to read about the fundamentals of SPARSE columns:

One of the thing which people have misunderstood is the number of sparse column we can have with a table. As per documentation we can go beyond 1024 column limit by using sparse columns. One of my blog reader has done some test and said that the statement is not true. Here is the script which we was using:

CREATE TABLE SomeTable (Id INT SPARSE NULL)
DECLARE @SN INT
SET
@SN = 1
WHILE (@SN <= 1030)
BEGIN
DECLARE
@CLN VARCHAR(100)
SET @CLN = 'C' + CONVERT(VARCHAR(100), @SN)
DECLARE @Query VARCHAR(MAX)
SET @Query = 'ALTER TABLE SomeTable ADD ' + @CLN + ' nvarchar(100) SPARSE'
SET @SN = @SN + 1
EXEC (@query)
END
GO

Above script is having a logic where it would create a table called SomeTable as below

CREATE TABLE SomeTable (Id INT SPARSE NULL)

And then it would keep adding columns using dynamic SQL

ALTER TABLE SomeTable ADD C1 NVARCHAR(100) SPARSE
ALTER TABLE SomeTable ADD C2 NVARCHAR(100) SPARSE
..
..
ALTER TABLE SomeTable ADD C1023 NVARCHAR(100) SPARSE
ALTER TABLE SomeTable ADD C1024 NVARCHAR(100) SPARSE

It fails with below error

Msg 1702, Level 16, State 2, Line 1
CREATE TABLE failed because column ‘C1024′ in table ‘SomeTable’ exceeds the maximum of 1024 columns.

As per https://msdn.microsoft.com/en-us/library/ms143432.aspx (Maximum Capacity Specifications for SQL Server) we should not have received the error because our table is a wide table.

Columns per non-wide table 1024
Columns per wide table 30,000

Here is the answer from Qingsong Yao (Microsoft)

In order to create more than 1024 columns, you have to have a columnset column defined in the table. Once the table has columnset defined, the select * will hide all sparse columns, and only see the column set. Meanwhile, people can still select individual sparse columns in their query.

So, the test done by my blog reader was having little problem. He forgot to define columnset column. Here is the sample of columnset with sparse column.

AllDetails XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

Let me give you a quick example.

SET NOCOUNT ON
GO
USE tempdb
GO
CREATE TABLE MySparseTable (
i INT
,Detail XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
,Height DECIMAL(10, 2) Sparse
,Width DECIMAL(10, 2) Sparse
,Length DECIMAL(10, 2) Sparse
,Color VARCHAR(10) Sparse
,Volume DECIMAL(10, 2) Sparse
)
GO
INSERT INTO MySparseTable (i,Height,Width)
VALUES (1,11.9,12.0);
INSERT INTO MySparseTable (i,Height,Length)
VALUES (2,2.8,9.3);
INSERT INTO MySparseTable (i,Color)
VALUES (3,'Red')
GO
SELECT *
FROM MySparseTable
GO
SELECT i
,Height
,Width
,Color
FROM [MySparseTable]
GO
/* Clean up time
drop table MySparseTable
go
*/

Here is the output.

Sparse 01 SQL SERVER   CREATE TABLE Failed Because Column C1024 in Table myTable Exceeds the Maximum of 1024 Columns

Have you ever use this feature in your environments? What has been your experience in using and what scenarios have you been using these capabilities? Let me know via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 2008 – Introduction to SPARSE Columns – Part 2

Previously I wrote about SQL SERVER – 2008 – Introduction to SPARSE Columns. Let us understand the concept of SPARSE column in more detail. I suggest you read the first part before continuing reading this article.

All SPARSE columns are stored as one XML column in database. Let us see some of the advantage and disadvantage of SPARSE column.

Advantages of SPARSE column are:

  • INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.
  • SPARSE column can take advantage of filtered Indexes, where data are filled in the row.
  • SPARSE column saves lots of database space when there are zero or null values in database.

Disadvantages of SPARSE column are:

  • SPARSE column does not have IDENTITY or ROWGUIDCOL property.
  • SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
  • SPARSE column can not have default value or rule or computed column.
  • Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.
  • Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.
  • A table operation which involves SPARSE column takes performance hit over regular column.

Let me know your thoughts about SPARSE column feature of SQL Server 2008, I am very eager to see your point of view on this new feature.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Introduction to SPARSE Columns

I have been writing recently about how SQL Server 2008 is better in terms of Data Stage and Backup Management. I have received very good replies from many users and have requested to write more about it. Today we will look into another interesting concept of SPARSE column. The reason I like this feature because it is way better in terms of how columns are managed in SQL Server.

SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).

In SQL Server 2008 maximum column allowed per table is 1024. All the SPARSE columns does not count to this limit of 1024. The maximum limit of SPARSE column is 100,000. In summary any table can have maximum of 100,000 SPARSE and 1024 regular columns.

Let us see following example of how SPARSE column saves space in database table.
CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),
FirstCol INT,
SecondCol VARCHAR(100),
ThirdCol SmallDateTime)
GO
CREATE TABLE Sparsed(ID INT IDENTITY(1,1),
FirstCol INT SPARSE,
SecondCol VARCHAR(100) SPARSE,
ThirdCol SmallDateTime SPARSE)
GO
DECLARE @idx INT = 0
WHILE @idx < 50000
BEGIN
INSERT INTO
UnSparsed VALUES (NULL,NULL, NULL)
INSERT INTO Sparsed VALUES (NULL, NULL, NULL)
SET @idx+=1
END
GO
sp_spaceused 'UnSparsed'
GO
sp_spaceused 'Sparsed'
GO
DROP TABLE UnSparsed
GO
DROP TABLE Sparsed
GO

sparse SQL SERVER   2008   Introduction to SPARSE Columns

UPDATE : Read Advantages and Disadvantages of SPARSE Column SQL SERVER – 2008 – Introduction to SPARSE Columns – Part 2

Reference : Pinal Dave (http://blog.SQLAuthority.com)