Probably one of the most common challenges I see when I do ETL and business intelligence work is analyzing a table (or a file) for possible primary keys. And while a bit of domain knowledge, along with a quick eye and some experience will get you really far, sometimes you may need some computational help just to be sure.
Here are some handy tricks to get you started!
Remember that time when you accidentally truncated a table in production? Or when you forgot the WHERE clause in your UPDATE statement? You’re not really a seasoned professional if you haven’t. There’s even a very apt name for that moment in time when the realization hits you: The oh-no second.
But what if there was some type of control to prevent this from happening? Like more restrictive controls, perhaps some type of peer-review process before you clicked “go”? Or even…
In a sense, you could call me lazy. If there’s a script that will perform a task for me, I’d rather use that script than reinvent another wheel. Then again, if needs be, I’d rather spend a day writing such a script, rather than spending ten minutes just getting the job done.
Somehow, that makes me a happier developer.
Want to try something in SQL Server, but don’t have a server handy where you are? Check out SQL Fiddle – it’s a kind of web scratch pad for SQL code (not just SQL Server, by the way, lots of other platforms).
This is truly a beautiful tool with separate entry panes for schema and DML statements, as well as the possibility to view or download query plans for each query. And when you’re done editing, you can copy a URL and post on online forums, in e-mails, etc, so other people can run and test your code.