Pay Yourself First

The biggest problem I see is the huge number of DBAs who have let themselves fall behind. Live in interview mode, and pay yourself first, and you won’t fall behind!

I’ve done a ton of interviewing in my career, both as the interviewee, and as the interviewer.  The biggest problem I see is the huge number of DBAs who have let themselves fall behind. How does this happen?

Middle of the pack

Once you’re a more or less established DBA, you have yourself a job, and you go in every day and you do your duty.  You back up what you need, do the restores you need, fix replication, look at a couple bad queries, and set up a new server for the latest project.  You’re busy as hell at work, and there’s no end in sight to all the tasks and projects and problems.

That’s the problem with being a DBA these days: you’re always swamped with problems. (The real reason is that companies are absolutely dedicated to not taking data seriously, but that’s another article entirely.)

Losing ground

So you work, and you get further and further behind the learning curve because there’s no time to do anything for yourself: no time to learn anything new, pick up a book, watch a tutorial, or even practice what you already know.  You’re always putting out fires!

Then, when it’s time to interview for a new job, you find yourself cramming in the last couple days to try to bone up on your knowledge.  Speaking as someone who’s interviewed a lot of DBAs: this definitely shows! Anyone who conducts any amount of interviews at all can tell when you’re just barely recounting something and when you know the topic cold.

Live in interview mode

Okay, I have a radical, two-step plan for your professional development. Here we go:

  1. Stop cramming for interviews like you’re trying to pass a test.
  2. Live in interview mode.

Interview mode (n.) – The practice of conducting your daily work life as if an interview could happen unexpectedly, at any time.

Take time to study every day.  It doesn’t matter how much, but I think 30 minutes isn’t too much to ask.  Even if you’re not studying to interview, your skills will get rusty when you don’t put them into practice for a while. Chances are, your company won’t ever give you the time to do it, so you have to take that time yourself.

Pay yourself first

Every day when you come in to work, take 30 minutes to work on something for you.  Learn how to do partial restores.  Learn how to set up an Availability Group.  Learn how to add a primary key to a table using T-SQL.  Learn XML, or JSON, or HTML.  It doesn’t matter.  Pick something up that you want, or something that you know you lack.

I call this paying yourself first, which is actually a financial term:

Pay yourself first is a phrase referring to the idea that investors should routinely and automatically put money into savings before spending on anything else. – InvestingAnswers.com

When it comes to your career, make sure you routinely and automatically put time into your development before spending it on anything else.

When someone comes to your desk and asks you to do something, tell them that you’re doing your daily checklist, and you’ll be with them in a few minutes.  (People at work don’t understand that daily checklists are dumb out of style, so they’ll leave you alone.)  Your company won’t give you the time to do this, so you have to take the time.

Study first thing in the morning, before things get started. Once the day really gets going, it’s hard to even remember studying, much less to find the time.

You may not be able to do it every day.  There may be some days when you walk in and there’s some emergency that honestly takes priority.  That’s okay, take care of your emergency.  But outside of that, there’s very little that can’t wait 30 minutes, especially when you’re “doing you’re checklist to make sure things are okay”.

So take some time to be good to yourself before things get crazy every day.  Improve yourself, live in interview mode, and pay yourself first.

 

 

Photo by Tim Gouw on Unsplash

Coding Naked

Let’s talk about CASE tools, and why coding for databases without one feels so….exposed.

I know the title sounds like clickbait, but it’s how I’ve felt for a long time.  Why? Because of CASE tools.

CASE stands for Computer-Aided Software Engineering. Here, I’m referring to data modeling tools like ErWin and ER/Studio.  It disheartens me that the industry as a whole has gotten away from using these tools. In this world of rushing software to market, we’ve forgotten that the data-driven marketplace starts with the word ‘data‘.

A brief history of CASE

I started in IT in the 90s when things moved a little slower and we actually took time to model databases.  [Editor: I somehow managed to resist inserting a gif of an old man and a cane.]  So, I got used to using CASE tools, and by the year 2000 I was quite adept at using the different flavors of these tools.  And I’m going to tell you, I miss them greatly.

The word domain has had a lot of play in IT, because it’s a nice generic container for many things. In the 1990s, a domain was a universal concept when working with a CASE tool. In SQL, we have another word for domain: column.  Old timers like Joe Celko get really irate when you call a domain a column, because they know what it’s for.  But we’ll get to that in a minute. First, let’s talk about the difference between a domain and a column.

Column vs Domain

A column is a single property in a table, which holds the same type of data throughout all rows.  Think of an Excel spreadsheet that stores a list of names.  That’s how data is represented to us, even in SQL Server Management Studio (and practically every other query tool out there).  We see the data as these little on-the-fly spreadsheets.  But, that would be a very myopic view of what a domain is.

In data modeling, a domain is implemented as a column. But, the domain itself is an organizational structure for column definitions.  A single domain can apply to individual columns in dozens of tables, if you like. Let’s take an example.

Let’s say you’re writing a SQL application, and you sit down to create a table for it. In that table, you need a FirstName column.  After some thought, you give that column a datatype of VARCHAR(50).  You then get pulled away, and don’t get back to the code for another week or so. When you return to the code, you pick up where you left off and start creating another table.  The second table also needs a FirstName column. As it’s been weeks, you forget that the first table has a FirstName column with VARCHAR (50), and you’re in a different mindset today, so you give this new FirstName column VARCHAR(75).

This sort of thing happens all the time, especially in projects with multiple developers. See how this app is just begging for bugs?  These bugs can be very difficult to track down.

Developing with CASE and domains

This is where domains and CASE tools come in.  In a CASE tool, you can define FirstName as a domain.  That domain a single entity where you define the datatype, length, nullability, and often even more properties.  Then, when you’re creating tables in your CASE tool, you associate each column with a domain, instead of assigning datatypes.

With a defined domain, every column you associate with that domain automatically gets consistent properties.  Furthermore, if you have a change of heart and decide to change FirstName from varchar(50) to nvarchar(100), you change it at the domain level. Then all the columns that use it pick up the changes.  (I’ve always liked how meta it is that you’re normalizing the data model process itself.)

You can see why the old-timers insist on calling it a domain: because it is.  It’s not a single instance of a FirstName…it’s the domain of FirstName, which every FirstName column will take its properties from.

Fill your data applications with domains, and not columns.  Now that you’ve got a domain for FirstName, you can create domains for every other ‘column’ in your different data projects.  Even better, you can reuse these domains in your different projects, so FirstName is the same in every application you write!  This is what we call a data dictionary.  It’s the right way to do things because it forces you to think about the right data type for the job, and then it enforces it throughout your entire data enterprise.

What’s more, publish your data dictionary! Anyone writing writing stored procedures and other code against your databases should use that data dictionary to determine what datatypes they need for parameters, variables, temporary tables, and the like.  With any luck, they would look up those values in the database anyway, so you may as well expose them all in a single, searchable place.

Coding naked, without CASE

All of this is to show now naked I feel when I code these days.  You see, I don’t have access to a CASE tool any more, because they’re so expensive.  The companies that sell them think an awful lot of the software, while the companies I’ve worked for don’t. So, I haven’t been able to talk anyone into footing the bill in a long time.

The industry as a whole doesn’t see the value in not rushing software out the door, so nobody takes time to actually model data anymore.  [Old mane cane gif, resisted again. -Editor] So, the tools should be cheaper because demand isn’t very high.

All I know is that my projects have suffered as a result.  I’ve had silly bugs and inconsistencies that are pointless, because I was in a different frame of mind when designing different tables, and didn’t realize I was creating my columns with different properties.  Until I can solve my CASE tool problem, it’s like coding naked because I’m exposed to bugs, data anomalies, performance issues due to implicit conversion issues, and probably more.

Let’s get back to a time where we can slow things down and start doing things right.  And vendors, stop treating CASE tools like a precious commodity.  Price them to own and you may see an increase in sales.

"Old man yells at cloud."
Close enough. -Ed.

Avoid this sp_executesql issue

If you don’t use this feature very often, you can come across some unexpected sp_executeSQL issues. This time we’ll look at an sp_excuteSQL output issue.

If you don’t use this feature very often, you can come across some unexpected sp_executeSQL issues.  In fact, I’ll probably use this blog the most because I don’t use it that often and I have to look up the specifics every time.

Let’s start with a code sample so we can see what happens when we run it.

Setting up the sp_executeSQL issue

Above you’ll see some really simple sp_executeSQL syntax.  All it does is to parse out the YEAR from the current date.  And you’ll see that I’m taking that result and putting it into a variable called @DynamicResult.  Finally in line 6 I select the variable to see my value.  But the results aren’t what I expect.  Take a look at the results and you’ll see what I mean:

sp_executesql issueThe problem here is clear; I’m getting two resultsets! The first one has the value I’m after, and the other one is NULL.  And looking at it, the NULL value is the one that’s supposed to be the variable.  So how do I get the value into my variable, and get this down to a single result set?  The answer is that you have to treat it like it’s not inside the sp_execute at all.

Understanding the sp_executeSQL issue

Here’s what I mean.  Let’s assume you’re not running this command in sp_executeSQL.  Let’s assume you just want to run it as a regular query like this:

Now this is something you’re a bit more familiar with.  You declare the variable, then you set it equal to the result of your function call.  Then you select it.  We have no problem filling our variable when we do it this way.  And that’s the way you have to do it inside your dynamic code to avoid this sp_executesql issue.  And just for completeness, here’s the result:
spexecuteresults3

Fixing the sp_executeSQL issue

It’s easy now to see what needs to be done.  All we have to do is make sure that we use that same syntax inside the procedure call and we’ll avoid this sp_executesql issue.  Take a look at the fix and you’ll see that it’s exactly like the code above where we just ran the straight statement.

If you didn’t notice, the difference is in the dynamic query itself.

Okay, that’s it.  All you have to do is make sure that you output that exact variable and you’re golden.  You can see that the variable I’m setting inside the dynamic SQL string is the same one I’ve got marked as OUTPUT.

These types of features in SQL are very handy but they all have pitfalls and nuances.  And learning how and when to use them can be tricky because the solution in one case won’t be the solution in another case.

Download our FREE maintenance modules below:

  • Minion Backup: Free SQL Server Backup utility with world-class enterprise features and full lifecycle management.
  • Minion Reindex: Free SQL Server Reindex utility with unmatched configurability.
  • Minion CheckDB: Free SQL Server CHECKDB that solves all of your biggest CHECKDB problems.

 

Using SQL Server OUTPUT Parameters

Let’s talk about using SQL Server OUTPUT parameters!

Last time we talked about where OUTPUT parameters are useful.  This time, let’s talk about using SQL Server OUTPUT parameters.  So let’s get started.

OUTPUT parameter syntax

For starters, a lot of people hate the OUTPUT parameter syntax.  They think it’s bulky and too much trouble.  Honestly, I used to think that too, only now I’ve got enough experience with them that it’s not that bad.  Or maybe I’ve been beat down by it so much that I don’t even notice anymore.  Well, whichever it is, here’s the syntax for using SQL Server OUTPUT parameters.

In the above code I’m creating a stored procedure named SP1.  That procedure has a single parameter that’s declared as an OUTPUT parameter.  This much you know from the last post.  Now, starting on line 16, I declare the variable that’s going to pick up the value.  I’ve given it a name that lets you know exactly what it’s going to be doing.

Here’s a screenshot of the code if you’d prefer:
OUTPUT parameters

OUTPUT Parameters have Backwards Syntax

And here’s where everybody’s hate for this topic kicks in.  I call the procedure in line 22 and I assign the variable to the OUTPUT parameter.  However, let’s take a step back and see why this is so strange to everybody.  When you normally assign a value to a variable, it usually goes left to right like this:

So you assign variables to values like in the code above… from left to right.  However, in the original code block, it’s backwards.  The value for @NameBackupFromSP comes from the left.  And this is essential to understanding how to use OUTPUT parameters.  When you assign them, the assignment is backwards from what you do in every other language.  This is a by-product of mixing methods and here, the stored procedure syntax had to win.  Here’s what I mean.  When you pass in values to stored procedures, you always assign the values from right to left, like you would expect.

So the above is what you would expect to see when doing regular assignment to a stored procedure.  And that’s what I mean when I said above that the stored procedure syntax had to win.  No matter what, when calling a stored procedure with parameters, you have to use this syntax (assuming you’re naming your variables, that is).  So when you go to use OUTPUT parameters, the same stored procedure syntax holds true.  Only now you’re signifying that you also want to get that value back out as well.

So we assign the @Name parameter the same way we do if it were a regular assignment, only now we use the OUTPUT keyword to tell SQL that we want to also get that value back out.  And of course, nothing is going to happen visibly unless we select the variable so we can see it.

That’s the basics of how to call a stored procedure with OUTPUT parameters.  Next time we’ll get into more details of how to use this feature.

Article 1: Learn SQL Server OUTPUT Parameters

Download our FREE maintenance modules:

  • Minion Backup: Free SQL Server Backup utility with world-class enterprise features and full lifecycle management.
  • Minion Reindex: Free SQL Server Reindex utility with unmatched configurability.
  • Minion CheckDB: Free SQL Server CHECKDB that solves all of your biggest CHECKDB problems.

Learn SQL Server OUTPUT Parameters

There are plenty of sites out there that show the syntax of using SQL Server OUTPUT parameters in your SPs, but not many take the time to explain the syntax in detail, or even why you should care. Here I’m going to show you why OUTPUT parameters are a great way to achieve your programming goals.

There are plenty of sites out there that show the syntax of using SQL Server OUTPUT parameters in your SPs, but not many take the time to explain the syntax in detail, or even why you should care.  Here I’m going to show you why OUTPUT parameters are a great way to achieve your programming goals.  This is going to be a multi-part series.

SQL Server OUTPUT Parameters: The Why

There are a couple reasons why you’d want to use OUTPUT parameters:

  1. You need to get a couple solid values out of the procedure.  This is a common requirement and something that a lot of people use functions for.  And let’s face it, functions are very handy for giving you scalar values.  However, functions also have some pretty strict guidelines; you can’t do anything that generates log activity, for example.  So in these cases, where you need scalar data, but a function won’t do, you can switch to a stored procedure with OUTPUT parameters.
  2. You’re trying to pass data from one procedure to another, but you’re running into the ‘nested INSERT…EXEC’ issue.  This issue happens when you have an INSERT…EXEC in say SP1, and then you call SP2 from SP1, but SP2 also contains an INSERT…EXEC.  SQL Server won’t allow this and believe me when I say it’s a huge pain because there are some things you simply can’t reasonable do without INSERT…EXEC.  So, so avoid this nesting of INSERT…EXEC, you can use OUTPUT parameters.

SQL Server OUTPUT Parameters: The How

Creating SQL Server OUTPUT parameters is easy, but the syntax isn’t intuitive at all.  So I’m going to talk you through the syntax of creating a procedure with an OUTPUT parameter, and then in the next post we’ll talk about how to call that procedure and get the values we need.

So again, SQL Server OUTPUT parameters are basically used in lieu of a function when either the function restrictions are too great, or when you just need better performance.

Therefore, to create a stored procedure with an OUTPUT parameter, you simply need to define it as OUTPUT in the parameter section like this:

CREATE PROCEDURE SP1
(
Name varchar(20),
Zip varchar(10),
Phone varchar(20) OUTPUT
)

AS

You can see that we have 3 INPUT parameters, and even though the OUTPUT is the only one that’s actually marked, the others are definitely INPUT parameters.  The default parameter direction is INPUT, so it doesn’t get marked.  Therefore, only the OUTPUT gets marked.  When we call this procedure we’ll pass in the Name and Zip we’re interested in, and we’ll get back the Phone.  So if I wanted more than one piece of information, then I would specify more than one OUTPUT parameter like this:

CREATE PROCEDURE SP1
(
Name varchar(20),
Zip varchar(10),
Phone varchar(20) OUTPUT,
CustomerID int OUTPUT
)

AS

SQL Server OUTPUT Parameters: The Practice

Here’s a screenshot from one of our procedures in our FREE maintenance modules to show you how this will look IRL.

SQL Server Output Parameters

Ok, that’s really all there is to creating a stored procedure with an OUTPUT parameter.  And next time we’ll talk about how to get the parameter value into your calling procedure.

Article 2: Using OUTPUT Parameters

You can download our FREE maintenance modules below:
Minion Backup: Free SQL Server Backup utility with world-class enterprise features and full lifecycle management.
Minion Reindex: Free SQL Server Reindex utility with unmatched configurability.
Minion CheckDB: Free SQL Server CHECKDB that solves all of your biggest CHECKDB problems.