Aggregates... Some Times they become brain teasers...

One Day ... I was sitting with one of my team mate.. He just asked me casually what could be the value of SUM aggregate on a table containing no row...
to confuse me more he ran ....

SELECT COUNT(1) FROM @tmp

The result was zero (0)...


Then he changed the SQL to...

SELECT SUM(1) FROM @tmp

I was little confused; Can any body be able to solve my confusion... make sure you guess the answer without executing the above query...

USING sp_spaceused FOR Multiple tables

Using sp_spaceused we can only find statistics for particular table within database in SQL Server. But there is way by which we can USE sp_spaceused for multiple tables too. Actually the fact is that it can be used for all tables and for multiple/all databases. Following script can help you to get space used by all tables in particular database. I will update this script for all/multiple databases soon (hint: use sp_foreachdb along with this scipt to achieve the result) .

-- Declare variables

DECLARE @Table_Size TABLE

(

[Table_Name] NVARCHAR(200)

, [Number_of_Rows] NVARCHAR(100)

, [Reserved_Space] NVARCHAR(100)

, [Data_Space] NVARCHAR(100)

, [Index_Size] NVARCHAR(100)

, [Unused_Space] NVARCHAR(100)

)

DECLARE @Table_Name NVARCHAR(200)

-- Select first table

SELECT TOP 1 @Table_Name = (TABLE_SCHEMA + '.' + TABLE_NAME)

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

ORDER BY (TABLE_SCHEMA + '.' + TABLE_NAME)

-- loop thorugh rest of the tables

WHILE ( @@ROWCOUNT != 0 )

BEGIN

INSERT INTO @Table_Size

EXEC sp_spaceused @objname = @Table_Name

PRINT @Table_Name

SELECT TOP 1 @Table_Name = (TABLE_SCHEMA + '.' + TABLE_NAME )

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND @Table_Name < (TABLE_SCHEMA + '.' + TABLE_NAME)

ORDER BY (TABLE_SCHEMA + '.' + TABLE_NAME)

END

-- Display end results

SELECT * FROM @Table_Size ORDER BY Table_Name