SQL & how different databases implement it

SQL Server 2000 can not be installed on Windows XP. If you would like to get a "near SQL Server" experience on your Windows XP PC, then read on.

Check your PC has 512MB of memory, Windows XP installed and at least 200MB of free disk space and that you have administrator privileges.

Download SQL2000.MSDE-KB884525-SP4-x86-ENU.EXE (c.78.5MB) from the Microsoft web site. Do not use Mozilla Firefox to download this package.

From the folder you have downloaded it to, double click it. Click on Accept if you agree to terms of the license.

Choose an installation folder of your choice. C:\SQL2kSP4 is the default. The package is unpacked into this folder.

Go to installation folder, navigate to MSDE subfolder and edit the setup.ini to add two lines after the first (and only) line which contains the text "[Options]":
SAPWD="sql"
INSTANCENAME="sql001"
Note the double quotes are required.

Then execute the setup.exe in the installation folder. Wait a few seconds for it to do its work. There is no success message, but after it has run look at the list of Windows services. Two entries appear as MSSQL$SQL001 and MSSQLServerADHelper. Right-click and start the former of these to check it is successful. Then stop the service.

Now install the client tools from the SQL Server 2000 CD (for example part number X10-72440 EN). Insert the CD. Autorun shows an install panel. Click SQL Server 2000 components. Click Install Database Server.

Installshield starts and an expected error message shows that SQL Server is not supported on this operating system. Click OK.
Click Next
Select local computer and click Next
Select Create a new instance and click Next
Enter your name and company name and click Next
Click "Yes" to accept the license if you agree to its terms.
Enter the CD Key xxxxx-xxxxx-xxxxx-xxxxx-xxxxx (you will have to supply your own valid license key :-) ) and click Next
Select Client Tools Only and click Next
Leave the default component selection (all selected except Code Samples) and click Next
Click Next to start the install.
When progress bar reaches 100% the installation finishes.

Now from the Windows desktop click Start -> Run -> SVRNETCN.EXE
Enable TCP/IP as a protocol by selecting it and clicking the enable button, then Apply. TCP/IP is the only protocol required, the other two can be left disabled. Then click OK. Acknowledge the popup messages.
Remove the CD and reboot the PC.

When Windows starts you will see a new icon in the systray (bottom right) with a green triangle / arrow on it.

Start -> All Programs -> Microsoft SQL Server -> Enterprise Manager
In the left hand pane, click until you can see the server SQL001. Open that up and the databases hierarchy.

Right click on Databases and select "New Database". In the popup window choose a name of “sqlexpert” without the double quotes. Click OK. This new database now appears in the list in the left hand pane.

If you have a database backup from another server, right click on the sqlexpert database and choose All Tasks, then Restore database.

If not, then go ahead and start to create your tables in the database. From here on you will be able to use Enterprise Manager and SQL Query Analyzer just as you would if the database server was on high-end kit on a fancy server. Except that it is all local. Perfect for training, presentations and demos.

Do you try to establish a visual style for laying out your SQL statements? I have a particular style for my SQL, but to be honest I am not sure if it adds any value or not. Your feedback would be appreciated.

In essence what I try to do is have a single space after the first keyword of the statement which is carried through a vertical line all the way down the subsequent lines of the SQL statement.

For example:
SELECT ramp_agent_id
  FROM dispatches
 WHERE delay_mins > 5

Notice how I have right justified the keywords to the left of the vertical spacer (i.e. SELECT, FROM and WHERE) and left justified text to the right of the spacer.

Group By is too long to fit as a whole to the left of the spacer, so I only fit Group to the left and put By to the right. For example:
SELECT ramp_agent_id, COUNT(*)
  FROM dispatches
 WHERE delay_mins > 5
 GROUP BY ramp_agent_id

Although when adding a group by I could move the first three lines of the above example to the right so that the vertical spacer was after "BY" I deliberately don't do this as 2 or more select statements near each other in the code, one with and one without a group by look odd (to me).

Things get a little more complicated with more complex WHERE clauses. This is how I deal with that:
SELECT ramp_agent_id, COUNT(*)
  FROM dispatches
 WHERE delay_mins > 5
   AND (   delay_code = 500
        OR flight_status = 'A')
   AND destination = 'LHR'
 GROUP BY ramp_agent_id

Here I am lining up a subsidiary vertical spacer in the WHERE clause to align the various predicates that are placed inside the brackets.

The above is based on my background with Oracle (the DBMS I was using when I was first paying serious attention to these things).

Anybody got other schemes they use?