My Cart (0)

Customer Service 1-800-221-5528

Murach’s SQL Server 2012 for Developers

by Bryan Syverson and Joel Murach
23 chapters, 794 pages, 341 illustrations
Published August 2012
ISBN 978-1-890774-69-1
eBook: $49.50

When we published the first edition of this book, we heard from plenty of people who thought it was a great way to learn SQL Server from scratch. But we also heard from a lot of experienced developers who read it and were amazed to discover how much they didn’t know about SQL. That’s why we’ve dubbed this book "the SQL book that most developers don’t even know they need."

College Instructors

Go to our instructor’s site to learn more about this book and its instructor’s materials.

Although I have used SQL Server on a daily basis for over 15 years, I was amazed at the number of new things that I learned while reading this book. I used a couple of the ideas to create a noticeable improvement in response time for one of my client/server projects."

Brian Mishler, Orlando .NET User Group

  • About this Book
  • Table of Contents
  • FREE Downloads
  • Book FAQs
  • Corrections
  • Reviews

What this book does

Section 1: An introduction to SQL

In this section, you’ll learn the concepts and terms you need for working with any database. You’ll also learn how to use Microsoft SQL Server 2012 and the Management Studio to run SQL statements on your own PC.

Section 2: The essential SQL skills

In this section, you’ll learn all the skills for retrieving data from a database and for adding, updating, and deleting that data. These skills move from the simple to the complex so you won’t have any trouble if you’re a SQL novice. And they present skills like using outer joins, summary queries, and subqueries that will raise your SQL expertise if you already have SQL experience.

Section 3: Database design and implementation

In this section, you’ll learn how to design a database and how to implement that design by using either SQL statements or the Management Studio. When you’re done, you’ll be able to design and implement your own databases. But even if you’re never called upon to do that, this section will give you the perspective that will make you a better SQL programmer.

Section 4: Advanced SQL skills

In this section, you’ll learn the skills for working with database features like views, scripts, stored procedures, functions, triggers, cursors, and transactions. You’ll also learn how to manage database security, and you’ll learn how to use the features for working with XML and BLOB data. These are the features that give a database management system much of its power.

Section 5: CLR integration

In this section, you’ll learn how to use the CLR integration feature along with a 2012 feature called SSDT (SQL Server Data Tools) to create database objects using the C# or Visual Basic programming language. That includes database objects such as stored procedures, functions, and triggers that can be created using SQL, as well as user-defined types and aggregate functions that can’t be created by using SQL.

Who this book is for

Application developers

I think it’s fair to say that most developers don’t know enough about SQL and the database management system they’re using. As one customer put it, "I know just enough about SQL Server to be a little bit dangerous!"

That’s why developers often code SQL statements that don’t perform as efficiently as they ought to. That’s why they aren’t able to code some of the queries that they need for their applications. And that’s why they don’t take advantage of all the features that SQL Server has to offer.

That’s also why this book should be required reading for every application programmer who uses SQL Server. It shows you how to code the SQL statements that you need for your applications. It shows you how to code these statements so they run efficiently. And it shows you how to take advantage of the most useful advanced features that SQL Server has to offer.

Anyone who wants to become a database administrator

This book is also the right first book for anyone who wants to become a database administrator. Although this book doesn’t present all of the advanced skills that are needed by a DBA, it will get you started. Once you’ve finished it, you’ll be prepared for more advanced books on the subject.

Anyone who wants to learn standard SQL

This book is also a good choice for anyone who wants to learn standard SQL. Since SQL is a standard language for accessing database data, most of the SQL code in this book will work with any database management system. As a result, once you use this book to learn how to use SQL to work with a SQL Server database, you can transfer most of what you have learned to another DBMS such as Oracle, DB2, or MySQL.

What the prerequisites are

Although you will progress through this book more quickly if you have some development experience, everything you need to know about databases and SQL is presented in this book. As a result, you don’t need to have any programming background to use this book to learn SQL.

However, if you want to use C# or Visual Basic to work with a SQL Server database as described in chapters 20 through 23, you need to have some experience using C# or Visual Basic to write ADO.NET code. For example, these chapters assume you can understand the code that’s presented in chapter 20 of Murach’s C# 2012 or chapter 16 of Murach’s Visual Basic 2012.

What software you need

For chapters 1 through 19

For chapters 1 through 19, you can download all the software you need from Microsoft’s website for free. That includes:

  • SQL Server 2012 Express
  • Express Edition of the SQL Server Management Studio

In appendix A in the book, you’ll find complete instructions for installing these items on your PC. And in chapter 2, you’ll learn how to use them.

For chapters 20 through 23

For chapter 20, on BLOBs, you can use an Express Edition of Visual C# or Visual Basic. These products are also available from Microsoft’s website for free.

However, for chapters 21 through 23, on CLR integration, you’ll need to install:

  • one of the full editions of Visual Studio
  • SQL Server Data Tools (SSDT)

Like the Express Editions, SSDT is available for free. Unfortunately, the full editions of Visual Studio are not. However, you can download and install a 90-day trial version of these editions from the Microsoft website if you just want to try out certain features.

What people say about this book

"If you are at all new to relational databases or SQL Server, then I recommend this as an excellent starting point that may well be the only book you will need.... The most positive feature of the book is the simple informative writing. Wow! I think a lot of thought went into this book before they ever wrote a word."
--Michael Robbins, Denver Visual Studio .NET Users Group

"Although I have used SQL Server on a daily basis for over 15 years, I was amazed at the number of new things that I learned while reading this book. I used a couple of the ideas to create a noticeable improvement in response time for one of my client/server projects."
--Brian Mishler, Orlando .NET User Group

"The Perfect Book for Application Programmers: If you’re new to using SQL Server in your applications, this book will save you a lot of time. It teaches you about SQL, database design, a lot about admin (which many developers have to do; not everywhere has dedicated DBAs), and even provides C#/Visual Basic.NET example code. The advanced SQL is excellent. Highly recommended."
--David Bolton, Guide for About.com C/C++/C#

"I have recommended this book and others in the series to developers and architects as an outstanding reference for application and database development.... From an academic perspective, I believe instructors will find this text comprehensive and easy-to-use. The student does not receive assignments that are abstract and pie-in-the-sky, but rather they develop projects comparable to real-life development activity."
--Eric Nothiesen, Enterprise Developers Guild

"This book exceeds my expectations for books that teach database and software development. A wonderful book for both learning and mastering SQL Server 2012."
--David Haertzen; posted at Infogoal.com

View the table of contents for this book in a PDF: Table of Contents (PDF)

Click on any chapter title to display or hide its content.

Section 1 An introduction to SQL

Chapter 1 An introduction to relational databases and SQL

An introduction to client/server systems

The hardware components of a client/server system

The software components of a client/server system

Other client/server system architectures

An introduction to the relational database model

How a database table is organized

How the tables in a relational database are related

How the columns in a table are defined

How relational databases compare to other data models

An introduction to SQL and SQL-based systems

A brief history of SQL

A comparison of Oracle, DB2, MySQL, and SQL Server

The Transact-SQL statements

An introduction to the SQL statements

Typical statements for working with database objects

How to query a single table

How to join data from two or more tables

How to add, update, and delete data in a table

SQL coding guidelines

How to work with other database objects

How to work with views

How to work with stored procedures, triggers, and user-defined functions

How to use SQL from an application program

Common data access models

How to use ADO.NET from a .NET application

Visual Basic code that retrieves data from a SQL Server database

C# code that retrieves data from a SQL Server database

Chapter 2 How to use the Management Studio

An introduction to SQL Server 2012

A summary of the SQL Server 2012 tools

How to start and stop the database engine

How to enable remote connections

An introduction to the Management Studio

How to connect to a database server

How to navigate through the database objects

How to manage the database files

How to attach a database

How to detach a database

How to back up a database

How to restore a database

How to set the compatibility level for a database

How to view and modify the database

How to view the database diagrams

How to view the column definitions of a table

How to modify the column definitions

How to view the data of a table

How to modify the data of a table

How to work with queries

How to enter and execute a query

How to handle syntax errors

How to open and save queries

An introduction to the Query Designer

How to use Books Online

How to display Books Online

How to look up information

Section 2 The essential SQL skills

Chapter 3 How to retrieve data from a single table

An introduction to the SELECT statement

The basic syntax of the SELECT statement

SELECT statement examples

How to code the SELECT clause

How to code column specifications

How to name the columns in a result set

How to code string expressions

How to code arithmetic expressions

How to use functions

How to use the DISTINCT keyword to eliminate duplicate rows

How to use the TOP clause to return a subset of selected rows

How to code the WHERE clause

How to use comparison operators

How to use the AND, OR, and NOT logical operators

How to use the IN operator

How to use the BETWEEN operator

How to use the LIKE operator

How to use the IS NULL clause

How to code the ORDER BY clause

How to sort a result set by a column name

How to sort a result set by an alias, an expression, or a column number

How to retrieve a range of selected rows

Chapter 4 How to retrieve data from two or more tables

How to work with inner joins

How to code an inner join

When and how to use correlation names

How to work with tables from different databases

How to use compound join conditions

How to use a self-join

Inner joins that join more than two tables

How to use the implicit inner join syntax

How to work with outer joins

How to code an outer join

Outer join examples

Outer joins that join more than two tables

Other skills for working with joins

How to combine inner and outer joins

How to use cross joins

How to work with unions

The syntax of a union

Unions that combine data from different tables

Unions that combine data from the same table

How to use the EXCEPT and INTERSECT operators

Chapter 5 How to code summary queries

How to work with aggregate functions

How to code aggregate functions

Queries that use aggregate functions

How to group and summarize data

How to code the GROUP BY and HAVING clauses

Queries that use the GROUP BY and HAVING clauses

How the HAVING clause compares to the WHERE clause

How to code complex search conditions

How to summarize data using SQL Server extensions

How to use the ROLLUP operator

How to use the CUBE operator

How to use the GROUPING SETS operator

How to use the OVER clause

Chapter 6 How to code subqueries

An introduction to subqueries

How to use subqueries

How subqueries compare to joins

How to code subqueries in search conditions

How to use subqueries with the IN operator

How to compare the result of a subquery with an expression

How to use the ALL keyword

How to use the ANY and SOME keywords

How to code correlated subqueries

How to use the EXISTS operator

Other ways to use subqueries

How to code subqueries in the FROM clause

How to code subqueries in the SELECT clause

Guidelines for working with complex queries

A complex query that uses subqueries

A procedure for building complex queries

How to work with common table expressions

How to code a CTE

How to code a recursive CTE

Chapter 7 How to insert, update, and delete data

How to create test tables

How to use the SELECT INTO statement

How to use a copy of the database

How to insert new rows

How to insert a single row

How to insert multiple rows

How to insert default values and null values

How to insert rows selected from another table

How to modify existing rows

How to perform a basic update operation

How to use subqueries in an update operation

How to use joins in an update operation

How to delete existing rows

How to perform a basic delete operation

How to use subqueries and joins in a delete operation

How to merge rows

How to perform a basic merge operation

How to code more complex merge operations

Chapter 8 How to work with data types

A review of the SQL data types

Data type overview

The numeric data types

The string data types

The date/time data types

The large value data types

How to convert data

How data conversion works

How to convert data using the CAST function

How to convert data using the CONVERT function

How to use the TRY_CONVERT function

How to use other data conversion functions

Chapter 9 How to use functions

How to work with string data

A summary of the string functions

How to solve common problems that occur with string data

How to work with numeric data

A summary of the numeric functions

How to solve common problems that occur with numeric data

How to work with date/time data

A summary of the date/time functions

How to parse dates and times

How to perform operations on dates and times

How to perform a date search

How to perform a time search

Other functions you should know about

How to use the CASE function

How to use the IIF and CHOOSE functions

How to use the COALESCE and ISNULL functions

How to use the GROUPING function

How to use the ranking functions

How to use the analytic functions

Section 3 Database design and implementation

Chapter 10 How to design a database

How to design a data structure

The basic steps for designing a data structure

How to identify the data elements

How to subdivide the data elements

How to identify the tables and assign columns

How to identify the primary and foreign keys

How to enforce the relationships between tables

How normalization works

How to identify the columns to be indexed

How to normalize a data structure

The seven normal forms

How to apply the first normal form

How to apply the second normal form

How to apply the third normal form

When and how to denormalize a data structure

Chapter 11 How to create and maintain databases, tables, and sequences with SQL statements

An introduction to DDL

The SQL statements for data definition

Rules for coding object names

How to create databases, tables, and indexes

How to create a database

How to create a table

How to create an index

How to use snippets to create database objects

How to use constraints

An introduction to constraints

How to use check constraints

How to use foreign key constraints

How to change databases and tables

How to delete an index, table, or database

How to alter a table

How to work with sequences

How to create a sequence

How to use a sequence

How to delete a sequence

How to alter a sequence

The script used to create the AP database

How the script works

How the DDL statements work

Chapter 12 How to use the Management Studio for database design

How to work with a database

How to create a database

How to delete a database

How to work with tables

How to create, modify, or delete a table

How to work with foreign key relationships

How to work with indexes and keys

How to work with check constraints

How to examine table dependencies

How to generate scripts

How to generate scripts for databases and tables

How to generate a change script when you modify a table

Section 4 Advanced SQL skills

Chapter 13 How to work with views

An introduction to views

How views work

Benefits of using views

How to create and manage views

How to create a view

Examples that create views

How to create an updatable view

How to delete or modify a view

How to use views

How to update rows through a view

How to insert rows through a view

How to delete rows through a view

How to use the catalog views

How to use the View Designer

How to create or modify a view

How to delete a view

Chapter 14 How to code scripts

An introduction to scripts

How to work with scripts

The Transact-SQL statements for script processing

How to work with variables and temporary tables

How to work with scalar variables

How to work with table variables

How to work with temporary tables

A comparison of the five types of Transact-SQL table objects

How to control the execution of a script

How to perform conditional processing

How to test for the existence of a database object

How to perform repetitive processing

How to handle errors

How to use surround-with snippets

Advanced scripting techniques

How to use the system functions

How to change the session settings

How to use dynamic SQL

A script that summarizes the structure of a database

How to use the SQLCMD utility

Chapter 15 How to code stored procedures, functions, and triggers

Procedural programming options in Transact-SQL

Scripts

Stored procedures, user-defined functions, and triggers

How to code stored procedures

An introduction to stored procedures

How to create a stored procedure

How to declare and work with parameters

How to call procedures with parameters

How to work with return values

How to validate data and raise errors

A stored procedure that manages insert operations

How to pass a table as a parameter

How to delete or change a stored procedure

How to work with system stored procedures

How to code user-defined functions

An introduction to user-defined functions

How to create a scalar-valued function

How to create a simple table-valued function

How to create a multi-statement table-valued function

How to delete or change a function

How to code triggers

How to create a trigger

How to use AFTER triggers

How to use INSTEAD OF triggers

How to use triggers to enforce data consistency

How to use triggers to work with DDL statements

How to delete or change a trigger

Chapter 16 How to work with cursors

How to use cursors in SQL Server

An introduction to cursors

The seven types of SQL Server cursors

SQL statements for cursor processing

How to use cursors to retrieve data

How to declare a cursor

How to retrieve a row using a cursor

How to use the @@FETCH_STATUS system function

How to use the @@CURSOR_ROWS system function

How to modify data through a cursor

How to use the cursor concurrency options

How to update or delete data through a cursor

Additional cursor processing techniques

How to use cursors with dynamic SQL

How to code Transact-SQL cursors for use by an application program

Chapter 17 How to manage transactions and locking

How to work with transactions

How transactions maintain data integrity

SQL statements for handling transactions

How to work with nested transactions

How to work with save points

An introduction to concurrency and locking

How concurrency and locking are related

The four concurrency problems that locks can prevent

How to set the transaction isolation level

How SQL Server manages locking

Lockable resources and lock escalation

Lock modes and lock promotion

Lock mode compatibility

How to prevent deadlocks

Two transactions that deadlock

Coding techniques that prevent deadlocks

Chapter 18 How to manage database security

How to work with SQL Server login IDs

An introduction to SQL Server security

How to change the authentication mode

How to create login IDs

How to delete or change login IDs or passwords

How to work with database users

How to work with schemas

How to work with permissions

How to grant or revoke object permissions

The SQL Server object permissions

How to grant or revoke schema permissions

How to grant or revoke database permissions

How to grant or revoke server permissions

How to work with roles

How to work with the fixed server roles

How to work with user-defined server roles

How to display information about server roles and role members

How to work with the fixed database roles

How to work with user-defined database roles

How to display information about database roles and role members

How to deny permissions granted by role membership

How to work with application roles

How to manage security using the Management Studio

How to work with login IDs

How to work with the server roles for a login ID

How to assign database access and roles by login ID

How to assign user permissions to database objects

How to work with database permissions

Chapter 19 How to work with XML

An introduction to XML

An XML document

An XML schema

How to work with the xml data type

How to store data in the xml data type

How to work with the XML Editor

How to use the methods of the xml data type

An example that parses the xml data type

Another example that parses the xml data type

How to work with XML schemas

How to add an XML schema to a database

How to use an XML schema to validate the xml data type

How to view an XML schema

How to drop an XML schema

Two more skills for working with XML

How to use the FOR XML clause of the SELECT statement

How to use the OPENXML statement

Chapter 20 How to work with BLOBs

An introduction to BLOBs

Pros and cons of storing BLOBs in files

Pros and cons of storing BLOBs in a column

When to use FILESTREAM storage for BLOBs

How to use SQL to work with a varbinary(max) column

How to create a table with a varbinary(max) column

How to insert, update, and delete binary data

How to retrieve binary data

A .NET application that uses a varbinary(max) column

The user interface for the application

The event handlers for the form

A data access class that reads and writes binary data

How to use FILESTREAM storage

How to enable FILESTREAM storage on the server

How to create a database with FILESTREAM storage

How to create a table with a FILESTREAM column

How to insert, update, and delete FILESTREAM data

How to retrieve FILESTREAM data

A data access class that uses FILESTREAM storage

Section 5 CLR integration

Chapter 21 An introduction to CLR integration

An introduction to CLR integration

How CLR integration works

The five types of CLR objects

When to use CLR objects

How to enable CLR integration

How to use Visual Studio to work with CLR objects

How to start a SQL Server Database project

How to specify the type of CLR object

How to enter and edit the code for CLR objects

The code for a CLR object

How to create a database for testing

How to compile and deploy CLR objects

How to test and debug a CLR object

How to use SQL to work with CLR objects

How to deploy an assembly

How to deploy a CLR object

How to drop an assembly

Chapter 22 How to code CLR stored procedures, functions, and triggers

How to work with CLR stored procedures

How to use the SqlTypes namespace to map data types

How to declare a stored procedure

How to create a connection

How to use the SqlPipe object to return data

How to use output parameters to return data

How to return an integer value

How to raise an error

A stored procedure that manages insert operations

A script that calls the stored procedure

How to work with CLR functions

How to declare a function

How to work with the SqlFunction attribute

A scalar-valued function that returns an amount due

A table-valued function that returns a table with two columns

How to work with CLR triggers

How to declare a trigger

How to work with the SqlTrigger attribute

A trigger that updates a column

How to use the SqlTriggerContext object

A trigger that works with DDL statements

Chapter 23 How to code aggregate functions and user-defined types

How to work with aggregate functions

How to declare an aggregate

How to work with the SqlUserDefinedAggregate attribute

An aggregate that returns a trimmed average

An aggregate that returns a comma-delimited string

How to work with user-defined types

How to declare a user-defined type

How to work with the SqlUserDefinedType attribute

A user-defined type for an email address

SQL that works with a user-defined type

Appendixes

Appendix A How to install the required software for this book

The four editions of SQL Server 2012 Express

How to install SQL Server 2012 Express with Tools

How to install Visual Studio and SQL Server Data Tools

Appendix B How to install the files and databases used in this book

How to install the files included in the book download

How to create the databases for this book

How to restore a database

Appendix C Coding and syntax conventions

 

Sample chapters

Chapter 3: How to retrieve data with the SQL SELECT statement

After two introductory chapters (not included in this download), chapter 3 shows how to use SQL SELECT statements to retrieve data from a single table. Our hope is that this will give you a better idea of how well our book works and how much there is to learn about SQL.

Chapter 3 PDF (681Kb) Download Now

Book examples and exercises

This download includes:

  • The databases that are used in the book examples and chapter exercises
  • The scripts for every SQL example presented in the book
  • The C# projects presented in chapters 20 through 23
  • Visual Basic versions of the C# projects in chapters 20 through 23
  • Solutions to the exercises in the book so you can check your work

Appendix B in the book describes how to install and use these files.

Exe file for Windows (10.6Mb) Download Now

Zip file for any system (10.6Mb) Download Now

Below are the answers to the questions that have come up most often about this book. If you have any questions that aren’t answered here, please email us. Thanks!

Why do I get an error about a version mismatch when I try to attach the databases for this book?

This error occurs when you have an older version of SQL Server (besides the 2012 version) installed on your system. Often, you aren’t even aware that this older version is on your system because the software was installed as part ofanother product, such as Visual Studio.

To solve this problem, you can use the SQL Server Configuration Manager to view the instances of SQL Server that are running on your computer. This lets you view their names. Then, you can use the SQL Server Management Studio to connect to the correct instance. After you connect, you can make sure you are connected to the correct version by viewing the version number of the server in the Object Explorer window.

Here’s how the version numbers correspond to the product names:

Version 9.x→SQL Server 2005

Version 10.x→SQL Server 2008

Version 11.x→SQL Server 2012

There are no book corrections that we know of at this time. But if you find any, please email us, and we’ll post any corrections that affect the technical accuracy of the book here. Thank you!

Sammy Hinson

Tuesday, 15 March 2016

This book is great for the beginner and fills in the blanks for the seasoned SQL programmer and user. A must for reference. Another winner for Murach's!

To leave a review, please log in to your account.     Log In Here

Our Ironclad Guarantee

You must be satisfied. Try our print books for 30 days or our eBooks for 14 days. If they aren't the best you've ever used, you can return the books or cancel the eBooks for a prompt refund. No questions asked!

Contact Murach Books

For orders and customer service:

1-800-221-5528

Weekdays, 8 to 4 Pacific Time

College Instructors

If you're a college instructor who would like to consider a book for a course, please visit our website for instructors to learn how to get a complimentary review copy and the full set of instructional materials.