July 10, 2014 at 1:30 am
Hi,
I'll keep this as short as possible. I've read many of the posts re: order by in a view (searched here on "view with order by"). I know that TOP ### in order to support ORDER BY is a kludge, that might disappear in a future release.
However, a few quick questions/comments:
1) My front end is Access, the back end is SQL Server 2014 Express. I'd prefer the sorting done on the more powerful machine, rather than the end users' machines. However, the data is highly summarized by the view, so the data volumes aren't that bad if Access has to do the sorting. Is it best to get SQL Server to do the sorting and, if so, how if the source is a view?
2) I'd prefer to sort on a column I don't want to return in the view (StatusID). However, it's not a deal breaker to add it to the view and drop it from the select statement calling the view. Again, would like the BE to sort if possible, then I wouldn't need to include the extraneous column.
3) I'm unaware how I can use a stored procedure as a "virtual table" with an Access front end. Some Google hits suggested adding the sorting to a SP, but I don't think that works with Access. If Access can use a SP as a data source, please let me know.
Thanks for pointing me in the right direction re: the above points.
P.S.: FWIW, I am used to ORDER BY being supported by views in other SQL dialects, so I wonder which standards, if any, are being honoured / violated by the different SQL dialects?
Here is the view source. It works but is frowned upon in the other hits on this forum.
CREATE VIEW view_Letters AS
SELECT TOP 999999999999
c.CaseNumber, u.Fullname as Analyst, m.CovNo, m.Suffix, m.LastName, m.FirstName, s.Status, t1.LetterDate, t1.Amount
FROM (
SELECT CaseID, StatusID, LetterDate, sum(BenefitPaid) as Amount
FROM fact_Claims
WHERE Applicable = 1
GROUP BY CaseId, StatusID, LetterDate
) t1
LEFT JOIN fact_Cases c
ON t1.CaseID=c.CaseID
LEFT JOIN dim_Users u
ON c.AnalystID=u.UserID
LEFT JOIN dim_Members m
ON c.MemberID=m.MemberID
LEFT JOIN dim_Status s
ON t1.StatusID=s.StatusID
ORDER BY t1.StatusID, Amount DESC, LetterDate
July 10, 2014 at 4:52 am
I am not an Access person, but Access absolutely can call stored procedures within SQL Server and based on what you're describing, that's what you want, not a view. Or, you could try putting this into an inline table valued function. That can be called more or less just like a view. Just don't create a multi-statement table valued function. They generally perform very poorly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2014 at 3:40 am
I think that when you use an Access FE with a SQL Server BE, then Access will use 'pass-through' queries to get the data. The entire query text is executed by SQL Server, so any ORDER BY clause is run at the SQL Server BE, not by the Access FE.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 14, 2014 at 4:21 pm
...I know that TOP ### in order to support ORDER BY is a kludge, that might disappear in a future release.
I think I know what Post you are talking about.
TOP 100 PERCENT is not a kludge nor is TOP <really big number>. The TOP clause is supported through SQL Server 2014 and does not look like it's going to be depreciated anytime soon. Microsoft usually warns you when a feature is going to be depreciated.
TOP is pretty major and affects query processing; stuff like that is not depreciated very often. Take a look at this query processing order pdf:
Logic Query Processing. The stuff you see here is likely not going away for a long, long time (e.g. SELECT, APPLY, JOIN, GROUP BY, WHERE, HAVING, DISTINCT, TOP, SELECT, ETC...)
They kind of stuff that gets depreciated looks more like this. This is purely opinion based on my observations.
-- Itzik Ben-Gan 2001
July 15, 2014 at 1:06 am
Alan.B (7/14/2014)
TOP 100 PERCENT is not a kludge nor is TOP <really big number>.
It is kinda. TOP (100) PERCENT ... ORDER BY will result in SQL ignoring the order by in a view because it's not a row-limiting top. Whether the optimiser will get smart enough to realise that TOP (really large number) is also not a row limiting top and hence will allow it to ignore the order by is an open question. It's probably not something that will be indicated as deprecated if it ever happens, because it's used to evade the SQL rule that only and ORDER BY on the outer-most select has any effect on the ordering.
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
July 15, 2014 at 7:51 am
I'm used to being able to specify an order by in a view in other dialects of SQL. I find it handy.
Rather than Microsoft deprecating (or silently removing) TOP <big number> in the future, it would be nice if they would officially support an order by in a view.
Is there a particular SQL standard that dictates that order by cannot be specified in a view?
Re: my original post: I have defined the summarisation view w/o an order by, and a simple stored procedure selecting the view including an order by. I've researched Access <--> SQL Server more, and Access can use the SP as a (readonly) record source, so that will meet my needs. In this instance, it's fine that the record source is readonly in Access.
Thanks all for your replies.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply