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.
Topics this page:
Screen: Max • 1024 • 800
This page compares the capacity limitations of Oracle vs. Microsft SQL Server databases.
SQL is pronounced either as a sequence of letters "Es-Queue-Ell" or as acronym "Sequel".
MySQL AB was created by Monty Widenius.
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.
Other Oracle SQL commands:
This table cross-references the jargon for the same concept from both products.
Every time an Oracle user invokes SQL*Plus, two scripts are also automatically executed:
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-SQLThis 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] GO Create procedure s_SpaceUsed @SourceDB varchar(128) as /* 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 ' + @SourceDB + '.INFORMATION_SCHEMA.TABLES 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) begin select @name = min(name) from #tables where name > @name select @sql = 'exec ' + @SourceDB + '..sp_executesql N''insert #SpaceUsed exec sp_spaceused ' + @name + '''' exec (@sql) end select * from #SpaceUsed drop table #tables drop table #SpaceUsed go
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):
For a list of current users and processes within MS-SQL, use sp_who system stored procedure.
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'
select @@VERSION returns a value such as
SELECT @@microsoftversion / 0x01000000 yields:
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"); Console.WriteLine(theServer.Information.VersionString);
To get Oracle's version (such as "18.104.22.168.0" or "22.214.171.124.0") :
to_char(startup_time,'DD-MON-YYYY HH:MI:SS') strtd AS 'STARTED'
At a Unix command prompt:
Oracle 9i introduced a package:
EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION DBMS_DB_VERSION.RELEASE);;
To define a bind variable in Oracle's SQL*Plus:
VARIABLE x NUMBER
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.
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:
RESTORE FILELISTONLY FROM DISK = 'C:\projects\Ashim\db01.bak' GO RESTORE DATABASE db01 FROM DISK = 'C:\projects\Ashim\db01.bak' WITH MOVE 'sample_Data' TO 'C:\projects\Ashim\db01.mdf', MOVE 'sample_Log' TO 'C:\projects\Ashim\db01.LDF' GO
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'
The "MSSQL10.MSSQLSERVER" is the instance name.
To import tab-delimited csv files, the AdventureWorks sample database for Microsoft SQL Server 2005/2008 use this BULK INSERT code which runs within the SQL Server process (instead of BCP, which creates its own process):
BULK INSERT [Production].[WorkOrder]
FROM '$(SqlSamplesSourceDataPath)AdventureWorks 2008 OLTP\WorkOrder.csv'
SQL 2008 enhanced the VALUES clause for multiple rows:
INSERT INTO dbo.Orders (orderid, orderdate, empid, custid) VALUES (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.
This assumes that there is a valid .my.cnf file with entries such as:
[client] user = DBUSERNAME password = DBPASSWORD host = DBSERVER [mysql] database = DBNAME
This also assumes that permissions have been changed on the file to make it writable with a shell command such as:
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.
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:
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:
T-SQL 2008 allows SELECT $identity FROM ... to display values of the identity (ID) column for the table.
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”!
In Microsoft SQL Case statements:
WHEN 0 THEN 'alternate'
This replaces sp_bindefault and sp_bindrule objects in SQL7 now recognizes only for backward compatibility.
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:
Oracle supplied built-in package UTL_FILE can be used to read or write text operating system files from within PL/SQL.
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.
Also set Tempdb to autogrow at increments appropriate to the maximum anticipated load.
To rebuild all indexes in a MS-SQL database, schedule an undocumented procedure to execute during CPU idle time and slow production periods:
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?
Microsoft has deprecated "legacy" joins which use the WHERE clause to link tables. This approach is also used in Oracle.
USE ... SELECT WHERE ... ORDER BY ...
Microsoft instead uses the JOIN keyword.
USE ... SELECT WHERE ... ORDER BY ... JOIN ...
Views simplify ad-hoc queries by saving SQL statements consisting of multiple joins.
Avoid ORDER BY within views unless TOP is used.
Views cannot access temporary tables.
SQL_RoboPERF procedure reports
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
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.
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 ...
Sum, Avg, Min, Max, StDev, StDevP, Var, VarP
Max is less efficient than top(n).
count(*) returns the number of rows in the table.
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
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 AS BEGIN DECLARE @row_count int SELECT @row_count = rows FROM sysindexes WHERE id = OBJECT_ID(@table) AND indid < 2 RETURN @row_count END GO USE pubs GO SELECT dbo.ROW_COUNT('authors') GO
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:
SET NoCount ON
-- Add column to hold the CumulativeTotal:
ALTER TABLE Sales.SalesOrderHeader
CONSTRAINT dfSalesOrderHeader DEFAULT(0)
DECLARE cRun CURSOR FAST_FORWARD -- not READ_ONLY
ORDER BY OrderDate, SalesOrderID
FETCH cRun INTO @SalesOrderID, @TotalDue -- to prime the cursor
WHERE SalesOrderID = @DalesOrderID
This example is shown because cursors are more efficient than SET transactions at calculating running sums (cumulative totals) and at dynamic DDL Code iteration.
DECLARE 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:
FETCH RELATIVE -3
Declaring a CURSOR INSENSITIVE makes it not sensitive to changes in the table and won't refresh.
FETCH RELATIVE 3 (3 rows prior)
FETCH ABSOLUTE 4
FETCH ABSOLUTE -1
FETCH LAST (bottom row)
To determine how many rows have been affected by the executable statement:
Oracle provides built-in cursor ATTRIBUTE SQL%ROWCOUNT, one of Oracle's four built-in cursor ATTRIBUTES to determine the status of cursors:
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:BEGIN --mandatory data manipulation statement(s):
CURSOR emptyp_cur IS
WHERE type_code = :emp.type_code
DBMS_OUTPUT.PUT_LINE('v1= ' || v1);
IF NOT emptyp_cur%ISOPEN THEN
FETCH emptyp_cur INTO :emp.type_desc;
INSERT INTO employee VALUES (6, 'TOM LEE', 10000);
THEN null; passes control to the next statement. END; --mandatory .
DECLARE @ComulativeTotal MONEY
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.
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.
Sequence of Trigger processing:
MS-SQL Alerts are stored in the Msdb physical database and managed by the SQL Server Agent
Microsoft SQL Server 2008 T-SQL Fundamentals
Inside Microsoft SQL Server 2008: T-SQL Programming (Microsoft Press, 2009).
Oracle View 1996-8 is an online magazine by Intelligent Enterprise, Inc. for architects and managers of information technology on the implementation and management of Oracle database products, development strategies, and application tools.
The Virginia Tech reference tool describes functions, procedures, data types in the PL/SQL packages that make up the Web Application Server PL/SQL Web Toolkit in addition to an Overview, Glossary, FAQ, Installation, and Cartridges.
Reveal Net: PL/SQL Pipeline maintained by the International Oracle Users Group (IOUG-A) of more than 7,500 Oracle professionals,
Want to access Oracle and mySQL databases from .NET apps?
Amazon EC2 runs Windows SQL Server for about $1 per instance hour.
Microsoft's Best Practices Analyzer for SQL Server 2000 (bpasetup.msi) published 5/25/2004 reports on the implementation of common Best Practices such as the usage and administration aspects of SQL Server databases.
Microsoft SQL Analysis Services:
$35 Transact-SQL Programming by Kline, Kevin E.; Gould, Lee.; Zanevsky, Andrew. Sebastopol, Calif. O'Reilly and Associates, 1999 compares Microsoft SQL Server 6.5 and Sybase SQL Server 11.5.
This User's Security Guide details how to harden SQL Server 2000 for a C2-evaluated configuration.
2005 Exams are retiring March 2009, so MS has reduced their price 40% from $125 to $75.
MCITP: Database Administrator certification
70-447 Upgrade from 2000 to 2005
Inside SQL Server 2005 books "T-SQL Querying", "T-SQL Programming", and "Query Tuning and Optimization".
Question: You are collecting SQL server Cache Hit Ratio data. What should the value be greater than for a well-tuned SQL server?
f. 95% Correct answer: e
Your first name:
Your family name:
Your location (city, country):
Your Email address:
Top of Page