Rules of SQL formatting – SQL code commenting

In this article, tips on T-SQL code commenting and improving productivity will be given, while using the ApexSQL Refactor’s Comments feature. ApexSQL Refactor is a SQL Server and Visual Studio SQL formatting add-in with nearly 200 formatting options.

The main purpose of comments is to document our code and write descriptions of what code is doing.

February 12, 2014

Rules of SQL formatting – Commas and spacing in T-SQL

This article describes some possibilities of formatting commas and spacing in T-SQL using ApexSQL Refactor SQL formatter with nearly 200 SQL formatting options.

Formatting commas in T-SQL

Commas in T-SQL are used to format numbers, as list-separators, and value separators. When commas are used as separators, there is no specific rule for placing commas at the end of a line or at the beginning.

February 5, 2014

How to format SQL like a pro – formatting to implicit Microsoft standards and guidance

There is little formal guidance regarding SQL formatting and coding styles, but there is no universally accepted coding standard for SQL Server. In this article, though, implicit guidance will be followed from:

  • MSDN
  • SQL Server 2012 Books Online documentation
  • and the Adventure Works 2012 SQL Server database

This article will describehow to implement these standards via ApexSQL Refactor.

January 8, 2014

SQL code refactoring – ways to improve database design

A “one-to-many” relationship is one of the most common relationship types as many real world scenarios can be represented using it. For instance, the same product can be sold by more than one supplier; a customer can have more than address, and so on.

May 21, 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 code. 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

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

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

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

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 that the life make 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, assuming it can afford the luxury of downtime.

April 4, 2013

How to quickly convert SQL code to language-specific client code

Integrating SQL Server with business infrastructure often requires that developers prepare T-SQL code for use within various client codes, such as C#, PHP, and Perl. This means adding language specific code that will be inserted before and after every SQL statement, as well as escape characters for quotes and code to terminate a line, so that the other languages can parse and execute it correctly. Updating the code manually is a time-consuming process prone to mistakes.

April 4, 2013