September 12, 2008 at 11:28 am
I'm doing a proof of concept on moving a database from sql 2000 to sql 2005
Heres what I did
I detached the database from SQL Server 2000
I copied the files to a test machine and attached the database.. I then went into options and changed the compatibility to sql 2005
I opened a view, and noticed my results were not ordered.. so I look at the view, and fair enough, theres no order by
I edited the view to put an order by on, and saved it.. I opened the view again, and its still out of order.
After some humming and ha'ing.. i took the SQL from the view and created a new view with the exact same sql.. i opened it up, and the results are in order..
I'm confused.. am I missing something here?
heres the first view
USE [Tracking]
GO
/****** Object: View [dbo].[v_Order_SKU_NumSerialsx] Script Date: 09/12/2008 12:26:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[v_Order_SKU_NumSerialsx]
AS
SELECT TOP (100) PERCENT order_id, sku_id, COUNT(*) AS NumSerials
FROM dbo.v_SerialDetail
GROUP BY order_id, sku_id
ORDER BY order_id, sku_id ASC
and heres the first 5 rows of data returned
order_id sku_id NumSerials
76510135110022117
70510225031022270
124010123020022250
30110135110022234
11181022304002280
heres the sql from the 2nd (new) view
USE [Tracking]
GO
/****** Object: View [dbo].[v_order_sku_numserials] Script Date: 09/12/2008 12:26:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[v_order_sku_numserials]
AS
SELECT TOP (100) PERCENT order_id, sku_id, COUNT(*) AS NumSerials
FROM dbo.v_SerialDetail
GROUP BY order_id, sku_id
ORDER BY order_id, sku_id
and heres the top 5 rows
order_id sku_id NumSerials
100010225030022100
100110225030022201
100210225030022120
10031022503002250
100410225030022100
Something to note is that order_id is a varchar(50) field, not an int field as it COULD contain letters as well as numbers
I'm just confused why the order by works on one and not the other
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
September 12, 2008 at 11:35 am
SQL 2005 ignores the TOP 100%... ORDER BY construct in views, as order by statement is only valid in the outermost select statement, unless a row limitation is in effect (a top that's not 100%)
What you're probably seeing in the second view isn't the order by. It's a ordering that's there because of the group by statement. If you don't specify an order by in the outermost select statement (the one that queries the view) then there's no guarantee at all of the order that your rows will be returned in. They may be as you want, they may not, and that may change from time to time as the optimiser picks different plans.
If you look at the execution plans, you'll probably see that the one that's 'in order' is doing a stream aggregate and the one that's 'out of order' is doing a hash aggregate.
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
September 12, 2008 at 11:41 am
thanks gail.. that makes sense i guess.. and it wasn't really a problem... just struck me as weird..
thanks again
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
September 12, 2008 at 11:44 am
Change in collation level even at operating system can change the sort order. I had been through this when I moved 2000 database (Latin1_general_AS_CI) to 2005 server (Latin1_General_BIN).
SQL DBA.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply