Just for the heck of it, I scratched together a template parser for T-SQL . The usage of this function is similar to the STRING_SPLIT() function, except instead of splitting a string by a delimiter character, we want to split a string according to a defined template.
Notice how the “%” wildcard character denotes how the string is split. Unlike the fancy stuff you can do with regular expressions, T-SQL wildcards don’t allow you to define capture groups, so this function is unfortunately constrained to just using “%”. I hope it will still come in handy to someone out there.
I’ve been working on a little gadget for a while now, and today I finally got around to completing it and so now I’ve published it for everyone to try out. It’s a web API (wait, wait, don’t go away – it’s for database people!) that creates a randomized list of names, addresses, etc.
In this post, I’ll show you how easy it is to use this service to anonymize a development or test database so you don’t have all that personally identifiable information floating around.
If you’re an organizer, sponsor and/or an exhibitor at a SQL Saturday event, you often collect raffle tickets from attendees. In exchange for giving you their contact information, they have the opportunity to win cool raffle prizes.
Here’s the thing, though. Raffle tickets can be a bit painful to scan on your mobile phone. You point the phone at the QR code, then click the URL that opens up the browser, after which the SQL Saturday web service takes another second or two (or three) to load. This is fine when you have just a few tickets, but it can be mindnumbing if you have hundreds.
Typically, the advice on fill factor is “if it ain’t broke, don’t fix it”. But occasionally, you’ll find a database or even a server with a crazy default setting that just fills your disk and buffer pool without any real benefit. Here’s a nifty script to rebuild any tables and indexes to a fill factor of 100%.
Regular readers of my blog will know that I occasionally share some useful scripts on my Downloads page. And even though I update some of those scripts regularly when new versions of SQL Server come out, or if I run into a bug feature, there really hasn’t been a practical way for readers to subscribe to those updates or to contribute with good ideas.
I recently attended the annual PASS Summit conference in Seattle, and as part of my personal goal to try to learn new (and scary) things, I took a precon on working with Git.
So as of now, a bunch of downloads are available on GitHub (which is, really, a much better place to host scripts than a shared Dropbox link). You can download them as usual, and if you want, you can add your improvements and send me a pull request. I know I’ve received a ton of good ideas and suggestions over the years, but more often than not, I haven’t had the proper environment to test those changes in, or I just haven’t had the time to dig into my old code.
I’m the type of developer that invents wheels. Yes, every wheel I design is unique in its own way, and hand-crafted for a specific purpose. And so it has also been with calendar dimensions (typically when I do data warehousing work).
This got me thinking – why not design the mother of all calendar dimensions? One that includes every conceivable calendar and property that I and others could use and re-use. One that could save me a ton of coding, and lessen the burden of having to validate it each and every time?
And that’s how I got started designing my one calendar script to rule them all.
With good naming and datatyping conventions, an automated script can help you with the process of creating foreign key constraints across your database, or actually, suggest table relations where you’ve forgotten to implement them.
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.
I frequently need to look up object definitions when I’m developing or query tuning. You could use Object Explorer in SSMS, but that takes a lot of time and clicking. Then there’s the Alt+F1 shortcut, which will trigger the sp_help stored procedure. That however, comes with a lot of annoying built-in limitations, so a few years ago I started building and maintaining a “better Alt+F1” of sorts.
I decided to call it “Ctrl+3“. But I suppose you could assign it to any keyboard shortcut you want.