For getting Idea

SELECT Parts.SKU, Locations.Description, Manufacturer.Name,
PartsLocations.Qty, PartsLocations.LastInventoried
FROM Parts
INNER JOINT Manufacturer ON Parts.ManufacturerID=
Manufacturer.ManufacturerID
INNER JOINT Locations ON Parts.LocationID= Loactions.LocationID
WHERE SKU?
Would performance will be increased after creating a nonclustered index on
the primary key of each above tables ?
Thanks
NOOR
Noor,
If you really have a PK constraint then you already have indexes on those
columns as they are created behind the scenes when you create the
constraint. If not you should definitely create them as all PK's should
have the proper index. It would also help to ensure there is a proper index
on the SKU columns as well.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:Ou7xkJoXEHA.3796@.TK2MSFTNGP11.phx.gbl...
> SELECT Parts.SKU, Locations.Description, Manufacturer.Name,
> PartsLocations.Qty, PartsLocations.LastInventoried
>
> FROM Parts
>
> INNER JOINT Manufacturer ON Parts.ManufacturerID=
> Manufacturer.ManufacturerID
> INNER JOINT Locations ON Parts.LocationID= Loactions.LocationID
> WHERE SKU?
>
>
> Would performance will be increased after creating a nonclustered index on
> the primary key of each above tables ?
>
> Thanks
> NOOR
>
|||Yes I am talking about non clustered index not the clustered index.
Thanks
Noor
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uEU7gHqXEHA.3112@.tk2msftngp13.phx.gbl...
> Noor,
> If you really have a PK constraint then you already have indexes on those
> columns as they are created behind the scenes when you create the
> constraint. If not you should definitely create them as all PK's should
> have the proper index. It would also help to ensure there is a proper
index[vbcol=seagreen]
> on the SKU columns as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Noor" <noor@.ngsol.com> wrote in message
> news:Ou7xkJoXEHA.3796@.TK2MSFTNGP11.phx.gbl...
on
>
|||A PK does not have to be a clustered index. It gets created that way by
default but if you have another index that would be better as a clustered
index you can create the PK as non-clustered. But it makes no sense to have
2 indexes on the same column, one clustered and one not.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:%235Ca%23PqXEHA.1764@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Yes I am talking about non clustered index not the clustered index.
> Thanks
> Noor
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uEU7gHqXEHA.3112@.tk2msftngp13.phx.gbl...
those[vbcol=seagreen]
> index
index
> on
>
|||Andrew,
While it's not often the case, it can make sense to have both
clustered and non-clustered indexes on the primary key. In the repro
below, you can see how fewer data pages might be read by a query on the
key columns when there is a non-clustered index available - presumably
in this example there are separate reasons for the primary key to be
clustered.
use Northwind
go
drop index [Order Details].OrdersOrder_Details
drop index [Order Details].OrderID
go
set statistics io on
go
select OrderID, min(ProductID) as minProd
from [Order Details]
group by OrderID
go
set statistics io off
go
create unique nonclustered index OD_OP on [Order Details](OrderID,
ProductID)
go
set statistics io on
go
select OrderID, min(ProductID) as minProd
from [Order Details]
group by OrderID
go
set statistics io off
go
drop index [Order Details].OD_OP
go
create index OrdersOrder_Details on [Order Details](OrderID)
create index OrderID on [Order Details](OrderID)
go
Steve Kass
Drew University
Andrew J. Kelly wrote:

>A PK does not have to be a clustered index. It gets created that way by
>default but if you have another index that would be better as a clustered
>index you can create the PK as non-clustered. But it makes no sense to have
>2 indexes on the same column, one clustered and one not.
>
>
|||Steve,
Yes covered indexes usually have less IO. But a single column PK will
usually not be advantageous to have both a clustered and non.
Andrew J. Kelly SQL MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:OiNjm15XEHA.996@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Andrew,
> While it's not often the case, it can make sense to have both
> clustered and non-clustered indexes on the primary key. In the repro
> below, you can see how fewer data pages might be read by a query on the
> key columns when there is a non-clustered index available - presumably
> in this example there are separate reasons for the primary key to be
> clustered.
> use Northwind
> go
> drop index [Order Details].OrdersOrder_Details
> drop index [Order Details].OrderID
> go
> set statistics io on
> go
> select OrderID, min(ProductID) as minProd
> from [Order Details]
> group by OrderID
> go
> set statistics io off
> go
>
> create unique nonclustered index OD_OP on [Order Details](OrderID,
> ProductID)
> go
> set statistics io on
> go
> select OrderID, min(ProductID) as minProd
> from [Order Details]
> group by OrderID
> go
> set statistics io off
> go
> drop index [Order Details].OD_OP
> go
> create index OrdersOrder_Details on [Order Details](OrderID)
> create index OrderID on [Order Details](OrderID)
> go
> Steve Kass
> Drew University
> Andrew J. Kelly wrote:
have
>