I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005: Synonyms in T-SQL (analogous to Aliases in .Net)

If you're a .Net developer aliases should be very familiar to you.

if they're not here is a reminder:

using MyAlias = This.Is.My.Full.Namespace.MyClass;
MyAlias m = new MyAlias(); // MyAlias is of course of type MyClass

 

Well it seems SQL Server 2005 added a similar functionality to T-SQL. It's called Synonyms.

You can use them in all CRUD operations as well as in sub-selects and dynamic SQL

What's interesting is that synonyms are evaluated at run time and the binding between the synonym and an object is by name only.

This means that you can drop the object referenced by the synonym at any time, but you'll get an error only at run-time.

They are useful because they provide a level of abstraction in your app, since you can change the name or even a location of the object

referenced by the synonym.

And not to mention having less to type as seen in this example:

USE tempdb;
GO
-- Create a synonym for the Product table in AdventureWorks.
CREATE SYNONYM MyProduct
FOR AdventureWorks.Production.Product;
GO

-- Query the Product table by using the synonym.
USE tempdb;
GO
SELECT ProductID, Name 
FROM MyProduct
WHERE ProductID < 5;
GO

 

These database objects can be referenced by a synonym:

- SQL stored procedures, scalar, table-valued and inline-tabled-valued functions

- Views

- Local and global temporary tables

- Replication-filter and extended stored procedures

- CLR stored procedures, table-valued, scalar and aggregate functions

 

Don't know in how widespread use this will be but it's a nice feature that can come in handy.

 

kick it on DotNetKicks.com

Print | posted on Monday, November 26, 2007 9:37 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server 2005: Synonyms in T-SQL (analogous to Aliases in .Net)

I'm currently mulling the idea of using Synonyms to assist with maintaining different versions of database objects for different clients.

If there is a better way of achieving this goal I'd love to hear your views!

SCENARIO:

We have a database which currently has 3 different versions for 3 different clients. 80% of the database code is common between all 3 versions and we could probably get this figure up to 90% with a little bit of rejigging.

The other 10% will be more difficult to get into a common model without lots of hardcoded conditional logic.

My idea is to try and apply the template pattern.

I intend to create one core development database with all the common code in the dbo schema then to encapsulate the client specific implementations will be in a schema named after the client.

e.g.

clientX.spCalculateProfit
clientY.spCalculateProfit

These client specific objects are never referenced directly but instead a Synonym is created dbo.spCalculateProfit pointing to (say) clientX.spCalculateProfit.

The main draw back I see with this is persuading my work mates it is not too complicated - so I intend to write a tool to automate/hide a lot of this.

I stumbled upon this blog whilst researching addins as I intend to write an SSMS addin to allow the "Active Schema" to be selected which will ensure all synonyms are pointing at the desired schema.

The addin will also assist with some of the tedium of branching existing shared objects and allow the production client specific schemas to be generated through a "publish" option. This will create a copy of the database schema for each client and perform the following customisations.

1. All objects not in the client or dbo schema will be dropped.

2. All synonyms will be dropped.

3. All objects in the client schema will be copied to the dbo schema.

4. All objects in the client schema will be dropped.

2/15/2008 11:22 PM | Martin Smith
Gravatar

# re: SQL Server 2005: Synonyms in T-SQL (analogous to Aliases in .Net)

if you have 3 db to 3 different clients then just name your objects the same and also put everything under the same schema. this way you don't have to chantge anything.

if you have to go with the synonyms i don't really see a problem.
just be carefull about droping the underlying objects because then the synonym won't be pointing to anything and will fail at run time.
2/15/2008 11:40 PM | Mladen
Gravatar

# re: SQL Server 2005: Synonyms in T-SQL (analogous to Aliases in .Net)

Hiya,

The reason why I think synonyms may be useful is because then we only need to maintain one database model instead of three.

So adding new common functionality will only need to be done in one place and I believe it will help prevent the database models diverging unnecessarily and it will help keep the code base as unified as possible.

The synonyms/schemas are more of a convenience to allow the project's unified "master" development database to be ran in a particular client mode. The script to publish the specific production versions will remove the synonym (dbo.spXyz) and instead create a stored procedure dbo.spXyz which is the client specific implementation.
2/16/2008 12:15 AM | Martin Smith
Gravatar

# re: SQL Server 2005: Synonyms in T-SQL (analogous to Aliases in .Net)

cool... can't say much more than that :)
haven't really thought of using them that way but it sounds good...
2/16/2008 12:31 AM | Mladen
Gravatar

# re: SQL Server 2005: Synonyms in T-SQL (analogous to Aliases in .Net)

Just to update this. The synonyms didn't end up working quite as seamlessly as I had hoped.

We have lots of ADO calls to stored procedures in the database.

When routing these calls through synonyms the parameters never get passed in the eventual stored procedure call. I assume that ADO enumerates the parameters belonging to the object and concludes that for a synonym it is always zero (rather than looking at the underlying object the synonym points to).

The profiler trace looks like this for the ADO call...


exec [MyDatabase]..sp_procedure_params_rowset N'MyProcedure',1,N'dbo',NULL
go
SET NO_BROWSETABLE ON
go
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,NULL,N' EXEC dbo.MyProcedure ',1
select @p1
go
SET FMTONLY ON exec sp_execute 1 SET FMTONLY OFF
go
exec sp_unprepare 1
go
SET NO_BROWSETABLE OFF
go
exec dbo.MyProcedure
go
4/13/2008 5:58 PM | Martin Smith
Gravatar

# re: SQL Server 2005: Synonyms in T-SQL (analogous to Aliases in .Net)

thanx man wery nice
5/30/2008 11:20 PM | oyun
Gravatar

# re: SQL Server 2005: Synonyms in T-SQL (analogous to Aliases in .Net)

I created a synonym by using "CREATE Synonym syn_US FOR [Server_2].Database1.Table2" keyword.
and it is executed success fully. But when i execute the query " select * from syn_US" i got the error message "Msg 208, Level 16, State 1, Line 1
Invalid object name '192.9.200.102.Allergan_100.TBL_MST_AGN_BC_HCP'.
"
4/8/2009 6:32 AM | angel m eldhose
Gravatar

# re: SQL Server 2005: Synonyms in T-SQL (analogous to Aliases in .Net)

You might consider using different schemas for each customer.

Things common go into a single schema.

Things that differ belong in the customer specific schema.

Then when user performs their action, it looks first for the object in the customer specific schema, then in the common one.

This makes it possible to maintain the same code on all instances while separating user actions.

The command SET USER [UserName] allows you to take on the credintials of that user, allowing you to test how the database acts under their configuration.

This is probably more efficient and easier to manage.

My 2 Cents

Ben
9/14/2010 3:47 PM | Ben
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET