Author: way0utwest

I never liked GUIDs, not really using them in most of my development until I had a conversation many years ago with Andy Warren. At the time he was working for a company as a development manager and building in house applications. We were debating the ways in which we built applications, me mainly using web technologies, and him still using client-server technologies at a call center. We debated the various pros and cons of both methods, and one thing he said stuck with me for many years.

At the time I often used (and still do use) identities as the surrogate and PK of many of my tables. That’s another debate, and I don’t want to digress here. Instead I wanted to point out an interesting item that Andy explained to me years ago about the advantages of GUIDs over identities.

The client can create them.

That’s pretty interesting, especially in cases where you need to insert a parent and a few child records from a remote machine. You can create the parent record, and child records, without a round trip to the database, and still have them related. That’s something that isn’t easy to do with identities, and it could easily cause lots of breakage in your client applications.

An identity is generated on the server. You insert a row, you then use @@scope_identity, not @@identity, to retrieve the identity of the inserted row. You can then use that and insert it in your child records. It’s quick, clean, and it often follows the workflow for an application.

However a GUID, which is supposed to be unique (it isn’t always), can be generated on the client. A client can’t generate an identity since it can’t be sure of the concurrency issues with other clients. A GUID can be built on the client, it’s likely unique, and so the client can already know the surrogate key for the parent, thus the FK for the child rows, and send all that back with a minimum of round trips to the server.

There are different ways to create a GUID on the client, depending on your technology, but they all essentially work the same as the NEWID() function in SQL Server. You’d have to look for a function or API depending on your client technology to do this.

It’s an interesting idea, especially with disconnected clients, which might need to update a local database of some sort, and then have that replicated or sync’d with a master database later. By using GUIDs for the surrogate keys, you don’t need to set aside ranges of identities, or worry about collisions for the most part. Just be aware there could be issues, and this StackOverflow question highlights some of the issues.

Adam Machanic started the idea of a blog party last month, getting people to write about date and time issues in SQL Server. This month he’s asking for a puzzling situation, and I had a little time, so I decided to oblige.

I’ve done a lot of different things in SQL Server over the years, but this particular situation is one that stumped me for awhile. And I’m not the only one as I see this question posted on a regular basis and the answer typically is similar to what I used to solve my problem.

Years ago I was working for a small company, receiving regular data feeds from vendors that required us to load and update our list of products. We sold education based products, and as the cost, name, etc. changed from Vendors, we had to update our data.

So I had a table that was something like this one. This is simplified for the sake of the example.

CREATE TABLE MyTable
( id INT,
  PONumber VARCHAR(20),
  Cost NUMERIC(10,4)
  )

One day I had to run an update statement like this one to fix a product. It was a simple update that would correct a simple product:

UPDATE dbo.MyTable
SET Cost = 56
WHERE PONumber = 24

and I got this:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ’24 A’ to data type int.

Huh? I’m updating a specific product, and while the data types are different, this ought to be easily handled by the engine. I tried this same statement a few ways, and it kept failing.

I see many people running into this, and not understanding why it fails. After a few hours of working through this I eventually realized what was happening.

There is an implicit conversion occurring here because the data type of the PO number is a varchar, meaning that the value is

’243’

‘24’

etc.

and it’s being compared, or converted, to an int to match the 24 integer in the WHERE clause. If you examine the implicit conversion table in the CAST and CONVERT BOL entry, you’ll find that this is indeed an implicit conversion.

Does order matter? No. This returns the same error.

UPDATE dbo.MyTable
SET Cost = 56
WHERE 24 = PONumber

Why isn’t 24 converted to ‘24’ and compared? I think it has to do with data type precedence, in which int has a higher precedence than char (or varchar), so the conversion takes place with the column being converted to 24, an integer.

I fixed this by changing my query.

UPDATE dbo.MyTable
SET Cost = 56
WHERE PONumber = ’24’

That removed the conversion, and things worked.

Knowing your implicit conversions is important. It’s a basic thing, but you ought to have an idea of when they occur, and you ought not to depend on them. This should be something you keep in mind, and make it like the printer power cord: check it first.

Happy T-SQL Tuesday!

Most people set their New Year resolutions for the new year at this time. We know from history that many people don’t stick to those resolutions, often failing to get in better shape, eat better, or other similar personal changes. I think the average person has bailed on their efforts by Feb 1.

I think some of this is precisely because they’re not goals that are specifically written down and committed to. It’s easy to say you want to make some grand change, but the reality is that you need to focus on one small goal at a time and make a concentrated effort to achieve it.’

Many of the Microsoft MVPs and active, successful community people I know create goals. They list a series of things that they want to accomplish in the new year. Some of them are big goals (publish once a month), and some are small (take a test, attend an event), but they are a list of measurable goals for the new year.

And most of them then look back at the previous year as they’re getting ready. I did my goals for 2009, a series of updates through out the year, and then a final look back. I had written a way to measure my success (or failure), which is something I’d recommend as well. I did all of this before writing goals for 2010, using my thoughts on 2009 to look forward to 2010.

Here’s what I’d suggest, since it’s not too late:

1. Think of some goals for 2010 that you want to accomplish. They can be big or small
2. Write them down, along with a way to decide if you’ve succeeded or failed.
3. Set a quarterly reminder to revisit your goals and see how you are doing.
4. Analyze how you did between Christmas and New Years next year.
5. Repeat.

Is that really what people want? At least in the US? This note talks about how KPMG surveyed executives and while biodiesel wasn’t on the list, hybrids and electric power were. They seemed to think that was the important thing to focus on to make sales.

I wonder if that’s true. Is this a case of the industry focusing efforts in an area that may not be that popular, but raising awareness and using advertising to influence people? Or is it really what many people want?

It’s a hard thing to decide. Looking at many businesses, and reading about them, it seems the line between success and failure is often a good amount of luck. So many executives guess at things and steer the company that way. If they succeed, they’re smart. If not, they’re fired.

I like my hybrid, and it has some great qualities, but I’m not sure it’s worth the extra $$ for many people. Especially if you don’t drive a lot. So convincing people to spend the extra $$, is that good for consumers and what they want? Or just good for the auto industry?