creating unique index and clustered index in sql server management studio

Hello,
I have run across a very strange situation.
If i use the management studio to change an index that is clustered is does
not actually change the index. It shows up in the gui as changed, but the
data is not changed when returning the data.
Also when you use the createto> when right clicking on the table, none of
the indexes or index constraints are recreated.
In order to make the changes work you have to manually create the
constraints:
ie
ALTER TABLE dbo._messages_initiated_from ADD CONSTRAINT
IX_key_messages_initiated_from UNIQUE CLUSTERED
(
key_messages_initiated_from
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_initiated_from ON dbo._messages_initiated_from
(
initiated_from
) ON [PRIMARY]
Then the changes work and the code is genereted in the scripts.
This used to work in enterprise manager in sql 2000.
Any ideas why this is not functioning right?
Thanks
gary
See in-line:
Andrew J. Kelly SQL MVP
"gary" <garyseven7@.nospam.nospam> wrote in message
news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have run across a very strange situation.
> If i use the management studio to change an index that is clustered is
> does not actually change the index. It shows up in the gui as changed,
> but the data is not changed when returning the data.
The order in which data is returned is never guarenteed unless you use an
ORDER BY clause. This has always been the case and is nothing new. Without
an ORDER BY clause SQL Server is free to return the rows in what ever order
it seems most appropriate. Often times when you have a clustered index on a
table and issue a SELECT without a WHERE clause it will return the data in
mostly clustered index order. But this has never been guarenteed and should
not rely on that behaviour.

> Also when you use the createto> when right clicking on the table, none of
> the indexes or index constraints are recreated.
> In order to make the changes work you have to manually create the
> constraints:
If you want all the Indexes, constraints etc. you should use the Generate
Scripts wizard found by right clicking on the DB under Tasks. There you can
pick and choose all objects or none.

> ie
> ALTER TABLE dbo._messages_initiated_from ADD CONSTRAINT
> IX_key_messages_initiated_from UNIQUE CLUSTERED
> (
> key_messages_initiated_from
> ) ON [PRIMARY]
> CREATE NONCLUSTERED INDEX IX_initiated_from ON
> dbo._messages_initiated_from
> (
> initiated_from
> ) ON [PRIMARY]
> Then the changes work and the code is genereted in the scripts.
> This used to work in enterprise manager in sql 2000.
> Any ideas why this is not functioning right?
>
> Thanks
> gary
>
|||This response does not completely answer the question. First off, if the
index is clustered, the data is returned in the order of the records, which
is the order of the cluster. That is the definition.
Second of all, if you right click on the table, and select createto> to
create the script, absolutely none of the indexes that you created manually
other than the primary index appear in the script.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||I mean, when you right click on a table and select "script table to -->
create to--> the code to create the other indexes is not created.
if you were to create the indexes as constraints as the previous version did
in the enterprise manager then the script to works.
Also, on clustered indexes created with a constraint the unique setting is
automatically yes and can't be changed (it is disabled). In the new sql
console you can create a clustered index and then mark it as non-unique,
which just does not make sense as the clustered index is what all the other
indexes point to.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||Ok, i guess i can assume the indexes are being created differently. But it
still does not make sense that when the tables are scripted that the script
to create the new indexes are not showing up.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||No, that's not the definition of a clustered index. The rows a STORED in
the order of the clustered index. They're returned in whatever order SQL
feels like if you don't specify an ORDER BY.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gary" <garyseven7@.nospam.nospam> wrote in message
news:eyTC0jYDGHA.740@.TK2MSFTNGP12.phx.gbl...
> This response does not completely answer the question. First off, if the
> index is clustered, the data is returned in the order of the records,
> which is the order of the cluster. That is the definition.
> Second of all, if you right click on the table, and select createto> to
> create the script, absolutely none of the indexes that you created
> manually other than the primary index appear in the script.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
|||gary (garyseven7@.nospam.nospam) writes:
> This response does not completely answer the question. First off, if
> the index is clustered, the data is returned in the order of the
> records, which is the order of the cluster. That is the definition.
NO!
The fact that index is clustered means that the data is at the leaf
pages of the index. Thus, data is physically sorted according to the
clustered index.
But SQL does not bother about physical storage. If you say:
SELECT ... FROM tbl WHERE ...
without an ORDER BY clause, the DB engine is free to return the rows
in any order. For instance, if the WHERE clause includes a column on
which there is an non-clustered index, it is likely that that the
output will follow that index. If you say "SELECT * FROM bigtable",
SQL Server may open parallel streams, and run from different parts of
the table, at the same time.
In a relational datbase, there is only one way get data sorted in a
certain order, and that is ORDER BY.

> Second of all, if you right click on the table, and select createto> to
> create the script, absolutely none of the indexes that you created
> manually other than the primary index appear in the script.
Yes. Andrew addressed this in his post:
[vbcol=seagreen]

> Also, on clustered indexes created with a constraint the unique setting
> is automatically yes and can't be changed (it is disabled). In the new
> sql console you can create a clustered index and then mark it as
> non-unique, which just does not make sense as the clustered index is
> what all the other indexes point to.
What happens when you define a clustered index as non-unique, is that
SQL Server adds a four-byte "uniquifier". It has been this way since
SQL 7. (And all SQL Server versions have permitted non-uniuqe clustered
indexes.)

> Ok, i guess i can assume the indexes are being created differently. But
> it still does not make sense that when the tables are scripted that the
> script to create the new indexes are not showing up.
On http://lab.msdn.microsoft.com/produc...k/default.aspx you
can enter suggestions for improvments in SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Thanks for all your help.
It is kind of odd that the script table does not generate the indexes and
the script database that is in the wizard is capable of generating the
sciprts for the index (it doesn't generate them by default, you have to
select the script indexes to be true).
SQL 2000 GUI created the indexes by creating a constraint while the new SQL
2005 GUI creates them using the create index statement. If created as a
constraint the alter statement ins generated in the create table, if created
in a create index statement it is not.
This seems somewhat consistantly inconsistant so to speak.
Thanks for helping clarify the issue.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||Yes I agree that it is misleading and not a very good design that it leaves
out a bunch of objects. Its really only useful for a quick look at the DDL
of the table itself. Anything more should be done thru the Generate Scripts
task.
Andrew J. Kelly SQL MVP
"gary" <garyseven7@.nospam.nospam> wrote in message
news:OaEMUSaDGHA.4052@.TK2MSFTNGP10.phx.gbl...
> Thanks for all your help.
> It is kind of odd that the script table does not generate the indexes and
> the script database that is in the wizard is capable of generating the
> sciprts for the index (it doesn't generate them by default, you have to
> select the script indexes to be true).
> SQL 2000 GUI created the indexes by creating a constraint while the new
> SQL 2005 GUI creates them using the create index statement. If created
> as a constraint the alter statement ins generated in the create table, if
> created in a create index statement it is not.
> This seems somewhat consistantly inconsistant so to speak.
> Thanks for helping clarify the issue.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
sql

Creating small index takes LONG time!

Hi- I am trying to add a simple, non clustered index, using Enterprise
Manager all default settings, to a table with about 30 columns and about 15K
rows of data. The index is on a smalldatetime column. For some reason, when
I
try to add this index, it takes FOREVER (let it go at least 6 hours, still
not completed). Killed process (which had very high processor count),
restarted SS, tried again, no go. No sign of any locks (in fact, no one even
connected to this database). Plenty of disk space.
Any ideas why this might occur? There are several other indexes on the
table, including primary key id column and other indexes referencing the
column I am trying to index as secondary, tertiary column, but never as
primary column. This is a large database with many tables (100+), lots of
data (4GB or so). But, this seems unusual to me...
I can access the table and the data in it from another instance of EM while
I am trying to create this index.
Any ideas?
Thanks,
ChrisEM is notorious for generating inefficient code. It's far better to script
it by hand because:
1) a skilled human will write better code
2) the script can be used elsewhere and be checked into source code control
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
Hi- I am trying to add a simple, non clustered index, using Enterprise
Manager all default settings, to a table with about 30 columns and about 15K
rows of data. The index is on a smalldatetime column. For some reason, when
I
try to add this index, it takes FOREVER (let it go at least 6 hours, still
not completed). Killed process (which had very high processor count),
restarted SS, tried again, no go. No sign of any locks (in fact, no one even
connected to this database). Plenty of disk space.
Any ideas why this might occur? There are several other indexes on the
table, including primary key id column and other indexes referencing the
column I am trying to index as secondary, tertiary column, but never as
primary column. This is a large database with many tables (100+), lots of
data (4GB or so). But, this seems unusual to me...
I can access the table and the data in it from another instance of EM while
I am trying to create this index.
Any ideas?
Thanks,
Chris|||Chris,
Either it is not building the index, or something is seriously wrong.
An index like you describe should be built in seconds.
When I just created an index using Enterprise Manager (within Design
Table) it did not start the process until I closed the Design Table
window, when it prompted me with the question asking if I wanted to
save changes. Did you reach that point?
Roy
On Mon, 27 Feb 2006 17:09:27 -0800, querylous
<querylous@.discussions.microsoft.com> wrote:

>Hi- I am trying to add a simple, non clustered index, using Enterprise
>Manager all default settings, to a table with about 30 columns and about 15
K
>rows of data. The index is on a smalldatetime column. For some reason, when
I
>try to add this index, it takes FOREVER (let it go at least 6 hours, still
>not completed). Killed process (which had very high processor count),
>restarted SS, tried again, no go. No sign of any locks (in fact, no one eve
n
>connected to this database). Plenty of disk space.
>Any ideas why this might occur? There are several other indexes on the
>table, including primary key id column and other indexes referencing the
>column I am trying to index as secondary, tertiary column, but never as
>primary column. This is a large database with many tables (100+), lots of
>data (4GB or so). But, this seems unusual to me...
>I can access the table and the data in it from another instance of EM while
>I am trying to create this index.
>Any ideas?
>Thanks,
>Chris|||Hmmm... looking at the process info, I can see that the query generated by
Enterprise Manager is
CREATE
INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
I can't imagine a much more efficient means of creating an index than that?
Thanks,
Chris
"Tom Moreau" wrote:

> EM is notorious for generating inefficient code. It's far better to scrip
t
> it by hand because:
> 1) a skilled human will write better code
> 2) the script can be used elsewhere and be checked into source code contro
l
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
> Hi- I am trying to add a simple, non clustered index, using Enterprise
> Manager all default settings, to a table with about 30 columns and about 1
5K
> rows of data. The index is on a smalldatetime column. For some reason, whe
n
> I
> try to add this index, it takes FOREVER (let it go at least 6 hours, still
> not completed). Killed process (which had very high processor count),
> restarted SS, tried again, no go. No sign of any locks (in fact, no one ev
en
> connected to this database). Plenty of disk space.
> Any ideas why this might occur? There are several other indexes on the
> table, including primary key id column and other indexes referencing the
> column I am trying to index as secondary, tertiary column, but never as
> primary column. This is a large database with many tables (100+), lots of
> data (4GB or so). But, this seems unusual to me...
> I can access the table and the data in it from another instance of EM whil
e
> I am trying to create this index.
> Any ideas?
> Thanks,
> Chris
>|||Are there any processes blocking it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
Hmmm... looking at the process info, I can see that the query generated by
Enterprise Manager is
CREATE
INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
I can't imagine a much more efficient means of creating an index than that?
Thanks,
Chris
"Tom Moreau" wrote:

> EM is notorious for generating inefficient code. It's far better to
> script
> it by hand because:
> 1) a skilled human will write better code
> 2) the script can be used elsewhere and be checked into source code
> control
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
> Hi- I am trying to add a simple, non clustered index, using Enterprise
> Manager all default settings, to a table with about 30 columns and about
> 15K
> rows of data. The index is on a smalldatetime column. For some reason,
> when
> I
> try to add this index, it takes FOREVER (let it go at least 6 hours, still
> not completed). Killed process (which had very high processor count),
> restarted SS, tried again, no go. No sign of any locks (in fact, no one
> even
> connected to this database). Plenty of disk space.
> Any ideas why this might occur? There are several other indexes on the
> table, including primary key id column and other indexes referencing the
> column I am trying to index as secondary, tertiary column, but never as
> primary column. This is a large database with many tables (100+), lots of
> data (4GB or so). But, this seems unusual to me...
> I can access the table and the data in it from another instance of EM
> while
> I am trying to create this index.
> Any ideas?
> Thanks,
> Chris
>|||Nope, neither blocking nor blocked by...
"Tom Moreau" wrote:

> Are there any processes blocking it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
> Hmmm... looking at the process info, I can see that the query generated by
> Enterprise Manager is
> CREATE
> INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
> I can't imagine a much more efficient means of creating an index than that
?
> Thanks,
> Chris
> "Tom Moreau" wrote:
>
>|||I'm actually using the "manage indexes" window; after specifying the index,
I
click ok, and then another window appears which implies that the index is
being built, and this is where the hang occurs. Also, I know the request has
been sent at that point b/c I can see it as a process. Yes, something is
definitely wrong; I am also used to indexes like this being built in
seconds...
"Roy Harvey" wrote:

> Chris,
> Either it is not building the index, or something is seriously wrong.
> An index like you describe should be built in seconds.
> When I just created an index using Enterprise Manager (within Design
> Table) it did not start the process until I closed the Design Table
> window, when it prompted me with the question asking if I wanted to
> save changes. Did you reach that point?
> Roy
>
> On Mon, 27 Feb 2006 17:09:27 -0800, querylous
> <querylous@.discussions.microsoft.com> wrote:
>
>|||On Mon, 27 Feb 2006 17:46:26 -0800, querylous
<querylous@.discussions.microsoft.com> wrote:

>I'm actually using the "manage indexes" window; after specifying the index,
I
>click ok, and then another window appears which implies that the index is
>being built, and this is where the hang occurs. Also, I know the request ha
s
>been sent at that point b/c I can see it as a process. Yes, something is
>definitely wrong; I am also used to indexes like this being built in
>seconds...
So what happens if you run the same CREATE INDEX in Query Analyzer?
Roy Harvey
Beacon Falls, CT|||Could you please give us the SQL edition, version number, hardware, etc.?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:90639871-203F-4402-ADC0-D5D583E5BAB0@.microsoft.com...
Nope, neither blocking nor blocked by...
"Tom Moreau" wrote:

> Are there any processes blocking it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
> Hmmm... looking at the process info, I can see that the query generated by
> Enterprise Manager is
> CREATE
> INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
> I can't imagine a much more efficient means of creating an index than
> that?
> Thanks,
> Chris
> "Tom Moreau" wrote:
>
>|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows N
T
5.0 (Build 2195: Service Pack 4)
Never had any trouble with it.
"Tom Moreau" wrote:

> Could you please give us the SQL edition, version number, hardware, etc.?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:90639871-203F-4402-ADC0-D5D583E5BAB0@.microsoft.com...
> Nope, neither blocking nor blocked by...
> "Tom Moreau" wrote:
>
>

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
Griff
When indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
GriffWhen indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
GriffWhen indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>