How I may help
LinkedIn Profile Email me!
Call me using Skype client on your machine

Reload this page SQL Coding: Microsoft T-SQL vs. Oracle PL/SQL

Here is a comparison of SQL usage and coding for the three most popular database products: Microsoft's T-SQL, Oracle's PL/SQL, and mySQL.

Related Topics:

  • MS-SQL AdventureWorks
  • Oracle Architecture
  • Benchmark Apps
  • Performance/Tuning
  • Data Management
  • Data Warehousing
  • Data Types Compared
  • Free Training!
  • Tech Support

    Topics this page:

  • Limitations
  • ANSI
  • Sample DBs
  • Execution Env.
  • Batch Env.
  • SQL Commands
  • Global Variables
  • Versions
  • Formatting
  • Data Definitions
  • Integrity Contraints
  • Default Objects
  • Indexes

  • Frag Stats
  • Query Joins
  • Views
  • Logic Control
  • Conventions
  • Cursors
  • Triggers
  • Alerts
  • Aggregate Functions
  • User Defined Funcs
  • Debugging
  • MS & Oracle Resources
  • Certifications
  • Your comments???

    Site Map List all pages on this site 
    About this site About this site 
    Go to first topic Go to Bottom of this page

    Screen: Max1024800

    wav sound Sound: Match found

    Set this at top of window. SQL Limitation Factoids - Numbers to Remember

      Maximum...MS-SQL Oracle
      Rows per Table - unlimited
      Database size (2005) 32 terabytes
      (2008) 524,272 TB
      Log file size 4 TB -
      BLOB, CLOB, NCLOB, BFILE bytes - 4GB
      Page extent contiguous size 64 KB (8 pages) -
      size of SQL Procedure - 64KB
      Page block size 8 KB (8,192) =
      header: 96 bytes
      body: 8096 bytes
      bytes per row 8,060 (9i) 2000
      (10g) 4000
      char type bytes 8,000 255
      Pages tracked by each PFS
      (Page Free Space) page
      8,000 -
      UDT size (2005) 8,000
      (2008) unlimited
      ROWID / UROWID bytes
      n/a 4,000
      int type value 2.147 billion (2^31) -
      Tables per Database 2 billion -
      Objects open per Database 2,147,483,647 -
      Users and roles in database - 2,147,483,638
      LONG and Long Raw type bytes - 2 billion
      smallmoney type value $214,748.0000 -
      Money decimal precision 4 (.4444) -
      Files per Database - 65,533
      smallint type value
      User Connections
      databases per server instance
      32,767 -
      Files per database 32,767 -
      Filegroups per database 256 -
      Parameters per stored procedure
      or UDF (user defined function)
      2,100 -
      Files per physical Oracle
      Tablespace/MS-SQL Filegroup
      - 1,022 (OS dep.)
      Columns per SELECT statement 4,096 -
      Columns per INSERT statement 1,024 -
      Columns per non-wide base table
      Columns per (2008) wide table
      Columns in composite index.
      primary/foreign key
      16 32
      Tables per SELECT statement 256 -
      Non-clustered indexes per table (2005) 249
      (2008) 999
      Characters in Table / Column Name 30 30
      Index column key size bytes 900 -
      Levels for nesting in stored procedures, subqueries, trigger levels 32 -
      Clustered indexes per table 1 -


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. ANSI Standard Conformance

      Microsoft's T-SQL is compliant with just the Entry Level of the SQL-92 ANSI (American National Standards Institute) standard, and not all features of the Intermediate and Full Levels of SQL-92.

      Stored procedures are not part of the ANSI SQL standard.

      Set this at top of window. SQL Commands

      SQL commands are classified in these groups:

      • DDL (Data Definition Language) commands change metadata of tables containing data. These commands (CREATE, ALTER, TRUNCATE, DROP) require a COMMIT. PL/SQL does not support ANSI-SQL's data definition commands.
      • DML (Data Manipulation Language) commands (SELECT, INSERT, UPDATE, DELETE, MERGE) change data values in tables under transaction control. These commands can be undone.
      • DCL (Data Control Language) commands (GRANT, ALTER, REVOKE) control permissions to access data.

      Other Oracle SQL commands:
      SET ROLE

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. Interactive Execution Environments - Oracle vs. Microsoft

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

      Action Oracle SQL Microsoft SQL

      Language Name

      PL/SQL - Procedural Language Transact-SQL

      Tool for interactive input, store, and running of stored procedures

      SQLPLUSW userid/

      C:\Windows\system32\SQLServerManager.msc for 2005/2008 SQL Server Management Studio's SQL Editor


      isqlw for SQL Query Analyzer

      Batch invocation - 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

      Read product documentation

      - For Books online: Within SQLW, Help toolbar -> Building SQL Server Applications -> Transact-SQL Reference -> System Stored Procedures (T-SQL)

      Return to the OS

      exit -

      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 -

      View properties about an object

      select * from all_views sp_help 'object_name'
      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 */
      -- double hyphen or
      IF 1 = 0 -- never true
          PRINT 'Line 1';
          PRINT 'Line 2';

      To execute a procedure in the buffer

      @ (ampersand)

      To execute SQL script in the buffer

      . dot
      / (slash)
      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.

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. Batch Execution

      SQLCMD Win32 utility from Microsoft enables SQL to be executed in batch mode such that it can be applied across several SQL servers in a single execution. The version available for SQL Server 2005 is in C:\Program Files\Microsoft SQL\90\Tools\Binn, so this should be in your PATH Environment Variable.

      The GO command is needed at the end of each statement.

      Output Space Used to a CSV file: MS-SQL

      This procedure from Nigel creates a stored procedure s_SpaceUsed which outputs a file containing space used.

      if exists (select * from sysobjects 
      	where id = object_id(N'[dbo].[s_SpaceUsed]') 
      	and OBJECTPROPERTY(id, N'IsProcedure') = 1)
      drop procedure [dbo].[s_SpaceUsed]
      Create procedure s_SpaceUsed
      @SourceDB	varchar(128)
      exec s_SpaceUsed 'AdventureWorks2008'
      set nocount on
      declare @sql varchar(128)
      	create table #tables(name varchar(128))
      	select @sql = 'insert #tables select TABLE_NAME from ' 
      	where TABLE_TYPE = ''BASE TABLE'''
      	exec (@sql)
      	create table #SpaceUsed (name varchar(128), rows varchar(11), 
      		reserved varchar(18), data varchar(18), index_size varchar(18), 
      		unused varchar(18))
      	declare @name varchar(128)
      	select @name = ''
      	while exists (select * from #tables where name > @name)
      		select @name = min(name) from #tables where name > @name
      		select @sql = 'exec ' + @SourceDB 
      			+ '..sp_executesql N''insert #SpaceUsed exec sp_spaceused ' 
      			+ @name + ''''
      		exec (@sql)
      	select * from #SpaceUsed
      	drop table #tables
      	drop table #SpaceUsed

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. SQL Transactions

      With T-SQL, each command is treated as a transaction unless specified.

      SELECT @@TRANCOUNT returns 0 if you're not in an open transaction, and a value greater than 0 if you are.

      Oracle SQL commands:

      MS-SQL specific commands:

      Raise errors of different severity levels:

      MS T-SQL processes SELECT statements are logically processed in this order (regardless of the sequence they are coded):

      1. FROM table
      2. WHERE filter
      3. GROUP BY
      4. HAVING group values
      5. SELECT (return) columns or variables
      6. ORDER BY (sort by) columns


      SELECT * FROM SchemaOwner.TableName   tuple ;

      For a list of current users and processes within MS-SQL, use sp_who system stored procedure.
      For more detailed information, use this undocumented stored procedure:
      sp_who2 [[@login_name =] 'login']


      To mark a stored procedure (myproc1) in the master db to start automatically when MS-SQL Server starts:

        USE master
        EXEC sp_procoption 'myproc1', 'startup', 'true'


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. Read-Only Global Environment Variables

      Value Oracle SQL Microsoft SQL
      - - select @@Connections;
      select @@Max_Connections;
      Session Id of each connection in the current user process - select @@SPID;
      select @@ProcID;
      % CPU Busy & Idle - select @@CPU_Busy;
      select @@Idle;
      % IO Busy - select @@IO_Busy;
      Number of Rows in most current statement. - select @@Cursor_Rows;
      The first day of the the week (1 to 7) specified by SET DATEFIRST. - select @@DateFirst;
      timestamp (datatype varbinary) provides the
      last-used rowversion value of the current database.
      A new rowversion value is generated when a
      row with a rowversion column is inserted or updated.
      - select @@DBTS;
      The error number of previous SQL command (used instead of TRY/CATCH) - select @@Error;
      select @@Fetch_Status;
      - - select @@Total_Errors;
      Count of reads and writes - select @@Total_Read;
      select @@Total_Write;
      Unlike SCOPE_IDENTITY(), not scope-specific way to
      get a unique id to the last auto-incremented record added among all tables during a session.
      - select @@Identity;
      Language Identifier - select @@LangID;
      select @@Language;
      - - select @@Lock_TimeOut;
      - - select @@Max_Precision;
      select @@TimeTicks;
      - - select @@Pack_Received;
      select @@Pack_Sent;
      - - select @@RemServer;
      select @@ServerName;
      select @@ServiceName;
      - - select @@TextSize;

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. Versions

      select @@VERSION returns a value such as

        9.00.1399.06 for v2005.

      SELECT @@microsoftversion / 0x01000000 yields:

        6 for sql v6.0
        7 for sql 7
        8 for sql 2000 [code named Shiloh (32-bit), Liberty (64-bit)]
        9 for sql 2005 [code named Yukon]
        10 for sql 2008 [code named Katmai (mountain in Alaska) / Akadia]
        11 for sql 2010 [code named Killimanjaro (mountain in Keyna, Afica)]

      SELECT @@microsoftversion & 0xffff gives the build number integer.

      Within a .NET VB program, to use SQL Management Objects (SMO) DLLs introduced with Microsoft SQL Server 2005 folder C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies directory:


        Use this code:

        using Microsoft.SqlServer.Management.Common; 
        using Microsoft.SqlServer.Management.Smo;
        Server theServer = new Server("myServerName"); 

      To get Oracle's version (such as "" or "") :

        SQL> select instance, version, hostname, status, database_status,
        to_char(startup_time,'DD-MON-YYYY HH:MI:SS') strtd AS 'STARTED'
        from v$instance;

      At a Unix command prompt:

        % $ORACLE_HOME/bin/svrmgrl command=exit | grep "Release"

      Oracle 9i introduced a package:


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. Bind Variables (temporary holding areas)

      To define a bind variable in Oracle's SQL*Plus:


      To print a bind variable in SQL*Plus:

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

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

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. Loading Data Into Databases

      There are several ways to get data into a SQL Server system.

      To restore a .bak (backup) file, first do a FILELISTONLY to get LogicalName of files in the MOVE statement:

        FROM DISK = 'C:\projects\Ashim\db01.bak'
        FROM DISK = 'C:\projects\Ashim\db01.bak'
        	MOVE 'sample_Data' 	TO 'C:\projects\Ashim\db01.mdf',
        	MOVE 'sample_Log' 	TO 'C:\projects\Ashim\db01.LDF'

      To attach a trusted .mdf and .ldf file, instead of the less secure sp_attach_db procedure using this T-SQL:

        ON 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PetShop.mdf'
        FOR ATTACH

        The "MSSQL10.MSSQLSERVER" is the instance name.

      To import tab-delimited csv files, the AdventureWorks sample database for Microsoft SQL Server 2005/2008another page on this site use this BULK INSERT code which runs within the SQL Server process (instead of BCP, which creates its own process):

        PRINT 'Loading [Production].[WorkOrder]';
        BULK INSERT [Production].[WorkOrder]
        FROM '$(SqlSamplesSourceDataPath)AdventureWorks 2008 OLTP\WorkOrder.csv'
        WITH (

      • TABLOCK avoids row-at-a-time locking, which eats up time.
      • ROWS PER BATCH = 2500 imports multiple streams into one table while avoiding lock escalation.

      SQL 2008 enhanced the VALUES clause for multiple rows:

        INSERT INTO dbo.Orders (orderid, orderdate, empid, custid)
        	  (10003, '20090213', 4, 'B'),
        	  (10004, '20090214', 1, 'A'),
        	  (10005, '20090213', 1, 'C'),
        	  (10006, '20090215', 3, 'C');

      $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:

        user = DBUSERNAME
        password = DBPASSWORD
        host = DBSERVER
        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

      robocopy (a contraction of Robust File Copy), is a command-line directory replication command to mirror directory trees inclusive of all NTFS attributes and properties. It is more reliable because it includes auto-restart in case of network connection disruption. It was once in the Windows Resource Kit but since has become a standard feature of Windows Vista and Windows Server 2008.

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. 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' )

        In T-SQL, the COALESCE function below is often used to set the current database to work with if a database name is not specified in the input parameter:

          SET @dbname = COALESCE(@dbnameParam, DB_NAME())
          SELECT	@dbname AS [Database Name], ...

        In Oracle, 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

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. Data Definitions

      Action Oracle SQL Microsoft SQL

      Format US Social Security (9 digit) Number

      - SELECT STUFF(STUFF('123456789',4,0,'-'),7,0,'-')
      Convert a small number of fixed values decode( weekday, 1,'Sun', 2,'Mon', 3,'Tue', 4,'Wed', 5,'Thu', 6,'Fri', 7,'Sun', '???') -

      Datatypes and Value Assignment

      skalar char, number, long, date, and varchar2 as in
      price := qty*(cost*2.5);
      v_valid_order boolean not null := true;

      Set date format just for current session

      alter session set nls_date_format = 'YYYY-MON-DD-HH24:MI:ss' SET DATEFORMAT
      Identify date of table - SELECT crdate FROM sysobjects WHERE name = 'mytable'

      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
      ( EmployeeID INT PRIMARY KEY,
      EmployeeSSN typeSSN,
        ( EmployeeSSN LIKE '[0-9][0-9][0-9]—[0-9][0-9]—[0-9][0-9][0-9][0-9]' )


      - SET ENCRYPTION ON -- Introduced with SQL 2008, enables cell-level Transparent Data Encryption (TDE) using Data Encryption Key (DEK).

      Date Time User Defined Functions

      T-SQL 2008 allows SELECT $identity FROM ... to display values of the identity (ID) column for the table.

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 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


      A table can only have one column to uniquely identify rows. However, a composite index can consist of up to 16 columns.
      - CONSTRAINT tab1_pk PRIMARY KEY (col1, col2)

      If this is not specified, the MS-SQL default is to create a clustered index which stores data in physical order within leaf nodes containing data pages which are linked in a doubly-linked list implemented as a B-tree index structure that supports fast retrieval of the rows based on their clustered index key values. Tables created with a non-clustered index are called heaps with leaf nodes containing index rows. A covering index includes all columns referenced in the query such that the index contains the data sought in order to avoid looking up actual data in the table -- good If applications perform the same query over and over on the same table.

      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.


      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)
      Whether a table has an identity column can be determined using the OBJECTPROPERTY function. This can be selected using the IDENTITYCOL keyword.
      To turn off Identify checking temporarily (for ETL) - SET IDENTITY_INSERT mytable On;
      INSERT mytable (IdentityColumnID, etc. )
      SET IDENTITY_INSERT mytable Off;

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Default Objects

      In Microsoft SQL Case statements:

        SELECT CustomerTypeName, [Default],
          CASE [Default]
            WHEN 1 THEN 'default type'
            WHEN 0 THEN 'alternate'
            ELSE '-'
          End as AssignStatus -- end case
        FROM dbo.CustomerType

      This replaces sp_bindefault and sp_bindrule objects in SQL7 now recognizes only for backward compatibility.

        USE table1
        CREATE DEFAULT default_par AS 72
        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 can be used to read or write text operating system files from within PL/SQL.

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Indexes

      Action Oracle SQL Microsoft SQL

      Create Index

      - Syntax:
      INDEX index_name ON table (column [,...n])
      [[,] FILLFACTOR = fillfactor]
      [[,] IGNORE_DUP_KEY]
      [[,] DROP_EXISTING]
      [ON filegroup]

      • MS-SQL 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.
      • Since MS-SQL7, key values from several fields (such as last name, first name, middle initial, etc.) can be stored together 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).
        • The top node of the B-tree pyramid is the sysindexes.root node.
        • A lookup traverses the Intermediate nodes in the middle.
        • Pages containing data are called leaf nodes at the bottom layer.
      • 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.
      • The DMV sys.dm_db_index_usage_stats lists indexes used by select, update or delete operations. Conversely, unused indexes do not appear on the list should be removed to improve OLTP performance.

    Go to Top of this page.
    Previous topic this page
    Next topic this page

      Set screen Index Tuning

      Which tables in a database may need indexes?
      Which tables are being scanned by queries instead of using an index?

      Microsoft's Index Tuning Wizard uses a workload file generated by the SQL Server Profiler Create Trace Wizard "Identify Scans of Large Tables" trace run.

      Its results are presented in a Index Usage Report of the percentage of queries and recommended indexes.

      For applications that perform the same query over and over on the same table, consider creating a covering index on the table.

      However, each additional index operating on a table increases the time to do CREATE, UPDATE, etc.

      Indexes on integer columns are significantly faster than columns with character values.

      Set screen MS-SQL Configuration

      For faster MS-SQL Server restart, set the tempdb to the size needed while running a normal load. The larger size avoids the time to autogrow the tempdb.

      Also set Tempdb to autogrow at increments appropriate to the maximum anticipated load.


    Go to Top of this page.
    Previous topic this page
    Next topic this page

      Set screen 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

      To rebuild all indexes in a MS-SQL database, schedule an undocumented procedure to execute during CPU idle time and slow production periods:

        sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen DBCC SHOWCONTIG Fragmentation Statistics

      SQL Server's DBCC (Database Console Command) interface is rather dated.

      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.

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Query Joins

    • inner joins
    • left outer joins
    • cross joins to connect primary key or to create test data or pidgeon-hole positions for future data values.
    • nested loops
    • merge joins
    • hash joins
    • Star Joins (MS-SQL 2008)
    • Set screen Inner Joins

      Inner joins are the most common for connecting a category look-up table to a value.

      Microsoft has deprecated "legacy" joins which use the WHERE clause to link tables. This approach is also used in Oracle.

      USE ...
      WHERE ...
      ORDER BY ...

      Microsoft instead uses the JOIN keyword.

      USE ...
      WHERE ...
      ORDER BY ...
      JOIN ...

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. Views

      Views simplify ad-hoc queries by saving SQL statements consisting of multiple joins.

      Action Oracle SQL Microsoft SQL

      Create/Alter View


      CREATE VIEW testview AS
      SELECT firstname, lastname FROM employees
      ... or
      ALTER VIEW testview AS


      List Views defined

      select view_name from user_views; -

      Display read I/O


      Display Clustered indexes used

      in optimized query Execution Plans (algorithms)
      for resource costing estimates or
      graphical SHOWPLAN in SQL Server Query Analyzer

      Update Statistics Used for Auto Indexing

      to stop updating

      Display database linkages and sizes


      Get on this page Fragmentation Statistics

      DBCC SHOWCONTIG(123456789)
      number from Data Base Consistency Checker response DBCC CHECKTABLE
      displays linkages and sizes

      Sync view metadata with view

      - sp_refreshview 'viewname'

      Avoid ORDER BY within views unless TOP is used.

      Views cannot access temporary tables.

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. 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 .
      Top 50 CPU bound statements
      (Queries Expensive for CPU)
      SELECT TOP 50 (a.total_worker_time/a.execution_count) 
      	as [Avg_CPU_Time],
      		as 'Last_execution_Time',
      	(case when a.statement_end_offset = -1 
      		len(convert(nvarchar(max), b.text)) * 2 
      		- a.statement_start_offset)/2) 
      			as Query_Text,
      	b.objectid as 'Object_ID'
      FROM sys.dm_exec_query_stats a
      	cross apply 
      	sys.dm_exec_sql_text(a.sql_handle) as b
      ORDER BY [Avg_CPU_Time] DESC

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set this at top of window. Logic Control

      Action Oracle SQL Microsoft SQL

      Control structures

      PL/SQL extensions to SQL:
      conditional if-then-else

      Control structures

      PL/SQL extensions to SQL:
      iterative for-loop
      sequential while-loop & end loop; exit-when
      unconditional flow-control goto

      Labels to go to

      << SampleLabel >>. -

      Sequential processing using a cursor

        cursor each_emp is
          select ename, sal
          from emp
          where deptno = 30;
        emp_name varchar2(20);
        emp_sal number(7,2);
        open each_emp;
          fetch each_emp
            into emp_name, emp_sal;
          if each_emp%NOTFOUND then
          end if;
          emp_sal := emp_sal + (emp_sal * .10);
        end loop;
        close each_emp;
      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
          set @emp_sal = (@emp_sal + (@emp_sal * .10))
          FETCH NEXT FROM each_emp into @emp_name, @emp_sal
      CLOSE each_emp
      DEALLOCATE each_emp

      Related functions and cursors stored together

      Packages -

      Table support

        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;
        open each_emp;
          i := i + 1;
          fetch each_emp
            into emp_table(i);
          if each_emp%NOTFOUND then
          end if;
        end loop;
        -- work with emp_table(1), emp_table(2), etc.
        close each_emp;
      EXEC CREATE TABLE emp_table (column1 INT NOT NULL, column2 CHAR(10) NOT NULL)

      EXEC sp_help emp_table



      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.

      SQL_RoboPERF procedure reports

        1) HIGHCPU - Top 50 statements that are CPU intensive
        2) LOCKS - Detect real time locking information (like system stored procedure sp_lock2 corrected for 2000)
        3) TempDBData - View current SQL Statements from TempDB
        4) SQLJobs - View SQL Server Job details
        5) IndexUsage - View which indexes are being used

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Locks for Transaction Isolation

      Dynamic management view (DMV) sys.dm_tran_locks lists locks GRANT or in WAIT at a moment in time.

      T-SQL by default imposes a shared (S) lock on the data resource requested by each transaction request. This lock is released as soon as the read statement is done. Multiple transactions can hold shared locks on the same data resource simultaneously. This is done for lock compatibility -- so that a grant request for exclusive locking can be rejected on a resource already having any kind of lock.

      To get an exclusive lock (X), a transaction must first acquire an intent exclusive (IX) lock both on the page where the row resides plus an intent exclusive lock on the object that owns the page.

      update mode lock converts to exclusive.

      Use TRY/CATCH to handle deadlocks (a fatal embrace) when two transactions are waiting on each other.
      SQL Server Profiler, New Trace, Event Deadlock Graph and Lock:Deadlock -- schema locks?

      Coarse-grain locks (such as on a whole database or table during schema changes), block locks of smaller granularity on RID (row key), 8KB page, 64KB extents, allocation units, or object (for example, table), and heap or B-tree.

      SQL Server 2008 adds a way to set table LOCK_ESCALATION using the ALTER TABLE statement to control automatic lock escalation.

      By default, T-SQL implements the isolation level to READ COMMITTED to prevent "dirty reads" when a transaction sees uncommitted changes by other transactions. This causes locks to wait until changes are committed.

      To enable dirty reads, set the least restrictive isolation level -- READ UNCOMMITTED.

      To prevent "non-repeatable" reads when a transaction sees changes committed by other transactions, set isolation level to REPEATABLE READ.

      To prevent "phantom rows" when repeating the same SELECT reflect changes committed by other transactions. set isolation level to SERIALIZABLE.

      T-SQL Snapshot Isolation is an alternative, but it puts a heavy load on tempdb.

      To set session lock expiration time-out ...

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 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
      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
      sp_revokelogin, sp_denylogin login, name_in_db
      sp_denylogin login, name_in_db
      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
      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

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Aggregate Functions

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

      Max is less efficient than top(n).

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

      Output table to a flat text file:

      DECLARE @sql VARCHAR(8000)
      SELECT @sql = 'bcp master..sysobjects out c:\sysobjects.txt -c -t, -T -S ' + @@servername
      EXEC master..xp_cmdshell @sql


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen User Defined Functions

      To avoid the full scan that MS-SQL 6.5 & 2000 performs for SELECT COUNT(*), the ROW_COUNT() scalar UDF from Alexander Chigrik returns from sysindexes the total row count for a given table:

        CREATE FUNCTION ROW_COUNT ( @table sysname )
        RETURNS int
          DECLARE @row_count int
          SELECT @row_count = rows FROM sysindexes
            WHERE id = OBJECT_ID(@table) AND indid < 2
          RETURN @row_count
        USE pubs
        SELECT dbo.ROW_COUNT('authors')


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Cursors



    The Oracle "opened cursors cumulative" metric
    in V$SESSTAT is the count of cursors opened since session start. But
    in V$SYSSTAT it is the count of cursors opened since instance start.

    Oracle configuration item OPEN_CURSORS specifies the maximum number of open cursors a session is allowed to have at once in order to prevent a session from opening too many cursors. This parameter is needed to constrain the size of the PL/SQL cursor cache PL/SQL uses. Having enough save avoids the need to reparse statements reexecuted by users.



      Unlike a GUI cursor, a databse cursor is an area of memory which holds data fetched from the database. Global cursors are deallocated when the connection ends. Local cursors are limited to a single procedure.

      A database 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 processing the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position.

      Cursors must die!

      Cursors are derided by some because cursors process one row at a time, so they scale less well than SET transactions (correlated subqueries) which operate on entire sets of rows at a time. It is also a hassle to allocate and de-allocate cursor memory, such as these MS-SQL statements from Paul Nielson:

        USE AdventureWorks
        SET NoCount ON
        -- Add column to hold the CumulativeTotal:
        ALTER TABLE Sales.SalesOrderHeader
          ADD CumulativeTotal MONEY NOT NULL
          CONSTRAINT dfSalesOrderHeader DEFAULT(0)
        ALTER INDEX ALL ON Sales.SalesOrderHeader
          @SalesOrderID INT,
          @TotalDue MONEY,
          @CumulativeTotal MONEY
        SET @CumulativeTotal = 0
          FOR SELECT SalesOrderId, Total Due
          FROM Sales.SalesOrderHeader
          ORDER BY OrderDate, SalesOrderID
        OPEN cRun
        FETCH cRun INTO @SalesOrderID, @TotalDue -- to prime the cursor
          WHILE @@Fetch_Status = 0 --[0=FOUND]
            BEGIN --loop to accumulate values:
              SET @CululativeTotal = @CumulativeTotal + @TotalDue
              UPDATE Sales.SalesOrderHeader
                SET CumulativeTotal = @CumulativeTotal
                WHERE SalesOrderID = @DalesOrderID
              FETCH cRun INTO @SalesOrderID, @TotalDue -- next
        CLOSE cRun
        DEALLOCATE cRun

      This example is shown because cursors are more efficient than SET transactions at calculating running sums (cumulative totals) and at dynamic DDL Code iteration.

      PL/SQL example:

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

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


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

        FETCH NEXT
        FETCH RELATIVE 3   (3 rows prior)
        FETCH LAST   (bottom row)

      To determine how many rows have been affected by the executable statement:

        MS-SQL updates @@ROWCOUNT and displays even when SET NOCOUNT ON which controls display of "nn rows affected" at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE. The default is OFF to reduce network traffic to send DONE_IN_PROC messages to the client for each statement in a stored procedure.

        Oracle provides built-in cursor ATTRIBUTE SQL%ROWCOUNT, one of Oracle's four built-in cursor ATTRIBUTES to determine the status of cursors:

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

      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;

      Cursor variables allow different SQL SELECT statements.

      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;
          EXECUTE procedure EXCEPTION --optional error handling code

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

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Set-Base Query Optimization

      Using a SET command in MS-SQL :

        DECLARE @ComulativeTotal MONEY
        SET @ComulativeTotal=0
        UPDATE Sales.SalesOrderHeader
        SET @ComulativeTotal=ComulativeTotal=@ComulativeTotal+ISNULL(TotalDue, 0)

      The SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

      The problem with this is there is no assurance of data ordering assuming a clustered index.

    • Use a data-driven database schema design
    • Use UDF to embed logic within a set-based query
    • Use CASE and PIVOT expressions for flexible logic in crosstab queries
    • Use multiple queries and multiple assignment variables to break down complex tasks
    • Common Table Expressions vs. Derived Tables MS-SQL 2005 introduced CTEs which (as Nigel Rivett explains) is used instead of functions, temp tables, cursors, etc. because it's a "temporary result set" that exists only within the scope of a single SQL statement.

      But unlike a derived table, a CTE can be referenced multiple times in the same query. which allows CTEs to be great at recursion. used to calculate percentages by performing arithmetic using aggregates without repeating queries or using a temp table.

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Triggers on Tables

      Action Oracle MS-SQL

      Get a list of triggers

      . SELECT trigger_name
      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] }
      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.

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Alerts

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

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 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.

      The MS-SQL Server Profiler Create Trace Wizard run with "Identify Scans of Large Tables" (during non-peak times from a workstation machine) determines (into a trace text file) which tables are being scanned by queries instead of using an index.

      Ignite™ for SQL Server Performance Finds, analyzes, and resolves bottlenecks impacting SQL Server response time with Ignite™ for SQL Server.


    Itzik Ben-Gan's
    Microsoft SQL Server 2008 T-SQL Fundamentals
    Inside Microsoft SQL Server 2008: T-SQL Programming (Microsoft Press, 2009).

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Oracle Resources

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen MS-SQL Resources

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Certifications for Database Administrators


    "Small Blue Window, 1999" (Oil on linen) by Sean Scully

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen 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

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Portions ©Copyright 1996-2014 Wilson Mar. All rights reserved. | Privacy Policy |


    How I may help

    Send a message with your email client program

    Your rating of this page:
    Low High

    Your first name:

    Your family name:

    Your location (city, country):

    Your Email address: 

      Top of Page Go to top of page

    Thank you!