SQL Server Tips: A Developer's Guide

In the world of enterprise development, interacting with a database can become a daily occurrence. Although the range of knowledge and responsibility varies, skilled expertise in this area has moved from a "nice to have" to a "must have." SQL databases have a unique set of requirements and constraints unlike any other technology. Because of this, some programmers have a difficult time grasping all of the concepts. For many others, they learn just enough to stay ahead of the next potential problem. Why is this? SQL provides the look and feel of a structured language, but each database has its own personality. In a regular programming environment, the output of a value will be same every time. In a SQL database there are many factors that play into the decision making for each request. For instance, the ultimate path taken to find data may change over time or due to an outside influence. Achieving success requires equal parts science and art. The following sections outline a few tips for avoiding potential pitfalls.

Know the Language!
This might sound like a no-brainer, but jumping in without a plan or a life-preserver is an all too common path for developers. Take the time to learn the language. Look at each keyword and its uses. Maintain a watchful eye for insightful community contributions and new product releases. Each new version of SQL Server has brought increased speed, visibility, and options. MSDN has an excellent library that covers the basics. Check out more on this here.

Beware of User Defined Functions
With great power comes great responsibility. Programmers tend to feel very comfortable with user defined functions (UDFs) because they mimic a paradigm found in other languages. Pass data in, get data out. Unfortunately, overuse of UDFs can get a developer into trouble because SQL is an interpretive language meaning no compiled code; therefore, it can be inefficient in large scale or high demand situations. Also, using UDFs in a WHERE clause forces a row-by-row comparison of data which can be very slow and memory intensive. Check out more on this here.

Understand Temp Tables vs. Table Variables
SQL Server provides two temporary areas for storing table data. Both options exist because each has its advantages. Unfortunately, in some scenarios, using the wrong option can be memory or CPU intensive. For example, due to the nature of table variables it is not advisable to store a large number of records in this option. Furthermore, using the larger, more robust temp table option can create unnecessary load on a system for simple solutions. Check out more on this here.

Comprehend Isolation Levels
This is a must for all developers. Once the basic keywords (e.g. SELECT, FROM, WHERE, etc.) are learned, programmers should step back and understand how isolation levels affect data gathering. In a transactional world, based on the timing of a request, all data is not equal. Although the levels have been increasing in newer versions, simply understanding the difference between COMMITTED and UNCOMMITTED is paramount. Additionally, it's important to understand how NOLOCK, a commonly used keyword, factors into this discussion. Check out more on this here.

Become Familiar With Execution Plans
Working with a database does not start and stop with writing a SQL statement or stored procedure. SQL Server uses various internal mechanisms such as indexes and optimization statistics to determine the best path for the data being requested. Once this is determined, SQL Server caches the path to speed up future requests. Being able to read these plans is vital for troubleshooting speed and performance issues. In many scenarios when the answer isn't obvious, the plan tells the real story. Check out more on this here.

Watch Out For Parameter Sniffing
Parameter sniffing starts with SQL Server building an execution plan for a stored procedure. When it builds this plan it uses whatever initial parameters it was given. In some cases, if the data was an unusual case, SQL will cache a bad path to the data. Additionally, developers can also create problems by augmenting the data in a parameter, but still use that parameter in a SQL statement. This can confuse the database while building the plan. Check out more on this here.

Keep An Eye On SQL Injection
This is an area that catches people by surprise. Programming naturally lends itself to building things in the most efficient manner possible. Eventually developers broach the concept of building dynamic SQL statements for requests. This is where SQL injection can be problematic. Without the proper validation on string input fields, external users can gain access to the database. At that point, they can steal data or perform other malicious activities. Check out more on this here.