Transact-SQL is a procedural language used on both Microsoft SQL Server and Sybase SQL Server systems. It is a full-featured programming language that dramatically extends the power of SQL (Structured Query Language).The language provides programmers with a broad range of features, including:
- A rich set of datatypes, including specialized types for identifiers, timestamps, images, and long text fields
- Local and global variables
- Fully programmable server objects like views, triggers, stored procedures, and batch command files
- Conditional processing
- Exception and error handling
- Full transaction control
- System stored procedures that reduce the complexity of many operations, like adding users or automatically generating HTML Web pages
|Publisher:||O'Reilly Media, Incorporated|
|Product dimensions:||7.00(w) x 9.10(h) x 1.60(d)|
About the Author
Kevin Kline is the Technical Strategy Manager for SQL Server Solutions at Quest Software, a leading provider of award winning tools for database management and application monitoring tools. Kevin is also a founding board member and former President of the international Professional Association for SQL Server (PASS) and frequently contributes to database technology magazines, web sites, and discussion forums. Kevin's most popular book is SQL in a Nutshell published by O'Reilly Media. Kevin is also the author of monthly magazine columns for SQL Server Magazine and Database Trends & Applications. Kevin is a top rated speaker, appearing at international conferences like Microsoft TechEd, DevTeach, PASS, Microsoft IT Forum, and SQL Connections.
Lee Gould is a principal consultant with Sybase Professional Services in New York where she works mainly in the financial services industry on Wall Street. She has been working with SQL Server since 1992 and has been involved in the computer industry for over ten years. She has published numerous articles in Microsoft SQL Server Professional (Pinnacle Publishing) and presented on a variety of topics at the International Sybase User Group and Powersoft conferences. Lee was born in Liverpool, England. She grew up in Johannesburg, South Africa, where she attended the University of Witwatersrand and received her bachelors of commerce in business information systems. Lee immigrated to America four years ago and is currently pursuing an MBA at Seton Hall University in New Jersey. Lee lives in the suburb of North Brunswick, New Jersey, with her two cats, Lady and Paganini. She can often be seen running, cycling, swimming, and horseriding while she trains for marathons and ultra triathlon events. When not immersed in sport, Lee is an avid fan of classical music, good wine, the piano, science fiction and Terminator II. Her motto on life is "carpe diem," which she endeavors to follow regardless of where it leads her. Lee can be contacted at firstname.lastname@example.org.
Andrew Zanevsky, an independent consultant and founder and president of AZ Databases, Inc., has been working with SQL Server since 1992. He writes a monthly column "Super Administrator," for Microsoft SQL Server Professional (Pinnacle Publishing) and has published more than a hundred technical articles throughout his career. He immigrated to the United States from Minsk, Belarus in 1991. He started programming in 1982 and holds a degree from the Belarus State University of Informatics and Radioelectronics (equivalent to an M.S. in computer science). Andrew lives in a suburb of Chicago with his wife Katrin, son Anthony, and stepdaughter Esther. Katrin is also a SQL Server consultant. They work on some projects together and can talk about Transact-SQL at dinner. Andrew's daughter Nikkie lives in New York, wants to be a writer, and has won numerous awards in art contests. Andrew was the president of the Great Lakes SQL Server Users Group in Chicago from 1994 through 1997. He can be reached at email@example.com.
Read an Excerpt
Chapter 2: Database Analysis and DesignOverview of Design
Design is concerned with finding an efficient solution to an identified problem. During the design phase, you'll finalize decisions about the technology platform, the database product, and the chosen development tools. Since these decisions impose limits on the proposed system, the designer is faced with the challenge of finding an innovative and efficient solution. This solution must work against a backdrop of real constraints that have been described in the analysis phase deliverables. In many respects, design is about juggling a series of trade-offs into an optimal configuration. As with analysis, there are certain tasks we can perform to minimize the possibility of failure. Unfortunately, there is always an element of risk in any project, but if analysis has been thorough, and if competent designers are performing this phase of the life cycle, the risk is minimal.
These are the basic design tasks:
- Applying a design method
- Getting a sense of the amount of data and the user load the system will need to manage
- Understanding the technology that will be used
- Redesigning an existing system or developing a new design
- Applying a Design Method
It is equally important to select an appropriate method for design. Many organizations have a preference about what sort of design method is used, although in some cases, you may be lucky enough to be able to define a method of your choosing. Design methods are similar to coding standards; you need to have them but you need to temper their implementation with common sense. For a fuller discussion of coding standards, refer to Chapter 5, Format and Style.
Current design methods cover some of the following topics:
- Distributed processing versus centralized processing
- Mainframe versus client/server, intranet, or Internet-based application
- Object-oriented databases versus relational databases
- Structured systems design versus object-oriented design
- Unix versus Windows NT
Each of these methods is designed to encourage heated debates since each has its own merits. However, sometimes one approach is more appropriate than another for a particular environment. As the designer, your responsibility is to weigh the relative merits of each approach and decide which one is more appropriate for the particular system being designed. Each organization has a different set of operating conditions. Therefore, a design strategy that proves successful in one organization may not necessarily be successful in another. Always bear in mind the unique conditions of the organization when you evaluate different methods.
As a designer, you need to be aware of the major principles that underlie each of these individual approaches and determine which of the approaches (or combination) is more appropriate to your particular scenario. Your decision will invariably be swayed in a particular direction as a result of the information you gather from the other design tasks.
Getting a Sense of the Amount of Data
Data is the foundation of every system. For a system to have a purpose, it must take in inputs of some form or another and produce a series of outputs. The data in a system may be stored in a database or, heaven forbid, in a flat file. The amount of data the system is responsible for and the processing to be performed on this data determine the appropriate database to use. Even with a flexible budget, you can still have overkill, which may serve to inhibit performance for your particular environment.
Understanding your data volume is critical when it comes to designing a database and configuring it for optimal performance. Knowing this information before you begin the design process enables you to consider a more appropriate implementation strategy once you get to a physical design.
It is also critical to be aware of the volume of users who are going to be using this system. Very few systems are designed for single users. A database design may need to be modified if there are large numbers of users who consistently access the same data, since this may cause problems like blocking and user contention. Chapter 11, Transactions and Locking, discusses these issues in detail.
Understanding the Technology
Many organizations have preferred vendors who supply their hardware and software, and these vendors cannot be changed. During the design phase, you need to identify what technology infrastructure is already in place. Upper management usually chooses these vendors, which means that you will have to implement a permutation of a familiar theme that has been previously implemented.
Technology changes rapidly, and one of the biggest issues that usually has to be addressed here is making a purchasing decision for a solution that may not actually exist for a long period of time. This happens when the development phase may take years to complete. One of the approaches that has been adopted in many organizations is the use of a prototype solution. Typically, a scaled-down version of the final solution is implemented using only key components of the eventual system, In following this type of approach, it is possible to do a proof of concept that can (if successful) engender the support required to get the budget approval needed for this particular project.
It is highly unlikely that you will need to have everything from a technology infrastructure standpoint before you can begin the development phase. Often, it is possible to grow your technology requirements in tandem with your implementation schedule; a multiphased implementation schedule is not uncommon in systems development.
Analysis and design are critical steps in any systems life cycle. Numerous studies have proven that the cost of change increases exponentially as you step through each stage in the traditional life cycle. A change you identify and implement in the analysis phase could save you hundreds or even thousands of dollars over finding it out in the coding stage.
All too often, analysis and design are left by the wayside and regarded as costly and unnecessary. But in reality, a well-designed system is not an unreachable goal. There are many systems in every organization that are living examples of this.
Redesigning an Existing System or Developing a New Design
Designing a new system is a lot easier than retrofitting an existing system into a new design. Often, a system will already exist, and the new system that is being developed will be required to utilize components of the existing system (for better or for worse). As a system designer, during the design process you need to identify what has to remain and what can go. Based on these decisions, you can determine how you integrate these components without compromising your design.
Before you begin your design process, take an inventory of what components are in the current system and what components are going to need to stay. Prioritize these components as critical, probable, and not sure. After you have completed this inventory, you will have two basic paths you can follow based on this inventory:
- Design your new system as if the checklist didn't exist; then, once you have a perfect design, focus on how you incorporate these components into your new design.
- Design your new system, taking those components into consideration and designing your system around them....
Table of ContentsPART I: The Basics: Programming in Transact-SQL
1. Introduction to Transact-SQL
- 2. Matching Business Rules
- 3. SQL Primer
- 4. Transact-SQL Fundamentals
- 5. Format and Style
PART II: The Building Blocks: Transact-SQL Language Elements
6. Datatypes and Variables
- 7. Conditional Processing
- 8. Row Processing with Cursors
- 9. Error Handling
- 10. Temporary Objects
- 11. Transactions and Logging
PART III: Functions and Extensions
- 13. CASE Expressions and Transact-SQL Extensions
PART IV: Programming Transact-SQL Objects
14. Stored Procedures and Modular Design
- 15. Triggers
- 16. Views
- 17. System and Extended Stored Procedures and BCP
PART V: Performance Tuning and Optimization
18. Transact-SQL Code Design
- 19. Code Maintenance in the SQL Server
- 20. Transact-SQL Optimization and Tuning
- 21. Debugging Transact-SQL Programs
PART VI: Appendixes
A. System Tables
- B. What's New for Transact-SQL in Microsoft SQL Server 7.0?
- C. BCP
The book comes with a CD-ROM containing an extensive set of examples from the book and complete programs that illustrate the power of the language.
Most Helpful Customer Reviews
Unfortunately, it's not 1999 anymore. Most developers and analysts will get a lot out of this book. However, for DBAs or developers looking to get the most out of advanced features (which should be pretty much anybody using a for-pay SQL server in today's market), the breadth of knowledge just isn't there. But I know of no other book that covers the concepts IN this book BETTER than this book; I've cracked it at least once a week, every week for the past 12 years.
This is the best t-SQL book I've seen, and I've spent hundreds on the things. There are so many resources for PL/SQL developers, but none for t-SQL programmers--until now. Most important for me was the standards section. I plan to use this book as the basis of training for my department and all developers who need to write stored procedures.
This is the best book on Sybase's Transact-SQL. It is also very good at comparing MS SQL Server and Sybase -- especially from a Sybase perspective. If you're interested in MS SQL Server this book is probably far less interesting.
This book was obsolete when published -- targeting 4.2 is simply inexcusable. SQL Server 7.0 coverage is stuck away in an appendix that omits most of the important changes in the most recent version of the language. Don't waste your money on this book.
The problem with this book is that it doesn't cover SQL Server 7.0 in any depth despite claims to the contrary on its cover. It was obviously written for versions 4.2 and 6.0/6.5. Many of the example queries no longer work because of the many changes in SQL 7. I can't recommend this one for that reason alone -- it's simply too out-of-date.