Tuesday, September 20, 2005

Average length of all rows in a table

Average length of all rows in a table

Sometimes you want to know how much space the average row is in your table
Instead of writing tedious AVG(LEN(field1) + LEN(field2) + LEN(field3)) from tableA
You could use the INFORMATION_SCHEMA.COLUMNS system view and create a stored procedure that loops through the table, creates the script and executes/prints this script

Well lucky for you I have created this stored procedure and the code is posted below
Before anyone says what if you just run
dbcc showcontig (‘TableName’) with tableresults you will get a field AverageRecordSize use that instead, well that’s fine but what if I only want 3 out of 4 fields? This stored procedure will let me do that; I will run it with 0 instead of 1 for the last parameter.
Once the procedure runs I copy the SQL statement from the messages tab in Query Analyzer take out the fields I don’t need and we are done

This is how you run it
@chvTableName
this is your table name
@bitExecutionType 1= select,0=print


exec AverageColumnLength 'authors',1 --select
exec AverageColumnLength 'authors',0 --print

--------------------------------------------------
create procedure dbo.AverageColumnLength
@chvTableName varchar(255),
@bitExecutionType bit
as
set nocount on

create table #TempColumnNames (
tID int identity not null,ColumnName varchar(50) not null)

insert into #TempColumnNames
select column_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @chvTableName

declare @chvSQLString varchar(2000)
select @chvSQLString = 'SELECT AVG('

declare @chvColumnName varchar(50)

declare @intLoopID int,@intLoopMaxID int
select @intLoopID = 1, @intLoopMaxID =Max(tID) from #TempColumnNames

while @intLoopID <= @intLoopMaxID
begin
select @chvColumnName =ColumnName from #TempColumnNames where tID =@intLoopID
select @chvSQLString =@chvSQLString + 'LEN(' + @chvColumnName + ')+' + char(10)
select @intLoopID = @intLoopID + 1
end


select @chvSQLString =left(@chvSQLString,len(@chvSQLString) -2) + ') ' + char(10)
select @chvSQLString =@chvSQLString + ' AS AverageColumnLength FROM ' + @chvTableName

if @bitExecutionType =0
print @chvSQLString
else
exec( @chvSQLString)--


drop table #TempColumnNames

set nocount off

Thursday, September 15, 2005

How to return random results from SQL SERVER

Sometimes you want to display 4 random articles/pics/you name it on your web page and you don’t want to write a lot of code.
SQL SERVER 2000 has a neat little function called NEWID() that can help you out.
Run the code below from Query Analyzer and keep hitting F5.
You will see that the results will be different every time.

USE pubs

SELECT top 4 * FROM dbo.authors
ORDER BY NEWID()

Note: this does not work in SQL SERVER 7

@@IDENTITY returns wrong identity field

When using @@IDENTITY I get the wrong results
I have seen this problem posted many times on newsgroups and It actually happened to a co-worker not too long ago
This ‘problem’ can occur when you do an insert into Table A and there is at trigger defined that fires when the insert happens and this trigger does an insert into table B.
What @@IDENTITY returns is actually the identity from Table B
In order to get the identity back from table A you should use SCOPE_IDENTITY() instead