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