Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 26, 1998

Datatypes and TSQL - Page 13

By Sharon Dooley

25 Jan 98


In addition to the Unicode datatypes, SQL Server 7.0 has

8000 byte CHAR/VARCHAR



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:

(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.]


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 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.


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)


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!


MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM