SQL Coding

Here is a comparison of SQL programming code for the three most popular products: Microsoft, Oracle, and mySQL.

Topics this page:

Site Map  
About this site  

Sound: Match found

Related Topics:

Certifications for Database Administrators

“If anyone's name was not found written in the book of life, he was thrown into the lake of fire.” —Revelation 20:15 (NIV)

• Microsoft Certified Database Administrator (MSCDBA) preparation:
  Take the Brainbench certification test on MS SQL Server 2000 Administration & Programming and MS SQL Server 7 Administration & Programming
  • ITForum
  • Braindump Heaven
  • Exam 70-228: Installing, Configuring, and Administering Microsoft SQL Server and 2000 Enterprise Edition or Exam 70-028: MS SQL 7.0

    AND

  • Exam 70-029: Designing and Implementing Databases with Microsoft SQL Server 7.0 or Exam 70-229: 2000 Enterprise Edition
  • Obsolete:
    • Exam 70-026: Administering MS SQL 6.5 Braindump
    • Exam 70-027: MS SQL Server 6.5 Implementation Braindump

    AND

  • Exam 70-215: Installing, Configuring, and Administering Microsoft Windows 2000 Server or
  • Exam 70-275: .NET Server (Available 2002)

    AND one Elective:

  • Exam 70-216: Implementing and Administering a Microsoft Windows 2000 Network Infrastructure

• Oracle CPD (Certified Professional Developer)

  Take the Brainbench certification test on Database Development and Administration

• Seagate offers a free download (update?) of Crystal Reports ActiveX control at: http://www.seagatesoftware.com/scrvbasic/download/. This download includes samples and help file.
• About ER (Entity-Relationship) Diagramming: Federal Information Processing Standard (FIPS) Publication 184 "Integration Definition for Information Modeling (IDEF1X)" is the standard for data modeling implemented in major CASE tools.
• Bill Vaughn's Building Great SQL7 Applications with Visual Basic
• Migrating to SQL7 from Access
• Upsizing from Access to SQL.
• Professional Association for SQL Server holds a chat the 4th Wednesday of every month 10-11 AM Pacific Time.
• List of consultants with the SQL Server Worldwide Users Group (sswug.org)
• Swynk.com's discussion forum on SQL7 and SQL2000
• Anil Desai

SQL Server 2000: New Features Provide Unmatched Ease of Use and Scalability overviews new product features including AWE support, new data types, new user-defined and built-in functions as well as enhancements in trigger functionality, indexing, collation, and property definition.

Administrating SQL 7.0 #70-028 Braindump

Implementing SQL 7.0 #70-029 Braindump

$35 Transact-SQL Programming by Kline, Kevin E.; Gould, Lee.; Zanevsky, Andrew. Sebastopol, Calif. O'Reilly and Associates, 1999 compares Microsoft SQL Server 6.5 and Sybase SQL Server 11.5.

T-SQL is compliant with just the Entry Level of the SQL-92 ANSI standard, not all features from the Intermediate and Full Levels of SQL-92.

MSDN on Securing SQL 7

This User's Security Guide details how to harden SQL Server 2000 for a C2-evaluated configuration.

SQL Factoids

Factoid	Numbers to Remember
	MS-SQL7	Oracle 8i	mySQL
Max. size of SQL Procedure	-	64KB
Tables per database	2 billion
Columns per table	1,024
Max characters in Column Name	?	30
max # of columns in composite index	16
max nonclustered indexes per table	249
max key bytes	900
page extent size	64 KB
page size	8 KB (8,192)
max bytes per row	8,060
max char bytes	8,000	255
max int value	2.147 billion (2^31)
max smallint value	32,767
max smallmoney value	$214,748
money decimal precision	4 (.4444)

To invoke MS-SQL automatically, the SQL service Manager is placed in the Startup folder with a Target of:

E:\MSSQL7\Binn\sqlmangr.exe /n

SQL is pronounced either as a sequence of letters "Es-Queue-Ell" or as acronym "Sequel".

Yahoo Computers and Internet > Programming and Development > Languages > SQL

MySQL AB was created by Monty Widenius.

MySQL Enterprise Solutions (Indianapolis, Ind. : John Wiley & Sons, Inc. (US), 2003) by Pachev, Alexander "Sasha"

SQLCMD from Microsoft enables SQL to be applied across several SQL servers.

Execution Environments - Oracle vs. Microsoft

This table cross-references the jargon for the same concept from both products.

Action	Oracle SQL	Microsoft SQL
Tool for interactive input, store, and running of stored procedures	C:>SQLPLUSW userid/password@what.world	Run SQLW Run isqlw for SQL Query Analyzer
To control output	set serveroutput on set serverout off	-
Send output to a file	spool myoutput.out	-
Stop sending output	spool off	-
Send output file to printer (for lpr)	spool out	-
To invoke SQL statements stored in file named my.sql	start my.sql	-
Return to the OS	exit	-
Language Name	PL/SQL - Procedural Language	Transact-SQL
Read product documentation	-	For Books online: Within SQLW, Help toolbar -> Building SQL Server Applications -> Transact-SQL Reference -> System Stored Procedures (T-SQL)
View properties about an object	select * from all_views	sp_help 'object_name' or sp_helptext 'object_name' for unencrypted comments in the syscomments system catalog table.
Text source of all stored objects belonging to the user	user_source	-
Tool to trap activity between client app and SQL to a flat file	-	SQL Server Profiler
Comment operator	-- double hyphen or between /* and */	same
To execute a procedure in the buffer	start or @ (ampersand)	?
To execute SQL script in the buffer	. dot run; or / (slash)	GO or F5 key or Ctrl-E
To concatenate and print	DBMS_OUTPUT.PUT_LINE ( 'hello' || v_name );	-
Ending a block	period ends each PL/SQL block	; semicolon ends each SQL block.

Every time an Oracle user invokes SQL*Plus, two scripts are also automatically executed:

• the Site Profile glogin.sql for all users defines column formats. By default, environment variable SQLPATH points to this file at $ORACLE_HOME/sqlplus/admin
• the login.sql for the user.

Commands

Oracle classifies and separates SQL commands into two distinct groups:

• DML (Data Manipulation Language) commands change data in tables under transaction control (can be undone).
• DDL (Data Definition Language) commands change metadata of tables containing data. These commands, such as TRUNCATE, require a COMMIT. PL/SQL does not support ANSI-SQL's data definition commands.

Oracle SQL commands:

ALTER
ANALYZE
AUDIT
CONNECT
CREATE
DELETE
DROP
GRANT
INSERT
LOCK
NOAUDIT
RENAME
REVOKE
SELECT
SET ROLE
SET TRANSACTION
TRUNCATE
UPDATE

To define a bind variable in Oracle's SQL*Plus:
PROMPT
ACCEPT
VARIABLE x NUMBER

To define a Oracle PL/SQL procedural block using an explicit cursor:

DECLARE --optional PL/SQL user variables, cursors, local subprograms, exceptions:
v1 NUMBER(3);
v_empno employee.empno%TYPE;
CURSOR emptyp_cur IS
SELECT emptyp.type_desc
FROM employee_type
WHERE type_code = :emp.type_code
BEGIN --mandatory data manipulation statement(s):
v1 := 3;
DBMS_OUTPUT.PUT_LINE('v1= ' || v1);
IF NOT emptyp_cur%ISOPEN THEN
OPEN emptyp_cur; END IF;
FETCH emptyp_cur INTO :emp.type_desc;
CLOSE emptyp_cur;

INSERT INTO employee VALUES (6, 'TOM LEE', 10000);
UPDATE employee SET sal = sal + 5000 WHERE empno = 6;
SELECT sal INTO v_sal FROM employee WHERE empno = 6;
COMMIT;
EXECUTE procedure EXCEPTION --optional error handling code

WHEN exception
THEN   null; passes control to the next statement. END; --mandatory .

To print a bind variable in SQL*Plus:

PRINT :x; --Note the colon to designate an Oracle bind variable

Clauses

Column clause	Table clause
SELECT *	FROM SchemaOwner.TableName   tuple ;

Loading Data Into Databases

$99 DBLoad shareware provides a GUI to load data from text delimited files and to transfer data from among databases of different vendors (MySQL to Oracle, etc.), The package can be setup to transfer on a set schedule.

# Manually load a text delimited file named "ImportFile.csv" on a PC into MySQL table1:
LOAD DATA LOCAL INFILE "./ImportFile.csv"
     INTO TABLE table1
     FIELDS TERMINATED BY ","
     OPTIONALLY ENCLOSED BY """"
     LINES TERMINATED BY "\r\n"
     (field1, filed2, field3);

The order of fields in the flat file should match fields in the database.
If your file is delimited by tabs, use instead: FIELDS TERMINATED BY "\t"

This assumes that there is a valid .my.cnf file with entries such as:

[client]
user = DBUSERNAME
password = DBPASSWORD
host = DBSERVER
[mysql]
database = DBNAME

This also assumes that permissions have been changed on the file to make it writable with a shell command such as:

chmod 600 /.my.cnf

Scriptella ETL (Extract-Transform-Load) tool is a Apache Open sourced Java program that uses XML syntax to load CSV data into databases Javadoca on it. Notes on this

Formatting and Transforms

The lack of a value is called "NULL" -- the value of columns before being populated with actual values. Null values are not returned by SQL queries. Comparing a variable to the reserved word NULL will return NULL rather than TRUE. So to transform nulls to recognizable value, use a special built-in (datatype sensitive) function:

Oracle SQL	Microsoft SQL
NVL( column_name, value_if_null ) NVL( num_field, 0 ) NVL( txt_field, "NULL" )	ISNULL( column_name, value_if_null ) ISNULL( price, $0.00 ) ISNULL( SSN, 'NNN-NN-NNNN' )

To convert a small number of fixed values in Oracle:

decode( weekday, 1,'Sun', 2,'Mon', 3,'Tue', 4,'Wed', 5,'Thu', 6,'Fri', 7,'Sun', '???')

To calculate numbers or display text not from any table, use the special dummy table owned by Oracle user sys:

select 1+1 FROM dual

Variables (temporary holding areas)

PL/SQL prompts entry of values for variables preceded with an ampersand and ending with a semi-colon, such as WHERE empid = &empid;

To override Oracle's default prompt text, precede the SELECT statement with

ACCEPT var PROMPT 'Enter ...'

SQL*Plus preserves values for variables preceded with a double ampersand.

Type a slash and press Enter to rerun statements. SQL*Plus stores and reuses the most recently executed SQL statement in file afiedt.buf.

Data Definitions

Action	Oracle SQL	Microsoft SQL
Datatypes and Value Assignment	skalar char, number, long, date, and varchar2 as in price := qty*(cost*2.5); plus v_valid_order boolean not null := true; and tables	-
Set date format just for current session	alter session set nls_date_format = 'YYYY-MON-DD-HH24:MI:ss'	SET DATEFORMAT
Create Custom Datatype	-	EXEC sp_addtype @typename=typeSSN, @phystype='CHAR(11)', @nulltype='NOT NULL'
Create a Table with a Check Constraint using a custom data type typeSSN	-	CREATE TABLE SSNTable ( EmployeeID INT PRIMARY KEY, EmployeeSSN typeSSN, CONSTRAINT SSNCheck CHECK ( EmployeeSSN LIKE '[0-9][0-9][0-9]—[0-9][0-9]—[0-9][0-9][0-9][0-9]' ) )

Data Integrity Constraints

The preferred method for maintaining data integrity is to add constraints associated with tables. Rules and default objects are, as my tween daughter says, “so last season”!

Type of Constraint	Oracle	MS-SQL
list constraints	select constraint_name from user_constraints;	?
Domain integrity by limiting acceptable values for a column entry.		CHECK
PRIMARY KEY A column (or a composite index of up to 16 columns) used to uniquely identify a row in a table. A table can only have one.		CONSTRAINT tab1_pk PRIMARY KEY (col1, col2) If this is not specified, the MS-SQL default is to create a clustered index. Tables created without a clustered index are called heaps.
UNIQUE Entity integrity A non-primary key column is unique among all rows.		By default, a nonclustered index is created for each such column. Each combination of columns that could uniquely identify rows in a table is called a candidate key.
NOT NULL		Special processing for null values degrade performance.
FOREIGN KEY A foreign key in one table points to a primary key in another table.		ALTER TABLE table1 col_a INT NULL CONSTRAINT tab1_fk1 REFERENCE table2(tab1_col_pk)
IDENTITY property (starting IdentitySeed, IdentityIncrement)		ALTER TABLE table1 ADD Identity_column INT IDENTITY(1,1) GO Whether a table has an identity column can be determined using the OBJECTPROPERTY function. This can be selected using the IDENTITYCOL keyword.

Default Objects

This replaces sp_bindefault and sp_bindrule objects SQL7 now recognizes only for backward compatibility.
USE table1
GO
CREATE DEFAULT default_par AS 72
GO
sp_bindefault default_par, 'scores.par'

By default, table names specified in the FROM clause have an implicit schema associated with the user's login. A schema is a logical grouping of database objects based on the user/owner. Tables in another schema can be specified if the other schema is prepended to the table:

FROM other_schema.other_table

Oracle supplied built-in package UTL_FILE used to read or write text operating system files from within PL/SQL.

Indexes

Action	Oracle SQL	Microsoft SQL
Create Index	-	Syntax: CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON table (column [,...n]) [WITH [PAD_INDEX] [[,] FILLFACTOR = fillfactor] [[,] IGNORE_DUP_KEY] [[,] DROP_EXISTING] [[,] STATISTICS_NONRECOMPUTE] [ON filegroup]

• MS-SQL7 stores as many data rows as can fit in 8KB pages.
• To support sequential retrieval of data, each data page contains a header record containing a row locator which points to the previous and next data page.
• To arrange for fast sequential access, data in a table may be arranged physically to a key (such as a sequentially assigned employee number or the date/timestamp of entry). Such a table is usually physically pre-sorted according to its clustered index key. This takes 120% more space than the space of data alone. If a new row is inserted in the middle, data rows are physically moved to make room for it. So it's not a good idea to cluster index a table by a column which is not sequentially input. This allows queries to be answered without the time-consuming task of sorting.
• In MS-SQL7, key values from several fields (such as last name, first name, middle initial, etc.) are stored in composite index spanning up to 16 columns.
• To speed up the random retrieval of data pages, clustered indexes are created and stored on index pages apart from the data pages so that sorting is performed on pointers to the data instead of the entire data pages.
• Clustered indexes are organized using a technique called B-trees, which are depicted visually as a triangular upside-down tree (like a pyramid).
• Pages containing data are called leaf nodes at the bottom layer.
• The top node of the B-tree pyramid is the sysindexes.root node.
• A lookup traverses the Intermediate nodes in the middle.
• To avoid the need to shuffle index pages, indexes are built with only a percentage full. FILLFACTOR=100 specifies full index leaf, which limits reads to resolve queries. This reduces space usage at the expense of time for page splits during inserts and updates.
• The PAD_INDEX option specifies intermediate page fullness.
• Index fields should be created as 'NOT NULL'.
• When IGNORE_DUP_KEY is specified, SQL ignores individual duplicates but not the entire request.

SQL Indexing Architecture

Microsoft's Index Tuning Wizard uses a workload file generated by the SQL Server Profiler during a trace run and presented in a Index Usage Report which documents the percentage of queries resulted by recommended indexes.

Index Work

Action	Oracle	MS-SQL
Create statistics on every column supporting statistics in the current database		sp_createstats
Create statistics data from the table.		CREATE STATISTICS
When were statistics last updated?		STATS_DATE
Determines whether a particular statistic will be automatically updated.		sp_createstats
The density of an existing statistic set.		DBCC_SHOW_STATISTICS
Update statistics data from the table.		UPDATE STATISTICS
Remove statistics data from the table.		DROP STATISTICS

DBCC SHOWCONTIG Fragmentation Statistics

An out-of-order page is one for which the next page indicated in an IAM is a different page than the page pointed to by the next page pointer in the leaf page. Huh?

Statistic	Description
Pages Scanned	Number of pages in the table or index found during scanning.
Extents Scanned	Number of physical extents among pages scanned.
Extent Switches	Number of times the DBCC statement left an extent while it traversed the pages of the extent.
Avg. Pages per Extent	Number of pages per extent in the page chain.
Scan Density [Best Count: Actual Count]	100 percent if everything is contiguous. The smaller this value, the more fragmentation exists. The best count is the ideal number of extent changes that would be necessary if everything were contiguously linked. The actual count is the actual number of extent changes.
Logical Scan Fragmentation	The percentage of out-of-order pages returned from scanning the leaf pages of an index.
Extent Scan Fragmentation	Percentage from a count of out-of-order pages divided by the count of all leaf pages of an index scanned.
Avg. Bytes free per page	The absolute number of free bytes averaged over all pages scanned. Lower values are better because they make fuller use of each page. Higher row sizes make this number worse.
Avg. Page density (full)	Percentage of each page's bytes of useful data divided by the total bytes of all pages scanned. Higher values are better because they make fuller use of each page. make fuller use of each page. er worse.

Query Joins

• inner joins
• outer joins
• nested loops
• merge joins
• hash joins

Views

Action	Oracle SQL	Microsoft SQL
List Views defined	select view_name from user_views;	-
Display read I/O	-	SET STATISTICS_IO ON
Display Clustered indexes used in optimized query Execution Plans (algorithms)	-	SET SHOWPLAN_TEXT ON or SET SHOWPLAN_ALL ON for resource costing estimates or graphical SHOWPLAN in SQL Server Query Analyzer
Update Statistics Used for Auto Indexing	-	UPDATE STATISTICS or UPDATE STATISTICS NORECOMPUTE to stop updating
Display database linkages and sizes	-	DBCC CHECKTABLE
Get Fragmentation Statistics	-	SELECT OBJECT_ID('PUBS..TITLES') GO DBCC SHOWCONTIG(123456789) number from Data Base Consistency Checker response DBCC CHECKTABLE displays linkages and sizes
Create View	?	CREATE VIEW testview as SELECT firstname, lastname FROM employees

View Information

Information Stored	Oracle Data Dictionary View Name	Microsoft MS-SQL
A list of current errors on all objects accessible to the user	all_errors	.
Text source of all stored objects accessible to the user	all_source	.
Current errors on all stored objects in the database	dba_errors	.
All PL/SQL objects in the database	dba_object_size	.
Text source of all stored objects in the database	dba_source	.
Current errors on all a user's stored objects	user_errors	.
Text source of all stored objects belonging to the user	user_source	.
User's PL/SQL objects	user_object_size	.

Logic Control

Action	Oracle SQL	Microsoft SQL
Control structures	PL/SQL extensions to SQL: conditional if-then-else iterative for-loop sequential while-loop & end loop; exit-when unconditional flow-control goto	-
Labels to go to	<< SampleLabel >>.	-
Sequential processing using a cursor	declare   cursor each_emp is     select ename, sal     from emp     where deptno = 30;   emp_name varchar2(20);   emp_sal number(7,2); begin   open each_emp;   loop     fetch each_emp       into emp_name, emp_sal;     if each_emp%NOTFOUND then       exit;     end if;     emp_sal := emp_sal + (emp_sal * .10);   end loop;   close each_emp; end;	use payroll declare @ename as varchar(20) declare @emp_sal as decimal(7,2) DECLARE @each_emp CURSOR FOR SELECT ename, pay_rate FROM emp inner join pay on emp.emp_id = pay.emp_id where deptno = 30 OPEN each_emp FETCH NEXT FROM emp_cursor into @emp_name, @emp_sal WHILE @@FETCH_STATUS = 0 BEGIN begin set @emp_sal = (@emp_sal + (@emp_sal * .10)) FETCH NEXT FROM each_emp into @emp_name, @emp_sal end CLOSE each_emp DEALLOCATE each_emp END
Related functions and cursors stored together	Packages	-
Table support	declare   type emp_table_type is     table of varchar2(35)     index by binary_integer;   emp_table emp_table_type;   i binary_integer := 0;   cursor each_emp is     select ename     from emp; begin   open each_emp;   loop     i := i + 1;     fetch each_emp       into emp_table(i);     if each_emp%NOTFOUND then       exit;     end if;   end loop;   statement(s);   -- work with emp_table(1), emp_table(2), etc.   close each_emp; end;	EXEC CREATE TABLE emp_table (column1 INT NOT NULL, column2 CHAR(10) NOT NULL) GO EXEC sp_help emp_table GO SELECT emp_table FROM INFORMATION_SCHEMA.TABLES GO
Debugging	Public package dbms_output creates a buffer to accept dbms_output.put or put_line commands. set serverout displays the buffer.	exec sp_addmessage 50500, 14, 'msg' creates error code 50500 with severity 14 at state 1. raiseerror (50500,14,1) issues the message

PL/SQL does not support SQL's data definition commands, session control commands, or the system control commands.

Conventions

Item/Object	add/drop/help Stored Procedure EXEC ...	To get a list: SELECT createdate, updatedate, accdate, ... FROM server.database.owner.object	Executive GUI
NT Network Accounts		Domain SAM
SQL Login		dbname, loginname, isntgroup FROM master..syslogins (view of master table sysxlogins)
Databases	sp_helpdb database_name	name, filename, crdate FROM master..sysdatabases
Columns		TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
Privileges		* FROM INFORMATION_SCHEMA.TABLES_PRIVELEGES
Database User Accounts	sp_addlogin login, name_in_db	uid, name from northwind..sysusers where islogin = 1
	sp_grantdbaccess login, name_in_db
	sp_grantlogin login, name_in_db or GRANT CREATE VIEW, CREATE PROCEDURE, CREATE TABLE TO cathy
	sp_revokelogin, sp_denylogin login, name_in_db or REVOKE CREATE VIEW, CREATE PROCEDURE TO cathy
	sp_denylogin login, name_in_db or DENY CREATE VIEW, CREATE PROCEDURE TO cathy
Permissions for Uid's		uid, action, protecttype from northwind..sysprotects
Fixed Server Role	sp_addsrvrolemember @loginame, @rolename	name from master..syslogins system table	for Logins
Fixed Database Role	sp_addrolemember role, security_account	name from northwind..sysusers where issqlrole = 1
Database Standard User Role	sp_addrole @rolename, @ownername	name from northwind..sysusers where issqluser = 1
Application Role	sp_addapprole @rolename, @password GO sp_setapprole @rolename, @password	name from northwind..sysusers where isapprole = 1
Primary Data Files	CREATE DATABASE library ON PRIMARY ( name=library_data, FILEname='c:\mssql7\data\library.mdf', size=10MB, MAXsize=15MB, FILEGROWTH=20%) DROP DATABASE library	...\*.mdf containing 8KB data pages (128 pages per MB)
Secondary Data Files		...\*.ndf containing 8KB data pages (128 pages per MB)
Log Files	... LOG ON ( name=library_log, FILEname='c:\mssql7\data\library.ldf', size=3MB, MAXsize=5MB, FILEGROWTH=1MB)	C:\Mssql7\Log\*.ldf (sized 25% of data file by default)
File Groups	ALTER DATABASE northwind ADD FILEGROUP orderhistorygroup GO

• The NT Registry is used to store SQL start-up parameters.
• Unlike User-defined roles, Fixed means you can't create new roles.
• Max row size = 8060 bytes

Cursors

An Oracle cursor is a variable that runs through the tuples of some relation. This relation can be a stored table or the answer to a query. Fetching into the cursor each tuple of the relation allows us to process the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position.

A cursor is an area of memory to hold data fetched from the database. global cursors are deallocated when the connection ends. local cursors are limited to a single procedure.

This requires manual effort to de-allocate cursor memory, so cursors are de-emphasized by SQL 2005 in favor of other alternatives.

Cursor variables allow different SQL SELECT statements.

Oracle provides four built-in cursor ATTRIBUTES to determine the status of cursors:

• SQL%ROWCOUNT tells how many rows have been affected by the executable statement.
• SQL%FOUND tells if one or more rows have been found that fit the select criteria.
• SQL%NOTFOUND tells if no rows are affected by the executable statement.
• SQL%ISOPEN will be FALSE in SQL and is used only after an EXPLICIT cursor has been defined.

Declaring a CURSOR INSENSITIVE makes it not sensitive to changes in the table and won't refresh.

FETCH FIRST
FETCH NEXT
FETCH RELATIVE 3   (3 rows prior)
FETCH ABSOLUTE 4
FETCH ABSOLUTE -1
FETCH LAST   (bottom row)


Declaring a SCROLL CURSOR allows you to fetch backward with these commands:

FETCH PRIOR
FETCH RELATIVE -3

PL/SQL example:

DECLARE
v_cnt NUMBER;
c1rec NUMBER;
CURSOR c1 IS 
    SELECT * FROM customers;
BEGIN OPEN c1;
FETCH c1 INTO c1rec;

To link a row updated to the latest row fetched inside a cursor called row_cur:

UPDATE x
   SET due_dt = sysdate + 2 
WHERE CURRENT OF row_cur;

Triggers on Tables

Action	Oracle	MS-SQL
Get a list of triggers	.	SELECT trigger_name 'INSERT'=object_name(instrig), 'UPDATE'=object_name(updtrig), 'DELETE'=object_name(deltrig), FROM sysobjects WHERE type = 'U' AND (instrig <> 0 OR updtrig <> 0 OR deltrig <> 0)
Create a new trigger	.	CREATE OR REPLACE TRIGGER trigger_name after logon -- after logoff -- after servererror -- after startup -- after shutdown -- after create -- after drop -- after alter ON table FOR ( [DELETE] [,] [INSERT] [,] [UPDATE] } [WITH ENCRYPTION] AS sql_statement
Drop a trigger	.	DROP TRIGGER trigger_name


Sequence of Trigger processing:


Step	Action
1	Execute before statement triggers.
2	Loop for each row affected by the SQL statement.      a.    Execute before row triggers.      b.    Lock and change rows, check for integrity constraints.
3	Complete deferred integrity constraint checking.
4	Execute after statement triggers.

Alerts

Alerts are stored in the Msdb physical database and managed by the SQL Server Agent

Aggregate Functions

Sum, Avg, Min, Max, StDev, StDevP, Var, VarP

count(*) returns the number of rows in the table.
count(column name returns the number of non-null values.

Aggregate functions in Jet databases

Debugging

• First, look for the most common syntax errors:
  • missing semi-colon ending SQL blocks
  • missing parens,
  • unmatched variable names
  • referencing tables or column names that have not been defined (spelling)
• Look for these mistakes at the line Oracle reports in error.
• Use the sho err (show error) command to list code errors.

How to Use SQL Server 7.0 Performance Analysis Tools to Optimize Your SQL Server 7.0 Applications by Chris Foss

Tables: Chapter 6 from Inside Microsoft SQL Server 7.0

Oracle Resources

Oracle Underground FAQ list provides links, message boards, articles, a glossary and other resources submitted by Oracle affciados.

Moderator Frank Naude's Oracle PL/SQL FAQ

Oracle View 1996-8 is an online magazine by Intelligent Enterprise, Inc. for architects and managers of information technology on the implementation and management of Oracle database products, development strategies, and application tools.

Andrew Odewahn's Oracle Web Applications: Developer's Introduction online book

The Virginia Tech reference tool describes functions, procedures, data types in the PL/SQL packages that make up the Web Application Server PL/SQL Web Toolkit in addition to an Overview, Glossary, FAQ, Installation, and Cartridges.

Reveal Net: PL/SQL Pipeline maintained by the International Oracle Users Group (IOUG-A) of more than 7,500 Oracle professionals,

Want to access Oracle and mySQL databases from .NET apps?
CoreLab's OraDirect.NET can do it for less than $200.

MS-SQL Resources

Microsoft's Best Practices Analyzer for SQL Server 2000 (bpasetup.msi) published 5/25/2004 reports on the implementation of common Best Practices such as the usage and administration aspects of SQL Server databases.

Microsoft SQL Analysis Services:

• Installing
• Monitoring Analysis Services Performance
• How to Install SQL Server 7.0, Enterprise Edition on Microsoft Cluster Server: Step by Step Instructions To cluster MSDTC run comclust.exe on both nodes before you set up SQL Server.

  network bandwidth is likely to become a problem first, then RAM within the server, then maybe CPU capacity.

  To upgrade from 6.5 to 7, first uncluster, remove replication, migrate, then re-cluster. SQL Server 2000 - there is no such thing as "unclustering" anymore.

  How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000 (September 2000)

  fibre interconnects for the drives and a private network for the heartbeat (most of the time I simply use a crossover cable).

  between 5 & 15% reduction in performance impact on the host during an online backup.

  Replication wizard

  6 roundtrips are needed for a 2-node 2-phase commit - the prepare, the actual transaction, and the commit.

  http://www.microsoft.com/sql/productinfo/tpc.htm benchmarks

  To make a database accomodate VLDB, You shouldn't need to partition the database logically, but I would explore adding filegroups and files to make it more manageable. Do this my creating clustered indexes on tables with the ON FILEGROUP option.

  October 1998 Microsoft SQL Server 7.0 Performance Tuning Guide

  Microsoft SQL Server 7.0 Query Processor

  Microsoft SQL Server Administrator's Companion: Chapter 8 - Monitoring Server Performance and Activity

  Microsoft SQL Server Diagnostics: Chapter 9 - SQL Server Tools Troubleshooting

  SQL Server 7.0 Evaluation Exercises from Microsoft TechNet Server Utilities CD file AllExer.exe in the \Sql\Evalex directory.

Questions

Question: what is the point when the small size of a table makes it more efficient than an indexed search?

Question: You are collecting SQL server Cache Hit Ratio data. What should the value be greater than for a well-tuned SQL server?

a. 10%

b. 25%

c. 50%

d. 65%

e. 80%

f. 95% Correct answer: e