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 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

Get an alert when a certain record changes in SQL Server

Auditing a database is the first step towards staying updated about database changes. However, if specific, highly sensitive data needs reviewing, an immediate notification of any change is preferable.

Sending email alerts to one or more recipients is one of the most common ways of immediate notification. Sending emails for every change in a database is not advised. It will, in most cases, quickly fill your inbox, and add many unnecessary actions, which can also affect the overall application performance. Therefore, it is important to choose the changes that you want to be alerted about, carefully.

April 4, 2013

Recover SQL data from a dropped table without backups

If you’ve accidentally executed a DROP statement and you have a full database backup, given that no changes occurred after the table was dropped, you can simply recover the data by overwriting the original database with the backup. However, if that’s not the case, either because the database objects and data have been changed after the DROP SQL statement was executed or because you don’t have a valid database backup, there is still a way to recover all of your lost data

April 4, 2013

Recover a SQL Server database using an old backup and the current transaction log file

How to recover a SQL Server database using an old backup and the current transaction log file DBAs have various disaster recovery plans. One of the questions they have to answer before setting up a right plan is how much data can they afford to lose. Many of “accidental DBAs” become aware that making regular full database backups might not be enough, only after it’s too late.

April 4, 2013

Recover data lost due to a TRUNCATE operation without backups

If you’ve accidentally executed a TRUNCATE statement and you have a full database backup, given that no changes occurred after the table was truncated, you can simply recover the data by overwriting the original database with the backup. However, if that’s not the case, either because the database objects and data have been changed after the TRUNCATE SQL statement was executed or because you don’t have a valid database backup, there is still a way to recover all of your lost data.

April 4, 2013

How to secure stored procedures against SQL injection

Although SQL Server’s stored procedures help 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

Audit SQL Server database schema changes

Keeping track of the changes made to your database objects is a key part of any SQL database security strategy or compliance policy including, among others, the Health Insurance Portability and Accountability Act, Sarbanes-Oxley, Payment Card Industry Data Security Standard or the European Union Data Protection Directive. However, even if your IT environment doesn’t have to comply to stringent security rules, being able to identify what database object has been changed, who has changed it as well as the exact time of the change, is invaluable in troubleshooting any schema related problems down the road, such as broken dependencies. So, how does one audit SQL Server schema changes?

April 4, 2013