SQL Snapshots – coming soon

Hi Folks,

I’d taken a bit of a break from producing SQL Down Under podcasts over the last few weeks. It came on the end of a lot of travelling. However, there is good news for listeners. I’m recording three shows over the next week, so watch out for a bunch of new shows. I hope you really like them.

Another question I’ve had from a few people is why I don’t get someone to interview me for a show. I remember Kent Tegels offering to do that a little while back and I might take him up on it soon. However, I did do a SQL podcast with Wally McClure and Paul Glavich’s ASP.NET podcast a little while back (show 12).

But I’ve decided to add another podcast into the mix. I should be posting the first of a new series called SQL Snapshots up over the weekend. My intention with it is just to post a weekly 5 – 10 minute show that discusses any SQL Server related topic that I’ve found interesting during the week. Watch this space for details in the next few days.

I liked the way one of the SQL Down Under podcast listeners described it as “hmmm – excellent – more nerd noise for the drive”. I hope you’ll think the same about this new series.



More on Primary Keys and GUIDs – NewSequentialID()

In answering a query on the SQLDownUnder mailing list today, one of the functions Paul Nielsen mentioned was the NewSequentialID() function, which is new to SQL Server 2005. I then had an email asking about this function, so I thought a short post explaining it might help.

In a previous post, I discussed one of the problems identified with using GUIDs as primary keys where they end up being used in clustered indexes. The preference for clustering keys today is short and monotonically increasing. Many will now consider a GUID as “short enough” so the remaining problem is the need to have a value that’s continually increasing, so that reasonable insert performance is achieved. This is what the NewSequentialID() function is designed to provide.

The function used to calculate a GUID has been well thought out. As soon as you start to change how that works, you have to start to worry about the real “randomness” of the calculated value. NewSequentialID() isn’t an answer to every problem but it might help in certain situations.

One of the internal input parameters to the function is the MAC address of the computer’s network card. The NewSequentialID() function generates a GUID that is based on this MAC address. Each value provided is always larger than any other value generated on the same computer. The issues to consider with this are:

1. MAC addresses are meant to be globally unique also. There have been situations where they haven’t been eg: network card manufacturers reusing address ranges. For the network card manufacturers, that isn’t an issue unless two cards with the same address end up on the same physical network segment. Overall, I’d rate the likelihood of this being a problem as low.

2. Some computers don’t have network cards. Where this occurs, the values generated could well end up duplicated on another system. This means, the generated GUIDs are fine on that system but combining data from multiple systems could yield surprises 🙂 Again, this is unlikely to be an issue except in very restricted situations ie: most computers running SQL Server have network cards of some type.

3. The GUIDs generated by NewSequentialID() follow a pattern. There may be a security issue to consider with this as knowledge of recently generated values could allow someone to predict upcoming values. There are limited scenarios where this would matter but in those situations, it could well be a showstopper.

4. As mabster mentions in the comments, one of the main reasons for using GUIDs as primary keys is that they can be generated at the client without reference to the database. Additionally, the NewSequentialID() function can only be used as a column default.

Apart from these, NewSequentialID() looks to be another useful new addition to the toolset although useful only in specific scenarios.

Avoiding source code deployment with CLR projects

One of the things I haven’t been keen on is the auto-deployment of source code when deploying an assembly from within a Visual Studio Database project.

Each assembly that is sent to the database ends up with an entry in sys.assemblies.

However, there can be multiple entries in sys.assembly_files for a single assembly. The binary of the assembly is one entry. Visual Studio also deploys the pdb file and the source code as additional files. Many see this as undesirable as it is another copy of the source code that isn’t in a source code control system ie: another copy of the source out in the wild.

Note: you need these files deployed to be able to debug against a server. However, that shouldn’t be happening on a production server anyway. For example, when you enable debugging on a connection in one of these projects, you are warned that when you hit a breakpoint, *all* managed threads on the server will stop. Think about that very carefully. This means that most developers should be testing/debugging against their own local copy of SQL Server (or SQL Express – yes it supports SQL CLR).

Today, on an email list I participate on, Milind Lee from the product group pointed out that you can avoid this deployment behaviour:

There is a Deploy Code check box in the deployment properties of the project.

Great tip thanks Milind !

Measuring the speed of light with chocolate chips

I spent some time at the University of Queensland way back in the 70’s. One of the things I remember most vividly was the physics pracs. In the pass-level class, they gave you the equipment, told you what you had to show and then told you step-by-step how to do it. In the honours-level class, however, they gave you the equipment, told you what you had to show but *didn’t* tell you how to do it.

I’ve often mentioned to people that the prac I remember best was one where we went into the room and my recollection of what they had given us was:

1. A 12 inch ruler

2. A coil of wire

3. Two metal discs mounted on a frame with a micrometer that could be used to find the distance between them

4. A voltage source

5. Two resistors in a box. The resistors were joined at one end and you could get to the three contact points

6. An oscilliscope

The prac was to “estimate the speed of light”. After my lab partner (and super-bright guy) Steve Paul and I got over the shock, I recall us spending about two hours working out what on earth to do.

So, this really caught my eye today:



TSQL Variable Definition based on Column Type

I ran into Scott Thornton at the VDNUG meeting in Melbourne a week or so ago. Scott was a student I had taught in a SQL Server class last year. He sent me a number of suggestions regarding enhancements to T-SQL he’d like to see, particularly ones he misses from Oracle. One that really caught my eye was on variable declarations.

Currently, when you declare a variable in T-SQL, you need to provide the data type. Often you are picking a type which is compatible with an existing database column type. The problem is that if the column type in the database changes, you can need to update a large number of procs, etc.  that use that type. Instead, it would be great if you could define a variable as:

DECLARE @TradingName dbo.Customers.TradingName

I’d really love to see that. I’ve posted it to the MS feedback centre. If you’d like to see it, the place to vote for it is:




What Makes A Good Data Entry App

We had an internal discussion the other day about what makes a good data entry app. The vast majority of Windows applications I see are lousy for data entry purposes. The discussion was prompted by an Outlook-style app that had a scrolling data-entry form window. My current hit-list for good data entry apps is:

1. Must 100% be able to drive it with a keyboard (good test is to open up your app, put your mouse off to one side and try to work)

2. Tab order must be totally appropriate

3. Must be able to see everything without scrolling (see #1)

4. Feedback on entry errors needs to be immediate but not intrusive (ie: no modal messages, etc.)

5. App needs to logically enable/disable fields based on previous inputs. Tabs take you directly to the next thing to answer

6. Fonts must be appropriate and consistent

7. Entry field sizes must display complete values without the need to scroll to see text (ie textboxes, etc. are big enough)

8. Entry field sizes should relate to the amount of text that can be entered into them (ie two textboxes that both cop 35 chars are the same size and it’s big enough)

9. Use of colour is subtle – mostly used for exceptions but occasionally for categorising items when things like grids are used (eg a grid that allows you to enter sales quantity/weight values might have most products black but new ones blue, items on promotion green, etc. but always easy on the eye and non-intrusive)

10. Entry errors are easily rectified/reversed so users feel comfortable “trying” things

11. Actions are responded to in a tactile way 100% of the time. (no wondering if you clicked or hit something)