Synonyms

Aaron Bertrand discusses synonyms:

Let’s say you have a table called dbo.BugReports, and you need to change it to dbo.SupportIncidents. This can be quite disruptive if you have references to the original name scattered throughout stored procedures, views, functions, and application code. Modern tools like SSDT can make a refactor relatively straightforward within the database (as long as queries aren’t constructed from user input and/or dynamic SQL), but for distributed applications, it can be a lot more complex.

A synonym can allow you to change the database now, and worry about the application later – even in phases. You just rename the table from the old name to the new name (or use ALTER TABLE ... SWITCH and then drop the original), and then create a synonym named with the old name that “points to” the new name

I’ve used synonyms once or twice, but they’re pretty low on my list, in part because of network effects:  if I create this great set of synonyms but the next guy doesn’t know about them, it makes maintenance that much harder.

Related Posts

Matrix Transposition In T-SQL

Phil Factor has some fun transposing a matrix using T-SQL: What I’m doing is simply converting the table into its JSON form, and then using this to create a table using the multi-row VALUES  syntax which paradoxically allows expressions. The expression I’m using is JSON_Value, which allows me do effectively dictate the source within the table, via […]

Read More

Joins And Parentheses

Shane O’Neill walks through different ways of grouping tables in a SQL query: Asker: that’d be awesome if i can inner join two other tables instead of the table mentioned after FROM keyword Me: …wait, what? A: He’s asking t1 left join t12 t1 left join t13 t12 inner join t13 M: em…it’s possible but it’s…iffy […]

Read More

Categories

March 2016
MTWTFSS
« Feb Apr »
 123456
78910111213
14151617181920
21222324252627
28293031