Using Visual Studio 6.0 Database Tools with Microsoft SQL Server 7.0
Summary: Explains how to analyze data from different database management systems with Microsoft® SQL Server™ 7.0 and the Visual Studio® 6.0 Database Tools. (7 printed pages) With the linked server and TOP features in Query Designer you can:
- Access outside OLE DB data sources with the linked server feature.
- Limit the data you want returned by using the TOP clause.
- Improve database performance and efficiency with filegroups.
- Generate unique identifiers with the GUID data type.
Picture this: You're analyzing the top 50 revenue generators in your company. Your company, a multinational, has large subsidiaries on five continents plus the main office in North America. The subsidiaries specialize in different products, and all six offices, including yours, use different database management systems. For example, the Tokyo subsidiary keeps its data in a Microsoft Visual FoxPro® database, London uses Oracle, Buenos Aires stores its data on a Sybase system, the main office uses Microsoft SQL Server™, as does Johannesburg, and the Canberra office uses a third-party OLE DB system. How do you, in the main office, gather this data into a report?
Sounds like a tough job? Not if you use the new Microsoft SQL Server 7.0 with Microsoft Visual Studio 6.0 Database Tools! Use the linked server and TOP features in Query Designer, and off you go.
Of course, it takes a few more steps than that, but the point is that with Visual Studio 6.0 Database Tools, you can take advantage of several SQL Server 7.0 features to make your enterprise data tasks easier and more efficient.
First Things First
You can use the information in this article if you have either of these combinations of tools:
- Visual Studio 6.0 Database Tools and SQL Server 7.0
- Visual Studio 6.0 Database Tools and SQL Server 7.0 with Client Tools updated
To update Database Tools during SQL Server 7.0 installation:
- If SQL Server 7.0 is installed on a server and you have Visual Studio 6.0 Database Tools on your client computer, run SQL Server 7.0 setup on the client computer and select the Client Tools installation.
- If your computer is set up with both Visual Studio 6.0 Database Tools and SQL Server, then the SQL Server 7.0 Installation Wizard will update Database Tools automatically.
Access Outside OLE DB Data Sources
To access heterogeneous OLE DB data sources and deal with enterprise data work, a powerful, flexible solution is the linked server feature.
Linked server is a method of defining for SQL Server 7.0 the outside data object with the information needed to access that data. This definition is a four-part name that includes the outside data object's server name, the catalog or database that contains the object, the schema or owner of the object, and the object name (the table or view in the database).
The four-part name uses this syntax:
Having defined the linked server, you can use this four-part name in Transact-SQL statements to refer to data objects in the linked server. You can refer to tables and views in external data sources directly in SELECT, INSERT, UPDATE, and DELETE Transact-SQL statements.
Thus, a simple SELECT statement, as applied to the multinational company scenario, could read this way:
SELECT * FROM johann_sa.svs.dbo.products
- johann_sa is the name of the server in the Johannesburg subsidiary.
- svs is the database name.
- dbo is the owner of the object.
- products is the name of the table.
When the client application executes a distributed query, SQL Server breaks down the command and sends requests in rowset form—basically, sets of rows where each row contains columns of data—to OLE DB. Because OLE DB providers expose their data in rowsets, they know what to do when they receive the rowset requests from SQL Server.
In Database Tools, you use the linked server feature in the SQL Pane.
Using the multinational company scenario, to access the net revenue information from the products table in the services database of the Johannesburg subsidiary, your SELECT statement might look like this:
SELECT Prod.name, Prod.net FROM johann_sa.svs.dbo.products AS Prod WHERE date between '07/01/97' and '06/30/98' AND Prod.net > 1 ORDER BY Prod.net desc
Your result set will list the net revenues, valued at more than $1 million, of all products handled by the Johannesburg subsidiary for the fiscal year ending June 30, 1998. The products will be arranged from the highest net revenues to the lowest, in descending order.
Limiting Result Sets
Let's expand the scenario. What if, because the Johannesburg subsidiary covers the African continent plus the Middle East, its product offerings run in the hundreds and most of them are popular and profitable? How do you limit the list you get?
It's easy: use the TOP clause. The TOP clause is a tool for limiting the number of rows you get in what could possibly be a large result set. With the TOP keyword you specify that you want only the first n rows of a result set. Or, if you use the PERCENT keyword in conjunction with TOP, you can specify the top n percent of the result set.
Thus, you use TOP in your SELECT statement in one of two ways:
SELECT TOP n * SELECT TOP n PERCENT *
Let's say, going back to the Johannesburg query, you use the TOP clause to limit your list to 50 rows. The query would read:
SELECT TOP 50 Prod.name, Prod.net FROM johann_sa.svs.dbo.products AS Prod WHERE date between '07/01/97' and '06/30/98' AND Prod.net > 1
To use the TOP clause in Database Tools, go to the Query tab in the Properties window. If you want a certain number of rows, type the integer in the Top text box, and the appropriate query will be generated. If you want the TOP n percent, you should type n, and the word "percent" in the Top text box.
In the Johannesburg query, for example, all you need to do is type "50" in the Top text box of the Query tab in the Properties window.
Arranging the Rows in the Limited Result Set
If you specify ORDER BY in the SELECT statement that uses the TOP clause, SQL Server first builds the entire result set according to the specified order, then pulls out the TOP n rows or n percent from the built set.
Say the Johannesburg query reads:
SELECT TOP 50 Prod.name, Prod.net FROM johann_sa.svs.dbo.products AS Prod WHERE date between '07/01/97' and '06/30/98' AND Prod.net > 1 ORDER BY Prod.net desc
SQL Server first gets all the products with net revenues at more than $1 million and then arranges them in order from largest to smallest. Only after the list is arranged will the top 50 be pulled out and returned in the result set.
Making Database Operations Efficient
When you're querying large databases, performance is important. SQL Server 7.0 has two new features you can utilize in Database Tools to improve database operations. Filegroups can speed up database operations, and GUID data types allow for globally unique database objects that could be useful in enterprise—such as multinational—projects.
Improving Access to Databases
The filegroup is an administrative mechanism of database file placement that allows you to create a database across multiple disks.
When you create a SQL Server 7.0 database, SQL Server automatically assigns all files to the default filegroup. Unless you specify another filegroup as the default, the default is the Primary filegroup. (You can change the default filegroup for future files in the database later too.) However, even if you specify a different filegroup as the default, all of your database system files will remain in the Primary filegroup.
The filegroup property assigned to database files keeps track of these files even if they are located on different disks.
For example, you assigned a table to one filegroup, but saved the table's index files on a separate disk. When you access the table for data, your query will be spread across two disks so several searches on the various files of the table will be happening at the same time, thus speeding up return of the result set.
Filegroups also facilitate administrative work on databases. You do not need to perform backup operations on your database at one time. Instead, you can back up your database one filegroup at a time.
In Database Tools, you specify the Table and Text filegroup in the Tables Property Page. You specify the Index filegroup in the Indexes/Keys Property Page.
When you start planning the filegroup properties of your database, keep these points in mind:
- Filegroups cannot be created independently of database files.
- A filegroup cannot be used by more than one database.
- Only one filegroup can be the default filegroup for a database at any one time.
- A file can be a member of only one filegroup.
- Once a file is added to the database and assigned to a filegroup, you cannot move the file to another filegroup.
- System objects and files are always assigned to the Primary filegroup.
For more in-depth discussion of filegroups, consult your SQL Server 7.0 documentation.
Ensuring Uniqueness of Data Objects
When you are dealing with large databases used by multiple sites, as in this multinational scenario, you might encounter the tricky issue of ensuring the uniqueness of table objects. In such situations, UNIQUEIDENTIFIER may just be the answer. UNIQUEIDENTIFIER is a GUID data type that stores globally unique identifiers. A GUID is a binary number guaranteed to be unique, so no other computer will generate the same value.
The GUID's unique values are generated from the identification number of the computer's network card plus a unique number from the CPU clock. Network card manufacturers guarantee the uniqueness of each network card—at least, for the next 100 years.
UNIQUEIDENTIFIER values are not generated automatically the way the IDENTITY property does. To generate UNIQUEIDENTIFIER values for your table objects, you must specify the NEWID function as the default value for the column.
For example, if you want to create a table that lists net revenues of all your multinational company's subsidiaries' top products, and you want to specify a GUID data type, you can type:
CREATE TABLE NetRevenueTable (UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(), Characters VARCHAR(10))
In Database Tools, you do this in Database Diagram or when you're designing a table. Select Is RowGUID for the column you want uniquely identified. The default would be (newid()), which automatically generates the RowGUID.
Although there are many instances where you need to guarantee that a table object is unique, keep in mind the following characteristics of the UNIQUEIDENTIFIER data type if you choose to use it:
- Values are long and obscure.
- Values are random and follow no pattern that makes sense to the user.
- Values are difficult to use in applications that depend on serially incrementing values.
- Values, at 16 bytes, are large, so indexes built with these keys may be slower.
For more information on the GUID data type, consult the SQL Server 7.0 documentation.
Getting the Data for the Report
Altogether, the new SQL Server 7.0 features you can use with Database Tools will make enterprise database tasks, such as the multinational company scenario, more efficient and doable.
Using linked server and the TOP clause, you would be able to access external OLE DB data sources and limit a potentially long result list to the size you want and can use easily. In addition, such operations ultimately can be more efficient and easier to accomplish if filegroups and the GUID data type are implemented appropriately on the database files.
So, back to the multinational scenario, how do you get the result set that would be the basis of your revenue analysis?
Here's a SELECT statement you can run while using the database in your Chicago (main) office:
SELECT TOP 50 NetRevenueTable.name, NetRevenueTable.net FROM ( SELECT Prod.name, Prod.net FROM johann_sa.svs.dbo.products AS Prod WHERE date between '07/01/97' and '06/30/98' AND Prod.net > 1 UNION SELECT Prod.name, Prod.net FROM tokyo_jn.svs.dbo.products AS Prod WHERE date between '07/01/97' and '06/30/98' AND Prod.net > 1 UNION SELECT Prod.name, Prod.net FROM london_uk.svs.dbo.products AS Prod WHERE date between '07/01/97' and '06/30/98' AND Prod.net > 1 UNION SELECT Prod.name, Prod.net FROM buenosa_ag.svs.dbo.products AS Prod WHERE date between '07/01/97' and '06/30/98' AND Prod.net > 1 UNION SELECT Prod.name, Prod.net FROM canberra_au.svs.dbo.products AS Prod WHERE date between '07/01/97' and '06/30/98' AND Prod.net > 1 UNION /*The following SELECT statement is for information from the svs database in the Chicago Main Office*/ SELECT Prod.name, Prod.net FROM products AS Prod WHERE date between '07/01/97' and '06/30/98' AND Prod.net > 1 ) as NetRevenueTable ORDER BY NetRevenueTable.net desc
The results should give you a picture of your company's top revenue generators—a good starting point for your analysis. Good luck!