INSERT INTO Issue in SQL 2012

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  August 23, 2012 at 9:24 pm

  #1529524

  SQL Kiwi (8/21/2012)

  ---

  No they're not.

  OK, I'm really curious what's going on behind the scene.

  I managed to get identical plans on 2008 as well.

  And different plans for the same queries launched from the same SSMS window.

  --TRUNCATE TABLE [dbo].[Test]

  DECLARE @initTime datetime

  SET @initTime = GETDATE()

  INSERT INTO [dbo].[Test]

  (ID)

  SELECT N

  FROM dbo.Tally AS T

  ORDER BY N

  PRINT 'Order By N : ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)

  --TRUNCATE TABLE [dbo].[Test]

  SET @initTime = GETDATE()

  INSERT INTO [dbo].[Test]

  (ID)

  SELECT N

  FROM dbo.Tally AS T

  ORDER BY N DESC

  PRINT 'Order By N DESC : ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)

  

  DECLARE @initTime datetime

  SET @initTime = GETDATE()

  INSERT INTO [dbo].[Test]

  (ID)

  SELECT N

  FROM dbo.Tally AS T

  ORDER BY N

  PRINT 'Order By N : ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)

  --TRUNCATE TABLE [dbo].[Test]

  SET @initTime = GETDATE()

  INSERT INTO [dbo].[Test]

  (ID)

  SELECT N

  FROM dbo.Tally AS T

  ORDER BY N DESC

  PRINT 'Order By N DESC : ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)

  

  I must to mention - I changed the environment a little bit by ecreating the table with a clustered index on (ID DESC).

  It seems one of the queries uses previously cached plan, and another one had to create a new one, as it text differs a little bit from the cached SQL text.

  Here identical plans make some sense, because SQL Server has to revert the order or Tally rows in both cases to insert them into dbo.Test.

  _____________
  Code for TallyGenerator

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  August 23, 2012 at 9:27 pm

  #1529527

  GSquared (8/22/2012)

  ---

  Based on that, I'll just pipe in with, "How dare you speak about those people that way! They've suffered in ways you can't possibly imagine! You are an evil, evil person!"

  Last argument of MVP's

  :hehe:

  _____________
  Code for TallyGenerator

• Paul White

  SSC Guru

  Points: 150467

  More actions

  August 23, 2012 at 11:39 pm

  #1529557

  Sergiy (8/23/2012)

  ---

  OK, I'm really curious what's going on behind the scene.

  Let me explain. In an update query, the optimizer considers sorting the incoming rows into index order. Sorting into key order tends to result in more sequential access to the index, but there may be a cost involved in sorting. In deciding whether to sort of not, the optimizer weighs up the pros and cons and makes a cost-based decision

  You can tell if the optimizer decided a sort was worthwhile or not by checking the properties of the update operator. If you see [DMLRequestSort] = True, the optimizer decided sorting was worth doing. Of course, you may or may not see an *explicit* sort in the plan. If the optimizer finds a plan that happens to produce rows in the desired order naturally, it can use that plan without a sort.

  So, whether [DMLRequestSort] appears or not depends on a detailed calculation that I won't go into, expect to say that you are generally more likely to see it appear as the number of rows to be updated becomes larger.

  Second point is that before SQL Server 2012, the optimizer always produced a plan as if an identity column existed. As Gail mentioned, ORDER BY in this context has only ever guaranteed that the order of assignment of identity values would match the ORDER BY clause. SQL Server 2012 contains an improvement so that the plan only has the shape required by the identity-assignment guarantee if the table actually does contain a column with the identity property.

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi