November 8, 2004 at 7:14 pm
Do composite key constraints automatically create clustered indexes?
November 8, 2004 at 8:27 pm
No. Each table can (by definition) have only one clustered index - and whether that is on the table's primary key field or on some other field is down to the table designer.
However, a PK (composite or not) is created as a clustered index by default in EM - unless you have already defined another index on the table as clustered.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 9, 2004 at 12:58 am
However, a PK (composite or not) is created as a clustered index by default in EM - unless you have already defined another index on the table as clustered.
Well, not only in EM, but generally in SQL Server. Try this:
CREATE TABLE a
(
c1 INT PRIMARY KEY
)
GO
SELECT
CASE OBJECTPROPERTY(OBJECT_ID('a'),'TableHasClustIndex')
WHEN 1 THEN 'Yes' ELSE 'No' END
DROP TABLE a
----
Yes
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 12, 2004 at 11:43 am
so now my question is:
Do composite key constraints automatically create non-clustered indexes?
November 12, 2004 at 1:00 pm
When you create an index on more than one column, you automatically have a composite index. And this one can either be on a non-clustered or on a clustered index.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 12, 2004 at 1:30 pm
No. Composite keys are not automatically clustered. If you use EM, then you have to select if that particular index is clustered. In QA, you should know from your script whether the keys are clustered or not.
Quand on parle du loup, on en voit la queue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply