Oh, OK. Well it sounds similar to a problem we have

SELECT ...

FROM p

CROSS APPLY (SELECT TOP 1 ...

...

Wouldn't the EndDate be a "+" 1 to make it a day larger than the EffectiveDate?

Also, I think that you may want to use the DATEADD() function because MS is...

SELECT TOP 1 *

FROM X

ORDER BY Something (ASC/DESC)

The ASC (ascending) will get you the first record, the DESC (descending) will get you the last record. Just remember that TOP processes...

If you've got an existing system, monitor it to see what, if anything, is behaving badly. Start from there. Once you've identified the issues, you can start to make decisions...

I agree with what everyone has written and, since I didn't see it, I'll add one more thing. The reason you have to be so picky about the clustered index...

You can't do that, but there is a filter tool in the Object Explorer and in Object Explorer Details window that will allow you to look at subsets of the...

Stupid question, I know, but is it possible that the SET NOCOUNT ON is, you know, off inside these queries? Depending on how you make the call, I've seen rows...

OK. I take it all back. I've done some more research in preparation for the tests I was going to run. The Ninja is right. It's a myth.

This is from...

One key difference, ignoring the "myth" for the moment, is that the constraint can be used as part of a foriegn key if you choose while the index can't.

Now,...

What John said.

You know, I've never tried, but I have to assume that it would involve a larger amount of data. Based on the information, I doubt you'd see a difference with...

No, I can't prove it. It's information I've inherited. I thought it was from an article by Kalen Delaney, but I can't find it. Here's a reference from Kimberly Tripp:...

Creating a unique constraint is different than creating a key field. It sounds like you're in the Index/Key creation screen. Basically if you select unique key you're making an alternate...

The links I supplied are for 2005 (it's that kind of forum), but the concepts should be the same.

If you literally mean the difference between what you have typed, the first selects all columns & all rows from the table in question. Depending on the size of the...