Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In MS SQL Server 2005, how can I extract statistical information about NULL fraction (or NULL count) for a given column?

Same information is available in Oracle and PostgreSQL, and I heard that MS SQL also has statistics, but I don't know where to find it. Google didn't help much.

I hope I will hear something like

SELECT null_frac FROM pg_stats WHERE tablename='mytab' and attname='mycol';
/* PostgreSQL, stats gathered by ANALYZE */

or

SELECT nullcnt FROM custom_stats_table WHERE tabname='MYTAB' and colname='MYCOL';
/* Oracle, stats gathered by dbms_stats.get_column_stats */

I'm not asking how to compute these values myself - I know how to do it. I need these stats for several thousands of columns, and computing this would take very long on the database that I'm working on. I need approximations, and they should be somewhere in the server - they are needed anyway for cost-based optimizer.

share|improve this question

2 Answers 2

up vote 7 down vote accepted

Nothing so easy. You would need to use DBCC SHOW_STATISTICS and look at the histogram, taking into account that the statistics might be filtered or multi column.

Probably easier to just let SQL Server do it and generate an estimated execution plan for the statement select * from foo where bar is null SQL Server will then use appropriate statistics if they exist or create them if they don't (assuming that the auto create statistics option is enabled).

You can then look at the estimated number of rows going into the SELECT iterator (This shows up as StatementEstRows in the XML for purposes of parsing programatically)

Example code

/*Create temporary stored procedure with query of interest*/
CREATE PROC dbo.#plan_test
AS
SELECT *
FROM master..spt_values
WHERE low IS NULL

GO

/*Generate an execution plan*/
SET FMTONLY ON
EXEC dbo.#plan_test
SET FMTONLY OFF;

/*Retrieve the plan and parse the estimated rows*/
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql) 
SELECT  query_plan.value('(//sql:StmtSimple[1]/@StatementEstRows)[1]', 'float') AS EstimatedNumberOfRows
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
WHERE sys.dm_exec_sql_text.objectid=OBJECT_ID('tempdb.dbo.#plan_test') 
     AND sys.dm_exec_sql_text.dbid=2

DROP PROC dbo.#plan_test
share|improve this answer
    
thank you Martin, I guess that's best what can be done. Do you know if things get simpler with version 2008R2 and 2012? Exposing per-column statistics via a system view is really useful - lots of valuable information can be extracted at no cost. –  filiprem Nov 3 '11 at 22:24
    
@filiprem - I don't know. Looking at this list of system views there isn't anything that leaps out as being promising in this area though. –  Martin Smith Nov 4 '11 at 13:39

For exact results, something like

SELECT IsNull(col) AS is_null, count(col) AS count FROM tbl GROUP BY is_null;

might do the trick (cannot test right now, don't have MS SQL here). Obviously that will be more expensive than asking the statistics module.

share|improve this answer
1  
That isn't valid syntax in SQL Server. You could use SELECT COUNT(*) FROM tbl WHERE col IS NULL or SELECT COUNT(*)-COUNT(col) FROM tbl or SELECT COUNT(CASE WHEN col IS NULL THEN 1 END) FROM tbl –  Martin Smith Nov 3 '11 at 15:32
1  
Sure, I was merely pointing out the idea of constructing a query that would return the number of NULL and non-NULL values. –  Simon Richter Nov 3 '11 at 15:48
1  
The question does say "I'm not asking how to compute these values myself - I know how to do it." and the syntax isn't valid for SQL Server so seems to be of limited value in this context? –  Martin Smith Nov 3 '11 at 15:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.