Some Books on Programming SQL Server 2012

It is a measure of the range of facilities that are available to the database developer nowadays that three different books on the same general topic of TSQL programming  can actually complement each other.

Microsoft SQL Server 2012 T-SQL Fundamentals


by Itzik Ben-Gan

Make no mistake, Itzik Ben-Gan knows his stuff. Each page of T-SQL Fundamentals testifies to his breadth of knowledge and years of experience with SQL Server. He drills into T-SQL with unerring precision, particularly when it comes to data manipulation language (DML). Not only does he confidently explore the SELECT statement-in all its querying glory-but he also delves into statements used to insert, update, and delete data. Yet it’s the SELECT statement that takes center stage, and for good reason. It’s the most complex and versatile of the DML players, and many of the elements used in SELECT apply also to the other statements.

At the same time, he neither limits the book to DML, nor does he confine it to T-SQL syntax and its mechanics. He also avoids providing us pages of repetitive examples with little in the way of substance. In fact, the book abounds with detailed and insightful information that’s not only useful, but that also reaches deeply into the T-SQL landscape, while providing a wide perspective of the terrain.

Take, for instance, the discussion on creating tables-one of the few sections devoted to data definition language (DDL). In his CREATE TABLE example, Ben-Gan starts with a USE statement that sets the current database context. Along with this, he explains the statement’s purpose and then discusses why its use is important. The example also includes an IF statement that contains the OBJECT_ID function to check for the table’s existence before creating it. Once again, he explains how the statement and function work and why he has included them.

These issues in themselves might not seem like such a big deal, but the combination of good coding practices and precise explanations help to ensure that those new to SQL programming establish good habits from the start and know why they’re doing what they’re doing.

Yet it’s not just his careful explanations of T-SQL language components that make this book shine, but also the range and depth of the information. When the author discusses joins, for instance, he describes how SQL Server supports two standard syntaxes for cross joins-ANSI SQL-92 and ANSI SQL-89-and then shows how to create cross joins that adhere to each standard, while explaining why one is better than the other.

And he doesn’t stop there. He describes why there’s a discrepancy between the way a SELECT statement is written and the way it is processed. He explains how NULL values are used and what they mean to statement evaluation. He shows how the new OFFSET-FETCH operator in SQL Server 2012 is superior to the TOP operator. He discusses how an ORDER BY clause affects a query and why the result set is no longer considered a table.

These are but a sample of the extensive amount of useful information contained in this book. You’ll find details about joins and subqueries and table expressions, about set operators and window functions and pivoting data, about transactions and variables and cursors and programmable routines. Ben-Gan explains each concept so completely that when you’re finished, you’ll know it inside out.

Perhaps the one aspect of the book that I take exception to is the way some of the information is presented. Although the book is chock full of great information, the novice might have a difficult time in places. The author tends to throw out terms or reference concepts or use statement elements long before they’re fully explained. For example, he mentions variables often and includes DECLARE statements in several places, but doesn’t fully discuss variables until Chapter 10. In addition, the way he first presents the SELECT statement-in terms of how it’s processed, rather than how it’s written-introduces topics in a way that might be difficult for the beginner to understand.

Someone with even a small background in databases or application development will probably be okay with the way information is presented, but I’m not so sure about those individuals just starting out. However, even complete novices have much to gain by this book, if they’re willing to stick with it, as does anyone looking for a refresher. It’s a resource rich in information and specifics and a great place to hone your T-SQL skills.

Programming Microsoft SQL Server 2012


by Leonard Lobel and Andrew Brust

When I come across a book whose title includes the terms programming and SQL Server, my first question is what exactly the authors mean by programming. Are they talking T-SQL code and data manipulation or client applications that connect to SQL Server databases? Generally, books fall into one camp or the other, giving only a slight nod to the other side.

Not so with Programming Microsoft SQL Server 2012. The book’s authors-Leonard Lobel and Andrew Brust-delve into both worlds with confidence and vigor. It doesn’t matter whether you’re developing at the database level or further up the application stack, chances are, you can benefit from this book.

One aspect of the book that makes it so effective is the authors’ no-nonsense approach to presenting information. From the first chapter about SQL Server development to the last chapter on xVelocity memory technologies, they jump right in with concrete, useful information, without the type of self-indulgent prattle you come across in some technology books. True, there are a few places where the authors slip into Microsoft marketing hype in their product descriptions, but those passages are relatively short and far between, laced into the text without being too annoying.

The authors have structured the books in three parts. The first one focuses on the core SQL Server features, such as SQL Server Data Tools and T-SQL enhancements. The second gets into extended SQL Server capabilities like XML, spatial data, and native file streaming. The third explores techniques for building client/server, n-tier, and cloud solutions.

Each part contains four-to-six chapters that are remarkably comprehensive, relevant, and provide a good balance of background information, examples, and explanations. Take, for instance, the chapter on transactions. The fact that it’s included so early in the book is itself worth noting. Transactions often seem added as an afterthought, thrown in somewhere near the end and given a cursory treatment. But understanding the importance of transactions, whether developing databases or client applications, is essential to deploying systems that can handle the high concurrency rates often found in today’s web-based systems.

The authors also have a knack for providing examples that help readers easily understand concepts. For instance, in the chapter on transactions, they first compare a transaction to buying a car. Often, when I come across such comparisons in a programming book, unrelated as they are to actual programming, I tend to dig in my heels because too often page space-and my time-is wasted in senseless analogies. (And for some reason, cars seem to be a favorite among such books.) But in this case, the authors provide their comparison quickly and painlessly and then move on to the heart of their discussion, which they support with real-world examples.

What all this points to is how carefully the authors have thought out this book’s content. And it’s not only transactions that get the full treatment. They also cover such topics as security, LINQ, SQL Azure federations, the Windows Communication Framework, SQL Server business intelligence (BI) tools, SQL CLR, client data access, and a number of other topics important to programming against SQL Server. And for each topic, they provide the background information and historical context necessary to fully comprehend that subject, often supporting their explanations with specific and informative figures, such as those found in the chapter on spatial data.

I suppose one could challenge the order in which the chapters have been presented. For example, does the chapter on xVelocity really belong in the third part? And what about the chapters on BI or SQL Azure? On the other hand, these chapters are written to stand independently of one another, and that’s what they do. You don’t need to read this book from cover-to-cover or in any particular order. Each chapter is complete in itself, and within the chapter, the authors carefully spell out where they’re taking you and give you everything you need once you’re there.

Not all programmers will necessarily be interested in reading every page of this book, but there is indeed something for everyone in just about every section. The application developer can benefit from the discussions on transactions and security. The database developer can benefit from the discussions on client data access and SQL CLR. Everyone can benefit from understanding the new features in T-SQL and SQL Azure. Certainly, many topics are too broad for a single chapter or even a single section, such as the discussions on LINQ and the BI tools, but even within the scope of this book, the authors provide information that is comprehensive, clear, and well worth taking the time to read. There’s little here that cannot provide at least some benefit to someone.

Beginning Microsoft SQL Server 2012 Programming


by Paul Atkinson and Robert Vieira

Few programming books, especially those for beginners, hit the ground running like this one. Paul Atkinson and Robert Vieira, the book’s authors, jump right in with database basics-explaining the different types of objects, data types, and identifiers-and then launch into the tools used for database development, such as SQL Server Management Studio and SQL Server Data Tools. From there, it’s a quick leap into querying data and creating schemas and tables. The rest is T-SQL history.

But it’s not a race to the finish for these authors. Each chapter provides an in-depth exploration of the topic at hand. Explanations are clear and to the point. Examples are relevant and described in detail. Figures are added to reinforce and supplement the material. All presented without wasting our time on unnecessary or tedious information meant only to show off the author’s accomplishments. The book does a remarkable job, in fact, of staying on track and delivering to beginning programmers the information they need to get started as quickly and effectively as possible.

The chapter on T-SQL basics, for example, covers what might seem a surprisingly large amount of material in such a short space. Not only do the authors introduce us to the SELECT statement and its many clauses, but also to the INSERT, UPDATE, and DELETE statements. That’s a heck of a lot to cover in one chapter, maybe even too much for some, but within the chapter’s confines, the material is so well organized and thoughtfully presented and the explanations so clear and concise, that despite what might seem too aggressive an undertaking, the authors have provided us with a cohesive and comprehensive picture of how to access and manipulate data.

That’s not to say any one topic cannot be expanded further. The SELECT statement alone can easily warrant its own chapter, if not more, and that’s only to cover the basics. In fact, there are times it might be good to have more information. For example, I think it’s important for SQL Server programmers to understand how the query engine processes a SELECT statement, but that information seems to be missing from the discussion. And I think we need a bit more information in other places as well. For instance, additional details about T-SQL in general would have been nice, not just a cursory note regarding ANSI/ISO compliance.

Still, the places that I think could have been beefed up more represent only a small portion of the book, and for the most part, those new to SQL Server programming will find just about everything they need to get started. At some point, they might want to dig around for other resources to gain a better sense of the conceptual underpinnings of many of the topics discussed, but what the authors have given us provides a great starting point for most beginners.

Perhaps some of my favorite aspects of this book are the many extras I found along the way, information I don’t often see in this sort of book. For example, the authors not only explain how to use T-SQL to create tables, but also how to use the GUI features in SQL Server Management Studio to achieve the same results. They also provide such savory tidbits as how audit a view, display an estimated query plan, and create a database diagram.

I especially appreciated their coverage of SQL Server’s business intelligence (BI) tools. In most SQL Server programming books, you’re lucky to get a chapter on BI, if that much. Yet there’s a reason that Microsoft has invested such significant resources in its BI offerings. BI has become a critical consideration in most data-driven systems, and more often than not, SQL Server programmers must be versed in at least some of SQL Server’s BI capabilities.

That said, even with the three chapters devoted to BI in this book, the authors could have easily done more. Whole books are written on Analysis Services alone. The same goes for Reporting Services and Integration Services. You can, in fact, say that about many of the topics covered in this book, from XML to subqueries to database design. But within the space the authors are working, they manage to provide information that is comprehensive and clear, without wandering down some ill or forgotten trail.

I do think the book could have been organized differently. For instance, I’m not quite sure why they discuss database design after creating tables, or why they discuss creating tables in between joins and subqueries. Certainly, deciding on order is tricky business, and regardless of the authors’ choices, there’s so much good information in this book that I think anyone new to SQL Server programming can benefit, and so too can those who have a few years under their belts. The authors’ direct and accessible style-along with their jump-right-in methodology-makes the book a valuable resource to a wide range of readers, although it’s the beginners who have the most to gain.