Synonyms and other Oracle stuff

Every now and then I meet Oracle specialists at customers sites. Sometimes they start a good discussion on the differences between the Oracle engine and the SQL Server engine. I don't mind a good discussion on the differences but sadly lots of Oracle guys compare 11G to SQL Server 6.5. (btw don't get me wrong : Oracle has a great engine, but we definitely closed the gap!)

   

Two features they always refer to are schema's and synonyms. Schema's are very important in Oracle. Basically Oracle uses schema's like SQL Server uses databases. When you install an Oracle you have 1 database and you can create schema's in there to separate different data. Since SQL 2005 we also have schema's. Nothing will stop you for using them like you did in Oracle. But usually they are used to group related objects in 1 database. I must say I do not see many databases where they are used at all. Too bad, because it's a great way to group objects which share the same security. You only have to give permissions on schema's and not on the separate objects anymore.

   

Synonyms is another thing. In Oracle you create synonyms to use objects from 1 schema in another schema. In SQL Server you can also create synonyms. Normal practice is to create synonyms in the model database. That way, every new database created after that already has the synonym. After a restart it's even in the tempDB. In SQL Server synonyms make objects available across databases and schema's. Instead of using the 3 part name like AdventureWorks.Person.Address you can use the name of the synonym. For synonyms goes the same as for schema's: I almost never see them in customer databases.