Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Questions, Answers, and Tips - 21 Sep 2000
Learn how to implement hierarchical relationships in SQL Server, how to optimize cursor performance, how and when to use the DBCC CONCURRENCYVIOLATION command, and more.
Brian Moran
September 20, 2000
5 Min Read
How can I create functionality in SQL Server similar to the Oracle keyword CONNECT BY, which lets me manage parent-child hierarchies, such as manager-employee relationships in an organizational chart?
Hierarchical relationships such as those in organizational charts or bills-of-lading can be difficult to implement in standard ANSI SQL, and Microsoft hasn't created a specific T-SQL function that mimics Oracle's CONNECT BY keyword. Until Microsoft supplies a specific function to handle such common relationships, you can check out two T-SQL solutions that might meet your needs.
SQL Server Books Online (BOL) provides stored procedure code that helps you expand a hierarchy to an arbitrary depth. You can find this solution under the topic heading "Expanding Hierarchies." You can also read Itzik Ben-Gan, "Maintaining Hierarchies," July 2000, which shows you how to add columns to your database tables to hold hierarchical data and how to use triggers and T-SQL queries to manage that data.
Also, consider using SQL Server 2000's Analysis Services 2000 to help you represent hierarchical relationships. Analysis Services (formerly known as OLAP Services) has new parent-child dimensions that let you accurately model hierarchies. In fact, the BOL example under the subheading "Parent-Child Dimensions" talks specifically about supervisor-employee relationships.
You can also build a cube by using Analysis Services' new realtime data-analysis feature, which uses SQL Server 2000's indexed views to give you quick access to aggregated answers without having to manually reprocess the OLAP cube. You can then create a linked server from SQL Server that points to the OLAP cube. Because the OLAP cube is pointing to data stored in SQL Server, you're really using the OLAP engine as a way to provide rich hierarchical relationship management. Finally, you can pass SQL or native MDX queries to the OLAP cube that your linked server manages; doing so exposes Analysis Services' rich hierarchical relationship management features to common SQL interfaces and tools. (For more information about Analysis Services' hierarchical relationship management capabilities, see Bob Pfeiff, "Building a Cube from a Dimensional Database," page 55, and Russ Whitney, Mastering OLAP, "Ragged Hierarchies," August 2000.)
Do you have any tips for optimizing API cursors that SQL Server client applications call through ODBC or OLE DB?
Proper API cursor use can mean all the difference in the world when it comes to application scalability. The Microsoft TechNet article "Top 10 SQL Server 7.0 Support Issues and Resolutions" (http://www.microsoft.com/technet/sql/topsup.asp) gives several cursor tips, including
Use default settings for singleton select statements (which return one row) or other small result sets. Caching a small result set on the client and scrolling through the cache is more efficient than asking the server to implement a cursor.
Use the default settings when fetching an entire result set to the client, such as when you're producing a report. Default result sets are the fastest way to transmit data to the client.
You have to use default result sets for any T-SQL statement or batch of T-SQL statements that generate multiple result sets.
In joins, static or keyset-driven cursors can be faster than dynamic cursors. You must use static or keyset-driven cursors when you want to do absolute fetches.
Static and keyset-driven cursors increase the usage of tempdb. Static server cursors build the entire cursor in tempdb; keyset-driven cursors build the keyset in tempdb.
In applications that use server cursors, each call to an API fetch function or method causes a round-trip to the server. Applications can minimize these round-trips by using block cursors with a reasonably large number of rows returned on each fetch. You can set the block size by using ADO's CacheSize property. I like to use batch sizes in the range of 100 to 500 rows.
Consider SQL Server 7.0's cursor performance optimization feature, called Fast_Forward Cursors. SQL Server Books Online (BOL) has a lot of information about Fast_Forward Cursors.
Many of these rules are common sense if you've been working with cursors for a while, but having them all in one place is handy.
Is is true that SQL Server 2000 can take advantage of Secure Sockets Layer (SSL)?
Yes, Network Libraries (NetLibs) in SQL Server 2000 all support SSL encryption. Thus, SQL Server 2000 can use SSL to encrypt all data transmitted between an application server and a SQL Server instance running on a database server. SQL Server performs the SSL encryption through the Super Socket NetLib (Dbnetlib.dll on the client, and Ssnetlib.dll on the server), and the encryption applies to all intercomputer protocols that SQL Server 2000 supports.
Before you can enable SSL encryption, however, your configuration must meet certain conditions. The database server must be running SQL Server 2000 and must receive a server certificate from a public certificate authority. In addition, your application must use the SQL Server 2000 client components, and the application server must receive a root Certificate Authority (CA) certificate from the same certificate authority that issued the database machine's server certificate. The application must also connect to an instance of SQL Server 2000 because SQL Server 7.0 and 6.5 don't support SSL or the Super Socket NetLib.
Do I have to run SQL Server 2000 on Windows 2000?
No. All versions of SQL Server 2000 run on Windows NT 4.0 Service Pack 5 (SP5) and later. In addition, you can install SQL Server 2000 Personal Edition and SQL Server 2000 Desktop Engine on Windows 9x OSs. (With SQL Server 7.0, you can run only the Standard version of the SQL Server engine on Windows 9x.)
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
