InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 02:52:00 GMT20http://www.sqlservercentral.com/Forums/Topic640674-392-1.aspx[quote][b]lucassouzace (1/22/2009)[/b][hr]ok, but this example, the query using union all more better union do you think ?[/quote]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.Thu, 22 Jan 2009 05:42:58 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic640674-392-1.aspxok, but this example, the query using union all more better union do you think ?Thu, 22 Jan 2009 05:28:44 GMTlucassouzacehttp://www.sqlservercentral.com/Forums/Topic640674-392-1.aspxHere is a simple illustration. Run the following:[font="Courier New"]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'[/font]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.Wed, 21 Jan 2009 07:37:32 GMTMichael Earl-395764http://www.sqlservercentral.com/Forums/Topic640674-392-1.aspxhi 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]GOSET ANSI_PADDING OFFGOafterinsert into MyTest3 select 1 , 'John' , 'Smith' , 150000.00union select 2 , 'Hillary' , 'Swank' , 250000.00union select 3 , 'Elisa' , 'Smith' , 120000.00union select 4 , 'Liz' , 'Carleno' , 151000.00union select 5 , 'Tony' , 'Mcnamara' , 150300.00What´s difference between This code: ??insert into MyTest3 select 1 , 'John' , 'Smith' , 150000.00union ALL select 2 , 'Hillary' , 'Swank' , 250000.00union ALL select 3 , 'Elisa' , 'Smith' , 120000.00union ALL select 4 , 'Liz' , 'Carleno' , 151000.00union ALL select 5 , 'Tony' , 'Mcnamara' , 150300.00i'm lookup Plan Execution, using only 'union' i'm think then has more cost... and union all is less.Wed, 21 Jan 2009 07:02:05 GMTlucassouzace