Database Version Compatibility
Today we have a couple comments that I found quite interesting. Robert talks about the ORDER BY clause in Views being supported, deprecated, and returned once again. Sanders shares thoughts about using features beyond those ANSI defines for SQL Engines.
Robert writes:
ORDER BY IN VIEWS!
First it was there, then it was gone, then it was back, and now it is officially gone but technically still there.
In 2000, we used TOP 100 PERCENT and ORDER BY in views extensively for all application pick lists (hundreds of them). At the time, we thought it best to centralize and optimization the processing on the DB Server and at the discretion of the DBA.
Then in 2005 it was depreciated, but it still worked, so we left it in.
Then in 2008, it worked RAMDOMLY! We learned our lesson – get rid of TOP 100 PERCENT! and move to procs for pick lists.
Now, Microsoft has apparently brought it back (see MS Article ID: 926292).
I’m getting dizzy.
Editor:
Supporting the ORDER BY clause in views has always been a controversial topic. In theory, a view is not a physical table, but still represents the concept of a table. Tables don’t have an order by clause (with the exception of a clustered index, which also may not be counted on).
Regardless, I would expect some consistency of existence and performance for this kind of support. Sometimes things are cut in order to make delivery timelines…that’s about the only reason I can imagine.
There are a couple workarounds I might consider. First, is the use of a table user defined function. Even then you may have the same experience. The second workaround that I have found effective is to use a ranking function such as RowNumber(). If you don’t provide a ranking attribute, or simply use a static value, then the order by clause in the ranking function works nicely.
RowNumber() OVER (PARTITION BY 1 ORDER BY [ColumnList])
Sanders Writes:
<flashback>
I once wrote an article relating to databases and compatibility for C|Net. It was my first experience as a public figure as the subject of a flame war.
I said that if you’re going between Oracle and SQL Server, don’t use inner and outer joins because they worked differently back then.
Hundreds of comments followed, along with emails, to speculate on and criticize my career, my family, etc.
</flashback>
My advice on compatibility today is what it was then – stick with ANSI SQL.
Product-specific features come and go – as do the corporations behind them.
But if your project needs to work untouched for decades into the future, ya gotta go with national standards over corporate candy.
Our nation’s best and brightest minds worked and thought hard to come up with those standards so that we could all use these tools for free!.
But when corporations make products, they need profits so they build in obsolescence and the need for ongoing maintenance.
In that regard, corporate database technologies are no different from any other product – their features are built to break… sooner or later.
But one thing they all have to do is support the ANSI standard. If they don’t do that, they can’t rightly call themselves database servers.
So as long as you stick with ANSI for work, and just have fun on your own time with the corporate nick-nacks, your stuff will work wherever and whenever and forever.
You’ll also enjoy the added virtue of having a product that is your product, and not just an extension of Oracle or Microsoft’s proprietary stuff.
Thank you all for sharing your thoughts. If you have something to share send an email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Demystifying SQL Server Differential Database Backups
Incorporating differential backups will add complexity to your backup strategy but the benefits can be staggering. Between the storage savings and reduction in recovery time it’s clear that differential backups should be in your tool belt.
Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ
SQL supports a concept called all-at-onc… (read more)
Featured Script
Oracle: Howto: Rename or Move data files with database open
Datafiles can be renamed or moved while the database is open. However, the tablespace must be made READ-ONLY. This will al… (read more)