Table's Rowsize calcualtion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table's Rowsize calcualtion

Author

Topic

rajnish
Starting Member

4 Posts

Posted - 2010-07-08 : 08:02:35

Dear All,
I have to calculate the rowsize of the table per user.I have to show the total memory consumed by per user by calculatin the rowsize in each table where user is storing the data.?How can i do that?
Another How can i use the for loop in sql server and iterate through each row in the table.Like i have one query which returns me let 10 patients per doctor.And now i want to calculate the memory occuipied by 1st patients till 10th patient.
I don't know how to iterate in sqlserver?
Kindly help me!!
Thanks and Regards.

Ranish chattha

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-08 : 17:03:49

to iterate is faily simple:


declare @int int = 1
while 10 < @int
    begin
     --do_something_like_insert_select_etc
     select * from mytable where id = @int 
     set @int = @int + 1
   end

to calculate the rowsize, you must be more pricise what are you looking for?
you can either manually calculate the row size on the disk, in the table, on general, by calculating the lenght of the string/field and by diving the column definition. and as well keep in mind if you are calculating the size on the disk to keep in mind your disk configurations.

rajnish
Starting Member

4 Posts

Posted - 2010-07-09 : 07:56:53

ok am trying to calculate the rowsize by datalength('columnname') is it right way to do find the size??
Secondly I have one query which returns me the ID like 6,5,4
Select @id=funid from tablename where userid=@userid
Now i want to check the conditiond like
if(@id=1)
{
}
if(@id=2)
{
} n so on...But i don't know its syntax..How do i fetch the multiple rows...I want to do it without cursor...as am already using cursor in my store procedures..
Thanks n Regards
Rajnish Chattha.

Ranish chattha

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-09 : 11:11:53

SQL is a set based language, so you should use set based operations and not iterate. If you can post some table definitions, sample data and expected output, we might be able to provide alterative solutions to iterating through rows. Here is a link that might help you in preparing your data/question:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rajnish
Starting Member

4 Posts

Posted - 2010-07-13 : 06:09:35

1.How can I iterate through the result returned by Select Query In SP?
2.CREATE TABLE [dbo].[ActorFun](
[UserID] [bigint] NOT NULL,
[FunId] [tinyint] NOT NULL
) ON [PRIMARY]
3.INSERT INTO [newesanjeevani].[dbo].[ActorFun]
([UserID]
,[FunId])
VALUES
(<UserID, bigint,>
,<FunId, tinyint,>)
4.I have performed select operations on this table
Select @funid=funid where userid=@userid
and it reruns me the value like:
6
3
1
5.Now i want to do some operations as per the value returned by the select statement like
if(@funid==6)
begin
do something--
end
if(@funid==5)
begin
some logic
end
....The problem is @funid only contains the last rows's value i.e 1
How I can do the above operations.

Ranish chattha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources