|
|
Posted by Hugo Kornelis on 09/22/06 21:28
On 22 Sep 2006 13:11:12 -0700, mike@barrodale.com wrote:
>I'm looking for an efficient way to populate derived columns when I
>insert data into a table in SQL Server. In Informix and PostgreSQL
>this is easily done using the "for each row..." syntax, but all I've
>been able to come up with for SQL Server is the following:
(snip)
>This seems terribly inefficient since each insert results in an extra
>select and update. And if the table is large and unindexed (which it
>could be if we are bulk loading) then I would imagine this would be
>very slow.
>
>Are there any better ways of doing this?
Hi Mike,
Since SQL Server is optimized for set-based operations, you'll probably
find the speed of these operations to be quite adequate in most cases.
Here's an alternate syntax of the UPDATE statement that might result in
even faster operation. Note, though, that this syntax has some quirks,
especially if there's not a guaranteed one to one mapping of rows in the
update target and the source of the data.
UPDATE t
SET c = some_function_of_b
FROM testtrigger AS t
INNER JOIN inserted AS i
ON i.id = t.id
But only use this if you have a solid reason for being unable to use a
computed column, as Duncan ("undercups") demonstrates in his reply!
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|