5 T-SQL development

 

This chapter covers

  • Mistakes that can cause unexpected results
  • Mistakes that can lead to performance problems
  • Avoiding looping with cursors in T-SQL
  • Deleting large numbers of rows

SQL is an ANSI and ISO standard language that allows database developers to interrogate and manipulate data within a relational database. T-SQL is SQL Server’s dialect of the SQL language and is used to interact with SQL Server instances and the databases hosted within.

In chapter 4, we designed and created the tables for a new MagicChoc database. In this chapter, we will explore some of the common mistakes that can be made in T-SQL by developers who are less experienced with the language. For examples of T-SQL development, we will look to MagicChoc, which wants us to develop the logic that will be used by its frontend applications. We will use this as an opportunity to start exploring some of the common mistakes that can be made in T-SQL by developers who are less experienced with the language.

Getting to grips with SQL can be a challenge for developers who are more familiar with writing application code, using languages such as C# or Visual Basic, because of the large conceptual difference between how the languages work. For example, looping in .NET languages is perfectly acceptable, but in the set-based world of SQL, it can cause serious performance problems.

5.1 #14 Dealing incorrectly with NULL values

5.2 #15 Using NOLOCK as a performance tweak

5.3 #16 Using SELECT * as standard

5.4 #17 Unnecessarily ordering data

5.5 #18 Using DISTINCT without good reason

5.6 #19 Using UNION unnecessarily

5.7 #20 Using cursors

5.8 #21 Deleting many rows in a single transaction

Summary