March 13, 2007 at 9:57 pm
Hi,
We are creating views and table functions and it appears that we cannot get the ORDER BY to take effect.
For example; CREATE VIEW... SELECT TOP 100 PERCENT ... ORDER BY XyzColumn
And when we open the view the records are NOT ordered by XyzColumn. Are there any guesses as to why this could be happening.
Thanks,
-matt
March 14, 2007 at 1:25 am
Hi Mathew Leigh
I try your problem
but i don't face any such order by problem , as i do the following test
create view shashi as
select top 10 Persent * from title order by titleid
----------------------------
select * from shashi
i got the result in ordered form according to the titleid.
March 14, 2007 at 6:49 am
Why do you want to do the Order By in a View? If you want a different order than what the view presents, you would end up with 2 ORDER BY's and some slow code. This is listed as a "worst" practice (for the most part, there are exceptions) by most.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2007 at 7:26 am
Hi Shashi,
In your example titleid may be the primary key. Please try on a different column and does it still work? This would be helpful to know. Because then we know for sure it should work. (Although documentation says it does, and I have heard of no other situation, it is still nice to have verication that I am doing something wrong.) Thank you!
-matt
March 14, 2007 at 7:32 am
Jeff,
I agree, VIEWS are not the place to put ORDER BYs (in general). The situation is the client is migrating from Access and they are using an Access ADP against SQL Server 2005 as a shortterm (hopefully) intermediate step towards VB.Net. Their approach is to push as much into SQL Server as possible (good). There are many areas that use the views with always the same order by. It's 'an' approach. I think the approach is trying a little too hard.
And on the other hand I am curious as to why I can't get something to work that I should be able too.
Many thanks for your reply!
-matt
March 14, 2007 at 7:51 am
It may be a bug in TOP that someone recently reported for SS 2k5. Stupid little things like...
SELECT TOP (75) PERCENT *
FROM sometable
Yield the incorrect results... but not all the time... unpredictable bugs are the worst. Dunno if they've come up with a hot-fix on it yet... was reported just a couple of days ago. Sorry I can't help more but I think this one is all on Microsoft. I'd start checking their website for a fix... maybe they pulled off a minor miracle...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2007 at 10:15 am
Hi Matt,
What type of sort is being done on the data if your desired sort is not performed?
Have you tried checking the collation of your Access table?
You can specify what collation should be used for each field that you read from the Access Table using the collate command and specifying how the SQL Server should interpret the data.
Example:
SELECT TOP 100 PERCENT
*
from table
ORDER BY XyzColumn collate SQL_Latin1_General_CP1_CI_AS
Doing it this way will make sure the data is then sorted using the collation but won't necessarily give you the results in the same collation. To make sure of that you need to specify the collation for each field that you want.
Example:
SELECT TOP 100 PERCENT
field1 collate SQL_Latin1_General_CP1_CI_AS,
XyzColumn collate SQL_Latin1_General_CP1_CI_AS
from table
etc...
HTH
GermanDBA
Regards,
WilliamD
March 14, 2007 at 10:49 am
Inability to use ORDER BY in a view in SQL2K5 highlights the danger of building a solution on something that "works, but is unsupported".
SQL2000 and earlier versions should never have allowed ORDER BY in a view. Period.
However, there was a hack/workaround, where you could trick the server into accepting a view containing an ORDER BY *if* you threw in a TOP PERCENT to the SELECT.
Voila. Throw in TOP 100 PERCENT, and you can do something you weren't supposed to be able to do - incorporate an ORDER BY in a view.
Now that SQL2K5 tightens up the validation on the SQL defining a view, all the solutions built on this "hack" are broken.
March 15, 2007 at 1:45 am
I read about this on a blog some months back. Can't remember which one. I'm pretty sure it was one of the SQL dev teams.
SQL Server 2005 is under no obligation to honour an order by in a view or subquery if the top restriction is 100%
SQL 2000 did, and it was often a waste of execution time, if a second order by was applied to the outer query, or a query operation forced a resort.
Only if the top statement specifies a portion of the table will an order by in a view or sub query be performed.
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
March 16, 2007 at 6:58 pm
Correct, this is by design.
SQL Server isn't obligated to honor any ORDER BY except in the outermost scope.
eg
CREATE VIEW myView
AS
SELECT TOP 100 PERCENT foo, bar FROM foobar ORDER BY foo
go
If you then say 'SELECT * FROM myView', then it's *not* guaranteed that the displayed result will be ordered according to 'foo'.
If that is needed, the only way is to say 'SELECT * FROM myView ORDER BY foo' - which these days makes the TOP 100/ORDER BY 'cheat' a moot issue.
It doesn't 'work' anymore, as is intended.
Similar with virtual tables.
SELECT x.someCol
FROM ( SELECT someCol, anotherCol
FROM someTab
ORDER BY somecol ) x <== This ordering is not guaranteed within the derived table.
/Kenneth
March 17, 2007 at 7:54 am
Thank you very much to all whom replied. I appreciate it a lot.
In this case since each view was called in many places (in the order 300 total) we felt that a common Order By might be a little easier for maintenance.
We did come up with a mickey rig. It appears that a multi-statement table function worked. Records are inserted into the Return table in order, and when the table function is called it appears to provide the records in that same order.
Thank you again for all the information and suggestions. It is good to know that the behaviour is not because I am not understanding something and it is good to know that it is not a bug.
-matt
March 19, 2007 at 8:47 am
>>Records are inserted into the Return table in order, and when the table function is called it appears to provide the records in that same order.
This will come back to bite you.
A table is an unordered set. This may appear to work, but at some future date, maybe due to a data volume threshold, or a SQL service pack, this solution will fail.
March 19, 2007 at 8:47 am
"SQL2000 and earlier versions should never have allowed ORDER BY in a view. Period."
Hey PW,
Does this also mean that Case Statements used in views which work for SQL2000 but are not supported, may have problems with SQL2005?
March 19, 2007 at 2:48 pm
How do you mean?
A CASE isn't a sort like an ORDER BY..
Could you provide an example?
/Kenneth
March 19, 2007 at 2:54 pm
Rather lengthy and sorry about the formatting, but this is one I'm currently using in production on SQL2000. We are porting some of this data to SQL2005 servers and I would be using the same views there. The Case is not used in the Order or Where clauses, but in the Select. Do you see any potential pitfalls?
CREATE VIEW dbo.CSR_Call_Activity_View
AS
SELECT TOP 100 PERCENT
CASE WHEN len(RTRIM(dbo.contact_history.poc_source)) = 10
THEN RTRIM(dbo.contact_history.poc_source)
WHEN len(RTRIM(dbo.contact_history.poc_source)) > 10
AND LEFT(dbo.contact_history.poc_source, 2)= '81'
THEN substring(RTRIM(dbo.contact_history.poc_source), 3, len(RTRIM(dbo.contact_history.poc_source)) - 2)
WHEN len(RTRIM(dbo.contact_history.poc_source)) > 10
AND LEFT(dbo.contact_history.poc_source, 1) = '9'
THEN substring(RTRIM(dbo.contact_history.poc_source), 2, len(RTRIM(dbo.contact_history.poc_source)) - 1)
WHEN len(RTRIM(dbo.contact_history.poc_source)) < 10
AND len(RTRIM(dbo.contact_history.poc_source)) > 4
AND LEFT(dbo.contact_history.poc_source, 1) = '9'
THEN substring(RTRIM(dbo.contact_history.poc_source), 2, len(RTRIM(dbo.contact_history.poc_source)) - 1)
WHEN len(RTRIM(dbo.contact_history.poc_source)) < 10
and len(RTRIM(dbo.contact_history.poc_source)) = 4
THEN RTRIM(dbo.contact_history.poc_source)
END AS Delivered_ANI,
CASE WHEN Cast(dbo.contact_History.delivered AS Datetime) < cast(dbo.contact_history.entered_queue AS Datetime)
THEN dbo.contact_History.Entered_Queue ELSE dbo.contact_History.Delivered END AS Delivered_DateTime,
rtrim(dbo.skill_dispositions.description) AS CallDisposition,
CASE WHEN AL.ProblemCode is NULL THEN 'Tech Support'
WHEN AL.ProblemCode = '' THEN 'Tech Support'
WHEN AL.ProblemCode LIKE '%[0-9]%'then
(Select rtrim(LeadLevel) from RFS.dbo.RFSMaster where RFSmasterkey = cast(AL.ProblemCode as int))
ELSE 'Tech Support'
END AS Lead_Disposition,
CASE WHEN AL.ProblemCode LIKE '%[0-9]%'then 1
else 0
end as LeadCallFlag,
CASE WHEN AL.ProblemCode NOT LIKE '%[0-9]%'
THEN 0
ELSE Cast(AL.ProblemCode as Decimal(8,0))
END as LeadNbr,
COALESCE (DATEDIFF(s, dbo.contact_history.entered_queue,dbo.contact_history.contact_terminated), 0)
AS CallSecs,
CASE WHEN Cast(dbo.contact_History.delivered AS Datetime) < cast(dbo.contact_history.entered_queue AS Datetime)
THEN COALESCE (DATEDIFF(s, dbo.contact_history.entered_queue, dbo.contact_history.entered_queue), 0) + 1
ELSE COALESCE (DATEDIFF(s, cast(dbo.contact_history.entered_queue AS datetime),cast(dbo.contact_history.delivered AS Datetime)), 0)
END AS HoldSecs,
CASE WHEN Cast(dbo.contact_History.delivered AS Datetime)< cast(dbo.contact_history.entered_queue AS Datetime)
THEN COALESCE (DATEDIFF(s, dbo.contact_history.entered_queue,dbo.contact_history.contact_terminated), 0) + 1
ELSE COALESCE (DATEDIFF(s, Cast(dbo.contact_history.delivered AS datetime),cast(dbo.contact_history.contact_terminated AS datetime)), 0)
END AS TalkSecs,
COALESCE (DATEDIFF(s, dbo.contact_history.contact_terminated, dbo.contact_history.wrap_complete), 0)
AS WrapSecs,
CASE WHEN Cast(dbo.contact_History.delivered AS Datetime)< cast(dbo.contact_history.entered_queue AS Datetime)
THEN COALESCE (DATEDIFF(s, dbo.contact_history.entered_queue,dbo.contact_history.wrap_complete), 0)
ELSE COALESCE (DATEDIFF(s, dbo.contact_history.delivered, dbo.contact_history.wrap_complete), 0)
END AS AgentSecs,
rtrim(dbo.users.username) as Call_UserID,
Case when AL.HILStation is null then
(Select Top 1 HilStation
from Traffic.dbo.BFICSActivityLog with (NOLOCK)
where UserID = dbo.users.username AND rtrim(EventType) = 'Call'
and datetime > DATEADD(d,-1,dbo.contact_history.entered_queue)
and datetime < DATEADD(d,3,dbo.contact_history.entered_queue)
)
ELSE rtrim(AL.HILStation)
END as HILStation,
AL.Acct_Nbr as Call_Acct_Nbr,
AL.DateTime AS ActivityLog_TimeStamp,
dbo.contact_history.Entered_Queue,
dbo.contact_history.Contact_Terminated,
dbo.contact_history.Wrap_Complete,
Case When AL.EventType is null
THEN 'OB_Call'
ELSE 'IB_' + rtrim(AL.Eventtype)
END as EventType,
dbo.contact_history.Contact_History_Guid,
dbo.point_of_contact.poc_identifier AS Queue,
dbo.point_of_contact.description AS QueueDescription,
CASE WHEN len(RTRIM(dbo.contact_history.poc_source)) = 10
THEN 'IB'
WHEN len(RTRIM(dbo.contact_history.poc_source)) > 10
AND LEFT(dbo.contact_history.poc_source, 2)= '81'
THEN 'OB'
WHEN len(RTRIM(dbo.contact_history.poc_source)) > 10
AND LEFT(dbo.contact_history.poc_source, 1) = '9'
THEN 'OB'
WHEN len(RTRIM(dbo.contact_history.poc_source)) < 10
AND len(RTRIM(dbo.contact_history.poc_source)) > 4
AND LEFT(dbo.contact_history.poc_source, 1) = '9'
THEN 'OB'
WHEN len(RTRIM(dbo.contact_history.poc_source)) < 10
AND len(RTRIM(dbo.contact_history.poc_source)) =4
THEN 'XFR'
ELSE 'IB'
END AS Call_Initiation
FROM dbo.contact_history WITH (NOLOCK)
INNER JOIN dbo.skills
ON dbo.contact_history.skills_guid = dbo.skills.skills_guid
LEFT OUTER JOIN dbo.skill_dispositions with (NOLOCK)
ON dbo.contact_history.skill_dispositions_guid = dbo.skill_dispositions.skill_dispositions_guid
LEFT OUTER JOIN dbo.point_of_contact with (NOLOCK)
ON dbo.contact_history.point_of_contact_guid = dbo.point_of_contact.point_of_contact_guid
AND dbo.contact_history.point_of_contact_guid = dbo.point_of_contact.point_of_contact_guid
LEFT OUTER JOIN dbo.users with (NOLOCK)
ON dbo.contact_history.users_guid = dbo.users.users_guid
Left OUTER JOIN Traffic.dbo.BFICSActivityLog AL with (NOLOCK)
ON dbo.contact_history.contact_history_guid = AL.CallID
AND rtrim(AL.EventType) = 'Call'
LEFT OUTER JOIN Traffic.dbo.BFICSActivityLog LEAD with (NOLOCK)
ON LEAD.CallID = dbo.contact_history.contact_history_guid
AND AL.EventType = 'Lead'
LEFT OUTER JOIN RFS.dbo.RFSMaster RFS with (NOLOCK)
ON cast(RFS.RFSMasterkey as Varchar) = AL.ProblemCode
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply