Storing Small Amounts of Data

If you are accustomed to a programming language such as Perl, Java, or Visual Basic, you will have grown used to having variables to store temporary values, global constants, and parameters that are known only at runtime. It would be convenient to have similar variables in SQL. If you want to perform 10 different actions on "Jim Smith," for example, it's a shame to have to say "Jim Smith" in 10 different queries. Some queries, especially ones involving subqueries, reuse the same search conditions multiple times, and again it would be good not to have to say "Jim Smith" multiple times in the same query. If variables were available in SQL, you could use them to parameterize your code. However, the SQL standard has no obvious support for named variables to store such small values.

Most vendors supply a simple programming language with their SQL engine, which generally include support for variables. But you don't have to rely on that. You can stick to minimal SQL and use a single-row table [Hack #58] to keep variables. There are costs, however. Your single-row table will likely be kept on disk and will be slower to access than traditional memory-resident variables. Query optimization and caching technologies can minimize this cost.

With a little more work you can share a singleton style among multiple users [Hack #59], allowing each user to have variables without one user's variables interfering with those of a different user.

SQL Fundamentals

Joins, Unions, and Views

Text Handling

Date Handling

Number Crunching

Online Applications

Organizing Data

Storing Small Amounts of Data

Locking and Performance


Users and Administration

Wider Access


SQL Hacks
SQL Hacks
ISBN: 0596527993
EAN: 2147483647
Year: 2004
Pages: 147 © 2008-2020.
If you may any questions please contact us: