SQL Server 2005: Two Little Known Features That Matter Big!
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
SQL Server 2005 introduces a host of new features, some well-known; some not as familiar. This article introduces you to two exciting options: synonyms and non-clustered index included columns.
Synonyms help you create aliases for your objects. They simplify the naming of remote objects as well as objects that are in another database or another schema. They can also help you create a layer of abstraction for the database objects—thus allowing you to swap the underlying objects without affecting any code that references the objects.
Non-clustered index included columns help improve query performance. This feature is totally new and (as far as I know) does not exist in other database products. It is similar to the indexed view feature of SQL Server 2000, but is much easier to plan implement and maintain.
Synonyms
You probably already know what synonyms are. In fact, they are harder to pronounce than to understand (one of my colleagues keeps calling them "cinnamons"). No marks to Microsoft for figuring out this one. But better late than never, right?
Synonyms are aliases for other objects. And here is an example. Directly access the table that is on another server (server=myserver, instance=SQLBETA):
USE TEMPDB SELECT * FROM [myserver\SQLBETA].pubs.dbo.authors
Using a synonym:
USE TEMPDB CREATE SYNONYM synGetProductName FOR [myserver\SQLBETA].pubs.dbo.authors SELECT * FROM synGetProductName;
In previous SQL Server versions, most developers/DBAs got around this by creating a view! But you can't do that for stored procedures and functions. For example, you can do something like this only in SQL Server 2005:
-- Create a function to return author name, given au_id
USE PUBS;
GO
CREATE FUNCTION [dbo].[fnGetAuthorName](@au_id [varchar](11))
RETURNS [nvarchar](60) WITH EXECUTE AS CALLER
AS
begin
DECLARE @name nvarchar(60)
SELECT @name = au_lname + ', ' + au_fname
FROM authors
WHERE au_id = @au_id;
RETURN @name;
end
GO
USE TEMPDB
GO
-- use the fully qualified name of the function
SELECT pubs.dbo.fnGetAuthorName('172-32-1176')
-- use a synonym to access the object
CREATE SYNONYM synGetAuthorName FOR pubs.dbo.fnGetAuthorName;
SELECT dbo.synGetAuthorName('172-32-1176')
A synonym is also convenient if you need to repoint to another server (maybe for QA/testing). Just modify your synonym to point to the new server! And SQL Server 2005 doesn't care if you change the base object and replace it with a completely different object. For example, you can initially point to a table and later change it to a view.
Note that the object does not need to be remote; it can exist on another database in the same server or even in another schema in the same database as the base object. So you can do this:
-- Create a synonym for an object in another database USE TEMPDB CREATE SYNONYM synObjectInAnotherDB FOR pubs.dbo.authors -- assuming aschema is another schema in pubs database USE pubs CREATE SYNONYM aschema.synObjectInAnotherSchema FOR dbo.authors -- Create a synonym for an object in the same schema USE PUBS CREATE SYNONYM synAuthors FOR authors;
Look out for one "gotcha" (this cannot be emphasized enough): The base object need not exist at synonym creation time. SQL Server checks for the existence of the base object at runtime. If you make a spelling error while typing the object name, the synonym will still be created, but you will get an error when you actually access the object. Many times, you might find that this is the reason why your newly created synonym will not work.
Synonyms can be created for tables (even temporary and global temporary tables), views, procedures, and functions. Note that you cannot create a synonym for a synonym (sorry, all you tricksters).
NOTE
If you create a synonym for a synonym, the creation succeeds, but when you actually access the synonym, you will get an error.
References to synonyms are not schema-bound; therefore, a synonym can be dropped any time (that is, if the synonym is used in a procedure or function, you will not get an error when you drop the synonym). However, by dropping a synonym, you run the risk of leaving dangling references to the synonym that was dropped, and these references will be found only at runtime.