Find size of row in table

Is there any way to find the size of a particular row in a table?
Thanks,
TomTomT wrote:
> Is there any way to find the size of a particular row in a table?
> Thanks,
> Tom
For storage usage, you can use datalength(). For number of characters,
you can use LEN().
create table test1 (
id int identity,
col1 int,
col2 varchar(100),
col3 nvarchar(50),
col4 nchar(10),
col5 datetime)
insert into test1 values (5, 'Hi there', 'hi there unicode', '',
getdate())
Select * from test1
Select datalength(col1) +
datalength(col2) +
datalength(col3) +
datalength(col4) +
datalength(col5)
From test1
Where ID = 1
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks for your reply. I tried your suggestion, and after adjusting for
possible null values in some of the columns, got the results I needed.
The reason I'm doing this is due to a strange problem with a row in a table.
This table has a variety of columns, one of which is for notes, and it is
varchar(3000).
On the row in question, I am trying to insert notes which are 1900
characters (including spaces, line breakes, etc.).
The problem is, for some reason I cannot insert more than 1023 characters
into the column. I'm testing this by going directly into the row in the
table, and typing in the characters. When I try to go beyond 1023 characters,
all that happens is my system beeps, and no characters are accepted. Then
entire size of the row, via your suggestion, is 1239.
Curiously, the row just before this has a total length of 2481, and the
number of characters in the notes column is 2258.
I'm stumped as to why I can't get more text into the column, I even removed
some characters from other columns in the row, just to see if this would make
a difference, but it did not.
Do you have any idea what might be happening here?
Thanks for your help,
Tom
"David Gugick" wrote:
> TomT wrote:
> > Is there any way to find the size of a particular row in a table?
> >
> > Thanks,
> >
> > Tom
> For storage usage, you can use datalength(). For number of characters,
> you can use LEN().
> create table test1 (
> id int identity,
> col1 int,
> col2 varchar(100),
> col3 nvarchar(50),
> col4 nchar(10),
> col5 datetime)
> insert into test1 values (5, 'Hi there', 'hi there unicode', '',
> getdate())
> Select * from test1
> Select datalength(col1) +
> datalength(col2) +
> datalength(col3) +
> datalength(col4) +
> datalength(col5)
> From test1
> Where ID = 1
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||TomT wrote:
> David,
> Thanks for your reply. I tried your suggestion, and after adjusting
> for possible null values in some of the columns, got the results I
> needed.
> The reason I'm doing this is due to a strange problem with a row in a
> table. This table has a variety of columns, one of which is for
> notes, and it is varchar(3000).
> On the row in question, I am trying to insert notes which are 1900
> characters (including spaces, line breakes, etc.).
> The problem is, for some reason I cannot insert more than 1023
> characters into the column. I'm testing this by going directly into
> the row in the table, and typing in the characters. When I try to go
> beyond 1023 characters, all that happens is my system beeps, and no
> characters are accepted. Then entire size of the row, via your
> suggestion, is 1239.
> Curiously, the row just before this has a total length of 2481, and
> the number of characters in the notes column is 2258.
> I'm stumped as to why I can't get more text into the column, I even
> removed some characters from other columns in the row, just to see if
> this would make a difference, but it did not.
> Do you have any idea what might be happening here?
> Thanks for your help,
> Tom
Unless this is a small table, you should probably get that varchar(3000)
out of there or change it to a text/ntext. If that column is even 1/3
full, you'll end up with very large row sizes and decrease your row
density (which slows everything down). 8060 is the limit on row length,
but it doesn't appear you are near that.
You say, "by going directly into the row in the table". That term means
nothing in the relational database world. There is no way to go directly
into a row and edit, short of opening a data file in a editor and
directly manipulating the file. You are using some application and that
application likely has column entry limits of 1K or 1024 characters. Try
entering the data using SQL or another application.
--
David Gugick
Imceda Software
www.imceda.com