clustered Index on a table

I have a question,
whenever a table has a primary key created, a clustered index created on
those fieldes.since one table can only have one clustered index, if a table
has primary key already, you will never have a new clustered index created on
that table.
Thanks.> whenever a table has a primary key created, a clustered index created on
> those fieldes.
That is only the default index type. You can override that. Many dba does, as you often have a
better candidate for your clustered index than the PK.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:364792AD-0BA5-49D9-B4BE-A60B49FB33A5@.microsoft.com...
>I have a question,
> whenever a table has a primary key created, a clustered index created on
> those fieldes.since one table can only have one clustered index, if a table
> has primary key already, you will never have a new clustered index created on
> that table.
> Thanks.
>|||You can only have one clustered index but it doesn't have to be the
primary key. That's just the default setting but there's nothing to
stop you changing it.
--
David Portas
SQL Server MVP
--|||A primary key index can be either clustered or non-clustered. If you don't
specify clustered or non-clustered, the primary key will be clustered if no
existing clustered index exists. The PK index will be non-clustered if a
clustered index already exists on the table.
It's best to create your clustered index first (primary key, unique
constraint or clustered index) and then add the non-clustered indexes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:364792AD-0BA5-49D9-B4BE-A60B49FB33A5@.microsoft.com...
>I have a question,
> whenever a table has a primary key created, a clustered index created on
> those fieldes.since one table can only have one clustered index, if a
> table
> has primary key already, you will never have a new clustered index created
> on
> that table.
> Thanks.
>|||Thank you guys, these are really helpful.
I have 6 base tables with couple millions records in each of them. I need to
report off these tables. I have 3 views( table tables union together) created
first , then I have a new view created with these 3 views and other 2 tables
. Since the outer joins , views and high volumes data in the table may affect
the performance, what are the best ways to index on the tables ?
Thanks s lot.
> I have a question,
> whenever a table has a primary key created, a clustered index created on
> those fieldes.since one table can only have one clustered index, if a table
> has primary key already, you will never have a new clustered index created on
> that table.
> Thanks.
>|||Large tables are less forgiving than small ones when it comes to
performance. The best index candidates are columns specified in JOIN and
WHERE clauses. This will allow the optimizer to develop a query plan that
filters data efficiently and can take advantage of index ordering. Examine
your query execution plans to ensure indexes are used efficiently.
Check out the Designing an index topic in the Books Online
(createdb.chm::/cm_8_des_05_2ri0.htm). The topic also describes clustered
vs. non-clustered index considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:BB0F9881-DFD7-4BB7-90F6-E8E788E7F406@.microsoft.com...
> Thank you guys, these are really helpful.
> I have 6 base tables with couple millions records in each of them. I need
> to
> report off these tables. I have 3 views( table tables union together)
> created
> first , then I have a new view created with these 3 views and other 2
> tables
> . Since the outer joins , views and high volumes data in the table may
> affect
> the performance, what are the best ways to index on the tables ?
> Thanks s lot.
>
>> I have a question,
>> whenever a table has a primary key created, a clustered index created on
>> those fieldes.since one table can only have one clustered index, if a
>> table
>> has primary key already, you will never have a new clustered index
>> created on
>> that table.
>> Thanks.