SQL Server Management Studio allows you to view effective permissions on an object, but it’s limited in a few important respects. To work around some of those limitations, I’ve built a stored procedure to display all the defined and effective permissions across an entire SQL Server database.
Author: daniel
Fun with random names
Just for laughs, we’re going to take a look at a slightly-too-complex yet elegant solution to a seemingly simple task: creating a list of 100 fictional company names for a demo database.
How good are best practices?
You’ll find a lot of people, consultants and peers, liberally dispensing plenty of “best practices” on the Interwebs and in real life. Heck, I’d say that’s pretty much all I do on this blog. But when should you go with the best practice and when should you make your own road?
Prioritizing rows in a union
I just remembered a pretty common data challenge the other day. Suppose you have a number of tables, all with similar information in them. You want to union their contents, but you need to prioritize them, so you want to choose all the rows from table A, then rows from table B that are not included in A, then rows from C that are not included in A or B, and so on.
This is a pretty common use case in data cleansing or data warehousing applications. There are a few different ways to go about this, some more obvious than others.
Accounting basics for tech people
Just like the tech business, accounting uses loads of acronyms and seemingly undecipherable names, but the basics are actually really simple. I’m not saying that accounting in and of itself is simple, but rather that the concept is quite understandable and convenient if you approach it correctly.
The goal of this post is to provide a basic understanding of the basic principles of accounting, which a lot of tech people (particularly in the database business) will at some point encounter.
Automatically guessing foreign key constraints
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.
Finding primary key candidates
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!
Please don’t feed auditors and lawyers
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…
Speaking at the Group By conference!
This past Friday, I had the great privilege of speaking at the on-line Group By conference. Group By is a community-driven conference where anyone can submit an abstract. Site visitors will then rate sessions as well as help you build and improve your abstract.
My presentation was about various tips and tricks in SQL Server Management Studio, some of which I’ve already covered in previous articles on this blog.
Practical uses of binary types
The binary datatype of SQL Server is one of those features most developers don’t really use that often, but it turns out there’s more to binary values than just storing large, non-relational blobs.