Forskel på MS-SQL og Oracle

Der er meget stor forskel på MS SQLServer og Oracle DB fra en DBA synsvinkel.

Forskellen set fra en programmør synsvinkel kan være nul (hvis man holder
sig til helt standard SQL) eller generende (hvis man har brugt en del
ikke-standard SQL features).

Som du selv har fundet ud af så er der forskel på SQL dialekterne.

Stored Procedures er et f de områder hvor der er forskelle.

Jeg må melde pas til en komplet eller ihvertfald stor liste
over forskellene.

Ved du om SCOPE_IDENTITY() eller @@IDENTITY er T-SQL funktioner som ikke kan benyttes andre stedet?

Tror faktisk kun at jeg benytter ISNULL og SCOPE_IDENTITY() hvor jeg er i tvivl om det er t-sql specifikke funktioner jeg tager i brug.

Jeg har lavet en del konverteringer mellem Oracle og Microsoft SQL Server - og der er nogle forskelle. Har dog aldrig fundet en komplet liste noget steds:

Min erfaring kort (og jo, en programmør kan hamre rent ind i disse forskelle hvis han/hun ikke forstår database platformen - og så er det DBA'en som skal til at holde grundkursus og forklare...) :

En meget stor er , at Ms SQL Server benytter en helt anden låsemekanisme end Oracle, men hvis du kender den gamle RDB (Købt af Oracle og lanceret som Oracle RDB) så forstår du hvordan MsSQL håndterer låse.

Kort sagt: En opdateringslås i SQL Server er blokerende for læsninger modsat Oracles låse. Derfor skal du holde meget korte transaktioner på MsSQL.

Default på MS er hvad Oracle ville kalde Autocommit.

Triggere på MS SQL er "kun" AFTER STATEMENT triggere. Oracle har flere typer (BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW og AFTER STATEMENT).

Ms SQL procedurer kan returnere et recordset uden fixfaxerier - det kan man ikke på Oracle.

PL/SQL understøtter Exception Handling - MS T-SQL gør ikke (desværre!). Reelt set er T-SQL ikke et nær så prof sprog som PL/SQL - Og MS ved det og retter op på det i næste version :-)

Cascade update/delete på Oracle kan have flere stier mellem to tabeller eller virke på egenrelation - det kan de ikke på SQL Server.

Den grundlæggende opbygning af database motoren gør, at MS SQL db'en er at sammenligne med en mellemting mellem et Oracle Skema og en instans.  Dette er en styrke på nogle områder - en svaghed på andre.

Brugerstyringen i MS SQL er mere avanceret end Oracle på nogle punkter (DENY rettigheder - logon via domæne konto) - på andre er den svagere (ingen komplekse password krav etc). Administrative konti på SQL Server omgår al sikkerhed - det gør de ikke på Oracle.

Ms SQL styrer hukommelse dynamisk - Oracle har statisk hukommelsesstyring hvor DBA'en skal pille. MS SQL er væsentligt simplere at drifte.

Ouery Optimizeren / rewriter mv virker mere avanceret i Ms SQL end Oracles - f.x kan Ms SQL vurdere virkningen af flere indeks - Oracle kun af et indeks.

Standard værktøjerne (Enterprise Manager / Query Analyzer / Profiler mv) er bedre ved Ms SQL end Oracles (de har simpelthen aldrig formået at lave ordenlig kodning på klientsiden). På Oracle bør man bruge TOAD fra Quest Software.

Om du benytter det ene eller det andet er hip som hap i mange tilfælde - begge database leverer en høj ydelse og kan håndtere mange, rigtig mange data og brugere.

Men en intel basereret server ER bare svagere end en stor UNIX boks - så er det rigtig stort, vil jeg nok vælge Oracle på UNIX. Jeg har dog haft en MS SQL db med 900.000.000 (jep 900 millioner) rækker i en tabel - uden problemer! Det ene indeks der var på tabellen fyldte over 14 GB !

Sluttligt: Oracle på Windows er efter min mening noget l*rt - Så hellere på Linux eller brug Access :-)  Nej - skal det være en Windows server, så vil jeg anbefale MsSQL ...

Og -  vigtigt; I Oracle benytter man datatypen VARCHAR2 og aldrig VARCHAR. Oracle har nemlig et "problem" med tom streng '' og NULL som den sidestiller (altså '' = NULL).  På sigt! vil VARCHAR i Oracle komme til at håndtere '' og NULL korrekt så '' <> NULL.

På SQL Server er tom streng allerede forskellig fra NULL.

Den detalje kan give nogle sjove udfald hvor where betingelser opfører sig meget forskelligt!

Sidst; Oracle har PRAGMA transaktioner - dvs. du kan lave separate sub-transaktioner i en stor transaktion - det kan man ikke på SQL Server.

Jeg mener bestemt at SCOPE_IDENTITY() er TSQL specifikt.

Stort set alle non-sql funktioner du kalder er specifikke for hver enkelt platform.

Du har ikke IDENTITY kolonner på Oracle som på SQL Server - på Oracle vil du benytte en SEQUENCE som skal "programmeres".

Du skriver at du benytter ISNULL() - vær opmærksom på det jeg skrev med tom streng!

Tak for guldkornene begge to, har lidt at skrive om nu :)

Hvis nu man forstillede sig at jeg gerne ville undgå at binde mig til en specifik SQL server type, hvad skulle denne SP så omskrives til?

CREATE PROCEDURE addBug (@BugTypeID integer, @BugDescription text, @ProjectID integer, @CustomerID integer)
AS
INSERT INTO tblBugs VALUES(@BugTypeID, @BugDescription, NULL, '1', CURRENT_TIMESTAMP, NULL, '1', @ProjectID, @CustomerID, '0')
SELECT BugID FROM tblBugs WHERE BugID = SCOPE_IDENTITY()

ok, så ikke dine svar imens jeg postede dette. Dvs. at det ikke er en nem opgave for en SQL nybegynder at gøre det i oracle.

stored procedures er altid server specifikke fordi flere af de meget
brugte databaser ikke supporterer dem.

Hvis du vil lave det samme som en IDENTITY kolonne og @@IDENTITY/SCOPE_IDENTITY()
er du nødt til at flytte logikken fra database over i applikationen.

Det er rimeligt standard at generere unikke nøgler. Søg på Scott Ambler
High Low approach.

ISNULL() findes slet ikke i oracle vel?

"er du nødt til at flytte logikken fra database over i applikationen."

Kun delvis - du bør generere din unikke nøgle via en SEQUENCE. 

Og ISNULL(kolonne,erstatningsværdi) bliver til NVL( kolonne, erstatningsværdi) i Oracle.

Og din procedure vil være temmelig svær at lave i Oracle da du returnerer en SELECT.

Der findes workarounds hvor du returnerer en cursor reference, men jeg tror desværre det performer dårligere end MS SQL's metode...

Søg på google på PROCEDURE ORACLE RECORDSET REF CURSOR - så burde du få nogle ideer...

Nej - man skal skifte fra identity til squence hvis man vil ændre sin app fra
SQLServer specifik til Oracle specifik - man skal flytte logikken til app
hvis man vil være database uafhængig.

for lige at være sikker på om jeg har fattet null problematikken rigtigt..

man bør ikke benytte varchar i oracle da der er problemer med at null='', men varchar2 i stedet (varchar2 findes slet ikke i MS-SQL vel?).
Når jeg nu benytter varchar2, så vil NVL() i oracle give samme resultat som ISNULL() med varchar i MSSQL?

Har det her noget sammenhæng med hvorfor dataSet.GetXml() i c# ikke returnere de elementer som indeholder en null værdi?

Oracle har både VARCHAR og VARCHAR2.

Uanset hvilken du benytter kan Oracle ikke se forskel på NULL og tom streng.

Men du *bør* angive kolonne typen som VARCHAR2, grunden er da VARCHAR *på et tidspunkt* vil skifte implementering så den overholder standarden - altså at NULL <> '' - for at undgå problemer senere anbefaler Oracle at man aldrig benytter VARCHAR

Og det betyder at du skal skrive din kode således at den ikke giver forkerte resultater når Oracle ikke ser forskel på tom streng og NULL værdier.

Fx

.. where kolonne is null

i SQL Server vil kun returnere rækker hvor kolonne er NULL
i Oracle vil returnere rækker hvor kolonne er NULL eller tom streng.

Hvis du vil have SQL Server til at opføre sig som Oracle skriver du

.. where isnull(kolonne,'') = ''

Hvis du vil have Oracle til at opføre sig som SQL Server skriver du

... et klagebrev til Oracle - for du kan ikke :-)

SQLServer 7.0 online books har faktisk en lille side
"Data Types in Oracle and SQL Server", som fortæller lidt om
disse ting (selvfølge med et Oracle->SQLServer perspektiv !).
Men den er vist faldet ud af SQLServer 2000 versionen.

trer>

Og så er der vist ret langt mellem dem der har arbejdet med DEC RDB / Oracle RDB.

Men:

$ mcr sql$ 
SQL> show version
Current version of SQL is: Oracle Rdb SQL V7.0-6

arne_v> Ah ja.. Oracle RDB kender jeg da nogle stykker der har arbejdet med (mig selv inkl. omend det var på et meget overfladisk niveau :-) og på den gamle RDB kender jeg vist kun en enkelt...

Men mange Oracle folk kender RDB - i hvert fald af omtale - og de fleste migreringer jeg kender til har været fra Oracle til SQL Server, så det var egentlig det der var mit udgangspunkt...

arne_v> Ser nu dit svar mht sequence / identity - du har ganske ret. Eneste er, at performance bliver dårligere når man gør den slags...

Rent performance mæssigt: Skal man lade klienten lave en unik nøgle bør man ikke basere den på nogen form for max(kolonne)+1 - man bør i stedet generere en GUID (Global Unique Identifier) eller tilsvarende unik værdi da man så ikke skal starte med at få en maks værdi fra databasen.

GUID åbner så op for en anden problematik - nemlig at værdien er kompleks og ikke nødvendigvis nummerisk stigende samt at den typisk fylder ret meget (16-32 bytes)

Nå, det er vist bare min sædvanlige performance kæphest :-)

Eneste grund til at jeg benytter ISNULL er for at udskifte alle null værdier i mine select forspørgelse til tomme strenge, da dataSet.GetXml() dræber kolonner med null værdier. Men jeg vil meget gerne se disse alligevel, enten som en tom streng eller med null som værdi. Jeg bruger den ikke til at sammenligne værdier.

Så for at få oracle til at æde:

SELECT BugDescReply ISNULL(tblBugs.BugDescReply,'') AS BugDescReply FROM tblBugs

kan jeg skrive den om til dette:

SELECT BugDescReply = CASE WHEN BugDescReply IS NULL THEN '' ELSE BugDescReply END FROM tblBugs

Er der nogen forskel på disse to sætninger?

Hvis man gør det forkert bliver det langsommere.

Hvis man gør det rigtigt bliver det hurtigere.

[og jeg kunne ikke drømme om at anbefale MAX+1 til noget som helst]

De færreste uid generatorer inkl. high low kan lave kontinuerligt
stigende.

Men hvis man kan leve med 66536 blokke af 65536 så kan high low
godt generere 4 byte integer uid'er.

Som jeg læser trers svar er:

SELECT ISNULL(BugDescReply,'') FROM tblBugs

det samme som:

SELECT NVL(BugDescReply,'') FROM tblBugs

yep, det var også min konklusion, men med:

SELECT BugDescReply = CASE WHEN BugDescReply IS NULL THEN '' ELSE BugDescReply END FROM tblBugs

ville jeg lave noget der kunne bruges i begge databaser, men der er så meget der skal laves om alligevel hvis man vil have mine ting til at være generelle, så det kan næsten være lige meget hehe. Men jeg har da i det mindste lært en del af dette.

Jeg ved ikke om den CASE virker i begge.

Men et alternativt forslag:
  samme kode
  koden bruger en userdefined function
  den implementeres så både i SQLServer og Oracle - bare forskelligt

CASE fungerer kun i Oracle 9i og frem - men den vil sandsynligvis ikke æde din syntaks da den er "forkert" dvs non-standard.

Standard er:

SELECT CASE WHEN BugDescReply IS NULL THEN '' ELSE BugDescReply END as BugDescReply FROM tblBugs

Performance mæssigt vil jeg tro at ISNULL / NVL er en del hurtigere end en CASE - men jeg har faktisk aldrig lavet en test på det.

Jeg lavede lige en lille test på SQL Server hvor jeg sammenlignede eksekveringsplanerne for de to queries både med og uden indeks på kolonnen bugdescreply.

ISNULL / CASE er ser ud til at være lige hurtig på den platform - det kan skyldes at Query Rewriteren i SQL Server 2000 er så kvik at den forstår hvad man prøver på.

Jeg har *ikke* testet det samme i Oracle.

Men jeg vil generelt anbefale at bruge ISNULL/NVL - det er mere gennemskueligt hvad man laver...

Mange tak for alle de gode informationer!

Smid et svar arne hvis du vil dele points med trer, ellers giver jeg dem til ham senere når jeg kommer hjem.

ok

(men jeg synes faktisk at Trer har hjulpet dig mest)

Jeg synes det er helt fint at dele points. Arne var bl.a. først på pletten med en fin opsummering af forskellene + min tilgangsvinkel var forkert da jeg tænkte Oracle -> SQL Server og ikke SQL Server -> Oracle.

Lige en sidste ting, jeg synes at læse at PL/SQL kan håndtere arrays i modsætning til T-SQL, ville jeg være i stand til at lave en SP i PL/SQL som tager et array som parameter?

Jeg kan ikke huske om du kan bruge array datatypen som parameter i en procedure på Oracle - men du kan i hvert fald lave den som en "global" variabel i en pakke.  Ved ikke om det er svar nok.

Hvis du kun skal have flag eller små numeriske værdier i dit array kan du fake et array i SQL Server ved at benytte en BINARY parameter.

I samme stil: En anden ting som du kan på Oracle er (og vist ikke på andre databaser), at du kan lave en kolonne definition som en subtabel. Rent relationelt set er det noget forfærdeligt lort, men der er nogen der mener det er praktisk...

Er faktisk noget du har hjulpet mig med en gang før i et andet spørgsmål, men det står ikke helt klart :)

Lige nu har jeg en stump c# kode der ser sådan her ud:
   
    if(attach!=null)
    {
      //Adds the array of attachments to the database
      for(int index=0;index<attach.Length;index++)
      {
        cmd = new OleDbCommand("addAttachment", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@BugTypeID", OleDbType.Integer).Value=bugID;
        cmd.Parameters.Add("@BugAttachment", OleDbType.VarBinary,
            attach[index].Length).Value = attach[index];

        cmd.Parameters.Add("@BugAttachmentFilename", OleDbType.VarChar).Value =
            BugAttachmentFilename[index];

        cmd.Transaction = trans;

        cmd.ExecuteNonQuery();
      }
    }

Dvs. at jeg har et array indlæste filer (byte[][]) som skal ned i databasen, lige nu smider jeg dem bare ind enkeltvist ved at kører den samme SP som tilføjer en enkelt fil per gang, dette bliver så gjort i en for-løkke lige så mange gange som jeg har filer. Jeg mener at dit forslag var så enten at lade det være som det er (som er det jeg gør), eller angive en masse parametre til SP'en som jeg mener var max antallet af filer jeg vil tilføje i den SP.

Så det jeg mente med spørgsmålet var egentligt om, hvis jeg havde brugt en Oracle server, om jeg så kunne have kommet alt logikken ind i en SP, ved at den tog imod et array af disse binære filer. Eller om jeg under alle omstændigheder skulle have haft det i stil med hvad jeg har allerede.

Ah ja, det var et array af blob (image) felter du havde brug for. Det *tror* jeg heller ikke du kan lave på Oracle - men jeg er dig svar skyldig.

Jeg har faktisk aldrig set Oracles array type brugt på andet end number (integer/numeric) og varchar / char...

Okies, tak for alt den gode hjælp. Kommer nok tilbage og opretter et nyt spørgsmål om samtidighed senere, når jeg har tænkt lidt over dette problem.