Probably y never built reports.

Once again.

Look for SUBSTRING in your mysterious SP_GENERATE_INSERTS procedure.

Find why CHARINDEX(...) inside SUBSTRING returns 0 and CHARINDEX(...)-1 becomes "Invalid length parameter".

It does not matter how deeply you believe that SP...

First of all, you did not specify context where you "best practice" is the best. You just declared it "the best".

You were wrong there.

Let's not fool newbies.

> So,...

Your trigger must look like this:

UPDATE T2

SET Column1 = i.ColumnA

FROM inserted i -- system table containing rows from Table1 affected by update/insert

INNER JOIN Table2 T2 ON {join criteria between tables...

"Best practice" is something best, right?

If "it depends" then there are cases when it's not the best.

So, it cannot be named "the best practice".

The practice you suggested fails in many...

Because object names are not qualified with owner names you procedure is recompiled every time it's been called.

Because you've got WFSA.* optimiser needs to read database schema to retrieve list...

And of course your database must be in "Full" recovery mode.

What are those regions?

What does that set means?

Lets assume it's a set to be returned for some report.

Then create a table:

|ReportName|Region_Name|

| MyReport |bfs|

| MyReport |Canada|

| MyReport |East Coast|

| OtherReport |Canada|

|...

It's not about limitations.

It's about SQL Server interpreter.

It reads a string and translates it to computer code according to its understanding.

It takes string word by word (word is text...

Look for SUBSTRING in your mysterious SP_GENERATE_INSERTS procedure.

Find why CHARINDEX(...) inside SUBSTRING returns 0 and CHARINDEX(...)-1 becomes "Invalid length parameter".

I would not name it best practice, I would not name it even recommended practice.

You cannot always control naming conventions. Sometimes it's dictated by some business rules you cannot change.

So,...

Jeff's query becomes affected when it's less than 128M of memory reserved.

At 128M point it's about 6.5s vs 18.2s

It's definitely memory.

I went to my local server (to avoid other users influence) and played with memory size allocated to SQL Server.

I ran test 3 times for every memory size...

Dunno.

On another computer got this:

===== Jeff ======

(4958 row(s) affected)

7143 Milliseconds

===== Peso ======

(4958 row(s) affected)

9826 Milliseconds

Tempdb here is...

> Not sure why Peter's 2k solution took so long on your machine, Serqiy.

It's probably because Peter's solution consumes much more memory and size of intermediate results exceeds available memory.

Try...