Column Level Synonym

Question

Post reply

Column Level Synonym

dt293

Default port

Points: 1412
More actions
April 29, 2008 at 9:05 am

#184931

Hi all,
Is it possible to create a column level synonym in SQL 2005?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Do you mean something like:

select Col1 as Name

from dbo.Table1

Or do you mean an actual synonym? As in:

create synonym Name for dbo.Table1.Col1

If so, then no, that syntax doesn't do what you want. (It creates the synonym, doesn't raise an error, but selecting it doesn't work. It's assuming that dbo.Table1.Col1 is a table or view.)

(The first option is called an "alias", and does work.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dt293 Default port Points: 1412 More actions · Answer 2

Thanks for your reply.

An alias is not what I need in this instance but you have answered my question regarding the column level synonym.

I did try to create the synonym the same way you did but could not select it using the synonym name, I just assumed I had written the create statement code incorrectly.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Per Books Online, synonyms can be created for:

Assembly (CLR) Stored Procedure

Assembly (CLR) Table-valued Function

Assembly (CLR) Scalar Function

Assembly Aggregate (CLR) Aggregate Functions

Replication-filter-procedure

Extended Stored Procedure

SQL Scalar Function

SQL Table-valued Function

SQL Inline-table-valued Function

SQL Stored Procedure

View

Table (User-defined)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dt293 Default port Points: 1412 More actions · Answer 4

Suprisingly a synonym object is created when you issue a create statement against a column, but the 'object type' field is empty in the synonym properties.

Thanks again for your help.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

You could add a calculated field to a table to have two fields in the table with the same data and two different names. Or, you could create a view.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Michael Earl (4/29/2008)
You could add a calculated field to a table to have two fields in the table with the same data and two different names. Or, you could create a view.

The calculated field will only work is you want READ-ONLY access to the data in the column. You can't update a calculated field, so using the "other name" would be a problem in that setting.

The view would likely be better.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?