index question

We have a stored procedure that stopped working. I put it in the debugger and
discovered that the update statement takes forever - I stopped it after 2
minutes. This is the update statement: UPDATE tbltelemarketing SET download =
'2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
column. When I do an execution plan on the query, it doesn't use the index on
the id column. It does a clustered index scan using an index on several
columns. The table keeps growing so I guess at some point, it stopped using
the ID index.
Is there a way to force it to use the ID index? Any other ideas on how I can
speed up the query. The table has about 11 million rows. Here is the schema
and indexes.
CREATE TABLE [dbo].[tbltelemarketing] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_date] [smalldatetime] NULL ,
[prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[id_mail] [int] NULL ,
[bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[drop_date] [smalldatetime] NULL ,
[hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pest_cncl] [smalldatetime] NULL ,
[term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[term_cncl] [smalldatetime] NULL ,
[promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[download] [smalldatetime] NULL ,
[recd_date] [smalldatetime] NULL ,
[mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[recd_week] [int] NULL ,
[live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[id_lns] [int] NULL ,
[branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reassign_date] [datetime] NULL ,
[company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[delete_date] [smalldatetime] NULL ,
[update_cd] [int] NULL ,
[update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
[dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], [drop_date]) ON
[PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing]([live],
[promo_code]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarketing]([branch],
[promo_code], [drop_date], [delete_cd], [live], [prop_zip5], [prop_zip4],
[hh_score2]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_3] ON
[dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarketing]([branch],
[delete_cd], [live], [promo_code]) ON [PRIMARY]
GO
CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
ON [PRIMARY]
GO
Thanks,
Dan D.
1) Are your statistics on the table are current
2) Why i don't see the unique keywork on ID index and other schema question
if ID is identity why can't it be primary key with non-clustered index
"Dan D." wrote:

> We have a stored procedure that stopped working. I put it in the debugger and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET download =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped using
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I can
> speed up the query. The table has about 11 million rows. Here is the schema
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [update_date] [smalldatetime] NULL ,
> [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_mail] [int] NULL ,
> [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [drop_date] [smalldatetime] NULL ,
> [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pest_cncl] [smalldatetime] NULL ,
> [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_cncl] [smalldatetime] NULL ,
> [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [download] [smalldatetime] NULL ,
> [recd_date] [smalldatetime] NULL ,
> [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [recd_week] [int] NULL ,
> [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_lns] [int] NULL ,
> [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [reassign_date] [datetime] NULL ,
> [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_date] [smalldatetime] NULL ,
> [update_cd] [int] NULL ,
> [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> [dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], [drop_date]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing]([live],
> [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarketing]([branch],
> [promo_code], [drop_date], [delete_cd], [live], [prop_zip5], [prop_zip4],
> [hh_score2]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_3] ON
> [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarketing]([branch],
> [delete_cd], [live], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.
|||You can use an index hint when you want to force SQL Server to use an index.
You can search Books Online (within the SQL Server program group) for INDEX
HINT. The general usage is:
..
FROM tbltelemarketing WITH (INDEX (nci_tbltelemarketing_ID)) WHERE ID =
10617138
I assume that ID is the primary key on the table...yet you don't define it
as a primary key constraint. Perhaps you should define a primary key on the
table.
Keith
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:2A769066-CBE1-4391-8900-02E825BE30EA@.microsoft.com...
> We have a stored procedure that stopped working. I put it in the debugger
and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET
download =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index
on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped
using
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I
can
> speed up the query. The table has about 11 million rows. Here is the
schema
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [update_date] [smalldatetime] NULL ,
> [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_mail] [int] NULL ,
> [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [drop_date] [smalldatetime] NULL ,
> [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pest_cncl] [smalldatetime] NULL ,
> [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [term_cncl] [smalldatetime] NULL ,
> [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [download] [smalldatetime] NULL ,
> [recd_date] [smalldatetime] NULL ,
> [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [recd_week] [int] NULL ,
> [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [id_lns] [int] NULL ,
> [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [reassign_date] [datetime] NULL ,
> [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_date] [smalldatetime] NULL ,
> [update_cd] [int] NULL ,
> [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> [dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], [drop_date])
ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing]([live],
> [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_2] ON
[dbo].[tbltelemarketing]([branch],
> [promo_code], [drop_date], [delete_cd], [live], [prop_zip5], [prop_zip4],
> [hh_score2]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_3] ON
> [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code]) ON
[PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_4] ON
[dbo].[tbltelemarketing]([branch],
> [delete_cd], [live], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.
|||Dan,
The current index on ID is non-unique. If the ID column is in fact the
identifying column, then make sure you create a unique index on it. Or
even better, to create a Primary Key constraint on it (which will
automatically create a unique index on it).
HTH,
Gert-Jan
Dan D. wrote:
> We have a stored procedure that stopped working. I put it in the debugger and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET download =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped using
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I can
> speed up the query. The table has about 11 million rows. Here is the schema
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
<snip>
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketing]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.
|||I did update the statistics. You don't see the "unique" keyword because I was
trying different indexes with different names and the one you see was the
last one I tried. At one time it was primary with non-clustered index.
"harvinder" wrote:
[vbcol=seagreen]
> 1) Are your statistics on the table are current
> 2) Why i don't see the unique keywork on ID index and other schema question
> if ID is identity why can't it be primary key with non-clustered index
>
> "Dan D." wrote:
|||It was a primary index at one time. When it stopped working I changed it in
the process of testing.
"Gert-Jan Strik" wrote:

> Dan,
> The current index on ID is non-unique. If the ID column is in fact the
> identifying column, then make sure you create a unique index on it. Or
> even better, to create a Primary Key constraint on it (which will
> automatically create a unique index on it).
> HTH,
> Gert-Jan
>
> Dan D. wrote:
> <snip>
>
|||Problem solved. I was looking at the development server but querying the
production server. There was no index on production. Now that there is, it
works. Wonders never cease.
Thanks to all for your help.
"harvinder" wrote:
[vbcol=seagreen]
> 1) Are your statistics on the table are current
> 2) Why i don't see the unique keywork on ID index and other schema question
> if ID is identity why can't it be primary key with non-clustered index
>
> "Dan D." wrote: