Nuwan Online - Nuwan Weerasinghe’s Web Log

How to exactly diiferentiate between a NULL value and a Zero value in a DataBase table? Good Question ! Think lke this……………

Null is like not having a checking account, and Zero is like having a checking account with a zero ballance. Not much of a difference other than conceptual……………………..

This interestingly came in a Book I’m reading these days.

Technically NULL stands for a Data value which is Unknown in SQL.

Couple of weeks before, we had a serious discussion at the office regarding a client requirement on a very comprehensive Business Intelligence Module for their requested software. I was’nt very clear about the overall gravity (scope) of this requirement and also was not sure How to incoporate BI into a Microsoft Application.

So I did some RAD over the weekend on available/proposed Microsoft support for BI, and Here’s what I found.

First, BI is the ability to INTEGRATE, ANALYSE and REPORT on our existing data.
For the above purposes Microsoft has SQL Server Integration Services, SQL Server Analysis Services and SQL Server Reporting Services. These are allready there in SQL Server 2000 and improved/enhanced in 2005.

1. SQL Server Integration Services (SSIS)
This will collate data from different data sources. In SQL Server 2000, this was essentially Data Transformation Services (DTS). In SQL Server 2005 this is much improved. SSIS will prepare and Integrate data much easily than DTS which is then sent to a merge engine. This inserts data into the Data Warehouse.

2. SQL Server Analysis Services (SSAS)
SSAS will manipulate the information that has been brought together in the Data Warehouse from various data sources. SSAS is the next version of SQL Serevr 2000 Analysis Services and has revolutionized the data cube concept.Microsoft has also added XML for Analysis (XML/A) to improve accessability for clients and applications. Introduction of XML will standardize the interface to allow any application to import data for analysis.

Also a new plug-in interface is available for companies to develope their own Data Mining Algorithms and put them in place with the existing ones. The use of these algorithms are also simplified. The Logic is accessed through SQL querys and results are returned as DataSets.

3. SQL Server Reporting Services (SSRS)
There’s a new report builder tool with new support for XML and Web Services. Within a report, now you can link to web sites and external data. Microsofthas also opened up the interface for charting to allow third party plug -ins for clients.
Also it is noteable that Office 12 which is due to be shipped next year also has extensive integration to BI tools.

Suggested/Further Reading:
1. SQL User Group White Papers: www.sswug.org/whitepapers

2. Delivering Business Intelligence with SQL Server 2005. Publisher- Osborne McGraw Hill

3. SQL MSDN www.msdn.microsoft.com/sql

4. Pro SQL Server 2005 Reporting Services : Publisher- APress

5. SQL Team: www.SQLteam.com

This whole issue of whether to use SP’s or Querries did’nt really occure to me until my recent 70-229 Exam.(SQL Server Development). I did not have a reason to select one over the other since I did not know the benefits,pitfalls of both approachs. But Our Web Application ‘Client’ was totally done using SP’s and the ‘Admin’ was running on Transact SQL Querries.
Once I’ve done 70-229 I had every reason to believe that ‘Admin’ also better be shifted to Stored Procedures due to couple of reasons.

1. Our Application is totally Internet based which means that we have severe limitations when it comes
to guaranteeing a maximum application execution speed. (Network Bandwidth.)
This was a critical issue since even an ADSL connection is rare commodity in Sri-Lanka.

2. Also we have to think in terms of Optimizing Queries once the system is up and running and the Data Base is
fed with high volumes of data. This also could be better handled.(Maintenance cost will be less) with SPs

3. Later if the client wants to restrict certain user activities by introducing extra Views, Invoking/Revoking
permisins on SELECT, UPDATE, DELETE statements, these could be easily handled via Stored
procedures .

So Considering all these we thought that we have a very good case to Make our whole application run on SPs. To further justify my thinking I did some research and found some interesting articles which says exactly why SPs are a better choice over Transact SQL queries.

This I thought would be a good starting point for anyone who is going through the same dilema. This is also another article which explains the same stuff a little more in detail. Anyway google offers a lot on this topic.

This also speaks of taking certifications since this would not have stuck me so powerfully if no for 70-229.

Having said all this I’ll briefly jot down a few Advantages of using SPs Over Transact SQL Querries in your code.

* SP’s greatly reduces Network Traffic by limmiting the Network call to a simple RPC style call rather than
sending a whole Querry Statement from Client to SQL Server.

*Stored procedure execution plans are reused by SQL Serevr rather than parsing-optimizing and Compiling
Queries everytime as and when they are executing.

*Code reusability is high with SPs since your code is separate from your querries.

*Stored procedures do a better job in Encapsulating Logic. You can change and Manipulate SPs as long as the
parameters remain the same and returning result sets are intact.

*Finally Stored procedures helps you to implement a better security model with INVOKING/REVOKING user
rights.