CLR Integration Changes in SQL Server 2008

Technorati Tags: SQL Server 2008,CLR Integration,Nullable Types,Database

CLR Integration was first introduced in SQL Server 2005. Though there was a lot of excitement when the feature was  introduced, it has since fizzled off. Personally, I think CLR Integration is one of the most underused features of SQL Server. For some reason, we have not been able to move away from extended procedures or UDFs to writing CLR Integrated code.

In SQL Server 2008, there are two noticeable introductions to CLR Introduction. One is the support for LINQ and the other is the support for Nullable types.

To enable CLR support in SQL Server, you first have to switch it on. This is done by running the sp_configure with clr enabled = 1.  One thing to keep in mind is when you are using nullable types, you cannot use the automatic deployment option within Visual Studio 2008. You will have to register your assemblies manually. I will briefly show you how it is done.

Once you have turned the support for CLR on, the next step is to create your assembly. For this, I have a stored procedure written in Visual Studio. The code for the same is as below

 

   1: using System;

   2: using System.Data;

   3: using System.Data.SqlClient;

   4: using System.Data.SqlTypes;

   5: using Microsoft.SqlServer.Server;

   6:  

   7:  

   8: public partial class StoredProcedures

   9: {

  10:     [Microsoft.SqlServer.Server.SqlProcedure]

  11:     public static void SearchEmployee(Int64? iEmployeeId, out Int32? iVacationhours)

  12:     {

  13:         iVacationhours = null;

  14:         if (iEmployeeId !=null)

  15:         {

  16:             //open the sql connection with the current context

  17:             using (SqlConnection connection = new SqlConnection("context connection=true"))

  18:             {

  19:                 //open the connection

  20:                 connection.Open();

  21:                 //build the query and execute it               

  22:                 string query = "Select VacationHours from HumanResources.Employee where EmployeeId = " + iEmployeeId.ToString();

  23:                 SqlCommand sCommand = new SqlCommand(query, connection);

  24:                 SqlDataReader vacationReader = sCommand.ExecuteReader();

  25:                 using (vacationReader)

  26:                 {

  27:                     vacationReader.Read();

  28:                     iVacationhours = vacationReader.GetInt32(0);

  29:                 }

  30:             }            

  31:         }

  32:         

  33:  

  34:     }

  35: };

The code just returns the vacation hours for a given employee. The point worth noting is that both input and output parameters are nullable types. The next step is to add the assembly to the SQL Server database.

The syntax to do that is

   1: create assembly NullableTypes from

   2:  'C:\Users\Administrator\Documents\Visual Studio 2008\Projects\NullableTypesExample\NullableTypesExample\bin\Debug\NullableTypesExample.dll'

   3:  Go

Once the assembly is registered, you can now proceed to create your stored procedure pointing it to the managed code by executing the statement below

   1:  Create Proc dbo.SearchEmployee(@a bigint, @b smallint output)

   2:  as

   3:  EXTERNAL NAME NullableTypes.[StoredProcedures].SearchEmployee

   4:  GO

That is it!!! You are now all set to execute the stored procedure and check the results. For your reference, I am also giving below the statements to execute the procedure

 

   1:  declare @outvalue smallint

   2:  set @outvalue = 0

   3:  exec dbo.SearchEmployee 1,@outvalue output

   4:  select @outvalue

If I change the input value to null, null will be returned as output exhibiting support for Nullable Types.

A Date with SQL Server 2008

Technorati Tags: SQL Server 2008,Database,Datetime,datetime2,datetimeoffset,SwitchOffset,datatypes

Almost everybody who has worked a fair bit in SQL Server knows that the datetime data type in SQL Server is one of the most primitive. Infact, in a lot of applications I have seen, they don’t even see it worthy of being used, opting to use varchar instead. Their argument: If I am going to convert it into varchar anyways, then I might as well store it in the same format.

Hopefully, that will change with SQL Server 2008. DateTime datatype has been given special attention in the latest edition of SQL Server; so much so that I will stick my neck out and say that it is the only data type that has been enhanced!!! (FILESTREAM is not a data type, remember?). This post is an attempt to give you a preview of what the enhancements are in the DateTime datatypes.

In the previous versions of SQL Server, there were no separate datatypes for date and time. That has changed now. In SQL Server 2008, you have date and time datatypes that let you store only date or time. Apart from this, there is also the datetime2 and datetimeoffset. The datetime2 lets you store a wider range of dates. If you use datetime your minimum value for the year is 1/1/1753. The datetime2 datatype lets you store a wider range and is based on the ANSI standard. The datetimeoffset as you would have already guessed, helps you store dates for different regions. Some of the key points that you have to remember when using the above datatypes are

• You cannot use mathematical functions or expressions with the new datetime2 datatype. Use DateAdd instead.
• Try avoiding the format ‘dmy’. This is the only string format that produces different results with the old datetime and the new datetime2 format.
• Most of the old t-sql functions work consistently with both the datetime and the datetime2 datatypes.
• If you are using datetime2, use the SYSDATETIME function for more precision.
• Though you can use the Datetimeoffset datatype, SQL Server 2008 does not support automatic support for daylight savings.
• It does not also provide support for timezones, so the most effective way to use the offset is to store the timezone at which the date was entered.
• Use the SWITCHOFFSET function of SQL Server to convert the offsets to the timezones.

Madras Quiz 2009

Technorati Tags: Chennai,Quizzing,Madras Week,Indian Quizzing League

I am just back from the annual Madras quiz that is conducted as a part of the Madras Week celebrations. The quiz this time was conducted by the Indian Quizzing League.  The quiz master for the day was Sylvian Patrick.

The event started off with a 30 question prelims. There were some thought-provoking questions from various aspects of Chennai, ranging from the Chennai Metro to the Ergo. On the whole, the prelims were not too tough. A lot of the questions were guessable. Couple of questions from the top of my head

What did Gandhiji establish in Chennai in 1918 recognising the importance of the whole nation speaking one language?

Who is the most famous grandson of Rajagopala Bhagavadar who excelled in Hari Katha kathakalakshebam?

Six teams made it to the finals, which comprised of 5 rounds of Madras Intensive questions. Jayakanthan and his teammate managed to surge ahead wresting the rolling trophy from V.V. Ramanan and Ramshankar who were lacklustre today. There were some quality answers from all the teams. I don’t remember the name of the team who finished second.

Overall the quality of the questions in the finals was passable. I personally believe the Indian Quizzing League need to find themselves some quizmasters. I attended their India quiz and there again, the quizmasters were found wanting.