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 (https://blog.sqlauthority.com)
