Table Size Question

Sep 17, 2004

I've got a table with 180 million rows and 19 columns.

There may be a need to add an additional 250 columns(all of them decimal (7,2).

how prohibitive would this be from a performance standpoint?

Many Thanks,

Isaac

View 3 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Possible To Find Table Size And In That Table Each Row Size

Jun 10, 2014

It is possible to find table size and in that table each row size.

View 4 Replies View Related

Table Size And Database Size

Mar 2, 2008

Hi,
i use this script that show me the size of each table and do the sum of all the table size.

SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]

the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?

THX

View 5 Replies View Related

SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.

Sep 4, 2007


I am trying to resize a database initial log file from 500M to 2M. I€™m using€?

ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "



And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.



Any help with this process?

View 1 Replies View Related

Table Size

Mar 1, 2002

I have some question and would like ask experts:

The largest table in our database eats up above 4G . we do "sp_spaceused" for this table.The length of all columns of this table ( just int, char, varchar, money ,numeric fields types) is about 200 bytes, and the table has around 1,300,000 rows, but the reserved spaced for this table is 4,800,000kb and the data space is around 4,600,00kb.

How can average each row take 3.7kb ( the total size of all columns just 200 bytes)? Any other things I need check?
Any one can give any suggestion what cause this problem? or it is normal?

Thank you very much.

Judy

View 1 Replies View Related

Table Size

May 18, 2001

Hello, Everyone,

Can anyone tell me how to find the size of a table in a DB?

Thanks,

View 2 Replies View Related

Table Size In SQL 6.5

Nov 21, 2000

I'm trying to run a query or sp that will give me a
list of tables and the number of rows in it.

Is there any way for me to do this?

I appreciate the assistance.
Toni

View 1 Replies View Related

Table Size

Dec 21, 1999

I am wondering if there is the limitation of maximum table size in SQL 6.5. I have a table with 2.6GB and 12,000,000 rows in SQL 6.5 database. Is this a problem?

TIA,

Stella Liu

View 2 Replies View Related

Max Table Size?

Jul 15, 1999

Is there a practical size limit, in MB's, of a table in SQL Server 6.5?

Is there a size, that once exceeded, degrades performance signifigantly?

I am speaking of raw megabytes. The table in question will consist of only 3 int columns but has the possiblity of becoming VERY LARGE (+1,000,000 rows). I am still in the design phase and can change my strategy if this will prove to be a problem.

Thanks for any help!

View 2 Replies View Related

Table Size.

Jan 14, 1999

hello all,

I am trying to solve this problem for quite some time.. I was wondering if I can get some help..

These questions are all abt. MSSQL 6.5

1. Is there a limit on the size of the table ?
2. Does it make sense to have more tables if the size of the row size is more that the limit set by 6.5 or i should let have more rows in a different table with duplicate entries for a particular field.
3. What is the number of rows before the performance of a query starts getting affected..

Thanks..

Sachin.

View 1 Replies View Related

Table Size

Aug 26, 2002

I created a same table on two different server with same data. I run sp_sapceuse on both server and I got following result

it's useing 392 MB for table
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
DUNS_SITE 100000 401288 KB 400000 KB 1264 KB 24 KB

its using only 97.3 MB

name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
DUNS_SITE 100000 99720 KB 99376 KB 328 KB 16 KB


WHY?????

Thanks
Manesh

View 2 Replies View Related

Table Size

Jul 19, 2004

Hello,
How can i get the size of a table in sqserver 2000 ?
How can read a image field ?
Thanks for this answer.

View 9 Replies View Related

Max Size Of A Table

Mar 2, 2005

Is there a maximum or optimum number of rows I should have in a table so that I can have fastest search queries. I am a novice programmer just developed something for my work place.
The database has a table created by converting data from excel spreadsheets. There were 24 spreadsheets for 12 months each having approximately 500 rows. Designed this way the table will have approximately 24 * 500 = 12000 records. Should I consider redesigning the database to make searches faster

View 1 Replies View Related

Table Size ;

May 25, 2006

Hi all,

How can I find the exact size in the disk occupied by a TABLE ?

When I execute "sp_spaceused" it returns the following parameters for a table

reserved
data
index_size
unused

Which of the parameters I should consider to calculate the exact space occupied by the table.

Thanks in advance,

Hari Haran Arulmozhi

View 9 Replies View Related

Table Size

Jan 19, 2006

Is there any way I can find the size of each table?

View 2 Replies View Related

Table Size

Jul 20, 2005

Is it better to have a table with 10,000 row or 10 tables of 100 rows?

View 8 Replies View Related

SQL Table Size

May 17, 2006

Hello,

If i want to know the size of table then how can i do it in SQL Server 2000 and in SQL Server 2005.

-- how much amount of data can a table store in sql.

-- On which thing the size of table depends.



or Can anyone give me a introduction about the size of table in sql

View 5 Replies View Related

Row Size Of Each Row In Each Table - Urgent

Jul 10, 2006

Hi. I am trying to get a row count of each row of each table in the database. Is that possible? Using a SP or UDFS? I dont want the column size of each table but the total datasize of each row.So for example if I have 5 rows each in 3 tables I need a query that will return 15 rows with the size of each row(size of all coumn data summed together). Thanks.

View 6 Replies View Related

Size Of Table - Any Functions?

Jan 1, 2004

Been digging around, I want to query the size of a table (disc size)

Anything?

View 1 Replies View Related

Get Size Of A Table/query

Sep 15, 2004

Is there a way to get the size of a query of a table?

I know I can use DATALENGTH on every column in my query, but I thought there might be an easier way.

View 9 Replies View Related

Query Size Of Table

Oct 30, 2004

is there an easy way to query the size of a table on disc?

View 3 Replies View Related

Average Size Of A Row In A Table

Feb 20, 2001

How do I find the average size of a row in a table? I need to calculate a row size in a number of tables, then sum those to find the average size of one record ( a hotel guest in this case), which includes entries in a dozen tables.

Thanks

View 1 Replies View Related

Name And Size Of Each Index In A Table

Mar 21, 2001

Hi all,
Is it possible to get the name and size of each index in a table? Please let me know.

Thanks in advance,
Praveena

View 4 Replies View Related

Table Size/Breakup

Apr 25, 2001

SQL 7

I have inherited a system where several of the tables have WAY TOO MANY fields (over 255) and performance, well, you know.

I've been thinking about breaking the tables out into multiple tables and I know a view linking all these tables together can provide me with backward compatibility.

I'm wondering if a view will truely be a greater gain than leaving it alone while we redesign. hhhmmmm

Thoughts ?

Craig

View 2 Replies View Related

Total Row Size In A Table

Jun 18, 2001

Hi,
I am getting this error when I try to run a script to create a table. The error reads as below

The total row size (12488) for table exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.

Can somebody tell me what I have done wrong?
thanks
zoey

View 1 Replies View Related

Calculating Size Of A Table

Aug 7, 2000

I would like to calculate the size of a table eg. 100 rows, 5 columns.

Any suggestions?? Thanks Vic

View 1 Replies View Related

Size Of Table(Urgent)

Feb 6, 2001

Hello,

I've a database in Production db which is 300 MB and one of its table has
948900 rows then how can I know how much size this particular table is of.

Thanks.

View 2 Replies View Related

List Table Name And Size

Jul 29, 2002

Anybody know any method/script I can use to see a list of table name
and table size (in bytes, not rows) within a database?
I know I can see the size of any particular table in "Table info" tab or using "sp_spaceused" procedure. But I can't generate a report with a list of table name and table size using these methods.
Any idea? Thanks.

John

View 2 Replies View Related

Average Row Size For A Table

Aug 21, 2002

I want to find out the average rwo size of a table in sql server 2000. How can i achive this requirment? Please let me know how can i do this?

Thanks & regards,

Dishant Sharma

View 1 Replies View Related

Name And Size Of Each Index In A Table

Mar 21, 2001

Hi all,
Is it possible to get the name and size of each index in a table? Please let me know. In sql 7, we could do this using EM but, in sql 2000, I'm not sure how to do this.

Thanks in advance,
Praveena

View 1 Replies View Related

Calculate Table Size

Oct 10, 2001

Hi all!

How can I calculate the combined table size for the following:
The Destination and the ShipmentWages tables on a database of a courier service have approximately 10,000 rows and 15,000 rows, respectively. The average row size of the Destination table is 4KB and that of the ShipmentWages table is 3KB.

Thanks,
ndba

View 1 Replies View Related

Finding The Size Of A Table?

Oct 12, 2007

Hi All,

Do any of you know how I can find the size of a table in SQL server?

Cheers

Dave

View 3 Replies View Related

Calculating The Size Of A Table In A Db

Aug 10, 2006

If I have a database with a list of tables is there a way to calculate the size of each table individually and
then calculate the size all the tables. If you have 1 table with say 10 rows and 3 columns and the width of the
columns are of variable length you could do something like

( column1width + column2width + column3width ) * No.of Rows = Tablesize

So my question is can I reference the column width of different columns in a
table using sql ?

Another issue is that some of the columns are different datatypes so I should be taking that
into consideration as well.

From searching the internet so far I have seen little on SQL showing how to
reference column width in a table.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved