Thanks, I agree using the solution to fill a sister table with the result and a task to keep it up to date makes the most efficient sense.

Sorry about posting...

Can't update the original table...knowing when the data came in is an important piece of data.

adding a couple of columns and running a process once in a while to update...

I am using SQL server 2005, 2008 and 2012 so CTE's are OK

Thanks for your solution, but it looks a little messy as a view definition.

I was thinking of something...

might have gaps

yes, because of the joins and the use of "not in" it should get much worse with a large data table.

you might try it looking at one name (add to...

Here is a version that works but I am not sure a cursor solution might be faster.

It also misses the special case of one row for a Name entry (no...

you asked so: I have dual AA in CS and Math, (back when computers were big beasts) a BS in Physics and spent 27 years in a research...

I use SQL2000 and Access front ends in many applications...works great.  I have tested the apps with SQL2005, but don't have any in production.  I am not sure what translation...

Are you sure the field in SQL-Server is defined as "ntext" or "nvarchar", it looks like it is taking some characters as 2 which is what happens with the field...

We are using mirror tables like you describe for some data and they work well.  I took the indexes off the mirror table to cut down on the overhead since...

OK, now I understand...from the books on-line:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The...

Jeff, the problem is with the multiply not the Power function....very strange...

try this:

select cast(2.34 as dec(38,19)) * .000000001

you will get .000000002340000000

then try this:

select cast(2.34 as dec(38,19)) * cast(.000000001 as...

Thanks Jeff, sorry I should have been more careful.  Power takes its precision from the first argument.  (10.0 and 10.000 yield different results for small numbers)

this should cover all bases:

Declare...

Here is an example of something you can use in a query:

Declare @N Dec(38,19)

set @N = 23.4500678

select @N,Round(@N/Power(10,FLOOR(LOG10(ABS(@N)))),3)*Power(10,FLOOR(LOG10(ABS(@N))))

Beware of using Float instead of Decimal as this has round off issues. ...

Here is a simple example using the pubs db ...

create trigger test1_up on test1 instead of update as

 BEGIN

  update stores set stores.city = i.city, stores.state = i.state,

       stores.stor_id...