需要优化SQL Server查询

问题描述:

我有一个表“N”列 &我必须写一个简单的查询是这样的:需要优化SQL Server查询

SELECT col_1,col_2,col_3,...col_n FROM SingleTableOnly 
WHERE col_1 IS NOT NULL 
AND col_2 IS NOT NULL 
AND col_3 IS NOT NULL 
... -- for each column i need to put this condition again & again 
AND col_n IS NOT NULL 

请建议我为同一更好的逻辑。

还能有这样的:

SELECT ALL COLUMNS FROM SingleTableOnly 
WHERE ALL COLUMNS IS NOT NULL 
-- i know this isnt right sql query, but trying to figure something where i can write less. 

这是特定于MS SQL Server 2005的唯一。

+2

没有其他你正在描述的where子句的方式。 – 2011-01-12 07:01:20

+3

您可以通过编写视图并从中进行选择来绕过这个问题。 – 2011-01-12 07:32:15

如果有很多的列或列的数量是未知的,你可以使用动态SQL来完成这项工作,虽然可能不是最好的办法:

declare @sql varchar(4000) 
set @sql = ' where 1=1 ' 
declare my_cursor cursor 
for 

SELECT 
    COLUMN_NAME 
FROM 
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_NAME = 'tablename' 
ORDER BY 
    ORDINAL_POSITION ASC; 

open my_cursor 
declare @colname varchar(20) 
fetch next from my_cursor into @colname 
while(@@FETCH_STATUS <> -1) 
begin 
set @sql = @sql + ' and ' + @colname + ' is not null ' 

fetch next from my_cursor into @colname 
end 

close my_cursor 
deallocate my_cursor 

--select 'select * from tablename ' + @sql 
EXEC('select * from tablename ' + @sql) 

DECLARE @TableName VARCHAR(100) 
SET @TableName = 'Your_Table_Name' 

DECLARE @columns VARCHAR(MAX) 

SELECT @columns = STUFF((SELECT ' and '+COLUMN_NAME +' IS NOT NULL ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName ORDER BY ORDINAL_POSITION ASC FOR XML PATH('')),1,4,'') 

EXEC('SELECT * FROM ' + @TableName ' WHERE ' + @sql) 
+0

它可以在没有游标的情况下完成!很高兴知道... – 2011-01-12 07:33:40

唯一的办法我可以看到你可以写少一点的代码(与优化无关)不是检查非空列。
如果它是一个谜或测试的问题,你可能会很可能使用的事实,任何+空= NULL(请,不这样做在实际应用中),所以看起来

WHERE ((int_field1+int_field2+ intfield_n) IS NOT NULL) 
AND ((string_field1+string_field2+...) IS NOT NULL) 
AND ((datetime_field1+datetime_field2+...) IS NOT NULL)