Formatting Number Output

I’ve been working with some large data load processes recently and have been dumping upwards of a billion records into tables. As these are not instantaneous loads I found myself wanting to keep an eye on how much data was loaded.

This is nice and easy to do using the sys.partitions system view. Here’s an example from AdventureWorks

SELECT t.name, rows

FROM sys.partitions p

   INNER JOIN sys.tables t

       ON p.OBJECT_ID = t.OBJECT_ID

WHERE p.index_id IN (0,1)

ORDER BY rows DESC;

top+10

This is all good and well, but when you start getting to large values on the tables it becomes a little difficult to tell the difference between 31 million and 312 million. That’s when I resort back to using commas. Unfortunately SQL Server does not make this easy, although it can be accomplished with a couple of converts.

SELECT t.name, substring(convert(varchar, convert(money, rows), 1), 1, len(convert(varchar, convert(money, rows), 1))-3)

FROM sys.partitions p

   INNER JOIN sys.tables t

       ON p.OBJECT_ID = t.OBJECT_ID

WHERE p.index_id IN (0,1)

ORDER BY rows DESC

top+10+2

This is still a little arduous to type, so I created a function to do the work. Now it’s just a case of calling that function whenever I want to have the numbers presented to me in a much more readable format.

CREATE FUNCTION dbo.fnNumberComma (@iVal bigint)

RETURNS VARCHAR(30)

AS

BEGIN

DECLARE @oVal VARCHAR(30)


SELECT @oVal = SUBSTRING(CONVERT(VARCHAR(30), CONVERT(money, @iVal),1),1,LEN(CONVERT(VARCHAR(30),CONVERT(money,@iVal),1))-3)

RETURN (@oVal)

END

GO



SELECT t.name, dbo.fnNumberComma(rows) as [RowCount]

FROM sys.partitions p

   INNER JOIN sys.tables t

       ON p.OBJECT_ID = t.OBJECT_ID

WHERE p.index_id IN (0,1)

ORDER BY rows DESC;

top+10+3

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s