Murach’s SQL Server 2022 for DevelopersOur 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!
Murach’s SQL Server 2012 for Developers
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!