Jeff Moden (12/3/2009)

---

Ah... now I see. Somewhere along the line, a space in quotes got dropped when I transferred the code to the forum window. I've edited the...

Now I see too. Replacing the spaces back to zeroes after trimming isn't necessary as the Trimmed value is only used to determine the number of digits behind the decimal...

SSRS expects the DataSet to return a single result set. In your case, it looks likes it takes the first result set and ignores the remainder.

Quote from BOL:

Query string...

Nice solution, but not entirely correct, I think. What about non-trailing zeroes behind the decimal point i.e. 1234.5078. The result is Modifier=1000, Result=1234507. I little fix will do:

SELECT ColumnA,

...

OK, here's an attempt:

DECLARE @Temp TABLE (ID VARCHAR(50),Type VARCHAR(50))

INSERT INTO @Temp VALUES ('BD001','BD')

INSERT INTO @Temp VALUES ('BD002','BD')

INSERT INTO @Temp VALUES ('BD003','BD')

INSERT INTO @Temp VALUES ('BD004','BD')

INSERT INTO @Temp VALUES ('BD005','BD')

INSERT INTO...

lmu92 (11/13/2009)

---

Peter Brinkhaus (11/13/2009)

---

I did that test also. It requires less coding but, based on the 3 rows input, there are less rows generated within the cross apply to join...

I did that test also. It requires less coding but, based on the 3 rows input, there are less rows generated within the cross apply to join with if you...

Although the solution provided by Jus is simple and straight forward, it might lead to a performance problem. With eight triples of columns it leads to eight table scan. The...

There was lately a thread on an almost similar problem. Take a look at .

Peter

Paul White (11/1/2009)

---

3. Peter's method also suffers at the hands of the optimizer for large sets. The lazy spool on the #Update table is a bit silly -...

There is an XML file SqlCommonObjects.xml with a description of all the built-in functions. Its default location is C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlToolsData\1033. Maybe you can try to read this file...

Thanks Wayne, I've got the attachment. I don't think Paul has to redeem himself. He submitted a very original solution which seems, based on the execution plan, competitive to the...

Wayne, thanks for the extensive test results. However I don't see the attachments in your post. I'm quite curious about the execution plans on a millions input rows, because the...

select U.rowid, CA.column_nm, CA.old, CA.new

from @Update U

cross apply

( values

...

Bob, if you're going to write an article about this, this one may interest you as well. In an attempt to reverse engineer the query plan of your solution I...