SQL code refactoring – ways to improve database performance (part II) – Split table

In most cases, splitting a table into two tables containing different columns is required in order to address database design changes, business requirements or even adding domain restrictions retroactively (for example, isolating currencies in a separate table and enforcing referential integrity via a foreign key to ensure that only valid currencies can be stored in the database). However, splitting tables may actually offer additional performance benefits to the database.

April 4, 2013

SQL code refactoring – ways to improve database performance (part I) – Encapsulate code as a stored procedure

Database performance is a challenge for every developer and DBA. Even when some improvements are made, there is always a question – is there anything else that can be done.

There might be. Some of the performance improvement techniques are not code related – a proper indexing strategy, adding memory, using different disks for data files, log files, and database backups, using faster disks, optimizing tempdb performance.

April 4, 2013

How to secure stored procedures against SQL injection

Although SQL Server’s stored procedures help out with code security by hiding the implementation of the business logic and even protecting against some kinds of SQL injection attacks — primarily those that use an operator such as AND or OR to append commands onto a valid input parameter value, simply wrapping the code into a stored procedure doesn’t mean that applications, database and SQL Server are safe from all types of SQL injection attacks. So, how to make stored procedures bulletproofed against SQL injections?

April 4, 2013

How to build a SQL database from source control without dependency problems

One of the caveats of having your SQL database under a source control system, is the overhead when the time comes to deploy a new database build. Even if a single copy of the database isn’t shared among the developers, but rather each developer has its own local copy of the database objects’ scripts which are synchronized with source control on a regular basis, building a deployment SQL script may prove to be a rather challenging task.

April 4, 2013

How to re-order scripts to avoid dependency based errors

Having a database scripted to a script folder or to a source control, it enables to use these DDL scripts to recreate it. However, it might not go smoothly due to some missing dependencies.

When creating a database object from scratch, the needed objects are created along the way. The objects can be tied in relationships and constraints to make them dependent on each other.

April 4, 2013

Clean up SQL code from variables you don’t use

Writing and maintaining simple code for stored procedures and functions has never been a problem. However, maintaining complex code, with dozens of parameters and variables, and thousands of rows of SQL statements is. One of the things you can do to make your life easier is to have clean code – without unnecessary and unused parameters and values.

Removing a parameter or variable from code in a production database can cause some problems. They can be easily solved by applying the original object code from a database backup. That’s assuming you can afford the luxury of downtime.

April 4, 2013

How to change an object name without breaking your SQL database

Very often, initial database design does not fit the current criteria and requirements anymore. It is therefore necessary to periodically align to what our current needs and standards are. When working with a large database, any idea of changing the name of one or more SQL objects can be a real challenge.

Changing an object name is a simple operation and it could be done easily using the SP_RENAME system stored procedure or by utilizing the RENAME and ALTER… RENAME statements for indexes and triggers.

April 4, 2013

How to rename a column without breaking your SQL database

Renaming a table or view column is not something a SQL developer needs to do every day. However, when the time comes, how can it be done easily and effortlessly?

Just renaming a column is not difficult. To rename the column TerritoryID in the Sales.SalesTerritory table into TerID, execute the following statement:

April 4, 2013