The SQLChicken, Jorge Segarra, (blog/twitter) is hosting this months T-SQL Tuesday. It’s called “T-SQL Tuesday #007: Summertime in the SQL”. The question is “What’s your favorite hot new feature in the R2 (I’ll be nice and include 2008 in general) release?”
Well, the answer to the question is simple – the merge statement…and IntelliSense…and the resource governor…and compression both for data and backups. Okay, there are lots of good changes in SQL 2008 and I’ve seen lots of changes in my day.
In my old guy voice I say, “Young DBA’s these days, don’t know what it’s all about. Things are easy, point and click, drag and drop, right click, create script, etc….”
Back in my day…when we want to move data, we would have to create update statements, insert statements (usually based on a outer join where the joining column was null) and delete statements. There wasn’t any such thing as a merge statement. And we used to have to type these things out by hand, or copy and paste them from a result set from sysobjects – there was no IntelliSense. The way I used do it was to create temp tables, stage the data usually by creating a script in Excel and copying it for all the rows in the table. We had to write out Insert #TempTable1 Values (<A1>, <A2>…) then on the next line it would be Insert #TempTable1 Values (<B1>, <B2>…). There was no Insert #TempTable1 Values (<A1>, <A2>…), (<B1>, <B2>…). I usually had my standard of #TempTable1 would be updates, #TempTable2 would be inserts and #TempTable3 would be deletes. There wasn’t any table variables either, so we always had to drop the temp tables – or TempDB might fill up during maintenance.
Back in those days, there was no auto file grow either. There better be enough room in the database files for everything to fully complete or things were going to fill up and be left in an incomplete state.
There was no resource governor either, we scheduled index rebuilds, data imports, data cleanup tasks for off hours – when the systems weren’t expected to be online.
Back in the early days, there were no DDL triggers either, there was no SOX, so there wasn’t as much of a need to have DDL triggers. If someone needed a change in production, the green play button on the procedure you were looking at would change it. There wasn’t a “Generate Change Button” for tables and columns either, we did it the same way we did it in the development environment – by hand via the GUI.
Even creating databases, we had to create the devices first, then the database and we had to back them up to backup devices (that actually made restores simpler as the file name was always the same). There was no compression built into SQL. For large backups, we used 3rd party tools and then we’d include the tool in the backup directory and put it to tape. If we ever had to restore the backups, we would have the tool on the tape as well – who knows how many licensing agreements we broke by doing that.
When trouble shooting issues, we used to have keep hitting the refresh button in activity monitor to see if anything had changed – there was no auto refresh. There were no filters in it either, so we had to look through all the connections to the server.
If we found a performance issue, we didn’t have an execution plan to go off of, much less SQL didn’t tell us what indexes were missing! I always used the “Set Statistics IO” option and looked at the logical reads.
Things have changed a lot since SQL 6.5, but the bottom line is that a table is still a table, a column is still a column and an index is still an index (although those are changing)