|
Home > Archive > MS SQL Server > July 2005 > clustered Index on a table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
clustered Index on a table
|
|
| Catelin Wang 2005-07-11, 11:23 am |
| 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.
| |
| Tibor Karaszi 2005-07-11, 11:23 am |
| > 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@discussi
ons.microsoft.com> wrote in message
news:364792AD-0BA5-49D9-B4BE- A60B49FB33A5@microso
ft.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.
>
| |
| David Portas 2005-07-11, 11:23 am |
| 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
--
| |
| Dan Guzman 2005-07-11, 11:23 am |
| 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@discussi
ons.microsoft.com> wrote in message
news:364792AD-0BA5-49D9-B4BE- A60B49FB33A5@microso
ft.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.
>
| |
| Catelin Wang 2005-07-11, 1:23 pm |
| 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.
>
| |
| Dan Guzman 2005-07-12, 11:23 am |
| 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@discussi
ons.microsoft.com> wrote in message
news:BB0F9881-DFD7-4BB7-90F6- E8E788E7F406@microso
ft.com...[color=darkred]
> 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.
>
>
|
|
|
|
|