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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 25, 1998

Datatypes and TSQL - Page 13

By Sharon Dooley

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!

 



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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