|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
creating unique index and clustered index in sql server management studioI 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 The order in which data is returned is never guarenteed unless you use an 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. 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 If you want all the Indexes, constraints etc. you should use the Generate > the indexes or index constraints are recreated. > > In order to make the changes work you have to manually create the > constraints: Scripts wizard found by right clicking on the DB under Tasks. There you can pick and choose all objects or none. Show quote > 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. Show quote "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... >> 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 >> >> > > 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. -- Show quoteThis 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... >> 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 >>> >>> >> >> > > gary (garyseven7@nospam.nospam) writes:
> This response does not completely answer the question. First off, if NO!> 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. 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 Yes. Andrew addressed this in his post:> create the script, absolutely none of the indexes that you created > manually other than the primary index appear in the script. >> If you want all the Indexes, constraints etc. you should use the Generate What happens when you define a clustered index as non-unique, is that>> Scripts wizard found by right clicking on the DB under Tasks. There you >> can pick and choose all objects or none. > 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. 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 On http://lab.msdn.microsoft.com/productfeedback/default.aspx you> it still does not make sense that when the tables are scripted that the > script to create the new indexes are not showing up. can enter suggestions for improvments in SQL Server. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 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. Show quote "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... >> 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 >> >> > > 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. Show quote "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... >> 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 >> >> > > 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. Show quote "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... >> 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 >> >> > > 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. -- Show quoteAndrew 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... >> 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 >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics