It's not just one point or two extra bytes anymore!

It goes way beyond, if only I knew where it would take us...

Another one who chose NCHAR(1). The Netherlands, no score.

Amidst the 12 pages of reactions I found this:

What did I miss? Where can I join?

Off course!

Right...

That code is quite a rat's warren, without table structure, testdata and proper formatting.

But first let us get rid of the procedure call, once and for all.

SELECT data.*, ctrl.ctrln1

FROM

...

Thank you for the compliment, Manie. I appreciate it a lot.

😎

Manie Verster (4/30/2009) Here is a cursor that I would like you to convert to set for me please. If you can do this I will accept the fact that...

min.li (4/29/2009)

If you test it with my new edited populate test data script, you will see that this solution is not correct. The join have to be based on the...

Regarding the duplicate DataIDs: Well, then we can use a UNION.

SELECT TestData.RowNumber, TestData.DataID, FactRL.DataTime,TestData.Name NameFrom, FactRL.DataText

FROM TestData

JOIN FactRL ON TestData.DataID = FactRL.DataID

UNION

SELECT TestData.RowNumber, TestData.DataID, NULL ,TestData.Name NameFrom,...

And, off course, the populating of the tables can be done without a LOOP.

SET ROWCOUNT 10000;

SELECT IDENTITY(INT,101,1) AS id INTO #Tally FROM sysobjects a, sysobjects b;

SET ROWCOUNT 0;

INSERT INTO...

This query leads to an identical resultset, but I have no idea what you are trying to accomplish.

SELECT TestData.RowNumber, TestData.DataID, FactRL.DataTime,TestData.Name NameFrom, COALESCE(FactRL.datatext,factsl.datatext) DataText

FROM TestData

LEFT JOIN FactRL ON TestData.DataID...

It's just a way to populate a table/temp table/etc... with some values.

http://www.sqlservercentral.com/articles/TSQL/62867/

This was what I intended for the problem on page 8 which happens to look much the same as Samuel Vella's solution.

SET ROWCOUNT 5000;

SELECT IDENTITY(INT,0,1) AS id INTO #Tally FROM...

Well done! Can you tell us the publishing date of Part 3?

The results of the comparison: no spoilers here 😎

Original code:

CpuMs: 8750

LogRds: 834261

Elapsed: 9898

Rewrite:

CpuMs: 94

LogRds: 236

Elapsed: 89

DECLARE @Txt VARCHAR(MAX)

SELECT @Txt = ''

SELECT @Txt = @Txt + ao.name + ao.type_desc + CHAR(13) + CHAR(10)

FROM SYS.all_objects ao WHERE ao.type IN ('U' , 'V') ORDER...

select square(count(1)) From master.sys.columns C1