2009-01-07

Selectivity

After a breif discussion with a collegue about selectivity this script was created to have something concrete to work with.

-- Single key
SELECT TOP(10)
 SalesOrderID,
 (CAST(COUNT(*) AS float) / CAST((SELECT COUNT(*) FROM Sales.SalesOrderDetail) AS float))*100 AS [percent]
FROM Sales.SalesOrderDetail WITH (NOLOCK)
GROUP BY SalesOrderID
ORDER BY [percent] DESC

-- Combined key
SELECT TOP(10)
 SalesOrderID,
 SalesOrderDetailID,
 (CAST(COUNT(*) AS float) / CAST((SELECT COUNT(*) FROM Sales.SalesOrderDetail) AS float))*100 AS [percent]
FROM Sales.SalesOrderDetail WITH (NOLOCK)
GROUP BY SalesOrderID, SalesOrderDetailID
ORDER BY [percent] DESC


On SQL Server 2005 the database is [AdventureWorks] and on SQL Server 2008 the database is [Adventureworks2008].

Still I'm (a little) uncertain if the % calculation is correct - right now the percentage is low (good), but should it be high (good)? I'm looking for a more precise calculation definition.

No comments: