DbViewSharp in the Field

I may have mentioned this before, but I use DbViewSharp every day. That's why it has become a Swiss army knife of an application. In an attempt to promote it amongst my colleagues (all die hard Sql Management Studio users alas) I tend to fire it up at every opportunity to answer any database-related questions they may ask me. This sometimes misfires, but occasionally scores a hit. Today was an example of the latter.

A colleague was asking my opinion on the typical number of sub-records would be attached to a main record in a certain schema. Rather than make an educated guess I switched to DbViewSharp (already running of course), connected to the relevant database

sought the relevant sub-record table

and opened the data view. The sub-record includes a sequence number field so I selected a cell in that column and pressed the aggregation toolbar button.

The effect of this is to produce a sorted count of all the instances of the sequence number.

From this we could see that every main record contained a sub-record with sequence #1, but only 5% or so contained sequence number #2 with subsequent numbers dropping off to a maximum of around 100 sub-records associated to a main record. Below is the core of the query used to produce the grid:

select SEQ,
       count(SEQ) as [COUNT]
from SUB_RECORDS
group by SEQ
order by count(SEQ) desc, SEQ

DbViewSharp adds a few exotic touches such as a row limiter, some dodgy code to enable the query to count null records and a search clause to enable you to apply a filter. Since this aggregation function can be applied to most types (don't try it on Xml and binary types) it's useful for obtaining an overview of how any field with a limited range of values is populated; eg. proportion of 1 or 0 entries in a flag field, most common state in a US customer table, customers with the largest number of orders or sales and so on. In many cases using this feature avoids having to write a custom query. A further nice touch is that if you happen to discover an unexpected value in the report (a number out of range or an unusual text value)

then just double click on the row with the odd value to display all of the row data for any record that contains that value.


This can sometimes be the start of a trail to a problem in your data.

DbViewSharp is an open source desktop application that connects to SQL Server databases and allows you to search for just about anything important. Find it on Codeplex.