Debugging Stored Procedures

I have performed many tasks in numerous different roles, and one of the most frustrating has been debugging stored procedures. But no more! The ability to debug stored procedures as though we were debugging any development platform code is part of one of the enhancements to SQL Server 2000's Query Analyzer. We can insert break points, step into, step over, and so forth. This is wonderful for those of us who have tried to monitor what is happening in a stored procedure.

Previously we could do this with SQL Server 7.0 and Visual InterDev, but there was a lot of overhead in setting it up. With the new debugging tools, all we do is right- click and select Debug. How simple is that?

We have the standard debugging windows as well. We can get the values of variables from the Watch window and view the procedures that have been called and not completed in the Callstack window. This makes it easy to migrate from a development environment to using SQL Server 2000. So you Access developers out there must be getting really excited by now!

What? No More Room?

One of the trouble spots a DBA must keep an eye on is conserving a computer's most precious resources, memory and disk space.

If we have several databases on one server, we can find that we run out of disk space, and if that happens, our databases will fail.

Of course, in Spy Net's fictional scenario, that could mean World War III! But in the real world, running out of space can still cause serious problems, especially in mission-critical databases such as utilities or emergency response systems. In this section, we look at the causes of resource failure and several ways to avoid down time, including managing file and log size.

How Memory Affects Database Transactions

The memory-deprived databases will fail because tempdb is where most of the changes that you make to your data are performed before they are committed to disk. If you have enough RAM available, SQL Server 2000 will put as much of your database as it can up into RAM. After all, it is much faster to read from RAM than to scan a disk for the information. However, if RANI is a short commodity or you have concerns about the amount of disk space your database is eating, relax, because we have even have control over that.

When you are in Enterprise Manager you have the option to view how much space your data files for your database are allocated and how much is used. To see this information, simply click the SQLSpyNet (or any other) database within Enterprise Manager, and you will see a screen.

Shrinking Your Data Files to Reduce the Database

When we are talking about shrinking our data files we are not actually referring to the process of compacting them like a zip program would.

If we shrink our data files, we remove unused data pages. For example, if we had a table that had five data pages on which it stored the data, and we deleted two pages worth of the data, although our table would have only three pages that actually stored data, SQL Server 2000 still would have five pages allocated to the table.

When we shrink the data files, we just get rid of the extra two pages that the table was using. This does, however, have restrictions, but I think you get the idea.

What do we do when our data files are too large? Although we cannot shrink an entire database smaller than its original size, we can shrink our data files smaller than their original allocation sizes. We must do this by shrinking each data file individually by using the DBCC SHR I NKF I LE Transact-SQL statement. This allows us to reallocate how much space the given data file is allowed to use.

Sub Category and Product Detail Pages of Six Functional Parts

Online stores use the following six basic component webpages in their store design:

• Home page
• Category pages
• Sub-category pages
• Product detail pages
• Informational content pages
• Checkout page

Sub-category Pages—"The Shelf"

Because most online stores carry hundreds or thousands of products, it is important to break up the information into manageable chunks. Products are displayed on a shelf—whether it is physical shelving in a brick-and-mortar store, a catalog page, a TV infomercial, or an e-commerce website product page. The product shelf is any web page on which a product is merchandised and sold. It can be a category page featuring many products, or it can be a specific product page with a single product.

In this example, there are many varieties of all-in-one printers. The subcategory page helps to narrow down the offering. A thorough understanding of customer needs is necessary to structure these pages. Depending on product complexities, informational content can provide customers with "why to buy" information that helps them select the right product to meet their needs. Emerging online tools—such as the shopping wizard—can help narrow choices.

Product Detail Pages—"The Package or SKU"

Because customers can't pick up virtual merchandise and read labels, the product detail page represents the product or behaves like the product package. It must clearly communicate what the product is and what the customer will receive as a result of its purchase. Anytime a page offers a product for sale, it must provide the following information for each of the products offered:

• What it is—the description, picture, uses
• Relevant and complete compatibility, sizing, color, or other information
• What's contained in the package—what the customer will receive
• Other items needed for immediate operation (batteries, cables, assembly, UL specifications)
• Spare or complementary items (extra batteries, film, or a carrying case)
• "Care and feeding" of the product (special polish, cleaning instructions)
• The price and any hidden charges (extra shipping or handling)
• The manufacturer's or designer's name
• Sample content (for example, sample book pages)

The customer must know clearly what the product is and what it looks like. And for customers who know exactly what they are looking for, accurate descriptions and specific product numbers or models must be included so they may easily recognize the correct product. Our research across more than 25 major elcommerce websites identified incomplete or inadequate information. In many cases, the web stores did not provide complete compatibility information.

Shoppers will not purchase from a site that cannot confirm their choice or be specific about what the product is that they are purchasing.

Product detail pages let customers know what they're getting and what else they may need or want. In this example, product features—the "speeds and feeds"—are listed. This includes products they may need to purchase in order to use the product. It also features other products the customer may want. Good clothing stores recommend coordinated accessories to give customers ideas to complete ensembles for a variety of social occasions.

The product detail page also needs to let people know how to buy the product. As simple as that sounds, customers who participated in website evaluation research had difficulty adding a product to the shopping cart. This function varies from website to website. Button labels can also vary. Some say "add to cart," "buy," or "add." The buttons also have different locations on these sites. Some are next to the products and some are far enough away to disassociate them from the product.

Also, some websites force customers to go to the shopping cart or checkout page every time they add an item to the shopping cart. This then forces them to start over or go back and forth if they are shopping for multiple items. It adds an unnecessary extra step.

Make it easy for the customer to know what they're getting and how to get it. Customers will leave tedious websites.