Voice of the DBA

Tag: syndicated

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
Costumes at the PASS Summit

tl,dr; We’re raising money for Doctors without Borders. Donate and a few of us will dress up at the Summit. You don’t have to attend the Summit to have some fun here.

Last year a bunch of us agreed to get silly to raise money. We ended up getting over $13,000 for Doctors without Borders and it was fun. Quite a few people had laughs along at the Summit.

This year we’re doing it again, with Argenis Without Borders v2. Donate money and Argenis will be in a Ted suit at the Summit. I haven’t decided, but I’ll pick out something entertaining, and colorful.

It’s a great cause, and it if speaks to you, donate.

September 22, 2015
Simple Merges KDiff3 at SQL in the City

One of the hassles of working in a development team is that you will have conflicts between your code and another developer’s. That’s fine, and it’s a natural effect of allowing each person to work on parts of a database independently.

However resolving conflicts can be a pain. Fortunately there are some nice utilities that can help make the process easier.

If you want to see this live, come to SQL in the City 2015 in London or Seattle this October.

Here’s a quick example. I was simulating two developers, each with their own database, but linked to a common Git VCS repository. I went to commit a change and got this:

SQL Source Control detects a conflict before committing the stored procedure on the left. As you can see, it has more fields in it, while the version in my VCS has a TOP clause. I can keep my version or update mine with what’s in VCS.

Or, if you look to the right, you can see I have the option to “Merge” code. If I click Merge, then I’ll open KDiff3, which is installed on my system. SQL Source Control has detected this and given me the chance to resolve the issue.

I get a message that there are 3 conflicts, and none of them could be resolved. If there are places where the code can be merged automatically, KDiff3 will do that.

Once I acknowledge this, I see my code. This looks like what I saw in SQL Source Control. My code is on the left, and the version from VCS is on the right. Below is the merged code, which has some placeholders where the code conflicts.

If I right click the first conflict, I get some choices. In this case, I’ll take the line from the left, which has no TOP clause.

This gets inserted into my code and I can move on to the second conflict. In this case, I’ll take the version on the right, which has nothing.

For the last conflict, I’ll also take the missing fields from the right.

If I now save and close KDiff3, I’ll get the merged code in SSMS and SQL Source Control.

Not the “Keep mine” is selected. I’ll commit this, which will be the most up to date version in our VCS. Other developers can update their code with this version, and if they have conflicts, SQL Source Control and KDiff3 make them easy (ier) to resolve.

I’ll be showing you some basics of using a VCS with your database at SQL in the City 2015 this October. You can see this and more in London on October 16 or Seattle on October 26. If you can come to either event, register today and come pick up a few tips and tricks.

September 21, 2015