Spikeh'd

I've spent many years debating with my fellow developers across all manor of subjects (I do love to talk), but one subject that comes up time and time again is the usage of the UNIQUEIDENTIFIER data type in SQL Server; especially when they're used as identity and key columns. In fact, I see this more often than you would expect, and misconfigured UNIQUEIDENTIFIER columns can create "hidden" problems that can be difficult to discover and / or rectify, depending on the SQL experience throughout your team. If you care to ask around your colleagues, it's almost guaranteed that you'll get multiple conflicting opinions on why you should or should not use GUIDs in SQL Server, but few developers actually realise the impact of such a design choice. In the interests of science, I'll try and keep this as factual as possible, and I'll focus on the topic at hand, rather than GUIDs / UUIDs in general. Here's a list of common reasons for using a GUID in your d...

I've been doing a lot of database analysis recently, and fortunately I'm one of those people that enjoys it. I'm primarily a .Net developer, but I've spent many years looking after SQL Server databases, helping out (and even educating) DBAs, writing ETL packages and designing both large and complex databases. The database in question was in pretty bad shape; missing, unnecessary, and extremely fragmented indexes, missing primary and foreign keys, bad normalisation, and (the main reason I decided to look into it) undesired table locks during ETL operations. It's a 24/7 system, and live lookups are performed around the clock - data is imported early in the morning (when usage is at a minimum), which is when most of errors occur. Amongst many other pre-emptive actions I've recently performed on the database, I decided to set up regular table index maintenance job to keep queries as quick as possible. Unfortunately the database is SQL Server Standard edition, an...

So yesterday I made the second ever major technical boo-boo of my career - I accidentally ran a dynamic "DROP TABLE" script on an extremely large, high-availability system. The last time I did something of this magnitude was literally when I first started out with T-SQL, and I ran a load of DROP / CREATE PROC statements on a similar sized system, right in the middle of rush hour; instantly breaking the 500 of so clients that were connected to it. Obviously this kind of thing is very embarrassing, not to mention the massive inconvenience caused throughout the departments using the system. Luckily the database is backed up hourly (thank you Mr DBA), and although I made the mistake 6 minutes before the next back up, the impact was relatively minimal due to the main user's being in a departmental meeting. I'm blogging to outline my mistake and document changes to my T-SQL script, which up until 13:34 yesterday, made my life considerably easier and I thought was the dog...

Following on from my last post  complaining about MSSQLMS UI code generation , I thought I would back it up with a few T-SQL scripts that generate code that I actually want to see. I'm currently developing a large scale, real life application using my new .Net 4.0 nTier framework , so I've decided to make my life a little bit easier by generating the boring old CRUD stored procedures with some funky T-SQL scripts. I'm also currently looking in to Visual Studio's T4 code generation tools to help me create my business objects, as most of those will also be fairly standard - though I still need the flexibility to change them (and guarantee that a code generation tool isn't going to overwrite my changes) once the templates have all been created. Let me start by saying I'm usually against the use of the 'sp_executesql' stored procedure due to the additional overhead created by executing dynamic SQL. In this instance however, I'm happy to use it becaus...