Only three of Nasdaq Stock Market's 30 developers have expertise in using the native programming language for Microsoft's SQL Server database. So for two major upcoming IT projects, the New York-based exchange hopes to write database code using general-purpose programming languages that all of the developers know.
That approach wasn't possible until recently for users such as Nasdaq. Two of the most heavily promoted new features in SQL Server 2005 are the deeply integrated Visual Studio development tool set and the .Net Common Language Runtime (CLR) execution environment. Microsoft executives have cited the work required to integrate those technologies as the main reason for the more than two-year delay of the database upgrade, which the company finally released last month.
Ken Richmond, Nasdaq's vice president of software engineering, expects that his staff will be able to write all of the stored-procedures code for the upcoming projects in CLR-supported languages such as C# or C++ instead of Transact-SQL, Microsoft's extension of SQL. "It's one of the things that I find very, very attractive" in SQL Server 2005, Richmond said.
He isn't alone. More than half of 35 IT managers who responded to a random survey conducted by Computerworld also said they expect the integration of Visual Studio and the CLR with SQL Server to be helpful. Some even predicted that it will change their philosophy about database programming and lead them to put more logic directly into their database servers.
"It standardizes programming languages and gets us away from the sometimes archaic SQL language," said Christopher Siegle, a Pittsburgh-based systems analyst and project lead at
Kirkpatrick & Lockhart Nicholson Graham, a law firm with offices in the U.S. and the U.K. The integration will also help strengthen communication between database administrators and developers, he added.
Timothy O'Rourke, vice president for computer and information services at Temple University in Philadelphia, said his staff can "start building reusable code with .Net to execute logic from SQL Server." That is "much more flexible and powerful" than putting business logic at the application server tier, he said.
But any IT shop that hopes to make heavy use of the new development options will need to do a careful analysis, because using .Net languages likely won't be the best approach for all of their database development work, according to several programming experts, analysts and users.
There will be plenty of instances where code written in T-SQL "will beat the pants off .Net CLR code," said Phil Hummel, a solution architect who works at Microsoft's technology center in Mountain View, Calif. "I don't want to see people waste time heading down the wrong track of trying to do everything with .Net procedures just because we have new features that will help people in some selected situations."
Users such as Keith Glennan, chief technology officer at Los Angeles-based Northrop Grumman, are already anticipating the need for additional training to make sure their IT staffs can achieve optimal use of the CLR. Glennan said the new options will present a challenge to developers who are trying to determine when it's appropriate to embed code in a database.
On one hand, Glennan said, the new capabilities should make it simpler and more efficient for developers to encapsulate business rules within a database. But on the other hand, application logic embedded in the database will have to be controlled the same way that it was when it was in the application layer, he said. "The trade-off is between optimized performance and transparency of your object model," Glennan said. "It requires a lot of discipline in how and where you define objects and services. There's never a completely black and white answer to how you ought to do this."
The balance could get particularly delicate for companies such as Northrop Grumman that are moving to service-oriented architectures. The SOA approach encourages the separation of application-specific business logic into a different tier from databases. But the new capabilities in SQL Server 2005 may entice some IT shops to consider shifting business logic to the database, said Forrester Research analyst Carl Zetie.
Zetie said he worries about the prospect of users "backsliding into entangled code" after having spent a considerable amount of time layering their systems.
Andrew Brust, co-author of the upcoming book Programming Microsoft SQL Server 2005, said the CLR will be best reserved for two database programming scenarios: creating aggregate functions, in which numeric data from a series of separate data records is calculated or collected; and defining custom data types where data values might have different interpretations or expressions, such as a calendar-year quarter vs. a fiscal-year quarter.
But those aren't typical bread-and-butter data manipulation and retrieval uses, added Brust, who is chief of new technology at Citigate Hudson, a New York-based Microsoft business partner that specializes in development of business intelligence applications. T-SQL will remain the better choice in those scenarios because it's optimized for testing, retrieving and changing large sets of data, Brust said.
Microsoft said that in SQL Server 2005, it has improved T-SQL's exception-handling capabilities and added a set of relational operators and query-language extensions. Those are designed to better align it with the SQL:2003 standard and to enable users to navigate hierarchical relationships in a table, handle large data values and pivot data.
"T-SQL is a wonderful language," said Paul Flessner, senior vice president of server applications at Microsoft. "We're not going to orphan it in any way. As long as there's a SQL Server, there's going to be T-SQL."
Some users have no plans to shift away from T-SQL. Mediterranean Shipping worked with SQL Server 2005 for months as a participant in Microsoft's Technology Adoption Program and went live with the database earlier this year. But at the time of the product's official launch, the company still hadn't bothered to enable the CLR, which is turned off by default in the new database.
Mediterranean Shipping has more than 7 million lines of business logic written in T-SQL and employs 25 programmers who specialize in that language, said Fabio Catassi, chief technical officer at the Geneva-based operator of container ships.
Catassi said he thinks the CLR is best used for managed code on the application server and on the client - not in the database layer. He said Mediterranean Shipping will stay with T-SQL for its databases, with the possible exception of smaller projects. "If you have a large amount of data, T-SQL is still the fastest way of manipulating [it]," Catassi said.
Developers for years have had the ability to write stored procedures in Java for Oracle and IBM databases, but most haven't made use of that option, according to analysts and officials at the vendors.
Mark Townsend, senior director of product development at Oracle, estimated that no more than 30 percent of the developers who write code for his company's databases use Java to build stored procedures.
And most of the IT managers who responded to the informal Computerworld survey indicated that the Java capabilities have done little to change the way their shops program to Oracle and IBM databases.
"There's a perception -- accurate or not -- that a [Java virtual machine] operating in the database slows the database down and makes it more resource-hungry," said James Brockman, a developer at the Missouri Department of Insurance. "DBAs in particular are unpersuaded by arguments from developers that using Java everywhere would help their productivity or mean one less learning curve to climb. They don't understand Java, so developers are not allowed to use it."
Microsoft users may be more likely to want to take advantage of the company's new programming options, said Gartner analyst Mark Driver. He noted that it isn't unusual in the Microsoft world to find a developer working on the user interface as well as the business and database logic, whereas in Oracle environments, database developers tend to focus only on database code.
Yet Driver doesn't expect the ability to write stored procedures in languages like Visual Basic to be the driving factor that gets IT managers to upgrade to SQL Server 2005. "Most of the energy and excitement around SQL Server is around scalability and the database engine," he said.