Create table and Index

Hello,
I am using SQL server 2000 with SP4. I am running create table and index as
below

CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST

CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
ON TEST1

I am trying to create table in TEST file group and Index in TEST1 file
group. But table and index are both getting created in TEST1 file group. If 
I
execute just Creat Table statement alone, then the table is getting created
in TEST file group, but if I execute both statements together, the table  an
d
Index is getting created in TEST1.

Any help on this is really appreciated.
Thank you
-mvs

What happens if you replace it with this?
CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST

GO

CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
ON TEST1

http://sqlservercode.blogspot.com/


"mvs" wrote:

> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index a
s
> below
>
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
>
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg
)
> ON TEST1
>
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group. I
f I
> execute just Creat Table statement alone, then the table is getting create
d
> in TEST file group, but if I execute both statements together, the table  
and
> Index is getting created in TEST1.
>
> Any help on this is really appreciated.
> Thank you
> -mvs
>

A clustered index *is* the table (the leaf level of the index are the data p
ages). Hence, you cannot
separate a clustered index from the data pages, by definition.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"mvs" <mvs@discussions.microsoft.com> wrote in message
news:0C8E4E4B-C6D1-4294-9A64- D4A4B3F4161D@microso
ft.com...
> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index a
s
> below
>
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
>
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg
)
> ON TEST1
>
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group. I
f I
> execute just Creat Table statement alone, then the table is getting create
d
> in TEST file group, but if I execute both statements together, the table  
and
> Index is getting created in TEST1.
>
> Any help on this is really appreciated.
> Thank you
> -mvs
>

Hi,
I tried it, it is same problem.
--
mvs


"SQL" wrote:

> What happens if you replace it with this?
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
>
> GO
>
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg
)
> ON TEST1
>
> http://sqlservercode.blogspot.com/
>
>
> "mvs" wrote:
> 

My bad I didn't realize it was a clustered index

"mvs" wrote:

> Hi,
> I tried it, it is same problem.
> --
> mvs
>
>
> "SQL" wrote:
> 

MVS,

Table data by default is stored in a heap structure (unsorted set of data
pages).  When you create a clustered index on the table the data is copied
to a contiguous set of pages and physically sorted based on the clustered
index key.  Hence you either have a heap structure for a table (INDID = 0
sysindexes) OR a clustered index (INDID = 1 sysindexes) but not both.  So
your first statement does create the table ON TEST and your second statement
moves the data, sorts it ON TEST1.  So if you want the data to be on TEST
use ON TEST in both statement conversely if you want the data to be on TEST1
use ON TEST! in both statements.  That being said your nonclustered indexes
can exist on different filegroup than the clustered index (table).

HTH

Jerry
"mvs" <mvs@discussions.microsoft.com> wrote in message
news:0C8E4E4B-C6D1-4294-9A64- D4A4B3F4161D@microso
ft.com...
> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index
> as
> below
>
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
>
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777
> (t_creg)
> ON TEST1
>
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group.
> If I
> execute just Creat Table statement alone, then the table is getting
> created
> in TEST file group, but if I execute both statements together, the table
> and
> Index is getting created in TEST1.
>
> Any help on this is really appreciated.
> Thank you
> -mvs
>

Thank you all. Now it really helped me.
--
mvs


"Jerry Spivey" wrote:

> MVS,
>
> Table data by default is stored in a heap structure (unsorted set of data
> pages).  When you create a clustered index on the table the data is copied
> to a contiguous set of pages and physically sorted based on the clustered
> index key.  Hence you either have a heap structure for a table (INDID = 0
> sysindexes) OR a clustered index (INDID = 1 sysindexes) but not both.  So
> your first statement does create the table ON TEST and your second stateme
nt
> moves the data, sorts it ON TEST1.  So if you want the data to be on TEST
> use ON TEST in both statement conversely if you want the data to be on TES
T1
> use ON TEST! in both statements.  That being said your nonclustered indexe
s
> can exist on different filegroup than the clustered index (table).
>
> HTH
>
> Jerry
> "mvs" <mvs@discussions.microsoft.com> wrote in message
> news:0C8E4E4B-C6D1-4294-9A64- D4A4B3F4161D@microso
ft.com... 
>
>
>