By Craig Borysowich
Expert Author
Article Date: 2009-03-03
A. SQL LANGUAGE RESTRICTIONS The following is a list of major NON-ANSI and NON-ODBC SQL syntax provided by SQL Server which must not be used in the System Development Environment (SDE).
Do not use:
The COMPUTE keyword in a SELECT statement to generate simple reports with summary values.
The ALL keyword in a GROUP BY clause.
User defined datatypes built on system supplied datatypes.
Temporary tables which exist only for the duration of the current work session.
The != (not equal) sign. (Note that SQL Server 4.2 also supports the <> sign, which is ANSI and ODBC compliant.)
The ability to nest an aggregate function (e.g., AVG) inside another aggregate function (e.g., MAX).
The ability to insert data from a table into that same table.
The ability to extract data from one table and put it into another using a JOIN in an UPDATE statement.
The ability to remove data based on data in other tables using a JOIN in a DELETE statement.
The TRUNCATE TABLE statement to delete all rows in a specified table and reclaim the space it took up.
The ability to update through views.
The ability to scan a table and update its data one row at a time using the clause FOR BROWSE in a SELECT statement.
The ability to define a maximum of thirty characters as identifiers of database objects like tables, columns, etc. (The maximum for ANSI standard is eighteen.)
The "duplicate-key" or "duplicate-row" options in the CREATE INDEX statement to control whether duplicate keys/rows are allowed.
B. SQL Datatypes
The data types which can be used are listed below. The exceptions are:
Text
Image
Datatype
| Comment
|
bit
|
|
binary(n)
|
1<=n<=254, restricted to 254 as per ODBC standard
|
varbinary(n)
|
1<=n<=255
|
char(n)
|
1<=n<=254, restricted to 254 as per ODBC standard
|
varchar(n)
|
1<=n<=255
|
tinyint
|
|
smallint
|
|
int
|
|
float
|
|
real
|
|
smallmoney
|
|
money
|
|
smalldatetime
|
|
datetime
|
|
timestamp
|
System maintained
|
Table 10-1 - SQL Database
C. Stored Procedures
In a SQL Server environment, stored procedures can implement significant portions of the application logic. It is important to treat these stored procedures like any other program module and to use consistent standards for program structure, comments and naming.
1. Coding Conventions
Stored Procedures:
Consist of only SQL statements.
Are automatically committed so must correspond to a single unit of work.
Must have all parameters beginning with the special character @.
2. Sybase Documentation Standards
COMMENTS must be included in each SELECT, INSERT, DELETE or UPDATE statement to explain the intent of the statement and any special processing considerations.
All procedures must contain a COMMENT block at the top of the procedure similar to the example in the empty template. The file is available in the PVCS Working Set 'SQL Procs' stored under the file name: sample.prc.
3. Return Values
Code
| Description
|
E
|
Standard Sybase/Microsoft defined error code
|
R
|
User-defined error code, positive or zero indicating no error
|
V
|
Validation result code: 0 = Valid, 1 = Invalid
|
Table 10-2 - Return Values
D. Triggers
A trigger is a mechanism similar to a stored procedure. It is used to enforce strict integrity. Triggers can be defined for SELECT, INSERT, UPDATE, and DELETE actions against a database table.
Since Sybase does not support the CASCADE, RESTRICT and SET NULL options on DELETE or the FOREIGN KEY clauses in the DDL, the equivalent functionality will be enforced using Sybase triggers.
The format for triggers will be:
uu...u_Tx
Where: uu...u User-defined name, usually the table name.
T The first letter in the suffix indicating a trigger.
x The second letter in the suffix indicating the action, Upper case, May consist of multiple words separated by underscores.
Prefix
| Action
|
TS
|
Selection
|
TD
|
Deletion
|
TU
|
Update
|
TI
|
Insert
|
Table 10-3 - Triggers
E. Sybase Security
The system DBA, or a proxy, owns all databases. Only the DBA has the CREATE DATABASE level of authority. DBAs, as the database owners, are responsible for granting access to all database tables.
Comments
