Voice of the DBA

Author: way0utwest

Returning Results from an Insert – OUTPUT clause
I needed to return an identity value recently from an insert for use in another piece of code. For a client front end, you can easily encapsulate your insert in a stored procedure and then SELECT scope_identity() to get the last identity. However there’s an easier way: the output clause.

The OUTPUT clause is a clause that goes in your INSERT statement and allows you access to the INSERTED table, just like a trigger (also the DELETED table.

A short example below, where data is being added by the server in the state of an identity and a default. I am returning them with the OUTPUT clause.
```
CREATE TABLE mytesttable
( MyID INT IDENTITY , mychar VARCHAR(20) , mydate DATE DEFAULT GETDATE() ) GO DECLARE @mytable TABLE ( i INT, d DATE); INSERT dbo.mytesttable (mychar) OUTPUT INSERTED.myid, INSERTED.mydate INTO @mytable
VALUES ( 'First Row') SELECT i, d FROM @mytable
```
There are any number of ways to use this data, especially in terms of logging or inserts into another table. It should be cleaner code, but it doesn’t mean that you should be running inserts from the client without stored procedures, or at least without explicit parameters. Make sure you still use those.
January 30, 2012
Should we outsource identity management

My Database Weekly editorial for Jan 30, 2012

One of the first things many people build when they’re developing an application these days on the web is a login/identity system for their users. This allows them to identify people, customize the features and functions, and separate out the different classes of users from each other. However many developers don’t really understand good, secure design, much less encryption, and end up “storing passwords hair kari in unsecured databases”, a quote from this piece on password security.

I’d hope that most developers know that passwords should be stored in a digest (one-way hash) form, but that’s like assuming most developers understand the issues with SQL Injection. It’s not true, and not necessarily going to be true until, well, it’s not likely to ever be true. Even if we had minimum standards, there are plenty of developers that would ignore them and plow forward with the arrogance that their custom method is better implemented, and more secure, than any standard.

Passwords and password security are hard. I’d hope that most people would be looking to move to passphrases instead of passwords, but I don’t see the recommendations being widely disseminated in the world. I recently rebuilt a computer, requiring my kids to implement new credentials, and my recommendation was a simple phrase they can remember with a number. I was hoping it would serve the dual purpose of instilling good security habits as well as improving their typing skills. We’ll see if it helps.

With large rainbow tables, creative social engineering, and poor application security, it’s even more important now that we use passphrases, and even develop better identity frameworks for applications. Personally I liked the Passport system Microsoft had, and I like the OAuth system even better. Perhaps we can get more of these frameworks implemented in reference applications and frameworks, as a default way of managing identity systems.

That’s the easy part; convincing developers they can’t manage identity better is probably the hard part and I would love to see some good ideas for that.

Steve Jones

January 30, 2012
Morale Data
Would you use this service to make work more happy?

Would you use this site to enter your morale at work? It’s an idea by a new company that should enable employees to enter information about how they feel at work, how their job is going, and other meta data that describes their attitude for the day. Managers get access to an aggregated look at their employees, as well as anonymous feedback about the way people feel about their jobs.

As much as I’d like to say this is a good idea, I can see this being more of a tool for abuse than a tool for making changes. I suspect most managers would take negative feedback personally and investigate more, perhaps even asking network people to comb through logs to find out who posted comments, or perhaps even assume that a particular employee had written the commend without any evidence. We are often entirely petty in our reactions to criticisms, and I’m not sure most of the managers I have worked with would actually use this data to improve the way they perform their job.

However this is exactly the type of data that might really help some companies improve their internal operations. If they care about their employees, and want to retain the skilled knowledge workers, this type of site could help identify groups of people that are unhappy, or even managers that need improvements in their own skills. I do like that this is a third party site, which gives me some degree of confidence that this data could remain anonymous, something I wouldn’t assume would happen if the software were hosted on a company’s own servers.

I would like to know if any of you would use this type of tool at your job, and if you think it would help your company’s morale. In order for it to work, I think that the various manager’s bonus plan would need to be based on the results. Even if the manager’s “gamed” the system a bit to get a better score, that would result in a better work environment for most employees. One word of caution: if you do criticize managers, be careful how you do so. Some people can be identified by their word choice and writing style. Have someone review your message before you post it and let you know if the writing reads like your other communications.

Steve Jones

The Voice of the DBA Podcasts
- Watch the Windows Media Podcast – 19.1MB WMV
- Watch the iPod Video Podcast – 19.0MB MP4
- Watch the MP3 Audio Podcast – 3.9MB MP3
January 30, 2012
A Quick Export with SQL Packager

Disclosure: I work for Red Gate Software

Someone asked me the other day if I’d ever used SQL Packager to export a table to send to another person. I hadn’t, and in fact hadn’t even ever run the tool, but this individual said it worked great.

Since Red Gate tools are designed to be simple and intuitive, I thought I should give it a try and see what happens. I went through the Start Menu and found SQL Packager in my toolbelt installation:

I documented this as I went, shooting this images as I went through the process for the first time. As soon as Packager started, it began the packing wizard.

SQL Packager is designed to help you bundle up a database, or part of a database, as a part of an installation in your application. It can produce an .exe, a C# project, or a set of scripts that you can include as a distributable item in your application installation (or upgrade). The information can be compressed, so you reduce the requirements for your customers.

In my case, I decided to just package up a table. I first signed into my local instance, and chose the AdventureWorks database.

Next, I chose just one table, the Customers table. The Red Gate tools tend to follow a similar, intuitive design, and try to do the most common things for most customers. In this case, the entire database was selected (this is a database packaging tool), so I deselected all, and then chose the Customer table.

Once I choose the table and click next, and confirm the selection, the packaging begins. I get some options as to how I might choose to build my package.

The options are shown, and in this case I choose to save the script. Once I clicked next, I had a change to see the final script. First there was the schema tab:

On this tab, all the DDL for my table is there, including a couple dependent tables, and some functions needed for defaults or computer columns. Keys and indexes were included.

On the data tab, I had the DML for the actual data.

The comment says “Add 1000 rows”, which seems like a default. However I went back and checked in SSMS, and sure enough, my table had 1000 rows.

I clicked next, and had the chance to specify a save location.

After saving, I opened the script in SSMS, just to check. Sure enough, the DDL was at the top:

and the data at the bottom:

Simple, and easy.

If you are looking for a way to move certain sections of your database for a deployment, like all the lookup tables, give SQL Packager a try.

If you need to send some stuff to a client or friend, it might be a simple way as well to export the DDL and DML into one package.

January 26, 2012