Logo
CAR REVIEW
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
Windows Server

SQL Server 2008 : Working with Synonyms

6/29/2011 6:35:33 PM
You can create a synonym in SQL Server that references a base object, and then reference the synonym to perform operations on the base object. You can use a synonym in place of a threeor four-part naming convention. You can also use a synonym to reference objects on a linked server, thereby masking the server name. If the database on the linked server is moved, all you need to do is change the synonym instead of having to change every object where the linked server is referenced.

Following is a list of rules you need to know when using synonyms:

  • You can use a synonym to reference the base object in the following query contexts:

    • SELECT, including subqueries

    • INSERT

    • UPDATE

    • DELETE

    • EXECUTE

  • You cannot reference a synonym that is located on a linked server.

  • You cannot use a synonym in a Data Definition Language (DDL) statement, such as an ALTER TABLE command.

  • Synonyms are not schema bound; therefore, they cannot be referenced by schemabound objects such as these:

    • CHECK constraints

    • Computed columns

    • Defaults

    • Rules

    • Schema-bound views and functions

To create a synonym, right-click on the Synonyms folder located under the database where you would like to create the synonym and select New Synonym from the context menu. This will bring up the New Synonym dialog box, as shown in Figure 1.

Figure 1. New Synonym dialog box

Next, fill in the fields and choose an object type from the drop-down list. Following are the components of a synonym definition:

Synonym Name is the name of the synonym that will be created.

Synonym Schema is the name of the schema where the synonym will be created.

Server Name is either the local server name or the name of a linked server containing the base object.

Database Name is the name of the database containing the base object.

Schema is the name of the schema where the base object is located.

Object Type defines the base object type (view, table, stored procedure, or function).

Object type is a drop-down selection in Figure 1.

Object Name is the name of the base object.

Figure 1 creates a synonym called EmpAddress that references the HumanResources. EmployeeAddress table in the AdventureWorks database on a linked server called KEN-PC. The results of querying the EmpAddress synonym can be seen in Figure 2.

Figure 2. Result set returned by querying the EmpAddress synonym

You can also use T-SQL to create a synonym. The following statement is the equivalent to the parameters provided to create the EmpAddress synonym back in Figure 9-10.

CREATE SYNONYM [dbo].[EmpAddress]
FOR [KEN-PC].[AdventureWorks].[HumanResources].[EmployeeAddress]

You do not need permissions on the base object in order to create a synonym. For that matter, the base object does not even need to exist to create a synonym; all of the security and existence checks are deferred until runtime. Also, the permissions that you set on a synonym apply only to the synonym and not to the base object. For example, when you grant Select permission to a synonym, you are giving permission to query the synonym itself, and not to query the underlying object. You can query the sys.synonyms catalog view to return the metadata about the synonym, including the base object name.

To drop a synonym, all you need to do is execute the DROP SYNONYM command followed by the synonym name or right-click the synonym and select Delete from the context menu.

DROP SYNONYM [schema].SynonymName

You can drop a synonym even if other objects are referencing it. You will not encounter an error until executing the object that references the synonym.
Other -----------------
- SQL Server 2008 : Working with Views -Partitioned Views, Updateable Views & Indexed Views
- SQL Server 2008 : Post-Installation - Preproduction Tasks
- Microsoft PowerPoint 2010 : Expanding PowerPoint Functionality - Simplifying Tasks with Macros
- Microsoft PowerPoint 2010 : Enhancing a Presentation with VBA & Setting Developer Options
- Windows Server 2008 R2 : Manage Disk Storage - Manage Disk Storage Quotas
- Windows Server 2008 R2 : Work with RAID Volumes - Understand RAID Levels & Implement RAID
- Exchange Server 2010 : Perform Essential Public Folder Management (part 3) - Configure Client Connectivity
- Exchange Server 2010 : Perform Essential Public Folder Management (part 2) - Define Public Folder Permissions
- Exchange Server 2010 : Perform Essential Public Folder Management (part 1) - Manage Public Folder Content
- Microsoft Dynamics CRM 2011 : Using Microsoft Dynamics CRM for Outlook - Configuring Synchronization Filters
- Microsoft Dynamics CRM 2011 : Going Offline with Microsoft Dynamics CRM for Outlook
- Microsoft Dynamics CRM 2011 : Deleting Records in Microsoft Dynamics CRM for Outlook
- SharePoint 2010 : Securing a Web Application (part 2) - Managing Web Part Security & Self-Service Site Creation
- SharePoint 2010 : Securing a Web Application (part 1) - How Zones, Web Applications, and Security Work Together to Provide Secure Solutions
- BizTalk 2009 : WCF LOB Adapter SDK (part 6)
- BizTalk 2009 : WCF LOB Adapter SDK (part 5) - Implementing the Message Exchange Handlers
- BizTalk 2009 : WCF LOB Adapter SDK (part 4) - Implementing the Metadata Handlers
- BizTalk 2009 : WCF LOB Adapter SDK (part 3) - Implementing the Connection
- BizTalk 2009 : WCF LOB Adapter SDK (part 2) - UI Logical Grouping
- BizTalk 2009 : WCF LOB Adapter SDK (part 1) - Generating Adapter Skeleton Code
 
 
Most view of day
- Windows Server 2012 : Deploying Storage Spaces (part 5) - Implementing Storage Spaces, Using Windows PowerShell
- Microsoft Lync Server 2013 : Deploying Lync Online - Configuring Dial-in Conferencing, Configuring Lync Properties for User Accounts
- Microsoft Word 2010 : Viewing a Document’s Statistics, Evaluating a Document’s Readability
- Collaborating Within an Exchange Environment Using Microsoft Office SharePoint Server 2007 : Customizing and Developing MOSS Sites
- Games and Windows 7 : Using the Games Explorer (part 3) - Rating Your System's Performance
- Windows Server 2003 on HP ProLiant Servers : File and Print Services, Selection of ProLiant Servers for the Enterprise
- Windows Phone 8 : Designing for the Phone - Blend Basics (part 4) - Working with Behaviors
- Windows Server 2003 : Protecting Hosts with Windows Host Firewalls - Firewall Basics
- Windows Phone 8 : Configuring Basic Device Settings - Accessibility (part 2) - Enabling the Screen Magnifier, Using Speech for Phone Accessibility
- Windows 7 Mobility Features : Power Management (part 1) - Battery Meter, Power Plans
Top 10
- Windows Phone 8 : Scheduled Tasks - Scheduled Task API Limitations
- Windows Phone 8 : Scheduled Tasks - Updating Tiles Using a Scheduled Task Agent
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 5) - Editing an Existing To-Do Item
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 4) - Creating the To-Do Item Shell Tile, Saving a To-Do Item
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 3) - Debugging Scheduled Tasks
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 2) - TodoService, TodoItemViewModel
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 1) - TodoItem,TodoDataContext
- Windows Phone 8 : Scheduled Tasks - Using Scheduled Tasks
- Windows Phone 8 : Scheduled Tasks - Background Agent Types
- Windows Phone 8 : Windows Phone Toolkit Animated Page Transitions - Reusing the Transition Attached Properties
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro