I don't know about "better", but generally when I'm comparing two algorithms that should produce the same result-set I'd do something like this: -

SELECT MIN(name) AS TableName, --all columns here

FROM...

rahu21 (11/4/2011)

---

What about another unspectacular solustion:

SELECT ISNULL((SELECT TOP 1 RowNumber as FirstUnusedID

FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID )

AS RowNumber,ID FROM #testEnvironment2)T

WHERE RowNumber <> ID

ORDER BY 1),(ISNULL((SELECT MAX(ID)...

John Mitchell-245523 (11/4/2011)

---

I think you'd be best investigating converting your variable to datetime and then using the DATEPART function. But, if you insist on doing it as above, something...

The same could be said by many Oracle DBAs.

Test all three solutions against your own data, but ColdCoffee's CROSS APPLY is looking unbeatable on my...

ankita.patel01 81294 (11/3/2011)

---

Cadavre,

I didnt realize the data was doing this until i saw it. Maybe this will help you.

CREATE TABLE #Account ([Account#] BIGINT, RepCode CHAR(4))

INSERT INTO #Account

SELECT 12345678, 'XX01'

CREATE TABLE...

ankita.patel01 81294 (11/3/2011)

---

Hi Cadavre,

Thanks for helping with this request. What you have is almost exactly what i want. However, what i found that a lot of the accounts have last...

Really wasn't my intention to appear to be bashing...

Hello and welcome to SSC!

It seems that your DDL script has become detached from your post, or perhaps you were unaware of the benefits of providing one.

When you have time,...

Customer service operator for 6 months before getting fired for having no tact (have improved greatly since then, I was 17).

Bar work (ranging from Bar associate, Bar supervisor, Door supervisor...

SELECT ((59459 / 1368003) * 100), --this is 0

((59459 / 1368003.0) * 100) --this is 4.346408500

If you need further prodding, supply DDL please.

Teemo (11/3/2011)

---

guys i think i did it,

SELECT nkey,

(SELECT msg + '|'

FROM #tmpdata r2

WHERE r2.nkey = r1.nkey

ORDER BY msg

FOR XML PATH('')) AS fmsg

FROM #tmpdata r1

GROUP BY nkey

but my problem is that...

Thanks guys. The post was more to show future Google searchers the power of the tally table (which I learnt from Jeff's posts and articles :-)) than to correct CELKO....

Is your trigger attempting to insert into the identity column?

Please post the DDL for the tables involved and the triggers.

That's why I said set it as the dataflow source.