Author: Mike McQuillan

Publisher: Apress
Pages: 388
ISBN: 978-1484214206
Print: 148421420X
Kindle: B0142D693W

Rating: 4.0
Reviewer: Ian Stirk 

Chapter 10 Creating Data Import Scripts

This chapter looks at overcoming a problem with BULK INSERT, where constraints prevent sub-records being inserted. This chapter creates an import script that allows records, together with child records, to be inserted.

Chapter 11 The SELECT Statement

SELECT is the most widely used T-SQL statement, allowing the retrieval of data. Data can be filtered using the keywords WHERE, AND, OR, IN. The chapter continues with a look at ordering and grouping the output. The amount of data returned can be limited by using the TOP keyword. In each case, useful examples are provided. There’s a good point about not using SELECT *. 

Chapter 12 Joining Tables

Normalization ensures that tables contain data about specific entities only (e.g. employees). To get the most out of your data you need to join related data in different tables together. This chapter is concerned with joining tables. INNER JOIN matches data in both tables, LEFT OUTER and RIGHT OUTER JOINs match data in both tables but also preserves data given in either the LEFT or RIGHT table respectively. A FULL OUTER JOIN returns both exact matches together with any non-matching data in both the left and right table. A CROSS JOIN matches every row in the first table with every row in the second table, producing a Cartesian product – useful for generating test data. 

Chapter 13 Views

A view doesn’t contain data, rather it is a SQL object that get populated at run time. Views have the advantage of security and abstraction. Additionally, indexed views do contain actual data, and have the advantage of performance. Typically a view makes coding easier. Code and examples are provided for creating and dropping views. To ensure columns in tables that are used in views are not dropped, SCHEMABINDING is used. 

Chapter 14 Indexes

Indexes are the main method of improving the performance of T-SQL queries. The author describes an index in the context of a book’s index, allowing a specific word to be found quickly, rather than scanning through the whole book. There’s a useful section on identifying columns to index. This is followed by a section showing how indexes work.

The chapter continues with a look at clustered indexes (how the data is physically stored), and non-clustered indexes (a subset of data). Filtered indexes are indexes with a WHERE clause, resulting in smaller indexes.

While indexes improve performance, they can become defragmented when their data changes, and this can degrade performance. There’s a useful section on index maintenance, code is provided to identify fragmented indexes, and to rebuild or reorganize them (to decrease the fragmentation). There’s a brief section on statistics, these contain details about the distribution and density of column data values, and are used by the optimizer to create an efficient query (e.g. use an index). The chapter ends with a look at the occasions where indexes can be a bad idea.

Chapter 15 Transactions

The chapter opens with a look at an everyday transaction, buying and selling a service, in which both parts need to succeed for the transaction to be valid. The ACID (Atomic, Consistent, Isolated, Durable) properties of a transaction are briefly examined.

The chapter continues with a look at the T-SQL statements: BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION. Implicit and explicit transactions are briefly explained, and the use of @@TRANCOUNT and sp_who to aid debugging transaction problems shown. The chapter ends with some useful rules for working with transactions (e.g. keep transactions short).

Chapter 16 Functions

Functions allow code to be reused. Scalar functions return a single value. A simple example function is created to add 2 numbers together, this is followed with more complex examples. The use of functions in queries is shown.

Chapter 17 Table-Valued Functions

Table-Valued Functions (TVF) are functions that return a table of values. The various advantages of TVF are explained (e.g. hides complexity), together with some disadvantages (e.g. can degrade performance). An example TVF is created, and its use in queries shown. The use of TVFs in CROSS APPLY and OUTER APPLY is illustrated. In many ways a TVF behaves like a parameterized view.

Chapter 18 Stored Procedures—Part 1

Stored procedures contain blocks of SQL code, offering greater flexibility than functions, and are used widely. The chapter looks at the advantages of using stored procedures (performance, encapsulation, better maintenance). The syntax for creating, altering and deleting a stored procedure is shown, and a sample stored procedure is created and run. Nuances relating to schema names, NOCOUNT, and parameters are all explained. The use of PRINT to aid debugging is shown.

Chapter 19 Stored Procedures—Part 2

The work on stored procedure continues with a look at User-Defined Types (UDTs). These allow you to create your own types of data, defined more meaningful data. A simple UDT (ContactNote) is created and its use illustrated. The chapter ends with a statement about the importance of using SET based logic rather than WHILE loops in SQL code.

Chapter 20 Bits and Pieces

The final chapter contains a miscellany of very useful, if unrelated features. Firstly, security is examined in terms of GRANT, DENY, and REVOKE, and the various built-in security roles. The use of schemas as a security mechanism is illustrated. Triggers are then briefly discussed. The chapter ends with examples of using both SQL Server Profile and Extended Events to monitor your system/SQL.

Appendices

The book contains appendices for: SQL Data Types, Glossary, Common SQL Server System Objects, and Exercises.

Conclusion

This book aims to get the newcomer started in SQL Server development, and succeeds. It is mostly easy to read, with good explanations, discussions, screenshots, T-SQL code, website links, and practical step-by-step walkthroughs. Additionally, the author provides a humorous dialogue, and positive feedback, which should be especially useful for the beginner.

In some parts, this book could have been clearer and simpler, with fewer tables, and less scripts changes (very repetitive). Since it is an introductory book, it would be useful to have a section on where to go next for further information (e.g. newsletters, blogs, websites).

There are many excellent parts in this book, it should prove useful for the newcomer to SQL server development.