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
ADVERTISEMENT
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
Aug 11, 2000
I need to calculate the size of a database. Is the following formula correct??
(a) The size of a record (i.e. sum of all the columns) divide by 8Kb.
(b) Divide number of records by answer from (a)
(c) Multiple (b) by 8Kb
(d) Sum up the tables
Thanks,
Vic
View 1 Replies
View Related
Feb 8, 2000
How do I calculate the Record size? I mean the Row size for each table.
I heard that there are formulas to do that and the calculation is different for Fixed lenght Columns and Variable length Columns.
I need to calculate the record size for nearly 500 tables.This will help me to decide on the DISk capacities.
Suggest me if any scipts or calculations already available.
Thanks in advance.
Samson
View 4 Replies
View Related
Jan 1, 2004
Hi , everyone !
I need help . Is anybody can help me to finde transact-SQL code of calculating the database size.
Thanks in advnce , Alex
View 1 Replies
View Related
Jul 20, 2005
I've seen plenty of posts regarding the estimation of table size,usually in the processing of planning for server storage needs.Well, I've got a different problem. I need to know how much data eachof our Customers are using in a Database. (1 SQL DB stores multiplecustomers).Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:655 MB.So, how can I ask SQL Server how much data each Row in each table istaking up? I want to be able to calculate nightly the total size, so Iwould take each row in each table that belong to the customer, and addall the sizes together. I want to take into account blobs that arestoring images and PDF files also.Thanks in advance,Jesse Wolgamott
View 1 Replies
View Related
Jun 10, 2014
It is possible to find table size and in that table each row size.
View 4 Replies
View Related
Jan 19, 2008
Code Block
Hi,
I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:
Table Name: metrics_ladder
id security_id metric_id value
1 3 80 125.45
2 3 81 548.45
3 3 82 145.14
4 3 83 123.32
6 4 80 453.75
7 4 81 234.23
8 4 82 675.42
.
.
.
Table Name: metric_details
id metric_id metric_type_id metric_name
1 80 2 Fiscal Enterprise Value Historic Year 1
2 81 2 Fiscal Enterprise Value Current Fiscal Year
3 82 2 Fiscal Enterprise value Forward Fiscal year 1
4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2
5 101 3 Calendar Enterprise value Historic Year 1
6 102 3 Calendar Enterprise Value Current Fiscal Year
5 103 3 Calendar Enterprise value Forward Year 1
6 104 3 Calendar Enterprise Value Forward Year 2
Table Name: metric_type_details
id metric_type_id metric_type_name
1 1 Raw
2 2 Fiscal
3 3 Calendar
4 4 Calculated
The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:
id security_id metric_id value
1 3 101 <calculated value>
2 3 102 <calculated value>
3 3 103 <calculated value>
4 3 104 <calculated value>
Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.
Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,
metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally
metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).
As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:
security_id 80 81 82 83 101 102
----------- -- -- -- -- -- --
3 125.45 548.45 145.14 123.32 <calculated value> <calculated value>
4 ...
.
.
.
which is then unpivoted.
The SQL that achieves this is more or less as follows:
*********
START SQL
*********
declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...)
-- Dummy year variable to make it easier to use MONTH() function
-- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc...
DECLARE @DUMMY_YEAR VARCHAR(4)
SET @DUMMY_YEAR = 1900;
with temp(security_id, metric_id, value)
as
(
select ml.security_id, ml.metric_id, ml.value
from metrics_ladder ml
where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...)
-- only consider securities with fiscal year end not equal to december
and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec')
)
insert into @calendar_averages
select temppivot.security_id
-- Net Income
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103]
,NULL as [104]
-- Share Holders Equity
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107]
,NULL as [108]
-- Capex
-- Sales
-- Accounts payable
etc...
..
..
from temp
pivot
(
sum(value)
for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...)
) as temppivot
inner join company_details cd on temppivot.security_id = cd.security_id
*********
END SQL
*********
The result then needs to be unpivoted and stored in metrics_ladder.
And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.
Many thanks (if you've read this far!)
M.
View 6 Replies
View Related
Nov 16, 2007
I currently have 8,000 rows in the orders table and it is estimated that it will have in average 50 orders daily. The orders need to be kept for 6 months, before it is archived and deleted from the database. I calculated the amount of space that needs to be reserved for the table but unsure if I am on the right track in calculating the table size.
would there be anything wrong in my calcultation that i missed?
int
datetime
tinyint
nvarchar(15)
int
int
int
int
money
money
money
money
datetime
4
4
2
15
4
4
4
4
4
4
4
4
4
4
= 65 bytes
+ rowoverhead = 7
total = 72
View 12 Replies
View Related
Feb 19, 2012
I need to calculate a median on a column in a table. The code I have is:
Code:
Select gender,
CASE
when gender = 'F' then 'Female'
when gender = 'M' then 'Male'
else 'Unknown'
end as test,
datediff(day, [admit_date], getdate()) as 'datediffcal',
from [tbl_record]
How do I calculate the median on the datediffcal column?
It doesn't matter if the resultset only shows the median result. So if the output shows:
median
15
that's fine. Minimally, I need the median value.
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
Jan 19, 2006
Is there any way I can find the size of each table?
View 2 Replies
View Related
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
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
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
Jan 1, 2004
Been digging around, I want to query the size of a table (disc size)
Anything?
View 1 Replies
View Related
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
Oct 30, 2004
is there an easy way to query the size of a table on disc?
View 3 Replies
View Related
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
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
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
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
