How can I calculate the disk space which some records used in SQL 2005 database?

I execute the following SQL, and return 3 records, I want to know how many disk space the 3 records use, how can I do? thanks!

select * from myTable where username='Paul'

I'd like to use a temp table:

select * INTO tbl_tmp from myTable where username='Paul'

exec sp_spaceused tbl_tmp

drop table tbl_tmp

|||Many thanks! but is there a better way?|||

Or you have to sum date length for all fields:

SELECT [TotalDataSize(Bytes)]=SUM(DATALENGTH(col1)+DATALENGTH(col2)+DATALENGTH(username))
FROM myTable where username='Paul'

|||Sorry, I'm a beginner, what does '[TotalDataSize(Bytes)]' mean ?|||

And more, ifcol1 is null in some records, doesSELECT [TotalDataSize(Bytes)]=SUM(DATALENGTH(col1)+DATALENGTH(col2)+DATALENGTH(username))
FROM myTable where username='Paul' cause error?

Many thanks!

|||BTW, the datalength method is not very accurate. It will give you a kind of estimate, but it isn't even close to correct in many cases.

How calculate space that occupied?

I want to know following memory space related question.

1 . How can i get a Database Size by means of a SQL query?

2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .

ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m

Here i want know the space occupied by the field name "description"
for the product id=1.

Thanks in adavance

Regards
Visu.On Jun 12, 12:35 pm, visu <k.vis...@.gmail.comwrote:

Quote:

Originally Posted by

I want to know following memory space related question.
>
1 . How can i get a Database Size by means of a SQL query?
>
2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .
>
ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m
>
Here i want know the space occupied by the field name "description"
for the product id=1.
>
Thanks in adavance
>
Regards
Visu.

1. How can i get a Database Size by means of a SQL query

sp_helpdb 'dbname'

2. Here i want know the space occupied by the field name "description"
for the product id=1.

SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.|||On Jun 12, 10:47 am, M A Srinivas <masri...@.gmail.comwrote:

Quote:

Originally Posted by

On Jun 12, 12:35 pm, visu <k.vis...@.gmail.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

I want to know following memory space related question.

>

Quote:

Originally Posted by

1 . How can i get a Database Size by means of a SQL query?

>

Quote:

Originally Posted by

2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .

>

Quote:

Originally Posted by

ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m

>

Quote:

Originally Posted by

Here i want know the space occupied by the field name "description"
for the product id=1.

>

Quote:

Originally Posted by

Thanks in adavance

>

Quote:

Originally Posted by

Regards
Visu.

>
1. How can i get a Database Size by means of a SQL query
>
sp_helpdb 'dbname'
>
2. Here i want know the space occupied by the field name "description"
for the product id=1.
>
SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.- Hide quoted text -
>
- Show quoted text -

Use the length function

SELECT LENGTH(description) FROM tablename WHERE id = 1

This will return the length of the description field for the row
identified by id = 1.|||undercups (dwang@.woodace.co.uk) writes:

Quote:

Originally Posted by

Use the length function
>
SELECT LENGTH(description) FROM tablename WHERE id = 1
>
This will return the length of the description field for the row
identified by id = 1.

There is no LENGTH function in SQL Server.

You may be thinking of len(), but since Visu asked for the space
consumption, it does not fit the bill for two reasons:

1) It ignores trailing spaces.
2) It counts characters, not bytes, which makes a difference for nvarchar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||M A Srinivas (masri999@.gmail.com) writes:

Quote:

Originally Posted by

2. Here i want know the space occupied by the field name "description"
for the product id=1.
>
SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.

Should be:

SELECT SUM(2 + DATALENGTH(description)) FROM table
WHERE product_id=1.

Add 2 for the length stored for the field.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Hosted SQL Server: Where to start?

I just added a 30MB SQL Server 2000 space for my site.

My host said that it only provides the space and nothing else. They will not be providing any means to connect to the database like Enterprise Manager 2000 or MS Client Network Utility. I'm not familiar with any of these and I looked at Microsoft's site to download it but I cannot find it.

Do you have any suggestions on how I go about setting up my hosted SQL Server? If those utilities are costly, are there cheaper/free alternatives?

Thanks.A decent free SQL app you can try is:

http://www.aquafold.com/

Its free for personal use or $89 for a corporate license. It connects to SQL and Oracle dbs.|||Somebody referred to the SQL Server Web Data Administrator.

http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&displaylang=en

It is an MSI file. How do I install this on a shared hosting account?

Thanks.|||Get the IP address/username/password they are giving you to connect to the SQL Server. You can the register the server with the IP Address. That's "usually" how the webhosts handle this.