Archive

Archive for November, 2013

Fast Row Count for All Tables

November 12, 2013 Leave a comment

 

Using Count(*) to obtain a row count can be very slow on large tables. This is the only truly reliable way to obtain an accurate row count but, post SQL Server 2000, the mess that was sys.indexes, etc. was largely cleaned up and nowadays the row count can be obtained using the following code. Microsoft still states that the row count in sys.partitions is an “Approximate number of rows in this partition”, but it appears to be stable and accurate with appropriate caveats with regards to uncommitted transactions.

 

Select      s.Name
           ,t.Name
           ,Sum(p.rows) As Rows
From        sys.tables t
Inner Join  sys.schemas s On (s.schema_id = t.schema_id)
Inner Join  sys.indexes i On (i.object_id = t.object_id and i.index_id < 2)
Inner Join  sys.partitions p On (p.object_id = t.object_id)
Where       p.index_id In (0, 1)
Group By    s.Name
           ,t.Name
Order By    s.name
           ,t.name
Categories: SQL Server, T-SQL Tags: , , ,

Mark Items as Default in Reference Data … and Ensure There is Only One

November 6, 2013 Leave a comment

 

Often it is convenient to be able to default values in where they are not specified, or to not require items to be specified to make life easier.
For maintainability it is best to mark default values in the data itself, rather than in some code somewhere, and where better than in the source reference table.
By adding a bit column ‘IsDefault’ it is simple to mark a particular record as the default, however it is also easy to mark several items as default by mistake.
But with careful use of a filtered unique index it is possible to apply a constraint such that there can be only one default record as below.

 

Create Unique NonClustered Index IX_UNIQUE_IS_DEFAULT On MyReferenceTable
(
	IsDefault Asc
)
Where (IsDefault=(1))

 

By filtering, we don’t index the ‘false’ values so that more than one record can be marked as “not the default”, and by making it unique we ensure that we only have a single record marked as default.