使用SQL Server选择非空列

Question

我正在使用SQL Server2012。我有一个包含90列的表。我试图仅选择包含数据的列。搜索后，我使用以下过程：

1-使用一个选择查询获取所有列的计数

2-将结果表转换为临时表

3-创建选择查询

4-执行此查询

这是我使用的查询：

DECLARE @strTablename  varchar(100) = 'dbo.MyTable'
DECLARE @strQuery  varchar(max) = ''
DECLARE @strSecondQuery  varchar(max) = 'SELECT '
DECLARE @strUnPivot as varchar(max) = ' UNPIVOT ([Count] for [Column] IN ('

CREATE TABLE ##tblTemp([Column] varchar(50), [Count]  Int)

SELECT @strQuery = ISNULL(@strQuery,'') + 'Count([' + name + ']) as [' + name + ']  ,' from sys.columns where object_id = object_id(@strTablename) and is_nullable = 1
SELECT @strUnPivot = ISNULL(@strUnPivot,'') + '[' + name + '] ,' from sys.columns where object_id = object_id(@strTablename) and is_nullable = 1


SET @strQuery = 'SELECT [Column],[Count] FROM ( SELECT ' + SUBSTRING(@strQuery,1,LEN(@strQuery) - 1) + ' FROM ' + @strTablename + ') AS p ' + SUBSTRING(@strUnPivot,1,LEN(@strUnPivot) - 1) + ')) AS unpvt '

INSERT INTO ##tblTemp EXEC (@strQuery)

SELECT @strSecondQuery = @strSecondQuery + '[' + [Column] + '],'  from ##tblTemp WHERE [Count] > 0

DROP TABLE ##tblTemp

SET @strSecondQuery = SUBSTRING(@strSecondQuery,1,LEN(@strSecondQuery) - 1) + ' FROM ' + @strTablename

EXEC (@strSecondQuery)

问题是此查询太慢。是否有实现此目标的最佳方法？

笔记：

• 该表在主键Column上只有一个聚集索引，ID并且不包含任何其他索引。
• 表格不可编辑。
• 该表包含非常大的数据。
• 查询大约需要1分钟才能执行

提前致谢。

Answer 1

我不知道这是否更快，但是您可能会使用一个技巧：FOR XML AUTO将省略没有内容的列：

DECLARE @tbl TABLE(col1 INT,col2 INT,col3 INT);
INSERT INTO @tbl VALUES (1,2,NULL),(1,NULL,NULL),(NULL,NULL,NULL);

SELECT * 
FROM @tbl AS tbl
FOR XML AUTO

结果col3是：丢失了…

<tbl col1="1" col2="2" />
<tbl col1="1" />
<tbl />

知道了这一点，您可以找到所有行中都不为NULL的列列表，如下所示：

DECLARE @ColList VARCHAR(MAX)=
STUFF
(
    (
    SELECT DISTINCT ',' + Attr.value('local-name(.)','nvarchar(max)')
    FROM
    (
        SELECT
        (
            SELECT *
            FROM @tbl AS tbl
            FOR XML AUTO,TYPE
        ) AS TheXML
    ) AS t
    CROSS APPLY t.TheXML.nodes('/tbl/@*') AS A(Attr) 
    FOR XML PATH('')
    ),1,1,''
);

SELECT @ColList

的内容@ColList是现在col1,col2。您可以在动态创建的字符串中放置此字符串SELECT。

更新：提示

将替换为排除所有 not-nullableSELECT *创建的列列表将是非常聪明的。并且-如果需要并且可能的话-
类型包含非常大的数据（BLOB）。INFORMATION_SCHEMA.COLUMNS __

UPDATE2：性能

不知道您的 大数据 实际上意味着什么…只需在具有约500.000行（带有SELECT *）的表上进行尝试，然后在不到一分钟的时间内正确返回。希望这足够快…