tip 2 : you say schemata

Use a schema to isolate your application’s database code

because

you will centralise the database code used by your application, making it much easier to read, edit, maintain, and even (when you retire an app) delete

first create your schema

Or get your friendly DBA to do it

use [AdventureWorks2014]
go

if EXISTS (select schema_name from  information_schema.schemata where schema_name = 'myApplication1' ) 
begin
	drop schema [myApplication1]
end
go

create schema [myApplication1]
go

refactor

I think it’s fair to warn you that tip 2 is at its most effective if you decided that tip 1 (access the database only via stored procedures) was a good idea. If your application already accesses the database via a set of stored procedures purpose-built for that application, then it should be child’s play to recreate those same procedures under the new schema.

But wait, I hear you cry (that’s a lie, I can’t hear you at all), what if our app called a stored procedure that was used elsewhere, by other applications or database objects? Or what if we didn’t act on tip 1? What if we inherited an app that has miscellaneous database calls and raw SQL scattered throughout? Are we doomed to miss out on this excellent tip?

That’s a fair question, and the answer is yes, possibly doomed. In that case my advice would be to do what you can right now, and refactor later, whenever you get the chance. Don’t despair! OK, maybe despair a little bit.

careful

Unless you’re just starting out with a new database then it is of course possible and even likely that the SQL used by your application will be used elsewhere, by other apps, by other database objects. So trampling on that SQL by renaming it will not make you the object of universal hero-worship, to say the least. If your app calls SQL (even a stored procedure) that’s used elsewhere by other processes then what you should do is create a new stored procedure under your new schema. Use that stored procedure as a wrapper for the original SQL, and have your app call that new procedure instead.

what to do now

This is a tip that will have no effect on the speed and efficiency of anything at all. However, just like tip 1 it will make your life much easier when it comes to updates and changes. As the typical working life of a developer often consists of exactly that, this tip is worth using.

What can you do in order to get the benefit of this tip? First of all, decide on a schema name that unambiguously identifies database objects as belonging to your app.

Secondly, if you possibly can then create new stored procedures that correspond to all of your existing database code, but which fall under your new schema. You should only ever use these new procedures in the app to which the schema refers.

Finally, as for tip 1, adopting this convention requires refactoring work in your app. In fact, if this is a new schema then it will require refactoring of all of the database access calls in your application (sorry about that).