10 Things Oracle DBA Miss on SQL Server

Are you Oracle DBA frustrated while learning SQL Sever? Relax, you are in a right place. I have been an Oracle DBA for years supporting nothing but Oracle databases. Not all companies use Oracle for their Relational Database Management System. Depending on the needs, some use MySQL, SQL Server or other DBMS as their database. Recently, our company acquired another company where they have hybrid of SQL Server and Oracle database. Our core DBA team is now responsible for managing the databases of the new company as well. To support the hybrid environment, we all are required to learn SQL Server.

This article is based on my experience learning SQL Server. The key to success for learning the SQL Server is to stop thinking like Oracle and believes that SQL Server has it own mind and does things differently. Also, I am going highlight and talk about few key features that you use in Oracle are either are not available or use different commands or some work around in SQL Server.  Are you ready to explore them?

COMMIT: The commit statement ends your current transaction and makes change permanent for a transaction. This statement erases all the save point in the translation and release the transaction lock. Until the transaction is committed, you will not see the changes. You can rollback(undo) the change by rollback if the transaction is not committed.

SQL Syntax:

Insert into employee values(1054,'John', 'Doe', 'IS')
Commit;
Update employee set lname ='Smith' where empid =1054;
commit;

Oracle commit Statement or equivalent is not available on SQL Server but there is Commit Transaction statement for T-SQL. Any UPDATE, INSERT on SQL Server are final, You don't need to issue COMMIT statement and there is no rollback for it.

DUAL: Dual is a table automatically created by Oracle database along with the data dictionary. DUAL falls under SYS schema that is accessible to all the users. It has only on column VARRCHAR2(1). Selecting from DUAL table is useful for computing a constant expression with the SELECT statement.

SQL Syntax:

--Oracle
Select sysdate from DUAL;
Select (21+33) from DUAL;

Oracle DUAL dummy table concept is not available on SQL 2014 or earlier version however, You can still accomplish what DUAL can do with out using it.

SQL Syntax:

--SQL Server
select sysdatetime();
select (21+23);

SEQUENCE: A sequence is a sequence number generator. Sequence is used in a table to automatically populate primary key values or some ids. The sequence generated is incremented, independent of the transaction committing or rollback. If two users concurrently increment the same sequence, then the sequence number each user acquires may have gaps because the other users might have generated.

Oracle Sequence equivalent is available on SQL Server as Sequence on Version 2012 and later. Version 2008 and earlier do not support Sequence object.

DESCRIBE: This Oracle command provides a description of the specified table or view. This is really helpful to know the table detail like columns, table size, constraints, row, row size, dependencies etc. This is really useful command while working with tables.

SQL Syntax:

DESCRIBE {table-Name| view-Name}
DESC {table-Name| view-Name}

Oracle DESCRIBE is equivalent to sp_help functions. sp_help '[DB-Name].[Schema].[Table-name]' but this is not as detail as that of Oracle but it does the job.

RECYCLEBIN: Like Windows and MAC, Oracle also has a  recycle bin that stores all the dropped objects. You can empty it from the bin or retrieve the objects from recycle bin. Have you ever had a “Thank God, there is a recycle bin” moment? This recycle bin has saved me from being in a mess. I am sure you had been in this situations before and that is not fun moment to be. There are two
recycle bin views; recycle bin same as user_recyclebin, and dba_recyblebin. You can locate your dropped objects on these views. TO BEFORE Drop clause retrieves from the recyclebin of table that has been dropped along with all the possible dependent objects.

Oracle Recycle bin or equivalent feature in unavailable on SQL Server 2014 or prior version, therefore you should make a backup of objects before dropping.

PURGE: When you send PURGE command along with drop SQL statement, the dropped objects will not be in recycle bin, it is delete from the server OS. When you drop a table, the object  does not get dropped from the database. It actually gets renamed. Oracle renames the table and its associated objects(indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$. You should very well know when to use drop and purge.

Oracle Purge statement or equivalent feature is unavailable on SQL Server 2014 or prior version.

EXTERNAL Table: Oracle External Table allows Oracle Database to query data that are stored outside of the database in flat files as if they were inside a database. External tables are used to load data into a table, during Extract Transform and Load (ETL)  process or to compare between two files. SQL Loader will be your great choice if you are loading data in a table that will require index.
External table will not allow any DDL instead you can drop and recreate them. You can’t perform any DML operation on external tables other than SELECT. You will need to put a little thought on how you are going to query it before designing an external table.

Oracle External table or equivalent feature or object is unavailable in SQL server 2014 or prior version.

FLASHBACK: Flashback Table statement restore an earlier sate of a table in the event of human or application error. This is very handy command for Oracle DBA and developer. How far can you go back to restore entirely depends on the amount of undo data in the system. The Flashback only restore the changes made by DML not DDL statements.

SQL Syntax:

select * from employee as of timestamp;

Oracle FLASHBACK or equivalent feature is unavailable on SQL server 2014 or prior version of SQL Server.

HINT: Hint alters the execution plans of a query. The developer of an application knows the data better than Oracle therefore you are sometime required to alter the execution query to use Hint for using Index, Parallel Processing etc for optimizing query.

Oracle Hint equivalent is available on SQL Server as Hint. SQL Server hint force Index usage, but it doesn't do a parallel processing or minimum logging.

Oracle Enterprise Manager: This is a web based Oracle manager console which gets installed during Oracle installation. With this console, you can perform administrative tasks such as creating schema objects, tablespace, tables, indexes, managing user security, backup and recovering your database, and importing data. You can also view performance and status information about your database instance.  The URL for OEM is http://hostname:portnumber/em

Oracle OEM equivalent is not available on SQL Server, You will need to install SQL Server Management Studio to administer your SQL Server.

Do you feel better now? I can relate to your frustration because I went through the same route. Remember, this is not the comprehensive list but some major ones that makes your learning easier. This article by no means is trying to prove Oracle is better or SQL Server is less of a database, they are equally good databases. I find SQL Server learning fun and challenging. I wish you the best in learning SQL Server.