Software development, .Net, SQL Server, TDD, Agile, Community and other Odds and Sods
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Thursday, May 26, 2011
TSQL: Finding Maximum, Minimum and Average Data Row Lengths
This is probably a classic example of if you find you are doing something complicated, there’s almost certainly a better way. It’s also an example of if you think something is genuinely useful and can’t understand why it’s not been implemented already, it probably has but you just haven’t found it yet!
I wanted to get a table’s approximate minimum, maximum and average row size, so after a few attempts I came up with this TSQL snippet:
declare @schemaname varchar(200) = 'Person'
declare @columnList nvarchar(max)
[Note: It’s not 100% accurate due to some row overheads, but it is close enough for many purposes.]
At which point, the thought “Surely there exists a built-in method to do this?” crossed my mind, and of course there is!
If you have sufficient permissions you can quickly produce a min, max and average row size for each table in a database (along with fragmentation information) using
DBCC SHOWCONTIG WITH TABLERESULTS
MSN, Email: mitch døt wheat at gmail.com