This chapter is from the book
This chapter is from the book
This chapter is from the book
SQL Server 2000
Compared to Visual Basic .NET, SQL Server 2000 is almost an old-timer. Still I think it's important to discuss some of its new features. Although you may think that the leap from SQL Server 7 to 2000 would be a big one, the real changes aren't all that dramatic. However, there are some interesting features for developers to consider, including XML support, user-defined functions, and distributed partitioned views, among others.
XML Support
I'm a geek, and so I love to use new technology. When XML came on the market a couple of years ago, I used it as the file format in a project for a customer of mine. (Hey, I know what you think, but my customer actually thought—and still thinks—it was a good solution!) However, I soon learned that XML—especially its tool support—was not the wonder technology I had first envisioned. The project worked out fine in the end, but I became reluctant to use XML for some time. Since then, the standards and tool support for XML have gone through tremendous development, and are sure to continue into the future.
I became re-interested in XML with SQL Server 2000. There is a lot of XML support in SQL Server 2000. The following are the most interesting features:
You can now pass XML documents to stored procedures and open them for further processing—That gives, for example, a very good solution for the problem of handing over several rows to a stored procedure in one call.
You can fetch results from SELECT statements as XML documents—In some situations, it will be a good solution to create the result in XML directly at the database server instead of converting it at the middle tier or at the client.
Perhaps the XML support is the reason that SQL Server 2000 now is called a .NET server. It's not written in managed code and nothing has changed since it only was called SQL Server 2000, but hey, it can talk XML! What can I say? Marketers...
User-Defined Functions
In SQL Server 2000, we finally had User-Defined Functions (UDFs). Since we have waited so long, Microsoft was kind enough to give us three different versions of UDFs:
Scalar Functions—These can be used for creating functions to be used the same way as columns in SELECT statements, similar to, for example, CONVERT().
Inline Table Valued Functions (ITVFs)—ITVFs are similar to views, but can take parameters. The result is created with one single SELECT statement.
Multistatement Table Valued Functions (MTVF)—In this case, the result can be created with multiple statements. MTVFs are, for example, a good solution in situations where you normally use temporary tables.
The UDF implementation in SQL Server 2000 is a typical first version, with several quirks. For example, it is not possible to use RAND() and GETDATE() in MTVFs. The same goes for touching temporary tables and calling stored procedures. Microsoft will probably fix some of the rough edges in an upcoming version.
Distributed Partitioned Views
With distributed partitioned views (DPVs), a table can be partitioned over several machines and it will be completely transparent to the clients. This is currently the technique Microsoft is using to achieve shared-nothing clustering with SQL Server.
NOTE
I will discuss DPVs more in Chapter 5, "Architecture."
Other Interesting New Features
The following are some other new features of SQL Server 2000, of high interest to developers:
Improved replication support—It is now possible to use queued updates with MSMQ. In addition, merge replication doesn't have to be reconfigured from scratch, for example, every time you need to change the structure of a table.
BIGINT and TABLE data types—The ordinary INT (32 bits) is sometimes too small a data type. The typical example is for primary keys. Two billion rows (assume only positive values) aren't enough in many cases. BIGINT (64 bits) will probably come in handy in these situations. The TABLE data type can be used instead of an ordinary temporary table to create a temporary table in memory instead of in the tempdb. By doing this, you can save overhead. This data type is also important for the UDFs. Unfortunately, the TABLE data type can't be used as a parameter between stored procedures. Another limitation is that a stored procedure can't be the source when adding rows to a variable of TABLE type.
Declarative cascading referential integrity—People have been waiting for this SQL92 feature a long time.
INSTEAD OF triggers—INSTEAD OF triggers execute instead of a specific UPDATE operation, for example (in other words, the original UPDATE will not execute). This is the only kind of trigger you can use on a view.
Multiple instances—It's possible to have several SQL Server instances on a single machine. This is great for testing an application on several versions of SQL Server, on a single box. It is also very useful in consolidation scenarios.
Extended properties—Extended properties make it possible for you to add your own metadata to the schema, such as adding documentation for each column in each table, storing edit masks, and so on.
Indexed views—An indexed view will have an index of its own and not only a stored query definition as with an ordinary view. This can greatly enhance performance for some queries.
New built-in functions—These include GETUTCDATE() and SCOPE_IDENTITY(), which will return the last IDENTITY value that is created in the current scope, such as the stored procedure. This way, the problem with @@IDENTITY, in which a trigger could have added a row to another table with an IDENTITY that changed the value of @@IDENTITY, is avoided. (Once again, if you don't use triggers, this is not a problem anyway.)
Save TEXT, IMAGE, and so on in row value—Quite often, the data for a TEXT or IMAGE column is quite small. In these cases, it would be beneficial from a performance point of view to store that data with the rest of the row, instead of in its own structure, as was always the case in previous versions of SQL Server.
Several productivity enhancements in Query Analyzer—These include support for templates, object browser, and built-in debugging.
NOTE
In my applications, declarative cascading referential integrity isn't very important. I prefer taking care of this explicitly in the stored procedures that are responsible for the deletions. I will discuss this more in Chapter 7, "Business Rules."
And, as is tried with every version, there have been a lot of improvements when it comes to performance and scalability. One example is that each connection is now consuming less memory than before.
Outstanding Problems with SQL Server 2000
Unfortunately, the T-SQL programming language still feels weak and old-fashioned. The following are some of the basic problems it still has:
You cannot directly define ordinary constants.
You can't use statements (such as @x + @y) as parameters.
You can't directly send arrays as parameters. (The XML support in SQL Server 2000 is one possible solution that partly solves the problem. I will discuss this further in Chapter 8, "Data Access.")
The error handling is very old-fashioned and has many quirks.
There are some hindrances for centralizing code.
Beyond SQL Server 2000
There hasn't been much talk about the next SQL server version yet, code named Yukon, but the following are the features that I've heard of so far:
The possibility to store XML documents in columns and being able to index the content
Better administrative support for DPV
The possibility to use the .NET languages to write the "stored procedures"
The possibility to let SQL Server store the file system
NOTE
Time will tell which or all of these features come to fruition.