Insert vs Union ALL

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  December 21, 2007 at 1:19 am

  #121510

  Hi Experts,

  I have two different types of query to insert some data's into a table.

  Query1:

  Insert into TableX

  select 1,'AAA'

  Insert into TableX

  select 2,'BBB'

  Insert into TableX

  select 3,'CCC'

  Query2:

  Insert into TableX

  select 1,'AAA' Union ALL

  select 2,'BBB' Union ALL

  select 3,'CCC' Union ALL

  TableStructure:

  create Table TableX

  (

  Eno int,

  Ename varchar(50)

  )

  Eno - PrimaryKey

  My Questions:

  1) Which query is best in performance ?

  2) Which query will take less network packet ?

  3) Which query will be best in production server ?

  4) Will UNIONALL cause creation of WorkTable ?

  5) Which query will insert the data very fast ?

  6) Which query will not lock the table ?

  7) Which query will use PrimaryKey ?

  8) If we use more INSERT staement,will query performance get down to slow ?

  Thanks in advance for your answers.

  karthik

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  December 21, 2007 at 5:02 am

  #762060

  is this a test? Surely you'd run this yourself through profiler etc.and see what happens?

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 21, 2007 at 1:32 pm

  #762319

  Please don't create multiple threads on the same problem. It just fragments replies and annoys the regulars.

  Since this is a continuation of your old thread, please continue it there

  Union

  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

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