"Select Top 1000 Rows" Doesn't Show SPARSE Columns?

A colleague pointed out something I'd never noticed about SQL Server Management Studio's (SSMS) "Select Top 1000 Rows" feature: it doesn't display SPARSE columns. This is not a bug, but rather by design: SQL Server tables can have up to 30,000 SPARSE columns: imagine the issues with viewing thousands of columns at a time!

The annoying thing, however, is that no matter how few columns a table has, not even a single SPARSE column will be displayed. To workaround this limitation, I've written a script that creates views for all tables with SPARSE columns. Since views don't suffer from this limitation in SSMS, you can use "Select Top 1000 Rows" on them to effectively see all the columns on the table.

I personally run it as a "startup script", just to semi-automate the maintenance of re-creating the views when tables change over time, but it could just a easily be set to run as a SQL Agent Job, or manually if desired.

-------------------------------------------------------------------------------
-- Declare variables.                                                          
-------------------------------------------------------------------------------

DECLARE @Prefix     NVARCHAR(50)  = '_'
DECLARE @Suffix     NVARCHAR(50)  = '_SPARSE'
DECLARE @SchemaName sysname       = ''
DECLARE @TableName  sysname       = ''
DECLARE @ViewName   sysname       = ''
DECLARE @Sql        NVARCHAR(MAX) = ''


-------------------------------------------------------------------------------
-- Since tables can be added, modified, and deleted, start with a clean slate. 
-------------------------------------------------------------------------------

DECLARE cur CURSOR FOR
    SELECT SCHEMA_NAME(schema_id)   AS 'SchemaName'
         , name                     AS 'TableName'
     FROM sys.views
    WHERE is_ms_shipped = 0 
      AND name LIKE '%' + @Suffix ESCAPE '$'

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ViewName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)

    IF OBJECT_ID(@SchemaName, @ViewName) IS NULL
    BEGIN        
        SET @Sql = 'DROP VIEW ' + @ViewName
        EXEC sys.sp_executesql @Sql 
    END

    FETCH NEXT FROM cur INTO @SchemaName, @TableName
END

CLOSE cur
DEALLOCATE cur


-------------------------------------------------------------------------------
-- Iterate through all tables and create view for those with SPARSE columns.   
-------------------------------------------------------------------------------

DECLARE cur CURSOR FOR
    SELECT DISTINCT SCHEMA_NAME(t.schema_id)   AS 'SchemaName'
                  , t.name                     AS 'TableName'
     FROM sys.tables    t
     JOIN sys.columns   c
       ON t.object_id = c.
       object_id
    WHERE t.type_desc     = 'USER_TABLE'
      AND t.is_ms_shipped = 0 
      AND c.is_sparse     = 1

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ViewName  = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@Prefix + @TableName + @Suffix)
    SET @TableName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
    
    IF OBJECT_ID(@SchemaName, @ViewName) IS NULL
    BEGIN        
        SET @Sql = 'CREATE VIEW ' + @ViewName + ' AS  SELECT * FROM ' + @TableName
        EXEC sys.sp_executesql @Sql 
    END

    FETCH NEXT FROM cur INTO @SchemaName, @TableName
END

CLOSE cur
DEALLOCATE cur

Once the above code has been run, any tables you have with SPARSE columns will have views created for them. (If you don't happen to have any, here's some code to create a small test table.)

SET NOCOUNT ON

-------------------------------------------------------------------------------
-- Set up test table.                                                          
-------------------------------------------------------------------------------

IF OBJECT_ID('MyTable') IS NOT NULL
    DROP TABLE MyTable

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE MyTable
(
    TableID            INT                  NOT NULL,
    MySparseColumn     NVARCHAR (50) SPARSE     NULL,
    MyNonSparseColumn  NVARCHAR (50)        NOT NULL
)

GO

INSERT MyTable (TableID, MySparseColumn, MyNonSparseColumn)
VALUES (1, NULL, 'ABC')

GO 10

Once you've got the views created, simply use the "Select Top 1000 Rows" feature as usual, and you'll be able to see all the columns in your tables...

... even the SPARSE ones.