Not a problem. But, if you prefer, add the id...

It's a virtual certainty that the clustering key should be pf, not a meaningless id. That would also give better performance across the board on that table. ...

Were you able to use the rename approach?

Yes, non-trusted constraints could seriously degrade your performance because SQL won't be able to use the index associated with those constraints and thus might have to scan the table or...

But don't you have to save the permissions on those objects before you drop them?

I just rename the existing user table type to some other type name -- which you can then immediately drop -- then (re)create the original type name with the new...

--Edit:

--this provides the years which were serviced;

--could add years not serviced by generating all years, using a tally table,

--and doing a left join from that.

--unfortunately, my work server blocks...

I suppose you could. I think inevitably you will damage some data eventually by trying to have the same column try to contain plain data and encrypted data. ...

Looks ok. But you don't need to do a full count of rows to determine INSERT vs UPDATE:

CREATE TRIGGER dbo.IU_TestSSNs ON dbo.TestSSNs

INSTEAD OF INSERT, UPDATE

AS

SET NOCOUNT ON;

OPEN...

Lynn Pettis (7/23/2015)

---

ScottPletcher (7/23/2015)

---

...

I do say never. Never use ISNULL() in a WHERE or JOIN -- it's never needed, and it can be very harmful.

Never specify a unicode literal...

Lynn Pettis (7/23/2015)

---

ScottPletcher (7/23/2015)

---

Here's an alternate method: with only two columns, I'd probably skip using CROSS APPLY:

DECLARE @starting_year int

SET @starting_year = 2012

SELECT subquery.ID, e.NAME, subquery.Year, subquery.Max_Date

FROM (

...

Lynn Pettis (7/23/2015)

---

ScottPletcher (7/23/2015)

---

Lynn Pettis (7/23/2015)

---

WayneS (7/23/2015)

---

Good luck...

Here's an alternate method: with only two columns, I'd probably skip using CROSS APPLY:

DECLARE @starting_year int

SET @starting_year = 2012

SELECT subquery.ID, e.NAME, subquery.Year, subquery.Max_Date

FROM (

SELECT ID, Year,...

But a temp table / table variable won't begin to match the performance of a true array.

Lynn Pettis (7/23/2015)

---

WayneS (7/23/2015)

---

Good luck with adding that...