FYI - Interesting difference between Oracle SQL and MS SQL - MS SQL is wrong

While converting some code from T-SQL to PL/SQL, I ran across an interesting bit of MS SQL code.  The code looked odd to me so I did a quick experiment to prove what it did.  I was quite surprised by the MS SQL results!


create table testunion (aaa number);

insert into testunion
select 2 from dual
select aaa * aaa from testunion;

 select * from testunion;  -- Returns one record, as it should, "2"

delete testunion;

insert into testunion
select 2 from dual
union all
select aaa * aaa from testunion;

select * from testunion; -- Returns one record, as it should, "2"

In SQL Server (2000):

create table testunion (aaa numeric)

insert into testunion
select 2
select aaa * aaa from testunion

select * from testunion -- Returns one record, as it should, "2"

delete testunion

insert into testunion
select 2
union all
select aaa * aaa from testunion

select * from testunion -- Returns two records!  "2" and "4".

Oracle applies a concept known as "read consistency" to both halves of the union and union all statements.  Each of them only see the data that existed in the table at the time the overall statement started.

SQL Server only applies read consistency for the union, but not the union all. 

4/10/2009 9:20:27 AM 29906 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 47

Hi David,

Long time no see. Thanks for sharing. It is interesting to see the behavior in SQL Server 2000, but your sample will return the same thing as Oracle does in SQL Server 2005 and 2008.


4/10/2009 2:42:41 PM

Similar Artilces:

PL/SQL or Oracle SQL
I am mostly a SQL server user, but by chance used to use Oracle 7.3.4, 8.1.5 and now 9.2. In the current project, the client wanted us to write Oracle SQL instead of Stored Procedure (PL/SQL), and argued that since Oracle 8, the dynamic Oracle SQL string in the client code is as faster as a stored procedure. Actually, this kind of debate happened in the SQL server field. But, it seems in the SQL server field, most people prefer to use SP, not just due to the performance, but the considerations including code encapsulation, code re-use, less traffic, security, better management and so on. ...

difference between T-Sql and Sql,
Hi guys, I am very new to Sybase, can some one tell me the differences between T-Sql and Sql. Thanks in advance, Munish. I believe you are referring to: SQL--The standard SQL syntax used universally for most DBMS's T-SQL--Transact-SQL, Sybase's flavor of SQL the is used for writing stored proc's, etc. I supported variable declarations, functions and data-type conversion. -Dan ...

SQL A and SQL Server
Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL Anywhere, therefore, similar to SQL Server? -- Regards, John Not quite true. SQL Anywhere was created by Watcom. SQLServer was created by Sybase, and Microsoft licensed the technology. So MSSQL and ASE have common roots, not SQLAnywhere. -- Paul Horan[TeamSybase] "John Kingan" <> wrote in message news:40d82aa1$1@forums-1-dub... > Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL > Anywhere, therefore, similar to SQL Ser...

SQL Express to SQL ?
I am developing a site using the express edition of all tools (VWD and SQL Express).  I want to use GoDaddy hosting and was wondering if I will be able to effectively put my SQL database and website up on their hosting even though it was developed via Express edition of Microsoft programs?...

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

I have a problem, my page wants to connect to the sql server, but it has to be the mysql. How can I solve this problem? This is my code: <%@ page explicit="true" language="VB" debug="true" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.Odbc" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script runat="server"> sub page_load(sender as object, e as eventargs) If Not IsPostBack Then dim connect as sqlconnectio...

what's difference between Accesss DataBase and Sql Sever about Sql String(or Command),Sql StoreProcedure ?
i am being dirty to Access DataBase from Sql Sever Database for my new work.But the sql string of Access sounds like a little different,can i use  the sql string of SqlSever for Access database,too?another question is: Does Access DataBase support Sql StoreProcedure??? when i worked in Access2000 ,did not see any about StoreProcedure,but i download a file named AccessHelper.cs  , some function of it looks like support StoreProcedure,Does it?  i want to fly ... Hi dkzeng ,Your first question     The string of both sqlsever and acce...

Strange difference between SQL from .Net and SQL within stored proc
Hi, I am trying to fill a DataGrid with random records from my database. It thought I had found a way to randomly select records using SQL. So I built a stored procedure in Access and sure enough, it returns a random set of records. Whe I tried to call this from my app, it kept returning the same set of records. So I copied and pasted the SQL into the app (code below) but it made no difference. Could anyone tell me why this wouldn't work? <%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="...

what is the difference between InstallSqlState.sql and InstallPersistSqlState.sql?
i have the following questions  1> what is the difference between InstallSqlState.sql and InstallPersistSqlState.sql? 2> Both of these file comes with 1.1 and 2.0. what is the difference between those? 3> i have 1.1 application and 2.0 application which version of files i should use if i want share session information bwteen the 1.1 and 2.0 application. 4> if set Session mode in web.config to use SQLServer. is it possible to access that database/tables from classic ASP pages so that i can share session information with classic ASP also? First one is to install it on m...

SQL code to stop and restart sql server 2005 and sql agent
Hi. I would like to restore a SQL Server 2005 database from a backup file. The problem is that the users which are using the site, prevent the restore operation from completing. So, I stop the sql server by right clicking the server name in management studio and selecting stop, wait for 3-4 minutes and restart it by clicking start. Then I can restore. So I am looking for code that can do these operations (including the wait). let me just add that I am using a named instance - and I want to stop and restart the instance. Please help, thanks in advance. I found this link http://...

Announce: BNF Grammars for SQL-92, SQL-99 and SQL-2003
Hi Folks Jonathan Leffler has sent me these grammars, which I'm hosting at: -- Cheers Ron Savage, on 17/07/2005 Let the record show: Microsoft is not an Australian company At 11:08 AM +1000 7/17/05, Ron Savage wrote: >Hi Folks >Jonathan Leffler has sent me these grammars, which I'm hosting at: > Thanks for posting that; should be very useful. I will point out an apparent spelling mistake, though. See

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

sql server 2005 inserting prbblem..wrong SQL? wrong parameter?
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2) not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code:        Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _&nbs...

Is Oracle/SQL Server better than SQL Anywhere
Could anyone tell me if Oracle or SQL Server is better than SQL Anywhere? Thanks. That depends entirely on your criteria. Price, performance, supported platforms, developer/dba skillset, 3rd party application support etc etc. need to be taken in account. We use both Oracle and ASA in our shop. -- Terry Dykstra (TeamSybase) Please state PB / OS / DB versions in your post. SDN: "NG" <> wrote in message > Could anyone tell m...

any difference between sql anywhere and sql anywhere studio
Is there any difference between sql anywhere and sql anywhere studio? Thanks. >Is there any difference between sql anywhere and sql anywhere studio? The answer depends upon the context of the question. Years ago, "sql anywhere" referred to the DBMS that is now known as Adaptive Server Anywhere. Now, "sql anywhere" can mean a general reference to a set of products that includes ASA, as well as some other things. "sql anywhere studio" is a particular bundling of these products. Still, sometimes when people refer to "sql anywhere" t...

Web resources about - FYI - Interesting difference between Oracle SQL and MS SQL - MS SQL is wrong -

Magid: Precautions for holiday shopping
With the holiday season upon us, a lot of people will be shopping online this year. For the most part, it's pretty safe but there are some basic ...

Mad At All These Star Wars Spoilers? Install Star Wars Spoiler Blocker
There are two kinds of people: those who are sick of all this Star Wars news nonsense, and those who just want to avoid any spoiler before seeing ...

Barbra Accepts Medal Of Freedom From Obama, Gets In Great Dig At Trump
It’s safe to say that Barbra Streisand now has in her possession just about every award under the sun. The legendary entertainer has now crowded ...

Russian pilot killed when Turkey shot down jet over Syria pictured
Captain Konstantin Murahtin was saved by Russian military in a joint operation with Syrian government forces, and taken to a Russian air base ...

Bernie Sanders wants to raise wages of H-1B workers
U.S. Sen. Bernie Sanders (I-VT) wants to reform the H-1B program, in part, by "substantially" raising prevailing wages. Higher wages is one ...

Facts? Donald Trump Don’t Need No Stinkin’ Facts
In an exchange with Bill O’Reilly Monday night, Donald Trump summed up so much of his Presidential campaign in one short statement: File this ...

The trailer for Marvel’s ‘Captain America: Civil War’ is out, and it’s looking pretty amazing
And you thought sitting down with your family tomorrow was setting the stage for some epic internal battles. Marvel dropped the trailer for Captain ...

Macy's Thanksgiving Day Parade 2015
There’s just one day left until the 2015 Macy’s Thanksgiving Day Parade and we’re so excited about all the performers set to make appearances ...

Morning News: New City Council Member Lorena González Says She'll Fight for the Poor, King County Welcomes ...
"We will stand up for the poor, we will stand up for the underrepresented, and we will change the face of the table that represents power." Dan ...

19 people ill from E. coli linked to Costco chicken salad
People who bought chicken salad at any U.S. Costco store on or before Friday were advised to throw it away, even if no one has gotten sick.

Resources last updated: 11/25/2015 7:55:11 PM