Home > Articles > Data > SQL Server

SQL Server 2005: Two Little Known Features That Matter Big!

  • Print
  • + Share This
  • 💬 Discuss
Like this article? We recommend
SQL Server 2005 brings a host of new features to the table. Ravindra Okade details a couple of nuggets that are not easy to find but can be very useful to every developer and DBA. Synonyms are very handy for managing database objects. And non-clustered index included columns, although a mouthful, is a unique feature that can drastically optimize queries.

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.

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus