Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    Rio de Janeiro, Brazil
    Posts
    20

    Unanswered: Strange Behavior

    Hi Everybody,


    could someone explain to me the following behavior:

    The database has 3 datafiles and 3 filegroups with 1 datafiles for each filegroup.

    One for system tables, one for test table and one for the indexes of test table.

    i have created table test (id int identity (1,1), name varchar(50) not null) stored on test table filegoup.

    then i run a script to insert 6.000.000 rows, at the end the datafile for the table test has 130Mb.

    after this, i have created 2 indexes, one clustered and one nonclustered stored on the filegroup for the indexes.

    so, what was my surprise that the size of the datafile for the table test was reduced to 1Mb, and the size of the index file has 312Mb.

    thanks u all...


    Leandro.

  2. #2
    Join Date
    May 2002
    Posts
    299
    Take a look at "Using Clustered Indexes" in sql book online. If you have access to Kalen's Inside SQL Server 2k book, take a look at chapter 8.
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    A clustered index is an index built on a table where the leaf pages of the index are the data pages of the table. So infact a clustered index is index and data, where as a non-clustered index is seperate from the table. A non-clustered index's leaf pages are pointers to the data.

    So when you created the clustered index on another file group, you actually moved the data over as well.
    MCDBA

  4. #4
    Join Date
    Sep 2002
    Location
    Rio de Janeiro, Brazil
    Posts
    20
    thanks u all.

    i´ll continue studing this.
    Oracle 9i & SQL Server 2000 - DBA
    Rio de janeiro - Brazil
    lsantos.rj@globo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •