Bristle Software SQL Tips

Table of Contents:

Details of Tips:

1. Oracle Tips

   1. SQL Tips

      This section contains tips on standard SQL (Structured Query Language) statements in Oracle.

      1. SELECT * and more

         Last Updated: 6/6/1999
         Applies to:  Oracle 7.3, 8 (and probably earlier versions)

         To select all columns of a table:

         	select * from table

         However, to select all real columns, plus a pseudo-column like "user":

         	select table.*, user from table

         The following does not work:

         	select *, user from table

         --Fred

      2. Materialized View

         Last Updated: 1/7/2002
         Applies to:  Oracle 8+

         Oracle 8i introduced a new feature called a "materialized view".  You define it just like any other view, except that you add the keyword MATERIALIZED:

         	CREATE MATERIALIZED VIEW view_name

         A materialized view is like a combination of a table and a view.  Like a view, it is defined as a logical view into the data of one or more tables.  When you update the tables, subsequent queries of the view see the updated data.  However, like a table, its data is stored in the database.  Also, like a table, it is faster if you define indexes for it.

         A regular view is stored as a mapping of data from tables.  When you modify the data in the tables, the view is completely ignored.  When you access the view, it joins the data currently in the tables, and returns the data you requested.  A materialized view is stored as such a mapping along with a copy of the actual data from the tables.  When you modify the data in the tables, the view's copy of the data is also updated.  When you access the view, the data is drawn directly from the copy.

         Thus a materialized view makes table updates a little slower, but makes view queries much faster.  It also consumes additional space in the database.

         You could accomplish the same effect by defining an additional table instead of the view, and using triggers on the component tables to update it each time they are changed.  However, using a materialized view is more convenient, more efficient, and clearer to the next person who has to maintain your database.

         Thanks to Andy Glick for sending me a sample of a materialized view from his application!

         --Fred

      3. Search Source Code of Stored Procedures

         Original Version:  1/7/2006
         Last Updated: 1/20/2006
         Applies to:  Oracle 7.3+

         Since the source code of all Oracle stored procedures, functions, and packages is stored as data in the database, you can do queries against it like any other data.  For example, you can find all references to a given table in all stored procedures, functions, and packages with the following query: 

         	SELECT *
         	FROM   all_source
         	WHERE  LOWER(text) LIKE '%table_name%'

         If you don't have SELECT access to the all_source table, you can try user_source or dba_source instead.

         Thanks to Chris Boos, the most knowledgeable Oracle guru I know, for this and many other tips!

         Tip within a tip:  To prevent the LIKE clause from treating underscore as a wildcard, use the ESCAPE clause, as:

         	SELECT *
         	FROM   all_source
         	WHERE  LOWER(text) LIKE '%table\_name%' ESCAPE '\'

         --Fred

   2. PL/SQL Tips

      This section contains tips on PL/SQL statements -- the Oracle "procedural language" superset of SQL that you use to write stored procedures.

   3. SQL Navigator Tips

      This section contains tips on the SQL Navigator tool by Quest Systems. It is a graphical front end to the Oracle database, allowing you to create, delete, view, and modify all Oracle objects: tables, views, stored procedures, etc.

   4. See Also

      Last Updated: 6/6/1999
      Applies to:  Oracle 7.3+

      The following are good sources of info about Oracle:

      1. Koch, George, and Kevin Loney. Oracle 8, The Complete Reference.  Berkeley CA: For Oracle Press by Osborne McGraw-Hill, 1997.  ISBN 0-07-882396-X.
         This book includes introductory database concepts as well as a complete reference to Oracle SQL and PL/SQL statements.  The companion CD contains a complete copy of the book, so you can read it on-line, search it, etc.
      2. Any of the O'Reilly books.  I've been very impressed by all of the O'Reilly books since my early Unix and X-Windows days in the 80's, and they have a complete series on Oracle, covering PL/SQL, the standard packages, etc.

      --Fred

2. MS SQL Server Tips

   1. SQL Tips

      This section contains tips on SQL (Structured Query Language) statements in MS SQL Server.

      1. Dynamic SQL in a Stored Procedure

         Original Version:  2/7/1999
         Last Updated: 3/18/2006
         Applies to:  MS SQL Server 6.5+

         A typical tradeoff for a database application is dynamic SQL (SQL commands embedded in the application -- for flexibility) vs. stored procedures (pre-compiled SQL procedures stored in the database and invoked by name from the application -- for speed and control over what SQL statements get executed).   However, you can have the best of both worlds by using dynamic SQL inside your stored procedures.  In a stored procedure, you can use the EXEC statement to execute a string of SQL statements that you built dynamically in the stored procedure or read from the database or any other data source.

         Warnings:

         1. Beware of security issues.  One advantage of stored procedures is that they hide the underlying table structure, allowing you to grant access to the stored procedure and not to the tables.  The stored procedure can access the tables using its own credentials.  However, if the stored procedure uses dynamic SQL, the caller of the procedure will need permissions (select, update, delete, insert) on the tables.
            
         2. Beware of runtime errors, like exceeding the max string length when dynamically building a string of SQL to be executed.
            
         3. Beware of performance issues.  For dynamic SQL, the query plan is only created at runtime, and may not even be cached if the dynamic query changes too much.
            
         4. Beware of debugging issues.  Dynamic SQL is much harder to debug because you don't know for sure what string value is being passed to EXEC, unless you check it with the SQL Profiler.

         Thanks to:

         • Steve Rhoads for this tip.
         • Darin Strait for the warnings.

         --Fred

      2. Search Source Code of Stored Procedures

         Last Updated: 1/10/2006
         Applies to:  MS SQL Server 2000+

         Since the code of all MS SQL Server stored procedures is stored as data in the database, you can do queries against it like any other data.  For example, you can find all references to a given table in all stored procedures in the current database with the following query:

         	SELECT * 
         	FROM   syscomments c, 
         	       sysobjects o 
         	WHERE  o.id = c.id
         	AND    c.text LIKE '%table_name%'

         Thanks to Steve Weitzman for this tip!

         --Fred

   2. SQL Enterprise Manager Tips

      This section contains tips on the SQL Enterprise Manager tool. It is a graphical front end to the database, allowing you to create, delete, view, and modify all MS SQL Server objects: tables, views, stored procedures, etc.

      1. Keyboard Shortcuts

         Original Version:  6/20/1999
         Last Updated: 2/20/2006
         Applies to:  MS SQL Server 7.0+

         Here is a list of some of the more useful shortcut keys in SQL Enterprise Manager.

         Key	Function
         F1	Help on SQL Enterprise Manager
         Shift-F1	Help on syntax of current SQL statement
         F4	Search for objects by name, type, etc.
         Ctrl-E	Execute selected text in Query Analyzer
         Ctrl-R	Hide/show results pane in Query Analyzer
         Ctrl-Shift-C	Comment the currently selected text in Query Analyzer
         Ctrl-Shift-R	Uncomment
         Ctrl-Shift-L	Show the estimated query plan for the currently selected text.
         Ctrl-K	Hide/show "Show execution plan, as executed". (The query has to be run for the query plan to be returned to Query Analyzer.)

         Obviously, this list is far from complete.   Please feel free to mail me your favorite shortcuts.  I'll add to this list as time permits.

         Contributors so far include:  Darin Strait

         For a comprehensive list of shortcuts, search for "shortcut keys" in the SQL Server Books Online.

         See also: Windows Shortcut Keys

         --Fred

      2. SQL Generating SQL

         Last Updated: 2/7/1999
         Applies to:  MS SQL Server 6.5+

         To automate tedious database maintenance chores, you can use SQL statements to generate SQL statements that do your maintenance for you.  For example, to change the permissions on all stored procedures in a database, you can use a SELECT statement like:

         	SELECT 'GRANT EXECUTE ON ' + name + ' TO PUBLIC
                 	GO'
                 	FROM sysobjects
                 	WHERE type = 'P'

         The output of this SELECT statement is a series of alternating GRANT and GO statements, one pair per stored procedures, for all stored procedures in the database.  Then you copy that output as your next set of commands and execute it. 

         Note:  Be sure to leave the line break before the word GO. It is required to start on a new line, after the GRANT statement.

         Thanks to Steve Rhoads for this tip.

         --Fred

   3. See Also

      Original Version:  6/6/1999
      Last Updated: 2/20/2006
      Applies to:  MS SQL Server 6.5+

      The following are good sources of info about MS SQL Server:

      1. MS SQL Server books on the MSDN Library CD.
      2. MS SQL Server Books Online, installed with the client and server tools.

      Thanks to Darin Strait for mentioning the Books Online.

      --Fred

3. Differences Between Oracle and MS SQL Server

   1. Concepts and Terminology

      Original Version: 6/6/1999
      Last Updated: 2/20/2006
      Applies to:  Oracle 7.3+, MS SQL Server 6.5+

      The following table shows some differences in concepts and terminology between Oracle and MS SQL Server:

      Concept/Term	Oracle	MS SQL Server 6.5	MS SQL Server 7+
      Database engine (collection of databases)	Database	Database server	Instance
      Database (collection of tables)	Schema	Database
      Roles/Groups	Roles	Groups	Roles
      Database administrator account	dba	sa	Member of appropriate server or database role Also: sa (deprecated)
      Database owner	dba	dbo	Member of db_owner role
      Data about the database	Data Dictionary - one per server	Database Catalog - one per database "Master" database - one per server	INFORMATION_SCHEMA views
      Blocks and extents	Blocks and extents	Pages and extents
      Tablespaces	Tablespaces	Not supported	File Groups
      Network software	SQL*Net	Net-library
      Data stream protocol	Transparent Network Substrate (TNS)	Tabular Data Stream (TDS)
      Case sensitivity of names of tables, columns, etc.	Case-insensitive	Depends on character sort order, default is case-insensitive
      Synonyms	Supported	Not supported
      Readonly transaction	Supported	Not supported

      Thanks to Darin Strait for the MS SQL Server 7+ info.

      --Fred

   2. Data Types

      Last Updated: 6/6/1999
      Applies to:  Oracle 7.3+, MS SQL Server 6.5+

      The following table shows the corresponding data types in Oracle and MS SQL Server:

      Data Type	Oracle	MS SQL Server
      Fixed Length String	CHAR(n) - limit 2KB	CHAR(n), CHARACTER(n) - limit 255 (6.5) - limit 8KB (7.0)
      Variable Length String	VARCHAR2(n), VARCHAR(n) - limit 4KB in a column - limit 32KB in a variable - VARCHAR is obsolete	VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) - limit 255 (6.5) - limit 8KB (7.0)
      Integer	INTEGER, INTEGER(n), SMALLINT	INTEGER (4 bytes), INT (4 bytes), SMALLINT (2 bytes), TINYINT (1 byte), BIT (1 bit)
      Fixed Point	NUMBER, NUMBER(n), NUMBER(n,d), FLOAT, FLOAT(n), FLOAT(n,d)	NUMERIC, NUMERIC(n), NUMERIC(n,d), DECIMAL, DECIMAL(n), DECIMAL(n,d), DEC, DEC(n), DEC(n,d), MONEY, SMALLMONEY
      Floating Point	DECIMAL	FLOAT, FLOAT(n), DOUBLE PRECISION, REAL,
      Date	DATE	DATETIME, SMALLDATETIME, TIMESTAMP - TIMESTAMP auto-updated
      Binary	RAW(n) - limit 255 bytes	BINARY(n), VARBINARY(n), BINARY VARYING(n) - limit 255 (6.5) - limit 8KB (7.0)
      Large String	LONG, LONG VARCHAR - limit 2GB - limit one per table row CLOB - limit 4GB	TEXT - limit 2GB
      Large Binary	LONG RAW - limit 2GB - limit one per table row BLOB - limit 4GB	IMAGE - limit 2GB
      Multi-byte chars	NCHAR(n) NVARCHAR(n) NCLOB - same limits as CHAR, VARCHAR, CLOB	NCHAR(n), NATIONAL CHAR(n), NATIONAL CHARACTER(n) NVARCHAR(n), NATIONAL CHAR VARYING(n), NATIONAL CHARACTER VARYING(n) NTEXT, NATIONAL TEXT - same limits as CHAR, VARCHAR, TEXT
      OS File	BFILE	<not supported>
      Row Identifier	implicit ROWID column	(use an IDENTITY column)
      Secure OS Label	MLSLABEL, RAW MLSLABEL	<not supported>
      128-bit Unique Number (UUID, GUID)	<not supported>	UNIQUEIDENTIFIER (version 7.0 only)

      --Fred

   3. Limits

      Original Version: 6/7/1999
      Last Updated: 2/20/2006
      Applies to:  Oracle 7.3+, MS SQL Server 6.5+

      The following table shows differences in limits of Oracle and MS SQL Server:

      Description	Oracle	MS SQL Server 6.5	MS SQL Server 7+
      Columns per table	1000	250	1024
      Row size	Unlimited	1962 bytes	8060 bytes - Includes pointers, but not data, for TEXT and IMAGE columns - With 2000, some text can be stored in-row
      LONG and LONG RAW columns per row	1 (must be last column)	Unlimited (16-byte pointer per)
      LOB, TEXT, and IMAGE columns per row	Unlimited (16-byte pointer per)
      Clustered indexes per table	1
      Non-clustered indexes per table	unlimited	249
      Columns per index	16
      Index row size	2K bytes	900 bytes
      Identifier Length	30 chars		128 chars
      Tables per SELECT	unlimited	16	256
      Source code per stored procedure		64KB	250MB
      Data type limits	(see Data Types)

      For more info, search for "capacity" in the SQL Server Books Online.

      Thanks to Darin Strait for updating the MS SQL Server 7+ info.

      --Fred

   4. Operators

      Last Updated: 6/7/1999
      Applies to:  Oracle 7.3+, MS SQL Server 6.5+

      Most operators are the same in Oracle and MS SQL Server.  Here are some that differ:

      Description	Oracle	MS SQL Server
      String concatenation	string1 || string2	string1 + string2

      --Fred

   5. Built-In Functions

      Original Version: 6/7/1999
      Last Updated: 2/20/2006
      Applies to:  Oracle 7.3+, MS SQL Server 6.5+

      Oracle and MS SQL Server offer many of the same built-in functions.  For example, they both offer ABS, EXP, ROUND, UPPER, LOWER, AVG, COUNT, SUM, ASCII, etc.  The following table shows some of the corresponding functions that don't have the same name. For a more complete list, see "Migrating Oracle Applications to SQL Server"

      Description	Oracle	MS SQL Server
      Smallest integer >= n	CEIL	CEILING
      Modulus	MOD	%
      Truncate number	TRUNC	<none>
      Max or min number or string in list	GREATEST, LEAST	<none>
      Translate NULL to n	NVL	ISNULL As of 7.0, also: COALESCE
      Return NULL if two values are equal	DECODE As of Oracle 9i, also: NULLIF	NULLIF
      String concatenation	CONCAT(str1,str2)	str1 + str2
      Convert ASCII to char	CHR	CHAR
      Capitalize first letters of words	INITCAP	<none>
      Find string in string	INSTR	CHARINDEX
      Find pattern in string	INSTR	PATINDEX
      String length	LENGTH	DATALENGTH
      Pad string with blanks	LPAD, RPAD	<none> Workaround:  Use REPLICATE(' ', n) and string concatenation
      Trim leading or trailing chars other than blanks	LTRIM(str,chars), RTRIM(str,chars)	<none>
      Replace chars in string	REPLACE	STUFF, REPLACE
      Convert number to string	TO_CHAR	STR, CAST
      Convert string to number	TO_NUMBER	CAST
      Get substring from string	SUBSTR	SUBSTRING
      Char for char translation in string	TRANSLATE	<none>
      Date addition	ADD_MONTH or +	DATEADD or +
      Date subtraction	MONTHS_BETWEEN or -	DATEDIFF
      Last day of month	LAST_DAY	<none>
      Time zone conversion	NEW_TIME	<none>
      Next specified weekday after date	NEXT_DAY	<none>
      Convert date to string	TO_CHAR	DATENAME, CONVERT
      Convert string to date	TO_DATE	CAST
      Convert date to number	TO_NUMBER(TO_CHAR(d))	DATEPART
      Date round	ROUND	CONVERT
      Date truncate	TRUNC	CONVERT
      Current date	SYSDATE	GETDATE or CURRENT_TIMESTAMP
      Convert hex to binary	HEXTORAW	CAST
      Convert binary to hex	RAWTOHEX	CONVERT
      If statement in an expression	DECODE As of Oracle 9i, also: CASE ... WHEN or COALESCE	CASE ... WHEN or COALESCE
      User's login id number or name	UID, USER	SUSER_ID (deprecated), SUSER_NAME (deprecated), SUSER_SNAME (preferred)
      User's database id number or name	UID, USER	USER_ID, USER_NAME
      Current user	USER	USER

      Thanks to:

      • Jason Deacon for prompting me to update the Oracle column to reflect the new support for ANSI/ISO syntax.
      • Darin Strait for some MS SQL Server info.

      --Fred

   6. Differences in SQL Syntax

      Original Version:  6/7/1999
      Last Updated: 3/5/2006
      Applies to:  Oracle 7.3+, MS SQL Server 6.5+

      The following table shows the different syntax used in Oracle and MS SQL Server for the same SQL operations:

      Description	Oracle	MS SQL Server
      Left Outer Join	FROM table1 LEFT OUTER JOIN table2 ON (table1.column1 = table2.column2) Note: The following syntax is also supported, and was the only syntax prior to version 9i: WHERE column1 = column2(+) All rows from the right plus nulls to match additional rows from the left.	FROM table1 LEFT OUTER JOIN table2 ON table1.column1 = table2.column2 Note:  The following syntax is also supported, but is no longer recommended: WHERE column1 *= column2
      Right Outer Join	FROM table1 RIGHT OUTER JOIN table2 ON ( table1.column1 = table2.column2) Note: The following syntax is also supported, and was the only syntax prior to version 9i: WHERE column1(+) = column2 All rows from the left plus nulls to match additional rows from the right.	FROM table1 RIGHT OUTER JOIN table2 ON table1.column1 = table2.column2 Note:  The following syntax is also supported, but is no longer recommended: WHERE column1 =* column2
      Full Outer Join	As of Oracle 9i: FROM table1 FULL OUTER JOIN table2 ON (table1.column1 = table2.column2)	FROM table1 FULL OUTER JOIN table2 ON table1.column1 = table2.column2
      SELECT without FROM	SELECT 'hello world' FROM DUAL	SELECT 'hello world'
      SELECT data into a table	CREATE TABLE AS SELECT ...	SELECT ... INTO
      Intersection of 2 SELECTS	SELECT ... INTERSECT SELECT ...	SELECT ... WHERE EXISTS (SELECT ...)
      Subtraction of 2 SELECTS	SELECT ... MINUS SELECT ...	SELECT ... WHERE NOT EXISTS (SELECT ...)
      INSERT into a JOIN	INSERT INTO SELECT ...	Create a VIEW and INSERT INTO it.
      UPDATE data in a JOIN	UPDATE SELECT...	Create a VIEW and INSERT INTO it.
      UPDATE one table based on criteria in another table	UPDATE table1 t1   SET field1 =      (SELECT ...       FROM table2 t2      WHERE <condition involving both t1 and t2>) WHERE EXISTS      (SELECT ...       FROM table2      WHERE <condition involving both t1 and t2>)	UPDATE table FROM ...
      DELETE rows from one table based on criteria in another table	DELETE FROM table1 t1 WHERE EXISTS      (SELECT ...       FROM table2      WHERE <condition involving both t1 and t2>)	DELETE FROM table FROM ...
      DROP a column from a table	As of Oracle 8i: ALTER TABLE table_name DROP COLUMN column_name	ALTER TABLE table_name DROP COLUMN column_name
      Readonly VIEW	CREATE VIEW ... WITH READONLY	GRANT SELECT ...
      Save point	SAVEPOINT	SAVE TRANSACTION
      Table lock	LOCK TABLE...IN SHARE MODE	SELECT...table_name (TABLOCK)
      Exclusive table lock	LOCK TABLE...IN EXCLUSIVE MODE	SELECT...table_name (TABLOCKX)
      Reserving index space	PCTFREE=0	FILLFACTOR=100
      Declaring a local variable	DECLARE varname type;	DECLARE @varname type
      Initializing a local variable	DECLARE varname type := value;	<not supported>
      Declaring a constant	DECLARE varname CONSTANT type := value;	<not supported>
      Assigning to a variable	varname := value SELECT value INTO varname	SET @varname = value SELECT @varname = value
      Assigning to a variable from a cursor	FETCH cursorname INTO varname	FETCH NEXT FROM cursorname INTO varname
      Declaring a cursor	CURSOR curname (params) IS SELECT ...;	DECLARE curname CURSOR FOR SELECT ...
      If statement	IF ... THEN ELSIF ... THEN ELSE ENDIF	IF ... BEGIN ... END ELSE BEGIN ... END
      While loop	WHILE ... LOOP END LOOP	WHILE ... BEGIN ... END
      Other loops	FOR ... END LOOP LOOP ... END LOOP	<not supported>
      Loop exit	EXIT, EXIT WHEN	BREAK, CONTINUE
      Print output	DBMS_OUTPUT.PUT_LINE	PRINT
      Raise error	RAISE_APPLICATION_ERROR	RAISERROR
      Statement terminator	Semi-colon (;)	None required, but semi-colons are tolerated as of 2000.  GO is used to terminate a batch of statements.
      INSERT with some columns getting their default values	As of Oracle 9i:	As of SQL Server 2000 (perhaps 7):
      	INSERT INTO table1 VALUES (123, DEFAULT, 'a string', NULL)
      UPDATE resetting  some columns back to their default values	As of Oracle 9i:	As of SQL Server 2000 (perhaps 7):
      	UPDATE table1 SET col1 = 123, col2 = DEFAULT, col3 = 'a string', col4 = NULL

      Thanks to:

      • Tom Johnston for catching a mistake in this tip. I had the FROM DUAL in the wrong column.
      • Jason Deacon for prompting me to update the Oracle column to reflect the new support for ANSI/ISO syntax.
      • Darin Strait for some MS SQL Server info.
      • Tom Luskin for the technique of updating one table from another in Oracle via 2 correlated subqueries.

      --Fred

   7. Differences in SQL Semantics

      Original Version:  6/6/1999
      Last Updated: 2/20/2006
      Applies to:  Oracle 7.3+, MS SQL Server 6.5+

      The following table shows some semantic differences between Oracle and MS SQL Server:

      Description	Oracle	MS SQL Server
      Commit	Explicit COMMIT statement required	Automatic commit unless SET IMPLICIT_TRANSACTIONS ON
      Reading uncommitted data	Database does temporary internal  rollback to reconstruct most recently committed data for reader.	Depending on options, reader is allowed to read uncommitted data, or is forced to wait for writer to commit or rollback. As of 2005:  New functionality designed to mimic Oracle's writers-don't-block-readers consistency model.
      Releasing cursor data	CLOSE CURSOR releases all data.  You can't re-open.	CLOSE CURSOR does not release data.  You must explicitly call DEALLOCATE CURSOR.  Until then, you can re-open the cursor.
      Implicit data conversion in a statement like the following where vc is a column of type VARCHAR2: SELECT * FROM person WHERE vc =123	As each row is fetched from the table, an attempt is made to convert it to a number for the comparison with 123.  If any row contains a value that cannot be converted to a number, a runtime error occurs.	The number 123 is converted to the string '123' once, and then the data is fetched from the table.  If any row contains a value that cannot be converted to a number, it simply doesn't match '123' and is skipped without any error. Use the ISNUMERIC() function for more control, to search for expressions that do (or do not) evaluate to a valid numeric types.
      Conversion to NULL	Setting a VARCHAR2 column to '' (the empty string) makes it NULL.	Setting a VARCHAR column to '' makes it the empty string (not NULL). Note: With text columns, this usually means that an 8KB page is allocated to store the empty string. Setting the column to NULL releases that storage.
      Cursor performance		Typically, cursors are slower than joined select, insert, update or delete operations.

      Thanks to Darin Strait for some MS SQL Server info.

      --Fred

   8. Differences in Managing Databases

      Last Updated: 6/6/1999
      Applies to:  Oracle 7.3+, MS SQL Server 6.5+

      The following table shows some differences in how databases are managed in Oracle and MS SQL Server:

      Description	Oracle	MS SQL Server
      Model database	No model database	Newly created databases inherit characteristics (users, etc.) from the special database named "model".

      --Fred

   9. Differences in Managing Database Objects

      Original Version:  6/6/1999
      Last Updated: 3/18/2006
      Applies to:  Oracle 7.3+, MS SQL Server 6.5+

      The following table shows some differences in how database objects (tables, views, stored procedures, etc.) are managed in Oracle and MS SQL Server:

      Description	Oracle	MS SQL Server
      Fully qualified name	[schema.]table [schema.]view	[[[server.][database].][owner].]table [[[server.][database].][owner].]view
      Temp tables	Pre 8i:  Temporary tables must be deleted explicitly 8i+:  CREATE GLOBAL TEMPORARY TABLE	#table -- Any table named starting with a pound sign (#) is automatically dropped when the user logs off or the procedure ends. ##table -- Same as above, except that the table is accessible to other users and is dropped when the last user who referenced it logs off.
      Re-creating an object that may or may not already exist	CREATE OR REPLACE ... (Oracle chooses correctly which action to take.)	You have to choose which action to take.  If it doesn't already exist, use:      CREATE ... If is does exist, use:      ALTER ... or the sequence:      DROP ...      CREATE ...
      Create view before dependent tables	CREATE FORCE VIEW	Not supported.  Tables used by view must exist before view can be created.

      Thanks to Darin Strait for some clarifications.

      --Fred

   10. Differences in Managing Users

       Original Version: 6/6/1999
       Last Updated: 2/20/2006
       Applies to:  Oracle 7.3+, MS SQL Server 6.5+

       The following table shows some differences in how users are managed in Oracle and MS SQL Server:

       Description	Oracle	MS SQL Server 6.5	MS SQL Server 7+
       Membership in groups	Each user can be a member of any number of groups.	Each user can be a member of only one group other than "public".	Each user can be a member of any number of roles.

       Thanks to Darin Strait for the MS SQL Server 7+ info.

       --Fred

   11. Differences in Integration with MS ADO, RDO, etc.

       Original Version:  6/7/1999
       Last Updated: 1/14/2006
       Applies to:  Oracle 7.3+, MS SQL Server 6.5+

       The following table shows the different techniques used in Oracle and MS SQL Server to interact with MS ADO, RDO, etc.:

       Description	Oracle	MS SQL Server
       Return a recordset to the caller	Return a handle to a cursor. For more info:  See MS KB article Q174679.  Can return multiple recordsets as multiple output parameters.	SELECT with no INTO clause; Multiple such SELECTs return multiple recordsets

       --Fred

   12. Miscellaneous Differences

       Original Version: 6/6/1999
       Last Updated: 2/20/2006
       Applies to:  Oracle 7.3+, MS SQL Server 6.5+

       The following table shows miscellaneous differences between Oracle and MS SQL Server:

       Description	Oracle	MS SQL Server 6.5	MS SQL Server 7+
       Generate unique numbers	CREATE SEQUENCE	IDENTITY column of a table
       Cascaded DELETE	DELETE CASCADE ...	(use triggers)	DELETE CASCADE ...
       Call a user-defined function from a SQL statement (as column of SELECT or expression in WHERE clause)	Supported	Not supported	Supported

       Thanks to Darin Strait for the MS SQL Server 7+ info.

       --Fred

   13. See Also

       Original Version:  6/7/1999
       Last Updated: 3/3/2001
       Applies to:  Oracle 7.3+, MS SQL Server 6.5+

       The following are good sources of info about differences between Oracle and MS SQL Server:

       1. Bowman, Judith S., Sandra L. Emerson, and Marcy Darnovsky. The Practical SQL Handbook. Addison-Wesley Publishing Company, 1993.  ISBN 0-201-62623-3.
          This book gives a good introduction to SQL, with a slight emphasis on Sybase, but with a useful summary in the back of the syntax for each of the SQL statements (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REVOKE, etc.) for each of the major databases (Oracle, Sybase, DB2, Informix, Ingres, etc.)  The book pre-dates MS SQL Server, but the Sybase info is a good approximation since MS SQL Server is a derivative of Sybase.
          
       2. "Migrating Oracle Applications to SQL Server" on MSDN CD, and at MS TechNet Web site:
          http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx
          Microsoft clearly intended this to be used in one direction only, but I've used it quite successfully to translate my SQL Server knowledge to Oracle as well.

       --Fred

发表评论

IT瘾于2006年10月20日 下午02时19分46秒发布 #