How calculate space that occupied?

#1

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.

#2

On Jun 12, 12:35 pm, visu <k.vis... (AT) gmail (DOT) com> wrote:

Quote:

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.

#3

On Jun 12, 10:47 am, M A Srinivas <masri... (AT) gmail (DOT) com> wrote:

Quote:

On Jun 12, 12:35 pm, visu <k.vis... (AT) gmail (DOT) com> wrote:

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.- 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.

#4

undercups (dwang (AT) woodace (DOT) co.uk) writes:

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#5

M A Srinivas (masri999 (AT) gmail (DOT) com) writes:

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How calculate space that occupied?

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

How calculate space that occupied? - 06-12-2007 , 02:35 AM

Re: How calculate space that occupied? - 06-12-2007 , 04:47 AM

Re: How calculate space that occupied? - 06-12-2007 , 05:43 AM

Re: How calculate space that occupied? - 06-12-2007 , 04:31 PM

Re: How calculate space that occupied? - 06-12-2007 , 04:32 PM