Row Constructors? (beside INSERT)

Are we to expect support for Row Constructors beside what is in the current CTP (i.e. beside what is shown for INSERT?

Examples:

Select *

From MyTable

Where (a,b) in (Select a,b From OtherTable...);

or

Update MyTable
Set (a, b, c, d) = (SELECT a, b, c, d From OtherTable ....)
Where ...;

thanks

No. Use of row constructors in predicates and UPDATE will not be in Katmai. -- Umachandar Jayachandran Microsoft SQL Server Performance Team SQL Server Engine Team Tips Blog at http://blogs.msdn.com/sqltips/default.aspx SQL Server Performance Engineering Team Tips Blog at http://blogs.msdn.com/sqlperf/default.aspx This posting is provided "AS IS" with no warranties, and confers no rights. "Lakusha"@.discussions.microsoft.com wrote on Thu, 12 Jul 2007 07:06:02 -0700: L> Are we to expect support for Row Constructors beside what is in the L> current CTP (i.e. beside what is shown for INSERT? L> Examples: L> Select * L> From MyTable L> Where (a,b) in (Select a,b From OtherTable...); L> or L> Update MyTable L> Set (a, b, c, d) = (SELECT a, b, c, d From OtherTable ....) L> Where ...; L> thanks

rounding numerics

Hello,

Executing this statement outputs 10.0000000000. I expect it to be 9.999999999.

declare @.test NUMERIC(24,10)

declare @.test2 NUMERIC(24,10)

set @.test2 = 0.0000000000

set @.test = 9.999999999

select @.test * (1 - @.test2)

Changing the type of @.test2 to VARCHAR(12,10) corrects the problem. I don't understand why SQL Server does this rounding?

Thanx,

Wouter

Hi,

You need to change the script like this to avoid an implicit cast to an integer:

declare @.test NUMERIC(24,10)
declare @.test2 NUMERIC(24,10)
set @.test2 = 0.0000000000
set @.test = 9.999999999
select @.test * cast(1 - @.test2 as NUMERIC(24,10))

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

At least part of the answer lies with the fact that the "1" in the statement:

select @.test * (1 - @.test2)

is an integer datatype and must be converted for all of the operations to take place. This can be avoided by performing explicit converting to the numeric(24,10) datatype such as:

select @.test * convert (numeric(24,10), (convert (numeric(24, 10),1) - @.test2)) as converted

-- converted
-- -
-- 9.999999999

Also beware that converting to a float might not be exactly what you want either:

select @.test * convert (float,1) - @.test2 as floater

-- floater
-- --
-- 9.9999999989999999

|||{ Obviously, I am in agreement with Geert. :-) }