Temporarily Out of Stock Online
Senior Software Engineer STEP Technology. With 15 years database experience, Rob has moved from analyst, coding and architectural roles with primary focus on data. With 4 years SQL Server experience, focus is primarily on database architecture and advanced database and query design to produce a string of qualifications.
About the Author
Robert Vieira is Senior Software Engineer of STEP Technology. With 15 years database experience, Rob has moved from analyst, coding and architectural roles with primary focus on data. With 4 years SQL Server experience, focus is primarily on database architecture and advanced database and query design to produce a string of qualifications.
Read an Excerpt
The Enterprise ManagerThe Enterprise Manger, or EM, is pretty much home base when administering a SQL Server. It provides a variety of functionality to manage your server using a relatively easy to use graphical user interface. With version 7.0, it also has tools to assist the development side of the equation. EM now supports Entity Relationship (ER) diagramming (which we'll look into in Chapter 8 and again in Chapter 20 - albeit with somewhat non-standard ER diagrams) as well as greatly improved tools for creating tables, views, triggers and stored procedures. In addition, EM supports access to most of the other tools covered in this chapter.
For the purposes of this book, we're not going to cover everything that EM has to offer, but let's take a quick run down of the things you can do from EM:
- Create, edit and delete databases and database objects
- Create, edit and delete Data Transformation (DTS) packages
- Manage scheduled tasks such as backups and the execution of DTS package runs
- Display current activity, such as who is logged on, what objects are locked, and from which client they are running
- Setup web publishing jobs
- Manage security, including such items as roles, logins, and remote and linked servers
- Initiate and manage the SQL Mail Service
- Create and manage Full-Text Search Catalogs
- Manage configuration settings for the server
- Create and manage both publishing and subscribing databases for replication
In addition to the tasks that truly belong to EM, you can also run several of the other SQL Server tools, such as the Query Analyzer and the SQL Server Profiler from EM menu choices.
We will be seeing a great deal of the EM throughout this book.
Data Transformation Services (DTS)
Your friend and mine - that's what DTS is. I simply sit back in amazement every time I look at this addition to SQL Server. To give you a touch of perspective here, I've done a couple of Decision Support Systems (DSS) projects over the years. (These are usually systems that don't have online data going in and out, but instead pull data together to help management make decisions.) They have gathered data from a variety of sources and pumped them into one centralized database to be used for centralized reporting.
These projects can get very expensive very quickly, as they attempt to deal with the fact that not every system calls what is essentially the same data by the same name. There can be an infinite number of issues to be dealt with. These can include data integrity (what if the field has a NULL and we don't allow NULLS?) or differences in business rules (one system deals with credits by allowing a negative order quantity, another doesn't allow this and has a separate set of tables to deal with credits). The list can go on and on - so can the expense.
With the advent of DTS, a tremendous amount of the coding that had to be done for these situations can either be eliminated or, at least, simplified. DTS allows you to take data from any data source that has an OLE DB provider, and pump it into a SQL Server table.
While transferring our data, we can also apply what are referred to as transformations to that data. Transformations essentially alter the data according to some logical rule(s). The alteration can be as simple as changing a column name, or as complex as an analysis of the integrity of the data and application of rules to change it if necessary. To think about how this is applied, consider the example I gave earlier of taking data from a field that allows nulls and moving it to a table that does not allow nulls. With DTS, you can, during the transfer process, automatically change out any null values to some other value you choose (for a number, that might be zero, or, for a character, it might be something like "unknown").
We will be looking into DTS in some depth in Chapter 18.
MS DTC Administrative Console
What is the Distributed Transaction Coordinator (DTC) you ask? Well, the Reader's Digest version is that DTC makes sure that a group of statements that you're running either happen in their entirely, or are "rolled back" such that they appear to never have happened at all. SQL Server has it's own way of managing this kind of stuff, but DTC is special in that can deal with more than one data source even non-SQL Server data sources.
Let's say that you're running a bank. You want to transfer $100 from account "A" in your bank to account "B" in another bank. You wouldn't want the $100 dollars to be removed from the account in your bank unless it really is for sure going to be deposited in the other bank account - right? DTC is all about being sure that it happens this way.
The MS DTC Admin Console is actually more of a monitoring application than something that allows you to change much. Mostly, it just allows you to see what transactions are going through the DTC, and change the rules for logging transactions. (A transaction is a group of things that you want to apply this "all or nothing" rule to either all succeed or they all fail.)
I know I'm repeating myself here - but we will learn much more about transactions and DTC in Chapter 16.
Those of you who are familiar with NT's Performance Monitor will immediately recognize this tool. Much like the DTC, the performance monitor is really not about setting things - it allows you to understand how your current settings are affecting the system's performance. The Performance Monitor allows you to check out things like what percentage of reads are coming from cache as opposed to needing to be read off the hard disk. (Reads from the hard disk can be as much as hundreds of times slower than a read from cache.) In short, the SQL Server Performance Monitor is exactly the same as the NT Performance Monitor; save that it starts up by default with several SQLrelated measurements - for example, the percentage of reads coming from the data cache...
Table of Contents
Chapter 1: SQL Server 7.0 - Particulars and History
Chapter 2: RDBMS Basics: What Makes Up a SQL Server Database?
Chapter 3: Tools of the Trade
Chapter 4: The Foundation Statements of T-SQL
Chapter 5: Joining Tables
Chapter 6: Creating and Altering Tables
Chapter 7: Constraints
Chapter 8: Normalization and Other Basic Design Issues
Chapter 9: Speeding Performance: SQL Server Storage and Index Structures
Chapter 10: Views
Chapter 11: Writing Scripts and Batches
Chapter 12: Code Storage: Stored Procedures
Chapter 13: Transactions and Locks
Chapter 14: Triggers
Chapter 15: Asking a Better Question: Advanced Queries
Chapter 16: Spreading Things Out: Distributed Queries and Transactions
Chapter 17: SQL Cursors
Chapter 18: Making a Change... Bulk Copy Program and Data Transformation Services
Chapter 19: Replication
Chapter 20: Advanced Design
Chapter 21: OLAP
Chapter 22: English Query and Full-Text Search
Chapter 23: Security
Chapter 24: Performance Tuning
Chapter 25: Administration Overview
Appendix A: Global Variables
Appendix B: Function Listing
Appendix C: Tools for Our Time
Appendix D: Error Listing
Appendix E: Access Upsizing
Most Helpful Customer Reviews
I am a beginner in computer world. I found many books too complicated but not this one. It explains everything so clear and gives good examples. I enjoy in reading this book.
I purchased this book about five months ago. I had no knowledge of SQL Server at the time. With the help of the book now I can easily do some basic programming and run queries in SQL. It brakes down every detail that you must know about Microsoft SQL.
I am a Sybase DBA, and recently took a job as a SQL server 7.0 DBA. I purchased a few books to peruse, and this one rose to the top. The author speaks to you like you're a real person who wants to take the info in and understand it. He doesn't just spit technical jargon at you like some of the others. Highly recommended, whether a beginner or a veteran brushing up. I even understand some old concepts better, because he explained them in simple, understandable terms. Right on!
I'm not sure what book the other reviewers read, but it's not the one I got. This book has a terrible index (nothing's in there), and the examples are either too simple to help or too complex to understand at first attempt. I am a professional developer coming from the Oracle world, I never read an Oracle book that was this confusing. A simple problem like 'How do you execute a stored proc and get the output parameters displayed from SQL Server Query Analyzer' I never could find an answer to. This might be good reading material, but it is NOT a reference manual. Real world development does not provide time to read chapter by chapter. This book forces you to do that if you want to find what you need. Sorry, Mr Vieira, it's a lot of information, just not in an accessible format.
I really enjoyed the coverage of SQL 7 features. This book has an appropriate amount of depth for the topics it covers.
Hats off to Robert Vieira!!! This is perhaps the handiest SQL programming book you will ever own. He not only gets into a detailed analysis of SQL Programming, but he also makes it easy to read and understand. He has a lot of practical, real life methods of getting the most out of your scripts. I work as a SQL Support Engineer for an international firm that is responsible for several hundred MSSQL servers around the world, and I never let his book leave my side. During my conquest of Microsoft's MCDBA certification, I recognized the complex SQL Join questions that Robert contributed as a co-author of Microsoft's 70-029 SQL Design test. Do yourself a favor, buy the book!!!
I found this book to be a great investment. As a student, I needed this book for my Database Systems class because we were assigned projects to do in a database (either Access or SQL). I chose SQL and then bought this book to help me along. Although I have had some programming experience, I have never, ever before dealt with SQL Server. This book helped me all the way through with it's illustrated examples and different source code. The author writes in such easy-to-follow manner that I found it impossible to get lost. I highly recommend this book to anyone who wants to learn SQL server from beginning to end.