SQL SkillsSQL Skills
SQL
I first started using SQL for an actual shipping product in 1995. Before that I did some prototypes. This SQL was created dynamically and then sent to Oracle using OCI (the Oracle run-time interface). Later I would do something similar using ODBC.
I have used Oracle, MS SQL Server, Sybase and DB2 database engines. I have not done much with Sybase and DB2.
With DB2 I attempted to port an application from Oracle to DB2. Although we got a few queries and updates to work, DB2 had the rather draconian limit of only 19 characters for a column name. We abandoned the DB2 port at this point hoping that DB2 would change before we needed to use it.
Eclipsys purchased a company that used Sybase and I called a meeting for people from both parts of the new company to look at how we could use both Sybase and Oracle. We started down this path and had positive results but we never got a customer that needed Sybase. We were frustrated by the lack of user-defined functions in Sybase which our ETL tool made great use of.
SQL Server was much like Sybase in many ways although the new SQL Server has two features that we dearly missed after using Oracle: user-defined functions and cascading deletes. We found SQL Server (even the earlier version, SQL Server 7) to be easier to work with than Sybase so we did all non-Oracle work on it. This product is now being used at least internally at Eclipsys. Even one year later, I am still called in to help with SQL Server questions.
Currently, all of my database work is in SQL Server. I have a MCDBA from Microsoft and I used Data Warehousing as the elective test. I have set up backup jobs, DTS jobs, replications, stored procedures, triggers, indexes and more. I estimate that I have created more than 100 SQL statements for the current web-based application. I needed to add three triggers for every table in our database (about 50 tables). I didn't want to write all of these by hand (particularly since I might have to change them) so I created a Visual Basic program to generate them.
At Eclipsys we used Oracle more than any other database. This included using Pro*C, PL/SQL and the bulk loader. We have used Oracle at our user sites as well. We had one occasion where the customer complained of poor performance even though they had just upgraded their hardware. Oracle support couldn't help us. Eventually I found that they had been using the "bequeath" protocol (BEQ) and now they weren't. This correction made a huge performance difference.