Skip to content

Half a Year

Today is a holiday for much of the US workforce. Tomorrow is Independence Day, though we have another year until the sequel of the movie by that name. This is the day the US started down the road of becoming a country, and if you’re a US citizen, i’d urge you to take a few minutes and look back at our declaration.

Half a year is gone in 2015. It’s amazing to think that this year is already halfway gone. At the beginning of 2015, we had little information on the next version of SQL Server, though we had a preview of some features, like Row-Level Security (RLS), in January. Now we have CTP 2.1 and a lot of features that are being added. We’ve added some jumpstart links for Learning SQL Server 2016, so if today is quiet, check out an area and learn a bit about what’s coming.

We continue to see data loss incidents on a regular basis. It’s almost becoming a piece of news that we are desensitized to hearing. I’m not sure if it’s good or bad, but I don’t think the financial penalties are high enough to get companies taking it more seriously, or certainly not serious enough to ensure developers change their ways they build applications. If you doubt the issues here, read Troy Hunt’s blog. He regularly finds companies not implementing security well.

All in all it’s been a good year for technology and it’s exciting to see many products changing, technologies maturing, and more options than ever for building software.  I hope you have a great holiday weekend in the US, and a pleasant one elsewhere.

Steve Jones

Defining Foreign Keys at Table Create Time

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

How many of you can define a foreign key when you create the table? Probably a few of you, but I bet most of you are like me and don’t necessarily know the syntax. I have often defined these later, which is fine. As long as they get defined.

However I knew I needed a specific key when I was creating a table and couldn’t remember the syntax, so I had to search and learn how. I used Google and saw a few links from MSDN, but those tend to be overly documentation heavy. One of the links was to SQL Authority, run by Pinal Dave. He does a great job of simplifying things (and he’s a friend), so I followed that link. I could see the syntax and tested it in minutes.

It’s easy to create a Primary Key in CREATE TABLE, and I wrote about that for one of my first SQLNewBlogger posts. The Foreign Key is similar, but not quite as simple.

Imagine that I have a parent table:

CREATE TABLE orders ( orderid INT IDENTITY(1, 1) CONSTRAINT Orders_PK PRIMARY KEY ( orderid ) , orderdate DATETIME , complete BIT ); GO

I now want to create a child table and link the orderid in the child to the parent. I can do it like this:

CREATE TABLE OrderLines ( orderlineid INT IDENTITY(1, 1) CONSTRAINT OrderLines_PK PRIMARY KEY ( orderlineid ) , orderid INT CONSTRAINT orderlines_order_fk FOREIGN KEY REFERENCES orders ( orderid ) , qty INT ); GO

Note that I define a constraint inline, just as I did for the parent. However I note this one is an FK and it "references" another table. In this case, I list the Orders table and put the columns in parenthesis.

Quick, easy, build your FKs inline when you know about them in advance.


While trying to remember how to create an FK, I ran a search and chose the reference below to start. A matter of seconds had me seeing the syntax and writing the code.

Putting this together was less than ten minutes.


Creating Primary Key and Foreign Key Constraints –

SSMS Maturity

I’ve used a lot of tools with SQL Server over the years. We had a variety of individual tools from Microsoft for SQL Server 4.2, including the isqlw query editor that I used for years. I wrestled with the Enterprise Manager MMC plugin and eventually moved away to use the Embarcadero suite to work with SQL Server before coming back to Microsoft’s SSMS in SQL Server 2005. I’ve had a love/hate relationship with that tool ever since, though the Redgate suite of extentions has certainly made life easier.

However Management Studio (SSMS) hasn’t really evolved in quite a few years. It seems that the enhancements and additions that have been made with each version have been minimal, sometimes barely working and rarely improved across versions. It’s been disappointing that relatively few resources have been expended on SSMS, despite the regular evolution of SQL Server every 2-3 years. And despite the fact that one of the big reasons SQL Server was touted over Oracle and other RDBMSs is that the tooling was better. 

I suspect some of the problems were the pressure to release the core parts of SQL Server first, and link SSMS to the server product, despite the fact that it really needs to support multiple versions and previous tooling should have been improved.

That’s changing a bit. As Tim Ford noted, SSMS now has it’s own release and upgrade path. The tool should not have it’s own, separate download and lightweight installer along with a separate release cadence from SQL Server. I suspect this will evolve more rapidly, probably closer to an Azure like schedule, with more regular patches and enhancements. At least that’s what I hope.

I was glad to see SSMS being made available for 2012 and 2014 versions as a separate download, and the change to a completely separate product that will likely become de-linked from SQL Server versions. I expect we’ll just have SSMS in the future, with some version that’s more like what we see in Chrome and Firefox. 

For now SSMS is still based on the 2010 Visual Studio shell, but the comments in this announcement seem to indicate it will move to the 2015 shell soon. Let’s hope that happens and the performance improves along with the maturity of the tool in the future.

Steve Jones

The Voice of the DBA Podcast

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

Training Computer Scientists

We work in an interesting industry. While I think you can certainly enter many other professions, electrician, plumber, artist, etc. without any formal schooling, programming allows you to earn a very good living with little physical effort. You can also work in any location, even from home. I think the opportunities and requirements open this field up to many more people than previous occupations in the past.

Of course, plenty of people still go to school to learn about computers, though I’m not sure I’d recommend that to people if you want to work in this business. I think you can teach yourself lots of the skills you need, and I think companies are really starting to realize that a college degree doesn’t correlate with a strong technology worker.

I was reminded of this when I saw a piece on a student that dropped out of a computer science curriculum. While I would hope that 3rd year CS students had written lots of code (I had by that point), I do agree that the exercises and requirements of many universities do not necessarily prepare many students for working as developers or DBAs. The most valuable thing you can learn in university is great communication skills, on which most people don’t spend enough effort concentrating.

There is a lot of potential for universities to really train students in very strong ways to solve complex algorithmic problems, but schools need to evolve more quickly. The world is advancing quickly and companies are searching for employees that can learn quickly and adapt to new situations. However for students to learn these types of skills, we also need instructors that are willing and able to adapt as quickly to teaching new subjects and techniques, rather than relying on a curriculum that was built twenty years ago.

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.

Updating Extended Properties on a Table

I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

I wrote recently about adding an extended property to a table. As part of what I was testing, I also needed to update properties, changing values back and forth. It’s fairly easy to do so, and I wanted to document this for my own reference.

The sp_updateexteendedproperty is analogous to the sp_addextendedproperty procedure. Here’s the code I used to change my property value on the table from the last post.

EXEC sp_updateextendedproperty 
@name = N'PKException', 
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'SalesTax3'

As you can see, I pass in the same parameters. The procedure then changes the parameter in the table. A quick check in SSMS will show you the values changed. In my case, I was changing the value from 0 to 1 to test a query.

The property does need to exist. If I execute this:

EXEC sp_updateextendedproperty 
@name = N'PKcheck', 
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'SalesTax3'

I get an error thrown from the database engine.

Msg 15217, Level 16, State 2, Procedure sp_updateextendedproperty, Line 112

Property cannot be updated or deleted. Property ‘PKcheck’ does not exist for ‘dbo.SalesTax3′.


This is a good way to handle this, as a TRY..CATCH can trap the error and do an insert instead of something else.


This was another side post from my testing of a solution. As I used this code to solve a problem, I kept a copy and made a few screenshots. This one was about 10 minutes in total.


sp_updateextendedproperty –

sys.extendedproperties –

The Importance of Our Work

My wife has often uttered a particular saying during stressful times in her career. She spent 20 years working in computer telephony and speech, often in sales, and would remind her colleagues that they “weren’t saving babies” as they worked with customers.

It was a reminder that most of our work isn’t, ultimately, that critical to the world. It matters for our businesses to success, for our careers to move forward, but most of the time we should keep some perspective on the value of the time we spend at work versus the rest of our lives.

I ran across that saying recently in a post from Scott Hanselman, and like him, I’ll apologize if your work is actually affecting life. If so, I agree with Mr. Hanselman, make sure you have unit tests. Lots of them.

However for most of us, we’re trying to improve commerce in some way. A few of us work for non-profits or the government and hopefully are trying to make the workings of that organization more efficient.  Some of us might be building systems using open data to improve society in some way. No matter what you do, remember that there is some life outside of technology.

Remember to spend time with your family, with friends, even taking care of yourself. Not only is it healthy to get away from work, it can help refresh you and give you a new perspective on the work you do. Work is important, but keep your efforts in perspective.

Steve Jones

The Voice of the DBA Podcast

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

The New DBA

What does DBA mean? It’s traditionally meant Database Administrator for technologists. However it’s also been a synonym for a database developer, or even anyone that has raised privileges on a database system. The term has been expanded to people designing databases, and usually includes the BI developers or analysts that work with database related technologies.

In short, it’s been a vague term that means different things to different people. However as I look forward, I think there’s another interpretation I have. The people functioning as DBAs, are the Data Bridges to Applications. We take the data and find ways to ensure it’s useful to applications. We make sure it’s available. We connect systems together. We do the dirty work of cleaning data. We secure the bridge.

We really are the person that needs to answer any question about the database. Throughout my career, when a company has decided to use DBAs, I have found the DBA is usually the catch-all person that really needs to be able to do whatever is asked of them that might tangentially relate to a database.

While there have always been DBAs that tightly controlled their systems and acted as impediments to change, I think those positions are disappearing fast. More and more companies, if they even bother to hire DBAs, want those individuals to ensure data is useful. Whether that’s administering, developing, configuring, or anything else related to data, whether the data is in an RDBMS, Access, Excel, the cloud, NoSQL, any anywhere else, we need to be able to provide some solution to gets data to an application.

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.

Congrats Summit 2015 Speakers

I'm Speaking Graphic_SmallThe PASS Summit 2015 Speaker list has been released and I wanted to congratulate everyone that was accepted. It’s a great honor and I’m proud to be on the list once again.

I also apologize to everyone that submitted and wasn’t accepted. I hadn’t planned on submitted, but part of my job is speaking to the community. I was asked to submit sessions, which I did. Of the three I submitted, one was accepted.

I saw a post from Mark Broadbent (@retracement) today that talks about his submission experience this year. He’s spoken at the Summit before the Rally, and numerous SQL Saturdays. He’s got experience and success as a speaker. It’s hard to call a single session selection a failure, and I hope Mark doesn’t feel that way.

It’s Not You

One of the things I’d like to say is that if you submitted a session and weren’t picked, it’s not necessarily you. This is certainly true for the Summit, but the same thing can apply at SQL Saturdays or other events.

I’m sure you doubt I know what I’m talking about, and that might be true, but I certainly understand how you feel. I’ve received two rejections this year from events that didn’t like my submissions. It’s tough, and it can be a blow to your self-confidence, but it’s not necessarily you.

Don’t get me wrong. It could be. Please solicit feedback from the organizers and friends about your submissions. There might be things that you are doing poorly, and if so, you should learn to do a better job at writing abstracts, and a better job of speaking. I regularly ask people what they thought of my talks, and I’m happy to go watch and give feedback to others as well.

I’m sure some of you have heard of, or seen spoofs, of medical school grading where professors start a test letting students know that there will be x As, y Bs, z Cs, i Ds, and j Fs. In other words, a forced ranking where you must compete in order to succeed. That’s the PASS Summit submissions, but in a more complex scenario.

You might submit a great abstract, on an outstanding topic. Maybe you’re a SQL Server memory guru and you want to put in a 400 level internals talk on memory pressure. Great. But if Adam Machanic submits on a similar topic, I bet you don’t get picked.

In the various areas, sessions are ranked, and no matter how good a job you do, it’s possible you’ll get beat out by someone else. That’s not to say it’s hopeless. I believe that some slots are reserved for first time speakers (to the Summit, not in general), but it does mean that your abstract needs to be good AND not have great competition in your topic area.

Submitting to the Summit and getting picked is a bit of a lottery. It helps to speak at multiple other events (user groups, SQL Saturdays, etc), and get some reputation, but it’s also important to pick topics that will be both interesting and don’t have lots of competition. It also becomes important to write a great abstract, and if you need help, read Adam Machanic’s post on this topic.

Above all, don’t get discouraged. Get help in putting your submission together. Ask friends, ask colleagues, ask your family and read what others have done. Pick a topic area you’re comfortable with and try to find a niche that minimizes competition. Above all, make sure you get feedback from PASS and try to use that to tailor the next submission.

You can do it. We see more and more new speakers every year, and you have a better chance if you think about how to best present yourself to the world.

The First SQL Saturday in South Dakota

There have been so many SQL Saturday events in many places, and in most of the US states, but there are a few that have never had an event. One more state gets its first event this July, with SQL Saturday #427 in Sioux Falls, South Dakota.

I’m proud to be speaking at the event, and I just finished booking my travel. I’ve never been to South Dakota, so I’m looking forward to wandering around a bit on Friday before we kick off the first SD SQL Saturday on July 18, 2015.

I’m going to be talking about Continuous Integration in the afternoon, but there are some other great sessions. Learn about baselines, Service Broker, Auditing, SSAS, Stress testing, Reporting, and more.

If you’re anywhere nearby, make some plans now to get to Sioux Falls in July for some SQL Server education.


In April, Ed Leighton-Dick set up a challenge to motivate new bloggers to begin writing about their knowledge of SQL Server. This is a passion of mine, and I believe this is one of the ways you can truly help interest potential employers in your work. It’s allows them to perform some due diligence, and I’ve had success in my career with employers being impressed with the knowledge I’ve shared through blogging.

Ed made note of a new Twitter hashtag, #SQLNewBlogger, and there were a few people that participated in the challenge. However, there weren’t enough. I was really hoping to see more people join in, start blogging, and showcasing their knowledge. Therefore, I’m making an appeal here.

Let’s extend the SQLNewBlogger. I’d like to change the rules a bit and see if I can motivate a few of you to start building your communication skills and showcasing your knowledge across the next year. Just like a class at a gym can motivate you to try harder and longer than you might otherwise, I’m hoping the #SQLNewBlogger tag can do that.

If you’re thinking of asking for a raise, or looking for a new job next year, in 2016, do these things now:

  1. Set yourself a weekly reminder, and appointment, at a time when you can spare 15-30 minutes. Use this time to work on communicating, through writing.
  2. Get yourself a note taking system. You can use OneNote, Evernote, Remember the Milk, or Notepad. As you write queries, set up a system, or solve a problem, make a note of what you did. Pull out that list each week when the reminder you set in #1. Completely ignore anything anyone else has written on your subject and write your own thoughts.
  3. Make it a goal to get a 1-2 page post done each month. I’d like to think that in 1-2 weeks you can get one done, but find your pace.
  4. Use the #SQLNewBlogger tag to ask for help reviewing your post (you want good quality) and then finish one post a month. If you’d rather do this privately, have a friend review your work.
  5. You can get a free blog at WordPress, TypePad, Blogger, etc. You can choose to write and safe drafts forever, or publish posts for potential recruiters and employers to learn about your skills.
  6. Let us know what you wrote about. Either a link if you’ve published it for us to see, or a title if you keep the content private.

That’s it. If it sounds like a lot, I’d remind you this is asking for 1-2 hours a month. 12-24 a week, to showcase your knowledge, grow your career, and maybe most importantly, improve your ability to write a sentence. We communicate constantly in this business, despite all the code we write, so learn to do it better.

Next year, whether you publish publicly or not, you’ll have 12 things that show you have some knowledge about SQL Server. I’ve got a few references on setting up a blogchoosing topics, and writing better.

I’d also like my fellow bloggers to encourage and cheer on the people making an effort to share some knowledge and grow their careers.

Steve Jones

The Voice of the DBA Podcast

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


Get every new post delivered to your Inbox.

Join 5,308 other followers