November 15, 2011 at 10:54 am
I'm experimenting with the following code to dynamically create a temp table with a variable number of columns:
IF (SELECT OBJECT_ID('tempdb..#tmpVintage')) IS NOT NULL
DROP TABLE #tmpVintage
CREATE TABLE #tmpVintage(VntSeqINTEGERNULL,
VntVintageVARCHAR(4)NOT NULL,
VntTranDateDATETIMENOT NULL,
VntIssueRcptCHAR(1)NOT NULL,
VntLotKeyINTEGERNOT NULL,
VntPodKeyINTEGERNOT NULL,
VntPodCountTINYINTNOT NULL,
VntTagKeyINTEGERNOT NULL,
VntTranQtyDECIMAL(18,5)NOT NULL,
VntTranUOMVARCHAR(2)NOT NULL,
VntTranRunQtyDECIMAL(18,5)NULL,
VntQtyDECIMAL(18,5)NULL,
VntRunDECIMAL(18,5)NULL,
VntPctDECIMAL(18,5)NULL,
VntSparseCols XML COLUMN_SET FOR ALL_SPARSE_COLUMNS)
-- ADD THE COLUMNS FOR EACH INPUT LOT - TO BE USED FOR KEEPING RUNNING TOTALS BY INPUT LOT
SELECT @sql =
STUFF((SELECT DISTINCT
'ALTER TABLE #tmpVintage ADD [VntRun' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL, [VntPct' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL '
FROM #TraceInputs
WHEREtriInputLotKey <> 0
ORDER BY 'ALTER TABLE #tmpVintage ADD [VntRun' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL, [VntPct' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL '
FOR XML PATH('')
), 1, 0, '')
EXECUTE (@SQL)
When I execute the code with the SPARSE column type defined, I get the following error:
Cannot create a row of size 9563 which is greater than the allowable maximum row size of 8060.
If I remove the SPARSE definition, the code runs fine, but in some situations the distinct triInputLotKey count returns too many columns and the code fails, so I want to try using SPARSE. Am I missing something in my code?
Perhaps I need to provide more information. The SELECT statement returns 364 rows, so the code is trying to add 728 DECIMAL(18,5) columns to the table. Does anyone know why I am able to create the table without errors if I exclude the SPARSE column type, yet cannot create the same table with SPARSE columns?
November 17, 2011 at 11:04 am
Anyone?
Perhaps I need to provide more information. The SELECT statement returns 364 rows, so the code is trying to add 728 DECIMAL(18,5) columns to the table. Does anyone know why I am able to create the table without errors if I exclude the SPARSE column type, yet cannot create the same table with SPARSE columns?
November 17, 2011 at 11:09 am
Restrictions for Using Sparse Columns
________________________________________
Sparse columns can be of any SQL Server data type and behave like any other column with the following restrictions:
•A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.
•A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.
•A sparse column cannot have a default value.
•A sparse column cannot be bound to a rule.
•Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE.
•A sparse column cannot be part of a clustered index or a unique primary key index. However, both persisted and nonpersisted computed columns that are defined on sparse columns can be part of a clustered key.
•A sparse column cannot be used as a partition key of a clustered index or heap. However, a sparse column can be used as the partition key of a nonclustered index.
•A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.
•Sparse columns are incompatible with data compression. Therefore sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.
•Changing a column from sparse to nonsparse or nonsparse to sparse requires changing the storage format of the column. The SQL Server Database Engine uses the following procedure to accomplish this change:
1.Adds a new column to the table in the new storage size and format.
2.For each row in the table, updates and copies the value stored in the old column to the new column.
3.Removes the old column from the table schema.
4.Rebuilds the table to reclaim space used by the old column.
November 17, 2011 at 11:14 am
Thanks for the reply, Dev. I'm not sure which rule I'm breaking. Can you identify it?
November 17, 2011 at 11:22 am
gcresse (11/17/2011)
Thanks for the reply, Dev. I'm not sure which rule I'm breaking. Can you identify it?
First Rule... A sparse column must be nullable.
November 17, 2011 at 11:26 am
But doesn't the definition DECIMAL(18,5) SPARSE NULL make the column nullable?
November 17, 2011 at 11:36 am
Try to match up your DDL with this.
Example:
CREATE TABLE TableWithSparseColumns (
DocID INT IDENTITY, DocName VARCHAR (100), DocType INT,
c0004 INT SPARSE NULL, c0005 INT SPARSE NULL, c0006 INT SPARSE NULL, c0007 INT SPARSE NULL,
...
c0996 INT SPARSE NULL, c0997 INT SPARSE NULL, c0998 INT SPARSE NULL, c0999 INT SPARSE NULL,
c1000 INT SPARSE NULL);
GO
Basically I don't see any SPARSE keyword in your DDL (regular columns / column_set_definition).
November 17, 2011 at 11:49 am
I'm adding the SPARSE columns with this line of code:
'ALTER TABLE #tmpVintage ADD [VntRun' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL, [VntPct' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL '
I'm doing more digging and the error doesn't occur when the table is created and the SPARSE columns added, but rather when I'm updating the values in the SPARSE columns. My first thought was that my assumption that there would never be a value > 0 in *most* of the SPARSE columns on the same row must be wrong, but then why does the update work fine if I remove the SPARSE option from the columns?
November 18, 2011 at 1:11 am
gcresse (11/17/2011)
I'm adding the SPARSE columns with this line of code:
'ALTER TABLE #tmpVintage ADD [VntRun' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL, [VntPct' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL 'I'm doing more digging and the error doesn't occur when the table is created and the SPARSE columns added, but rather when I'm updating the values in the SPARSE columns. My first thought was that my assumption that there would never be a value > 0 in *most* of the SPARSE columns on the same row must be wrong, but then why does the update work fine if I remove the SPARSE option from the columns?
Why 2-Phase Table Creation (Create + Alter)? It has it's own issues with Sparse Columns.
When you change a nonsparse column to a sparse column, the sparse column will consume more space for nonnull values. When a row is close to the maximum row size limit, the operation can fail.
Also it can fail when the size of the data in the row exceeds the maximum allowable row size. This size includes the size of the data stored in the old column and the updated data stored in the new column. This limit is 8060 bytes for tables that do not contain any sparse columns or 8018 bytes for tables that contain sparse columns. This error can occur even if all eligible columns have been pushed off-row.
For more information, see Row-Overflow Data Exceeding 8 KB.
http://msdn.microsoft.com/en-us/library/ms186981.aspx
Estimated Space Savings by Data Type
--------------------------------------------------------------------------------
Sparse columns require more storage space for nonnull values than the space required for identical data that is not marked SPARSE. The following tables show the space usage for each data type. The NULL percentage column indicates what percent of the data must be NULL for a net space savings of 40 percent.
Data typeNonsparse bytesSparse bytes
decimal/numeric(1,s)59
decimal/numeric(38,s)1721
November 18, 2011 at 10:03 am
Okay, so because the sparse column actually takes up more space when there is data in it than an equivalent non-sparse column, that would explain why I don't get the error when I don't use sparse columns. Thanks for your input.
November 19, 2011 at 6:24 am
Most Welcome 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply