Author: Kathi Kellenberger and Scott Shaw
Publisher: Apress
Pages: 496
ISBN: 9781484200476
Print: 1484200470
Kindle: B00LPDVAGE
Audience: T-SQL beginners
Rating: 4.8
Reviewer: Ian Stirk

Chapter 14 Implementing Logic in the Database

The chapter opens with a look at the various constraints that restrict the data a table’s column can contain. The CHECK constraint ensures a column only contains certain data values. A unique constraint ensures the data is unique. Foreign keys provide data consistency, additionally they can contain rules for modification (e.g. cascading delete). Finally, different ways of automatically populating columns are discussed, these include: identity, rowversion, computed, and sequence object.

Next, views are examined. These contain no data, but contain a query definition, and are populated at runtime. Views are useful for providing a layer of abstraction, and security. Code to create views is given, including via the visual designer in SSMS. Common problems associated with views are discussed.

The chapter continues with a look at User-Defined Functions (UDFs). The various types (scalar, table value function) are described, and example code provided. UDFs can improve code readability since they can hide complex processing, however they can give poor performance (so expressions might be better).

Next, stored procedures are discussed, these are the workhorse of T-SQL. They typically contain blocks of SQL queries, and can contain lots of business logic. Again they offer a degree of abstraction and security (including protection from SQL injection attacks). Various code examples are provided in discussing parameters, default parameter values and output parameters. It is possible to capture the results of a stored procedure into a table using INSERT INTO… EXEC YourStoredProcedureName.

The chapter continues with a look at user-define data types, these are native data types given a restriction e.g. a length of 5 characters, and an alias. Additionally, the CLR can be used to define data types that have more granularity and functionality (e.g. methods associated with the data).

Triggers are discussed next. These are special stored procedures that run when data is modified (updated, deleted, inserted), they use the internal tables inserted and deleted. Triggers can prevent changes by rolling back the transaction, so they should run as quickly as possible. Triggers are often used to implement referential integrity across tables, but a foreign key constrain would be better.

This chapter contains a large amount of detail that the T-SQL developer will use in their day-to-day work. There’s a very useful comparison grid illustrating the functionality of stored procedures, UDFs and views. It might have been useful to mention that foreign keys do not have an index created for them automatically (unlike primary keys), and this can be the cause performance problems.

Chapter 15 Working with XML

XML is often described as self-describing data. This chapter opens with a look at processing XML using OPENXML, this shreds an XML document into a rowset (i.e. table). Associated processing routines are sp_xml_preparedocument and sp_xml_removedocument, example usage is given and discussed.

Data can be retrieved as XML using the FOR XML clause, this converts a rowset into an XML document. There are 4 modes of using FOR XML: raw, auto, explicit and path – each is discussed with relevant example code. Generally path is preferred since this allows more granular control of the document structure without the complexity of the other methods.

Next, the XML data type is discussed. It is possible to store XM in the XML data type, but there are limits e.g. size, can’t be used in a GROUP BY etc. Various methods can be applied to the XML data type, namely: query, value, exists, modify, and nodes. Examples of each method’s processing are given.

The chapter continues with a look at namespaces, these are used to uniquely define the elements and attributes of the XML. The related topic of typed and un-typed XML is briefly discussed. The chapter ends with an interesting demonstration of the use of the XML nodes method to split a string.

This chapter provided a good overview of what XML is, the XML data type, and how to process XML data.

Chapter 16 Expanding on Data Type Concepts

This chapter considers some of the more unusual data types. The chapter opens with a look at large-value string data, previous versions of SQL Server used the text and ntext data types, but these had several limitations. It is recommended that the MAX types be used now e.g. VARCHAR(MAX).

Similarly, the older method was to store large binary data using the binary, varbinary or image data types. The older methods stored the file path in the database, but the files were held on the file system, this could cause problems with backup co-ordination and security. The FileStream object fixes these concerns, it makes the files part of the database. This is extended with the FileTable, where the data is accessible from both the database and the file system. The recent date and time enhancements are briefly discussed, specifically: date, time, datetime2, and datetimeoffset.

Next the hierarchyid data type is discussed, this is used to represent hierarchical relationships e.g. family trees, and additionally it has methods that can be applied to its data. The spatial data types (geometry and geography) are examined, and examples discussed. Sparse columns are discussed next, here space can be saved if most of the data values of a numeric data type is NULL. The chapter ends with a look at the impact of data types on performance. If the wrong data type is used, a data conversion can occur.

This chapter discussed some of the more unusual data type. Useful examples where provided together with details of when they might be used.

Chapter 17 Running SQL Server in the Cloud

Increasingly organizations are looking to the cloud for storage and processing. This has many advantages, including scalability, easier maintenance (backups/updates), and a pay-as-you-go model.

The chapter discusses how to obtain a Microsoft Azure Account, including a 1 month free trial. Next, the Azure Dashboard is discussed, this allows you to manage your services, Virtual Machines (VMs), Microsoft Azure SQL databases, websites, mobile services, and storage.

Next, Windows Azure Virtual Machines are discussed, including the creation of a new VM within minutes, various options are available for you to specify the OS, and if you want SQL Server installed.

The chapter ends with a look at Microsoft’s cloud database, Azure SQL Database. These can be provisioned in seconds, and there’s no need to worry about reboots or upgrades. Most DML and DDL is the same as on non-cloud databases. There’s a step-by-step walkthrough on creating a SQL database and table. It’s also possible to access this database via SSMS. Since many different organizations may have databases hosted on the same cloud servers, throttling is implemented where necessary. Some limits are briefly discussed (e.g. database size).

Chapter 18 Where to Go Next?

After learning the basics of a topic, the next step is often deciding where you should go to get further information. The chapter opens with at look at online resources, most of the web sites listed will familiar to seasoned SQL developers. Many offer newsletters that could prove useful. Next, conferences are discussed, they allow a chance to listen to experts and perhaps talk with them. User groups are also a good source of gaining SQL knowledge, these are often located in big cities and cover a range of topic.

Books also offer a wealth of information, with the author often having a plenty of experience. A well organized book can save you a large amount of time. Similarly, classes can be useful – and some people learn better in classes. Of course BOL is a great source of detailed and wide ranging SQL knowledge. The chapter ends with the mantra: practice, practice, practice… the more you practice the faster you learn.

I think every book should have a section on where to go next. I liked the wide range of choices offered, however the content of each was relatively sparse. It might have been useful to say why specific web sites are good. Perhaps particular titles could have been given in the books section, again with the reason for including them – luckily book references are given throughout the body of the book, but they do tend to relate to this particular publisher.

I can certainly recommend Ben-Gan’s “T-SQL Querying” and “T-SQL Programming” (various editions for both books), as the next logical learning steps after reading this book.

Conclusion

This book aims to provide an introduction to T-SQL, with an emphasis on best practices and performance. It tackles these objectives in a clear manner, providing helpful discussions, relevant example code, and useful screenshots. Each chapter ends with a series of exercises and worked example solutions that will enable you to check and expand further on what has been learned.

Since the book is introductory, in many ways it is version independent. Although aimed at SQL Server2014, most of it will be applicable to earlier (and later) versions. In addition to being instructive, the examples provided can be used later to check syntax, and form the basis of your own T-SQL code.

Perhaps the overriding impression I have of this book is, it explains the subject in a manner that is very easy to learn from – precisely what’s needed for the beginner. If you’re new to T-SQL, I can highly recommend this very instructive book to start your journey.