Want to Challenge Yourself to Better Troubleshooting? (2 Viewers)

[Galaxiom]

BTW T-SQL queries and Stored Procedures incorporate virtually all of the functionality that Access developers use in VBA. They have variables, code loops, can work with the equivalent of Recordsets (known as 'cursors' in T-SQL) and build dynamic SQL commands to be executed. Consequently a T-SQL query can sometimes look rather more like a VBA procedure than what an Access developer usually thinks of as SQL.

This is why I take exception to comments such as in a recent post where Pat Hartman claimed that T-SQL was "a very limited language". Anyone who has spent years working with T-SQL should know that is complete and utter nonsense.

Being able to write both the code and the actual query in the same incredibly powerful editor is a major advantage. SQL Server Management Studio is based on Visual Studio so its Intellisense and syntax checking facilities are extraordinary. It also has inline highlighting of syntax errors, and words that are searched in the Find box. Lines with recently saved and as yet unsaved changes are also highlighted in the scroll bar making it very easy to navigate through the query sections being edited. Sections of the query can be closed down to a single line to get it out of the way when working on other sections.

MSSQLSMS does have a query designer too (itself quite superior to that provided in Access) but the T-SQL editor is so good that anyone reasonably fluent in SQL would prefer the editor over the designer. And it doesn't screw up the formatting of the query like Access does.

Access is great for simple databases but the vast majority of developers looking forward to a long career in databases should be exploring their way into SQL Server or another DBMS. The scope for available work is much greater with SQL skills. Pat has made herself a very good niche by focusing on Access but there are considerably more opportunities for developers who have added more advanced SQL skills to their repertoire.

 

[Mike Krailo]

@Galaxiom Thanks for the detailed explanation. I've always thought creating large complex queries was not as good as multiple smaller queries run in sequence but maybe that's not so in SQL server land.

 

[Galaxiom]

@Galaxiom Thanks for the detailed explanation. I've always thought creating large complex queries was not as good as multiple smaller queries run in sequence but maybe that's not so in SQL server land.

Yes not really the case in T-SQL. I have written queries that appeared to choke the server but they always turned out to have errors in the joins that ended up returned many tens of millions of rows. For reasons that don't make sense to me, it is possible to join two tables with a condition that involves columns in a completely different table. Access won't allow this. (It is easy to do when copying an pasting joins without paying enough attention to the subsequent editing.) It is an incredibly robust platform that appears to chug away at any task unperturbed, no matter how stupid the instruction.

T-SQL isn't just a querying language like Access SQL. It is a full blown procedural language. The big queries I'm talking about have lots of separate stages in them that would be considered completely separate queries in Access.

When I comes to optimising the basic database queries themselves, both Access and SQL Server use some pretty serious techniques. Many years ago it was said that the optimiser could not work across multiple levels of queries as separate objects in Access but I have since read (right or wrong) that is no longer the case. I don't think it makes much difference any more whether the queries are nested into one query or as separate objects. Isaac and Pat Hartman have covered the pros and cons of each approach earlier in this thread.

Building Views upon views was also once frowned on in SQL Server but that has apparently been sorted out too. Of course it is still entirely possible to build hideous structures that the query oprimiser can't handle.

There are many more factors involved in making a query efficient than whether objects were nested or not. Most of us know about indexes and Sargability but there are others.

In SQL Server they are exposed to the dba. One of the big ones is Statistics. The optimiser uses statistics about the data, mainly relating to the diversity of values, to make decisions for the query plan. If those statistics get out of date, the performance can be very adversely affected. It is a very well documented subject for SQL server and other DBMS.

I don't know what Access does to manage this. I have sometimes decompiled Access databases in order to regenerate the query plans but I don't have any hard facts about this. Decompile is an undocumented feature of Access so I somehow doubt that it could be vitally important. I have never found it to a bad thing though.

 

[Pat Hartman]

This is why I take exception to comments such as in a recent post where Pat Hartman claimed that T-SQL was "a very limited language". Anyone who has spent years working with T-SQL should know that is complete and utter nonsense.

Does it have a debugger now? I haven't built a sp in a while. I was probably referring to the fact that Access creates applications whereas T-SQL works only with data. Not that data is unimportant but it is just part of what Access does. Access is not a database engine and SQL Server does not create applications. Some people prefer to work with code, others prefer to work with a higher level tool. My question always comes down to - why work with a tool that does stuff for you but always ignore the tool's features so you can do it yourself? Use a different tool that doesn't come with so much baggage. Access has a heavy footprint and is ancient to boot. A craftsman always understands his tools and uses them to his advantage. I code when my tool won't solve my problem but otherwise, I let my tool do the heavy lifting.