(Before we go on to set-based, let's tidy the RBAR a bit)

SET NOCOUNT ON

DECLARE @MyData TABLE(pk INT IDENTITY(1,1) PRIMARY KEY, id INT)

DECLARE @index INT

DECLARE @MyID INT

DECLARE @startTime DATETIME

DECLARE @endTime DATETIME

...

It stays in effect in nested procedures, as a simple test shows.

Heh. Well unless you specifically need that integer as a fixed-length char(30)...

CONVERT(varchar(11), @num)

Paul

With your clarified description, the IDENTITY column would be closest in behaviour to an Oracle sequence.

ROW_NUMBER is great for individual set operations, but the next value in the row number...

Until a 'D3' expert comes along, my only suggestion would be to export the data from the source to a delimited file and bulk load that into SQL Server. ...

create database [04C87CAC-9AA8-4465-AC6E-921060A604FF];

go

alter database [04C87CAC-9AA8-4465-AC6E-921060A604FF] set recovery simple;

go

use [04C87CAC-9AA8-4465-AC6E-921060A604FF];

go

checkpoint

select * from fn_dblog(null, null)

print...

RBarryYoung (6/25/2009)

---

Alvin Ramard (6/25/2009)

---

Are you sure you weren't talking...

Bizarre. So if we change the question the...

Was this reply prompted by my post?

If so, I think you might have missed what I was driving at 🙂

If you get a minute,...

saidwarak01 (6/25/2009)

---

My simple advise to moderators is any kind of post whether it is simple or tuff. It should be properly handled by giving some solution. This forum...

I love this one! Would use UNION ALL instead of UNION, but still...:-)

Yeah I know what you mean - went through something similar with PCI-DSS (or whatever it was called).

Heh. You could always upgrade to 2008 Enterprise and use Transparent Database...

Cool stuff Chris. Of course it leaves me wondered about the hash aggregate versus stream aggregate thing but hey.

So PIVOT can be slightly more efficient - if you can...

Grant Fritchey (6/25/2009)

---

While I'm in agreement with the general statements of the post, I've got a pick a nit with this part. The algebrizer creates the parse tree, the first...

...the AFTER trigger version of my code is simpler as expected:

ALTER TRIGGER trg_cnsmr_accnt_ai

ON dbo.cnsmr_accnt

AFTER INSERT

AS

BEGIN

IF@@ROWCOUNT = 0 RETURN;

SET NOCOUNT ON;

SET ROWCOUNT 0;

;WITHOrdered AS

(

SELECTcnsmr_id,

cnsmr_id + '-' + CONVERT(VARCHAR(10), 100 + ROW_NUMBER()...