SPARSE COLUMN and COLUMN SET

Another new feature coming up with SQL Server 2008 is SPARSE column. It provides optimal storage by reducing the storage for nullable columns. But there are also downside to it as making a column as SPARSE would put some overhead when a non-null value is read for the column. The group of columns can be setup by making a COLUMN SET while creating a table. The column set would allow all the sparse columns to be updated like a single XML column.

Intellisence support in SQL Server 2008

For those of you who are bored with SQL Server management studio of SQL Server 2005. There is a good news that the same tool has intellisence support in the 2008 version.

Congratulations!!

But this comes at some cost. If you open the profiler then you would see these statements being executed.

I just executed the statement SELECT * FROM SYS.DATABASES. But this came as a price of execution of one more statement to the server. This is not unique to the SQL SERVER 2008. All those software which support intellisence services has to query the database to get the information about the object.

So if you have many database developers this might come as a cost to the Server instance. But this would also help you reduce the cost as the database developers become more productive with the support of intellisence.

I support the inclusion of this feature and admire it.

Batch Update ADO .net OPENXML

I wanted to use the Batch Update in one of my project and I explored the following information. This might be of interest to some of you.

I thought that using the Batch update option in .net 2.0 command, I can go with this. This is achieved using the rows in a dataset which are updated using a DataAdapter object. In reality one has to set the UpdateBatch size property to a specific value. But this is not the same. When i started the profiler, the trace showed that each statements are executed individually instead a single Update / Insert statement. When i studied the documentation, it became clear that Microsoft never claimed that a single statement would be executed. It says that a batch of statements are passed to SQL Server instead of individual statement. You can study the following article for further details:

http://msdn.microsoft.com/en-us/library/kbbwt18a(VS.80).aspx

But those who are like me and want that a single Update/ Insert statement is executed instead, have an alternate option. This is through the use of OPENXML feature of SQL Server. All the rows are provided to a stored procedure as a single parameter of XML. The parameter datatype is generally set as NText. For further details, please read the following:

http://en.csharp-online.net/XML_and_ADO.NET%E2%80%94OPENXML

After using this option, when you start your profiler trace, you should be satisfied that there is only a single Update statement being executed.

Are you happy now??

Functional Programming DataTypes F# .net

Functional Programming uses immutable data types. Those who are aware of the difference between String and StringBuilder in .net understand that the first is an immutable datatype but the other is mutable datatype. For those of you who are not familiar with the concept, immutable objects are not allowed to be updated. The updates that you make in the String are basically not the same object but each updated creates a new object and the earlier is presented to be collected and disposed by the runtime.

For compliance with .net, F# supports .net datatypes. But it considers them as mutable datatypes. It also introduces new datatypes, which are immutable ones. So with this we know that there are two categories of datatypes in F#, one is mutable and the other is immutable datatypes.

The immutable datatypes introduced by the language include tuples, records, discriminated unions and lists. A tuple is a collection which can hold more than one values. The number of values should be known at design time. Record is a specialized tuple in which each value may be named e.g. Age = 12. The list is a regular linked list. The discriminated union is like c style unions but it is typesafe. Like C, it defines a type whose instance may hold a value of any of the specified datatypes.

Though F# is a strongly typed language which used type inference. It deduces these datatypes during compilation.

Replication in SQL Server 2005

Replication is a way for geographically distributing your data. This provides a way to copy and distribute data and objects from one database to others and keeping them synchronized.

The benefits are increased availibility, load balancing and supporting remote clients.

The tools provided in SQL Server 2005 to support replication are as follows:
1- SQL Server Management Studio (SSMS)
2- SQL Server Replication Monitor (SSRM)
3- T-SQL replication stored procedures and RMO(s) Replication Management Objects.

There are three types of replication in SQL Server 2005.
1- Snapshot replication
2- Transactional replication
3- Merge Replication

There are different terminlogies which are necessary to discuss:
1- Article: This is the most basic unit of replication. It can be a table, view, etc.
2- Publication: This is a group of articles from the same database.

There are three roles which a server can play for replication. They are:
1- Publisher
2- Distributor
3- Subscriber

With SQL Server 2005, Microsoft did many changes to its replication technology.
like MOdifications in Replication Security agent. So if you have your replication scripts in SQL Server 2000 and you want to install SQL Server 2005 in your environment, update security settings.

SSIS Utilities (DTSWizard, DTUtil)

Today I stumbled across two very good utilities shipped with SQL Server
2005. They are both SSIS based.

1. DTSWizard:
This is a wizard based import export utility. This creates a SSIS package,
executes it, copy it to file system or to database instance. This SSIS
package is for import / export data from many sources to SQL Server. I used
it to create a package to export data to excel file, which is successfully
did. I loved this utility.

You can start this utility by just typing DTSWIZARD in the command prompt.



2. DTUTIL:
This is also related to SSIS. This is about copying / moving SSIS packages
from / to SQL Server.

You can start this utility by typing DTUTIL from SQL Server. This is not
wizard based but it uses or creates SSIS package.