Voice of the DBA

Author: way0utwest

  • Dropping Masking from a Column–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    This is a quick one. As I experimented with Dynamic Data Masking for the Stairway to Dynamic Data Masking, and writing my Using SQL Compare with Dynamic Data Masking, I needed to remove masking from a column. I didn’t want to rebuild tables, and hoped there was an easy way to ALTER a column.

    There is.

    Here’s my table. I added masking to this table, but this is the scripted DDL.

    CREATE TABLE [dbo].[OrderHeader](
    [OrderID] [INT] IDENTITY(1,1) NOT NULL,
    [Orderdate] [DATETIME2](3) NULL,
    [CustomerID] [INT] NULL,
    [OrderTotal] [NUMERIC](12, 4) MASKED WITH (FUNCTION = 'default()') NULL,
    [OrderComplete] [TINYINT] NULL,
    [SalesPersonID] [INT] NULL,
PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

    Note, I have masking enabled on the OrderTotal column. If I want to turn this off, I merely run this:

    ALTER TABLE dbo.OrderHeader
ALTER COLUMN OrderTotal ADD MASKED WITH (FUNCTION='default()');

    This removes masking, and if I script the table again, I get this:

    CREATE TABLE [dbo].[OrderHeader](
    [OrderID] [INT] IDENTITY(1,1) NOT NULL,
    [Orderdate] [DATETIME2](3) NULL,
    [CustomerID] [INT] NULL,
    [OrderTotal] [NUMERIC](12, 4) MASKED WITH (FUNCTION = 'default()') NULL,
    [OrderComplete] [TINYINT] NULL,
    [SalesPersonID] [INT] NULL,
PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

    Easy to turn off if you need to. No application changes, and no security changes.

    SQLNewBlogger

    A quick, handy piece. Give a why you need this, and show how to do it. Any of you could write this in 5 minutes.

  • Defensive Programming

    At SQLServerCentral, we have a book on Defensive Programming in T-SQL. However not a lot of people have read it, or at least they haven’t downloaded it. That’s disappointing, as I think this is an area many of could improve our programming skills. Many of the techniques are simple things, or items to be aware of when writing code in order to make it more robust and reliable.

    I ran across a piece on the Enterprise Craftsmanship blog als covering this topic. That post is geared more towards C# and other OOP languages, but some of the concepts are valid for any software system. Certainly the need to guard against invalid values, especially NULLs is important in much code, and especially important in SQL code. I hope all of you understand the impact of NULLs on aggregates as well as other T-SQL commands, operators, and expressions. If you have any doubt, be sure you read about the impact of NULLs in BOL and perhaps write tests that inject NULLs into your code.

    There are places in our T-SQL constructs where we might want to develop code defensively. Checking parameters, using defaults, and watching out for ambiguous updates, and more. However, the place where I really think we want to be defensive is when we deploy changes to our production systems.

    I’ve written about idempotent code before, and all of you that develop software should be familiar with the concept. Perhaps more importantly, you should practice writing code that is idempotent to ensure that you really can write code this way. While not all deployment code needs to be written this way, there are plenty of cases where we want to include some sort of guard clause to prevent unexpected changes during our deployment.

    I’d encourage you to take a bit of time and examine the idea of programming defensively, practicing some of the techniques and making an evaluation of where this might improve your own code base. Who knows, maybe you can prevent some future bugs by learning a bit more today.

    Steve Jones

  • Dreaming of SQL Saturday

    I’m truly stunned at the responses I received to my post asking how far people have traveled for a SQL Saturday. I posted here, and at SQLServerCentral. In addition, I got plenty of emails from people, detailing their travels.

    Early warning, this gets a little long.

    I am truly stunned. In fact, when I read some of the notes and comments, I got tears in my eyes.

    Tears of joy. I’m touched by the way people feel about SQL Saturday. A few random things that stood out:

    “This was my first time attending and I would gladly attend again.  The distance is about 3000 km.  I elected to attend the SQL Saturday in Orlando and to include it as part of my vacation plans. ” 

    I asked, and this person moved their vacation plans to get to the event. That’s amazing. Someone changed vacation plans (slightly) to attend a SQL Saturday.

    (paraphrased) “As an attendee and volunteer I’’ve been to 14 SQL Saturdays, from 7miles to 266miles from my home” – This person moved, and has been to multiple events, each in the state of residence.

    Another person had gone to 2 events, both over 100mi. away. their quote: “Why? bc I love hearing the discussions and learning. Also since they are on weekends I make mini vacations out of them and do other fun activities. That may be something you may find others do as well. I have asked my company for Friday off since I am spending all my own time and money which makes it a 3 day weekend.”

    This one is sad to me: “We (3 of us) traveled 160 km twice. Two of our colleagues, from our remote site, traveled 300 km once. It is just too much, so we stopped attending.” I understand, especially in less populous areas that it can be hard to get to events like this. I have a dream, see (way) below

    This is great: “Pursuant to your article on  SQL Saturday, the controlling issue is “information and not “distance” – if the information is extremely valuable,  I would travel one thousand miles!” 

    While it’s quite exciting, I’d hope that no one would have to travel 1000 miles.

    This next response stuns me. I’ll describe it because it’s a mix of things. A person has lived in the middle of the country. There is a SQL Saturday in their city. In fact, they’ve been to that event 4 times. In total, this person has been to 19 SQL Saturdays with distances of: [21, 21, 450, 516, 251, 21, 511, 516, 319, 21, 450, 319, 378, 203, 6, 595 516, 319, 378]. Of those, they were an attendee at 16 of those events.

    Now, you might say, “see, people will go over 400 miles for an event.” Absolutely, and I don’t argue that. However, what I’d do is flip this around.

    How many events would this person attend if there were more within 100mi of their house? How many more people would get training if there were more SQL Saturday events, more often, in more locations? 

    Dreaming of 500

    I’d like to see 500 events a year in the US. I’d like to see three or four events in many cities a year. I’d like to see a thousand more speakers, just average DBAs and developers that are willing to share some knowledge. I want more, many more, many more slim, easy events. Have big ones if you like, but let’s get more training out there.

    I want more chances to speak, and learn, with the challenge of picking and choosing. I want to think of what we can do, not what holds us back.

    There’s a great scene in Apollo 13, which I bet a few of you remember. The engineers need to get the power usage to 12A from 60A. They have to find a  way to get the spacecraft back to Earth from the projected point in between the Earth and Moon where the astronauts might die.

    They are challenged to do so, with a famous quote that you may remember.

    I challenge you. I want you to think about how we make that leap to 500 events a year. I want every organizer to think about how they can create more events, not just their one big event a year. I want the events to fit a budget, not the other way around. Find a way to 500 events a year.

    I want every speaker to think about how you can inspire, prod, cajole, convince, and support another person or two in your area to speak. I challenge you to staff 500 events a year.

    I want everyone at PASS, from the Board of Directors to the staff at HQ to the volunteers that help PASS to think about growing SQL Saturdays, and finding ways to provide support. Maybe more support for some events and people than others, but take the challenge. Don’t tell why you can’t, dream of why you can. I promise you, more events will create more SQL Saturdays, and more changes to make larger, paid for events work for PASS.

    I want every attendee to share SQL Saturdays with your friends, and help grow to more events in your area, or a new area closer to you. Fifty people can make an event, if you just give back a little.

    Failure is not an option.

    It shouldn’t be. Let’s dream big. After all, who would have thought we’d get this far? Andy and I certainly used to wonder if we could actually have 12  year.

    500 events in the US in a year is crazy. It’s silly. Maybe it’s not possible, but I’d like to think we can get to 500 if you challenge yourself and just dream a little.

  • Moving Fast at Redgate

    One of the pushes at Redgate Software over the last year or so is to move faster. We aim to develop software quicker and get the changes and improvements into the hands of our customers quicker. A new feature doesn’t help anyone if it sits inside of a VCS, undeployed, for months. Customers and clients need to see the software changes.

    There are various ways to achieve this. The SQL Prompt team manages themselves, and being a small group, they have been successful at not only writing code quickly, but releasing often. In fact, they’ve released code to customers over 70 times in the last year. As a result, a few other development groups are looking to work in a similar, lightweight, rapid development manner.

    However, that’s not an appropriate process for all developers. In fact, in most companies I’ve worked in, there is always someone that needs to manage and report on milestones and efforts to build software with some sort of plan. In addition, most products have a wider scope than something like SQL Prompt, which means more developers, more coordination required, and everyone must deal with more friction throughout the process.

    One of our project managers wrote an interesting piece on how he worked to get the SQL Monitor team, one of our larger pieces of software, to meet their goals, and also move to a more rapid development and deployment process. It’s a look at the changes made in process and the adaptations to reality that occurred last year. I found it fascinating to read since I’d see the team dramatically improve the product in a fairly short period of time.

    As with most of the stories about successful software development teams, buy in and support from management, especially project management is important. Without your leadership wanting and being willing to change, little will. Developers have to believe as well, but I suspect that if management truly supports improvement and is willing to change the way they work with developers, the people that write code are usually willing to go along for the ride

    Steve Jones

    The Voice of the DBA Podcast

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