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,

...

Create a replacement values table, and use it to do the data replacement.

CREATE TABLE dbo.data_replacements (

column_name varchar(30) NOT NULL,

current_value varchar(100) NULL,

...

You'd have to generate dynamic SQL to do that. First, get the highest count as it exists ni your current data. Then modify the above overall structure to...