Blog ala Vidar

There’s been quite a lot of changes in Ølen lately, but I’ll write more about that in a couple of days. Today I’ll tell you a couple of things about LIKE vs. = (equals). I got a phone call from Sigmund (btw, he’s much better now 🙂 today where he asked me why the following queries gave two different results:

SELECT something WHERE something = ‘string’
SELECT something WHERE something LIKE ‘string’

The first returned two records, while the second returned one. Strange? My first thought was unicode, but I didn’t actually know what happened here. The first record was ‘string’, while the second was ‘string ‘ (with a trailing space).

After some reading I found out that = removes trailing spaces. Just try for yourselves:

SELECT 1 WHERE CAST('a' AS VARCHAR(10)) = CAST('a     ' AS VARCHAR(10))
SELECT 1 WHERE CAST('a' AS VARCHAR(10)) LIKE CAST('a     ' AS VARCHAR(10))

Note that here we’re comparing ‘a’ to ‘a     ‘ (five trailing spaces)

SELECT 1 WHERE CAST('a     ' AS VARCHAR(10)) = CAST('a' AS VARCHAR(10))
SELECT 1 WHERE CAST('a     ' AS VARCHAR(10)) LIKE CAST('a' AS VARCHAR(10))

Now we’ve switched places. We’re comparing ‘a     ‘ to ‘a’. Suddenly it removes the trailing spaces on the left for both = and LIKE. Try changing to NVARCHAR instead of VARCHAR.

The conclusion is that when comparing strings it will always remove trailing spaces the left side, but only when using = on the right side.

EXCEPT if you’re using unicode 😀 When using LIKE on unicode it will not remove trailing spaces on either side! Confusing? Welcome to my world 🙂

Mr. Jon Jahren visited us yesterday, and we talked about SQL Server for 4 hours. It was fun, and very informative. I learned A LOT! In this blog I’ll try to sum up the most interesting discussions we had. We had a list of "questions" prepared for him:
– Tuning of (the setup of the) main SQL Server
– Specking new servers
– B-trees
– Mobile sync vs Replication
– Resource Governor
– Reporting Services

Tuning of (the setup of the) main SQL Server
First of all we logged on to the main SQL Server in Omega and started looking at hardware setup, settings on the server, database etc. The hardware setup was as it should be, but he explained a couple of settings that he recommended.
    "Boost SQL Server priority". This will, as it says, boost the priority of SQL Server on the CPUs. This should be turned off, because IF the server has some queries running 100% of the CPU you might not be able to log on to the server and kill those processes. He also explained that if we had network related problems, it might be a good idea to uncheck one core pr CPU, to let them deal with OS-related stuff (network). That way, SQL Server will not try to use these cores and therefore not be put in a queue.
    Under security on the server, there’s a option called "Enable Common Criteria compliance". This is a security feature that makes SQL Server not only release memory but overwrite memory with white-spaces or similar. Many customers has this as a criteria when setting up SQL Servers, so be aware!
    Named pipes should be disabled when you’ve got more than 500 users because this might cause performance issues if there’s more than 500 connections!
    When we talked about hardware setup, he reminded us about the problems with RAID5 and said he was a member of BARF(.com)

NB! Before SQL Server 2008 it was recommended to have the same amount of MDF files for the database as you’ve got CPU cores. This no longer applies to user databases, but this still applies to tempdb.

Specking new servers
Fast Track is collaboration between Microsoft, HP, Dell and Bull. This is a sort of standard for specking new servers. The only "thumb rule" we got was CPU cores X 4 = GB of RAM. We should double the amount of memory on our SQL Server.

B-trees
What’s the difference of a column in the index and an included column? First of all, you can’t filter on the included column. The reason they added included columns (or leaf node columns, as Jon wanted it to be called) was that the limit for indexes are 900 bytes. But in some cases you might want to select more than 900 bytes, but only filter on one or two fields.

Mobile sync vs Replication
We talked a bit about the differences between SQL Server Compact Edition and Express edition. CE was actually built with "cloud computing" in mind. CE should only be used as a local cache of data while it’s offline. EE could be used as a subscriber, or as a publisher/subscriber in merge-replication.

Resource Governor
This is a great tool for allocating resource. I’ve now set up a resource pool that restricts backups to only use 20% of the CPU. This means that users will hardly notice when we run backups (especially transaction log in the middle of the day). You can set various restrictions. For example I could make two pools and check for SUSER_SNAME(). If it’s me, then no restrictions, if it’s anyone else, use max 10% of CPU, RAM etc. I bet that would make my queries even faster! 😀

Reporting Services
We’ve changed our reporting engine a couple of times, and I know people are tired of recreating reports. Pr now, Reporting Services isn’t "good enough" for us, but MY PERSONAL OPINION (NB!!!! THIS IS JUST MY PERSONAL OPINION!!!!!) is that when reporting services is good enough, we’ll change to this. SQL Server 10.5 (2008 SP2?) will be released among with Office 14. In this release there will be no big changes to Reporting Services, but the release after this (11… Q2 2011?) will include a lot of changes to the Reporting Services tools. Most of them is actually ready already, but not included. So, no worries. I don’t think we’ll change reporting engine for at least 2-3 years 😉

I mentioned office 14. This will include an application codenamed Gemini. This is excel on steroides. The main goal for this application is to suite the needs of the heavy excel experts out there. With this you can connect to almost any database (dataware house), and play with millions of records in a couple of milliseconds. Looking forward to try this!

BTW! Did you know that the S in SUSER_SNAME() stands for Server?

If you log in to teamdocuments you can download all the files he sent me.

You might have heard the expression “computed column” before. If not, it’s an expression used for columns that are computed 🙂 A little more detailed, Field3 may be Field1 + Field2, Field1 * Field2 or similar. This is actually a pretty neat feature in SQL Server. Why? Let’s take a transactions table in an accounting system as an example. We’ve got a field called CurrencyAmount and one field called ExchangeRate. In most cases you want to calculate these as your currency’s amount. Therefore you make a field called Amount, and sets it as a computed column with the following formula: CurrencyAmount * ExchangeRate.

So, in your views, you can just use:

SELECT Amount FROM Transactions

Instead of:

SELECT CurrencyAmount * ExchangeRate AS Amount FROM Transactions

Also, you will always get the correct amount. In some cases I’ve seen people who have made a column “Amount” which was not computed. Then the user has to insert values into all three fields, and this might cause issues. Some users only use the “amount” field, others only the CurrencyAmount etc.

But what about persisted vs non-persisted? Persisted means that it’s stored WHEN SAVING the record. This means that every time you select/filter on the column, it’s picking data from the hard drives. If you’ve got heavy calculations this might be the best thing, but in many cases non-persisted is the best thing. Non-persisted means that it’s calculating Currency * ExchangeRate for each record when you SELECT the record(s). You might think that this is slower, but with the CPUs nowadays, this isn’t a problem at all. The bottleneck for SQL Server is disks, or IO-activity. You (almost) always want to reduce the amount of IO in a query, so next time you make a computed column, take a minute to think about if you want it persisted or not 🙂

And, just for the record. You can only have persisted computed columns in tables. Computed columns in views will always be non-persisted.

SQL Server Management Objects is a set of .NET objects from Microsoft. This replaced DMO (Distributed Management Objects) in 2005. You can use SMO to communicate with a SQL Server the same way you do with Management Studio. For example, you can list all tables, script them, find your permissions+++. You even use it to communicate with SQL Server Agent to make jobs etc!

The last days I’ve been working on a little application we’ll use to script over a database automatically. First we’ll delete all a-objects, then we’ll script them in from another database.

I’ve made an example project, so you can see how easy it is. First connect using a ServerConnection, and then setting the gServer. With gServer I can access any object I want on the server. To access a table, you use: gServer.Databases(“YourDatabaseName”).Tables(“YourTableName”)

To generate all my scripts, I’m looping the Script-method on the Tables property. I add a parameter vOptions so I only get what I want (dependent on which checkboxes are checked.

Click here to download the test project.