Private Data

I think that as we evolve into a more digital world, we really need to modify and enhance the various legal systems around the world to cope with the challenges of digital information. The world changes when vast troves of information can be gathered, indexed, easily maintained, queried, and copied without anyone being aware, or few people understanding how any particular data is used. Data fundamentally is different in the digital world precisely because the costs and barriers to its movement are so low.

I’ve been concerned with this for a few reasons. One is that I like my personal privacy and would like to ensure that data collected about me and my family is something I have some say in. Or at least some understanding. However as a data professional, I also have concerns about the responsibilities and potentials liabilities of managing data in the future. There’s also the not-so-little concern about employers pressuring employees to deal with data in a way that might conflict with their personal ethics, or even the local laws.

I’m glad someone at Microsoft is taking a stance, asking the US and EU to recognize the privacy of digital data as a right we have as individuals and corporations. The request asks that governments treat digital data like they treat analog data, serving subpoenas and warrants to the owners of the data, not the custodians. While this isn’t what always happens in the real world, we certainly should have more protections for digital data than we have now.

I’d like to see governments amend their laws to also exclude IT workers from liability in working with data that they are custodians of. We often don’t make the decisions about what data to gather or how it should be managed and moved. We just implement the decisions, often under coercion from our employers. Liability should rest with those employers, not those the employers have hired.

I don’t have hope that things will change anytime soon, but I hold out hope they will at some point.

Steve Jones

The Voice of the DBA Podcast

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

Back to SQL Saturday Washington DC

I’ve been to the SQL Saturday in Washington, DC a few times and I’m looking forward to going back. It’s a good event and since I have family in the area, I can usually take a day or two of vacation and visit.

This year the event is on Dec 5, 2015, and it’s SQL Saturday #470. Wow, we’re closing on #500, which is amazing.

I’ve got two talks I’m giving, both of which I really like. I’ve revamped them both a little, incorporating a few new items in there and I hope everyone enjoys them.

  • Branding Yourself for a Dream Job
  • Get Testing with tSQLt

The room I’m in will be the room for the day, with both myself and Brent Ozar giving two sessions around Wayne Sheffield talking T-SQL window functions. I know there are other great sessions, but feel free to camp out in this room.

If you’re in the area, come join us for a free day of training. The event is in Chevy Chase at the Microsoft building.

Rogue Software Changes

Could a group of software developers make changes that fundamentally alter the way a software system should work without management being aware?

That’s the question being asked of VW right now. Most people are skeptical, but I ran across a piece that wants to lend credence to the idea that a few software engineers acted with few people being aware. They did this, not because they wanted to defraud everyone, but they wanted a solve a problem that they couldn’t do in other ways. They also didn’t see the alteration of test results as much of an issue because they thought the tests were too stringent.

I’m not sure I believe that’s what happened. Certainly there is some disagreement from various parties, but with my experience in software projects, management always wants to know how things are proceeding, with more and more questions whenever the applications don’t work as expected. When problems are solved, natural human curiosity leads more managers to ask for details, even when they don’t understand. In this case, I can’t imagine lots of VW management weren’t aware that software was being used to pass tests. Many people report to many others, and everyone would have wanted to know how VW solved this engineering problem.

The stakes for organizations will continue to rise in a global economy, and software will play increasing roles in many companies. Will we see more and more pressure to manipulate our world with software, even in criminal ways? I suspect so, and I sympathize with those that might face the loss of employment for not complying with the requirements they’re given.

Ultimately I think transparency of software is the best way to bring about better software that complies with regulations and rules. Transparency also ensures that copyrights aren’t violated (since violators code is available), and we can determine if security is being built into systems. Perhaps best of all, developers can all learn from each other, seeing exactly what works and doesn’t in each system.

I doubt we’ll get there, but transparency would be a nice place to be.

Steve Jones

The Voice of the DBA Podcast

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

Custom Schemas

One of the things I seem to see more and more is an expanded use of schemas in their development efforts. This isn’t something I had done much in the past as almost half of my career was with SQL Server 2000 or earlier, where we didn’t really have schemas and we were loathe to tie too many objects to owners. However the last decade has had schemas as a first class citizen in all SQL Server versions since 2005.

I’ve seen a number of software packages use schemas, but it seemed more as a way of separating out development efforts than any well thought out architecture. However there have been a few interesting uses of schemas as a way of duplicating tables for various uses. ETL and auditing are two of the more common uses, though there have been others.

I ran into an interesting approach recently that I found interesting. A group of developers had added a new schema to separate out their custom objects from all other objects in the database. They did this as the database was created by, and maintained by a third party application. They wanted to create new objects, but wanted them separate from the vendor’s objects, and used a new schema.

I thought this was a good idea and wondered who else be doing something similar. This Friday, I wanted to ask the question of the rest of you.

Do you have a custom or reporting schema as a way to separate objects from those that aren’t under your development control?

Let us know if you’ve created some interesting use of schemas. Or are there reasons you don’t use schemas? Have you never considered the advantages of using schemas to group  objects in their own section of the database?

I think schemas can be a powerful way of grouping objects, applying separate security, and  organizing your database in a way that makes it easier for developers, and report writers, to manage the complex objects that may clutter up your database.

Steve Jones

The Voice of the DBA Podcast

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

Thankful in November

I’m taking a short break from technical talk today to spend a few minutes thinking about life. My wife started the Thankfulness challenge in November, and while I didn’t participate, I’ve been thinking about life as I watch her posts.

This is a career and learning blog, and much of my life has been about growing my career. However I’ve also had many things outside of my career that are important to me and occupy much of my time. My wife, my kids, my health and fitness, and various hobbies keep me busy, not to mention occasionally filling in as the ranch hand for my wife. I even got some unexpected, but very appreciated thanks this week.

However there is something that I was amazed by this week. The outpouring of support for my call to make a #sqlwish come true was incredible. I was truly touched that so many people came together and donated money that allowed us to raise the entire amount in a day. Not even a day. This is one of those things that makes me think the SQL Server community, the #sqlfamily, is truly a special and amazing thing.

Life is Hard

I do struggle at times with the overall load. I struggle with stress at times. I have plenty of guilt when I’m away from home and something happens. I definitely find that I don’t always have the time I’d like for myself amid all the chaos in life.

However I’m very lucky. I have a wonderful wife I love dearly. I have three kids, well two kids and an adult child, that are all healthy and successful in life. I have a good job that I enjoy. My family is financially secure. We don’t really have any complaints, and I definitely appreciate all that I have. I truly do try to stop and think about the amazing times I’ve had. I use Timehop to revisit memories in my life. I stop as I travel and reflect on the good and bad I see, while considering how I can leave the world a little better than what I found.

I wrote about a fundraiser yesterday, trying to make a #sqlwish come true. I can’t imagine for a moment how my life would be different, or how much things would change if someone in my family were sick. I can’t really imagine the situation, and my heart goes out to Lucy and her family.

As great as it’s been to make the wish come true, I also would like you to reflect a moment on the good things in your life. Your family, your health, your beliefs, the people around you, I’m sure there is some good around you. Stop for a minute and give thanks.

I do hope that all of you reading this are well, and you are able to create a great career doing something you love. I will help where I can, and hopefully SQLServerCentral does that. However I know that life isn’t always easy or smooth. I hope that when you experience tough times that you have someone to help pull you through. If you really need an ear, the #sqlfamily is great. On Twitter, or in our wild and wacky thread, there are plenty willing to listen.

Git Pull in SQL Source Control

I wrote recently on Git Push in SQL Source Control (SOC), and now wanted to add the other side of the story, pull. This process is even simpler.

I’ll be working with a new database, one that has two client systems set up already with Git support. I know I haven’t tackled that part of SOC and Git, but I’ll do that in a new post.

In this case, I’ve got a repository on Github for my Redgate and SQL in the City demos. It’s a sample database, but I’ve been making multiple changes to the database across time, using Version Control as a way of continuity, and then being able to rollback changes or see history.

I’ve got a connection in a VM to a second development machine. When I launch my VM, and SSMS, I go to the second SOC tab, the one that is labled “Get Latest.” You can see the image below, and notice that there are two buttons in the upper left. There is the familiar “Apply changes to database,” which has been the way that we pull changes from TFS and SVN, but now there is a new “Pull from remote respository.” This is the one specific to Git.

2015-10-22 23_19_54-OneNote

In this case, I’ve refreshed my database previously in sync with the local Git repo. Therefore the button is grayed out. However, I don’t know if there are remote changes until I click the other button.

I do that and get the familiar four step dialog from SOC that checks for changes and then compares them to the local database. However what you don’t see is that a “git pull” is issued first, pulling changes from the remote repo. In my case, I had one change I’d made in London, actually, and hadn’t synced with my VM.

2015-10-22 23_20_15-salesdemo-2015-02-25-1422-export-i-fgg5cstb - VMware Workstation

This is the familiar SOC actions now where I see the changes and can use the “Apply changes to database” to compile this code in my local database.

In some sense, this is less exciting then push, but it’s nice to be able to do this in the client.

I’ll write some more on the workflow as I develop changes in the coming posts, which are going to be aimed at building a small database in Azure SQL database and moving changes from my local system to Git for tracking, and then to Azure for production use.

Make a #SQLWish Come True

A few weeks ago I was sitting in Farestart at the annual Friends of Redgate dinner when my eyes teared up. The waiter was explaining the mission of the organization, and I was proud that Redgate chooses to support them. However I was also touched that people choosing to help others when there is so much pain, strife, and suffering in the world. I’m glad the speech was short or I would have had a wet napkin.

I have been lucky in life. I’ve had career success, my family is healthy, and we really don’t have any problems in the world. The difficulties we have are really minor annoyances compared to what so many must deal with. I suspect many of you are in similar situations, working in technology and making a good living. However not that’s not the case for everyone.

We have a prominent member of the #sqlfamily whose child, Lucy, has been sick with a life threatening illness. Mark Broadbent has spent the last few years volunteering his time, helping with the user group and SQL Saturday in Cambridge, UK. He has volunteered to speak and write about SQL Server. I would venture to guess that some portion of you have been helped by Mark’s willingness to share his knowledge. At the same time Mark and his wife, Lorraine, have been dealing with Lucy’s struggles at home.

I’d like to make a wish come true for Lucy and her family. Lucy loves Disney, and I’d like to send the entire family to Walt Disney World in Florida. This is a gift, a gift for a person in our community, a gift that will bring some joy after all they’ve been through.

I’m asking you to join me. I’m asking you to donate funds to help us bring the Broadbents from their home in the UK to the US for a vacation, for a break from the strife in their lives. Lucy is doing well, but she has a long road ahead, and I’d like to bring some joy to a little one that didn’t deserve the struggles she’s faced. I’m asking you to show some love for a member of the #sqlfamily that could really use it.

I’ve set up a fundraiser on, and started off the donations with $200 of my own money. I rarely attach my name to donations, but in this case, I’m hoping to inspire a few of you to join me in trying to raise the $15,000 this will cost.

Whatever you can give is appreciated. $5, the cost of a coffee, or maybe the $10 cost of a quick lunch, or more if I’ve touched you. Maybe a few of you might even get your company wants to participate. Every bit helps, and we’d very much appreciate your generosity.

I’m asking you to pause in your life when you read this. Imagine your child were sick, or a niece or nephew, or even the child of a good friend. Imagine the worry about the child’s future. Imagine the stress from bills and the struggles as you try to somehow work while a child suffers at home or in the hospital.


Now reflect on your good fortune.


Join me in showing the world just how amazing the #sqlfamily can be and make Lucy’s #sqlwish come true.

View the fundraiser at Youcaring

UPDATE: Thank you very much for your support. We achieved our goal in one day and I’m am so proud of the #sqlfamily out there. On behalf of the Broadbents, thank you for your generosity and support.

Steve Jones

The Voice of the DBA Podcast

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


Webinar: Unit Testing with SQL Server and tSQLt

I ran into Sebastian Meine at the PASS Summit a few weeks ago and we were talking testing. Sebastian is the founder and developer of tSQLt, which I really like using. We’ve done some teaching together and I’ve delivered a number of sessions on tSQLt at various events, but we wanted to get more people interested in testing code.

I had a session at PASS, which was very well attended. 150+ people came, which was stunning to me. I was expecting to see 20, and afterwards Sebastian and I started talking about what else we could do.

We’ve decided to do a webinar, but one driven by you. We are looking for you to ask questions about code you’d like tested, or which you’re unsure of how to approach. Leave a comment here, or put your question in the webinar registration. The details are:

Unit Testing in SQL Server with tSQLt
Thurs, Nov 19, 2015 11:00 AM – 12:00 PM EDT

Join unit testing experts Steve Jones and Sebastian Meine for this exciting opportunity to learn about unit testing and the tSQLt framework to improve your T-SQL code quality and maintainability. If this day/time is not good for you, register anyway so you receive a link to the recording when it is available.

Updating Extended Properties

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

I wrote recently about adding extended properties. Updating them is very similar. There’s an analogous procedure called sp_updateextendedproperty that changes the value of properties.

The arguments are again, unintuitive, but the more I work with extended properties, the more comfortable I become. In this case, I have the same name and value, and then the level 0,1, 2 items with both a type and name.

I highly suggest, however, that you name your parameters, including the names in your calls so programmers running across the T-SQL aren’t depending on position for an understanding of the parameter.

If I look at the table from the previous post, I can update the value of my property with this code:

EXEC sys.sp_updateextendedproperty
  @name = 'PKException'
, @value = 0
, @level0type = 'schema'
, @level0name = 'dbo'
, @level1type = 'table'
, @level1name = 'SalesHeader_Staging' -- sysname

However my property needs to exist. If I call this procedure with the wrong property, I get an error.

2015-11-02 17_25_03-Cortana

This means that you need to be sure that the property exists before you update it. Good code would have the error handling somewhere.


After writing the previous post, this one took only about ten minutes to do the typing. I’d been working with extended properties, so I had the code and just needed to take the screenshot.


A few items from BOL

sp_updateextendedproperty –