Introduction

Welcome to Sybase System 11 SQL Server

Congratulations! You have bought the premier multi-platform database management product on the market today. System 11 SQL Server continues to provide the performance, scalability, and value characteristic of Sybase database products.

System 11 SQL Server is a relational database management system (RDMS or RDBMS, depending on where you grew up!) that is capable of handling large amounts of data and many concurrent users while preserving data integrity and providing many advanced administration and data distribution capabilities.

Here are a few of the capabilities of SQL Server:

•  Complete data integrity protection, from complex transaction support and advanced security to objects that support your business rules as an implicit part of your database.

•  Fast and efficient multi-tasking and multi-threading on any platform, including support for symmetric multi-processing on SMP systems. System 11 SQL Server runs on operating systems from NetWare and Windows NT to OSF, Solaris, AIX and HP/UX.

•  Outstanding price/performance.

Is This Book for Me?

This Unleashedbook is meant for anyone who is responsible for designing, building, administering and tuning systems that rely on SQL Server. Among other things, this book contains performance information you may not find anywhere else, including tuning methods and advanced SQL techniques that are undocumented or not well described.

•  System Administrators will learn how to install and administer SQL Server. You will also learn about important standards and protocols to ensure that SQL Server applications can be maintained and supported for the long haul.

•  Database Administrators will learn to make the best use of SQL Server objects and datatypes and will learn to write effective stored procedures and triggers. The “Performance And Tuning” section of the book will help you understand what’s going on under the hood of the server, including a detailed analysis of the query optimizer and the physical storage mechanisms used by SQL Server. You can start to understand what performance you can reasonably expect with SQL Server so that you can focus on problems that you can really fix and on practical solutions.

•  Programmers will learn how to write code that runs well on SQL Server and will acquire a complete understanding of how the server interprets SQL statements when running. We also provide a solid foundation for your work in C and Visual Basic with DB-Library, ct-Library, and ODBC in the “Introduction to Open Client Programming” section of this book.

What Will I Learn from This Book?

Understanding SQL Server starts with understanding—both functionally and conceptually—the tasks an administrator or programmer must perform. There are several third-party tools available in the market for working with SQL Server, but few tools that ship with the core SQL Server product. For this reason, this book is task-oriented, not tool-oriented—concentrating on the tools and commands available in the core SQL Server product. In this book, we will explain the important tasks and the concepts behind them, describe when they need to occur, then show you how to perform them in the core product. You will then have a better understanding of what the tools you may choose to use are doing behind the scenes.

What’s in This Book?

This book consists of five sections:

•  Part I, “SQL Server Architecture,” discusses client/server architecture in general and looks closely at how Sybase SQL Server implements a client/server database system.

•  Part II, “Transact-SQL Programming,” discusses the language constructs of Transact-SQL and discusses how objects are created and maintained. You will learn about the different datatypes in SQL Server and how their use can affect performance, data maintenance, and capacity. We look closely at programming issues in writing procedures and triggers, examine cursor programming, and explain the ins and outs of transaction programming.

•  Part III, “Performance and Tuning,” describes in detail how SQL Server stores data, how it decides on optimization strategies, and how locking and multi-user issues impact overall performance. Perhaps the most important point here is that you can start to understand the kinds of expectations you should have for the system: when the query performance is as good as you can expect, and when it can get a lot better. Chapter 22, “Common Performance and Tuning Problems,” describes some performance pitfalls that are easy for newcomers to SQL Server to miss, but also easy to avoid once you know they are there.

•  Part IV, “System Administration,” describes the tasks needed to get SQL Server up the first time and to keep it running all the time. (The Performance and Tuning section worries about making SQL Server fast; the System Administration section worries about making it reliable.) You will learn to make backups, how to restore them, and how to develop a backup and maintenance regimen that ensures your data is safe. Chapter 35, “Administering Very Large SQL Server Databases,” looks at some of the issues associated with building very large databases (VLDBs).

•  Part V, “Introduction to Open Client Programming,” provides a first look at the programming issues with SQL Server. Of course, this is a topic for a whole other book, but you learn in these chapters the basics of DB-Library, ODBC, and ct-Library programming for both Visual Basic and C++.

What’s Next?

If SQL Server is not yet installed, you may want to start with Chapter 24, “SQL Server Installation and Connectivity,” to get SQL Server up and running, then continue on to the other sections of this book.

If You Are New to SQL Server…

Start at the beginning and read about how this stuff is built. (You will never make the time later and you really need to understand this stuff to make good use of the server.)

As a System Administrator, you should read Chapter 3, “Introduction to Transact-SQL,” because you will need to know some SQL. Then you can jump right to Chapter 23, “Overview of System Administration,” and work through the administration section.

As a Database Administrator, you will need to understand Transact-SQL programming, so work through the book in order from Chapter 3 to about Chapter 14. Before you design a system ready to go into production, you should finish the performance section and try some stuff using a hands-on approach. Then you need to understand thoroughly Chapters 28 (on database logging) and 31 (on server configuration and performance). Read Chapter 22, “Common Performance and Tuning Problems,” before you take a production system online. If you are working with large databases, be sure to read Chapter 35.

As a programmer, you should probably read the chapters in order through Chapter 10, then make the time to look at Chapters 12 through 16 to make sure you understand the kinds of performance issues and problems you might run into. If you will be writing in Visual Basic or C, read the section on Open Client Programming. (It may help you choose the best architecture for your application.) Finally, make the time to read 19, “Application Design for Performance,” and Chapter 20, “Advanced Transact-SQL Programming.”

Conventions Used in This Book

We’ve tried to be consistent in our use of conventions here. Names of commands and stored procedures are presented in a special monospaced, computer typeface. In the text itself, we have tried to present all SQL keywords in the text in upper case, but because SQL Server does not make a distinction between upper- and lowercase for SQL keywords in actual SQL code, many code examples show SQL keywords in lowercase.

We have purposely not capitalized the names of objects, databases, or logins/users where that would be incorrect. That may have left sentences starting like this, “sysdatabases includes…” with an initial lowercase character.

Code and output examples are presented separately from regular paragraphs and also are in a monospaced, computer typeface. Here is an example:

select id, name, audflags
from sysobjects
where type != "S"
id          name                           audflags
---------- ------------------------------ ----------
144003544   marketing_table                130

When we provide syntax for a command, we’ve attempted to follow the following conventions:

Consider the following example:

grant {all | permission_list} on object [(column_list)] to {public |
user_or_group_name [, …]}

In this case, the object value is required, but the column_list is optional. Note also that items shown in plain computer type, such as grant, public, or all, should be entered literally as shown. Placeholders are presented in italics, such as permission_list and user_or_group_name; a placeholder is a generic term for which you must supply a specific value or values. The ellipses in the square brackets following user_or_group_name indicates that multiple user or group names can be specified separated by commas. You can specify either the keyword public or one or more user or group names, but not both.

Our editors were enormously helpful in finding inconsistencies in how we used these conventions. We apologize for any that remain; they are entirely the fault of the authors.

Disclaimer

Please note that much of the material in this book was based upon a pre-release version of System 11 SQL Server. The syntax or output of some of the SQL Server commands and procedures may be different in the final General Availability (GA) release of System 11 SQL Server.

Good Luck!

You are in good shape now. You have chosen a fine platform for building database applications, one that can provide outstanding performance and rock-solid reliability at a reasonable cost. And you now have the information you need to make the best of it.

We wish you all the best with SQL Server.