Bristle Software SQL Tips

This page is offered as a service of Bristle Software, Inc.  New tips are sent to an associated mailing list when they are posted here.  Please send comments, corrections, any tips you'd like to contribute, or requests to be added to the mailing list, to tips@bristle.com.

Table of Contents:

  1. MySQL Tips
    1. SQL Tips
      1. Top-N query
      2. Pagination query
    2. See Also
  2. Oracle Tips
    1. SQL Tips
      1. Top-N query
      2. Pagination query
      3. Efficiency of Top-N and pagination queries
      4. SELECT * and more
      5. Materialized View
      6. Search Source Code of Stored Procedures
    2. PL/SQL Tips
    3. SQL Navigator Tips
    4. See Also
  3. MS SQL Server Tips
    1. SQL Tips
      1. Top-N query
      2. Pagination query
      3. Dynamic SQL in a Stored Procedure
      4. Search Source Code of Stored Procedures
    2. SQL Enterprise Manager Tips
      1. Keyboard Shortcuts
      2. SQL Generating SQL
    3. See Also
  4. General Tips
    1. Efficiency of Top-N and pagination queries
    2. Accuracy of pagination queries
  5. Portability Tips
    1. Portable SQL syntax
    2. Top-N query
  6. Differences Between Oracle and MS SQL Server
    1. Concepts and Terminology
    2. Data Types
    3. Limits
    4. Operators
    5. Built-In Functions
    6. Differences in SQL Syntax
    7. Differences in SQL Semantics
    8. Differences in Managing Databases
    9. Differences in Managing Database Objects
    10. Differences in Managing Users
    11. Differences in Integration with MS ADO, RDO, etc.
    12. Miscellaneous Differences
    13. See Also

Details of Tips:

  1. MySQL Tips

    1. SQL Tips

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

      1. Top-N query

        Original Version:  2/20/2008
        Last Updated: 2/20/2008
        Applies to:  MySQL 3+ (maybe even earlier)

        To limit the number of rows returned by a query, use the clause:

            LIMIT count

        For example:

            SELECT *
            FROM employee
            ORDER BY employee_id
            LIMIT 10;

        This can be much faster than querying all of the rows, not only because it sends less data back to the client (perhaps across a slow transmission line), but also because the query itself runs more quickly inside the database, using smaller internal data structures for sorting, etc.

        Unlike other databases (Oracle, Microsoft, etc.), in MySQL this "just works".  There is no need for nested subqueries to ensure the correct order of operations, no need for "hints" to reduce the size of internal data structures, etc.  Logically, it selects the specified rows, then sorts by the specified criteria, then finally limits the number of rows returned.  Internally however, it makes some optimizations since it can discard rows that won't be returned.  For example, while sorting, a LIMIT 10 clause allows it to avoid actually sorting all rows.  Instead, it only keeps the 10 best at each step in the sort, and discards the rest unsorted.

        For more details of speed and efficiency, see:
                Efficiency of Top-N and pagination queries

        Thanks to Tom Stluka for contributing to this tip!

        --Fred

      2. Pagination query

        Original Version:  2/20/2008
        Last Updated: 2/20/2008
        Applies to:  MySQL 3+ (maybe even earlier)

        To page through a large data set, returning a specified number of rows at a time, use the clause:

            LIMIT offset, count

        For example:

            SELECT *
            FROM employee
            ORDER BY employee_id
            LIMIT 50, 10;

        This skips rows until it reaches the specified offset (50), then returns the specified number of rows (10).  The offset is 0-based, so OFFSET 0 skips 0 rows and starts at the 1st row, while OFFSET 1 skips 1 row and starts at the 2nd row.

        As of MySQL 5 (or perhaps even MySQL 4 -- I'm not sure), an additional syntax was added as an option for better clarity.  (Note that the order of count and offset are reversed from the previous syntax.)

            LIMIT count OFFSET offset

        For example:

            SELECT *
            FROM employee
            ORDER BY employee_id
            LIMIT 10 OFFSET 50;

        See the previous tip: Top-N query for additional details of how and why this can make your queries faster, and for assurances about not needing nested subqueries to force it to select, sort, and then limit the number of returned rows.

        For more details of speed and efficiency, see:
                Efficiency of Top-N and pagination queries

        For issues about paging accurately, see:
                Accuracy of pagination queries

        Thanks to Tom Stluka for contributing to this tip!

        --Fred

    2. See Also

      Original Version:  2/20/2008
      Last Updated: 2/20/2008
      Applies to:  MySQL 3+

      The following are good sources of info about MySQL:

      1. The MySQL Reference Manual, included in the free download of the software.
      2. The MySQL links at my links page:
                http://bristle.com/~fred/#mysql_db

      --Fred

  2. Oracle Tips

    1. SQL Tips

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

      1. Top-N query

        Original Version:  1/24/2008
        Last Updated: 2/26/2008
        Applies to:  Oracle 7+ (maybe even earlier)

        To limit the number of rows returned by a query, use the clause:

            WHERE ROWNUM <= count

        Warning:  If you use an ORDER BY clause, as:

            SELECT *
            FROM employee
            WHERE ROWNUM <= 10
            ORDER BY employee_id;

        you may not get the rows you expected because, by default, it selects the first 10 rows in the database and sorts them, rather than sorting all rows and returning the first 10 of the sorted order.  Instead, you must apply the ROWNUM test to an outer SELECT that occurs after the sorting, as:

            SELECT * 
            FROM (SELECT * 
                  FROM employee
                  ORDER BY employee_id)
            WHERE ROWNUM <= 10;

        This can be much faster than querying all of the rows, not only because it sends less data back to the client (perhaps across a slow transmission line), but also because the query itself can be made to run more quickly inside the database, using smaller internal data structures for sorting, discarding irrelevant rows earlier, etc.  For details, see:
                General efficiency of Top-N and pagination queries
                Oracle efficiency of Top-N and pagination queries

        For complete gory details, see:
                http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html 

        Thanks to Tom Luskin and Rob Baptista for contributing to this tip!

        --Fred

      2. Pagination query

        Original Version:  1/24/2008
        Last Updated: 2/20/2008
        Applies to:  Oracle 7+ (maybe even earlier)

        Based on the Top-N query tip above, you might assume that you can page through a large data set, returning a specified number of rows at a time, using multiple ROWNUM clauses as:

            SELECT *
            FROM (SELECT *
                  FROM employee
                  ORDER BY employee_id)
            WHERE ROWNUM >= 6
            AND ROWNUM <= 10;

        However, that doesn't work because of the details of how ROWNUM is computed.  It is a counter that only gets incremented after each row is selected.  It starts at 1, which is never >= 6, so no row ever gets selected, so it never gets incremented to 2.  No rows are returned.

        Instead, you have to do the following to get ROWNUM calculated after the sort, for rows 1-10, and then filter out rows that are not >= 6:

            SELECT *
            FROM (SELECT e.*
                        ,ROWNUM rnum
                  FROM (SELECT *
                        FROM employee
                        ORDER BY employee_id) e
                  WHERE ROWNUM <= 10)
            WHERE rnum >= 6;   -- Note: rnum, not ROWNUM

        For speed and efficiency concerns, see:
                General efficiency of Top-N and pagination queries
                Oracle efficiency of Top-N and pagination queries

        For complete gory details, see:
                http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

        For issues about paging accurately, see:
                Accuracy of pagination queries

        Thanks to Tom Luskin and Rob Baptista for contributing to this tip!

        --Fred

      3. Efficiency of Top-N and pagination queries

        Original Version:  1/24/2008
        Last Updated: 2/26/2008
        Applies to:  Oracle 7+ (maybe even earlier)

        As described in General efficiency of Top-N and pagination queries, for very large tables (millions of rows or more), it can be pretty slow to page through the rows using a new query for each page of rows, or even to retrieve the first page of rows.  You may need to tune your database to get acceptable performance, adding indexes, optimizer "hints", etc.

        For Oracle, the Top-N and pagination queries may run much faster if you use the FIRST_ROWS(n) hint, as:

            SELECT * 
            FROM (SELECT /*+ FIRST_ROWS(10) */
                         * 
                  FROM employee
                  ORDER BY employee_id)
            WHERE ROWNUM <= 10;

        and:

            SELECT *
            FROM (SELECT /*+ FIRST_ROWS(10) */
                         e.*
                        ,ROWNUM rnum
                  FROM (SELECT *
                        FROM employee
                        ORDER BY employee_id) e
                  WHERE ROWNUM <= 10)
            WHERE rnum >= 6;   -- Note: rnum, not ROWNUM

        This hint allows the database to avoid fully sorting all rows.  Instead, it only keeps the 10 best at each step in the sort, and discards the rest unsorted.

        Thanks to Tom Luskin and Rob Baptista for contributing to this tip!

        --Fred

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

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

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

      Original Version: 6/6/1999
      Last Updated: 2/20/2008
      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.
      3. The Oracle links at my links page:
                http://bristle.com/~fred/#oracle_db

      --Fred

  3. MS SQL Server Tips

    1. SQL Tips

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

      1. Top-N query

        Original Version:  1/24/2008
        Last Updated: 1/30/2011
        Applies to:  MS SQL Server 2005+

        To limit the number of rows returned by a query, use the syntax:

            SELECT TOP n FROM

        Also, as of MS SQL Server 2005, there is a new Oracle-like ROW_NUMBER() function:

            ROW_NUMBER() OVER (ORDER BY ...)

        Since ROW_NUMBER() was added so many years after Oracle's ROWNUM clause, and even after MySQL's LIMIT clause, and since it requires you to specify the ORDER BY clause as part of its syntax, you might expect it to do a sub-select internally like MySQL does to get around the issues with Oracle's ROWNUM (see Oracle Top-N query).  However, for some reason, it does not.  The following:

            SELECT e.*
                  ,ROW_NUMBER() OVER (ORDER BY employee_id) AS rnum
            FROM employee e
            WHERE rnum <= 10;

        selects the first 10 rows in the database and sorts them, rather than sorting all rows and returning the first 10 of the sorted order.  To get the results you wanted, you must use the Oracle technique of applying the ROW_NUMBER() test to an outer SELECT that occurs after the sorting, as:

            SELECT *
            FROM (SELECT e.*
                        ,ROW_NUMBER() OVER (ORDER BY employee_id) AS rnum
                  FROM employee e)
            WHERE rnum <= 10;

        This can be much faster than querying all of the rows, because it sends less data back to the client (perhaps across a slow transmission line).  Like Oracle and MySQL, it may also be possible to make it run more quickly inside the database, using smaller internal data structures for sorting, discarding irrelevant rows earlier, etc.  Anyone know if it does so automatically, or how to cause it to?

        For speed and efficiency concerns, see:
                Efficiency of Top-N and pagination queries

        For complete gory details, see:
                http://www.asp.net/learn/data-access/tutorial-25-vb.aspx
                http://msdn2.microsoft.com/en-us/library/ms186734.aspx

        Thanks to Steve Weitzman, Rob Baptista, and Darin Strait for contributing to this tip!

        1/30/2011 Update: In MS SQL Server 2011, there's a new syntax.  See:
                http://www.sqlservernation.com/home/paging-features-in-sql-server-and-postgresql.html

        Thanks to Steve Weitzman for the update!

        --Fred

      2. Pagination query

        Original Version:  1/24/2008
        Last Updated: 2/20/2008
        Applies to:  MS SQL Server 2005+

        As you may have guessed, once you've mastered the quirks of the ROW_NUMBER() clause (see MS SQL Server Top-N query), you can use the same syntax to page through a large data set, returning a specified number of rows at a time:

            SELECT *
            FROM (SELECT e.*
                        ,ROW_NUMBER() OVER (ORDER BY employee_id) AS rnum
                  FROM employee e)
            WHERE rnum <= 10
            AND   rnum >= 6;

        For speed and efficiency concerns, see:
                Efficiency of Top-N and pagination queries

        For complete gory details, see:
                http://www.asp.net/learn/data-access/tutorial-25-vb.aspx
                http://msdn2.microsoft.com/en-us/library/ms186734.aspx

        For issues about paging accurately, see:
                Accuracy of pagination queries

        Thanks to Steve Weitzman, Rob Baptista, and Darin Strait for contributing to this tip!

        --Fred

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

      4. 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.
      3. The MS SQL Server links at my links page:
                http://bristle.com/~fred/#microsoft_db

      Thanks to Darin Strait for mentioning the Books Online.

      --Fred

  4. General Tips

    1. Efficiency of Top-N and pagination queries 

      Original Version:  2/20/2008
      Last Updated: 2/26/2008

      For very large tables (millions of rows or more), it can be pretty slow to retrieve the first few rows based on some sort criteria as described in:

              MySQL Top-N query
              Oracle Top-N query
              Microsoft Top-N query

      because some databases may internally do a complete sort of all selected rows in order to determine which are the first few.

      Also, it can be pretty slow to page through the rows using a new query for each page of rows as described in:

              MySQL pagination query
              Oracle pagination query
              Microsoft pagination query

      Remember that the database is not holding open a single resultset created by a single query, and just returning the next page of rows.  Instead, it is executing a new query for each page.

      Presumably, you are doing this for the sake of an interactive user, to avoid overwhelming him, or his Web browser or other user interface, or his communication line, with too much data.  Therefore, you only have to be fast enough to satisfy an interactive user.  Less than a second or so is probably fast enough.  If you were doing a massive batch operation on millions of rows, and needed much faster speeds, you would not have limited the query to one page at a time.  You would have simply done one large query and iterated over the rows.  

      However, you must still be fast enough to satisfy the user, and must also avoid consuming massive resources in the database server.  For these reasons, you may need to tune your database to get acceptable performance, adding indexes, optimizer "hints", etc.

      Thanks to Art Graham and Rob Baptista for contributing to this tip!

      --Fred

    2. Accuracy of pagination queries

      Original Version:  2/20/2008
      Last Updated: 2/20/2008

      If the contents of the DB are changing significantly during the pagination, a simple pagination query like:

              MySQL pagination query
              Oracle pagination query
              Microsoft pagination query

      may not work reliably.  Inserted rows can be skipped or can cause subsequent rows to be repeated.  Deleted rows can cause subsequent rows to be skipped.  Fortunately, for many applications, it doesn't matter much if you skip or repeat a row while paging through a long constantly changing list.  However, when it does matter, you can avoid such problems by doing any of the following:

      1. Lock the rows against update during the query.  Yuck!

      2. Copy the rows to a temporary table before paging.  Yuck!

      3. Select by a combination of row number and the sorted value that was displayed at the end of the previous page.  Thus, you can resume at an appropriate place based on the changing table, but still get only the next n rows.  If you do this, be careful about:

        1. Relying on a specific record continuing to exist.
          Don't rely on a specific record as your marker for where to resume.  Be sure to test with "<", "<=", ">" or ">=", not "=" or "!=".  You don't want to get tripped up by boundary conditions if someone deletes or updates that one record meanwhile.

        2. Sorting by highly duplicated values.
          If there are duplicate values in your sort column(s), you may need a tie breaker to distinguish among them and resume paging correctly after showing some of them, especially when there are more than a full page of duplicates.  You could use row number as the tie breaker, but the order of the duplicate rows may change.  It is better to specify more sort columns, to eliminate the duplicates.

      Thanks to Art Graham for contributing to this tip!

      --Fred

  5. Portability Tips

    1. Portable SQL syntax

      Original Version:  12/19/2008
      Last Updated: 12/19/2008
      Applies to:  All databases

      Whenever possible, you should limit your SQL to standard portable syntax, as defined by the SQL-92 standard that is now implemented by most databases.

      For links to a good SQL tutorial that focuses on portable syntax, and to the SQL-92 standard document itself, see:

              http://bristle.com/~fred/#portable_sql

      Thanks to Kevin Evans for telling me about the tutorial page!

      --Fred

    2. Top-N query

      Original Version:  2/20/2008
      Last Updated: 2/20/2008
      Applies to:  JDBC 1.0+

      To limit the number of rows returned by a query, in a portable way, avoiding all of the different syntaxes and quirks described in:

              MySQL Top-N query
              Oracle Top-N query
              Microsoft Top-N query

      use the JDBC/ODBC statement:

          stmtActiveStatement1.setMaxRows(n);

      where 0 means no limit.  Let the JDBC/ODBC drivers from the different database vendors deal with the differences among the databases.  

      However, if your resultset is large enough for efficiency to be a concern, beware of efficiency issues.  Presumably, the database vendor uses the best possible algorithm for its own database in the driver it provides.  However, that is not always the case.  If the driver you are using does things inefficiently (like loading all rows of a huge resultset before discarding those beyond the first n, or sorting an entire huge resultset completely before discarding those beyond the first n), find a better driver or revert to the database-specific techniques.

      Thanks to Art Graham for contributing to this tip!

      --Fred

  6. 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
      Fully Reviewed: 2/20/2006
      Last Updated: 6/9/2009
      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
      Parse date into parts (year, month, day, hour, min, sec, etc.) TO_CHAR(date, format_string)
      Examples:
      TO_CHAR(SYSDATE, 'yyyy')
      TO_CHAR(SYSDATE, 'ss')
      etc.

      Also, as of Oracle 9.2:
      EXTRACT (date_part FROM date)
      Examples:
      EXTRACT(YEAR FROM SYSDATE)
      EXTRACT(SECOND FROM SYSDATE)
      etc.
      DATEPART(date_part, date)
      Examples:
      DATEPART(YEAR, GETDATE())
      DATEPART(SECOND, GETDATE())
      etc.

      Also:
      YEAR(), DAY(), MONTH()
      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.
      • Tor Helland for some date parsing functions.

      --Fred

    6. Differences in SQL Syntax

      Original Version:  6/7/1999
      Last Updated: 1/24/2008
      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
      Limit number of rows returned WHERE ROWNUM <= n

      Warning!  See also:
      1. Oracle Top-N query
      2. Oracle Pagination query
      SELECT TOP n FROM

      Warning!  See also:
      1. Microsoft Top-N query
      2. Microsoft Pagination query

      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.
      • Tom Luskin for the technique of limiting the number of rows returned.

      --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.
      3. http://en.wikibooks.org/wiki/SQL_dialects_reference

      --Fred

©Copyright 1999-2014, Bristle Software, Inc.  All rights reserved