+1 for whoever commented on TVF's ... I had no clue how unbelievably valuable they were (from several different angles) until not that long ago, I worked once for a bank SQL Development team on
huge call center data with a lot of parametrized 'stuff' and just a lot of collaborative sql objects in general.
Boy, you talk about versatility - the table valued function yeah.
As far as indexing views, more power to whoever has found them beneficial, but I hammered away on those for a while and ended up finding so many 'gotchas' (just things that disqualified the view from being index-able), that I mentally abandoned it in favor of simply making sure my source Tables were indexed 'just right' and that my View SQL was as well optimized as possible. But I am not criticizing; if you find them helpful it's great.
@MajP I do the same thing too - saved PT query object, I call it qryBucket and just dump sql into it as needed.
Lastly, one of the (many) nice things about properly saving/storing/organizing/structuring stuff on SQL Server and then
minimally coding the Access invocation of it (i.e.
exec dbo.procname 'value', 'value') to me, is this:
Even though you might start out assuming that Access surely ought to allow you to paste ANY valid t-sql code chunk into a passthrough query and run it.....such is not quite the case. There are a variety of tiny things (which make NO sense to me), that, if included in your t-sql, will make a passthrough query unable to work properly - with vague error messages that will generally lead you nowhere.
But, encapsulated in a SP, most if not all of those problems seem to fade.
Overall, since SQL server allows a veritable
mountain of advantages in organization, scripting > ansi sql statements, commenting, version control, etc.....I find that a compelling reason, but I appreciate Pat's perspective too, it's all a matter of balance, how much is worth the time of converting, etc.
A lot of my apps which do end up using SQL as a back end, someday, they get slated for conversion by the inimitable ".Net team" (

), and the first thing they're going to do is place a 100% bulletproof server side setup anyway.