SQL Server parallels to Oracle Alter Table Set Column Unused?

Question

Is there a parallel in Microsoft SQL Server (2005, preferably) for the Oracle functionality of setting a column to be unused? For example:

ALTER TABLE Person SET UNUSED Nickname;

Nothing turns up in searches, so my thought is this feature must be Oracle specific.

Answer 1

There is no equivalent statement, but depending on your need you could probably write a trigger to roll back any changes if made.

Answer 2

Don't think there's anything like that in SQL server.

You could create a 1:1 relation to a new table containing the hidden columns:

insert into NewTable
select (keycol, Nickname) from ExistingTable

alter table ExistingTable drop column Nickname

That way you still have the data, but the column is in a table nobody knows about.

Alternatively, you could use column level permissions:

DENY SELECT (Nickname) ON ExistingTable TO domain\user
DENY SELECT (Nickname) ON ExistingTable TO public
...

This will return an error when someone tries to read the column. The big disadvantage of this method is that select * will also fail.