|
|
You're in the
sql server clients
group:For getting Idea
sql server clients:
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 [quoted text, click to view] "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 > >
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 [quoted text, click to view] "Noor" <noor@ngsol.com> wrote in message news:%235Ca%23PqXEHA.1764@TK2MSFTNGP10.phx.gbl... > 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 > > 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 > > > > > > > > > > > >
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 [quoted text, click to view] "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 > 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 > > > > > >
If you have already defined a primary on your table then a
index would automatically have been created. Use sp_helpindex [tablename] to check what indexes exist. [quoted text, click to view] >-----Original Message-----
>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 > > >.
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 [quoted text, click to view] 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 [quoted text, click to view] "Steve Kass" <skass@drew.edu> wrote in message news:OiNjm15XEHA.996@TK2MSFTNGP12.phx.gbl... > 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. > > > > > > >
Don't see what you're looking for? Try a search.
|
March 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |