Tag: T-SQL

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()

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

I saw a post recently from someone that was having trouble with Chinese characters being inserted into a SQL Server table. I’m not sure if they were using an application or just SSMS, but they inserted this:

insert into mytable select ‘<IDC>亮块(DS3或DS4)-清理/维修显像滚筒</IDC>’

And they got this in the table:

<IDC>??(DS3?DS4)-??/??????</IDC>

That’s a problem that I can see, even if I can’t read Chinese.

Someone else posted a note that when you insert, you need to let SQL Server know your string is Unicode. That means prefixing your string with an N’.

I looked in Books Online, and found this note under the nvarchar section:

“Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.”

I think this means that the N’ isn’t necessarily required, but it depends on the default code page of your database. For most of us, if we don’t include it, I believe our data gets converted to UTF-16, which might not be what we want.

SQLNewBlogger

I ran across the post and spent 5 minutes researching things and looking in BOL. This really took about 5 more minutes to write.

Reference

NVarchar –  https://msdn.microsoft.com/en-us/library/ms186939.aspx

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

I was looking to do some testing recently, but needed a v12 SQL Azure database. The test system that I have in Azure is v11, and I needed an upgrade. I clicked the upgrade button, and it was really slow to change from v11 to v12.  It’s easier now because I think all new databases you create are v12.

I was impatient, and ended up creating a new one. I decided to do a quick walkthrough of the process.

Getting Started

I connected to Azure and went into the new (Preview) Portal. I couldn’t find anything in the old portal, so keep tat in mind. Since “old” and “new” are very relative terms in Azure, and I hate them, I’ve included lots of screenshots. Here’s the portal I used:

My first step was to go to the “Create” item and select Data + Storage. As you can see, there are all sorts of options. For this post, let’s create a new database.

Selecting “New SQL Database” gives me this set of options. The name is the big item here.

Obviously I wasn’t very creative with the name:

Next I decided to make this one blank. I wanted to do some testing, so I selected that. It’s nice to be able to create a database from a backup, especially if you are recovering from a “Whoops” mistake.

With that selected, I had this:

Now on to the pricing tier. You can go crazy here.

I went down to a Basic level, since that’s what I really need. A basic, small database for testing. It’s $5/month, but I have credits from my MSDN subscription, so to date, I haven’t paid for any of my testing on the low tier systems.

At each stage, I can see what I’ve selected. This is a good way to get started, but if you needed to do these are any scale or repeatability, learn to do it through PoSh.

My next step is to decide where this database lives. I need a server, which is strange to me. I want a database as a platform, but I think this is legacy for connection purposes, so I’ll make a new one.

I decided to make up some generic, boring name. I wasn’t feeling very creative today. I entered a password, but like with any password, I generated this in PasswordSafe first, then entered it here.

<securityrant> Please use a password manager, use strong passwords, and use separate ones for services. Using the same one for all your Azure stuff and other accounts is a bad idea. </securityrant>

Once I complete this, it’s listed under my SQL Database as the location for my data.

Change your collation if you like, but since I’m a unilingual American, this is what I choose.

I also need a resource group. I don’t have any, so I’ll need a new one.

Again, not creative. If you are doing this for something more than fooling around, spend 2 minutes thinking of some way you’ll classify stuff and use a name that makes sense.

I think we’re ready to go. Click “Create”.

Once it’s done, you’ll get a notification in the main portal. I do like that the notifications come up at the bottom, but they’re also annoying when they stack up. I’ll sometimes close them because I’m doing something else and not pay attention. However, that’s a “me” problem.

I allowed this to create a pin for me, and I can easily see my database here. If you have lots of stuff, you won’t want pins for most of them.

If I select the database, I can see details.

I can select “Settings” at the top and get more options. One of which is what I need. Note that the version is v12.

The Properties item gives me the ability to change a few things.

The Firewall allows me to limit access to specific hosts.

Auditing has the ability to let you audit by events, success of failure

If I want to audit things, I need a place where I can store the audit data. The cloud charges by everything, so be aware.

The item I really care about is the “Latest SQL database update” item. When I pick that I can see the items that will be enabled.

If I pick Users and tags, I can specify accounts and labels, but that’s not really important for a test system. Really it’s me using it, so I’ll leave those alone.

It’s really easy to set up, and worth practicing if you want some cloud experience. You can connect from SSMS and work with the system, which is really SQL Server, but if you have other stuff in the cloud, or an app, you can connect and see what is possible.

Be aware, however, that you get charged for this stuff. Use your MSN subscription and keep an eye on usage. It’s pretty cheap, but no one wants an unexpected bill. Be especially careful about VMs. Those can really rack up charges.

 

SQLNewBlogger

This one took awhile. After I completed the process, I immediately deleted the database and started to take screen shots as I went through the steps. The steps don’t take too long, but refreshes on the Portal are sometimes slow.

This actually sat in my drafts folder as a single sentence (creating an Azure db) and a bunch of pictures. It took me about 20 minutes to load the pictures into Live Writer and then work my way through them, adding notes. I also created a new database as I was writing this to walk through the same steps again (and then deleted it).

References

None I used. I’ve done this before and didn’t bother to look around. The Portal does a good job of leading you along.