Size Of Tables/records

Jul 2, 2004

Is it possible to determine the data size of records in a table. basically I would like to run a select query with a condition and the result will be xKB. I know that this is possible on a database level, but can it be done on a record level (or number of records).





Cheers





H

View 4 Replies


ADVERTISEMENT

Is It Possible For A Table To Increase Size When Deleting Records?

May 24, 2007

No transaction log involved, only the table itself.



Use sp_spaceused "table_name" to check the space used.



It seems the table size actually increased from the beginning to the middle of deletion, at the end of deletion, its size decreased.



Recovery mode set to be simple, autoshrink turned on.



The tables tested are about 50MB ~ several GB in size, all have the same behavior. The size increased about 5%~10%.



Since the deletion is called from another software, I want to know if it is possible for SQL Server to have this behavior or it is absolutely the 3rd party software's issue



Thanks!

View 2 Replies View Related

Insert Records From Foxpro Tables To SQL Server Tables

Apr 22, 2004

Hi,

Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:

1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.

I only know the following ways to import Foxpro data into SQL Server:

#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables

I'm thinking whether the following choices will be better than the current way:

1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2

Thank you for any suggestion.

View 2 Replies View Related

How Can I Know The Records In SQL 2005 Table Possess Size Space?

Sep 19, 2006

We can know easily a database disk size, but can we konw a table in SQL 2005 database possess size? and more, how  can I know the records in SQL 2005 Table possess size space?

View 8 Replies View Related

Database Space Size Increased On Deleting Records

Feb 20, 2008

Hi,

I was running out of space and thus deleted some rows from a table. To my surprise the db size increased. I then shrunk it to bring it back to what it was earlier.

When i deleted some 5000 rows, some space must have been released. Where did the space go and why did the db size increase after deleting the records?

I thght it might be log files..but db is set to Simple Recovery which does not utilize a Log File.

Any reasons?

View 6 Replies View Related

Size Of Tables

Jan 21, 2000

I am finding that the size of my SQL Server 7 tables are approx 75% LARGER than the same Visual FoxPro table. I am not considering indexes in either one. The only difference is that the SQL Server table has a timestamp that the Visual FoxPro table does not.

Is this normal? or did I do something wacko?

I upsized the data from Visual Fox to SQL by appending the info in the Visual Fox table into a remote view and then doing a TABLEUPDATE to send it to SQL Server.

Any help would be appreciated.

Rick

View 2 Replies View Related

Size Of Tables

Nov 16, 2001

I'm having to do some estimation on the size of the db that I will eventually create.

I have a 6.7 million row table that holds 5 integers and a varchar(8).
Therefore my crude maths are that an integer is 4 bytes and a varchar is max 8 and therefore each line is 28 maxmimum bytes long. Multiply by 6.7 million and you get 1876000000 bytes or 183203KB or 179MB's.
With me playing with this table the db has balooned from 23mb to 1,325mb. This doesnt work out. One possible explanation is if SQL Server does the same as Access and you need to compress to gain the space back. I wasn't aware of this though. I have filled and emptied the table several times over to test the import routine.

I therefore have a few questions.
1. Why has the db ballooned to this size?
2. How do I accurately work out future db/table sizes?
3. Is there a way to work out the size of a table?
4. Do SQL db's have to be compacted like Access's?

Help appreciated.

View 2 Replies View Related

Size Of Tables

Jun 5, 2008

Hi,

I know I can check individual table's properties for the size and row count of each, but is there a way of doing it for all tables within a database in SQL server 2005 ? I have about 69 tables in one..

Thanks,
Raddy

View 2 Replies View Related

Size Of Tables

Nov 8, 2006

Hi,

I have a database which is 25 GB in size. The database has few tables.

Can I individually find out which table is occupying more space?

Thanks
Santhosh

View 3 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

List Of All Tables And Size

Oct 1, 2001

Does anybody know how to make a query that presents ALL
tables and their sizes (in Kb or Mb) from a database?

Is there a Stored Procedure for this purpose?

Thanks

View 2 Replies View Related

Sorting Tables Of Db By Size??

Apr 4, 2006

Hello I have 896 tables in a database and I want to sort the tables by size to get the biggest sized tables , how can I do it?

F16 LĂ?GHTĂ?NĂ?NNNG

View 1 Replies View Related

Size Of User Tables In A Database

Nov 4, 2003

The following procedure will display the size of all the user tables in a database.

CREATE proc sp_tablesize
as
if exists (select * from sysobjects where name = 'sp_tablesize')
begin
goto calculate_tablesize
end
else
begin
CREATE TABLE #SpaceUsed
(
TableName sysname,
TableRows int,
TableSize varchar(10),
DataSpaceUsed varchar(10),
IndexSpaceUsed varchar(10),
UnusedSpace varchar(10)
)
goto calculate_tablesize
end

calculate_tablesize:
declare @tablename nvarchar(50)
declare @cmd nvarchar(50)

declare c1 cursor for select name from sysobjects where xtype='u'
open c1
fetch c1 into @tablename
while @@fetch_status = 0
begin
set @cmd='exec sp_spaceused['+@tablename+']'
insert into #SpaceUsed exec sp_executesql @cmd
fetch next from c1 into @tablename
end

select * from #SpaceUsed

drop table #SpaceUsed
deallocate c1

View 20 Replies View Related

Check The Size Of All Tables In My Database

Jun 26, 2007



Hi



I am trying to check the size of each table in my database?



SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE



I can't for the lif of me figure out how this is done.



Any help would be greatly appreciated



Kind Regards

Carel Greaves

View 10 Replies View Related

How To Find Rowcount And Size(space Used) For All Tables In A Db?

Jun 2, 2004

How to find rowcount and size(space used) for all tables in a db? Can any one give me the script please?

Thanks,

View 2 Replies View Related

List All Tables Sizes, Sorted By Size

Jan 13, 1999

How do I get a list of table names and their sizes, sorted by sizes? The command sp_spaceused only lists one table at a time.
Thanks for your help.

Lan.

View 2 Replies View Related

T-SQL (SS2K8) :: To Get Size Of Database Based On Tables

Nov 17, 2014

I have 57 tables, 7 views and 1 stored procedure. Just wanted know based on these requirements how can I find the size of the database. Though the DB contains lots of tables, views and procedures. I am moving these details to new DB server. So I need to put right requirements.

View 2 Replies View Related

Database Size Allocation - Where Other Than Tables, Logs, Etc ????

Jul 12, 2007

I have a Db that is 1.7 gigs. The table data takes approximately 200megs. The transaction logs were truncated. Where else can this large size be coming from and how can I confirm?



DB is generally small. ~25 tables, 100 SPs, 10 views, etc.



Note:



I have 4 queues using SQL Notifications, but when selecting from them results in no data.



Thanks



Scott

View 1 Replies View Related

Transact SQL :: Calculate Size Of All Tables In A Database

Nov 16, 2015

I need to build a query to calculate the size of all tables in a database ...

View 5 Replies View Related

Adding Size Of Identified Tables On All Databases On A Servers??

Jun 13, 2000

I have used the following script to add the size of chosen tables in all the databases on a given server. What I need to do is to create a GRAND total. Can someone give me a hint?


exec sp_msforeachdb
@Command1 = "print'?'",
@Command2 = "use ?
SELECT CAST (o.name as char(20)) as 'Table', SUM(c.length) 'Record size',
MAX (i.rows) '#of rows',
CONVERT (decimal (10, 4), SUM (c.length * i.rows)/(1024.00 * 1024.00)) 'Approx. size (MB)'

FROM sysobjects o, syscolumns c, sysindexes i
WHERE o.id = c.id
AND o.id = i.id
AND (i.indid = 1 or i.indid = 0)
AND o.type = 'U'
And o.name in ('Table1','Table2','Table3')
GROUP BY o.name
COMPUTE SUM (CONVERT (decimal (10,4), SUM (c.length * i.rows)/(1024.00 * 1024.00)))"

Thank you

View 1 Replies View Related

SQL Server 2008 :: Temp DB Size Values From Different Tables

May 25, 2015

For finding size values of temp database:

select * from sys.master_files -
size column value here is 1024 for .mdf,size here for .ldf is 64
select * from tempdb.sys.database_files -
size column value here is 3576 for .mdf,size here for .ldf is 224

Why is there a difference and not the same. size columns in the above 2 tables for temp db's do they represent different values ?

View 1 Replies View Related

Database Size Info Without Cursors Or Temp Tables

Jan 22, 2004

I have seen a bunch of ways to get the size of all the tables within a database posted on this board. I decided to modify an older one I found here (http://www.sqlteam.com/item.asp?ItemID=282). I set it up so there is no cursors or temp tables. Pretty much just one select statement to return all the info you would need. It seems to be faster than anything I have seen so far. Take it for whats its worth. Thanks to the original creator.



/*
Original by: Bill Graziano (SQLTeam.com)
Modified by: Eric Stephani (www.mio.uwosh.edu/stephe40)
*/

declare @low int

select @low = low from
master.dbo.spt_values
where number = 1 and type = 'E'

select o.id, o.name, ro.rowcnt, (r.reserved * @low)/1024 as reserved,
(d.data * @low)/1024 as data, ((i.used-d.data) * @low)/1024 as indexp,
((r.reserved-d.data-(i.used-d.data)) * @low)/1024 as unused
from
sysobjects o

inner join
(select distinct id, rowcnt
from sysindexes
where keys is not null and first != 0) ro on o.id = ro.id

inner join
(select id, sum(reserved) reserved
from sysindexes
where indid in (0, 1, 255)
group by id) r on o.id = r.id

inner join
(select c.id, dpages+isnull(used, 0) data from
(select id, sum(dpages) dpages
from sysindexes
where indid < 2
group by id) c full outer join
(select id, isnull(sum(used), 0) used
from sysindexes
where indid = 255
group by id) t on c.id = t.id) d on r.id = d.id

inner join
(select id, sum(used) used
from sysindexes
where indid in (0, 1, 255)
group by id) i on d.id = i.id


where o.xtype = 'U'

order by reserved desc

View 3 Replies View Related

Impact Of Empty Tables On Database Size And Performance

Jun 21, 2007

Hello All,

When creating my database I have modeled some of the tables after the Adventureworks sample database.

There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).



In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.



Thanks for your help!

View 1 Replies View Related

Transact SQL :: Database Size Does Not Decrease After Dropping All The Tables

Oct 15, 2015

I have a database consisting of two main tables and 12 sub tables.

This was leading to increase in database size. So we thought of storing the sub tables data in the main tables in form of xml  in a column of varchar(2000) type.

So we created a new database that only had 2 tables and we stored the data of the sub tables in the new column of the main table.

Surprisingly we saw that the database size increased rather than decreasing .

View 9 Replies View Related

Replicating Database Btw Two Sites Why Aren't The Tables Identical In Size?

Apr 30, 2007

I am replicating an 80GB database between NY can CT and would like toknow why table sizes are different between the two.Here is an example of sp_spaceused::NY IOI_2007_04_23 rows(279,664) reserved(464,832)data(439,960) index_size(24,624)CT IOI_2007_04_23 rows(279,666) reserved(542,232)data(493,232) index_size(48,784)Thanks,

View 1 Replies View Related

Huge Tables: Minimizing Row Size - Remove Null Bitmap

Apr 21, 2007

I am in the process of designing a SQL 2005 database with tables that may hold several hundreds of millions of rows.

Due to various constraints, I am trying to save as much space as possible by optimizing the size of a row. Currently one row contains the following columns:



byte(4), byte(3), byte(3) = 10 bytes.



Adding 4 bytes for the row header, plus 3 bytes for the null bitmap (as described in BOL) I am ending up with 17 bytes/row. In a real world test it was an average of 18.3 bytes/row.



There are no indexes, no primary key and all columns are not NULL. Hence my question: since no column allows a null value, is there a possibility to "remove" the 3 bytes Null Bitmap?

Is there any other way to shave off one or two more bytes by using a clustered index etc?



Thanks

View 4 Replies View Related

SQL 2012 :: Check Size Of Database After Implementing Data Compression Across All Tables

Dec 4, 2014

I found it pretty interesting. I checked the size of a database, before implementing database compression across all the user tables in a database. And Post implementation of compression too I checked the size of the database.

I did not find any difference. But if I expand the table and check propetires->storage and I can see that PAGE compression is implemented across all the tables, but no compaction in the size of the db. It still remains the same.

View 6 Replies View Related

DB Engine :: Disk Block Size For Memory-optimized Tables Filestream Data

May 7, 2015

In SQL Server 2014, how big for the block size is better for performance? 64 KB? 4 KB?

For normal database files, best practise is 64 KB disk block size. Not sure if it is same for memory-optimized filegroup.

View 12 Replies View Related

Update Of Records From Other Tables

Sep 11, 2000

I am trying to update a field within one table with the values from another table. With the criteria that another field in each table are equal. What is the correct way to do this. My syntax is all wrong.

thanks
Jason

View 1 Replies View Related

Counting All Records In All Tables

Feb 26, 2001

I report SQL Server table information (table names, field names, field datatypes, etc.) to my users using an ACCESS front end. Most of this information exists in the system tables. But I can't find a record count per table in the system tables. What's the fastest way to get a record count for all tables?

View 1 Replies View Related

Need Help With Transfer Many Records Between Tables

Jan 27, 2008

Hi!
I have 2 tables (both have the same structure):
ID -> bigint (identity, not for replication, primary key)
Url -> nvarchar(1000)
MainUrl -> nvarchar(1000)

Tbl1 cantains about 0,5 mln records, and tbl2 - 1 mln.
What I need, is to copy records from tbl2 to tbl1. But records in tbl1 are unique, and it can't change. (Unique must be only "Url"; (and ID, but it's automatic)). How can I do this in fast way? Now I'm using SELECT for each record in tbl2 to see if it exist in tbl1. But it's a bit slow... Is there any faster method? (One thing: I'm beginner in databeses, so I'm wrote VB application to transfer records. How can I do it using only Microsoft Sql server?)
--------------
I'm forgot to write, I'm using MsSql 2005.

View 7 Replies View Related

Outlying Records Between Two Tables

Nov 15, 2006

I have two tables that have a common column (ID). Now, what i am trying to do is find what is not in one table that is in the other.

For instance:

Table A
ID NAME
1 Tom
2 George
3 Richard

Table B
ID NAME
1 Tom
3 Richard
4 Kevin

With this information, I am trying to write a query that would tell me that Kevin is the only record that doesn't exist in both tables ... like an outlier.

I have tried using something like the following:

SELECT distinct id, name
FROM Table 1 INNER JOIN Table 2
ON Table 1.id <> Table 2.id

Any help would be great. Thanks!

-L

View 3 Replies View Related

SQL Select Records NOT In Both Tables

Aug 7, 2005

HiI'm using Access 2002. I have 2 tables tblGroupContact,tblGroupPermission, both have 2 fields identical structure:ContactID GroupID (Both are Composite keys and both hold integers)tblGroupContact holds everybody and the groups they are members of.tblGroupPermission holds only those people who have permission to makechanges to another part of the DB.The SQL at the end of post works, but opens a dialogue box looking fora parameter value 'query1.ContactID'Clicking enter or cancel (without entering anything) works OK.What have I done wrong to cause this parameter request.Thanks ColinKSELECT tblGroupContact.ContactID, tblGroupContact.GroupIDFROM tblGroupContact LEFT JOIN tblGroupPermission ON(tblGroupContact.ContactID = tblGroupPermission.ContactID) AND(tblGroupContact.GroupID = tblGroupPermission.GroupID)WHERE (((tblGroupPermission.ContactI*D) Is Null) AND((tblGroupPermission.GroupID) Is Null));

View 3 Replies View Related







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