Advanced Transact-SQL for SQL Server 2000 by Itzik Ben-Gan

By Itzik Ben-Gan

This e-book explores the robust functions of T-SQL and allows you to make the most them to the max. The e-book tackles complex parts in T-SQL similar to joins, subqueries, shops tactics, triggers, user-defined services, listed perspectives, cascading activities, federated perspectives, hierarchial buildings and extra. The publication indicates strategies to universal difficulties either in SQL server 2000 and in prior types. particular emphasis is put on practicular difficulties within the and on writing effective and good acting code. The booklet doesn't cease at ANSI-92 SQL but in addition represents positive aspects which are specific to SQL Server, specifically SQL Server 2000. that will help you on your trip, the publication offers many puzzles (and watch out, those aren't easy ones) and proposed solutions.About the authors:Itzik Ben-Gan is a senior SQL Server teacher and advisor with ten years of expertise in networking, improvement, databases and knowledge warehousing. he's a well-liked columnist and contributing editor for SQL Server journal, and is a Microsoft SQL Server MVP.Dr. Tom Moreau is President of Brockman Moreau Consulting, Inc. with greater than 16 years adventure in details know-how. Moreau has been utilizing SQL Server given that 1993. He writes usually for Pinnacle's Microsoft SQL Server specialist publication.

Show description

Read or Download Advanced Transact-SQL for SQL Server 2000 PDF

Best sql books

SQL For Dummies

See how SQL interfaces with today's environments

commence construction and utilizing relational databases with SQL's latest good points

The database could be the twenty-first century submitting cupboard, yet development one is a bit more advanced than sliding drawers right into a steel field. With this ebook to steer you thru all of the most modern beneficial properties of SQL, you'll quickly be whipping up relational databases, utilizing SQL with XML to strength data-driven sites, and more!

realize how you can
* Use SQL in a client/server procedure
* construct a multitable relational database
* build nested and recursive queries
* organize database defense
* Use SQL inside purposes
* Map SQL to XML

What's New in SQL Server 2012

Microsoft SQL Server has been a part of the firm database panorama on the grounds that SQL Server 7 arrived in 1998 and has advanced into the relational and BI platform of selection by way of companies world wide. The functionality and entire function set of SQL Server has been well known via the enterprise neighborhood and it really is considered as a robust weapon of their database and enterprise intelligence arsenal.

Additional info for Advanced Transact-SQL for SQL Server 2000

Sample text

You want a list of customer contacts for customers who have ordered the product. You do not want repeated names if they ordered the product several times. The solution is presented in Listing 2−11. CustomerID ) Note In some other systems, it is recommended that you use a constant or an indexed column, instead of* inside the inner SELECT. In SQL Server, the optimizer is smart enough to create the correct plan regardless of what's in the SELECT list. When you use an EXISTS predicate, SQL Server has to do less work than if you used a COUNT(*) > 0 predicate.

The mindset is the same. In this example, you want to find orders in which the quantity ordered for a particular product exceeded by three times the average order for that product. Your first impulse might be to create a temporary table of average orders for each product and then join onto the Order Details table for the final result. However, this query can actually be done as a single SELECT with the subquery being in the HAVING predicate. Check out the solution in Listing 2−10. ProductID ) You can also use a derived table to solve this type of problem, as you will see later in this chapter.

Table 1−26: Output of Matching Couples Using a Cross Join; Couples with Different Names candname Trevor Terresa Mary Neil Terresa Mary Neil Trevor Mary Neil Trevor Terresa candname Neil Neil Neil Trevor Trevor Trevor Terresa Terresa Terresa Mary Mary Mary This did narrow the list, but you still have problems. Assuming that your task for this dating service is to match males with females, you need to eliminate couples with the same gender. You don't need an additional condition, because checking for unequal genders will also take care of the previous problem, so you write the query shown in Listing 1−33.

Download PDF sample

Rated 4.10 of 5 – based on 28 votes