[RESOLVED] UNION isn't returning what I thought it would (SQL Server)

[MMock]

I run a query and get back four rows. I run another query and I get back four rows. I want to get the eight rows at once, so I unioned them and I got...four rows. Does that make sense?

I have health care providers in pending tables (tblPendingXXX) and health care providers in non-pending tables (tblXXX). The providers have a fax number which is one of the columns I am selecting. So if I run the query against the pending tables I get four rows; one of them has a unique fax number and three of them have the same fax number. I am also selecting a hard-coded "pending" as one of the columns.

It just so happens (I am finding out if this is bad data) that the same provider also exists in the non-pending table. So when I select him there I get the same four rows but I am selecting a hard-coded "edit" as one of the columns. (edit and pending will be links on the grid when it is rendered on the web page).

So far we have four pending rows and four edit rows.

When I run these queries together with a UNION, I get four rows. Fax aaa and edit, fax bbb and edit, fax aaa and pending, fax bbb and pending. Which I don't quite understand. Why do they return 8 rows total separately, but 4 rows total when unioned?

[szlamany]

UNION is distinct by default

UNION ALL should give you what you want.

Actually it's been said that UNION ALL is faster in the fact that it doesn't have to consider a "distinct" pass on the result set.

[MMock]

So sorry - I've since discovered an important keyword - ALL!

[MMock]

As Steve also pointed out, before my refresh...

[MMock]

Now I realize (now that the data have been explained to me) that I want to do the exact opposite of a UNION. I want to do an ANTI-UNION.

The pending tables are a subset of the active tables. So every pending provider will also have an active record (or maybe "will" is too optimistic of me - there are three who don't). If he has a pending record it means he's pending. If he doesn't have a pending record it means he's active and editable.

So I have to think about how this affects my query and whether I return pending/edit. If the search criterion is last name = Smith I have to return "edit" for all the active Smiths but "pending" for all the pending Smiths. I am not sure how to select from the active table only if they're not in the pending table. Or would I just select from the active table then see if they're in the pending table and update the status...

[szlamany]

You can take the easy way out and put the UNION ALL into a temp table (or sub-query) and SELECT from that...

Or use:

Select * From Active A
   Where A.Name='Smith'
Union All
Select * From Pending P
   Where P.Name='Smith'
      and Not Exists(Select * From Active A1 Where A1.CustId=P.CustId)

Your WHERE clause can be made "smart" enough to know if a record should be pulled from Pending or not...

[MMock]

What I decided to do (not that I disregarded your advice but I was 20% into this solution when you posted and I believe they are very similar anyway):

1. I select into a temporary table all records from tblPendingX that match my criteria. These are action = "pending"
2. I select all records from tblX that aren't in my temp table that match my criteria, label them action = "edit" and UNION ALL with my temp table.

[szlamany]

Multi-step operations are ok - just put a BEGIN TRAN / COMMIT at the top and bottom of the SPROC to make sure you stay atomic.

Although I feel a SPROC is atomic in nature naturally - when I do a multi-step operation where concurrency can burn me I always put the BEGIN TRAN / COMMIT in.

It is safer to do it in a single SELECT / UNION ALL with smart WHERE clause - as a single SELECT is as atomic as you can get.

[MMock]

Okay, I understand what you are saying, I just thought I was saving time building the temp table rather than executing the same Select again.

Also, the use of this query is by from the user interface when a user wants to call up records for further processing. So if it crashed midway, they'd be starting over anyway? Or there's a chance they wouldn't know of a crash and would be processing partial data?

[szlamany]

Sorry - atomic was a misstatement...

The begin tran/commit is to insure isolation (the third letter of ACID) - which means that no one is sneaking in and "making active" a pending row while you are in the midst of considering it's active/pending status.

Not to protect against a crash - I would not imagine a sproc doing a simple couple of select's is going to crash.

[MMock]

Not unless a squirrel is chewing through the power cord at the time, but even given the likelihood of that happening, you can be sure it would be during my stored procedure!

Anyway, got your point. Thanks.

[MMock]

Multi-step operations are ok - just put a BEGIN TRAN / COMMIT at the top and bottom of the SPROC to make sure you stay atomic.

Can we revisit this again? Can you explain exactly why you recommended it (realizing you had changed "atomic" to "isolation")? I had to take it out, per the advice of the senior s/w egr with whom I am working on this project.

Often when I'd be closing SSMS at the end of the day, or even one of the windows where I'd been running my sprocs it would tell me I had uncommitted transactions. (I believe that was the verbage). So I just said commit or ignore or whatever the choices were. But then we needed maintenance done on the DB, and the DBA said my machine was still connected, even though everything was closed.

So my boss emailed me: can you check your search query code for instances where it is locking the DB and not releasing?

To which I replied: I am doing begin tran/ end tran statements around my selects. It might be because of that – I was getting messages saying the transaction hadn’t completed or something like that, every once in a while. I will remove them. They were put in for “isolation” – perhaps they are unnecessary, but if they are necessary we need to determine if they’re locking the db somehow.

To which the Sr S/W Egr replied: We don’t need tran in your search queries. That will cause problems if we have them.

Which has now made my code suspect and it has been targeted for a code review (which of course we should be doing anyway, but we don't, and even though they said we are going to, they will probably decide they don't have time. They also said we are going to do a code review of the consultant's MVC code, perhaps he is suspect simply because he's a consultant, but we are not reviewing the Sr S/W Egr's code, which is unfortunate, because although I'm sure it is 100% perfect and above criticism, it'd be nice for us mistake-makers to learn from it (yes, you do detect bitterness in that statement)).

So I'd just like to know why you think they are necessary and if you have any comments to make on the scenarios we experienced, and if perhaps I implemented it wrong. Here is the basic skeleton of my code:

ALTER PROCEDURE usp_Search
	@parameters...
		
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

BEGIN TRAN was here

SELECT 
	columns
	
	INTO #tblTempPendingRecords
	
	FROM pending-tables
	
	WHERE criteria
	
SELECT 
	columns
	
	INTO #tblTempRecords
	
	FROM regular-tables
	
	WHERE criteria 

IF @SearchType = 'ALL'
BEGIN
	SELECT * FROM #tblTempPendingRecords

	UNION ALL
	
	SELECT * FROM #tblTempRecords
END
ELSE	-- @SearchType = 'EXACT'
BEGIN
	DECLARE @CountOfRecords INT;
	
	-- Want to ensure there's only one record in both tables
	SELECT * INTO #tblAllRecords FROM #tblTempPendingRecords
	INSERT INTO #tblAllRecords SELECT * FROM #tblTempRecords 
	SELECT @CountOfRecords = COUNT(ID) FROM #tblAllRecords
	-- If there is only one record then it's verified and return the ID and the Name
	IF @CountOfRecords = 1
		SELECT ID, Name FROM #tblAllRecords
END

COMMIT TRAN was here

END
GO

[szlamany]

What I decided to do (not that I disregarded your advice but I was 20% into this solution when you posted and I believe they are very similar anyway):

1. I select into a temporary table all records from tblPendingX that match my criteria. These are action = "pending"
2. I select all records from tblX that aren't in my temp table that match my criteria, label them action = "edit" and UNION ALL with my temp table.

This type of activity - in my mind - needs to be protected by being in a transaction.

See this MSDN link

http://msdn.microsoft.com/en-us/library/ms187926.aspx

It's a lengthy read - but you will see examples of BEGIN TRAN in places where multi-step "situations" are happening where an "ALL OR NOTHING" requirement is in place. That is ISOLATION - right?

Regular SELECT statements that are returning row-set data - do NOT need to be in a transaction.

When you close windows and it tells you that you had "open transactions" - well that means you ignored an ERROR of some sort in an earlier test of the query - and it never reached the COMMIT.

That is really really bad - for two reasons.

One - you are locking other people out of whatever resources your transaction is holding.

Two - you are now getting "bogus" results in your testing - because you have a partially "processed" transaction that you are "working" new queries against.

That second point - that's the really evil one.

Whenever I use a BEGIN TRAN/COMMIT or ROLLBACK in a query I get used to clicking the ROLLBACK word (selecting it in the SSMS query window) and EXECUTING it by itself so I can see it say

"...no corresponding BEGIN TRANSACTION"
...

[FunkyDexter]

I know I'm coming to this a bit late but I'd have achieved the original goal with a left join and case:-

Select Active.ID,
Active.Name,
Case When Pending.ID is null then 'Active' Else 'Pending' END
From Active
Left Join Pending
on Active.ID = Pending.ID

Not sure it's any better or worse than the other suggestions but it is an alternative.

As for the transaction discussion, if your SSMS is holding transactions open at the end of the day it's probably not to do with the transactions you're putting in your sprocs but more likely to be from ad-hoc queries you've run during the day where you've opened a transaction and then forgotton to close it. Or as Sz put it:-

When you close windows and it tells you that you had "open transactions" - well that means you ignored an ERROR of some sort in an earlier test of the query - and it never reached the COMMIT.

Whether you need a transaction in the scanario you describe really depends on the business. If for example, a provider become active while your query is running and you miss-report them as pending, does it matter? If it does then you need a transaction.


edit> And you can never trust squirrels. Although in your example it would have bought the whole server down so your open transactions are unlikely to be the biggest problem you'll have to deal with.

We used to have a rule that you were allowed to use transactions on the server (as long as they were properly managed) but never to open one from the client. The reasoning was that a server crash means you don't care about a left ofer transaction but a client crash could leave that resource open on the server. I'm not sure that's true but it was an easy enough rule to follow so I never felt the need to find out.

[MMock]

Ok - thanks for the explanation. If/when we have the w/t, I will point this out. If we never have the walkthrough, I will informally explain why I thought it was necessary.

[szlamany]

I'm right up the street - I'll stop in and set your sr s/w eng straight

Bottomline...

There is absolutely no "damage" caused by having a BEGIN TRAN / COMMIT in a SPROC.

If the queries can fail in production you need to handle TRY CATCH and ROLLBACK's - but that's required and obvious in any programming model.

Putting them in all SPROCS creates a testing and debugging nightmare (as you have personally experienced with holding transactions open).

But as FunkyDexter said:

If for example, a provider become active while your query is running and you miss-report them as pending, does it matter? If it does then you need a transaction.

If this is a business requirement for you to handle - then you need the BEGIN TRAN / COMMIT

btw - I did further research and did verify that a SPROC is not an implied transaction - you do not get an ALL-OR-NOTHING experience just because you have a dozen queries in that SPROC container.

[MMock]

I know I'm coming to this a bit late but I'd have achieved the original goal with a left join and case:-

Select Active.ID, 
Active.Name,
Case When Pending.ID is null then 'Active' Else 'Pending' END
From Active
Left Join Pending
on Active.ID = Pending.ID

Not sure it's any better or worse than the other suggestions but it is an alternative.

So you are suggesting I select all my columns from the active table, but set the action (Active or Pending) based on if there is a correspoinding row in the pending table.

I think I might give that a try. I am just wondering if it's possible to have different data in the pending table and the active table, and if it matters. For example, the first one I happened to pick to test with that has a row in both has a phone and fax in the actve table but those columns are null in the pending table. So I'd be displaying the record as "pending" but with data from the active table. Ideally, you'd think they'd be the same, but that is the problem with replicating data and why we don't do it, right?

[FunkyDexter]

So I'd be displaying the record as "pending" but with data from the active table. Ideally, you'd think they'd be the same, but that is the problem with replicating data and why we don't do it, right?

Right! (although replicated is probably the wrong word if we're being pedantic). There are damn good reasons why databases are normalised and this is one of them. Really pending vs activeshould be handled as a flag on the provider table but I imagine that's outside of your control to change.

Actually, this is one of the places where a join aproach has an edge over the union because you use an isunll to pick up whatever info is available. e.g.
select isnull(Active.PhoneNumber, Pending.PhoneNumber) from blah blah blah Of course, you'll jave to decide whether you want to trust the active or the pending info more and pick that one up first.


edit>

I'm right up the street - I'll stop in and set your sr s/w eng straight

I'd have to travel half way round the world but I'm still willing to turn up if I get to watch Steve explain transactions to a "senior" engineer using words of one syllable and, optionally, a bat.

[MMock]

If you knew this guy, any travel would be worth it to put him or see him put in his place. He's my boss's golden boy, and I'm not sure I get why.

[MMock]

Okay, I've rewritten some stuff and I'm really happy with it (and believe me, it takes a lot to make me happy as far as my code is concerned). Thanks to you both, I am going to shine in our walkthrough! I've really learned a lot from the threads I've posted in this forum ove the past couple of weeks. Thanks again and if I haven't rated you too recently in the past, I will do so now.