January 21, 2009 at 7:02 am
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.
January 21, 2009 at 7:37 am
Here 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.
January 22, 2009 at 5:28 am
ok, but this example, the query using union all more better union do you think ?
January 22, 2009 at 5:42 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply