Voice of the DBA

Category: Blog

Exercism

I’ve been looking to work on my programming skills a bit and try some new languages. I’d like to grow my career in a few different ways, as well as investigate where some of the new languages and platforms might be useful for data analysis. As I’ve talked to some developers and been looking around, someone recommended Exercism.io.

Exercism.io is a site that allows you to practice coding exercises and get feedback from others. When you visit the site, it’s an interesting look, and invites you to log in through GitHub. If you’re not a member of GitHub, and you’re a developer, you probably are making some sort of mistake.

Once you log in, you really need to download the command line client and execute it. Once you do, you can configure it to connect and download exercises. Each of these is placed in a folder, as shown:

For each of these languages, you get an exercise that you need to complete in that language. I’ve been playing with Python, and I had a first exercise of hello-world. In the python folder, were my exercises (you can see I’ve moved on).

The hello-world folder had a read me and a test file (it’s since gotten my program in there). The Readme has instructions and the test file is a set of tests that can be executed to check your program.

When you pass the tests, you submit your solution from the command line client. The submissions appear on the website and people can comment on your code. I am in no way commenting on any else’s Python code at this point, but I did get a comment on my leap year calculation.

I looked over the comment and then changed some code. I had to futz with the command line to get this to resubmit, but as you can see, I ended up getting a second iteration in there. No comments on that one, but we’ll see.

It’s an interesting idea to share code and get comments. I think mostly this is a way to formally practice some exercises and get comments from experienced users, but the volume means that potentially you won’t get comments on your solutions. I know many of the SQL users may, or may not, comment on solutions.

I think this is interesting, and I’m tempted to try to do something like this for SQLServerCentral. The hard part of putting together enough questions that others can practice in an organized fashion.

September 28, 2015
SQL Saturday as an Attendee

Last week I did something that many people have already done. However this was my first experience, and I really enjoyed it. We’ll see if I get to do it again.

I went to SQL Saturday #441 – Denver as an attendee. I hadn’t submitted to speak, but I spent half my day over at the event, watching some sessions, networking, and enjoying some time with my fellow data professionals.

I’ve never done that before. I’ve attended many SQL Saturdays, 49 by my count, but always as a speaker. I’ll sit in sessions, but I always have a responsibility and my laptop.

Saturday was a bit more relaxing for me. I could attend sessions on topics and focus more, being unworried about keeping my talk in my head, worried about setup or practicing anything during the day. I could focus all my time on someone else’s presentation, which was nice.

I don’t know how many more SQL Saturdays I’ll get to attend like this. Certainly the ones in the Denver area are events I like to support by volunteering to present on a topic. Denver has grown quite a bit and we have many speakers from out of town that submit, so I may avoid submitting here in the future and continuing to attend, or volunteer in some other fashion.

It was a fun experience, and I’m glad I got to go. It was worth my time, as I learned a few things that I am looking forward to trying out over the next few weeks.

September 25, 2015
Use SCOPE_IDENTITY()–SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran across a question on Facebook, of all places, the other day. Someone had asked a friend how to return a value from a procedure and assign it to a variable. My friend answered, but in the discussion, I noticed the poster was looking to return @@IDENTITY to the calling procedure as the value of the row that was just inserted.

Don’t do that. At least not without understanding the potential issues.

It’s been years since I’ve seen @@IDENTITY in use, and for a number of years before that, this was an easy “weed out” question in interviews.

If you look at the documentation for @@IDENTITY, the documentation notes that SCOPE_IDENTITY() and @@IDENTITY both return the last identity value inserted in the table, but @@IDENTITY is not limited in scope to the current session. This means that when concurrent inserts occur, you could receive the identity value of another session. Depending on how you use this value, that may or may not be an issue.

How does this work? Let’s create a simple table with an identity. I also create a logging table and a trigger that will add a message to my logging table when I add a row to the first table.
```
CREATE TABLE newtable
    (
      id INT IDENTITY(1 ,1)
    , mychar VARCHAR(20)
    );
GO
CREATE TABLE Logger
 (logid INT IDENTITY(56,1)
 , logdate DATETIME
 , msg VARCHAR(2000)
 );
GO
CREATE TRIGGER newtable_logger ON dbo.newtable FOR INSERT
as
  INSERT INTO logger VALUES (GETDATE(), 'New value inserted into newtable.')
RETURN
;
go
```
If I run this, what do I expect to be returned?
```
INSERT INTO dbo.newtable
        ( mychar )
VALUES  ( 'First row'  -- mychar - varchar(20)
          )

SELECT @@IDENTITY
```
However I get this. A 56 in my result set for @@identity.

Why?

The reason is that the last identity value was 56, from the logging table. The order of operations is
- insert value into newtable
- @@identity set to 1
- trigger fires
- insert into logger
- @@identity set to 56
That’s often not what we want when capturing an identity value. What’s worse, this behavior can exist, but not manifest itself until someone changes a trigger later.

If I change this SCOPE_IDENTITY(), I get a different result.

This is because the SCOPE_IDENTITY() function takes the scope into account and doesn’t get reset by the trigger code.

SQLNewBlogger

This took some time to write. Mostly because I had to setup the demo, test things, and then get the explanation straight in my head. It took me 15-20 minutes, including lookup time in BOL, but if you are new to writing, this might take a bit longer. You’d also want someone to review your explanation since this can be tricky to explain.

Reference
- @@IDENTITY
- SCOPE_IDENTITY()
September 24, 2015
Trying Spoon

I ran into Kenji Obata of Spoon earlier this year and we ended up chatting about their technology. If you’ve never heard of them, they use container technology to allow applications to run on a Windows host, but separated from each other. The concept is similar to Docker, but Spoon is more client focused. They’ve expanded their offering to Turbo, which is a more client focused offering that I think some of you might be interested in.

I’ll talk about Spoon as the containers, but Turbo is the same technology and idea here.

As an example, you can run Spoon containers on your host machine that allow you to have IE7, IE8, and IE9 all at the same time. At the same time, connecting to the same website so you can see how things render. In fact, their Browser Sandbox has all sorts of choices, all of which can be completely contained and running together.

There’s a lot more you can do, but I wanted to get you started with a quick post on how you can use this with SQL Server.

A SQL Server SSMS Container

Browse over to Turbo.net and you’ll see a text box asking you what you might want to run. Type in SQL Server, and you’ll get some choices.

I’ve got SQL Server 2014 on my laptop, but not SQL Server 2012. Imagine I want to see how SSMS 2012 might compare to SSMS 2014.

I run the SSMS 2012 image. Turbo.net wants me to log in, and you can quickly create an account. They do charge for the service, but you can try it out for free. An account gives you the capability to save your images and restart them later with the saved settings and configuration for the future. You do need to download a small runtime if you haven’t done that in the past.

I’ll see a small pane pop up on the website as the image downloads. This doesn’t require me to acknowledge anything, no UAC, no admin privileges.

In a few minutes, I’ll get SSMS popping up. I can connect to my local 2014 instance and run the SSMS 2012 alongside the 2016CTP I have installed.

Many of you will say, Steve, you can run two instances of SSMS now. That’s true, but I have to install them both on my machine. This doesn’t require an install. It’s inside a container, that hasn’t affected my machine, other than to put a container file on my machine. I can see these from the command line:

My SSMS container is running. If I close it, then the container will stop running.

If I then restart it from the command line ( I could use the web), my local container file will restart.

I could achieve some separation here from my local machine. With a subscription, I can save my image file, altering it to include plugins (like SQL Prompt), licenses, settings for my CMS or other servers, etc. When I restart, then I have a separate sandbox running SSMS.

Amazing Possibilities

I use Spoon/Turbo right now to run multiple instances of Chrome. I have 4 GMail accounts and I don’t want to sign in/out of each one, so I have each container configured with a different account, and I can work with all of my various accounts at the same time.

I could see this providing quick Express access to a private database in a container. I could have a container that connects to my system with privileged accounts, providing some separation from my host OS in case of a malware attack.

There’s a container with Chocolatey in there as well, so you can easily install whatever inside a container and save it off for later use.

I’m sure there are other possibilities, and I plan to keep experimenting to see what else I can do with the Spoon/turbo containers that will give me flexibility across machines, require less resources than VMs, and provide the separation from host machine dependencies.

September 23, 2015