Table of Contents
| Foreword | XXIII |
| Preface | XXV |
| Preface to the First Edition | XXIX |
| Part I | Overview | |
| Chapter 1 | The Evolution of Microsoft SQL Server: 1989 to 1999 | 3 |
| Ron's Story | 6 |
| Kalen's Story | 7 |
| Microsoft SQL Server Ships | 8 |
| Development Roles Evolve | 10 |
| OS/2 and Friendly Fire | 12 |
| Version 4.2 | 13 |
| SQL Server for Windows NT | 16 |
| Success Brings Fundamental Change | 21 |
| The End of Joint Development | 23 |
| The Charge to SQL95 | 25 |
| The Next Version | 27 |
| The Secret of the Sphinx | 28 |
| The New Future | 30 |
| Chapter 2 | A Tour of SQL Server | 31 |
| The SQL Server Engine | 32 |
| DBMS-Enforced Data Integrity | 38 |
| Transaction Processing | 43 |
| Symmetric Server Architecture | 45 |
| Security | 47 |
| High Availability | 49 |
| Distributed Data Processing | 50 |
| Data Replication | 51 |
| Systems Management | 54 |
| SQL Server Utilities and Extensions | 61 |
| Client Development Interfaces | 68 |
| Summary | 71 |
| Part II | Architectural Overview | |
| Chapter 3 | SQL Server Architecture | 75 |
| The SQL Server Engine | 75 |
| Managing Memory | 99 |
| Transaction Logging and Recovery | 107 |
| The SQL Server Kernel and Interaction with the Operating System | 112 |
| Summary | 118 |
| Part III | Using Microsoft SQL Server | |
| Chapter 4 | Planning for and Installing SQL Server | 121 |
| SQL Server Editions | 121 |
| Hardware Guidelines | 124 |
| Hardware Components | 128 |
| The Operating System | 150 |
| The File System | 151 |
| Security and the User Context | 152 |
| Licensing | 154 |
| Network Protocols | 159 |
| Character Sets and Sort Orders | 163 |
| Installing SQL Server | 171 |
| Basic Configuration After Installation | 172 |
| Remote and Unattended Installation | 174 |
| Summary | 178 |
| Chapter 5 | Databases and Database Files | 179 |
| Special System Databases | 180 |
| Database Files | 182 |
| Creating Databases | 184 |
| Expanding and Shrinking Databases | 186 |
| Changes in Log Size | 189 |
| Database Filegroups | 193 |
| Altering a Database | 196 |
| Databases Under the Hood | 198 |
| Database Options | 203 |
| Other Database Considerations | 210 |
| Summary | 212 |
| Chapter 6 | Tables | 213 |
| Creating Tables | 214 |
| Internal Storage--The Details | 229 |
| Indexes | 246 |
| User-Defined Datatypes | 250 |
| Identity Property | 251 |
| Constraints | 255 |
| Altering a Table | 292 |
| Temporary Tables | 295 |
| Summary | 298 |
| Chapter 7 | Querying Data | 299 |
| The SELECT Statement | 299 |
| Joins | 302 |
| Dealing with NULL | 322 |
| Subqueries | 332 |
| Views and Derived Tables | 345 |
| Other Search Expressions | 350 |
| Summary | 389 |
| Chapter 8 | Modifying Data | 391 |
| Basic Modification Operations | 391 |
| Data Modification Internals | 421 |
| Summary | 441 |
| Chapter 9 | Programming with Transact-SQL | 443 |
| Transact-SQL as a Programming Language | 443 |
| Transact-SQL Programming Constructs--The Basics | 447 |
| Summary | 506 |
| Chapter 10 | Batches, Transactions, Stored Procedures, and Triggers | 507 |
| Batches | 507 |
| Transactions | 512 |
| Stored Procedures | 533 |
| Executing Batches, or What's Stored About a Stored Procedure? | 548 |
| Triggers | 575 |
| Debugging Stored Procedures and Triggers | 580 |
| Working with Text and Image Data | 584 |
| Environmental Concerns | 598 |
| Summary | 606 |
| Chapter 11 | Cursors | 607 |
| Cursor Basics | 608 |
| Cursors and ISAMs | 610 |
| Cursor Models | 615 |
| Appropriate Use of Cursors | 621 |
| Working with Transact-SQL Cursors | 632 |
| Cursor Variables | 652 |
| Summary | 660 |
| Chapter 12 | Transact-SQL Examples and Brainteasers | 661 |
| Using Triggers to Implement Referential Actions | 661 |
| Brainteasers | 670 |
| Summary | 726 |
| Chapter 13 | Locking | 727 |
| The Lock Manager | 727 |
| Lock Types for User Data | 734 |
| Lock Compatibility | 748 |
| Bound Connections | 750 |
| Row-Level vs. Page-Level Locking | 752 |
| Locking Hints and Trace Flags | 754 |
| Summary | 755 |
| Part IV | Performance and Tuning | |
| Chapter 14 | Optimizing Query Performance | 759 |
| The Development Team | 760 |
| Application and Database Design | 760 |
| Planning for Peak Usage | 766 |
| Perceived Response Time for Interactive Systems | 766 |
| Prototyping, Benchmarking, and Testing | 768 |
| Creating Useful Indexes | 772 |
| Using Stored Procedures and Caching Mechanisms | 780 |
| Concurrency and Consistency Tradeoffs | 788 |
| Resolving Blocking Problems | 789 |
| Resolving Deadlock Problems | 794 |
| Segregating OLTP and DSS Applications | 807 |
| Optimizing Queries | 807 |
| Monitoring Query Performance | 835 |
| Summary | 848 |
| Chapter 15 | Configuration and Performance Monitoring | 849 |
| Windows NT Configuration Settings | 849 |
| SQL Server Configuration Settings | 852 |
| System Maintenance | 867 |
| Monitoring System Behavior | 868 |
| Summary | 890 |
| Suggested Reading | 891 |
| Index | 897 |
Read an Excerpt
Chapter 4: Planning for and Installing SQL Server
... Network Protocols
If you accept the default installation option during setup, SQL Server on Windows NT installs named pipes, TCP/IP Sockets, and multiprotocol as its interprocess communication (IPC) mechanism for communication with its clients. SQL Server can simultaneously use many IPC mechanisms and networking protocols (but any specific client connection uses only one for its connection). As you learned in Chapter 3, each SQL Server networking interface is known as a Net-Library and represents a specific IPC mechanism. In addition to the above three, you can install one or more of the following Net-Libraries:
- NWLink IPX/SPX
- Appletalk ADSP
- DECNet Sockets
- Banyan VINES
Both the Named Pipes and Multiprotocol Net-Libraries use protocol-independent IPC mechanisms (named pipes and RPC services). This means that you can use either interface with multiple underlying network protocols, including TCP/IP, NetBEUI, and NWLink IPX/SPX. All of the other choices imply not only the IPC mechanism but the specific network protocol that both the client and the server must use.
Although the Named Pipes Net-Library remains a good choice, its use as a default is mostly historical. Named pipes was the first, and for a while the only, IPC mechanism used by the early versions of SQL Server. Later, even when TCP/IP sockets and IPX/SPX were supported, those protocols were more difficult to configure than named pipes, requiring configuration at each client. For example, using TCP/IP required that the administrator configure an arcane IP address at every client workstation for every instance of SQL Server it might access. Now, with the sophisticated network naming services provided by Windows NT (for example, WINS, DHCP, and DNS), other Net-Libraries such as Multiprotocol and TCP/IP Sockets are almost as easy to use as Named Pipes.
Unless you have a compelling reason to choose a different network interface (the most compelling, of course, is if your existing network or network standards dictate some other network choice), use the defaults because they provide the most functionality. They are protocol independent and allow the use of Windows NT Authentication when you connect to SQL Server, which lets you provide a single logon name to your users so they don't have to log on to both the Windows NT domain and the SQL Server. Windows NT Authentication for SQL Server uses the Windows NT impersonation features, which are available only with these default networking choices. Windows NT Authentication is an important feature for convenience and ease of administration as well as for making your system more secure. Windows NT Authentication is also assumed with SQL Server replication services and when Performance Monitor connects to SQL Server.
The Multiprotocol interface is built using Windows NT RPC services. It offers one important feature that none of the other networks offers -- encryption. All conversation between the client and server can be encrypted using the encryption services provided by Windows NT. (A 40-bit key is the maximum currently allowed for export by the U.S. government, so this is the key size used for Windows NT versions sold outside the United States. Windows NT 4, sold in the United States, uses a 128-bit key for tighter security.) Your data is secure even from someone using a hardware device such as a "network sniffer" to intercept network packets right off the wire. The encryption services work across the Internet as well.
The Cost of Encryption
Encryption services impose about a 25 percent performance overhead for network traffic. However, with use on a LAN, the network is rarely the performance bottleneck in a well-designed application and the actual performance difference is usually much less noticeable. But network performance is a bigger concern with a slow WAN or Internet application. In those cases, it can become a bottleneck. Even with slow networks, however, the performance issues usually relate to how often you make requests to the server (that is, how many network "round-trips" you make) rather than the speed of the Net-Library.
When you use the Multiprotocol interface and provide multiple underlying network protocols for it to choose from, you can explicitly choose the default binding. For example, by default the Multiprotocol network interface might, behind the scenes, use named pipes over NWLink. However, you can configure the interface to choose TCP/IP sockets instead if this is important in your network. (For more details, see the name resolution information under the Multiprotocol Clients topic in the "Administering SQL Server" section of SQL Server Books Online.) The ability to dynamically enumerate network computers that run SQL Server is not available with the Multiprotocol network interface.
Microsoft internal testing has found the TCP/IP Sockets Net-Library to be the fastest networking choice. (As stated in the above sidebar, in a typical LAN you rarely see the network as a performance bottleneck. In a low-speed WAN, however, this can be an important issue.) Some network administrators have also been concerned about potentially routing NetBIOS traffic across their LANs and WANs. However, if SQL Server is not using the Named Pipes Net-Library and the Multiprotocol Net-Library is not using named pipes under the covers for IPC, SQL Server is not using NetBIOS at all and this is not a concern.
The NWLink IPX/SPX Net-Library is of most interest to those running Novell networking software on their clients accessing SQL Server. If you are using a Novell NetWare-based network and file server but your SQL Server clients use Windows 95, Windows 98, or Windows NT, using the NWLink IPX/SPX Net-Library is unnecessary. Either Named Pipes or Multiprotocol over the underlying NWLink network protocol is probably a better choice because Windows NT Authentication is not available with the NWLink IPX/SPX Net-Library. If your clients use networking software provided by Novell, NWLink IPX/SPX is probably your best choice. Server enumeration is available with NWLink IPX/SPX using the NetWare Bindery services.
Choose Banyan VINES and DECNet Sockets if you interoperate with those environments. The Banyan VINES Net-Library uses StreetTalk naming services for server enumeration and name resolution. There is no support for dynamic SQL Server enumeration on DECNet. Windows NT Authentication is not an option with either of these Net-Libraries.
Use Appletalk ADSP Net-Library if you will support Apple Macintosh clients (using the Inprise -- formerly Visigenic -- ODBC driver for Macintosh) running only Appletalk, not TCP/IP.
During installation, you must supply additional information for any of the network options you have selected -- for example, the port number or network name on which the server running SQL Server will "listen" for new connections or broadcast its existence to a network naming service. In most cases, you should accept the default unless you have a compelling reason not to. In the case of TCP/IP Sockets, you should accept the default port number of 1433. This number is reserved for use with SQL Server by the Internet Assigned Numbers Authority (IANA), and as such it should not conflict with a port used by any other server application on your computer. (This assumes that the developers of other applications also followed the proper protocol of getting assigned numbers.)
Even if you will primarily use another Net-Library for SQL Server use, we recommend that you do not remove Named Pipes. If your network fails entirely, you can still access your SQL Server machine locally using named pipes as the IPC mechanism, because it is an intrinsic service of Windows NT even when the computer is not part of a network. Named pipes can provide a convenient "last chance" way to access SQL Server if, for example, your network card has failed and network access is currently unavailable. If you decide not to use the Named Pipes Net-Library, remove it after installation is complete. The installation program assumes the existence of named pipes services so that it can operate if the computer is not in a network environment.
SQL Server on Windows 95 and Windows 98 does not support the server Named Pipes, Banyan VINES, and AppleTalk Net-Libraries. SQL Server does support the client side of Named Pipes and Banyan VINES Net-Libraries on Windows 95 and Windows 98, so Windows 95 and Windows 98 clients can use them to connect to SQL Server installations on Windows NT.
TIP If you are new to networking and don't know your IP from your DHCP, don't fret. Accept the defaults and configure your networking later as your understanding improves (or get your network administrator to help you). Although it's a good idea to understand your networking choices before installing SQL Server, you can easily change the networking options later without disturbing your SQL Server environment.
...
loading...