default clustered index when selecting primary key

Hi,
I have a list of table which I need to replicate to another database. First
of all, why do I need a primary key to achieve replication (sorry I am new
with replication)
Then I've noticed that if I just click the primary key button from EM on
Design table, then it defaults to a clustered index on the column I've
selected. Does anybody knwow why it defaults to clustered? I am not too kee
n
on clustered indexes for my database.
Thanks,
Panos.Panos Stavroulis. wrote:

> Hi,
> I have a list of table which I need to replicate to another database. Firs
t
> of all, why do I need a primary key to achieve replication (sorry I am new
> with replication)
Without a key the database can't uniquely identify a row in a table.
Every table should have a key. Why would you want a table without one?

> Then I've noticed that if I just click the primary key button from EM on
> Design table, then it defaults to a clustered index on the column I've
> selected. Does anybody knwow why it defaults to clustered? I am not too k
een
> on clustered indexes for my database.
I don't think there's a very good reason why it defaults to clustered.
You can easily change the setting so the default doesn't matter very
much. Also, I would not usually create indexes or keys in Enterprise
Manager. EM is very inefficient in the way it implements schema
changes. Usually it's better to write TSQL for your schema mods. EM
will even script the change for you to review if that helps you get
started.
Most of the time it does pay to have a clustered index on every table.
Tables without clustered indexes should be the exception rather than
the rule.
David Portas
SQL Server MVP
--|||> Does anybody knwow why it defaults to clustered?
A design decision MS made some 12 years ago. Perhaps because you can only ha
ve one CL IX on a table
and you can only have one PK for a table? Important to notice is that you ar
e free to override that
default.

> I am not too keen
> on clustered indexes for my database.
Why not? Clustered indexes are a very important performance tools, and most
DBAs avoid heap tables
and try to decide carefully which index to be the clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:E07ADE7B-5424-43E1-AE34-936AF21D27AC@.microsoft.com...
> Hi,
> I have a list of table which I need to replicate to another database. Firs
t
> of all, why do I need a primary key to achieve replication (sorry I am new
> with replication)
> Then I've noticed that if I just click the primary key button from EM on
> Design table, then it defaults to a clustered index on the column I've
> selected. Does anybody knwow why it defaults to clustered? I am not too k
een
> on clustered indexes for my database.
> Thanks,
> Panos.|||You are opening a good subject here. Well the reason I don't use clustered
indexes for many tables is because I don't need to do a select statements
where col_id between 10 and 200 etc if column col_id was my primary key.
I normally select individual records. In fact which way do you think it's
faster? if I have a non clustered index and select where col_id = 100 or a
clustered index?
Thanks.
"Tibor Karaszi" wrote:

> A design decision MS made some 12 years ago. Perhaps because you can only
have one CL IX on a table
> and you can only have one PK for a table? Important to notice is that you
are free to override that
> default.
>
> Why not? Clustered indexes are a very important performance tools, and mos
t DBAs avoid heap tables
> and try to decide carefully which index to be the clustered index.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:E07ADE7B-5424-43E1-AE34-936AF21D27AC@.microsoft.com...
>|||> I normally select individual records. In fact which way do you think it's
> faster? if I have a non clustered index and select where col_id = 100 or a
> clustered index?
A clustered index will be marginally faster.
But are you really saying that you have no range queries (lower selectivity
search arguments) at all
in the database? What about joins? Grouping? Sorting? Clustered indexes can
beneficial for a wide
type of operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:4AFF7C5A-C43F-4C27-AA5B-9A6F65DAAE6E@.microsoft.com...
> You are opening a good subject here. Well the reason I don't use clustered
> indexes for many tables is because I don't need to do a select statements
> where col_id between 10 and 200 etc if column col_id was my primary key.
> I normally select individual records. In fact which way do you think it's
> faster? if I have a non clustered index and select where col_id = 100 or a
> clustered index?
> Thanks.
> "Tibor Karaszi" wrote:
>|||I don't think you quite grasp the point of a clustered index.
I really don't feel like going into it right now, but let's look at your
example:
With a nonclustered index several index pages must be searched before
reaching the 100 value, while with a clustered index this is done in two
quick steps:
1) find the page where the 100 value resides; and
2) only go to that page.
ML
http://milambda.blogspot.com/|||The explanation that I remember best from an old DBA friend of mine is
that a clustered index is like page numbers in a book, whereas
non-clustered indexes are like the index in the back; although the
analogy is not a perfect fit, it does explain the relationship between
clustered and nonclustered indexes. Without page numbers in a
sequential order (clustering), it's tough to find the topic you're
looking for.
Stu|||Thanks for the answers. OK how about this.
We have a query that joins 2 tables, file & file_detail linked by column
col_id which is integer. If I was making a query to select all files and
their detail which are between 100 and 1000, then I would expect that this
query will be faster if I had a clustered index on the table.
However, I personally would expect if the query was "give me file 250" then
the non-clustered solution will be faster? Do you agree with this. How about
covering on non-clustered indexes, I would expect the non clustered option t
o
be faster. Thank you.
"Tibor Karaszi" wrote:

> A clustered index will be marginally faster.
> But are you really saying that you have no range queries (lower selectivit
y search arguments) at all
> in the database? What about joins? Grouping? Sorting? Clustered indexes ca
n beneficial for a wide
> type of operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:4AFF7C5A-C43F-4C27-AA5B-9A6F65DAAE6E@.microsoft.com...
>|||> We have a query that joins 2 tables, file & file_detail linked by column
> col_id which is integer. If I was making a query to select all files and
> their detail which are between 100 and 1000, then I would expect that this
> query will be faster if I had a clustered index on the table.
Cluster on which table and which column(s)? It is possible that a clustered
index o the fireign key
column in the file_detail table will improve that join, but that depends on
a lot of other factors.

> However, I personally would expect if the query was "give me file 250" the
n
> the non-clustered solution will be faster?
Non-clustered index on what? For a query with high-selectivity, a clustered
index on the search
column will still outperform a nonc-lustered (but the ihger selectivity, the
more marginally) as SQL
Server doesn't have to fetch each row in the datapage. Unless the non-cluste
red index covers the
query, of course.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:CE190BBB-A52C-4D06-9AA8-8E33D983B643@.microsoft.com...
> Thanks for the answers. OK how about this.
> We have a query that joins 2 tables, file & file_detail linked by column
> col_id which is integer. If I was making a query to select all files and
> their detail which are between 100 and 1000, then I would expect that this
> query will be faster if I had a clustered index on the table.
> However, I personally would expect if the query was "give me file 250" the
n
> the non-clustered solution will be faster? Do you agree with this. How abo
ut
> covering on non-clustered indexes, I would expect the non clustered option
to
> be faster. Thank you.
>
> "Tibor Karaszi" wrote:
>|||Sorry meant to say clustered index on col_id on both tables in one case and
nonclustered index on both tables in the second.
So basically basically since for most of the queries you need more
information (columns) than the columns contained in the index (covering
situation) then it's better to create a clustered index on the table as long
as the data arrive in the database in a sequential order and you don't get
page breaks.
Also do you have a view on clustered indexes on tables which are over 8K
long, ie maximum size of a page? Thanks.
"Tibor Karaszi" wrote:

> Cluster on which table and which column(s)? It is possible that a clustere
d index o the fireign key
> column in the file_detail table will improve that join, but that depends o
n a lot of other factors.
>
> Non-clustered index on what? For a query with high-selectivity, a clustere
d index on the search
> column will still outperform a nonc-lustered (but the ihger selectivity, t
he more marginally) as SQL
> Server doesn't have to fetch each row in the datapage. Unless the non-clus
tered index covers the
> query, of course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:CE190BBB-A52C-4D06-9AA8-8E33D983B643@.microsoft.com...
>