Integrate source control with SQL Server to reduce database development time

Collaborating on database development introduces a series of challenges. For instance, having the development team change the tables, views, stored procedures and other objects in a single, shared database, although intuitive, can introduce severe issues down the road as valid changes can be lost or overwritten by an unsuspecting teammate. This issue might be mitigated by restoring a database backup – under the assumption a valid backup exists. Even if it does, overwriting the development database with a backup means losing all of the valid database changes that have occurred since the backup was taken; not to mention the fact that restoring a large backup can take time – during which none of the developers can work on the database being restored.

April 4, 2013

Using SQL database backups instead of live databases in a large development team

In scenarios where a development project of a large scale is being completed, DBA skills can really be put to the test. Due to the nature and the dependencies of the software which is being developed, it’s not uncommon for the DBA to be in charge of dozens of environments, used by multiple development teams working on different aspects of the project. In some cases, this scale of development doesn’t only require for multiple SQL Server instances to be set up across environments – more often than not it requires different versions of SQL Server to be accessible across environments as well

April 4, 2013

How to quickly search for SQL database data and objects

Frequently, developers and DBAs need to search databases for objects or data. If you’d ever searched for a database function that contains a specific table column or a variable name, or for a table that contains specific data, you would have found out that there’s no one click solution, such as Ctrl+F

As there is no out-of-the-box solution in SQL Server management Studio, nor Visual Studio, here are a couple of options you can use:

April 4, 2013

SQL Formatting standards – joins, lists, structure, operations

The first part of the series – SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis, explains the importance of having clean SQL. In short, deciphering someone else’s code is time-consuming. Clean and neat SQL code can be read faster; SQL reviewing and troubleshooting is more efficient; joint development efforts are more effective; handing off projects from one team to another is smoother than for inconsistently written SQL.

As there are neither style nor standards to format SQL, it’s up to the team to create its own set of formatting standards. Here are some recommendations to format joins, value lists, code structure, arithmetic, comparison and logical operations.

April 4, 2013

Use SQL database backups to reduce the performance impact of heavy reporting

Depending on your particular environment, database reporting can have a heavy impact on the database performance, can execute queries which run for dozens of minutes or both. This is usually the case with reports which require complex queries having multiple calls to SQL Server’s aggregate functions to be executed against very large data sets. The effect on the database performance is particularly severe in scenarios where the production database stores data which is changed often – as data modification operations require exclusive locks, in order to preserve the integrity of the database, SQL Server will go ahead with the data modification operation until the query initiated by the report is still running. This increases the chances of a deadlock occurring; especially in cases where another set of data modification instructions, dependent on the ones which are waiting for the reporting to finish has already been applied. Therefore, reporting can cause performance degradation in a production environment. So, how can reporting be optimized to prevent such a heavy load on production databases?

April 4, 2013

SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis

Nobody likes to read a wall of text, even when it’s just plain text. When it comes to reading code, the problem is even bigger. Code can have different formatting styles, which could make your job either easier or more difficult. It can make code difficult to decipher and understand. A clean and neat SQL is read faster than an inconsistently written SQL; SQL reviewing and troubleshooting is more efficient; joint development efforts are more effective; handing off projects from one team to another is smoother.

How to make your SQL readable so that it is enough just to skim through the code and get the general idea

It’s the SQL formatting that makes the difference.

April 4, 2013

SQL code refactoring – ways to improve database performance (part III) – Add surrogate key

Determining just the right primary key for tables is one of the most important parts of a robust, high-quality database design. The key candidates and the keys themselves need to be picked with caution, as suboptimal choices can snowball out of control and leave the bloated, slow databases which require heavy maintenance and require massive amounts of work in order to meet changes in the business requirements. Therefore, due to the importance of the primary keys for the future behavior of the database, their impact on the database performance needs to be weighted as well. So, from a performance standpoint, should replacing complex natural keys with a surrogate key be considered?

April 4, 2013

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