Can i create a table/index that spans multilple FGs such as
CREATE TABLE T1
( cola int PRIMARY KEY,
colb char(8) )
ON FG1,FG2,FG3Are you confused between files and filegroups? Have a read in bol about them.
The answer to the question you have posed is no - you have a clustered index
which resides on the data filegroup.
But I don't think it's the question you wanted to ask.
"Hassan" wrote:
> Can i create a table/index that spans multilple FGs such as
> CREATE TABLE T1
> ( cola int PRIMARY KEY,
> colb char(8) )
> ON FG1,FG2,FG3
>
>|||As Nigel says, it isn't possible. Why do you ask? The purpose of
filegroups is to provide a logical entity on which to place data. The
PHYSICAL placement of data is determined by the location of files,
rather than filegroups. So it should be possible to achieve whatever
configuration you need using a single filegroup per object.
--
David Portas
SQL Server MVP
--
Showing posts with label char. Show all posts
Showing posts with label char. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
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
-mvsWhat 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@.microsoft.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:
[vbcol=seagreen]
> 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:
[vbcol=seagreen]
> 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@.microsoft.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@.microsoft.com...
>
>
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
-mvsWhat 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@.microsoft.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:
[vbcol=seagreen]
> 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:
[vbcol=seagreen]
> 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@.microsoft.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@.microsoft.com...
>
>
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 and
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 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
>
|||A clustered index *is* the table (the leaf level of the index are the data pages). 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@.microsoft.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
>
|||Hi,
I tried it, it is same problem.
mvs
"SQL" wrote:
[vbcol=seagreen]
> 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:
[vbcol=seagreen]
> 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@.microsoft.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 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@.microsoft.com...
>
>
sql
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
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 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
>
|||A clustered index *is* the table (the leaf level of the index are the data pages). 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@.microsoft.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
>
|||Hi,
I tried it, it is same problem.
mvs
"SQL" wrote:
[vbcol=seagreen]
> 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:
[vbcol=seagreen]
> 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@.microsoft.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 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@.microsoft.com...
>
>
sql
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 and
Index is getting created in TEST1.
Any help on this is really appreciated.
Thank you
-mvsWhat 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 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
>|||A clustered index *is* the table (the leaf level of the index are the data pages). 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@.microsoft.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
>|||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:
> > 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
> >|||My bad I didn't realize it was a clustered index
"mvs" wrote:
> 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:
> >
> > > 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
> > >|||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@.microsoft.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 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@.microsoft.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
> >
>
>
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
-mvsWhat 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 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
>|||A clustered index *is* the table (the leaf level of the index are the data pages). 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@.microsoft.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
>|||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:
> > 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
> >|||My bad I didn't realize it was a clustered index
"mvs" wrote:
> 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:
> >
> > > 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
> > >|||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@.microsoft.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 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@.microsoft.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
> >
>
>
Subscribe to:
Posts (Atom)