I suspect it's all the same thing just moving the parenthesis around. But, having all those parenthesis in there makes it much harder to read. I don’t have too much...

Yeah, I think the approach is fine. I was just messing around with some junk so I thought I'd share my code (obviously the Date table I am using is...

The basics are Entities, Attributes and Relationships. Can you define all the Entities? Without knowing all the details I can see Policy, Question and Answer. Are there other entities? What...

Perhaps some sample data an expected output would help:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

For fun here is a one more way using a LEFT OUTER JOIN:UPDATE

T

SET

speccode = ''

FROM

#Test AS T

LEFT OUTER JOIN

(

select speccode, MAX(dateentered) AS MaxDate

from #test

group by speccode

) AS D

ON T.SpecCode...

Carl Federl (6/17/2009)

---

Actually, GSquared proposed the CTE solution but I was interested in comparing the performance to the "legacy" exists solution, which is needed when the RDBMS does not...

I would assume that applying functions to the "cph.cbTransEffectiveDate" columns is causing the performance issues. Can you describe in more detail what you are trying to do? Are you just...

Here is another way:UPDATE

T

SET

SpecCode = ''

FROM

(

SELECT SpecCode, ROW_NUMBER() OVER (PARTITION BY SpecCode ORDER BY DateEntered DESC) AS RowRank

FROM #Test

) AS T

WHERE T.RowRank > 1

EDIT: I'm getting lazy and didn't...

The ROW_NUMBER() fucntion is probably the way to go, but if you have the option to create the table you can use an IDENTITY INSERT INTO:SELECT 'SPC-'+OptionGroup+'-'+RTrim(cast(pid as varchar(6))),

'NEM','SPC-'+OptionGroup,IDENTITY(INT,1,1) as...

Although SQL Server manifests a PK as an index, and thus has performance implications. Additionally, FKs also have some overhead associated with them.

However, PKs and FKs have nothing to...

I cannot remeber if this was fixed in a patch, but SQL200 used to have issues with BIT columns. You might want to re-run your tests by CASTing the literal...

Hopefully, this address your question, but you should also read up on what transactions are/do.

When you put the TRANS at the top SQL has to do a lot of...

I'm not sure why you'd need to move those values into variables, unless you are converting your query to one that takes paramamters. Additionally, using a split function to populat...

One more for fun:SELECT RIGHT('0' + CAST(MONTH(CURRENT_TIMESTAMP) AS VARCHAR(2)), 2)

For what it is worth, SQL doesn't remove trailing spaces when inserting into a VARCHAR. Rather, SQL ignores them...