Top
Bottom
Top

Scott's Column
SQL Server vs. Oracle for Sequence Numbers, Laptops, Spam Filters

September 1, 2011
By Scott Lewis

I am getting ready to start a new project. Part of that is working with SQL Server. Since I usually work with Oracle there are some differences I need to learn. This month I cover how I dealt with the differences with sequence numbers in Oracle vs. SQL Server. The new project is probably going to require me to get a laptop, so I am revisiting my laptop research to find the best laptop for working in VB.Net and SQL Server. Finally, I spend some time perfecting my spam filters. Enjoy!

Current Topics:

Sequences - SQL Server vs. Oracle

I have been working on a project using SQL Server for the data. In my day job I use Oracle for data. SQL Server and Oracle do not have equivalent database objects for generating a sequence number.

What is a sequence number?

Let's start with a quick explanation of a sequence number. In Oracle (since it actually has a sequence number object) you create a sequence number. Typically you will use a sequence number as a way to generate a unique number that can be used to identify a record in a table. For example, say you have a table that holds orders. This ORDERS table will likely have a field that makes each record unique. This is often called an OrderID.

Here is the syntax for creating a sequence in Oracle:

Create Sequence SEQ_ORDER_ID
Start with 1
Increment by 1
Order;

You would run the above once to create the object SEQ_ORDER_ID. To get a value you would run a simple query. I have put that query into a Stored Procedure like this:

Procedure getOrderID(RESULT_VALUE Out Number) As
Begin
   Select SEQ_ORDER_ID.NEXTVAL
   Into RESULT_VALUE
   From Dual;
End getOrderID;

You would call this stored procedure from an application that was building an order, and the value would be saved in a record. You are guaranteed this value will be unique. Where it says Order at the end of the Create Sequence command means that the numbers returned will be in sequential order, as opposed to any order.

The beauty of a sequence in Oracle is that you can request the next value from anywhere. Whether you use it or not. So... in an application that was building an order, say a call center application, as you start taking the order you can request the OrderID right up front and display it for the person using the application.

SQL Server does not have anything like the sequence number. What SQL Server provides is an "identity" field. This is a field that gets populated with a unique value when you enter a record into the database. Typically you would create an ORDERS table and the first field in that table would be an OrderID field that was an identity field. Each time you save a record to the table, the OrderID field gets a unique value.

In our example above where we want to display the OrderID in the application it is impossible with the identity field because first you must save the order so that you can get the value. That means you have to have all (or at least the most important) fields ready so you can get the value.

Let's assume we have a table defined like this:

CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar(50) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[ProductID] [int] NOT NULL,
[Quantity] [int] NOT NULL

In SQL Server you would insert records using a stored procedure something like this:

Create Procedure [dbo].[sp_insertOrder]
@CustomerName Varchar(50)
@ProductID Int
@Quantity Int
@OrderID Int OUTPUT
AS
begin
   Insert into Orders(
      CustomerName,
      OrderDate,
      ProductID,
      Quantity)
   Values(
      @CustomerName,
      GetDate(),
      @ProductID,
      @Quantity)
   SET @OrderID = SCOPE_IDENTITY()
end

Notice here that we did not specify the OrderID in the list of values in our Insert statement. That is where SQL Server steps in and does this for you. The SCOPE_IDENTITY gives you the value that was generated for the identity field. With the above procedure we would receive the OrderID as its output.

But what if we want to create something that works more like a sequence number in Oracle. Where we can call for a new OrderID value before we save a record to our Orders table. We can do that using a table definted like this:

CREATE TABLE [dbo].[seq_OrderID](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[DateCreated] [datetime] NOT NULL

Now we create a stored procedure that looks like this:

CREATE PROCEDURE [dbo].[sp_getOrderID]
@OrderID INT OUTPUT
AS
begin
   Insert into seq_OrderID(DateCreated)
   Values(GetDate())
   SET @OrderID = SCOPE_IDENTITY()
end

Now we have a way to get a unique OrderID to display on a service rep's screen before they save the order to the database. As an added benefit we can tell exactly when the OrderID was generated by looking in the seq_OrderID table. Each OrderID will have a date associated with it.

This takes just a little more time to setup, but once it is done you have a more flexible way of using sequence numbers than SQL Server gives you out of the box with the identity option on a field. Here are some of the benefits of using this method to generate sequence numbers:

  1. You do not need to have all the NOT NULL fields ready to get an ID, since you can get your ID value before inserting into the table holding the data.
  2. You can have more than one field in a table that gets a value from a sequence. In SQL Server only one field per table can be an identity field. So you cannot have two generated values in a single table. By using separate tables to create "faux" sequence numbers you can have more than one sequence in a single table.
  3. You can use these table generated sequence values to build human readable IDs. Say you want to have an ID field that looks like STR-0001. You can build that from a "faux" sequence number and display that to your users. Then the ID field in the actual table can be a varchar that can have meaning to someone reading it. Sometimes it is better to have human readable format rather than everything starting with 1 and counting until eternity.

Top

Laptops Revisited

It has been quite a while since I looked at laptops. I pretty much gave up on the idea of getting one. I have two desktop computers now... and I only have time to use one on a regular basis. The other is a test machine running Win XP, Mac OSX & Ubuntu.

But a new project I am working on may require I get a laptop. In the past I thought about getting either an ultra portable laptop, or one with a full HD screen (1920x1080).

This time around I have given up on anything heavy. Since one of the reasons for the laptop is travel... size is important. Smaller the better.

I would still prefer a laptop with a screen resolution higher than 1366x768. I find that to be a bit tight when running Visual Studio and SQL Server Management Studio... and remotely connecting into a server or other workstation.

Notice though, that I need to run Visual Studio and SQL Server, which makes the MacBook Air almost impossible. I would love the 13" MacBook Air. I have no trouble using Boot Camp to dual boot the computer into Windows. And the screen resolution of 1440x900 is perfect.

Unfortunately, $1,299 is a lot to pay for a MacBook when I need a Windows development laptop. And you only get 128 GB of storage space with the SSD. If I partition that half for OSX and half for Windows... Wow... only 64 GB for a development machine. I don't think that will work. So the MacBook Air is out of the question.

Because I want portability over all else (except screen resolution), I am willing to skip an optical drive. I can always get an external USB drive if I need one in a pinch. I would prefer a 14" laptop, especially if I could get a high resolution one. But I will most likely end up getting a 13.3" laptop.

I used LaptopMag to get most of the information for these laptops. CNet filled in the blanks.

So... what's out there?

My top 4 picks are here:

Manufacturer Toshiba Lenovo HP Dell
Model Portege T420i Envy 14 Vostro V131
Price (approximately) $899 $899 $999 $799
Weight 3.2 lbs. 4.9 lbs. 5.6 lbs. 4.1 lbs.
Screen Size 13.3" 14.1" 14.5" 13.3"
Screen Resolution 1366 x 768 1600 x 900 1366 x 768 1366 x 768
Video Intel HD Graphics Intel HD Graphics Radeon HD 6630M Intel HD Graphics
Processor Core i5 Core i5 Core i5 Core i5
Processor Speed 2.3 GHz 2.3 GHz 2.3 GHz 2.3 GHz
Memory 4 GB 4 GB 4 GB 4 GB
Hard Drive 640 GB 500 GB 750 GB 500 GB
Hard Drive Speed 5400 RPM 7200 RPM 7200 RPM 7200 RPM
Rating 4.0 out of 5 4.0 out of 5 4.0 out of 5 3.5 out of 5 **
Optical Drive DVD Burner DVD Burner DVD Burner None
Battery 6.2 Hours 10.5 Hours 4.9 Hours 6:09 hours **

Here is my quick take on these:

Toshiba Portege R835 - From LaptopMag, "Thanks to a new Core i5 processor and other welcome upgrades, the Toshiba Portege R835 is one of the best full-performance ultraportables you can buy for less than $1,000. Those in the market for a Windows-powered ultraportable will find the Portege R835 to be a solid value."

The Toshiba is a head in this race because of its light weight. No... it is not as light as the MacBook Air... but at 3.2 lbs. it is much lighter than any laptop I have ever used. Battery life is a huge plus here as CNet tested this laptop to provide 7 hours of video playback. More than enough to fly across country... and sit in airports... for a day of traveling. I wish Toshiba would may a 14" version of this laptop with a high resolution screen.

Lenovo T420i - From LaptopMag, "A 14-inch powerhouse, the Lenovo ThinkPad T420 delivers all-day endurance at an affordable price."

This is it... a 14" laptop that weighs just a tick under 5 pounds... and can be optioned with a 1600x900 display. With the screen upgrade and a boost to an Intel Core i5 processor this laptop comes in at $899. Not cheap, but a lot more attractive than $1,300 for a Macbook Air, and on par with the going rate for the R835. Of course... this is Lenovo... and staunchy ex-IBM style Lenovo, so this laptop has no style. If we want to step up from the T420i to the T420 we get a slightly faster Core i5 plus discrete video. The T420 also upgrades the OS from Windows 7 Home Premium to Professional. But the cost goes up to $1,079, not something I like. Note: LaptopMag reviewed the T420, and I am looking at the T420i.

HP Envy 14 - From LaptopMag, "HP's updated Envy 14 sports a second-gen Core i5 processor and switchable graphics at an affordable price."

I like the Envy 14. It used to be available with a higher resolution screen, but not anymore. 1366x768... just like everyone else. But it does have a backlit keyboard and discrete graphics switchable to the Intel integrated. This should improve battery life when not using the more powerful graphics. Its construction is similar to the MacBook Pro line. A nice solid chunk of aluminum. Unfortunately, the Envy 14 is heavy for a 14" laptop at 5.6 lbs, and that is seriously holding it back.

Dell Vostro V131 - The Vostro is between the R835 and the Envy 14... weighing in at 4.1 lbs. It is a solid 13" laptop, but I am having a hard time finding something in it that stands out to push it over the top. The Dell drops an optical drive... which makes me wonder why it isn't under 4 pounds. The R835 is significantly lighter and it does have a DVD drive. The rating of 3.5 and battery life in the chart comes from CNet. I wanted to get a rating for each laptop from the same source, to make them comparable, but LaptopMag did not have a review of the Vostro V131.


I would like to give props to a few other laptops. They are not perfectly suited to me... but they could be attractive to others and are worth looking into.

Asus U36 Series - I could not find enough reliable review information on this laptop, but I like its size and weight. 3.74 lbs. and no optical drive. That beats the Vosto's 4.1 pounds... which makes the lack of optical drive seem appropriate. This laptop includes nVidia switchable graphics... which might come in handy if I do any gaming on the laptop.

Asus U31SD-A1 - This version from Asus is a 4.2 pound laptop with a favorable review (3.5 out of 5) from LaptopMag This was the closest I could get to the U36 when looking for a complete review of an Asus. So my comments for the U36 Series should probably apply here as well. The U31 lasted over 9.5 hours in LaptopMag's battery test. Not bad considering the graphics hardware. Even though it is geared for gaming... its weight and battery life are still compelling. I left it off the chart because I already had 3 laptops with 4 stars, so adding another with 3.5 seemed unnecessary. Also I couldn't fit anymore. So this one gave it up to the Dell... which is a brand I am more familiar with .

Acer Aspire TimelineX 3830TG-6431 - LaptopMag rated this above the Asus U31 at 4.0 out of 5. It was praised for its graphics power (more than the U31), which would help gaming, but probably not help me much with my intended use. The Acer sacrifices battery power for more graphics power than the Asus... making it less attractive to my needs.

HP Pavilion dv6t - I want to give honorable mention to the dv6t from HP. This is a 15.6" laptop... that can be optioned with a 1080p screen. Yes... a full HD screen at 1920x1080. Awesome! Unfortunately, this laptop is heavy at 5.78 lbs. I really want to stay at 5 lbs or less.

Notably absent from all this is the Samsung Series 9. The 13.3" version is perfect. It really is the MacBook Air for Windows users. However it is so damn expensive it will never make it onto my list. For the most part I am trying very hard to stay under $1,000.

I should be making my mind up soon... but I will probably have to wait and make this a Christmas present to myself to justify spending the money. We'll see how the project goes as the months progress and the need for a laptop outweighs my ability to be a cheap-skate.

Top

Spam Filters

I hate spam. Yea, I know, so do you. A long time ago I created some filters to take care of the spam problem. It was getting pretty bad at one time. I remember getting over 100 spam emails a day. My ISP does spam filtering... but a lot still gets through. Recently I found myself reviewing my spam folder way too much.

My filters worked pretty much by white listing emails. Emails from people I wanted to receive emails from went directly to my inbox. Some emails would go other folders, such as car show & meet up emails into a folders of their own. The key (back then) was the final filter to run would put all remaining mail into a possible spam folder... and mark it as read so I wouldn't have to look at it.

Perfect, right? Not so fast. I still had to look through the possible spam folder to see if I got an email from someone not already white listed. But it was worse than that. I would also get emails from people or businesses (like Amazon) that does not count as spam, but is stuff I don't usually care much about. And this was taking up way too much of my time.

What I needed was a filter that would actually delete real spam outright. So I set about creating a real spam filter. Until I could trust it I had it move messages to a real spam folder... as apposed to a possible spam folder.

Now it's perfect, right? Nope. Nothing was going into the real spam folder. I would regularly copy just part of the email addresses that spam was coming from, to try and get as much of it as possible. Nothing would work. The email address are so complicated that the filter can't catch them. When I actually looked at the sender's addresses they are incredibly long. (Like this: disequaliser.aggregately@
xanthoma357.mestee983.fathoming852.previsioning326.multifunctional
pregraduation.info
) I tried filtering on "pregraduation.info" but that did not help. Then I noticed that the addresses change every day! How can I keep up with that.

Time to get tough. I started unsubscribing to every accessory email that came my way. If I was getting offers from someone that I had a legitimate subscription with, I would unsubscribe to the extra stuff... or black list it into a crap folder. I continued to add addresses to the real spam list as well. With this kind of diligence I was able to get my spam much better under control.

Now I can safely delete emails that end up in the real spam folder. It catches two or three a day. I can also safely delete mail in the crap folder. Sometimes there might be an offer from Amazon worth looking at, but there is no harm in deleting from this crap folder.

That still leaves the "leftover" mail that goes into the possible spam folder. With the two new filter above I can scan the possible spam folder in a few seconds, because almost all of it is spam that can't be filtered. I rarely see anything worthwhile in there anymore.

Spam is much better controlled. Let's say it is Almost Perfect!

Top

My SQL Server vs. Oracle sequence number is the tip of the iceberg. I am about to start a new project that will involve SQL Server with a new Visual Basic .Net 2010 developed front end. It will be a big change, and hopefully provide some more content for this column in the coming months.

Bottom