\n| \n SQL scalar function<\/p>\n<\/td>\n | <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nCreating, using, and managing synonyms<\/h2>\nTo better understand how to create, use and manage synonyms, I\u2019ll go through a few examples. The examples will show you how to use synonyms that support different business situations, as well as how to manage synonyms.<\/p>\n Coordinating the renaming of an object<\/h3>\nOnce a database object has been created, and lots of application code has been written that references the object, it becomes a nightmare to rename the object. The nightmare comes from the amount of effort and coordination work required to make the name change without the application failing. If just one place is missed when coordinating the rename, the outcome could be disastrous. This is where a synonym can help minimize the risk associated with renaming a base object.<\/p>\n Suppose there is an application that has been built for a company that employs plumbers. Each plumber who has ever worked for the company is stored in a table named dbo.Plumber<\/em>. The company has written a lot of code around this table to manage their business and their plumbers. Then one day, the company decides to expand its business to include electrical work. This expansion of the business now requires modifying their existing application code to support the new line of work. Additionally, they plan to write a lot of new code to support their expanding business. One of the modifications they want to make is to change the name of the dbo.Plumber<\/em> table to dbo.Employee<\/em> as soon as possible. This way, they can start using this new table name for any new code they plan to write, while they coordinate the name change for all the existing code.<\/p>\nBy using a synonym, they can make the name change now, and ensure all the existing code doesn\u2019t fail. Then as time allows, they can slowly change the old code to use the new table. Once all the legacy code has been changed to use the new name, then the synonym can be dropped. To rename the dbo.Plumber <\/em>table and create the synonym, a DBA could execute the code in Listing 2.<\/p>\nListing 2. Renaming base object and creating synonym to support a name change<\/strong><\/p>\nEXEC sp_rename 'dbo.Plumber', 'Employee';\r\nCREATE SYNONYM dbo.Plumber\r\n FOR dbo.Employee;<\/pre>\nThe code in Listing 2 first renames the exiting dbo.Plumber <\/em>table to dbo.Employee <\/em>and then creates the synonym to support the renaming effort. <\/em>This code will run very quickly to minimize the risk of any errors occurring between the time the dbo.Plumber <\/em>table is renamed and the dbo.Plumber<\/em> synonym is created.<\/p>\nCreating a synonym for security purposes<\/h3>\nIn this example, assume that a company has a security policy that does not allow developers to update production data. They have this policy so developers don\u2019t mistakenly update production data when they thought they were working in development. However, there are situations, like bad data issues, where developers need to update a production table. When this situation occurs, a synonym can be created, with appropriate permissions, to allow developers to update production so they can fix the data issue.<\/p>\n Suppose there was a data issue in the dbo.Article <\/em>table that needed to be resolved by issuing SELECT<\/code>, INSERT<\/code>, UPDATE<\/code> and\/or DELETE<\/code> statements in the production environment. To provide the developers update access to resolve this issue, a synonym with appropriate permissions could be created. To accomplish this the code in Listing 3 could be run.<\/p>\nListing 3. Code to create synonyms and permissions.<\/strong><\/p>\nCREATE SYNONYM dbo.Dev_Article\r\n FOR dbo.Article;\r\nGRANT SELECT, INSERT, UPDATE, DELETE \r\n on dbo.Dev_Article TO Developer_Group;<\/pre>\nIn Listing 3, the synonym dbo.Dev_Article <\/em>was created to point to the based table named dbo.Article. <\/em>Once this synonym is created the Developer_Group<\/em> was granted SELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, or DELETE<\/code> permissions on the new dbo.Dev_Article<\/em> synonym. By doing this, the programmers in the Developer_group <\/em>will be able to browse and update the actual table, dbo.Article <\/em>using the synonym dbo.Dev_Article<\/em>. Once the data issues are resolved the synonym can be dropped.<\/p>\nCreating a synonym in another database<\/h3>\nFor this example, suppose there is a company that wants to create a mirrored database in a development environment so that parallel development efforts can take place. By parallel development, I mean one group of developers can work and write TSQL code in one database, while another group of developers can do development work in a different database. The only problem is the company DBA says there isn\u2019t enough disk space to copy all the tables from the existing development database (CurrentDB<\/em>) to the new mirrored development database (NewDB<\/em>). In this situation, the DBA decides he can establish the new mirrored database by creating synonyms in the NewDB<\/em> database to point to each of the big tables that can\u2019t be mirrored due to lack of disk space. The code in Listing 4 shows how to create a synonym in the NewDB<\/em> databases that points to one of those big tables that can\u2019t be mirrored.<\/p>\nListing 4. Creating a synonym that references table in another database<\/strong><\/p>\nUSE NewDB;\r\nGo\r\nCREATE SYNONYM dbo.BigTable\r\n FOR [CurrentDB].dbo.BigTable;\r\nGRANT SELECT, INSERT, UPDATE, DELETE on dbo.BigTable TO Developer_Group2;\r\nGO<\/pre>\nAfter creating this synonym in the NewDB<\/em> database, all the developers in Developer_Group2<\/em> will be able to write SELECT<\/code>, INSERT<\/code>, UPDATE<\/code> and DELETE<\/code> statements against the dbo.BigTable<\/em> as if it resides in the NewDB<\/em> database. Keep in mind when the developers executed code against the dbo.BigTable<\/em> in the NewDB <\/em>database the commands will actually be run against the dbo.BigTable <\/em>in the CurrentDB <\/em>database.<\/p>\nThis also comes into play during a continuous integration build process. Referenced databases may not be in place or have different names during the process and keep the build from being successful.<\/p>\n Creating a synonym to reference objects on another server<\/h3>\nThere are times when an application might need to run some code on one server but reference a table on another server. For discussion, assume a server has limited storage space. Therefore, periodically an archive process is run that moves historical information to a different server, that I will call the archive server. If the application needs to select some data from the archive server, they could use a four-part name and a linked server to reference those objects on the archived server, or they could define a synonym.<\/p>\n The four-part names will work, but they are long names to type. By creating a synonym for these long four-part names, it makes the coding easier and makes these remote server references appear like they are local. The code in Listing 5 shows how to create a synonym that references the four-part table name that resides on the archive server.<\/p>\n Listing 5. Defining a synonym for a table on another server<\/strong><\/p>\nCREATE SYNONYM ClientHistory\r\n FOR ArchivedProd.MyApp.dbo.ClientHistory;\r\nGO<\/pre>\nBy creating this synonym, an application can now use the name ClientHistory <\/em>to reference the client history information that resides on the archived server instead of using the long four-part name ArchivedProd.MyApp.dbo.ClientHistory.<\/em><\/p>\nSynonyms cannot reference other synonyms<\/h3>\nA synonym cannot reference another synonym. To demonstrate, this I will run the code in Listing 6 to create a new synonym in the NewDB<\/em> database that references the dbo.BigTable <\/em>synonym that I created using Listing 4.<\/p>\nListing 6. Creating a synonym that references another synonym<\/strong><\/p>\nUSE NewDB;\r\nGO\r\nCREATE SYNONYM dbo.Second_BigTable\r\n FOR dbo.BigTable;\r\nGO<\/pre>\nWhen I execute the code in Listing 6, it runs without error and creates the new synonym named dbo.Second_BigTable. <\/em> But if I try to execute the SELECT<\/code> statement in Listing 7, I get the error in Figure 1.<\/p>\nListing 7. SELECT statement <\/strong><\/p>\nSELECT * FROM dbo.Second_BigTable;<\/pre>\nFigure 1. Error when trying to use a synonym that references another synonym <\/strong><\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-149.png\") <\/p>\n
The reason I got no error when I created the synonym, but I got an error when I executed the SELECT<\/code> statement is because the code for a synonym is not validated at creation time, but it is validated at run time. This feature of delayed validation is a good thing. Delayed validation allows a DBA to create a synonym in advance of when the actual base object will be created.<\/p>\nChanging a synonym definition<\/h3>\nIf a base table for a synonym is renamed or moved, the synonym definition will need to be altered to reflect the change. The only problem is that there is no ALTER<\/code> SYNONYM<\/code> statement to help support renaming or moving of a based object. To change an existing synonym, it will need to be dropped and then recreated, using the new name and location of the base table.<\/p>\nHaving to drop and recreate a synonym can cause a problem if any permissions have been granted directly to a synonym. When the synonym is dropped the permissions associated with the synonym are also dropped. Therefore, if a synonym needs to be dropped and recreated for some reason, then a DBA will need to make sure permissions placed on the existing synonym are reviewed and documented prior to it being dropped. By doing this, they will be able to recreate the GRANT<\/code> statements to reapply the permissions once the synonym is recreated.<\/p>\nIdentifying the synonyms in a database<\/h2>\nWhen working with a new database, it is always wise to review it to see if any synonyms have been defined. Without this review, you might assume a synonym reference is just a reference to a base object and thus cause confusion. There are two different methods for reviewing the synonyms defined in a database.<\/p>\n The first one is to use SSMS. To find the synonym definitions for a database using SSMS, follow these steps.<\/p>\n \n- Open up SSMS<\/li>\n
- Expand the Databases folder<\/li>\n
- Expand the database that is being reviewed<\/li>\n
- Expand the Synonym item<\/li>\n<\/ol>\n
Figure 2 shows the synonyms added to the NewDB database.<\/p>\n ![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-150.png\") <\/p>\n
Figure 2. The synonyms in the NewDB database<\/strong><\/p>\nThe other method to display synonyms in a database is to write a query against the sys.synonyms<\/em> catalog view in the context of the database where you want to explore for synonyms. An example of how to do that can be found in Listing 8.<\/p>\nListing 8. Displaying all Synonyms in a Database using TSQL<\/strong><\/p>\nSELECT * FROM sys.synonyms;<\/pre>\nPrecautions when using synonyms<\/h2>\nIf you plan to use synonyms, then you should be aware of a few issues with using them. Here is a list of some of those things that might trip you up when using a synonym.<\/p>\n Cannot retain permissions on a synonym when they are dropped.<\/p>\n Using synonyms causes confusion when someone doesn\u2019t realize that synonyms are being used.<\/p>\n When creating synonyms that referencing objects on other servers for testing and debugging purposes you need to make sure you don\u2019t accidentally update the data on those other servers unless of course, that is what you intend to do.<\/p>\n Depending on the version of SQL Server being used, IntelliSense may not pickup that a name is a synonym.<\/strong><\/p>\nYou cannot reference a synonym across a linked server. If this is tried, you will get an error like the one shown in Figure 3.<\/p>\n ![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-151.png\") <\/p>\n
Figure 3. Error when trying to reference synonym across a linked server<\/strong><\/p>\nSQL Server Synonyms<\/h2>\nSynonyms are a great way to simplify your code for a number of different reasons, like shortening up those long object names, or references tables in other databases, or instances of SQL Server. Synonyms help in the coordination of renaming base objects or changing their location over time. By creating a synonym, the reference to the synonym might look like a local table, when in fact it resides in a different database or even a different instance of SQL Server. Even with the confusion that can arise when using synonyms, the trade-off of using one might be well worth it for several situations. Next time you have to rename an object or copy an environment, you might want to consider whether a synonym will make this effort easier and more seamless.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" SQL Server synonyms can be used to permanently alias database objects in the same or another database. In this article, Greg Larsen demonstrates how to use synonyms and the many reasons to consider using them.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[68856,124838],"coauthors":[11330],"class_list":["post-88435","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-provisioning","tag-synonyms"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/88435","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=88435"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/88435\/revisions"}],"predecessor-version":[{"id":88440,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/88435\/revisions\/88440"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=88435"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=88435"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=88435"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=88435"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} |