Re: Size of SQLServer databases with indices

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Erland Sommarskog (esquel_at_sommarskog.se)
Date: 07/23/04 

Date: Fri, 23 Jul 2004 22:13:07 +0000 (UTC)

Bart Torbert (barthome1@comcast.net) writes:
> I am starting to examine using SQLServer instead of Oracle. I went
> through the SQLServer import utility to copy tables from Oracle into
> SQLServer. I then checked the size of the database.
>
> I then started to specify indices for the tables. I did not see the
> database file size grow all that much. I kept on using the shrink
> command to make sure that wasted space was removed. I even made sure
> that minimal free space was allowed.
>
> I was rather expecting (from experience with Oracle) that there would
> be a large growth in the database when addind indices. So what did I
> miss? Are the indices stored elsewhere and I missed them? Does
> SQLServer handle indices differently so they dont' bloat the database
> size?

I cannot compare with Oracle, since I don't have any experience of
Oracle.

In SQL Server, yes, indexes take up space. But it depends a little on
what sort of index you add. A clustered index, has the data as its
leaf pages, which means that only the index tree itself takes up space.

To find out how much space you use on indexes for a certain table, you
can do:

   sp_spaceused tbl, TRUE

(The second parameter forces an update of usage data.)

To see the overall database size, it's better to use sp_spaceused with
NULL as the first parameter. The second result set, gives you four
numbers:

reserved: this how much space that has been reserved for the table.

data: data + clustered index if there is one.

index: non-clustered indexes.

unused: reserved space, which is not occupied by data or indexes yet.
Since SQL Server allocates space in extents of eight pages at a time,
there is always some unused space. But with heavy fragmentation, this
value can grow.

reserved = data + indexes + unused. 

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Relevant Pages

  • Re: Why not Access...?
    ... Next, I DID NOT SAY Oracle was considered a "best choice," only a sufficient ... It is a desktop application and the database in also on the same machine. ... I also want to know why Oracle is considered a best choice as compared to SQL Server. ... > Also, you need to answer the question regarding whether or not each client> will be using an individual database, and individual copy of a central> organization database, or making straight calls to a centralized ...
    (microsoft.public.sqlserver.server)
  • Re: Merge/Replication or Syncing with Oracle
    ... You can't directly access an Access database on the desktop from a WM 5 app, but you can sync between SQL Compact on the WM device and Access using the Access Syncronizer: ... As for the Oracle issue ... ... Where SQL Compact Edition easily does merge/replication to SQL Server using IIS ... ...
    (microsoft.public.sqlserver.ce)
  • Re: Oracle vs SQL Server as a back end for Access?
    ... post -- comparing back end database engines or replacing the front end ... say that Oracle 10g is better than SQL Server 2000 without determining ... whether or not the total cost of ownership of 10g over SQL Server ... developer than Oracle 10g, in spite of its new web interface. ...
    (comp.databases.ms-access)
  • Re: Oracle licence question
    ... And no - it does not freeze anything, the backups DO NOT affect connections ... SQL Server MVP ... freeze the database and send it to the target. ... What I really wanted is something that lists features I get in Oracle ...
    (comp.databases.oracle.server)
  • Re: ADO.NET and SQL, Oracle
    ... > off using SQL Server instead. ... > company's database from Oracle to SQL Server as that may be a huge ... download, but it contains the client as well). ... an open source db like Firebird or postgresql (and not sqlserver!). ...
    (microsoft.public.dotnet.framework.adonet)