Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Picking up Oracle development skills Expand / Collapse
Author
Message
Posted Monday, February 9, 2009 4:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 3:02 AM
Points: 163, Visits: 171
Thanks for all the help people, keep it coming!

:)
Post #653304
Posted Tuesday, February 10, 2009 4:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
Bruce W Cassidy (2/9/2009)Try creating an index on a bit field in SQL Server 2005. Yes, it works.


Unfortunatelly that's not the concept of an Oracle bitmap index :D

Oracle bitmap indexes are built to cooperate with each other allowing you to identify the desired rows using several criterias -bitmap indexes - at once. Each index is represented as a bitmap and boolean math allows to cross reference several of them to identify what you are looking for. ;)


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #653551
Posted Tuesday, February 10, 2009 12:16 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
PaulB (2/10/2009)
Bruce W Cassidy (2/9/2009)Try creating an index on a bit field in SQL Server 2005. Yes, it works.


Unfortunatelly that's not the concept of an Oracle bitmap index :D

Oh, this I know. An index of a bit (or even multiple bits) does not a bitmap index make.

Actually, the form of index I miss most from Oracle is being able to index expressions. You can sort of do this in SQL Server by creating a derived column, and then indexing the column. In both cases there are limitations to what can be indexed and what can't. But the Oracle form is very handy at times.
Post #654097
Posted Wednesday, February 11, 2009 12:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 8, 2012 2:57 AM
Points: 66, Visits: 433
I'd like to add my favourite SQL Server vs Oracle link.

Gints Plivna
http://www.gplivna.eu
Post #655170
Posted Wednesday, February 11, 2009 8:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 3:02 AM
Points: 163, Visits: 171
Brilliant, thanks Gints! That's exactly what I'm after!

Sam
Post #655371
Posted Thursday, February 12, 2009 7:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:17 AM
Points: 277, Visits: 512
Sam Peascod (2/11/2009)
Brilliant, thanks Gints! That's exactly what I'm after!

Sam


Wow. That page is wildly wrong in many cases. It's written by an Oracle User group, and really doesn't know all SQL Servers capabilities, and is wrong about some.

Take with a grain of salt. It's a good list of oracle capabilities, but it whiffs on SQL equivalents.
Post #655707
Posted Thursday, February 12, 2009 8:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 8, 2012 2:57 AM
Points: 66, Visits: 433
jgrubb (2/12/2009)
Wow. That page is wildly wrong in many cases. It's written by an Oracle User group, and really doesn't know all SQL Servers capabilities, and is wrong about some.

Take with a grain of salt. It's a good list of oracle capabilities, but it whiffs on SQL equivalents.


It would be nice if you could list at least some of these "many wrong cases". I suspect this might be possible, yes, because it really was written by more Oracle oiented person. But in fact I've never seen any comparison completely fair for both involved sides


Gints Plivna
http://www.gplivna.eu
Post #655732
Posted Thursday, February 12, 2009 9:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:17 AM
Points: 277, Visits: 512
gints.plivna (2/12/2009)
jgrubb (2/12/2009)
Wow. That page is wildly wrong in many cases. It's written by an Oracle User group, and really doesn't know all SQL Servers capabilities, and is wrong about some.

Take with a grain of salt. It's a good list of oracle capabilities, but it whiffs on SQL equivalents.


It would be nice if you could list at least some of these "many wrong cases". I suspect this might be possible, yes, because it really was written by more Oracle oiented person. But in fact I've never seen any comparison completely fair for both involved sides


How about SQL 2k8 doesn't support table compression? Nvarchar limited to 8K? 0 XML handling functions? Too many to go on about.;
Post #655867
Posted Thursday, February 12, 2009 1:04 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031

Okay, I could spend more time on this and reference Books Online, but I'm just going to give it a quick once over and list anything I have queries about.

Are extents always 64k? I thought this could be changed (not that I have ever tried to do so.)
Tempfile in Oracle is kinda equivalent to tempdb. Kinda.
Default tablespace in Oracle is kinda equivalent to [PRIMARY].
RAC: you can do similar sorts of things with federated servers.
SQL*Loader: bcp/SSIS. bcp is less capable than SQL*Loader, SSIS is far more capable.
Oracle Clusters: this is some stuff that's been hanging around in Oracle since at least version 6. I don't know of anyone who uses them. They just make life very complicated. It's a way of merging the storage of overlapping tables. When disc space was a premium, it made sense. These days, use data compression.
Validate/NoValidate: I think there's a SQL Server equivalent to this when you create a constraint to get it to check the data?
REF: why do we need to create hierarchical data stores in a relational database? Grrr. Anyway, you could possibly do some of this with a SQLVariant.
Dimension: Oracle has a chunk of data warehousing stuff built into the RDBMS. For SQL Server, you need to step outside into Analysis Services to get the equivalent.
Parameters: OUTPUT parameters in SQL Server are the equvalent to IN OUT in Oracle.
By default, indexes in both Oracle and SQL Server are B*Tree. You don't actually specify "B*Tree" in Oracle.
Compressed Indexes: is this available in SQL 2008? I know data compression is.
They haven't shown anything for SQL Server's INCLUDE() as part of an index.
Nothing about XML indexes (which I believe you can do on both platforms.)
For Reverse, they are referring to Oracle's reverse key indexes, where it reverses the order of the bytes in the key before storing them. This reduces index hot-spots and contention in high TP scenarios. Mostly useful for non-clustered indexes. Oracle and SQL Server have very different space allocation mechanisms, so this really isn't as much of an issue with SQL Server.
Libraries: there's still (I believe) the concept of a library (DLL) within SQL Server, as distinct from an assembly. Not that I would recommend using them!
AUTHID: I believe this has some similarity to execute as.
Check constraints are deprecated in SQL Server? Who knew!
Identity vs Sequence: oh yes, we could have some arguments over this one! Having used both, I prefer identity. It's easy enough to build a table to duplicate Sequences if you really need them.
Public Synonyms: this just creates a synonym in the "public" schema. In SQL Server, just create your synonym then grant rights to public.
Compressed Table: some new stuff with SQL 2008 here (the article IS comparing to SQL 2005).
Function Based Index: it says the equivalent in SQL Server is Computed Column, but that's not quite right. You need to create an index over the computed column to get the same effect.
External Table: I'd say OPENROWSET() is an equivalent.
Global Temporary Table (on commit delete rows): #table is mostly equivalent.
Global Temporary Table (on commit preserve rows): ##table is mostly equivalent.
XML Table: in SQL Server, equivalent would be a table with an XML column.
PCTUSED, PCTFREE, INITRANS, MAXTRANS: heh. Hangovers from an older Oracle. Not even Oracle uses these (much) any more.
Types: Oracle types are far more capable than SQL Server types, unless you are willing to step outside and into .net assembly-based types. In which case, you can have all of the equivalents to Oracle's types.
Varray, a table variable is similar.
SQL Server has ! ? Who knew...
Date operators: er, dateadd(), datediff()? They're not operators, they are functions. You can use + and - with dates too (if you specifically want operators).

Should I keep going?

Post #656041
Posted Thursday, February 12, 2009 3:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:17 AM
Points: 277, Visits: 512

Please stop. The differences are legion. The page in question was written from an Oracle perception of SQL 2k8, and not an informed one. Usually, comparisons of DB platforms are done by people with a bias, intentional or not. This is not a surprise. DB's are now such beasts very few would ever get past the surface of more than one. I've been one of those, and bear the scars.

As I said earlier, the details of the differences are too much for this forum. The biggest thing to remember is that for capability, there isn't a huge difference, and that it is more perception than reality.

When it comes to migrating between the platforms, tables can be translated, and the CRUD statements are fairly similar. Just need to map various functions for aggregates and such. Complex SQL is a different story

When you get into code (PL/SQL vs Transact SQL), there's little that's portable. Completely different approach.

Post #656153
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse