Jaycent Drysdale

Anticipated new Data Types in Microsoft SQL Server 2008

The next release of Microsoft SQL server, version 2008, promises a number of significant changes over its previous 2005 version release.  From a Developers perspective, there is much anticipation over the new data types that we will get to play with in our stored procedures and other database objects. I’ll go through some of the new data types in this post.

New Date data types:
Date:-The new Date data type allows you to store dates without a time component from 0001-01-01 to 9999-01-01. This new type will lend itself well to situations where a date variable doesn’t need to have a time attached to it...like a date of birth field/variable. There is also the Time type, which as you might guess, stores a time value, minus a date portion. Already I'm seeing situations where these Data types would have saved me a few lines of code had hey been available earlier. Other new date types include DateTime2 and DatetimeOffset.

New HierarchyId data type
According to the Microsoft documentation, the Hierarchyid is a new data type that can store values that represent nodes in a hierarchy tree. This data type, which has a flexible programming model, can be implemented as a Common Language Runtime User-Defined Type (CLR UDT). The CLR UDT exposes several efficient built-in methods for creating and operating on hierarchy nodes. I am eagerly awaiting further details on the use of this new type, but at first glance, it seems we will now be able to store the hierarchical metadata that describes things like menu trees etc in a format that is easily retrievable. Building menus etc could be as easy as binding a treeview control to a field returned from a database or web-method call. I hope my understanding of this new type is not too far off from what it actually is. Time will tell.

UserDefined Table type:
Again, the Microsoft documentation described this new type as follows: A user-defined table type represents the definition of a table structure. You can use a user-defined table type to declare table-value parameters for stored procedures or functions. You can use this table type to declare table variables that are to be used in a batch or in the body of a stored procedure or function. To ensure that the data in the table type meets specific requirements, you should create unique constraints and primary keys on the table type. Actually, this is a neat addition. If I understand this correctly, we can pass in an array (table) of parameter items into a stored procedure as a unit. So instead of passing in 50 individual parameters into a Stored Procedure, It’s now possible to pass in a single UDT type that holds all 50 parameter bits that need to get to the Query.

FILESTREAM storage
Now here is where it gets interesting. It’s now possible to store data directly to the file system on the server via the FILESTREAM data type. It allows you to store unstructured data directly in the file system. You can use the new storage type VARBINARY(MAX) FILESTREAM to define table columns and store large binary data as files in the file system instead of storing them as Binary Large Objects (Blobs). In addition, you can use T-SQL statements—SELECT, INSERT, UPDATE, or DELETE—to query and modify FILESTREAM data. You can use the rich set of streaming APIs provided by Win32 for better streaming performance, while maintaining transactional consistency. You can also apply SQL Server functionalities to FILESTREAM data such as triggers, full-text search, backup and restore, SQL permissions, Database Console Command (DBCC) checks, and replication.

Sparse Columns
Typically, if you have a column/field in your database table that is infrequently used, and contains a null value in most cases, the new Sparse Columns feature provides a more efficient way to represent these.

In wrapping up, its also worth mentioning that the UDT types, introduced in SQL Server 2005, have undergone some changes in the 2008 release. Previously UDT types were limited a size of 8K....that limitation has been removed.

Options for Database access outside of ADO.Net

In the world of .NET programming, ADO.NET has become the De Facto standard for accessing database of all types (relational or otherwise). The purists among us will be quick to point out that ADO.Net should be the only option and that as developers we shouldn’t even be thinking about bypassing ADO.NET to get to our database. But the no-so-pure will be quick to point out the alternative methods for pulling data from our databases without writing a line of database code. If you are still reading this article, it means you, like your truly have had occasions in the past where bypassing ADO.Net makes sense, whether you are creating a small website for your wedding guest list, or just putting together a demo website of sorts.

The SqlDataSource Control:
The SqlDataSource control (in my mind, the grand-daddy of declarative data Access) allows for the definition of queries in a declarative way. You can connect the SqlDataSource to controls such as the Datalist, and give your users the option to edit and update data without requiring any ADO.NET code.  While the sqlDatasource control handles the heavy lifting required to facilitate the communication, it's worth mentioning that behind the scenes, it uses ADO.NET to do this heavy-lifting. The sqlDatasource supports any database that has a full ADO.NET provider. Apart from the fact that the sqlDatasource connects you to your database with minimal code, it does provide more benefits to developers who are looking to provide functionality such as paging sorting of datagrids without having to write lines of code to achieve this. If you bind your datagrid directly to a sqlDatasource control, you have paging and sorting functionality right at your fingertips.  You can get to it by setting a few properties on your grid control. However, with all its "niceties" the SqlDataSource is somewhat controversial, because it encourages you to place database logic in the markup portion of your page. Many will agree that there are times when the benefits of using this control far outweigh this particular drawback.

LINQ to SQL:
Linq provides us with cool new way of accessing data.  The source of this data can range from anything from files in your computer file system, a collection of objects living in a generic list in server memory, or rows of data residing in a database table in on the North Pole. Linq comes in a variety of flavors (LINQ to objects, LINQ to XML, LINQ to entities, LINQ to SQL, LINQ to Dataset).  With LINQ to SQL, you define a query using C# code (or the LinqDataSource control) and the appropriate database logic is generated automatically. LINQ to SQL supports updates, generates secure and well-written SQL statements, and provides some customizability. Like the SQLDataSource control, LINQ to SQL doesn’t allow you to execute database commands that don’t map to straightforward queries and updates
(such as creating tables). Unlike the SqlDataSource control, LINQ to SQL only works with SQL Server and is completely independent of ADO.NET.

Profiles:
The profiles feature, introduced in .Net Framework 2.0, allows you to store user-specific blocks of data in a database without writing
ADO.NET code.  You specify what data you want to gather and store by configuring the appropriate elements in your applications configuration file.

In wrapping up, it’s worth mentioning that none of options presented in this article is a replacement for ADO.NET, because none of them offers the full flexibility, customizability, and performance that hand-written database code offers. However, depending on the specific needs of your application, it may be
worth using one or more of these features to augment your ADO.Net centric data access layer.

Enhance your apps with the Integrated ASP.Net Pipleline

The January publication of MSDN Magazine carried an article titled "Enhance your apps with Integrated ASP.Net Pipleline". While the subject of the article is not something that I would normally be super excited about, I decidede to spend at least 10 minutes perusing the article while commuting to work this morning (Been riding the train past couple of weeks).  I recently installed Vista on my Notebook, and was keen to start looking into the features of IIS 7.0, which ships with microsoft's latest desktop operating system.  As it turned out, the article touched on a subject that might be useful down the road as we take a leap into the world of PHP programming at work.

Its long been common knowledge that PHP could run under IIS, but not in a way that would make it feasible to run production apps...in other words, if you configure your PHP apps in IIS 6.0/5.0, the result would be a web site that runs very slow, if nothing else, due mainly to the lack of thread safety in PHP apps. Alternately, the app could be configured to run under IIS using CGI, but CGI is a resource hog (one process per request) and results in an app that scale poorly in IIS.