FAQ : What is Oracle Predefined Exceptions equivalent in SQL Server

What is Oracle Predefined Exceptions
An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers. PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself.
Here are few examples
NO_DATA_FOUND : A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.

TOO_MANY_ROWS : A SELECT INTO statement returns more than one row.

DUP_VAL_ON_INDEX : Your program attempts to store duplicate values in a database column that is constrained by a unique index.

Read more about Predefined exceptions here

What is SQL Server Equivalent?

Till SQL Server 2008 we do not have any equivalent. Ie there is no Predefined Exceptions in SQL Server. But this can be achieved by BEGIN TRY .. END TRY BEGIN CATCH … END CATCH. ERROR Number/Message combination.

FAQ : Oracle %ROWTYPE equivalent in SQL Server

What is %ROWTYPE in Oracle?

The %ROWTYPE attribute provides a feature to create Table based or Cursor Based record. Ie. If you have a table with 5 column and you want to select all the five column value of a single record at a stretch instead of declaring 5 scalar variable you just declare a single variable of %ROWTYPE which will intern have all the five column. Consider we have a table called Department with three columns Department_ID, Description and Name. Either you can declare three scalar variable or you can declare single variable of %ROWTYPE attribute. If you want to store a collection (multiple records ) then you can use TABLE OF TableName%RowType;
See the below eg.
DECLARE
Dept_REC DEPARTMENT%ROWTYPE;
BEGIN
SELECT *
INTO Dept_REC
FROM DEPARTMENT
WHERE DEPARTMENT_ID = 1;
DBMS_OUTPUT.PUT_LINE (' DEPARTMENT ID: '|| Dept_REC.DEPARTMENT_ID);
DBMS_OUTPUT.PUT_LINE ('COURSE DESCRIPTION: '||
Dept_REC.DESCRIPTION);
DBMS_OUTPUT.PUT_LINE ('Name: '||
Dept_REC.Name);
END;

SQL Server Equivalent.

In SQL Server what can come close to TABLE OF TableName%RowType is Declare a table type variable (in case it is collection). Another way may be create a #temp table. But temp table method may involve IO which need to be aware of and also in there are limitation where you can create #temp table. For a single row, it is better to declare separate scalar variable for each columns

Table variable Method
Declare @Dept Table (Department_id Int,Name Varchar(100),Description Varchar(1000))
The below statement will insert all the rows from Department to @Dept variable.
Select *INTO @Dept
FROM
Department

Temp table method
Select *into #Temp_Dept
FROM Department
The above statement will create a temp table on the fly. This can be used in Procedure but not in Function.

FAQ : What is SQL Server Profiler equivalent in Oracle

What is SQL Server Profiler?

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly. Basically this tool is a wrapper which internally calls SQL Trace system stored procedures. Generally, running profiler is not recommended in production if you need to trace events, then in production it is better to go for SQL Trace system Stored procedure. Refer this link for more info.

Oracle Equivalent . (please note that this is based on my little experience in Oracle and If anyone feels that If I am going wrong please feel free to correct me)

Automatic Workload Repository (AWR)

Oracle offers many ways to trace events which again later version may have new tools and features. In 10 G, Oracle offers Automatic Workload Repository (AWR) which is used to collect statistics including
• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

Read more about AWR here

SQL trace files and TKPROF

If you are still in Oracle 9, you can go for SQL trace files which gives you statement by statement log of SQL and PL/SQL executed by Database Client(s). The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can help you monitor and tune applications running against the Oracle Server. SQL Trace files entries can be classified in to mainly four categories.
• Database calls (parse, execute, and fetch)
• Wait events
• Bind variable values
• Miscellaneous events like timestamps, instance service name, session, module, action, and client
identifier

TKPROF

You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file. Optionally, TKPROF can also:
• Determine the execution plans of SQL statements
• Create a SQL script that stores the statistics in the database
Read more about SQL Trace files and TKPROF here

FAQ: What is optimizer in SQL Server?

Unlike procedural language SQL Statements does not state the exact steps that database engine should follow to extract the data or manipulate the data. In procedural language the statements are ran as per the sequence they have written. But in SQL , database engine must analyse the statement to determine the most efficient way to extract/manipulate data. This process is called optimization and the component which does this process is called Optimizer.

The input to the optimizer contains , Query , schema (table/indexes definitions) and the statistics available.

The output from the optimizer is query execution plan.



The SQL Server query optimizer is a cost-based optimizer. Each possible execution plan has an associated cost in terms of the amount of computing resources used. The query optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Some complex SELECT statements have thousands of possible execution plans. In these cases, the query optimizer does not analyze all possible combinations. Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

FAQ : What is Optimizing SQL statement .

Unlike procedural language SQL Statements does not state the exact steps that database engine should follow to extract the data or manipulate the data. In procedural language the statements are ran as per the sequence they have written. But in SQL , database engine must analyse the statement to determine the most efficient way to extract/manipulate data. This process is called optimization.

FAQ : What is the equivalent of SELECT * INTO of SQL Server in Oracle

SQL Server
Select * into TargettableName from sourceTableName

Oracle
Create table targetTableName as Select *From sourceTableName

FAQ : What is Packages in Oracle and its equivalent in SQL Server

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.

There is no equivalent in SQL Server yet.

FAQ : How to declare a variable in SQL Server (T-SQL) and in Oracle PL/SQL

SQL Server

Variables are declared in the body of a batch or procedure with the DECLARE Statement and are assigned to values with either a SET or SELECT Statement.After declaration all variables are initialized as NULL
Variables are often used in batch or procedure /functions as counters for WHILE, LOOP or for an IF..ELSE block. Variables can be used only in expression, not in the place of object names or key words (in that case you need to use Dynamic SQL. See Dynamic SQL for more info).

Types of Variable : Three types of variables are available
• @local variable
• @Cursor_Variable_Name
• @table_variable_name

Declare a variable :
DECLARE @VariableName datatype : Variable prefixed with @ symbol.
Eg. DECLARE @Count INT
DECLARE @Name varchar(50), @Address varchar(200)

Seting /initialising a variable :

SET @Name=’Madhu’
SET @Count=100
Initialise multiple variable using Select statement :

SET can be used for only single variable where as SELECT can be used for multiple variable in a single statement

SELECT @Name=’Madhu’ , @Count=0 , @Address=’XYZ’

WHILE(@i<@Count)
BEGIN
SET @i=@i+1
PRINT @i
END

Oracle (PL/SQL) : Declaring Variables
In oracle, Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or a PL/SQL-only datatype, such as BOOLEAN or PLS_INTEGER. For example, assume that you want to declare variables for part data, such as part_no to hold 6-digit numbers and in_stock to hold the Boolean value TRUE or FALSE. You declare these and related part variables as shown in Example 1. Note that there is a semi-colon (;) at the end of each line in the declaration section.

Example 1–1. Declaring Variables in PL/SQL
DECLARE
part_no NUMBER(6);
part_name VARCHAR2(20);

You can also declare nested tables, variable-size arrays (varrays for short), and records using the TABLE, VARRAY, and RECORD composite datatypes.

Assigning Values to a Variable
You can assign values to a variable in three ways. The first way uses the assignment operator (:=), a colon followed by an equal sign, as shown in Example 2. You place the variable to the left of the operator and an expression, including function calls, to the right. Note that you can assign a value to a variable when it is declared.

Example 1–2 Assigning Values to Variables With the Assignment Operator
DECLARE
wages NUMBER;
hours_worked NUMBER := 40;
emp_rec1 employees%ROWTYPE;
country := UPPER('Canada');


The second way to assign values to a variable is by selecting (or fetching) database values into it. In Example 1–3, 10% of an employee's salary is selected into the bonus variable. Now you can use the bonus variable in another computation or insert its value into a database table.

Example 1–3 Assigning Values to Variables by SELECTing INTO

DECLARE
bonus NUMBER(8,2);
emp_id NUMBER(6) := 100;
BEGIN
SELECT salary * 0.10 INTO bonus FROM employees
WHERE employee_id = emp_id;
END;

The third way to assign a value to a variable is by passing it as an OUT or IN OUT parameter to a subprogram, and then assigning the value inside the subprogram.

FAQ : How to declare a variable in SQL Server (T-SQL) and in Oracle PL/SQL

SQL Server

Variables are declared in the body of a batch or procedure with the DECLARE Statement and are assigned to values with either a SET or SELECT Statement.After declaration all variables are initialized as NULL
Variables are often used in batch or procedure /functions as counters for WHILE, LOOP or for an IF..ELSE block. Variables can be used only in expression, not in the place of object names or key words (in that case you need to use Dynamic SQL. See Dynamic SQL for more info).

Types of Variable : Three types of variables are available
• @local variable
• @Cursor_Variable_Name
• @table_variable_name

Declare a variable :
DECLARE @VariableName datatype : Variable prefixed with @ symbol.
Eg. DECLARE @Count INT
DECLARE @Name varchar(50), @Address varchar(200)

Seting /initialising a variable :

SET @Name=’Madhu’
SET @Count=100
Initialise multiple variable using Select statement :

SET can be used for only single variable where as SELECT can be used for multiple variable in a single statement

SELECT @Name=’Madhu’ , @Count=0 , @Address=’XYZ’

WHILE(@i<@Count)
BEGIN
SET @i=@i+1
PRINT @i
END

Oracle (PL/SQL) : Declaring Variables
In oracle, Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or a PL/SQL-only datatype, such as BOOLEAN or PLS_INTEGER. For example, assume that you want to declare variables for part data, such as part_no to hold 6-digit numbers and in_stock to hold the Boolean value TRUE or FALSE. You declare these and related part variables as shown in Example 1. Note that there is a semi-colon (;) at the end of each line in the declaration section.

Example 1–1. Declaring Variables in PL/SQL
DECLARE
part_no NUMBER(6);
part_name VARCHAR2(20);

You can also declare nested tables, variable-size arrays (varrays for short), and records using the TABLE, VARRAY, and RECORD composite datatypes.

Assigning Values to a Variable
You can assign values to a variable in three ways. The first way uses the assignment operator (:=), a colon followed by an equal sign, as shown in Example 2. You place the variable to the left of the operator and an expression, including function calls, to the right. Note that you can assign a value to a variable when it is declared.

Example 1–2 Assigning Values to Variables With the Assignment Operator
DECLARE
wages NUMBER;
hours_worked NUMBER := 40;
emp_rec1 employees%ROWTYPE;
country := UPPER('Canada');


The second way to assign values to a variable is by selecting (or fetching) database values into it. In Example 1–3, 10% of an employee's salary is selected into the bonus variable. Now you can use the bonus variable in another computation or insert its value into a database table.

Example 1–3 Assigning Values to Variables by SELECTing INTO

DECLARE
bonus NUMBER(8,2);
emp_id NUMBER(6) := 100;
BEGIN
SELECT salary * 0.10 INTO bonus FROM employees
WHERE employee_id = emp_id;
END;

The third way to assign a value to a variable is by passing it as an OUT or IN OUT parameter to a subprogram, and then assigning the value inside the subprogram.

FAQ : How to find Product version in SQL Server and in Oracle

SQL Server

Select @@Version
Or
SET NOCOUNT ON;
SELECT
CONVERT( varchar, SERVERPROPERTY('Edition')) AS Edition
, CONVERT( varchar, SERVERPROPERTY('ProductVersion')) AS Version
, CONVERT( varchar, SERVERPROPERTY('ProductLevel')) AS Level


Oracle

select * From V$VERSION ;
Note : V$VERSION is the catalog view which provides all the information in Oracle