Oracle |
SQL Server |
More Information |
%TYPE data
type |
No equivalent |
The %TYPE data
type of Oracle, lets you create a variable and have that
variable's data type be defined by a table or view column or a
PL/SQL package variable.
There is no equivalent for
Oracle's %TYPE datatype in T-SQL, but it can be simulated (not
very conveniently though) using User Defined Data types (UDT).
Here is an example:
EXEC sp_addtype 'MyType', 'smallint',
NULL
CREATE TABLE MyTable (i MyType)
CREATE PROC
MyProc AS BEGIN DECLARE @i MyType END
|
BEFORE
triggers |
INSTEAD OF
triggers |
Use INSTEAD OF
trigger in SQL Server as an equivalent to Oracle's BEFORE
trigger.
For more information on INSTEAD OF triggers, see
SQL Server Books Online
|
DECODE()
function |
CASE
expression |
DECODE can be
conveniently simulated using the T-SQL CASE expression. Here's
an example:
SELECT Sport, CASE Sport WHEN
'Cricket' THEN 'England' WHEN 'Hockey' THEN 'India' WHEN
'Base Ball' THEN 'America' ELSE NULL END AS 'Originating
Country' FROM Sports
|
DESCRIBE |
sp_help or
sp_columns |
There are a lot
of alternatives for Oracle's DESCRIBE, in SQL Server. You could
use the system stored procedure sp_help for detailed information
about a table's columns and other properties.
If sp_help
is providing you with too much information, then try the ODBC
catalog stored procedure, sp_columns.
There are a bunch
of other useful sp_help* stored procedures available in SQL
Server. You can find more information about those in SQL Server
Books Online.
If none of those procedures are suitable
for your requirements, then you could query the system view
INFORMATION_SCHEMA.COLUMNS, to get the desired information. You
could wrap your code inside a stored procedure named DESCRIBE,
if you wish.
As a last resort, you could even query
system tables like sysobjects and syscolumns, but this is not a
recommended approach.
|
DUAL table |
No equivalent |
There is no
DUAL table in SQL Server. In fact, you don't need one in SQL
Server, as you can have a SELECT statement without a FROM
clause.
For example, consider the following SELECT
statement in Oracle:
SELECT 'Something' FROM DUAL
In SQL Server, the same result can be obtained by the
following command:
SELECT 'Something'
If you are
porting some code from Oracle into SQL Server and if you don't
want to remove all references to DUAL table, then just create a
DUAL table in your database using the following commands:
CREATE TABLE DUAL ( DUMMY varchar(1) )
INSERT INTO DUAL (DUMMY) VALUES ('X')
|
INTERSECT
operator |
Not supported |
Use EXISTS
clause to generate the same result.
The following example
illustrates the simulation of Oracle's INTERSECT operator:
SELECT OrderID, OrderDate FROM Orders O WHERE EXISTS
( SELECT 1 FROM RefundsTable R WHERE O.OrderID =
R.OrderID )
|
MINUS operator |
Not supported |
Use NOT EXISTS
clause in your SELECT statement to generate the same result.
The following example illustrates the simulation of Oracle's
MINUS operator:
SELECT OrderID, OrderDate FROM Orders
O WHERE NOT EXISTS ( SELECT 1 FROM RefundsTable R
WHERE O.OrderID = R.OrderID )
|
Nested tables |
Not supported |
Oracle 8i and
prior versions didn't support this feature and is introduced in
Oracle 9i. This feature basically enables you to store a table,
within a column. It is like having an array of records in your
database columns.
SQL Server has no concept of nested
tables.
As a workaround, You could store your sub-tables
or child tables in the form of XML inside a char, nchar, varchar,
nvarchar, text or ntext type column, and parse it as needed, at
runtime. See OPENXML, sp_xml_preparedocument,
sp_xml_removedocument in SQL Server 2000 Books Online.
Another possible workaround would be to store comma separated
values (CSV).
Note that this is against the basic rules
of normalization. Columns are nomore atomic, with nested tables.
From a design point of view, best thing to do would be, to
create different tables for representing different entities and
link them with primary and foreign key relationships. This will
also make searching easier.
|
Oracle |
SQL Server |
:-)
|
Packages |
Not supported |
No equivalent
in SQL Server for Oracle's Packages and Package variables
concept
|
PL/SQL |
T-SQL |
Every database
product implements and extends the standard SQL. Oracle's
implementation of SQL is called PL/SQL, while Microsoft's is
called T-SQL (Transact-SQL)
|
Row level
security |
No equivalent |
Though there is
no inbuilt support in SQL Server for row level permissions, you
can implement it using view and system functions.
|
rownum pseudo
column |
No equivalent |
Though there is
no rownum or rowid in SQL Server, there are several ways in
which a row number can be generated.
|
SELECT...FOR
UPDATE |
UPDLOCK hint |
Use the locking
hint UPDLOCK in your SELECT statement.
See SQL Server
Books Online for more information.
|
Sequences |
IDENTITY |
It is much
simpler in SQL Server to generate a sequence value for a primary
key or a non-key column.
You just need to turn on the
IDENTITY property of column. IDENTITY property can be set to
columns of the following data types: tinyint, smallint, int,
bigint, decimal, numeric
Try this example to see how it
works:
CREATE TABLE foo ( i int IDENTITY(1, 1) j
int )
INSERT INTO foo (j) VALUES (911) INSERT INTO
foo (j) VALUES (999)
|
SQL *Plus |
Query Analyzer |
For connecting
to SQL Server and executing queries and modifying data, use the
built-in Query Analyzer. It is much more powerful and friendlier
than Oracle's SQL *Plus
|
START
WITH...CONNECT BY clause |
No equivalent |
Though there's
no direct equivalent in T-SQL for Oracle's START WITH...CONNECT
BY, there are several ways and efficient techniques for
processing and querying hierarchical data.
|
Synonym |
Views |
You can
simulate Oracle Synonyms in SQL Server using Views. For example,
the following creates a view that returns the OrderID and
OrderDate from Orders table.
CREATE VIEW vOrders AS
SELECT OrderID, OrderDate FROM Orders
Now you can
select data from the view, using the following SELECT statement:
SELECT * FROM vOrders
The following example will
create a view which is equivalent to Oracles TABS synonym or
USER_TABLES data dictionary view (For simplicity, I am only
including the table name in the view definition):
CREATE
VIEW TABS AS SELECT name AS table_name FROM sysobjects
WHERE type = 'U'
Now you can execute the following SELECT
statement to return all user table names:
SELECT
table_name FROM TABS
|