Sql-server – Get NULL statistics for a column in MS SQL Server

sql serversql-server-2005statistics

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 */

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.

Best Answer

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

Related Solutions

Sql-server – the most efficient way to get the minimum of multiple columns on SQL Server 2005

I tested the performance of all 3 methods, and here's what I found:

1 record: No noticeable difference
10 records: No noticeable difference
1,000 records: No noticeable difference
10,000 records: UNION subquery was a little slower. The CASE WHEN query is a little faster than the UNPIVOT one.
100,000 records: UNION subquery is significantly slower, but UNPIVOT query becomes a little faster than the CASE WHEN query
500,000 records: UNION subquery still significantly slower, but UNPIVOT becomes much faster than the CASE WHEN query

So the end results seems to be

With smaller record sets there doesn't seem to be enough of a difference to matter. Use whatever is easiest to read and maintain.
Once you start getting into larger record sets, the UNION ALL subquery begins to perform poorly compared to the other two methods.
The CASE statement performs the best up until a certain point (in my case, around 100k rows), and which point the UNPIVOT query becomes the best-performing query

The actual number at which one query becomes better than another will probably change as a result of your hardware, database schema, data, and current server load, so be sure to test with your own system if you're concerned about performance.

I also ran some tests using Mikael's answer; however, it was slower than all 3 of the other methods tried here for most recordset sizes. The only exception was it did better than a the UNION ALL query for very large recordset sizes. I like the fact it shows the column name in addition to the smallest value though.

I'm not a dba, so I may not have optimized my tests and missed something. I was testing with the actual live data, so that may have affected the results. I tried to account for that by running each query a few different times, but you never know. I would definitely be interested if someone wrote up a clean test of this and shared their results.

Sql-server – Queries for unique names from a site table using multiple criteria

1 Select unique site names which have been referred to at some point.

Get rows from site where site_id exists in referrals. This assumes site name is unique in site. If not you can add a distinct.

select s.name
from dbo.site as s
where s.deleted = 0 and
      exists (
             select *
             from dbo.referrals as r
             where s.site_id = r.site_id
             )

2 Select unique site names and how many times they have been referred to.

Join site to referrals and count the number of rows grouped by site name.

select s.name, 
       count(*) as times_referred
from dbo.site as s
  inner join dbo.referrals as r
    on s.site_id = r.site_id
where s.deleted = 0
group by s.name

3 Select unique site names and the last date they were referred to.

Add a join to calls and get the max call_date for each site name.

select s.name, 
       count(*) as times_referred,
       max(c.call_date) as last_call_date
from dbo.site as s
  inner join dbo.referrals as r
    on s.site_id = r.site_id
  inner join dbo.calls as c
    on r.call_id = c.call_id
where s.deleted = 0 and
      c.deleted = 0
group by s.name

4 Select unique site names which have never been referred to.

Same as first query only you get the sites that does not exist in referrals.

select s.name
from dbo.site as s
where s.deleted = 0 and
      not exists (
                 select *
                 from dbo.referrals as r
                 where s.site_id = r.site_id
                 )

Best Answer

Example code

Related Solutions

Sql-server – the most efficient way to get the minimum of multiple columns on SQL Server 2005

Sql-server – Queries for unique names from a site table using multiple criteria

Related Question