non-clusted index and space

Hi I understand clustered and non-clustered indexes
increase the amount of disk space used i have a table that
is 200mb and wondered if I added a non-clsted index how
much extra space that would use and is it added to the
table size or somewhere else.
thanks for any help
MikeyMikey:
As for how much space an index will use, a lot depends on the type and
number of columns in the index. If you
EXEC sp_spaceused 'tablename'
before and after you create the index you can get a pretty good idea
of how much space the index requires.
Non clustered indexes would not change the size of the table, while a
clustered index 'becomes' the table.
HTH,
Scott
http://www.OdeToCode.com
On Thu, 8 Jan 2004 05:24:01 -0800, "Mikey"
<anonymous@.discussions.microsoft.com> wrote:

quote:

---

>Hi I understand clustered and non-clustered indexes
>increase the amount of disk space used i have a table that
>is 200mb and wondered if I added a non-clsted index how
>much extra space that would use and is it added to the
>table size or somewhere else.
>thanks for any help
>Mikey

---

Non Clustered Effect

Hi ,
When I finish to create one non clustering index on of of my table , I
found out that the size of the hard disk increase amazingly.
I decide to drop the this index but the hard disk size did not back to
original size before that. What should I do next ? Please help
Travis Tan
Travis wrote:
> Hi ,
> When I finish to create one non clustering index on of of my table
> , I found out that the size of the hard disk increase amazingly.
> I decide to drop the this index but the hard disk size did not back
> to original size before that. What should I do next ? Please help
It's possible to configure automatic grow for database files and in this
way, when a db need space for an object (table or index) it take from os. If
you delete an object the space previously allocated won't be shrunk (unless
you set auto_shrink option but it's better to avoid this).
You may use DBCC SHRINKDATABASE or, better, DBCC SHRINKFILE to reduce the
space used by a database. See BOL for more details and for sintax of DBCC
commands.
Bye
PS: "clustering" in the name of this newsgroup means "Clustering technology"
and not as clustered (or not clustered) index...
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com