Sql-server – Mandatory to use “Three Parts Name” in T-SQL statements of SP

sql serversql-server-2016

Using three parts name in SQL statements is a good practice it helps in object identification and performance improvement as well. It can also help for many other purposes like if you want to move SPs,View Functions to different database with same T-SQL statement..

So I wanted to create a policy which can enforce developers to use "Three Part Naming" when they'll create SPs, Views and functions.

Is there any inbuilt policy or way in SQL Server 2016 which can help me out?

Response will be appreciated.. 🙂

Best Answer

Using three-part names in queries that reference objects in the current database is a terrible practice and you should create a rule against it. The most frequent problem with it is that it prevents you from having two instances of your application database on a single server, and can lead to unintentional cross-database access.

Using two-part names in dynamic SQL has some, slight, performance benefit as the object name resolution doen't have to check the user's default schema and then the dbo schema. But in a view or stored procedure is not as helpful as object name resolution uses the object's schema. And in the paradigm case of a proc/view in dbo referencing a table in dbo it doesn't help at all.

Related Question