It's better to have it as a constraint:

create table tlb_test (

id int identity(1,1), -- if you need it ???

RefType varchar(20) null,

EngCode varchar(20) null,

...

NULL is a kind of wildcard.

If you run "DIR *.*" you get list of all files with any name and any extention.

But if you run "DIR *. " you get...

No need to manage transactions or update with deleted values.

Just raise an error.

Error raised inside of trigger will roll back everything, including UPDATE/INSERT itself.

Each insert is separate transaction.

Which involves locks, reindexing, etc.

1st option (union all) create single transaction.

2nd option creates as many transactions as many rows being inserted.

Does not really matter.

If table is heavily used it will be sitting in memory anyway because of its small size.

If it's used occasionally those microseconds needed to read extra extent...

Well, I'm probably lucky, but I'm not THAT lucky.

I'm not sure there is any scientific proof that such...

Save space?

Heh...

Ask him: how much space?

Then, how much this space will cost?

Then give him 5 bucks and BUY that space for yourself.

😎

1. and 2. PK Clustered is on N.

3. All of them. Table is static - so there is no harm in over-indexing.

Yes, there are composites: WeekOfMonth + Date,...

My "essential" Tally table contains not only numbers but column of characters ( NCHAR(N) ), dates ( DATADD(dd, N, 0) and other date related values (DW, day of year, week...

Now try to estimate performance of updates ad inserts on this table.

Take into account locks to be applied not only on table pages but on index pages as well.

Replace IIf([Extension]<50,"0" & [Extension],[Extension])

with CASE WHEN [Extension]<50 THEN '0' + [Extension] ELSE [Extension] END

Should be all right then.

Query from my version of the table will look like this:

SELECT *

FROM t_all_combination T0

WHERE EXISTS (

select 1

from t_all_combination...

RBarryYoung,

the way you used NOLOCK makes it's a table alias.

To make it a table hint you must use (NOLOCK), better (WITH NOLOCK)

I guess (can only guess here) OP is interested in id and date values only.

If it's true then my query will give...

Effective query starts from CREATE TABLE statement.

The way the table is built there is no way to get fast response on a query like yours.

Convert it into something like this:

CREATE...