Index Creation Question

I have a table with structure
payment_id varchar(10)
date_received datetime
order_id_response_stat varchar(10)
order_response_error_msg varchar(10)
The payment_id was the clustered index and primary key.
The problem now is this field should allow duplicates so I need to change
the indexing on this table. These table is used mostly for INSERTS and will
have about 1 million rows in future. The only occasional select will be base
d
on payment_id and date_received fields.
I dont know which options will be best for me under this situation as I want
to get max perf gains be using an index.
Can anyone pls help me"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> The payment_id was the clustered index and primary key.
> The problem now is this field should allow duplicates so I need to change
OK, so what's your new primary key? Think about data integrity first --
then consider performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thats my question.
I can either create a composite index on payment and date and as a primary
key.
Can create identidy colmun as primary key
But I want to make sure that creating these wont impact the performance.
Pls somebody help what to do and the best scenario
"Adam Machanic" wrote:

> "Anup" <Anup@.discussions.microsoft.com> wrote in message
> news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> OK, so what's your new primary key? Think about data integrity first
--
> then consider performance.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||From an insert point of view, it might make more sense to put the date first
in the composite key, assuming that you insert the payments as they're made.
But that might have a negative impact on selects, if you retrieve the data
based on the payment id. You should probably experiment on your end to find
the best combination.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:6A5A06FA-3087-4E8E-B926-4C30C98710A0@.microsoft.com...[vbcol=seagreen]
> Thats my question.
> I can either create a composite index on payment and date and as a primary
> key.
> Can create identidy colmun as primary key
> But I want to make sure that creating these wont impact the performance.
> Pls somebody help what to do and the best scenario
> "Adam Machanic" wrote:
>