DevCity.NET - http://devcity.net
Book Review: Murach's SQL Server 2005 For Developers
http://devcity.net/Articles/305/1/article.aspx
Scott Waletzko

Scott Waletzko has been an Information Technology professional and Windows / Web programmer since 1995, with experience in software development and architecture, network design and administration, and project and team management. Currently he is the the Senior Vice President of Technology at Intellisponse, as well as the President of Skystone Software / Echosoft Design Studios, LLC.

At Intellisponse, Scott is responsible for architecture and implementation of the company flagship software called Synapse, the first full-featured Web survey authoring tool for market research, enabling researchers to design, publish, and manage full-featured and logically complex questionnaires to the Internet without programmer interaction.

As president of Skystone Software / Echosoft Design Studios, LLC, Scott is developing a unique Web site content management system named Tempest, which will drive myBard.com and provide comprehensive Web site hosting and content management to anyone with a Web browser.

 
by Scott Waletzko
Published on 3/11/2007
 

A review of Murach's SQL Server 2005 for Developers tutorial and reference book, written by Scott Waletzko (Skystone Software).

Overview

SQL Server 2005 for Developers, written by Bryan Syverson and Joel Murach, is a comprehensive tutorial designed for developers that provides a comprehensive window into the world of the SQL Server 2005 database. This book is not intended for consumption by database administrators and no assumption is made by the authors as to what sort of developers are reading the book (although C# and VB.NET code is used in examples) ; the topics covered are simply those that would be useful to a developer rather than to a DBA. This is an especially useful resource to today's developers because of the expectations that developers be skilled in many more areas of development than previously required.

The first part of the book introduces SQL syntax and functions to the reader, the second part covers database design (including best practices and DDL), and the third portion gets into advanced SQL features and features specific to SQL Server 2005 (like Xml data types and CLR integration). Topics are covered in a very logical order, leaving the reader with little doubt that they have the necessary information required to move on to the next topic.

In classic Murach style, every two pages in the book repeats the contents twice; the left-hand page is a detailed description of the concept being covered written in a conversational tone, and the right-hand page contains a recap of this discussion in bulleted-form, with screenshots and / or code samples. This layout is ideal for both novice and expert readers alike; beginners learn faster when a concept is restated / repeated, and experts can skim through the right-hand pages looking for content that is new to them.

Because the book is designed for developers and not DBAs, there are many topics intentionally not covered. In these cases, the authors usually mention the concept briefly and then explain that it is most commonly used by DBAs and therefore will not be discussed further. Conspicuously absent, however, was any mention of the cryptographics functions in SQL Server 2005, which would seem to be important enough for at least a mention, especially for developers.

 
Walkthrough: Section 1

The first section in the book ("An introduction to SQL") begins with a quick overview of how relational databases work ("An introduction to client/server systems", "An introduction to the relational database model", "An introduction to SQL and SQL-based systems"). These pages describe the basics of the client / server database system architecture, the organizational structure of relational database tables, and even delves briefly into a history of SQL and comparisons ot SQL Server to other relational database systems.

Next, Transact-SQL is introduced, beginning with an overview of simple retrieval queries and multiple table joins, then on to addition, deletion, and update statements, and culminating in a brief overview of SQL coding guidelines. This is followed by an explanation of views, stored procedures, triggers, and user-defined functions (all covered in more detail later on in the book). Chapter 1 ends with a discussion of how to access an SQL Server database from a front-end application using ADO.NET, including sample code in both Visual Basic .NET and C#.

Section 1 ends with coverage of the SQL Server Management Studio, the application that provides interaction between developers / DBAs and the SQL Server 2005 database. Complete with many screenshots, this chapter covers how to manage databases and perform basic administration tasks, hwo to design tables, and how to execute queries.

 
Walkthrough: Section 2

Section 2 delves more deeply into the concepts outlined in the previous section, beginning with a comprehensive discussion of SELECT statements ("How to code the SELECT clause", "How to code the WHERE clause", and "How to code the ORDER BY clause"). Table joins are then explained and outlined in detail ("How to work with inner joins", "How to work with outer joins", "Other skills for working with joins", How to work with unions").

Summary queries (and aggregate functions) are covered next, detailing all of the different ways to collapse data using SQL statements. Subqueries are then discussed ("How to code subqueries in search conditions", "Other ways to use subqueries"), followed by a brief discussion of complex queries and common table expressions (CTE).

Chapter 7 deals with INSERT, UPDATE, and DELETE statements ("How to create new rows", "How to modify existing rows", "How to delete existing rows"), and chapter 8 rounds out the section by covering SQL data types and functions ("A review of the SQL data types", "How to convert data", "How to work with string data", "How to work with numeric data", "How to work with date / time data").

 
Walkthrough: Section 3

Section 3 covers database design and implementation; topics usually relegated to database administrators. The inclusion of these topics provides developers with a solid foundation for understanding the more complex aspects of relational databases, and the ability to design properly-formed databases in the absence of a DBA. The section begins by covering the abstract concepts that necessary to a database designer, including data structure design and normalization (and denormalization). These discussions of database design theory quite properly prepare the reader for the eventuality that they will have to design a relational database from scratch.

DDL (Data Definition Language) is also covered in this section, providing a foundation for building database components using SQL statements ("An introduction to DDL", "How to create database, tables, and indexes", "How to use contraints", "How to change databases and tables"). The same concepts are then covered using the visual interface of the Management Studio ("How to work with a database", "How to work with tables", "How to generate scripts"), which is the preferred method for most developers (and DBAs) in the real world.

 
Walkthrough: Section 4

Section 4 delves into the more advanced SQL skills, beginning with a discussion of views ("An introduction to views", "How to create and manage views", "How to use views", "How to use the View Designer"), then covering scripts ("An introduction to scripts", "How to work with variables and temporary tables", "How to control the execution of a script", "Advanced scripting techniques").

Next, the meat of SQL Server database functionality is detailed, beginning with stored procedures, user-defined functions, and triggers. The use of cursors is covered next ("How to use cursors in SQL", "How to use cursors to retrieve data", "How to modify the data through a cursor", "Additional cursor processing techniques"). As stated by the authors in the text about cursors, client-side cursors are not discussed even through they are the preferred method of implementing cursor functionality when given a choice. Covering server-side cursors, however, provides the reader with a good foundation for understanding the important concepts related to use of cursors, which will enable them to better understand the client-side implementation of the same concepts.

From cursors the authors move on to discuss the crucial concept of transaction management and record locking ("How to work with transactions", "An introduction to concurrency and locking", "How SQL Server manages locking"). This chapter is well phrased, which is important given the importance of the concept being covered. The authors also complete the chapter by explaining deadlocks and how to prevent them.

Database security is the next topic, where the different conceptual options for implementing security are discussed ("How to work with SQL Server login IDs", "How to work with permissions", "How to work with roles") followed by a description of how to implement security using the SQL Server Management Studio application ("How to manage security using the Management Studio").

The final topic in this section is the integration of Xml in SQL Server databases ("An introduction to Xml", "How to work with the Xml data type", "How to work with Xml schemas"). This section briefly explains Xml, Xml Schema, and XQuery (Xpath) before explaning how each is used to interact with Xml columns in an SQL Server database (or generate Xml result sets from relational data), but a previous understanding of Xml is quite necessary to get the most out of these pages (as a detailed explanation of Xml is quite outside the scope of the book).

 
Walkthrough: Section 5

The final section of the book covers integration of the .NET CLR (Common Language Runtime) with SQL Server to extend the features available to developers through SQL and DDL. The section begins with an overview of the integration of the CLR with SQL Server, including how to use Visual Studio to work with CLR objects and how to deploy CLR assemblies for use by SQL Server.

Next, specific examples of how to use the CLR to write stored procedures, functions, and triggers are given, including detailed explanations of the differences between writing standard CLR code and SQL Server 2005-integrated CLR code. Lastly, CLR aggregate functions and user-defined types are discussed.

 
Summary

SQL Server 2005 for Developers by Bryan Syverson and Joel Murach is a comprehensive tutorial that properly introduces beginner developers or programmers new to SQL Server 2005 to the gamut of language, management and concepts surrounding the SQL Server 2005 database. The book is specifically targeted to developers who wish to be able to master SQL Server 2004, and after reading the book most developers should have a grasp of just about every concept and feature required to work with SQL Server, including those new to this version (such as Xml data types and CLR integration). Some (albiet more obscure) concepts and features are left out entirely (like cryptography functions), but that does nothing to adversely affect the length of the book (it weighs in at nearly 700 pages).

The tone of the book and its organization and paired page layout are very conducive to learning at any level, and the topics covered are varied enough to ensure a well-rounded learning experience, regardless of the reader's development background.