SQL Server pays off
Here's a data nightmare. You're on deadline and you need to do an analysis of on-the-job deaths. You've got OSHA's workplace safety data - so that's good - but the data's kept on four CD's. It takes you an hour just to copy the data to your hard drive. Then you discover that if you combine all the data into a single two-gigabyte table, Access chokes. You have no choice but to break it up. What you thought would be a simple question - how many deaths has OSHA investigated in the past 25 years? - takes you all day to answer. And that's just the start. There's a simple way to avoid situations like this: Use SQL Server.
SQL Server is high-powered database software from Microsoft that, as the name suggests, works on a server over a network. You can use the software without a server, but you lose many of the benefits that way. While I don't have personal experience with this, I assume that you could get the same benefits from other server-client software such as Oracle or Informix.
SQL Server has gained a reputation in newsrooms as a great tool for putting databases on an Intranet. In fact, it's also great for the kind of slicing and dicing of data that CAR folks do all the time. I have hardly touched Access or FoxPro in the last three years. There are many reasons for this, the chief one being that I haven't needed to.
Here's why:
SQL Server can handle a terabyte of data, so I've never come close to seeing it choke. Access cannot handle anything exceeding a gigabyte, which is not a lot these days and can be quite frustrating.
Your data is always warm. By keeping it on a server, you no longer have to waste time copying from a CD or restoring from a backup tape. As I was writing this, a reporter asked me to search for a name in several different databases. I was able to do it in less than five minutes because the data was ready to go.
Because the server is doing most of the work, my computer isn't tied up when I'm doing queries. In fact, I can run several queries at once and still be free to do other things on my computer while I'm waiting for the results.
SQL Server doesn't have as many point-and-click features for doing queries, which means you have to master SQL. But one advantage is that you can easily connect to a SQL Server database with Access or FoxPro and use their mouse-friendly querying tools. You can also easily import data directly into SPSS through what's called an ODBC connection. They are easy to create.
That means that you can set up any reporter on the network to do queries directly from SQL Server in Access. And you can do analysis from any PC on the network. You don't have to worry about copying data from one machine to another.
If your server is on the Internet, you can do queries from any computer on the Internet. You could go home, log onto America Online, open up Access and, with an ODBC connection, do queries on data kept in the office. The data is password protected, so it's secure.
Intranets
On top of all that, SQL Server is in fact a great tool for putting databases on the Intranet. Here's my routine:
When I get any data, I automatically "upsize" it to SQL Server, which is no more difficult than importing the same data into Access. As always, I index the fields I'm going to be querying. Then I'm ready to go.
If the data have people's names in it or may be of interest to others, I create a view and put it on our Intranet. The view simply creates a facade that converts field names and codes into plain English. To create search pages on the Web, I've used Uniquery and Cold Fusion. Both work great because they require no programming. Who has time to become a Web developer?
(This could be the subject of a separate article, but the big advantage of Uniquery is that it allows you to search all databases at once or individually. The big advantage of Cold Fusion is that you can easily customize the search page.)
If you are especially ambitious, a la Tom Torok at The New York Times, you can use SQL Server 7.0's advanced features, such as English query. This allows users to ask questions in plain English, such as "How many firefighters were paid overtime last year?" SQL Server converts that to an SQL statement, such as "select count(occupation) from payroll where overtime > 0 and year = '1999' and occupation = 'Firefighter'".
SQL Server 7.0 also allows for full-text indexing, which means that you can search for a word or words in a field of text. That's much faster than using a simple LIKE query.
What you need
SQL Server is insatiable when it comes to memory and space. You also need to learn how to create an ODBC connection. With your mouse, click Start Settings Control Panel ODBC Data Sources Add SQL Server. Then type in a name, type in the name of the server, click on Next, choose either NT authentication or a password, click on Next, change the default database, click on Next and then on Finish.
What do you need to make SQL Server work? You can install it on your personal computer. But to get all the benefits I've mentioned, you really need a nice Windows NT server - one with at least 500 megabytes of memory and 96 gigabytes of disk space.
The biggest downside to using SQL Server is that to get full benefit from it, you really need to master SQL. The good news is that there's not a steep learning curve. Once you've gotten SELECT, UPDATE, CREATE TABLE, and INSERT down, you will be able to do almost any query with it. Also, you can use the SQL you learn in Access or FoxPro. The challenge of learning SQL Server pays off, because you can use it for any database need you have. You can slice and dice. You can query data that's ready to go. You can connect data savvy reporters with Access. And you can make the data available to everyone via the Intranet. The software is specifically designed to work efficiently with multiple users. So why bother with anything else?
David Heath can be reached by e-mail at dheath@seattletimes.com