Skip to content

The April Blogger Challenge

I’d encourage you to take Ed Leighton-Dick’s challenge to blog in April. Read his post, start writing, and put your post out there. Tweet about it, and be proud.

However, if you’ve never blogged, I have a modification for you. Publish privately. The important thing is to just start writing and communicating.

If you’re looking for help getting started, I’ve got a few posts for you:

Blogging is a great way to give potential employers some insight into who you are. My view is this can only help you find a better job that’s a good fit for you. If you work at it and to it well.

Efficiency

This editorial was originally published on Oct 22, 2010. It is being re-run as Steve is away on vacation.

There was a debate recently about older IT workers and the fact that there is some age discrimination going on in this industry. While that’s probably true, it happens in other fields as well. Especially blue collar fields where the amount of work done often relates to some physical activity. Older workers might make less, or be paid less, because they do the job differently.

Without getting into the debate about age discrimination, I wanted to get a feel for how you feel about your career progressing. I’m sure those of you in your 20s can’t conceive of being 60 and programming any differently than you do now, but times will change for you.

One of my feelings is that there is a lot of value in experience. The latest .NET changes aren’t harder to use or understand than the previous ones, and the changes to T-SQL bring about new things to learn, but they aren’t really any more complex than previous features, commands, and constructs. For this Friday, I wanted to ask this:

Are you becoming more efficient as you progress in you career?

Maybe you can answer how you are getting more efficient. Are you developing skills that allow you to produce higher quality code in less time?

I hope that most of you are, and the explosion of blogs, books, and events like SQLSaturday mean that you have more chances than ever to learn how to become more efficient in whatever type of technology you work with in your career.

 

tSQLt with TRY..CATCH

Someone asked me the question recently about how tSQLt works with TRY..CATCH blocks and the exceptions that we might test for. It works fine, just as it would with other code, but you need to understand that a CATCH still needs to re-throw an exception.

Here’s a short example. I’ve got this query, which has issues.

SELECT TOP 10
         cs.CustomerID
     ,   cs.LastSale
     ,   cs.Salesman
     ,   CAST(cs.SaleValue AS NUMERIC)
     FROM
         dbo.CustomerSales AS cs;

If I run it, I get this:

Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting varbinary to data type numeric.

The CAST here has issues, but that’s fine. Perhaps it’s a data issue, perhaps something else. I can test for that, but for now, I want to be sure I handle these errors correctly.

Now, I embed that in a TRY..CATCH block.

    BEGIN TRY
        SELECT TOP 10
                cs.CustomerID
            ,   cs.LastSale
            ,   cs.Salesman
            ,   CAST(cs.SaleValue AS NUMERIC)
            FROM
                dbo.CustomerSales AS cs;
    END TRY
    BEGIN CATCH
        SELECT @@ERROR
            ,  ‘A CASTing Error has occurred.’
        ;

    END CATCH;

If I do this, and in the CATCH block I "handle" the error, I’m not really error handling. I’m error swallowing. Here are my results.

EXEC spGetCommission 12

casterror

I could log this, or try to return some data with a new query, maybe alter something that ensures the client gets results, but what I really need to do is give an error back, but one I’m aware of.

We could delve into error handling, but I won’t do that here. Instead, I want to be sure the application gets an error, when we have an error. It can then decide what the user does or sees.

If I write this test:

ALTER PROCEDURE [misc procs].[test spGetCommission Exceptions]
AS
BEGIN

– Assemble
EXEC tsqlt.ExpectException;

– ACT
EXEC dbo.spGetCommission @userid = 0 — int

– Assert
END;

Now I can run it, but it fails. I see the failure

test1

and I see this in the results

test2

What I should have is something more like this:

    BEGIN CATCH
        THROW 51001,  ‘An CASTING Error has occurred.’, 1;
    END CATCH;

 

Then my test should be looking for that message.

ALTER PROCEDURE [misc procs].[test spGetCommission Exceptions]
AS
BEGIN

– Assemble
EXEC tsqlt.ExpectException
   @ExpectedMessage = ‘An CASTING Error has occurred.’
   , @ExpectedErrorNumber = 51001
;

– ACT
EXEC dbo.spGetCommission @userid = 0 — int

– Assert

 
END;

If I do that, things work well. The error is handled, but also re-thrown, and my test passes.

test3

The Basic TRY..CATCH

Have you written a TRY..CATCH statement in T-SQL? I hadn’t done it for most of my career, since the construct hadn’t existed. As a result, my code over the years is littered with catching @@error in a variable and then acting on that result. 

However I’m trying to do better, and when I went to write one recently, I realized that I wasn’t doing it enough as I needed to check some syntax. Here’s a short post to try and capture that information and burn it into my brain.

The Syntax

The basic syntax is this:

BEGIN TRY

– do some work here.

END TRY

BEGIN CATCH

– error handling code here.

END CATCH

This almost seems funny as I’d expect a TRY with a BEGIN END block in the SQL language, but this reads better, and I think this is (Syntactically) a better implementation in the language.

Using TRY . . CATCH

The use of this is to do some work in the TRY block (BEGIN TRY..END TRY) and expect it to work. For example, I recently had this:

BEGIN TRY
    SELECT TOP 10
            cs.CustomerID
        ,   cs.LastSale
        ,   cs.Salesman
        ,   CAST(cs.SaleValue AS NUMERIC)
        FROM
            dbo.CustomerSales AS cs;
END TRY

BEGIN CATCH

– CATCH BLOCK

END CATCH

SELECT @@rowcount

The TRY block is the place where I perform some work. If it works as expected, then I just continue on. In this case, this should be a simple query that runs, and when it finishes, the SELECT for the rowcount executes.

However, if some error occurs, execution immediately goes to the CATCH block. In that case, whatever I have in that space will execute and then the execution will continue.

Example

Let’s look at an example of how this works. Here’s my full TRY..CATCH with a few print statements to track the activity.

ALTER PROCEDURE spGetCommission
@userid INT
AS
PRINT ‘Before TRY';

    BEGIN TRY
        PRINT ‘Start TRY';
        SELECT TOP 10
                cs.CustomerID
            ,   cs.LastSale
            ,   cs.Salesman
            ,   CAST(cs.SaleValue AS NUMERIC)
            FROM
                dbo.CustomerSales AS cs;
        PRINT ‘End TRY';
    END TRY
    BEGIN CATCH
        PRINT ‘Start CATCH';
        THROW 51000, ‘A calculation error occurred’, 1;
        PRINT ‘End CATCH';
    END CATCH;

PRINT ‘End of proc';

GO

If I not execute this, with a parameter, I get this:

Before TRY
Start TRY

(0 row(s) affected)
Start CATCH
Msg 51000, Level 16, State 1, Procedure spGetCommission, Line 20
A calculation error occurred

That might not be what you expected. The TRY works as expected, with the error in my query sending execution to the CATCH block, before the final print statement in the TRY block.

However I didn’t get the complete execution of the CATCH block, as the THROW throws an error and completes its execution. If I changed this to not re-throw the error, the final statement executes.

ALTER PROCEDURE spGetCommission

    BEGIN CATCH
        PRINT ‘Start CATCH';
        PRINT    ‘A calculation error occurred’
        PRINT ‘End CATCH';
    END CATCH;

PRINT ‘End of proc';

GO

In this case, I’ll get all my print statements.

Before TRY
Start TRY

(0 row(s) affected)
Start CATCH
A calculation error occurred
End CATCH
End of proc

A basic look at TRY..CATCH, and worth knowing about. I’d suggest you use this in future code, and even refactor code where you can to include this instead of looking at @@error to trap issues.

Are There That Many GUIDs?

This editorial was originally published on Oct 12, 2010. It is being re-run as Steve is away on vacation.

Do a lot of people actually use GUIDs as Primary Keys? I haven’t used them much, and I would have thought that more people chose identity keys. It seems that most of the demos and examples I see from bloggers and speakers are constantly using identities.

However an informal survey from Peter Bromberg showed that four times as many people actually had GUIDs as their primary keys. The blog actually says that GUIDs are not a good choice, but I’m not sure I agree with that. You can use sequential GUIDs, and you can avoid making them the clustered key, so I think they can work as well as anything.

There’s nothing inherently wrong with GUIDs, and they should be unique across all of your rows. There have been some reported cases of duplicates, but for most practical purposes, especially in database work, you ought to be able to count on a GUID as unique. They even have the nice capability of being generated by clients, removing the need for an extra round trip when a client needs to insert multiple rows.

I typically don’t use them because they’re long, hard to remember and type, and hard to view on the screen. I can’t easily compare rows in multiple tables, and it’s easier for me to work with integers.  I don’t recommend them, but if you are going to use them, be sure you understand the pros and cons, and use them appropriately.

 

Speaking at SQL Saturday #389 – Huntington Beach

I’ll be traveling to CA next month for SQL Saturday #389 – Huntington Beach as well as a Red Gate DLM training session run by Ike Ellis. I’m assisting Ike in running a Database Continuous Integration class. It’s a paid for event, but you’ll learn how to set up and run a CI process with your database.

Come.

CI is all the rage and companies are improving their development processes, building applications faster with it. We go into depth, using Red Gate tools, on how you can get your database development working in a CI environment, and integrate it closely with your application development work.

I don’t have details on my SQL Saturday session, but that should be coming soon. I will do a Red Gate presentation during lunch, so if you want to know how we can help you or have questions, come by at lunch.

The 2015 Car Update

I love cars. I don’t love commuting, but I enjoy driving. A little over a year ago, I left the Albuquerque SQL Saturday with a few fellow SQL Server pros at 8pm. I drove the 6+ hours home to arrive at 2:30am in time to get some sleep and then be up early for a family event.

It was hard, but I enjoyed the drive. I like the road trips.

I used to include some thoughts on cars and technology as an editorial piece every few months. Some people like it, some didn’t, some hated it, so I stopped. However I’ve been meaning to include a few notes here, so if you don’t like cars, stop reading.

The End of an Era

At the end of last year, I sold my 911. That was the dream car I wanted since I was 11 or 12 and finally bought. It was the second Porsche I owned (I had a 914) and it was a little sad to let it go.

However it stayed in the SQL Family, going to Merrill Aldrich. Here we were one morning in December before he drove away.

Photo Dec 19, 7 26 20 AM

This was the first time I hadn’t had a Porsche in over a decade, and I thought I might get another one soon. However I was looking for something that was larger and more comfortable to me.

Looking Forward

As I’ve aged, and since I’ve been driving my 2001 Suburban for a few years, I’ve wanted to have a bit more room in the car. With a bad knee (surgery last year) and again joints, I dislike getting down into cars these days. I don’t like driving our Prius and I try to rent SUVs when I’m traveling.

As a result, I’m looking for a mid-sized SUV. The Suburban has been great, but it has 180k miles on it, things are breaking, and I’d like to have a slightly smaller SUV with better gas mileage. I am looking for a truck that would easily get 3 of us skiing with gear. I’ll still have the Suburban for a bit of time, so if we need to take 5 people, we can. I considered (briefly) some of the AWD cars, but at the ranch we have snow drifts at times and the low clearance of cars becomes an issue.

I also want to be pampered. Things I think are important:

  • heated seats (must)
  • heated steering wheel (nice)
  • sunroof
  • smooth drive
  • leg room in the rear (I have kids starting to drive, who are large and sometimes put me in the back seat)

That’s about it. Navigation, cruise control, most of the other items are optional for me. I was looking for a 2012-ish used truck, maybe newer, depending on model. With this in mind, I started a search with these cars:

  • Chevy/GMC Tahoe/Yukon
  • Porsche Cayenne
  • VW Toureg
  • Audi Q7
  • Lexus RX350/450h
  • Hyundai Santa Fe
  • BMW X5
  • Volvo XC90
  • Lincoln MKX
  • Toyota Highlander
  • Acura MDX
  • Mazda CX-9

I drove quite a few of these, but not all. However they seemed to fall into a few classes and I could relate some to others. For example, the Cayenne, Q7 and Toureg all fall on the same frame. The Porsche is the smallest inside, and was underwhelming in terms of power. The Toureg was really nice, but it didn’t have any advantages over the X5, so I discarded it. I didn’t drive the Q7, but it’s a more expensive car, with no real advantages. To some extent, the Lincoln and Chevy fall into the same class. Expensive with no advantages.

The Highlander, CX-9, and Santa Fe don’t feel as polished or comfortable. Thinner seats, not as enjoyable, just not luxurious. The Lincoln was a bit underpowered, and also smaller.

I settled on the X5 and the RX350 pretty quickly. The Acura was essentially the RX350, but since there are fewer of them, why go that way? The Lexus is amazingly built, with so many older models with high mileage for sale. I even drove one with 325k miles and was tempted to get it for my son.

Ultimately the large sunroof and my wife’s affinity for Beemers have me choosing the X5 as the truck. It has rear seat heaters, which the kids like. A third row, which is really unusable for my family (everyone is 5′ 9" or taller) but it’s there in a pinch. The sunroof is huge, which I love. It’s comfortable and luxurious, and while it’s a touch heavy, the diesel has decent gas mileage.

All that’s left to do is find one. I’m on the search now, and we’ll see what I find in the next month.

Common Mistakes

At times I am rather dismayed by the quality of code I see written today. I’m not sure it’s worse than the poor code compiled early in my career, but there are so many more people writing code in our industry that it seems there is more and more poorly written code.

We suffer from the chef problem. As more companies look to become software companies, they need to hire more software people. To meet the staffing demand, more and more marginally skilled people will be chosen, and software quality goes down.

Part of what we do here is to try and educate the SQL Server professionals on how to become better at their jobs. That’s really the core mission that started SQLServerCentral and continues today thanks to the belief in that mission by Red Gate Software. As we look to do that, we want to bring to light the things that aren’t good ideas and can cause problems.

What common mistakes do you see T-SQL developers making?

The question this week is based on a post by the talented Doug Lane, who wrote about the top three mistakes T-SQL developers make. Doug has a good list, and I’d urge you to read it, along with some sage advice from Brad McGeHee. However I’m sure many of you see different common issues in your own work.

What things need to be fixed later? What code regularly causes performance issues? The more specific problems that you can share, along with their solutions, the more you might help another developer build better code in the future.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn.

Career Ratings

I wrote the other day about measuring your career, and I gave some general advice, but I wanted to give you some thoughts to take away about how you might rate your career.

These are some suggested areas, with a note or two in there. I’m not saying any of these, and certainly not all of these, should be important to you. Or that they are important to me. Some of these matter, some don’t.

Pick and choose what matters to you and think about the relative importance. These are presented in no particular order.

  • Salary – We need to pay the bills, but don’t let this overwhelm other areas. However also understand you can use this to get other benefits. If I make 10k more, but keep my budget the same, I could use that 10k to take unpaid time off, or pay for other things.
  • Vacation days – time off matters. However, taking time off matters more. If you can’t, or don’t, use it, then does this matter? As I get older, I think of this more. One thing I love about Red Gate is the sabbatical. I really enjoyed it, and it makes me think about just taking time off again in a few years. If salary is high enough, you can create your own time off, unpaid, of course.
  • Medical benefits - Again, as I get older, I think about this. The US is different than other places, so you might not care. However you might care about leave here with the birth of a child. Think about where you are.
  • Education benefits – This mattered when I was younger, but for most of my career, I haven’t had time to use these, so they essentially aren’t benefits. However I have had friend get degrees under these programs. I’d be sure, however, this matches what matters to you. Some companies only reimburse if it’s a field of study related to your job.
  • Retirement assistance/401k/matching/etc. – I hope you live a long time. Far, far too many of us don’t plan well for the future, when we may not work as much, we may make less, or just change our lives. What does your employer help you with here.
  • Commute - Time is the most valuable resource I have as I get older. Spending time in the car just to move to a job is something I dislike. What does this mean for you?
  • Equipment – Perhaps you want the perk of upgrading equipment regularly, or just having some control. Brent Ozar Unlimited offers this, does your employer? Mine does.
  • On-call – I once worked in a company where we had 20 operations people. We shared on call, one week of 20. My week I got over 40 calls when the sun was on the other side of the world. That really, really sucked. Think about what on-call does to your life. Some companies offer compensation time when you work more. Some don’t. Think about what you consider to be fair.
  • Side Projects – The famous Google 20% time, or the Red Gate Down Tools Week. Maybe you care.
  • Travel – Maybe you like to travel, maybe you don’t. Earlier in life I didn’t. I’m better at it now and enjoy some of the experiences. Get a firm grasp of how you feel before you commit to traveling, or get stuck in an office.
  • Kudos – Do you care if you get recognized for doing well? Most of the time I don’t, but if I never get a thank you or any acknowledgement of my efforts, it bothers me.
  • Training/conferences – We need to improve ourselves in technology, no matter what. Do you want help? Will you get support or even time to improve your skills?
  • Hours in the office – Does your company allow remote work? I’m not sure I need 100% remote (or my 98% now), but I’m not willing to work at 0% remote time. However I also have family commitments, so the core hours I need to be in a place matter.
  • Flexibility of Schedule – Required for me. Some of you might want a rigid schedule you can plan around. Choose what matters to you.
  • Promotion – What are your chances for advancement? How does the company review you and raise your salary or title? How do they move people into new positions?
  • Google benefits – Large companies have more overhead in working there. Parking, commutes, lots of stuff eat up time. However many large companies try to help you here. Google has published quite a few of their benefits, but I’ve worked in large companies that offered massages on site, oil changes, child care, gyms, cafeterias, and more. Ask, or suggest, things here. These concierge services can be helpful in life.
  • Charity – Some companies match donations, some give time off for volunteer efforts.

All of these items are things you should consider. They aren’t ranked here, but just listed. You might want these as benefits, or not want them. You might want more or less of them. It’s really up to you, but it’s important for you to determine how important each of these items is for your career.

The best advice I can give you here is to ask for what matters to you. Negotiate. You might not get what you want, but that shouldn’t discourage you, nor should you feel you’ve failed. Negotiation is give and take, with you and your employer compromising.

Whether you’re looking for a new position or want changes in your current arrangement, ask and discuss.

Microsoft and R

Microsoft purchased Revolution Analytics recently, a commercial company that works with the R programming language. This seems to be a decision to improve the analytics and analysis offerings from Microsoft. I’ve heard this will be incorporated into their Machine Learning offering, but I wouldn’t be surprised to see some basic R support in SQL Server at some point. If you’re not familiar with R, we’ve got a basic piece at SQLServerCentral on it.

Microsoft has made lots of acquisitions before, and while there’s some question of whether Microsoft can make R easy to use, I think they can. Years ago, Microsoft bought Proclarity, a company that had some BI type products. Did they succeed with that acquisition? I think they did. A number of the technologies likely made their way into PowerPivot and Power View even though the Proclarity products have somewhat disappeared, being incorporated into other products.

The R language and environment is complex, and I’m not sure how many database developers or DBAs want to become experts. However I do think that it could be possible to make the language easier, perhaps by building functions into SQL Server that help with the data analysis and computational features, and adding extensions in ADO.NET or other client libraries that might support easier rendering of visualizations.

We have lots of tools to help here already, with PowerPivot and other related Excel add-ins, and I suspect that any offerings here will overlap with those products, but allow Microsoft to woo an even wider audience of data professionals.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 1.9MB) podcast or subscribe to the feed at iTunes and LibSyn.

Follow

Get every new post delivered to your Inbox.

Join 5,130 other followers