SQL Server Magazine UPDATE, November 14, 2002

SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com

THIS ISSUE SPONSORED BY

Precise/Indepth for SQL Server

Head Back to School Online with SSMU!
(below COMMENTARY)

Did You Miss SQL Server Magazine's Web Seminars?
(below NEWS AND VIEWS)

SPONSOR: PRECISE/INDEPTH FOR SQL SERVER

Want tips and tricks on SQL Server performance from an industry leading expert? Read the free ebook "The Definitive Guide to SQL Server Performance Optimization".
http://lists.sqlmag.com/cgi-bin3/flo?y=eOUK0FgQMn0BRZ03HV0AE

November 14, 2002—In this issue:

1. COMMENTARY

2. SQL SERVER NEWS AND VIEWS

3. READER CHALLENGE

4. ANNOUNCEMENTS

5. HOT RELEASES (ADVERTISEMENTS)

6. RESOURCES

7. NEW AND IMPROVED

8. CONTACT US
See this section for a list of ways to contact us.

1. COMMENTARY

(contributed by Brian Moran, news editor, [email protected])

Attending events such as the SQL Server Magazine LIVE! Conference (October 27-30) always gives me a lot to think about, and the most recent LIVE! conference was no exception. During the course of the event, I experienced a particularly exciting "aha" moment when I began to better appreciate the long-term significance of stored procedures in the up-and-coming Web services world.

SQL Server currently lets you expose a stored procedure as a Web service. (For information about this functionality, see the resources I recommend in "Use the SQL Server 2000 Web Services Toolkit to Get Started with .NET".) I always thought this ability sounded like a good idea but was impractical. Calling a stored procedure through a Web service isn't fast enough for most real-world applications. But I've seen the light. The role that stored procedures exposed as Web services will have in future .NET-based architectures is related to "extreme performance," which I've discussed several times in past commentaries.

As I said in "Designing Databases for Extreme Performance", the need for extreme performance sometimes forces me to make a design decision that sacrifices some response time and performance in the short term to ensure needed scalability as the application grows. But what if I didn't have to sacrifice short-term response time for long-term scalability? What if I could cram as much processing onto the database tier as possible, then easily redeploy some processes to a mid-tier application server when my database began to run out of CPU steam? What if the trade-offs between scalability and response time became a runtime deployment issue rather than a fixed-in-stone design issue?

Although Yukon, the next release of SQL Server, won't be rolled out anytime soon, Microsoft says that XML integration in Yukon will be tighter than in SQL Server 2000. And the company has indicated that it will add features to solve many of the performance and integration problems currently associated with using stored procedures exposed as a Web service. In addition you'll be able to write stored procedures in any .NET Common Language Runtime (CLR)-compliant language. This ability will make it more practical to design and build certain business functions as stored procedures to gain performance benefits. You'll have the flexibility to redeploy those CLR-based stored procedures to a scalable Web farm if database server processing power begins to run short.

Thus, Yukon might make my current theory of extreme performance irrelevant by letting me dynamically make trade-offs between response time and scalability. The future of SQL Server is Yukon, and what an exciting world it's shaping up to be.

HEAD BACK TO SCHOOL ONLINE WITH SSMU!
Introducing SQL Server Magazine University — a virtual classroom environment where you receive quality technical training — on your schedule. Microsoft Certified Trainers help you acquire practical SQL Server skills and prepare for your certification exam at the same time. The 24x7 access to our Virtual Computer Lab allows the flexibility to learn new applications while keeping up with your day-to-day job duties. Our virtual computers are specifically configured for each training session — no need to set up the programs on your personal system, saving you valuable time. Move to the head of the class — enroll today!
http://lists.sqlmag.com/cgi-bin3/flo?y=eOUK0FgQMn0BRZ04GF0Aw

2. SQL SERVER NEWS AND VIEWS

Microsoft has released a new Web resource that can help SQL Server developers set up connectivity security for SQL Server 2000 Windows CE Edition 2.0 (SQL Server CE) and Microsoft Internet Information Services (IIS). The paper "Security Models and Scenarios for SQL Server 2000 Windows CE Edition 2.0" describes security requirements for single-server and multiserver environments. The paper includes example scenarios that you can use for development and testing.

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "When do you most often experience locking problems?" Here are the results (+/- 1 percent) from the 249 votes:

-  15% When running reports-  44% When accessing and modifying data through applications-  14% When running ad hoc queries through Query Analyzer-   3% Always-  24% Never

The next Instant Poll question is "Which of the following best describes your job?" Go to the SQL Server Magazine Web site and submit your vote for 1) IT management (e.g., database manager, departmental IT management), 2) IT staff (e.g., database administrator, application developer), 3) Consultant, 4) ISV/Reseller, or 5) Other.
http://www.sqlmag.com

SPONSOR: DID YOU MISS OUR WEB SEMINARS?
No worries! SQL Server Magazine's Web seminars are still accessible right at your desktop! Kalen Delaney discusses SQL Server internals; Brian Moran identifies performance problems; Rich Rollman teaches about XML for database professionals; Morris Lewis instructs on high availability. Valuable online desktop training at a fraction of the cost of traveling to further your education. Go to
http://lists.sqlmag.com/cgi-bin3/flo?y=eOUK0FgQMn0BRZ05yn0AT

3. READER CHALLENGE

(contributed by SQL Server MVP Umachandar Jayachandran, [email protected])

Congratulations to Charles Rummel, DBA and programmer at Chicago-based Morningstar, and Yuval Peleg, DBA at Eyron in Israel. Charles won first prize of $100 for the best solution to the November Reader Challenge, "Collation Conflict." Yuval won second prize of $50. You can find a recap of the problem and the solution to the November Reader Challenge at http://www.sqlmag.com/articles/index.cfm?articleID=27265.

Now, test your SQL Server savvy in the December Reader Challenge, "Stalking the Statements" (below). Submit your solution in an email message to [email protected] by November 20. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here's the challenge: Ryan is the DBA for several SQL Server 2000 installations. Lately he's noticed that queries and statements coming in from his company's Web servers to in-house SQL Server instances are executing more slowly. He wants to devise an automated method of flagging the worst-performing statements and queries without user intervention so that he can tune the queries. Help Ryan take the following actions:

4. ANNOUNCEMENTS

(brought to you by SQL Server Magazine and its partners)

The SQL Server Magazine Master CD gives you real-time, high-speed access to all the articles, code, and expertise from every issue of SQL Server Magazine ever published. Unique search features let you find what you need fast. Order your copy today by clicking here:
http://lists.sqlmag.com/cgi-bin3/flo?y=eOUK0FgQMn0BRZ06JZ0AN

It's quick and easy! The only thing you have to decide is whether you'd like it delivered to your desktop or to your doorstep. If you prefer to receive SQL Server Magazine digitally through your desktop, go to
http://lists.sqlmag.com/cgi-bin3/flo?y=eOUK0FgQMn0BRZ05mO0Ak

Or if you'd rather get the print version in your mailbox, click here.

5. HOT RELEASES (ADVERTISEMENTS)

DataFlux provides data quality technology that can be used anywhere in the organization by anyone at anytime on any data. Turn your data into accurate, consistent, and usable information by attending a free webinar.
http://lists.sqlmag.com/cgi-bin3/flo?y=eOUK0FgQMn0BRZ06Jb0AV

Get better performance for Microsoft SQL Server databases on a Unisys ES7000 server. The Unisys ES7000 database server couples Microsoft/Intel economics with a robust and scalable server architecture. Delivering more headroom for growth, faster response times and superior availability.
http://lists.sqlmag.com/cgi-bin3/flo?y=eOUK0FgQMn0BRZ045G0Af

6. RESOURCES

Web Matrix is a graphical application-development tool for the ASP.NET Web application framework. It's a great way to get your feet wet using ASP.NET without shelling out the money for a full copy of Visual Studio .NET. Michael Otey covers the tool's top features in "The Best of Web Matrix," which appears in the November 2002 issue of SQL Server Magazine. The article is available online at
http://www.sqlmag.com/articles/index.cfm?articleid=26548

Garstor has written a stored procedure that inserts the results of a dynamically filtered view into a table that's linked to Microsoft Access. He wants to delete previous results from the table and has tried using the DELETE FROM command. He'd rather use TRUNCATE, but he can't transfer permissions for TRUNCATE to other users. Can Garstor change the current user in his stored procedure code so that he can use TRUNCATE TABLE? Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
http://www.sqlmag.com/forums/rd.cfm?cid=9&tid=10300

(contributed by Microsoft's SQL Server Development Team, [email protected])

Q. What's the difference between using the "text in row" storage feature and using the varchar data type, and which will buy me better response time when I'm storing and retrieving arbitrarily long character strings?

A. If you're running SQL Server releases earlier than SQL Server 2000, you have two choices for storing and retrieving arbitrarily long character strings. If you choose the varchar data type, you might have to break the data across several rows to avoid hitting the 8000-character limit in SQL Server 7.0 or the 256-character limit in SQL Server 6.5. Alternatively, you can choose the text data type, which stores an in-row pointer to separate text pages in which the data is stored. This storage method requires two I/Os for a retrieval: one I/O for the pointer and at least one other I/O for the first text page. A double I/O slows down the scans that filter on the value of the text column.

SQL Server 2000 introduced the "text in row" concept, which helps you avoid the text data type's double I/O (and the potential seek/rotate latency) by letting you specify the part of the text data that you want to store in the data row. Using this feature is a good idea when you're storing small text files that fit on one page or when you're looking at the head page of a text file to help you decide whether to retrieve the whole file. Note, however, that using "text in row" reduces the rows-per-page density, and density reduction increases the number of I/Os necessary for SQL Server to scan the data. Therefore, if you're scanning the table on another column, you might consider using an index.

You should also be aware that text columns have limitations that varchar columns don't have. For example, you can't index them, and you can't use them with some cursor types. These limitations might require you to use special functions such as Updatetext, WriteText, and ADO's stream interfaces. We would use "text in row" only if data in some of the rows needed more space than varchar or nvarchar allows.

Send your technical questions to [email protected].

7. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

TechTrain announced a training program called PowerSQL, which provides 5 days of instructor-led training and 12 hours of structured e-learning training. Because of pretraining done at the student's own pace, the training program can cover topics in MS-2072 (Administering a Microsoft SQL Server 2000 Database) and MS-2073 (Programming a Microsoft SQL Server 2000 Database) in only 5 days of classroom time. The PowerSQL training program costs $2995 and includes the Microsoft Official Curriculum (MOC) training kits for MS-2072 and MS-2073. Contact TechTrain at 704-357-8010, 800-216-4566, or [email protected].
http://www.techtrain.com

dbNetLink announced DbNetGrid, an HTML grid component for Internet and intranet development. You can use the solution as a grid component integrated with your applications, as a Web reporting tool, or as a Web application. You can link grids together to create drilldown Web reports. SQL Server DBAs can use the tool to distribute interactive database information to their user base through a browser if they don't have the time or inclination to write lots of Active Server Pages (ASP) and HTML code themselves. You can license DbNetGrid on a per-processor basis rather than a per-client basis. A license must cover the total number of processors in the servers on which you install DbNetGrid. Pricing starts at $499 for a single-processor license. Contact dbNetLink at [email protected].
http://www.dbnetgrid.com

8. CONTACT US

Here's how to reach us with your comments and questions:

SQL Server Magazine UPDATE is brought to you by SQL ServerMagazine, the only magazine completely devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
http://www.sqlmag.com/sub.cfm?code=ssei211x1y

Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.
http://www.winnetmag.net/email