Image of Navigational Panel mapped to Home / Contents / Search Cool Things in SQL Server 6.0

by Lisa Hooper - GUI Computing

Image of Line Break

Microsoft SQL Server 6.0 is said to extend the performance, reliability and scalability of earlier versions.

The enhancements seen in the move to version 6.0 are certainly the most extensive made to the product so far. These include:

With this amazing array of new and improved features there is no doubt that DBAs and developers alike will be jumping for joy at the thought of getting their hands on this baby. But what I really want to know, as with any new version of a product, is how will this product make life easier for me as a developer?

Auto Sequencing

We all know how useful it can be to use an automatic sequencing column for a primary key. MSAccess gave us the counter data type, Oracle gave us the sequence object, and SQL Server pre 6.0 gave us nothing. Yes, we could manually implement a sequence by creating triggers:

  CREATE TRIGGER SequenceTrigger ON Atable FOR INSERT ASDECLARE @maxc int, @newc int  
  SELECT @maxc = (SELECT Max(SequencedField) FROM TableToSequence)
  SELECT @newc = (SELECT SequencedField FROM inserted) 
  IF @newc = 0 OR @maxc <> @newc SELECT @maxc = @maxc + 1
  UPDATE TableTo Sequence SET SequencedField = @maxc WHERE SequencedField = @newc
but this entails remembering to write this code for every sequence field, not to mention the performance overhead of the MAX operation.

SQL Server 6.0 introduces a new auto sequencing column called "identity", which has a seed and increment. An "identity" will automatically generate unique values - abolishing the need for triggers for sequence generation. The good thing about this is that the data structure is stored in memory so it is fast, not like select max. The identity column however does not enforce uniqueness. Unlike the MSAccess counter field it can be disabled temporarily to insert specific values.

  SET identity_insert jobs ON             Allows you to insert specific values

  insert into jobs (job_id, job_desc, min_lvl, max_lvl)
  values(21,"Lisa's description",45,76)   Enter specific value for job_id

  SET identity_insert jobs OFF            Turns the auto sequencing mechanism back on

  insert into jobs (job_desc, min_lvl, max_lvl)
  values("Lisa's next description",45,76) Do not manually enter a value for job_id

The feature is excellent for reviving lost numbers.

The last identity value inserted into a table can be found using the global variable @@identity. There is also a new keyword IDENTITYCOL which means you don't have to know the name of the identity column. ie., Select * from jobs where IDENTITYCOL = 12.

Declarative Referential Integrity

New declarative referential integrity and constraints can provide an alternative mechanism to triggers for many types of data integrity issues. The need for triggers will not be eliminated as they will always be required for particular business rules and complex actions, although they may be greatly reduced.

Declarative referential integrity functionality is convenient and high performance, but is most of all self documenting. Since declarative integrity is declared as part of the database definition it becomes more concise and less error prone, making life easier for anyone who has to maintain databases developed by other people.

In previous versions of SQL Server the primary key was set using the stored procedure sp_primarykey. This was only used for documentation purposes and did not physically enforce a primary key. Primary key constraints are now enforced by creating a unique index on the specified columns which cannot be directly dropped. The Foreign key constraint provides single or multi column referential integrity. This constraint does not create and index, but it is obviously advisable to do so for better joint performance. A table that is referenced by a foreign key constraint cannot be dropped until the constraint is dropped.

Declarative referential integrity can be very useful for strict relational database implementation as it reduces the likelihood of mistakes. Yet there are times where such a strict implementation may be inhibiting, such as in the early stages of development when the database structure could be changing rapidly. It could be very annoying if you suddenly had to drop 30 tables from your database - but before you could do it you had to either drop all your constraints, or work out which order you must drop your tables.

CASE Expression

The CASE expression is a particularly nice new feature as it greatly simplifies conditional values within SQL. The CASE expression is similar to decode() in Oracle, and nested immediate ifs in MSAccess. A simple example of this is when it is necessary to transcribe values into meaningful text (assuming no transcription tables are available).

  SELECT Category =
    CASE type
      WHEN 'am_hist' THEN 'American History'
      WHEN 'hlth_fit' THEN 'Health and Fitness'
      WHEN 'food_nut' THEN 'Food and Nutrition'
      WHEN 'sft_dev' THEN 'Software Development'
      WHEN 'bus_mng' THEN 'Business Management'
      ELSE 'Miscellaneous'
  FROM Titles

The CASE expression is great because it may contain subqueries. This means you can perform operations that check a numbers of tables for a value and print details depending on the result.

Execute @Text

This is by far my favourite language enhancement. Execute @text supports the execution of a character string. This means that with the EXECUTE statement, a string can be created with variables that are resolved at execution time - getting around the fact that you can't normally use variables as object names. This is fantastic for iterating through all tables in a database and performing a particular function.

In previous versions of SQL Server this would require a separate statement for each table, which in turn meant that any tables removed or added to the database would require a change to SQL scripts referencing those tables.

For example, the following code would have to be used pre SQL Server 6.0 to drop all tables in a database:

  drop table authors
  drop table discounts
  drop table employee
  drop table jobs
  drop table pub_info
  drop table publishers......................

Note that each time a new table is added to the database this procedure must be changed.

New code:

  DECLARE @next char(30)
  SELECT @next = ' '
  WHILE @next IS NOT NULL                 Loop through all the user tables in the database
      SELECT @next = MIN(name) FROM sysobjects WHERE type = 'U' and name > @next
      If @next IS NOT NULL
          EXEC ('drop table ' + @next)    Drop each table

This is great if your database requires large numbers of batch administration functions.

Temporary Tables and Procedures

There are now two types of temporary tables: private and global.

Private temporary tables are specified with a #, and only the connection in which the table was created has access to the table (you cannot go and find out the name from sysobjects and have other connections access the table). Temporary tables with global visibility are specified with ##. These tables are visible to all connections. Global temporary tables are dropped when the creator is gone and the open count is 0.

Temporary procedures are compiled and cached and can be reused like other stored procedures. Temporary procedures have a similar syntax to temp tables, ie. local(#) and global (##) and are cleaned up in the same way.

Start Up Procedures

Procedures can now be marked as "startup" and will be executed whenever the SQL Server is started. Startup procedures can be very useful for performing database administration and validation tasks. These procedures can be created by creating a stored procedure as normal and then using the system stored procedure sp_makestartup. Multiple procedures can be marked as startup, but each procedure consumes one user connection. It is advisable that if you do not need to run your startup procedures in parallel, use one startup procedure to call others for serial execution and the use of only one connection.

With Check Option

The new WITH CHECK OPTION available to views assures that a row can never be added to a view that does not meet the criteria for the view.

  CREATE VIEW ca_only AS
    SELECT * from authors WHERE state = 'CA'

This will restrict users from entering any other state besides California. This options also cascades to views on views.

There's heaps more cool stuff to play with in SQL Server 6.0, and if you love the internet and hate SQL Server locking strategies I suggest you get your hands on the recently released version 6.5.

SQL Server 6.5 will provide another impressive array of new features including:

These features are definitely worth looking into, so if my wonderful boss would arrange for me to have a copy I might be able to do so.

(Boss' Note : Just as soon as we can get hold of a real copy, rest assured Lisa will be playing with it.)

Written by: Lisa Hooper
April 96

Image of Line Break