Re: creating unique index and clustered index in sql server management studio
- From: "Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx>
- Date: Fri, 30 Dec 2005 21:48:58 -0500
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@xxxxxxxxxxxxx> wrote in message
news:OaEMUSaDGHA.4052@xxxxxxxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxx> wrote in message
> news:e4Bl$HYDGHA.2704@xxxxxxxxxxxxxxxxxxxxxxx
>> See in-line:
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>>
>> "gary" <garyseven7@xxxxxxxxxxxxx> wrote in message
>> news:%23NRW54XDGHA.3444@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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
>>>
>>>
>>
>>
>
>
.
- References:
- Prev by Date: Re: Query Analyzer won't let me edit grid data in 2000
- Next by Date: Re: mgmt studio-View user permissions?
- Previous by thread: Re: creating unique index and clustered index in sql server management studio
- Next by thread: Re: SQL Server 2005 and Express 2005 BOL
- Index(es):
Relevant Pages
|
