Thanks for the answer,

We already have views on top of our tables, so it would be easy to do. But that would serve no purpose. The main goal would...

Going from 28 to 19 would reduce the storage required, so, as you've discovered, I think SQL would create a new column and copy the data over to it.

If you...

SELECT ca1.*
FROM dbo.Table1 t1
CROSS APPLY (
    SELECT EmpNo, EmpName, DeptName, Location
    UNION ALL
    SELECT EmpNo, EmpName, DeptName, Location
...

CONCAT is not a safe way to do this because different values could appear to be the same.

Do all the columns have to match?  Or just some?

For now, I'll assume...

There could be a lot of different reasons for latency.  Did this just start recently?  Here are some possible things; (1) should be done first, the others are in no...

We need to see the DDL for Clicks2 and Clicks3 as well.  Particularly if those tables are not clustered uniquely on ClickID, the MERGE join could be less efficient.

As Jeffrey...

For efficiency, you should really just scan the table once to count all columns, as the code generated below does.  I've added the view name as input so the code...

Of course it must vs taking locks.

"and use snapshot isolation."  MEGA-HUGE overhead.  SI should...

Of course it must vs taking locks.

"and use snapshot isolation."  MEGA-HUGE overhead.  SI should only be...

Of course it must vs taking locks.

"and use snapshot isolation."  MEGA-HUGE overhead.  SI should only be used when...

"Switching to alphanumeric" from what?  Int, bigint?  If so, have you used negative numbers yet?  Typically the quick-and-dirty way to stretch values is to use the full range of negative numbers. ...

You can't use a "read-only connection" in/for a proc that does...

Of course it must vs taking locks.

"and use snapshot isolation."  MEGA-HUGE overhead.  SI should only be used when truly necessary.

You need to...

Yes, agree that it would benefit in the read loads. But we should also consider the overhead of CI on write operations. So if the database...

• This reply was modified 4 years ago by ScottPletcher.

It really depends on whether you understand the consequences of NOLOCK.  It can result in a 'dirty read' if there are uncomitted transactions in the database that...