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...

Very surprising that SQL doesn't the index on which it can do almost a full seek.

Do you really need the "PersonID<>0", or could you remove that? I believe that...

Another option to generate code to be run on the other server:

DECLARE @view_template varchar(8000)

SET @view_template = '

CREATE VIEW [$schema$].[$table$]

AS

SELECT * FROM [SourceDatabase].[$schema$].[$table$] UNION ALL

SELECT * FROM [ArchiveDatabase].[$schema$].[$table$]

GO

'

SELECT REPLACE(REPLACE(@view_template,

...