| Microsoft SQL Server 2012 T-SQL Fundamentals |
Page 3 of 3
Author: Itzik Ben-Gan Chapter 8 Data Modifications Up until now, the book has been about retrieving data. Now the emphasis is on changing data. The chapter opens with a look at various ways of inserting data, namely: INSERT VALUES, INSERT SELECT, INSERT EXEC, SELECT INTO, and BULK INSERT. Examples of each are provided and discussed. The section then looks at tools for generating keys i.e. Identity property and sequence object. Next, the two ways of removing data (i.e. DELETE and TRUNCATE) are explained with examples. The following section examines updating data, again with suitable example code and discussion. There’s useful code that shows a DELETE and an UPDATE based on a JOIN. The chapter continues with a look at merging data. From SQL Server 2008 onwards the MERGE statement can be used to INSERT, UPDATE, and DELETE data, via conditional logic. This typically requires less code and is more efficient. Next, the use of table expressions (derived tables, CTEs, views, and inline TVFs) to modify data is explained. Throughout, helpful examples are discussed. The chapter ends with a look at the OUTPUT clause, this contains data from modified rows (i.e. old and new values). This data is particularly useful for troubleshooting and auditing. Useful examples of this for INSERTS, UPSDATEs, DELETEs, MERGEs, and redirecting it to a table are provided. This is a very useful core chapter, discussing with useful examples, the various data modification statements. Helpful example code is provided throughout. Chapter 9 Transactions and Concurrency This chapter discusses how SQL Server handles concurrency, via transactions and their properties. The chapter opens with a look at transactions, which encapsulates a unit of work, where either all or none of the work is committed. The various transaction statements are explained, including: BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN. Implicit and explicit transactions are briefly discussed, as is the session object IMPLICIT_TRANSACTIONS. The ACID (Atomic, Consistent, Isolated, and Durable) properties of transactions are explained. Next, the chapter looks at locks and blocking. SQL Server uses the isolation property of transactions to protect resources (e.g. rows). The various lock types (e.g. exclusive, shared) and their compatibility is explained, via useful grid diagrams. A section on troubleshooting blocking looks at the use of various DMVs to help determine the problem’s cause. The chapter proceeds with a look at isolation levels, which influence how concurrent users see each other’s data. Various isolation levels are explained (read uncommitted, read committed, repeatable read, and serializable), with examples, before moving onto isolation levels based on row versioning (snapshot, and read committed snapshot). The section ends with a very helpful grid diagram that lists the isolation levels with the update conflicts they allow/prevent (e.g. allow lost updates). The chapter ends with a look at deadlocks. This is a special form of blocking, which cannot be resolved in the normal manner (i.e. by waiting), instead SQL Server chooses one of the transactions to rollback. The section provides example code for creating a deadlock. This chapter provides a useful overview of an area that can be troublesome for beginners after they’ve learned about data retrieval and modification. It might have been useful to combine the various pieces of blocking SQL to actually see what SQL is blocked. Additionally, other than DEADLOCK_PRIORITY, no other of the many methods of resolving deadlocks is discussed. Since this is an introductory book, perhaps this isn’t a problem. Chapter 10 Programmable Objects So far, the book has tended to work on discrete T-SQL topics. This chapter provides a miscellany of subjects that tie together what has been learned so far, together with various useful programming practices and extensions. The chapter starts with a brief look at variables, showing how they’re declared and used. This is followed with a natural progression to using groups of SQL statements via batches. Batches are treated as a unit of parsing, typically delimited by the GO separator. Next, the flow elements (IF...ELSE, and WHILE) are explained, with helpful example code. This is followed by a discussion on cursors, these allow a procedural approach to SQL processing, akin to C#, java etc. The author rightly highlights, where possible the use of cursors should be replaced with the typically more efficient set-based processing. That said, examples of cursor usage are provided. The chapter then progresses to discussing temporary tables, explaining their use and type (i.e. global and local). The related topics of table variables and table types are also discussed. The use of dynamic SQL is then examined, this allows you to build up a string of SQL dynamically, which can be useful for some problems. The problems associated with SQL injection are explained, together with some remedies. Routines (e.g. stored procedures) are then examined, these allow SQL to be encapsulating. The various advantages of encapsulation are given (e.g. code reuse). Example code is provided and discussed for stored procedures, user defined functions (UDFs), and triggers (both DML and DDL). The chapter ends with a look at error handling, namely TRY/CATCH, and SQL Server 2012 introduced the THROW command. Examples usage is provided and discussed. This is another very useful chapter, tying together aspects of previous chapters, and providing a natural progression onto what a SQL developer can expect in the real-world. The example of executing batches multiple times via “GO n” should prove useful. I was surprised the common SQL TRY/CATCH error-handling routine wasn’t provided. Appendix A Getting Started One problem with learning a new technology is setting up its environment. As such this appendix is to be welcomed. It contains very useful information on:
Conclusion This book aims to provide an introduction to T-SQL. It tackles this objective in a clear manner, providing helpful discussions, relevant example code, useful diagrams, and plenty of related tips. 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 Server2012, most of it is applicable to earlier and later versions. In addition to being instructive, the examples provided can be used later to check syntax, and can also form the basis of your own code. The one problem I had with the book was its first chapter. The chapter is certainly required, but I wonder if it is too advanced/theoretical to be introduced at the start of the book, perhaps preventing absolute beginners from reading further. If you’re new to T-SQL, I can certainly recommend this very instructive book. 
|
|||||||
| Last Updated ( Sunday, 12 April 2015 ) |