Microsoft SQL Server 2008 All-in-One For Dummies Cheat Sheet - dummies
Cheat Sheet

Microsoft SQL Server 2008 All-in-One For Dummies Cheat Sheet

From Microsoft SQL Server 2008 All-in-One Desk Reference For Dummies

By Robert D. Schneider, Darril Gibson

Microsoft SQL Server 2008 can help anyone in charge of database administration, developing database software, or looking for database solutions for a company. Peruse a list of six sources of information on SQL Server, and see what do to overcome a few common problems you might run into when using SQL Server.

6 Sources of Information on SQL Server 2008

Given the popularity of Microsoft SQL Server, it should come as no surprise that tons of helpful resources are available for SQL Server database designers, developers, and administrators. Here are a few of the best sources of insight for SQL Server 2008.

  • Microsoft SQL Server website: This is a great place to get started learning more about SQL Server. Aside from the usual marketing-flavored imagery, you find a variety of valuable product and technical details that you can use to further your understanding of all things SQL Server.

  • Microsoft SQL Server Developer Center: In an effort to support software developers, Microsoft offers a comprehensive set of services known as the Microsoft Developer Network (MSDN). This content-rich website offers a broad suite of software available for purchase to anyone, not just subscribers. It contains white papers, technical briefs, and a deep knowledge base that you can search to get answers to your questions.

  • Wikipedia: This Internet-based, open source encyclopedia is a great source of information about all technology topics, including relational database theory and practical application. For example, here’s a link to a very comprehensive article on database normalization in theory and practice.

  • Newsgroups: These collaborative spaces are an immense help when you’re struggling with a technical problem. Chances are that someone here can address your question. In the past few years, Google has done a great job helping to organize and rescue Usenet. It’s easier than ever to access these groups via your browser.

  • User Groups: These gatherings of like-minded individuals are a great place to enhance your understanding of SQL Server 2008. Some groups meet virtually, while others have physical events; some groups span both realms. Here are two of the better and most relevant, Internet-focused user groups:

    If you’d rather meet and greet your counterparts face-to-face, chances are that an Internet search can locate a good user group not too far from you.

  • Data generation tools: Generating sample data by hand is one of the more tedious tasks you face when building and testing an application for your SQL Server 2008 installation. Fortunately, tools are available that can automate this for you, freeing you to spend time developing and then tuning your application. To list one example, many programmers have had great success with the DTM Data Generator.

Troubleshooting SQL Server 2008

Given all the things you can do with SQL Server 2008, it’s natural that you might get confused from time to time. What follows are a few of the most common predicaments that you’re likely to encounter when using SQL Server, and how to overcome them.

Problems installing the SQL software

Having some cool new software and not being able to get it installed isn’t much fun. Luckily, SQL Server 2008 usually gets up and running without a hitch. If you do encounter an obstacle, use the following checklist to help you avoid hot water:

  1. Make sure you have sufficient permissions to add or remove software.

    Generally, a good idea is to install or remove software as an administrator. Otherwise, the operating system might block you from making these changes.

  2. Ascertain that your computer is powerful enough to support the product.

    SQL Server gobbles memory, CPU, and disk resources. Trying to install it on a lightweight machine guarantees frustration.

  3. Remove any previous versions (such as beta installations) of SQL Server 2008 via the Add/Remove Programs application within the Control Panel.

    If you skip this step, a good chance exists that the installer will complain loudly and then keel over. Even though it’s tedious, take the time to clean things up before trying to install.

  4. Make sure you have all necessary supporting software.

    Generally, the SQL Server installer is quite intelligent and diligently acquires whatever is missing (for example, Windows Installer, .NET Framework 2.0, SQL Server Native Client, and so on). However, you might need to obtain these components yourself.

Problems administering your database

A database administrator’s work is never done. Fortunately, Microsoft didn’t skimp on the supporting tools. Here are two good choices to get the job done quickly and easily:

  • SQLCMD utility: This character-based tool ships with every copy of SQL Server. You can run just about any administrative task using direct Transact-SQL or one of the hundreds of built-in system stored procedures.

  • SQL Server Management Studio: If you have more of a hankering for graphical tools when it comes to administration, you want to look at this utility. You can perform just about any administrative chore you might ever face. And, whatever isn’t possible can be handled with direct Transact-SQL or system stored procedures.

Problems with your data, itself

Unless you believe in gremlins that descend from the ether and wreak havoc on your data, chances are that any information problem is because of an error or omission. Here’s what to watch out for:

  • Referential integrity issues: To help keep all your data synchronized, SQL Server offers referential integrity features. These prevent you or your applications from inadvertently altering rows from one table without making corresponding changes in another table.

  • Failure to use transactions: Transactions help certify that your database interactions happen in logically consistent groups. Without proper transactions, an operation may update one table but fail to do the same for other tables. The result is damaged data integrity.

  • Incorrectly defined columns: Believe it or not, sometimes database designers choose the wrong data type when setting up their tables. For example, a particular field might need to contain currency amounts, which include decimals. Yet when designers write the SQL to create the table, they choose the INTEGER data type — SQL Server discards any fractional amounts from the column.

  • Not enough space for character-based fields: Another common problem is database designers not providing enough space for character-based fields. Again, SQL Server cheerfully tosses away any extra data, which leads to damaged information and unhappy users.

A database server that’s too slow

Before you toss your slow-running database server out the window, you can run a few effortless checks to identify and remedy the source of the headache.

  • Are your tables indexed correctly? Without a doubt, improper or missing indexes cause most of the performance problems that plague the average database application. Take the time to ensure that you’ve placed indexes in the right places.

  • Is there enough memory? Don’t shortchange your database server by denying it the memory it needs to get the job done quickly. You can tell if you’re running out of memory by launching the Windows Task Manager and viewing the amount of available physical memory. If this number is approaching zero, you’re asking your server to do too much work with too little memory.

  • Are there too many users and applications? Sometimes, no matter how much memory you install, or how well your tables are indexed, you approach the limit of what a database server can handle. There’s no hard-and-fast way to tell whether you’re on the brink, but if you’ve exhausted all your options and you can’t coax any more speed from your server, then distribute your workload among multiple servers.

  • Are you taking advantage of SQL Server’s performance tools? There’s no need to guess about what’s causing a responsiveness problem because SQL Server offers a collection of excellent performance monitoring and management tools.

Lost reports

After setting up an SQL Server Reporting Services (SSRS) server, it’s common to “lose” the URL you need to access the reports. The actual URL you use depends on whether SSRS is installed in the default instance of SQL Server or a named instance.

You can access both the report server (which lists all your reports) and the Report Manager (which allows you to manage many of your reports) via web browsers.

  • Finding SSRS on a default instance: To access the SSRS report server (which lists all your reports) on a server named SRV1, use the following URL:


    To access the Report Manager (which allows you to manage your reports) on a server named SRV1, use this URL:

  • Finding SSRS on a named instance: When accessing an SSRS that’s installed on a named instance, you need to add the instance name to the end of the URL preceded by an underscore.

    For example, if your named instance were MyReports on a server named SRV1, the URL for the report server would be:


    To access the Report Manager on a server named SRV1 with a named instance of MyReports, use this URL: