For the Love of Software

Tuesday, January 1, 2008

List of articles I read on 2007

This is a list of most of the articles I read in year 2007, I wish you find some of them interesting.

Sunday, December 23, 2007

Are MS SQL Server stored procedures precompiled ?

A common interview question and a common answer:
Q: Why should you use Stored procedures not ad-hoc queries?
A (happily, he's sure that he knows how to answer this question): They perform better because they are precompiled at creation time, also ...(Some, other reasons that I don't really care about now)...

Is that true?

In fact, this is a common misconception that many users of SQL Server 7.0, 2000, and 2005 suffer, may be because this was the case in SQL Server 6.5. Also because inaccurate and not up to date articles, books ,and learning materials.

I quote this from msdn:

In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan. At the time the stored procedure was created, a partially compiled execution plan was stored in a system table. Executing a stored procedure was more efficient than executing an SQL statement because SQL Server did not have to compile an execution plan completely, it only had to finish optimizing the stored plan for the procedure.

This is not the case in for SQL Server 7.0 and higher, again from msdn:

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement.

Knowing this, it's common that arguments take place from time to another comparing using Stored procedures vs. ad hoc queries.

Personally, I prefer to use stored procedures because I find this more modular.

My advice is, whether you use Stored procedures or ad hoc queries, always use best coding practices when performing database operations to avoid SQL injection attacks. Be them parameterized queries, prepared statements, or whatever depending on the programming language and framework you use.

Inspiring speech by Steve Jobs (Apple CEO)

Steve Jobs speech to the graduates of Stanford University in 2005
I enjoyed it and learned from it. He gives great directions in life and work.

Friday, December 7, 2007

How to check programmatically if WSS or MOSS are installed

I came into a situation when I needed to check if a SharePoint installation exists on a system, and specifically, WSS (Windows SherePoint Servises) or MOSS (Microsoft Office SharePoint Server).

The two properties can be useful in this case, the idea depends on checking the registry.
Note that I extracted this code from the open source project SharePoint Solution Installer and made minor modifications.

To check for WSS we check the key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0]
and the value "SharePoint" must be "Installed"

To check for MOSS we check the key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\12.0]
"BuildVersion"="12.0.4518.1016"
and check that the version is 12

Here is the code:


     public static bool IsWSSInstalled
     {
         get
         {
             string name = @"SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0";

             try
             {
                 RegistryKey key = Registry.LocalMachine.OpenSubKey(name);
                 if (key != null)
                 {
                     object val = key.GetValue("SharePoint");
                     if (val != null && val.Equals("Installed"))
                     {
                         return true;
                     }
                 }
                 return false;
             }

             catch (SecurityException ex)
             {
                 throw new Exception("Access deined. Could not read the Windows Registry key '" + name + "'", ex);
             }
         }
     }

     public static bool IsMOSSInstalled
     {
         get
         {
             string name = @"SOFTWARE\Microsoft\Office Server\12.0";

             try
             {
                 RegistryKey key = Registry.LocalMachine.OpenSubKey(name);
                 if (key != null)
                 {
                     string versionStr = key.GetValue("BuildVersion") as string;
                     if (versionStr != null)
                     {
                         Version buildVersion = new Version(versionStr);
                         if (buildVersion.Major == 12)
                         {
                             return true;
                         }
                     }
                 }
                 return false;
             }

             catch (SecurityException ex)
             {
                 throw new Exception("Access deined. Could not read the Windows Registry key '" + name + "'", ex);
             }
         }
     }

and at the top of your class:

using Microsoft.Win32;
using System.Security;

Tuesday, November 13, 2007

Free Microsoft E-Learning: What's New in Microsoft SQL Server 2008

Microsoft e-learning site offers free online training for SQL Server 2008
I Hope this helps getting ready for the new release...

Query to get database relations in SQL Server 2005

It is a common need to get a list of foreign key relations in a database
This simple query does this:


Select a.Column_name as [FK Column],a.Constraint_Name as [FK],c.column_name [PK Column] ,Unique_Constraint_Name As [PK]
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a
join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b on a.Constraint_Schema=b.Constraint_Schema and a.Constraint_Name=b.Constraint_Name
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c on c.Constraint_Schema=b.Unique_Constraint_Schema and c.Constraint_Name=b.Unique_Constraint_Name

Friday, November 9, 2007

Things I liked about Visual Web Developer 2008 Express

Microsoft's decision of releasing free express editions of visual studio (starting from VS 2005) has proven to be a really right step. Beginners and enthusiastic developers found express editions a great start or a chance to look into the MS world of software tools.

I used 2005 Express editions and today I downloaded Visual Web Developer 2008 Express and found nice things that I'd like to share:

Split view: Now you can view both HTML source and Design view of web pages, this feature makes design much easier, however you need to save or synchronize for HTML changes to reflect in the design view.

New Project and Item types: WcfService and AjaxWcfService

Targeting Framework version: You can choose which .net framework version to target.

AJAX Extensions are pre-installed

These are few things that I liked, discover more yourself by downloading Express editions.