If they're always Mondays, why waste resources with table I/O at all? Very simple date arithmetic can tell you what the starting and ending dates are.

You can't avoid blocking, and you don't want to. It is an integral part of SQL's engine to maintain data integrity.

Is there some reason you want to use multiple queries? One query can do this just fine. I've properly clustered the table to match lookup requirements.

DECLARE @MyTable AS...

It's my understanding that modern VINs are indeed unique. The year byte in the VIN code is reused every thirty years, but the 17-digit combination will still be unique.

Btw,...

I'd say varchar ... and now you have a lot of people to slap :-D.

Why on earth do people want characters in a po number??

A vehicle should always have a VIN, and if you look up by VIN, I would go with VIN. Define it as char(17) rather than varchar, though, to save...

If you just need whichever comes first, I suggest:

with addresses

as (select address = 'Elland **REQUIRES BOOKING IN***'

...

You certainly could do that with a trigger. And you'd have the extra benefit of being able to cluster the resulting table exactly as you needed for querying it.

For now you could create a non-persisted computed column you could reference in queries to get the "clean" number. Later you could make it an actual column by doing...

Alvin Ramard (6/2/2015)

---

ScottPletcher (6/2/2015)

---

A scalar function will handle that just fine. Particularly if you do it only once, when the data is inserted. You can add a format...

Is it just the id and address, or is there a customer id or other parent entity for the address? If so, the clustering key should be on (...

A scalar function will handle that just fine. Particularly if you do it only once, when the data is inserted. You can add a format code / pattern...

Dougieson (6/2/2015)

---

Hadn't thought of COUNT(DISTINCT CASE WHEN <column_name> > '' THEN <column_name> END) for the count of values within a possible column, I like that!

I was thinking of using MIN...

To evaluate this, we will need considerably more info about the table and its indexes. Please copy the code below into a "New Query..." window in SSMS, change the...

Maybe something along the lines below. You could save the results into another table to come back and update the main table with the consolidated data.

You could also add:

COUNT(DISTINCT...