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

working with union all or union Expand / Collapse
Author
Message
Posted Wednesday, January 21, 2009 7:02 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 1:10 PM
Points: 109, Visits: 79
hi people, suposse i've query:

CREATE TABLE [dbo].[MyTest3](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

after

insert into MyTest3
select 1 , 'John' , 'Smith' , 150000.00
union select 2 , 'Hillary' , 'Swank' , 250000.00
union select 3 , 'Elisa' , 'Smith' , 120000.00
union select 4 , 'Liz' , 'Carleno' , 151000.00
union select 5 , 'Tony' , 'Mcnamara' , 150300.00

What´s difference between This code: ??

insert into MyTest3
select 1 , 'John' , 'Smith' , 150000.00
union ALL select 2 , 'Hillary' , 'Swank' , 250000.00
union ALL select 3 , 'Elisa' , 'Smith' , 120000.00
union ALL select 4 , 'Liz' , 'Carleno' , 151000.00
union ALL select 5 , 'Tony' , 'Mcnamara' , 150300.00

i'm lookup Plan Execution, using only 'union' i'm think then has more cost... and union all is less.

Post #640674
Posted Wednesday, January 21, 2009 7:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
Here is a simple illustration. Run the following:

SELECT 1,'My Test 1'
UNION SELECT 1,'My Test 1'
UNION SELECT 2,'My Test 2'

SELECT 1,'My Test 1'
UNION ALL SELECT 1,'My Test 1'
UNION ALL SELECT 2,'My Test 2'


You will notice that the UNION groups by all fields and the UNION ALL does not (it will return a duplicate row). So, UNION needs to collect everything and group it together to remove the duplicates. Because of this it will always have a higher cost.
Post #640721
Posted Thursday, January 22, 2009 5:28 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 1:10 PM
Points: 109, Visits: 79
ok, but this example, the query using union all more better union do you think ?
Post #641558
Posted Thursday, January 22, 2009 5:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 42,468, Visits: 35,537
lucassouzace (1/22/2009)
ok, but this example, the query using union all more better union do you think ?


In your specific example, because there are no overlaps (rows in more than one of the resultsets) union all is better as it won't require a distinct sort. In general, it depends on whether or not there's a chance of overlapping rows and, if there is a chance, if you want the duplicates removing or not.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #641573
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse