SQL Tricks and Tips

A List of basic SQL Tricks and Tips by Barry Williams, January 3rd. 2009
This is a list of things that I have found useful over the years, usually when someone says “How do I … ?”
Please contribute your suggestions so that this Group becomes a reference point for Best Practice.

1. Cursors
Cursors make it possible to go through a Table, one record at a time.
This is sometimes essential when you can’t find a way to do what you want to do in SQL.
For example, concatenating many text fields into one text field.

2. Dynamic SQL
Dynamic SQL statements are constructed on the fly, its parameters based upon, usually, user input. For example, imagine that you had a database table that stored information on your company's employees. Perhaps you'd want to add some interface that allowed users to enter an employee's last name and have an ASP page that returned a list of employees with that last name.
Some problems associated with Dynamic SQL can be solved by putting it in a Stored Procedure.

3. EXISTS versus IN
EXISTS is usually more efficient because any row returned is OK, and the Search stops.
The IN, on the other hand, is looking for a specific match which takes longer.

4. Generating SQL
Generating SQL can be a very useful and very powerful technique.
For example, generating Scripts for administration of Users by reading the list of Users from a System Catalog and generating the same Script for each User by simply substituting the User name into a Template Script.

5. GUIDs
A Global Unique Identified (GUID) is a system-generated value that is built using the really unique information which is your network card’s internal serial number.
To use a GUID you must define the primary key column as a UniqueIdentifier data type and set the column’s ‘Is RowGuid’ property to Yes.
Microsoft has a UUID, which is a Universally Unique ID.

6. Hierarchies
Hierarchies can be easily implemented by creating a reflexive relationship which joins the table to itself. This supports a ‘Parent ID’ field.
A simple solution for a small number of levels, such as four, is to join the table the table to itself four times.
Oracle provides CONNECT BY and SQL Server 2008 introduces the HIERARCHY ID Datatype.

7. Joins
a. Outer Joins
Outer Joins are required to join tables when not every record in one table ahs matching records in the other Table. For example, doing an analysis of Sales by Product will not produce any values for Products that have no Sales. Bit this is always a requirement and the solution is to use an outer Join and either display NULL or zeros. Another good example is for Sales Managers who don’t earn Commission on sales. They will never have matching records, but it is appropriate to display NULL, rather than zero.
Sample syntax :-
SELECT fld_names
FROM tbl_name, tbl_name_2
LEFT OUTER JOIN tbl_name_2 ON (ID=ID)
INNER JOIN tbl_name ON(ID=ID)

b. Self-Joins
For example, a table of Airports is joined twice to find flights to and from Airports.
c. Cross Joins
This produces a Cartesian Product and is useful when generating Test Data from Reference Data, but otherwise is a simple beginners mistake with disastrous consequence where a JOIN is left out and a SELECT runs forever.

8. Reports
a. You need OUTER JOINS to handle NULLs and get values where there is no Join.
b. Displaying Rows as Columns and vice versa (see Ealing Reports)
Use the CASE statement which applies a Filter and has the effect of going through a table one record at a time and displaying the results as it finds them.
Appropriate WHERE conditions can generate the data where row values appear in columns.

9. Timestamp
In SQL Server, a Timestamp field helps to keep track of changes to a table in a Database.
To use it, you simply define a field without a name, and a data type of TIMESTAMP.
SQL Server automatically inserts an internal identifier into the column whenever a row is inserted or updated. To get the current value use ‘SELECT @@DBTS.

10. Triggers
A Trigger is a set of SQL Statements that is executed whenever a specific
.A typical use of Triggers is to maintain running totals for individual numeric values.
To achieve this, a Trigger is ‘fired’ or executed whenever a new record with a value is inserted (or updated).

11. Reservations and Checking Availability
This involves Scheduling and checking Bookings and availability
Should be simple by looking for Status=’Available’ and Date >=Start Date <= End Date
12. CALENDARS – Generating a Calendar uses Datepart in SQL Server
13. CASE – similar to IF/THEN/ELSE – used to turn Rows into Columns
Syntax –
SELECT a,b,c,
CASE WHEN(condition) THEN action ELSE action
FROM table_name
14. COALESCE – similar to IFNULL – returns first Non-NULL value in a list of values
Syntax – COALESCE (value1,value2)
15. EXTRACT – can be used to Extract Date parts –
Syntax - EXTRACT(month FROM date_field) or SUBSTR in Oracle
16. UNION and UNION ALL
Beware that UNION ALL is usually required –
because UNION implies a DISTINCT that does not return all the qualifying records.
17. Avoid Dividing by Zero – use NULLIF

The results of Googling for SQL Tricks and Tips
• Developer’s views - http://blog.cwa.me.uk/2007/07/11/a-few-sql-tricks-and-tips/
• Oracle :- http://www.artofprogramming.net/development/oracle-tips.html
• SQL Server Query Optimizer - http://www.developer.com/db/article.php/3418031
• SQL Tips and Tricks - http://www.xmlpitstop.com/VisitPage66033.aspx
• Professional Association for SQL Server (‘PASS’) - https://www.sqlpass.org/Pages/Default.aspx
• SQL Hacks for SQL Server - http://www.sqlhacks.com/
• SQL Server User Group - http://www.sswug.org/