Creating New Indexes

I have a table that I need to update and the update is killing everything
else because the column I need to update appears to not be indexed. Now, I
thought I could go into the Design Table screen and add an index, but that
doesn't seem to have helped me. So, how then do I check what indexes and/or
keys exist on a given table? Thanks.
Willie
Willie,
Try:
EXEC SP_HELPINDEX YOURTABLENAME
or use Manage Indexes in Query Analyzer.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>
|||Hi,
The best way Jelly told u
if u need more then u can search like this
declare @.id varchar(20)
select @.id=id from sysobjects where name='custtable'
select * from sysindexes where id=@.id
from
Doller
|||You don't need to index the column that's being updated to improve
performance just the index the columns in the WHERE clause.
Exmaple
1 million rows or
col1 int
col2 int
UPDATE table SET col1 = 5 WHERE col2 = 9
A full table scan must be performed to locate the data, if you add an index
on col1 a table scan will still be performed because what you're searching
on is col2
you should place an index on col2. This will enable SQL server to locate the
row quickly.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>
|||Hmm... OK, so I go in and the Index does exist. Now, the column I am
updating is the same column that I am querying on. Basically, I am trying to
do a bulk update on a column and that column is the only thing that
designates it. Here is the table create script:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CustomerProduct]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[CustomerProduct]
GO
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
And the Index info:
customerproduct_Site_ProductNumber nonclustered located on PRIMARY iSiteId,
chProductNumber
CustomerProduct0 nonclustered located on PRIMARY iOwnerId, iProductId
IX_CustomerProduct_Source nonclustered located on PRIMARY iSourceId
NNXCustomerProduct_iContactId nonclustered located on PRIMARY iContactId
NNXcustomerproduct_ownerid nonclustered located on PRIMARY iOwnerId
NNXcustomerproduct_prodnumber nonclustered located on PRIMARY
chProductNumber
NNXcustomerproduct_serialnum nonclustered located on PRIMARY vchSerialNumber
NNXCustomerProductInsertDate nonclustered located on PRIMARY dtInsertDate
NNXCustomerProductRecStat nonclustered located on PRIMARY tiRecordStatus
PKNUCCustomerProduct clustered, unique, primary key located on PRIMARY
iProductId, iSiteId
and finally the field I am searching/updating on is chProductNumber. Thanks
again for your help.
Willie
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:LLn1f.7212$nF6.3110@.fe04.news.easynews.com...
> You don't need to index the column that's being updated to improve
> performance just the index the columns in the WHERE clause.
> Exmaple
> 1 million rows or
> col1 int
> col2 int
> UPDATE table SET col1 = 5 WHERE col2 = 9
> A full table scan must be performed to locate the data, if you add an
> index on col1 a table scan will still be performed because what you're
> searching on is col2
> you should place an index on col2. This will enable SQL server to locate
> the row quickly.
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>