I wouldn't automatically add a leading %. That forces a full scan which is often unnecessary if the requestor knows what the first name starts with.

Instead, allow the user...

You should be able to update all rows at once, like so:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[tr_TaxID]

ON [dbo].[Item]

after INSERT, UPDATE AS

UPDATE [dbo].[Item]

SET

taxid...

I would just use a CASE statement.

But you raised a good point I overlooked.

If you do need only current year's data, I should have added a WHERE clause with a...

SELECT

name,

SUM(CASE WHEN date_column >= CAST(YEAR(GETDATE()) AS char(4)) + '0101'

THEN total ELSE 0...

Not 100% sure, don't think it's a lot longer. Neither one is very fast really rebuilding 🙂 .

For (almost) pure reads, as O/S and SQL pgms are, RAID5 should be faster than RAID10, with the same number of spindles for each RAID.

So, I'd say use...

An UPDATE statement will always cause some type of lock(s) on the table. SQL must do locks to insure the integrity of the UPDATEs.

Depending on the number of rows...

If I understand correctly:

((COALESCE([prefix]+'-'), '')+right('0000'+COALESCE(convert([varchar],[numeric],(0)), ''),(4)))

is persisted =yes

For XML data, you are very likely better off specifying XML as the data type rather than varchar(max), since SQL optimizes XML storage.

When did SQL Server start doing these "auto sorts" for clus indexes?

That's primarily what I meant. I thought it was conditional. I've seen SQL not do it.

To me it's much safer to code your...

I thought it depended on whether SQL thought it was "necessary" or not.

Whether the number of rows, etc., met whatever magic criteria SQL went by -- which are not documented...

Really? Interesting. I don't recall that. Would be nice tho.

I think the order does matter for the clus index and whether it gets fragmented or not. SQL is coded to recognize sequential inserts and not cause page splits...

Very interesting post by Conor.

I referred to the clus index only. I assumed you can't control any other index(es), nor a heap (the ORDER BY would be irrelevant to...