How are views stored in SQL Server?

• GSquared

  SSC Guru

  Points: 260824

  More actions

  September 26, 2011 at 9:39 am

  #1386604

  jared-709193 (9/26/2011)

  ---

  So I think part of this "take home message" from both of you is that the following:

  CREATE TABLE #TEMP (id INT, fruit VARCHAR(20))

  INSERT INTO #TEMP

  SELECT 3 AS id,'apple' AS fruit

  UNION ALL

  SELECT 2,'peach'

  UNION ALL

  SELECT 4,'orange'

  UNION ALL

  SELECT 1,'cherry'

  SELECT *

  FROM

  (SELECT TOP 100 * FROM #TEMP ORDER BY id) a

  MIGHT result in an ordered result set ascending in the id column, but is by no means GUARANTEED without the ORDER BY in the main query?

  Thanks,

  Jared

  Exactly.

  Run it 100 times and you'll probably get the same order all 100 times, because of caching and plan re-use. But you can't guarantee that. You're counting on luck if your code or user experience depends on it. Might work, might not. If that's okay, then don't worry about it. If it might cause problems, then add an Order By to the final, outer query, and guarantee your results.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon