I am going on a job interview and have zero experience with MS SQL Server. However I have 1 year with Oracle. Is there such a huge difference between the two? What programming questions can I expect?
+3
A:
Here is a comparison of concepts, terminology and syntax etc. and a comparison of performance / limitations
Also, search SO with tags or keywords like [sql-server], [oracle], "difference" etc. as I'm guessing this topic has been readily addressed, although I didn't find, quickly, such references.
mjv
2010-02-23 22:39:31
+4
A:
They probably know that you have Oracle experience and you do not have SQL Server experience (unless you lied in your resume). Just answer how you will resolve the problems in Oracle PL/SQL. Do not worry if your answer runs in SQL Server. There are differences but they do not matter in a job interview (except if the position is for a DBA). I know a lot of developers that knew Oracle that got jobs that required SQL Server skills and now they are really good in T-SQL.
Jose Chama
2010-02-23 22:40:26
+5
A:
Oracle and SQL Server have different extensions of SQL. Oracle has PL/SQL, while SQL Server has Transact-SQL (T-SQL). That means that while SQL (SELECT, INSERT, UPDATE, DELETE) is similar on a basic level, the syntax diverges quickly depending on what you are trying to do.
When I work with T-SQL decision structure/control of flow, I believe it to be less accommodating than PL/SQL - CASE is an expression in T-SQL so it can't be used for control of flow while that's not the case in Oracle. Another irritation I have with T-SQL is that if you want to more than one operation within an IF branch - the entire branch must be contained in a BEGIN END block. Oracle doesn't care, but you have to define the END IF.
Data types are similar, but Oracle doesn't descriminate between integer/decimal types - it's all NUMBER with the option to specify the degree of precision. Date functions in SQL Server are more accommodating.
Both being popular products, it's easy to google for functionality equivalent in one for the other. Doesn't mean it will exist, but someone has probably posted the workaround.
OMG Ponies
2010-02-23 22:44:46
+17
A:
I'm an Oracle and SQL Server DBA who's now spends too much time on SQL Server.
Not to be rude, but for someone who ONLY has 1 year of Oracle experience then I doubt you are totally ingrained into Oracle, but the most obvious differences are:
- The FIRST biggest difference: Transaction control. In Oracle EVERYTHING is a transaction and it is not permanent until you
COMMIT. In SQL Server, there is (by default) no transaction control. An error half way through a stored procedure WILL NOT ROLLBACK the DDL in previous steps.
Obviously, if you wrap the TSQL DML in BEGIN TRANSACTION and COMMIT then it will roll back but this is rare in SQL Server code I've seen.
- The SECOND biggest difference: MVCC. In SQL Server and Oracle is different. SQL Server will allow dirty reads, and writes can block reads in MS SQL (Again, it's configurable but the default in SQL Server is for performance and not read consistency, unlike Oracle where read consistency is default and unbendable.
Also consider:
When you setup an Oracle server, you tend to have one database with many "users/schemas", and tablespaces that are shared by all your users. SQL Server has separate databases that do not share disk files.
SQL Server uses "logins" to give you access to the SQL Server instance and each database has "users" that map to a login to get individual access to the tables and views etc.
Typically, all the objects in a database are owned by dbo.
TSQL is similar to PL/SQL, but (in my opinion) less powerful. You may need to simplify your SQL to get it to work as well as you'd expect in Oracle.
The SQL Server Management Studio (2008 SP1) is fantastic!
If you like Oracle, all the "getting under the hood" and "explain plan optimisation" then this training and experience will work well for you against guy's who just code straight SQL Server TSQL and expect the server to perform fast by magic.
SQL Server does not have packages. This might start off as a bonus (PL/SQL packages can be a PITA) but eventually you'll start to get a big nest of similarly named stored procedures in the database and you'll wish there was a way you could organise and group then them better.
Final tip, always play to your strengths. Admit that you don't have much SQL Server experience but highlight that you know the principles of how an RDBMS works, how to development a data driven application and how you design in performance from the start, etc.
Guy
2010-02-23 22:48:37
Just wanted to say that when a SQL statement is not wrapped in a transaction/commit block in SQL Server, SQL Server does a seperate transaction for each statement so if you have multiple SQL Statements sepetated by GO commands then each GO works like a Transaction/commit block.
David Parvin
2010-02-23 23:34:21
_Most_ multi-statement SQL Server stored procedures I've read have used `BEGIN TRANSACTION` / `COMMIT`
John Saunders
2010-02-24 01:59:26
You cannot rollback DDL in Oracle either. I think maybe you meant DML?
Jeffrey Kemp
2010-02-24 07:25:52
@Jeffrey, your correct of course. It was late!@John, I see a LOT of different vendors databases and even the best systems occasionally slip this by. The main point is that in Oracle the default is that a BEGIN / END block is a single atomic transaction.@David. Yes. Individual DML statements are rolled back. I don't often see GO statements in stored procedures...
Guy
2010-02-24 07:49:39
@Guy: I'd reverse the order of the two bolded comments -- I think the concurrency control differences are a bigger deal from an application architecture and design perspective, where as the other has a higher "gotcha" factor. Kinda like the gotcha factor where in Oracle, '' IS NULL whereas in SQL Server (and DB2, and everywhere else) '' IS NOT NULL.Also, in Oracle, one can issue COMMIT/ROLLBACK/SAVEPOINT within a BEGIN/END PL/SQL block. In Oracle, no DML statements or blocks or any units implicity commit themselves other than DDL forcing implicit commit before and after issuance.
Adam Musch
2010-02-24 14:40:32
@Adam +1 - Not much in it really. In Oracle you learn quite quickly about MVCC and implied transaction control that you almost forget about it. Only when you get the MS SQL and it does it different does this gotcha come up. Both are as devistating but in a way, the transactional differences will make your data logically corrupted whereas dirty reads will just give you "wrong" results. What would Tom Kyte say?
Guy
2010-02-24 18:01:44
@Guy: I'm talking more about how SQL Server programming practices (temp tables and frequent commits to mitigate the scarcity of locks and the effects of lock escalation) drive the application architecture. That's the sort of stuff that will drive you nuts when going between systems. An app "born on" SQL Server can run, if badly, on Oracle; an app born on Oracle going to SQL Server or DB2 could have huge concurrency problems necessitating a rearchitecture.
Adam Musch
2010-02-24 20:31:32
MVCC force programmer discipline ) easier to bollix up. SQL Server/Sybase lock strategy is safer by default. As a SQL Server Dev DBA, read consistency is kinda pointless: I can wait until the write has finished to get latest data...
gbn
2010-02-24 21:16:56
@Adam. Agreed, in SQL you have to work around the DB to get stuff to work. My life is as a DBA so data consistency is paramount.@gbn. Lol! With Oracle's MVCC you ALWAYS have the latest COMMITTED data available AT THE TIME THE TRANSACTION STARTED.
Guy
2010-02-25 09:30:08
@gbn: I can't tell if that's British understatement for effect or not. Read consistency is *everything* -- phantom reads are evil, and serialization (which is what read locking introduces if you don't want phantom reads) is nearly as evil. In Oracle, you can read the data as it was (read committed) or SELECT FOR UPDATE if you want to guarantee no one's changing your data. In SQL Server/Sybase/DB2, the read-committed behavior blocks updates. And don't even get me started on lock escalation.
Adam Musch
2010-02-25 17:18:19
But if the update is short and sweet, only locked for as long as necessary there should be no issues. And what about this example http://en.wikipedia.org/wiki/Snapshot_isolation Each way has their pros and cons, but i feel the lock based way is more intuitive and *safer* be default. A poor Oracle dev and screw up more than a poor SQL Server dev..., no?
gbn
2010-02-25 17:30:20
Ah, sweet if, master of the simplifying assumption. What if the update isn't short and sweet? And what if the change volume is high? The lock-on-read-interest vs. lock-on-change-interest introduces, as it must, scalability concerns which must be coded around (rows-per-page 1, for example, or other data isolation techniques which are inefficient) or those scalability concerns are ignored through toleration of dirty (and thus possibly phantom) reads.
Adam Musch
2010-02-25 18:35:13
If change volume is high, you push load into either the row versioning system or the locking system depending on the engine used. The choice of engine merely shifts the load around: it's all a compromise... By default in SQL Server, you don't have dirty reads: http://msdn.microsoft.com/en-us/library/ms189122(SQL.105).aspx
gbn
2010-02-25 21:00:15
@David Parvin - ABSOLUTELY NOT!! GO has **NOTHING** to do with transaction/commit. It is only a batch separator. _Individual_ statements are implicitly bound within a transaction, but you can have _multiple_ statements in a batch.
Craig Young
2010-04-14 14:34:08
SQL Server Management Studio (2008 SP1) is fantastic? Sometimes it crashes.
AlexKuznetsov
2010-04-19 21:51:43
@AlexKuznetsov, In my experience 2008 SP1 seems to be much more stable than 2005 or 2008 RTM SSMS. It can "fall asleep" for 30secs (not responding) but it's never full out crashed on for a few months. (Windows 7 x64). YMMV. :)
Guy
2010-04-20 06:15:46
+1
A:
If you can handle Oracle, you can easily handle SQL Server. Yes the syntax is different, but it is relatively easy to learn the differences. The database concepts of joins and grouping and aggregates, relational design, PK/FK etc are pretty much the same even when the syntax is slightly different. Just highlight your general database knowledge. If they ask specific syntax questions and you don't know the answer for SQl Server, tall how you would do it in Oracle and note that the syntax might be different in SQL server.
One area I have seen where there is a difference is that Oracle performs better using cursors than SQL Server and some people coming from the Oracle world try to do things in cursors when they shouldn't in SQL Server. If you are used to doing row-by-row in Oracle, learn to think in the set-based fashion instead.
You have execution plans and profiler available to help performance tune. Look these up, I'm sure Oracle has similar tools called something different. If you can show you are aware of some of the things you will have to adjust to coming from an ORacle environment, I think that gives you an edge in the interview over someone who hasn't us they aren't happy with the SQL server people whose resumes they got.
From everything I've seen admin is less complex on SQL Server, so again if you can handle Oracle, SQL Server will be easy.
HLGEM
2010-02-23 22:57:39
+3
A:
I think just be honest. Dishonesty kills you in a job interview and (no offense), but if you only have 1 year Oracle experience and you didn't lie on your resume then you shouldn't have too much to worry about they won't be expecting you to have "all the answers".
Skyguard
2010-02-23 23:25:47
+2
A:
Honestly the biggest difference I've most often encountered is that Oracle is case-sensitive and MS SQL is case-insensitive by default. It leads to more gotchas and bugs than you'd think!
Coxy
2010-02-24 02:36:15
+2
A:
Oracle strengths:
- a better transaction system
- packages
- Cursor For Loops
- anchored declarations (variables declared as table.column%type)
- initial values for variable declarations
- %rowtype variables
- much lower overhead for cursors
- BEFORE triggers
- FOR EACH ROW triggers
- While sequences require either discipline or before each row triggers, they are more flexible than SQL Server identity columns.
SQL Server Strengths:
- Transact-SQL is just one language, so you don't have to worry about what's SQL, what's SQL*PLUS and what's PL/SQL.
- Because T-SQL is just one language, the T-SQL collections actually work decently with SQL. You can join T-SQL table variables to real tables. This tends to mean, while PL/SQL is more powerful for procedural programming, you just don't need to do procedural programming in T-SQL.
- If you perform a select query with no target, the results are automatically returned to the client. For production code, this means you don't need to declare and pass sys_refcursor. For ad-hoc research work, this means you can easily make scripts that perform lookups and display multiple recordsets.
- SQL Server Management Studio is much better than SQL*Plus or SQL Developer. Because it just displays any returned recordsets, data retrieval procedures are very easy to test.
- easier client connectivity setup (nothing as bad as tnsnames)
- less confusion about what drivers to use, apart from JDBC
- Declare a column "Int Identity Not Null Primary Key" and then you can forget about it.
- Every variable name starts with an "@" sigil, which looks terrible, but prevents name collisions between variables and columns.
- The case you declared a table or column with will be remembered, but it's not case sensitive, and you aren't limited to 30 characters.
- Crystal Reports can call SQL Server stored procedures, where you tend to be forced into a view with Oracle.
PstScrpt
2010-04-19 21:27:42