What are the best practices for writing SQL queries that work with all versions of SQL?

Writing standard SQL code requires experience and time. If you want retrieves 100 rows you will use TOP in t-sql or LIMIT in both pl/pgsql/Mysql or FETCH FIRST in pl/sql. However you always can use the windowing function ROW_NUMBER() for achieving this in all dialects

Adopting ANSI SQL standards in your SQL queries isn't just about compatibility; it's about writing clean, efficient, and reliable code that stands the test of time and technology shifts. ANSI SQL ensures that your SQL queries are universal, which means they are more likely to run smoothly across different database systems. This universality is particularly important when working in diverse environments or when migrating between different SQL databases.

You can always start by implementing ANSI Standard SQL which mostly works with most of the database platforms. However you need to use platform specific constructs to keep the performance of queries at acceptable level. Having said that, you may avoid using the highly specialised features of database which makes it hard to migrate to other data platforms. You may also consider database virtualisation platforms if database platform independence is a key concern.

ANSI SQL Standards are good summaries of current features and rationale of RDBMS. Learn them (to be sure you know which works best for you). Monitor them (they will evolve over time and include features praised by users and vendors). Yet, most importantly - do not be limited by them. You may have some contractual/regulatory obligations where use of standard SQL is dictated by some law that is hard to change, but is is pretty rare. In vast majority of other cases sticking to slower-but-standard sql can be both unethical (you will need to knowingly pass higher operational costs to end users) and not environmentally sustainable (your application will need to consume more resources to run).

Great insights seen so far. Thanks. Keep it so simple (the KISS rule) as much as it behoves you especially when starting out with a different DBMS than you are accustomed with. And as you progress over time, there is always room to refactor your code, taking advantage of the Vendor specific offerings in SQL over the ANSI SQL baseline.

Avoid vendor specific extensions in application code. The more system specific housekeeping code I use does have some of the system stored procedures for ease of obtaining meta-data based information. This can vary from system to system. If you are cleaver, you can test what version of a system you are on and use syntax that is specific to that particular version. For example... /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */ IF (SELECT ServerProperty('EditionID')) IN (1804890536, 610778273, -2117995310) SET @editionCheck = 1 -- supports online rebuilds ELSE SET @editionCheck = 0; -- does not support online rebuilds

Why? Your SQL code should not be stored in your application code at all. Think of handling it the same way you handle I18n and L10n issues if you cannot store data manipulation code where it belongs: With the data it manipulates.

I think one should consider what the aim is. To have the best performance, to have the best readability, to have easily portable queries. Once it's known the answer is simple. Personally, I recommend getting a healthy balance between being neutral and getting the most performance. Otherwise, what is the point of paying license fees if one doesn't use what the vendor offers?

There is no incentive for following this tip. You will loose benefits, and get no harm from loosing compatibility In most cases unless you sell software that will be run on-prem by your customers, you have full control over database and its setup. Please note that even if your software is running on-prem for customers, it might be beneficial for you do also dictate which version of db will be used. A lot of orchestration tools (say kubernetes operators) can help simplify and automate db tasks for customers. So embrace opportunity, but be sure you know what version of RDBMS your arthicecture expects, and require to have active guards in application code to not worry about it over time.

Vendor-specific SQL features are like fancy sprinkles on a dessert – enticing but tricky. Using them might backfire when switching SQL platforms, like wearing beach gear to the Arctic! Crafting a query with these special features can be like arriving in a Halloween costume on a regular day – awkward! So, stick to standard SQL. It's the trusty old reliable, fitting in anywhere, just like your favorite pair of jeans. Remember, in SQL, versatility trumps fancy tricks. Keep your SQL skills adaptable and ready for any challenge. Standard SQL: your passport to cross-platform success!

Use meaningful Aliases, joining a table and calling it 'a' joined to 'b' might be easy now, but for the next person can make interpretation much more difficult especially with many table joins in play

Always start with the data you need to produce and work backward. Ten map out where the data comes from and the necessary joins needed. Then use the made ansi code to determine the segments needed.

Giving meaningful aliases to your CTEs, fields and tables is like creating a easy to follow map. Anytime a complex query (you don't usually start complex, that's where you end) needs to be updated, good naming will pay dividends in time spent.

Aliases are supposed to shorten the names of the tables... so use short meaningful aliases (Inv, Det, Pay, Acc, etc.) Also remember that when you assign an Alias to a table, the table name cease to exist on that query.

I agree with Andrew on this one. I was taught to always use fully qualified databasname, tablename when writing my queries. It not only eliminates confusion but it also makes it easier for someone else to read, understand and perhaps inherit your queries.

Some of the online (read:free) SQL formatters do a very good job of making query's look good. A quick Google search will start you on your way.

In my experience, I noticed that the first line of a query is crucial. It allows for filtering a workload instantly and gives a good outlook. E.g. --MON: The query returns CPU utilization --BATCH: The query is part of batch B123Q3

One day I have encountered situation where i seriously considered to have some kind of "sql mnifier" as query itself was several hundred kb, and removing all whitespace would decrease size by about 1/3, which would help both network (less to send) and parser (less to parse and throw away). Key lesson: query that is actually sent to the server does not need to be the same pretty thing that humans write and improve over time, there can be sql builder, minifier, linter and many other interesting things that happen during code compilation or even in runtime.

Imagine your SQL query as a book. Without proper formatting, it's like a novel with no paragraphs or chapters – chaotic and daunting! Consistent formatting is like neatly lining up your ducks. It brings order, making your code a visual treat. Use spaces, tabs, or line breaks – your pick, but keep it uniform, like a well-rehearsed orchestra. Indentation? It's your code's GPS. It guides the reader through the logic, turning a maze of commands into a smooth, scenic route. Embrace this art, and watch your SQL code transform into a masterpiece of clarity and elegance!

Formatting and indentation can be just as important as the query syntax itself. The longer the query, the more crucial it is to have elegant code in a logical and legible format for developers and analysts to visually distinguish from other areas of the query.

Use joins wisely and make sure you choose indexing options that best match your joins and where clauses. Avoid using hints if you can.

Four uses of SQL unions and sub-queries: 1.- Sub-Query in FROM Clause: Create a derived table for your main query. Useful when performing operations on a subset of your data. 2.- Sub-Query in SELECT Clause: Calculate a value for each row in your main query. This is known as a scalar sub-query. 3.- Correlated Sub-Query: A sub-query that uses values from the outer query. In other words, the inner query depends on the outer query. 4.- UNION vs UNION ALL: UNION removes duplicate rows, while UNION ALL does not. If you don’t need to remove duplicates, use UNION ALL as it’s faster because it doesn’t need to check for duplicates.

Its always easier to work with a reduced set, or derived data. For example, using a sub query in a from clause to pre compute certain values and working your way out from there. Work from the bottom up in the problem.

Think of subqueries and joins as the spices in your SQL recipe. Used wisely, they bring out the best flavors; overdo them, and you've got a recipe for complexity. Subqueries, those queries nested within another, are like secret agents working behind the scenes. They're great for detailed, layered data analysis. But remember, too many agents on a mission can complicate things. Use them judiciously! Joins are the bridges connecting islands of data. They link tables through common columns or conditions, uniting separate data realms into a cohesive whole. But caution – building too many bridges can lead to a tangled web. Keep joins clear and purposeful.

I noticed that joins generally perform better than sub-queries. However, it's case-specific. Enterprise databases usually re-write queries, so it's better to focus on readability. But both should have proper indexing. Keep in mind that joins could be treated as a filter.

Test your query's on a system as close to the production environment hardware, data, and load as possible. There are also third-party packages to simulate some of these criteria. Use execution plans to dig into a troublesome query to understand what exactly is going on.

When optimizing queries, ensure that only the necessary columns are needed for the result. Also, keep in mind the potential size of the source table and further optimize the query by using aggregations when possible. Depending on the use-case of the query (ie. as part of an ETL process for B.I. software and visualizations, for instance) always strive to optimize the query to reduce processing times.

You need to test your queries even if you only use single database engine and upgrade to newer major version. However this is not different from any other application code / logic.

if it's possible utilize indexes using bit fields to group twice or more on (64 bits column). I did on a large Oracle database. Keep in mind that your WHERE clause will be filtered on DB index page.

This is the most important part of the SQL code creation process. The optimal query depends on many factors: the specific version of the DBMS, existing indexes, actual statistics, etc. Therefore, testing systems should be as close as possible to product systems. Not always possible to create a complex query that works optimally on all or many versions and platforms. There are general tips on optimizing queries. In complex tasks each time in each particular cases, you should first of all create more optimal query and test different hypotheses about which path is better. You should maintain your database very competently and accurately. Even updating statistics too frequently can harm your query.

Following ANSI standard is the first way of writing SQLs confidently and in much cleaner way . Most of the databases follow ANSI standard and you might really need to remember specific syntax. Also when ou write SQLs if you have good development timeline , always think and write the SQLs in optimised way , use CTEs as much as possible , use joins wisely , effective indexing or clustering whichever is needed , proper aliases so that other people will also understand when you write complex SQLs , usage of comments to explain the complex logic you have written. Keep yourself updated of the latest and different ways to solve a problem and cost associated to each of them .

MySQL is the baseline for ANSI SQL. If it runs there, it should run in anything. And it’s free. Keep a virtual running MySQL spun up with a subset of your data and you have an “ACID” test for your queries :)

This is a broad perspective on RDBMS and ANSI standards. As a data professional you should be aware of the limitations and constraints of the RDBMS you are working with. The language being used to interact with each RDBMS can greatly differ. Understanding that each RDBMS does differ I have an opinion of a code compliant analyzer that would flag if you are connected to (A) server type and trying to execute (B) type language that it would flag the query and make recommendations on how to change it to make it (A) compliant.

Be sure you have know and understand your reasons to avoid use vendor specific features. Very often there are some benefits compared to regular standard versions of queries, making them either faster or more sequre by offering capabilities that did not yet make it into modern SQL standard. Instead of limiting self from using best your database can offer, write an application, where business logic has no idea about any database at all, and actual knowledge about database implementation is limited to few files that can be easilly replaced as needed. Situation with ANSI databases is reminding situation with web pages, there is standard HTML, and there are things supported by browsers. And people target reality not standard.

N?o há nenhuma garantia de universalidade ao escrever um código SQL, mas a simplicidade das queries reduzirá a necessidade de se usar comandos proprietários e tornará o seu código minimamente portável.