Index Table

Hi ,
I have created 3 fields in for my table , FieldA , B and C all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Do the performance is the same ?
Travis TanThe optimizer should create the same execution plan for both of these
statements therefore performance should be the same.
--Brian
(Please reply to the newsgroups only.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
> Hi ,
> I have created 3 fields in for my table , FieldA , B and C all asc
> order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
> Do the performance is the same ?
>
> --
> Travis Tan|||Brian ,
Sorry , What I means is I create an index in TableA with FieldA , B and C
all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Travis Tan
"Brian Lawton" wrote:

> The optimizer should create the same execution plan for both of these
> statements therefore performance should be the same.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Travis" <Travis@.discussions.microsoft.com> wrote in message
> news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
>
>|||Same answer:
>The optimizer should create the same execution plan for both of these
>statements therefore performance should be the same.
Uytkownik "Travis" <Travis@.discussions.microsoft.com> napisa w wiadomoci
news:48D82684-DF75-4740-BC4F-9604F33235A8@.microsoft.com...[vbcol=seagreen]
> Brian ,
> Sorry , What I means is I create an index in TableA with FieldA , B and
> C
> all asc order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
>
> Travis Tan
>
> "Brian Lawton" wrote:
>

Index Table

Hi ,
I have created 3 fields in for my table , FieldA , B and C all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Do the performance is the same ?
Travis Tan
The optimizer should create the same execution plan for both of these
statements therefore performance should be the same.
--Brian
(Please reply to the newsgroups only.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
> Hi ,
> I have created 3 fields in for my table , FieldA , B and C all asc
> order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
> Do the performance is the same ?
>
> --
> Travis Tan
|||Brian ,
Sorry , What I means is I create an index in TableA with FieldA , B and C
all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Travis Tan
"Brian Lawton" wrote:

> The optimizer should create the same execution plan for both of these
> statements therefore performance should be the same.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Travis" <Travis@.discussions.microsoft.com> wrote in message
> news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
>
>
|||Same answer:
>The optimizer should create the same execution plan for both of these
>statements therefore performance should be the same.
Uytkownik "Travis" <Travis@.discussions.microsoft.com> napisa w wiadomoci
news:48D82684-DF75-4740-BC4F-9604F33235A8@.microsoft.com...[vbcol=seagreen]
> Brian ,
> Sorry , What I means is I create an index in TableA with FieldA , B and
> C
> all asc order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
>
> Travis Tan
>
> "Brian Lawton" wrote:

Index problem

this my script
CREATE UNIQUE INDEX [RELBFM].[UQ_PMT_PMTNAME] ON [RELBFM]. [ISMPMT] ("PMTNAME" ASC);
following errors occur
where i am facing problemSyntax for a create index statement:CREATE UNIQUE NONCLUSTERED INDEX ix_t_name_c_name_nu_nc
ON schema_name.t_name (c_name ASC)
Spot the diff ;)|||The original is correct for Oracle, but indexes in SQL Server are assumed to be part of the table's schema. In fact, the "index owner" is not even a concept in SQL Server.|||[QUOTE=pootle flump]Syntax for a create index statement:CREATE UNIQUE NONCLUSTERED INDEX ix_t_name_c_name_nu_nc
ON schema_name.t_name (c_name ASC)
Spot the diff ;) [/QUOso

so when we use CLUSTERED AND NONCLUSTERED INDEX

I had used clustered primary key so what type of index should I use|||Actually the clustered\ nonclustered bit is not mandatory. I just like to put it in so it is explicit. It also appears that there was a copy and paste problem (apologies) - there should be a space between nonclustered and index. The difference was actually as mcrowley said - you "qualified" the index name when you should not have.

A pk and a not null unique index are exactly the same as far as sql server is concerned.

Index hell again.

Would you allow an index on Customer
CREATE NONCLUSTERED INDEX [IX_Customer] ON [dbo].[Customer]
(
[first_name] ASC,
[last_name] ASC,
[email] ASC
) ON [PRIMARY]
So the defrag on this is terrible and insertion on the batches is slowed
down.As always, it depends. I would certainly put a fill factor < 100 - and this
depends on how frequent your inserts and defrags are. This index would
cover a query such as:
select
email
from
Customer
where
first_name = 'John'
and last_name = 'Smith'
If you're using SQL 2005, you could make email an included column and just
key on first_name, last_name.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"_Stephen" <srussell@.electracash.com> wrote in message
news:%23rWVJ1wYGHA.5012@.TK2MSFTNGP04.phx.gbl...
Would you allow an index on Customer
CREATE NONCLUSTERED INDEX [IX_Customer] ON [dbo].[Customer]
(
[first_name] ASC,
[last_name] ASC,
[email] ASC
) ON [PRIMARY]
So the defrag on this is terrible and insertion on the batches is slowed
down.

Index hell again.

Would you allow an index on Customer
CREATE NONCLUSTERED INDEX [IX_Customer] ON [dbo].[Customer]
(
[first_name] ASC,
[last_name] ASC,
[email] ASC
) ON [PRIMARY]
So the defrag on this is terrible and insertion on the batches is slowed
down.As always, it depends. I would certainly put a fill factor < 100 - and this
depends on how frequent your inserts and defrags are. This index would
cover a query such as:
select
email
from
Customer
where
first_name = 'John'
and last_name = 'Smith'
If you're using SQL 2005, you could make email an included column and just
key on first_name, last_name.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"_Stephen" <srussell@.electracash.com> wrote in message
news:%23rWVJ1wYGHA.5012@.TK2MSFTNGP04.phx.gbl...
Would you allow an index on Customer
CREATE NONCLUSTERED INDEX [IX_Customer] ON [dbo].[Customer]
(
[first_name] ASC,
[last_name] ASC,
[email] ASC
) ON [PRIMARY]
So the defrag on this is terrible and insertion on the batches is slowed
down.