Does creating a synonym copy the table?

No. It creates an alternative reference rather than another set of rows.

Does it grant access automatically?

Not by itself. The applicable permission rules still govern use of the target.

Can an unchanged synonym name return changed results?

Yes. The target's contents or the target mapping can change, subject to the database's supported behavior.

AI Glossary / Data engineering and architecture / Advanced

Synonym (database)

Definition

A database synonym is an alternative name for another database object, such as a table, view or stored procedure, in a system that supports this feature. It lets applications refer to the object through a separate name. A synonym does not, by itself, copy the target's data or transform its contents. Supported target types, name resolution and permission behavior depend on the database product.

What it means

An application may benefit from a stable, convenient name even when the underlying object's owner or location is more complicated. A synonym provides a layer of naming indirection. For example, a reporting tool can refer to an agreed name whose target is documented separately. That convenience is useful only if the target continues to satisfy the application's expectations. Repointing the name to a different object does not automatically preserve column meanings, allowed operations or performance. The name is an interface to the target, not a guarantee that every possible target is interchangeable.

How it works

The database stores a mapping from the synonym name to a target object and resolves references according to its naming rules. Oracle supports private and public synonyms and explicitly states that a synonym does not replace the privileges needed to use the underlying object. Its documentation also permits creating a synonym before the target exists. SQL Server likewise documents deferred checks on its base object. Consequently, successful synonym creation is not sufficient proof that the application can use it successfully. Product-specific access and resolution rules must be tested with the intended application identity.

Three practical examples

A reporting application uses a convenient name for a table owned by another database schema, with appropriate access configured separately.
A deployment process maintains an application-facing name while checking that its target has the required columns and meaning.
A maintenance review traces a synonym to its actual object when a query unexpectedly reads from a different dataset than the operator intended.

Think of it this way

Imagine an office directory that lists a department under a short familiar name and points to its current room. The directory entry helps people find the department, but it neither creates another department nor gives visitors permission to enter. If the entry points to the wrong room, its familiar name can be misleading. This illustrates naming, target selection and access as distinct concerns.

Worked example

Consider an original fictional reporting schema with a synonym named course_summary pointing to reporting.course_summary_v1. The target contains three rows with enrollment counts 12, 8 and 10. A permitted query through the synonym sums those counts to 30, just as a query against that same target would. Creating the synonym has not created three additional rows or doubled the total to 60. Suppose a replacement target contains counts 12, 8 and 11. If the synonym is changed to point there using the database's supported procedure, the corresponding total becomes 31. The unchanged name does not imply unchanged data. This illustration assumes compatible columns and permissions; it does not prescribe portable syntax for changing synonyms across database products.

Illustrative case study

In a fictional AI reporting assistant, generated queries use an agreed database synonym. During a release, the synonym's target is changed to a new reporting object. A validation query runs successfully, but a business check reveals that the new object includes provisional enrollments that the old object excluded. The team adds a semantic comparison to its deployment checks and documents the target's inclusion rule. It also tests using the assistant's actual database identity. The scenario shows why a valid name and successful execution do not prove an unchanged business meaning. It is not a report of a real product failure.

Common misconceptions and mistakes

A synonym is not the same as a SQL query alias that exists within a particular statement. It also differs from a view: the synonym itself supplies an alternative name, rather than defining a query that filters or reshapes data. A public synonym does not automatically grant everyone access to its target. Another mistake is assuming that all database products support identical synonym behavior, including chaining, remote targets or alteration. Finally, a synonym is not a backup. If the target is unavailable or unusable, the alternative name does not provide an independent copy of its contents.

Business use

Synonyms can simplify application naming and reduce the number of places where a target's detailed name must appear. Use that convenience with clear ownership and target documentation. For an AI query service, maintain an approved vocabulary and verify what each name resolves to under the service's actual access context. When changing a target, check expected columns, business meaning and representative results. A naming layer can make a migration easier to manage, but it should not hide a meaningful change in what users receive. Record the mapping so operational teams can investigate unexpected results without guessing from the short name.

Check your understanding

What total does the first toy target produce?

Answer: Thirty.

Why does creating a synonym not produce a total of sixty?

Answer: No data copy was created.

What changes the total to thirty-one in the second scenario?

Answer: The name points to a target with a different count.

Why did the fictional team add a semantic check?

Answer: Successful execution did not reveal that provisional enrollments were now included.

Related terms

Database object
Schema
View
name resolution
object privilege
query alias

Learn next

Define a fictional application-facing name and two possible target objects. State which properties must remain compatible if the target changes, including the meaning of returned rows. Design one structural check, one result check and one access check. Explain why passing only the name-creation step would not establish that the application is ready.

Sources

Oracle Database SQL Language Reference, CREATE SYNONYM: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-SYNONYM.html Microsoft Learn, CREATE SYNONYM, indexed base-object permission-check description: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-synonym-transact-sql?view=sql-server-ver16 The enrollment-count example, directory analogy and reporting-assistant scenario are original educational illustrations. Oracle documentation was retrieved directly; the SQL Server point was checked in indexed documentation. Product-specific synonym rules are not presented as a universal SQL guarantee.

Previous: Synchronous context-free grammar Next: Synonyms