| Beginning T-SQL (3e) |
Page 1 of 3 Author: Kathi Kellenberger and Scott Shaw This book aims to teach you the basics of T-SQL, while emphasizing the importance of best practices and performance, how does it fare? This book is aimed at the beginner, assuming little or no experience of T-SQL. It aims to introduce you to T-SQL programming, and at the same time introduce best practices, sound coding techniques, and performance considerations. Below is a chapter-by-chapter exploration of the topics covered.
Chapter 1: Getting Started When starting to learn a new topic, getting started is perhaps the most important step, if this is not done correctly progress can abruptly stop and frustration abound. The chapter starts with a helpful step-by-step walkthrough on installing SQL Server Express. A useful download link is provided, together with reassuring words that although the download page may change, you will be able to find the downloadable file to install. There’s a useful link that provides details of the different versions of SQL Server, together with the features each supports. Installation and use of Books Online (BOL) is then discussed. As you progress with T-SQL you’ll find BOL an invaluable reference source. Next, the installation of sample databases is shown – this is very helpful if you want to follow along with the code examples and chapter exercises. The chapter ends with a look at SQL Server Management Studio (SSMS), this is the environment where T-SQL developers spend most of their time, so it makes sense get familiar with it early. This chapter provides everything you need to get started with SQL Server from a T-SQL developer’s perspective. Everything is explained in an easily understandable manner, and reassurance provided where necessary. The chapter contains useful links to other chapters and websites, good screenshots, and instructive step-by step walkthroughs (all these things are done throughout the book). Chapter 2 Exploring Database Concepts Having installed and introduced the relevant software, this chapter explores some useful database concepts – how the database is organized and its component parts. The chapter opens with a look at the different version of SQL Server, what they’re used for, and their relative cost. Links are provided to case studies illustrating how companies have used SQL Server. Next, the use of SQL Server in the cloud is outlined, it is becoming increasingly popular owing to its scalable nature, reduced maintenance, and no need for on-premises hardware. The database is described at the physical level as consisting of a primary data file and a log file, and sometimes secondary data files. The database is composed of tables holding the data, and relationships between tables. Tables contain rows and columns, columns have data types – and these are discussed briefly. Various database objects are briefly covered, including stored procedures and views. Tables typically contain information about one thing (e.g. customer), and are created with the aim of eliminating repeated data. This is achieved by following the process of normalization, and is illustrated in the relationship between Customer, Order, and OrderDetail tables. A useful link is provided for more information. The purpose of indexes and their different types are briefly discussed. As is the concept of schemas as a means of grouping database objects, this is useful for assigning permissions. This chapter contains a miscellany of background information that’s needed to fully understand T-SQL querying. In some ways the information is inadequate (e.g. the normalization steps are not discussed), however this misses the point – this chapter contains enough information to progress with T-SQL querying without getting slowed down by what is often an academic discussion.
Chapter 3 Writing Simple SELECT Queries The chapter opens with a look at the SELECT statement, with useful examples and explained output results. Next, filtering of data is introduced with the WHERE clause, together with its various operators e.g. >, <, +, <=, !=. Filtering based on date and time is then discussed. The WHERE clause is examined further with reference to OR, AND, and IN. Sorting data is shown via the ORDER BY clause. NULLs are unknown data values, and they often present problems for beginners. The concept of NULLs is examined, together with example code, including incomplete results (due to NULLs). IS NULL is used to test for the presence of NULLs. The chapter ends with a look at performance considerations. Indexes are often good for performance but shouldn’t be on every column. Execution plans are briefly introduced, here to see if an index is used. As is typical with this book, the information is explained in an easily digested manner. There are plenty of examples provided to illustrate the concepts being discussed. The reference to Fritchey’s “SQL Server 2008 Query Performance Tuning Distilled” should be updated to the 2014 version of the book. Chapter 4 Using Built-In Functions and Expressions T-SQL provides a lot of inbuilt functionality to help with your coding, this chapter discusses this functionality. The chapter opens with a look at expressions using operators, then string concatenation is examined, together with the problems of NULL (use ISNULL or COALESCE). The use of CONVERT or CAST to convert between data types is illustrated. Various mathematical operators are examined with useful examples, including +, -, *, /, %. This is followed by a similar approach for string functions, including: LTRIM, LEN, CHARINDEX, SUBSTRING, REPLACE, and UPPER. The use of nested functions is explained. Date and time data types are notorious for creating problems for the beginner, various functions are examined that should help remove this obstacle, including: GETDATE, DATEADD, DATEDIFF, DATEPART, together with conversion examples. The chapter continues with a look at various mathematical functions including ABS, POWER, SQUARE, and RAND. Logical functions and expressions are then examined, specifically CASE, IIF, and COALESCE. SQL Server includes various inbuilt system functions that can be useful, those discussed with examples include: DB_NAME, HOST_NAME, and CURRENT_USER. This chapter provided a good introduction to a wide range of built-in functionality. Plenty of useful examples are provided to consolidate your understanding. This chapter could be a useful future reference. There’s a good point about being careful when you have functions in the WHERE clause. Chapter 5 Joining Tables The chapter opens with a look at the INNER JOIN, this is the most common join. The chapter uses the example of joining the Customer and Order tables, explaining primary keys and foreign keys along the way. The importance of using fully qualified names, and the practice of aliases are explained. Next, the OUTER JOIN is examined. With a RIGHT or LEFT OUTER JOIN, the right and left indicate the table you want to preserve i.e. include its data in the output even if there is no matching entry in the other table. The use of OUTER JOIN to find non-matching rows (a common requirement) is shown. Finally, a FULL OUTER JOIN is discussed – this is equivalent to a combined LEFT and RIGHT OUTER JOIN. Self-joins are examined next. This typically occurs when the table contain a relationship within itself e.g. an Employee table may have columns called employee and manager – so a self join can show all the employees for a given manager. The section on performance examines the 3 different types of join implementation (nested loop, hash and merge), showing how they work within execution plans. This chapter is again very easy to follow, with many useful examples provided, and output results discussed. It might have been worthwhile mentioning that although the tables are normalized to eliminate data redundancy, to do anything useful you typically need to join the tables. Chapter 6 Building on Subqueries, Common Table Expressions, and Unions The chapter opens with an example of a subquery (also called a nested query), this is a query that is part of a WHERE clause. This allows you to get rows without joining tables. Consideration needs to be given to NULLs since if the inner query contains NULLs then no rows will be returned. Extensions to subqueries are examined, including: NOT IN, EXISTS, and CROSS APPLY. UNION and UNION ALL are also discussed. The chapter continues with a look at derived tables, and Common Table Expressions (CTEs). The latter often provide easier solutions to complex problems e.g. recursion. The performance section highlights that UNION ALL should be used in preference to UNION where possible, since it doesn’t spend time removing duplicates. Useful example code shows their relative cost in the execution plan. |
||||
| Last Updated ( Wednesday, 14 January 2015 ) |
