25 Jan 98
Datatypes
In addition to the Unicode datatypes, SQL Server 7.0 has
8000 byte CHAR/VARCHAR
8000 byte BINARY/VARBINARY
UNIQUEIDENTIFIER 16 byte GUID
Bits can now contain nulls. Fixed length fields that permit nulls are now still fixed length fields rather than turning magically into variable length as they did before.
The max key size is 1024 bytes. Numeric/Decimal datatype processing is improved; uses integer arithmetic internally.
There are read-only computed columns. These cant be indexed. Example:
CREATE TABLE T1 AS
(celsius .,
farenheight AS (32 + ((9/5) * celsius))
[Note the AS in the CREATE TABLE. Dont know if that is an addition or a mistake.]
DDL and Schema changes
There can be 1024 columns per table and currently 32 tables per query. The latter is expected to increase to at least 128 by the final release.
It is possible to drop columns with the Alter table statement. It is possible to change the datatype of an unindexed column as long as it is a legal conversion. There is an ALTER TRIGGER, VIEW, Procedure statement which allows you to change the definition of these without losing the existing permissions as you do when you drop and recreate them.
You can have multiple triggers per update action. The order of execution of these is undefined. If you create a trigger with the same name as an existing trigger, it replaces the trigger. If it has a different name, it is added. Trigger recursion (if a trigger performs an operation on its own table, the trigger fires again) is now supported with a database option [too high a level in my opinion; should be at a table level.]
Security
Groups are gone. Roles are here. Users get placed in roles. The model isnt quite consistent with NT groups. There are predefined roles and you can define your own. Its much more flexible than the old group scheme.
Cursors
Cursors are now scoped and can be declared as local or global. They can be passed as output parameters from a stored procedure. There is a cursor variable in T-SQL which allows an SP to create a local cursor and pass it around.
Schema info
There is support for ANSI/ISO Information Schema
System functions are still supported. New system functions include ObjectProperty, ColumnProperty, TypeProperty, IsMember, and Permissions
Delayed name resolution
This allows you to, for example, create a table and immediately refer to it in a stored procedure.
DML
Theres a new TOP clause for the SELECT statement. Returns top n or n% rows. Can be used in subqueries. Examples:
SELECT TOP 10 emp_name FROM employees ORDER BY emp_salary DESC
UPDATE employees SET salary = salary * 1.10 FROM
(SELECT TOP 10 WITH TIES * FROM employee ORDER BY rating)
AS e WHERE employees.emp_id = e.emp_id
[There are some subtle changes to the UPDATE statement buried in this example.]
You can do a substring on Text or Image data.
You can get real empty strings (optional) not a single space.
Cursor status functions (no further details)
Datetime addition and subtraction (no further details)
STDEV/STDEVP/VAR/VARP functions (no further details)
CAST (no further details)
"[ ]" identifier quoting (no further details)
OSQL
Like isql but uses ODBC instead of DBLIB.
Many thanks to Sharon for providing these notes - drop her a note at sharond@compuserve.com and tell her thanks!
» 
IE 7
Firefox 2.0
Comment and Contribute
