Cool Things in SQL Server 6.0
by Lisa Hooper - GUI Computing
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 = @newcbut 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'
END
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
BEGIN
SELECT @next = MIN(name) FROM sysobjects WHERE type = 'U' and name > @next
If @next IS NOT NULL
BEGIN
EXEC ('drop table ' + @next) Drop each table
END
END
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'
WITH CHECK OPTION
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.)