Whitepaper : SQL Syntax and Query Performance
New SQL developers on every platform do not ‘talk pretty’ and keep struggling while trying to get out of the mindset of their prior language. The SQL language made a massive jump in the SQL-92 Standards. With those standards, it added many new language features based on the actual usage of the language, improvements in the hardware, and attempts to make ‘talk SQL pretty’ easier and more natural.
The query that runs well on a local set of test data can fail miserably on the production system. Very often, a simple change in the syntax can make a huge performance difference. Much of SQL advice is ‘it depends’ because tuning a database is both art and science.
However, there are some principles you can follow that should produce good SQL most of the time and give you a maintainable schema. These are heuristics, not mathematical formulas.
It takes some time for new SQL developers to ‘talk pretty’ and get out of the mindset of their prior language. If I had to summarize all of the tips in this article, then it would be to write SQL that looks like SQL. Learn each SQL statement and all of the tricks inside it, even if you have no immediate need. Practice coding SQL. Do not expect to have an epiphany overnight. However, one day, you will talk pretty in SQL.
This whitepaper describes SQL syntax and query performance, how to write SQL that looks like SQL, between predicates, membership predicates, generalized group predicates, the all syntax, the all predicate and extrema functions, the exists predicate, the overlaps predicate, and the case expression. The whitepaper also discusses why you do not need to retrieve more columns than you need, delete and update batches, why to use stored procedures, why GUIDs are not what you think, why you should not use count if you need existence, why you do not need to do negative searches, why you need to use CTEs, and double-dipping and the windows clause.
Author: Joe Celko
Share This:
Mr. Joe Celko serves as Member of Technical Advisory Board of Cogito, Inc. Mr. Celko joined the ANSI X3H2 Database Standards Committee in 1987 and helped write the ANSI/ISO SQL-89 and SQL-92 standards. He is one of the top SQL experts in the world, writing over 700 articles primarily on SQL and database topics in the computer trade and academic press. The author of six books on databases and SQL, Mr. Celko also contributes his time as a speaker and instructor at universities, trade conferences, and local user groups.
Register to read the full whitepaper.
Topics :
Database Diagnostics,SQL Query Performance,
Products :
SQL Query Tuner,
New SQL developers on every platform do not ‘talk pretty’ and keep struggling while trying to get out of the mindset of their prior language. The SQL language made a massive jump in the SQL-92 Standards. With those standards, it added many new language features based on the actual usage of the language, improvements in the hardware, and attempts to make ‘talk SQL pretty’ easier and more natural.
The query that runs well on a local set of test data can fail miserably on the production system. Very often, a simple change in the syntax can make a huge performance difference. Much of SQL advice is ‘it depends’ because tuning a database is both art and science.
However, there are some principles you can follow that should produce good SQL most of the time and give you a maintainable schema. These are heuristics, not mathematical formulas.
It takes some time for new SQL developers to ‘talk pretty’ and get out of the mindset of their prior language. If I had to summarize all of the tips in this article, then it would be to write SQL that looks like SQL. Learn each SQL statement and all of the tricks inside it, even if you have no immediate need. Practice coding SQL. Do not expect to have an epiphany overnight. However, one day, you will talk pretty in SQL.
This whitepaper describes SQL syntax and query performance, how to write SQL that looks like SQL, between predicates, membership predicates, generalized group predicates, the all syntax, the all predicate and extrema functions, the exists predicate, the overlaps predicate, and the case expression. The whitepaper also discusses why you do not need to retrieve more columns than you need, delete and update batches, why to use stored procedures, why GUIDs are not what you think, why you should not use count if you need existence, why you do not need to do negative searches, why you need to use CTEs, and double-dipping and the windows clause.
Author: Joe Celko
Share This:
Mr. Joe Celko serves as Member of Technical Advisory Board of Cogito, Inc. Mr. Celko joined the ANSI X3H2 Database Standards Committee in 1987 and helped write the ANSI/ISO SQL-89 and SQL-92 standards. He is one of the top SQL experts in the world, writing over 700 articles primarily on SQL and database topics in the computer trade and academic press. The author of six books on databases and SQL, Mr. Celko also contributes his time as a speaker and instructor at universities, trade conferences, and local user groups.
Register to read the full whitepaper.
Topics : Database Diagnostics,SQL Query Performance,
Products : SQL Query Tuner,