Thursday, December 30, 2004

[Yukon] About large value data types

The maximum capacity for Varchar / Varbinary in SQL Server 7 and 2000 are 8,000 bytes. Similarly for nvarchar it is 4,000 bytes. For any content which is more than 8000 bytes we would go for "Text, NText, or Image" data types.

In SQL Server 2005 it has changed greatly with the introduction of the "Max" specifier. The Max specifier allows storage of up to 2^31 bytes of data. For Unicode content it allows storage of up to 2^30 bytes.

When you use the Varchar(Max) or NVarchar(Max) data type, the data is stored as character strings, whereas for Varbinary(Max) it is stored as bytes.

Basic example showing the usage of this new specifier.

Create table PatientDetails
PatientNumber int Identity,
FirstName varchar(max),
LastName varchar(max),
Memo varchar(max)

In earlier version of SQL Server we cannot use Text, ntext or image data types as variables / parameters in stored procedure or user defined funtions. But that is perfectly valid in Yukon.

Passing character data as parameter to a user defined proceedure and doing string concatenation. This would work perfectly fine in Sql Server 7 and later.

Create proc uspStrConcatenation
@strInputValue varchar(10)
Select 'The string passed as parameter is ' + Rtrim(Ltrim(@strInputValue)) as 'Concatenated Value'

exec uspStrConcatenation 'vel'

Passing Text data as parameter to a user defined proceedure.

This would fail in Sql Server 7 and 2000 as Text / ntext / image etc., data types cannot be used as variables in procedures / functions.

Create proc uspTextConcatenation
@strInputValue Text)
Select 'The string passed as parameter is ' + Rtrim(Ltrim(@strInputValue)) as 'Concatenated Value'

In SQL Server 2005 we can now pass any size string as parameter.

Create function fnStrConcatenation
@strInputValue varchar(max)
returns varchar(max)
return 'The string passed as parameter is ' + Rtrim(Ltrim(@strInputValue))

Saturday, December 25, 2004

Creating reports using Pivot operator

In this article let me try and compare the way to create cross tab reports in SQL Server 2000 and SQL Server 2005.

Cross Tab Report: Representing columns as Rows and Rows as Columns is known as cross tab report or PivotTable.

Sample Table Structure and Data

Create table TestPivot
YearOfSales Int,
SalesQuarter Int,
Amount money

Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 1, 100)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 2, 200)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 3, 300)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 4, 400)

Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 1, 500)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 2, 600)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 3, 700)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 4, 800)

Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 1, 900)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 2, 1000)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 3, 1100)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 4, 1200)

The below query is to create Cross tab report OR PIVOT table in SQL 2000. As expected it would work in SQL 2005 also.

Select YearOfSales,
sum(case SalesQuarter when 1 then Amount else 0 end) as Q1,
sum(case SalesQuarter when 2 then Amount else 0 end) as Q2,
sum(case SalesQuarter when 3 then Amount else 0 end) as Q3,
sum(case SalesQuarter when 4 then Amount else 0 end) as Q4
from TestPivot
group by YearOfSalesgo

The below query is to create Cross tab report OR PIVOT table in SQL 2005

Select * from TestPivotPIVOT (sum(Amount) for SalesQuarter IN ([1], [2], [3], [4])) as Pt

Thursday, December 23, 2004

Database Compatibility ...

Database Compatibility

This article would explain a practical way of using the system stored procedure "sp_dbcmptlevel".

Sp_dbcmptlevel :: Set the database compatibility level.

By default,
for SQL Server 6.5 the comatibility level is 65,
for SQL Server 7.0 the comatibility level is 70,
for SQL Server 2000 the comatibility level is 80

One can check their database compatibility level by executing the sp_dbcmptlevel system stored procedure.

Let me explain the way I made use of this system stored procedure in my previous company. We were having SQL Server 2k in our development environment but for a project our requirement were to use only SQL Server 6.5 (hmm quite old isn't it? when did you last heard about SQL Server 6.5 :) ). Its for sure that we can't purchase 6.5 version for this project alone. At that time sp_dbcmptlevel came to our rescue. We just executed the below query so that our database (get it right, its database level and NOT server level change) compatibility would be set to SQL Server 6.5

-- Replace Pubs with your actual database name
EXEC sp_dbcmptlevel 'pubs', 65

This way if by mistake you use any keywords which are not in version 6.5 it would throw an error. Once the project is done or anytime during the course of the project you can reset the compatibility level back to 80.

For testing purpose

1. EXEC sp_dbcmptlevel 'pubs'
This would give you the current compatibilty information for pubs database

2. EXEC sp_dbcmptlevel 'pubs', 65
This would set the compatibility to SQL Server 6.5 for Pubs database alone.

3. Select top 5* from authors
After executing point 2 if we run the above query it would throw an error because TOP keyword is not available in SQL Server 6.5

4. EXEC sp_dbcmptlevel 'pubs', 80
This would reset the compatibility of pubs database to SQL Server 2000. Now execute the
query mentioned in point 3 to see the top 5 records displayed.

For further reading:

I suggest you have a look at for more details on this topic.

Thursday, December 02, 2004

Row_Number function in Sql Server 2005

In this article let us look into the way of displaying sequential numbers in Yukon (code name of Sql Server 2005).

Method 1 (Sql Server 2000)

Create table TestTable
EmployeeNumber int,
FirstName varchar(50),
Salary money NULL

Insert into TestTable Values (100,'Vadivel',10000)
Insert into TestTable Values (200,'Vadi',20000)
Insert into TestTable Values (300,'vel',30000)

Select Identity(int, 1,1) as 'Serial number', * INTO #TempTable from TestTable
Select * from #TempTabledrop table #TempTable

Method 2 (Sql Server 2005)

Row_Number() :: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Select EmployeeNumber, FirstName, Row_Number() Over (Order By EmployeeNumber) as 'Row Number' From TestTable

Tuesday, November 30, 2004

Creating a very simple WebPart using Whidbey

Web Parts are similar to that of an user controls. But what makes it different is "Customization". Yes webparts allow the users to customize the site by modifying (for ex: moving) controls around the page based on WebPartZones.

"ASP.NET version 2.0 introduces new type of personalization
called Page Personalization or Web Parts"

Any ASP.NET server control can act as a Web Part but by creating a custom control derived from the WebPart class you gain access to advanced features.

I tried out an very very simple web part sample and let me try and explain it here. I didn't even write a single line of code in code behind file for this sample. Mind you for more webparts we need to use code behind files :) Btw I used Visual Studio Beta for this testing purpose.

Steps I followed

1. I expanded the "Tool box" and had a look at the "Webparts" section. As we have "Standard", "Html", "Webcontrols" etc., "Webparts" is one of the new section added in Visual studio 2005

2. There are 13 controls within "Webparts" section in the toolbox. Out of which I used 3 controls and they are:

a. WebPartManager
b. WebPartZone
c. WebPartPageMenu

"WebPartManager" -- is the one which keeps track and coordinate all the Web Part controls on the page. Be advised that it is only a design time control and at runtime it is not visible.

"WebPartZone" -- is the one which marks the area for each webpart.

"WebPartPageMenu" -- is the one we use for designing the page at runtime!! This control is visible at both design and runtime. Read on you would understand better later in the article.

3. That said, I then dragged a "WebPartManager" control to the page.

"Please be advised that we can place only one "WebPartManager" control on a page"

4. I added a table with 2 rows and 2 columns (Layout menu >> Insert >> inserted 2x2 table). Then dragged "WebPartZone" control on all those 4 columns. So they name would read, "WebPartZone1", "WebPartZone2", "WebPartZone3" & "WebPartZone4". Within each "WebPartZone" for our easy understanding there would be a description saying "Click here to add WebParts to the WebPartZone".

5. Then I dragged a label control each into "WebPartZone1" and "WebPartZone2". An interesting stuff happened immediately. In both of this "WebPartZone" a "Minimize" and "Close" hyperlink appeared.

6. First I thought I need to write some code for those links to function properly. But its not like that. When I just ran the page I saw both the links fully functionaly as per their name :) Cool isn't it.

7. Now I dragged a "WebPartPageMenu" control beneath the table (2 rows x 2 cols). Thats all we are all set to test our first webpart sample :)

8. Just build and run the application.

9. Let me explain what I saw in runtime. Two webpartzones with label control in it. Also minimize and close button was there for each zone.Then I saw a drop down menu with a "Change" button. i.e, nothing but the WebPartPageMenu.

10. I tried to move the webpartzone from one place to other but i wasn't. I thought I have made some mistake.

11. After fiddling for sometime I understood that we need to change something in the dropdown for it to work :) The drop down has some menu item by name "Design page layout". Just I changed it and clicked on Change button. Now I am able to design the page at runtime. After moving the webpartzone again change the dropdown item to "Browse this page" I clicked on Change button to see the redesigned page.
"By any chance if we didn't like the newly designed page and if you want to revert to the previous state itself choose "Reset Page Content" and click on Change button."

12. Coolest of all the features are --- I just closed the application and ran it once again from the begining. But to my surprise what ever change I have done previously are persisted as it is.

"Web Parts / Page personalization are based on the personalization system so it gets persisted for each user."
13. If you are interested in knowing more about "WebParts" I suggest you go through this article.

Source code of my aspx page:

Do feel free to contact me @ if you need the source code of the sample webpart which I created.

My other articles on Whidbey

1. Cool features of Whidbey ::
2. Introduction to ASP.NET 2.0 GridView Control ::
3. Cool features of Whidbey -- Part II ::

Friday, October 01, 2004

About TimeStamp datatype of SQL Server ...

SQL Server has a less known datatype called “TimeStamp”. But I wasn't able to find any article about it on the web. So I thought I would try a sample myself and write about it.

TimeStamp is actually used for record versioning in a table. When you Insert (or) Update a record in a table with a TimeStamp field, that field gets updated automatically.

Lets create a sample table to understand the usage of TimeStamp field.

Create table TimeStampExample
RunningNumber int identity,
LastName varchar(30),
tStamp timestamp

The above table “TimeStampExample” has been created with a TimeStamp field (tStamp). By the way its not mandatory to provide a field name for timestamp columns. The below table structure is perfectly valid only.

Create table TimeStampExample
RunningNumber int identity,
LastName varchar(30),
timestamp -- note we haven't mentioned a field name here

When a record is inserted the value of tStamp gets automatically set by SQL Server. Lets see that in action.

Insert TimeStampExample (LastName) values ('RajiniKanth')

After inserting a sample record lets query the table to see the complete record with timestamp value in it.

Select * from TimeStampExample

We need to understand that TimeStamp are just binary values that are incremented with every change in the database and therefore they are unique with the database.

For better understanding lets update the record and check whether tStamp has been updated or not. Make use of the below code snippet for checking that.

Update TimeStampExample Set LastName = 'Shivaji Rao' Where LastName = 'RajiniKanth'

Just query the table now and you could see that the TimeStamp field being updated automatically. Cool isn't it :)

Saturday, September 04, 2004

About TSEqual function (SQL 2K)

One of the common problem the database developers face in their day to day life is record concurrency issues. Lets try to address that with the help of timestamp data type.

Lets assume that Sarah and Ram are reading a same record. First Ram updates that record with some new data. Later if Sarah also updates the record (mind you she is viewing the old content only still) then it would overwrite the changes made by Ram.

There are two ways of solving this issue they are:

1. Pessimistic Locking
2. Optimistic Locking

Pessimistic Locking: First person who reads the record would put a lock on it so that nobody else can change it until he is done with it. Only when he releases the record the other user can make use of it. This method is not recommended because it might also takes hours or days together for the first person to release his lock due to various reasons.

Optimistic Locking: The record would be locked only when a user wants to modify its content.

SQL Server has a TSEqual (I presume it means TimeStamp Equal) function which compares TimeStamp values in the table and the T-SQL statement. If the timestamp values doesn't match it would throw an error and abort the operation. Let see this in action:

Declare @tStampOriginal TimeStamp

--Here tStamp is a TimeStamp column and TimeStampExample is the name of the table
Select @tStampOriginal = tStamp from TimeStampExample

-- Compares current timestamp value with the original value before updating
Update TimeStampExample Set LastName = 'Kapil' Where LastName = 'Tendulkar' and TSEqual (tStamp, @tStampOriginal)

The above batch of code should execute fine without any issues.

Declare @tStampOriginal TimeStamp

--here tStamp is a TimeStamp column and TimeStampExample is the name of the table
Select @tStampOriginal = tStamp from TimeStampExample

--dummy update statement to change the timestamp value
Update TimeStampExample Set LastName = 'Gandhi' Where LastName = 'Gandhi'

-- Compares current timestamp value with the original value before updating
Update TimeStampExample Set LastName = 'Kapil' Where LastName = 'Tendulkar' and TSEqual (tStamp, @tStampOriginal)

This batch would fail because the @tStampOriginal contains the initial timestamp values which has changed during the dummy update statement.

Wednesday, September 01, 2004

Text functions in SQL 2k

As much as possible it is advisable to keep large text in a document on the file system and store a link to that within the database. Why do I say this?

Because for storing large chunk of data we need to depend on the TEXT, NTEXT and IMAGE data types. So What?

There are 2 disadvantages in it. They are:

1. These data types does not support commonly used string functions such as Len, Left, Right etc.,

2. It occupies more / large space in the DB which internally means there might be a performance issues if you store such data in the database.

Inspite of all these if you still want to use TEXT data type due to your business requirment then these functions might interest you!

1. PatIndex()
2. TextPtr()
3. ReadText()
4. TextValid()


This function is useful with TEXT, CHAR and VARCHAR data types. This seeks for the first occurrence of a pattern within a string. If the pattern is found, it returns the character number where the first occurrence of the pattern begins.

For better understanding, the following code snippet makes use of TEXT datatype and PatIndex to search for occurrences of the "dum" pattern:

--Create a sample table with one TEXT datatype field
Create table Sample
LargeTextColumn TEXT

--Insert a sample record
Insert Sample Values ('Enter some dummy content here')

Search for the text of your choice and check the output. Please note that usage of wildcards is the difference betweeen PatIndex and CharIndex. CharIndex doesn't understand wildcards and it takes those wildcards and searches for the exact symbol :)

Select PatIndex('%dum%', LargeTextColumn) from Sample


This function returns the binary pointer to the TEXT, NTEXT, or IMAGE column in a table. Once we get the binary pointer from TEXTPTR(), we can make use of the same in READTEXT, WRITETEXT, and UPDATETEXT statements.

The below code snippet returns the text pointer to the field LargeTextColumn.

SELECT TEXTPTR(LargeTextColumn) FROM Sample


As explained above, ReadText makes use of the text pointer returned by TextPtr function to retrieve Text/ntext/Image values from its corresponding datatypes. If you have a look at the below code snippet you would understand the syntax and usage of ReadText function.

Declare @getPointer varbinary(16)
Select @getPointer = TextPtr(LargeTextColumn) From Sample
ReadText Sample.LargeTextColumn @getPointer 11 5

Similarly WriteText and UpdateText are used for overwritting the existing content and updating the existing content respectively.


This function provides a way to see whether an existing pointer to a column with TEXT, NTEXT, or IMAGE data type is valid. It throws 1 if valid and 0 otherwise.

Declare @getPointer varbinary(16)
Select @getPointer = TextPtr(LargeTextColumn) From Sample
SELECT TEXTVALID('Sample.LargeTextColumn', @getPointer)

Monday, August 16, 2004

Avoid using sp_rename ...

sp_rename is the system stored procedure used to rename user created objects like “Stored procedure“, “Trigger“, “Table“ etc., This SP works pretty fine as long as you don't use it to rename a “Stored proc“, “Trigger“ or a “View“.

Let me try and explain this with an example. Normally once a SP, Trigger or a View is created an entry is made into sysobjects as well as syscomments tables.

For better understanding follow the following steps (examples uses Pubs database).

Step 1:

Description: Sample procedure to demonstrate sp_rename issue
Author: M. Vadivel
Date: August 12, 2004

Create Procedure FetchAuthorsDetails
Select * from Authors

Step 2: [self explanatory]

Description: Query to see the stored proc details in sysobjects
Author: M. Vadivel
Date: August 12, 2004

Select [name] from sysobjects where name like 'FetchAuthors%'

Step 3: [self explanatory]

Description: Query to see the stored proc details in syscomments
Author: M. Vadivel
Date: August 12, 2004

Select [text] from syscomments where [text] like '%FetchAuthors%'

Step 4:

Now let us try to rename this stored proc using sp_rename.

Description: Query to rename the stored procedure into a new name
Author: M. Vadivel
Date: August 12, 2004

sp_rename 'FetchAuthorsDetails','FetchAuthorsDetailsRenamed'

After this if you execute the queries mentioned in Step 2 and 3 you could find that in syscomments table still the old name is retained!!

In order to double check it, go to SQL Enterprise Manager and navigate to the Pubs database. If you check the SP name it would be the new name only. But just double click that SP and see the code within it. It would be refering to the old sp name i.e., 'FetchAuthorsDetails'.

So in order to avoid this confusion its always better to generate the script of the SP, trigger or view which you would like to rename. Then delete it and recreate it with a new name (using the script generated).

Wednesday, June 23, 2004

Delete Vs Truncate Statement

• Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.

• Truncate table also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Truncate table can be rolled back if it happens within a Transaction.

• Truncate table is functionally identical to delete statement with no “where clause” both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete.

• Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is.

• In truncate table the counter used by an identity column for new rows is reset to the seed for the column.

• If you want to retain the identity counter, use delete statement instead.

• If you want to remove table definition and its data, use the drop table statement.

• You cannot use truncate table on a table referenced by a foreign key constraint; instead, use delete statement without a where clause. Because truncate table is not logged, it cannot activate a trigger.

• Truncate table may not be used on tables participating in an indexed view.

Friday, June 11, 2004

Comparing tables ...

There are times when we would like to check whether the content in two tables are same or not. As of now there isn’t any built-in function in SQL Server to do the same (who knows they might come up with something in Yukon!). At present we need to manually compare the contents of tables to find out whether they are matching or not. Won’t it be nice to have a stored procedure which would do the job for us? Read on …

Setting the environment

First let us create a test table and populate it with some test data.

Create table Student
[name] varchar(50),
[age] int

Insert into Student ([name],age) Values ('Vadivel',27)
Insert into Student ([name],age) Values ('Ash',30)

Let us now create a copy of this table with a new name:

Select * into StudentCopy from Student

Now both the table ‘Student’ and ‘StudentCopy’ has the same structure and values.


The stored procedure helps in comparing two tables:

Create Procedure usp_CompareTable
@FirstTableName varchar(128),
@SecondTableName varchar(128)

-- Variables to hold the dynamic query
Declare @TotalRowsFirstTable nchar(100),
@TotalRowsSecondTable nchar(100),
@TotalRowsUnion nchar(100)

-- Variables to hold the row count of different tables
Declare @RowCountFirstTable int,
@RowCountSecondTable int,
@RowCountUnion int

-- Variable which would hold the result of the comparison. 0 = Mismatch, 1 = Match
Declare @result bit
SET @TotalRowsFirstTable = 'Select @RowCountFirstTable=count(*) From ' + @FirstTableName

@TotalRowsFirstTable OUTPUT,
N'@RowCountFirstTable Int OUTPUT',
@RowCountFirstTable = @RowCountFirstTable OUTPUT

SET @TotalRowsSecondTable = 'Select @RowCountSecondTable=count(*) From ' + @SecondTableName

@TotalRowsSecondTable OUTPUT,
N'@RowCountSecondTable Int OUTPUT',
@RowCountSecondTable=@RowCountSecondTable OUTPUT

SET @TotalRowsUnion = 'Select @RowCountUnion=count(*) From (Select * From ' + @FirstTableName + ' Union Select * From ' + @SecondTableName + ') as AliasSecond '

@TotalRowsUnion OUTPUT,
N'@RowCountUnion INT OUTPUT',
@RowCountUnion=@RowCountUnion OUTPUT

If (@RowCountFirstTable = @RowCountSecondTable) and (@RowCountSecondTable = @RowCountUnion)

Set @result = 1
Print 'Success: Tables are matching'
Set @result = 0
Print 'Error: Tables are not matching'

To Test

Execute usp_CompareTable 'student', 'studentcopy'

Monday, June 07, 2004

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are:

1) xp_regwrite
2) xp_regread
3) xp_regdeletekey
4) xp_regdeletevalue

Let us see each one of them in detail!

About xp_regwrite

This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key.


We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD. The last parameter is the @value parameter, which assigns a value to the data item.

Let us now see an example which would add a new key called "TestKey", and a new data item under it called TestKeyValue:

Use Master

@type='REG_SZ', @value='Test'

About xp_regread

This extended stored procedure reads an individual registry key (OR) to read a data item in the key. The @rootkey parameter is the root key in the registry, and the @key parameter is the individual key.

Usage: We must specify the registry key’s root and key as shown below:

Use Master

As we are using @rootkey and @key alone it would test the existence of the key only. i.e., if the key exist it would return 1, if not it would return 0. To fetch the value stored inside the key (which we did it using xp_regwrite) use the below code snippet:

Use Master

Exec xp_regread

Assume that you have installed SQL Server in your machine and unfortunately you lost the CD Key. Won't it be useful if there is a means by which you could retrieve the CD Key used while installing? Obviously Yes right :) Execute the below script to fetch the CD KEY.

USE master

EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server\80\registration',
@value_name = 'CD_KEY'

About xp_regdeletekey

As the name say, this procedure allows us to delete a key from your registry. For your information this won't warn us before the procedure deletes the entire key.

Usage: Use the @rootkey parameter to specify the root registry key and @key parameter to tell which key you want to delete:

Use Master


About xp_regdeletevalue

You can also delete individual data items inside a registry key by using the xp_regdeletevalue procedure. The procedure uses the same parameters as the xp_regdeletekey procedure, but has an added @value_name parameter, which is the data item name:

Use Master


Points to Note:

1. You can replace the first 3 lines in the code snippet with master..

2. After running the scripts for testing go to START >> RUN >> And type regedit. This would open up the registry editor. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE to find the rest we discussed above.

3. Once done run the below scripts to clear the entries we added to the registry

---Run this to Delete "TestKey" entry

---Run this to Delete "thinkingMS" entry

---Run this to Delete "RegistryExample" entry

---Run this to Delete "Vadivel" entry

Thursday, May 27, 2004

Sunday, May 23, 2004

UDF's in Constraints ...

In this post I have explained the way to use UDF (User Defined Functions) in constraints. For the purpose of discussion I have provided the structure of 2 (self explanatory) tables "MasterTable" and "ChildTable". Sample data for MasterTable have also been provided below.

--Table structure of MasterTable:
Create Table MasterTable
ItemID int Identity Primary Key,
ItemName Varchar(50),
Status bit

--Sample data for the table MasterTable:
Insert Into MasterTable (ItemName, Status) Values ('Rice',1)
Insert Into MasterTable (ItemName, Status) Values ('Wheat',1)
Insert Into MasterTable (ItemName, Status) Values ('Corn flakes',0)

--Table structure of ChildTable:
Create Table ChildTable
ItemID int Foreign Key References MasterTable(ItemID),
Quantity int

Let us now create an user defined function to check whether the Item exist and its status is 1. According to this sample Status 1 means the record is enabled if it is 0 it is disabled.

Create Function CheckItemID
@ItemValue int
Returns Integer
Select Count(*)
ItemID = @ItemValue and
Status = 1

Add a constraint that calls the User defined function "CheckItemID" for validating the ItemID before entering the record into ChildTable.

Alter Table ChildTable
Constraint CK_ChildTable_CheckItemID Check ( dbo.CheckItemID( ItemID ) = 1 )

Let us now test the above code snippet

--Insert valid ItemID whose status is 1
Insert into ChildTable (ItemID, Quantity) Values (1,10)

--Insert valid ItemID whose status is 0
Insert into ChildTable (ItemID, Quantity) Values (3,10)
-- This record would not insert and it would throw an error.

--Insert an ItemID which is not there in MasterTable
Insert into ChildTable (ItemID, Quantity) Values (25,10)

Hope this code snippet helps you understand UDF usage within Constraints!!

Saturday, May 01, 2004

Got a GMail account today ...

Yesterday I created a GMail a/c ( for myself. Thought would inform you all about that.

If you also have an active blogger a/c ( you could see the invite for creating a Gmail account immediately after logging into the site (

If at all you don't have an blogger account check the heading "Interested in an account" here. I guess that would help you to get a GMail id at some later date!!

I was actually wondering if Google gives 1 GB of space to each user how big their servers needs to be?!??! Won't they run into storage problem within few months after they release it for public?? At that time I got to see the post from my good old friend Pandurang Nayak which had some interesting facts about GMail !!

Friday, April 30, 2004

Copying database diagrams ...

The Database diagram tools in the SQL Server enable administrators and developers to create Database diagrams very easily. This article demonstrates an efficient way to copy diagrams from one Database to another.

Monday, March 15, 2004

Won an article contest !!

One of my article has won last months (Feb 2004) article contest at As of now my name and the article title is displayed in the site. They are yet to point the title to my article. Hopefully they would do at least before end of March :) For further details check out

Tuesday, March 09, 2004

Flash work ...

Check out .... offlate this is one of the best flash work I have seen. (Link to the main project is there at the top left.)

Tuesday, February 24, 2004

Using accesskeys for previous/next

I happened to read wherein it was said that accesskeys for previous or next is as follows:

1. Mozilla/Netscape: Ctrl+Alt+P or Ctrl+Alt+N
2. IE: Alt+P or Alt+N, then press RETURN

But I always prefer using, ALT + right arrow for moving to the next screen and ALT + left arrow for moving to the previous screen in IE

Friday, February 13, 2004


Without LOVE - days are



Monday, February 09, 2004

Posting a message ...

Are you a member of any online community?
Do you post messages / queries in forums, usergroups etc., ?

Then I suggest you to have a look @ "Making good NewsGroup Posts"

Saturday, February 07, 2004

myDoom could not dislodge Microsoft site :)

Generally people always used to complain about weak security of MS products and servers ... this post is for all those people :)

MyDoom could make SCO's web-site go offline but could not harm MS web-site. For more information on this check out this article.

Wednesday, February 04, 2004

Closing the parent window ...

Many times I have been asked how to close a parent window without getting that ugly warning dialogbox. You know what the solution is pretty straight forward :)

Script of the Parent window:

  <Script language=""javaScript"">

Here 'Test' is a name of the window. You can give any name of your choice there. 'test.htm' is the name of the file which would be opened in fullscreen. This way we could avoid that ugly warning dialogbox.

Tuesday, February 03, 2004

Credit ...

Sometime back I happened to check the George Hernandez's site In one of his page I found an error in his code snippet. I sent him a note on the same with a code snippet which could be used instead.

George has reviewed it and has responed back:


Hey thanks a lot for the correction. I updated my page with your
suggestion and credited you for it.


Wednesday, January 28, 2004

Another article ...

Another article of mine have been published now in MSDN. Its regarding database documentation. Do check my article and let me know your feedback.

Microsoft Consumer Virus Alert:

Why We Are Issuing This Alert

W32/Mydoom@MM spreads through e-mail. This worm can disguise the sender's address, a tactic known as spoofing, and may generate e-mails that appear to have been sent by Microsoft. Many of the addresses Mydoom uses are valid addresses that are being spoofed for malicious purposes.

Mydoom Virus Alert: What to Do

Treat all e-mail attachments with caution, particularly .zip files in the case of this virus, even if they appear to be from a trusted source. Learn what to do about virus infections.

Complete Information:

New Virus ...

This is an Informational update about the mass-mailing computer virus, W32.novarg.A@mm:

The virus can infect several files on your computer and spread itself by sending an email to all addresses in your address book.

E-mails with the following subject line and characteristics can contain the virus:

Subject: Random - "Test" or "Hi" or "Hello"
Attachment: Files with the extensions of .exe, .pif, .scr, and .zip.

If you receive an e-mail with one of the subject lines and attachments listed above YOU SHOULD DELETE the e-mail immediately.

Thursday, January 22, 2004

Good link ...

The easiest way to see all classes inside a namespace is to check this Class Library Comparison Tool. The interesting part is, this page contains a "Display options" go and explore that for yourself :)

Wednesday, January 21, 2004

Security Bulletins released last week ..

Important information:

On Jan 13th, Microsoft have released 3 security bulletins. Of these one that deals with ISA is a serious one (critical). If you are running ISA as a firewall in your organizations, you should consider patching it. Here are all of them for your reference.

a.) Vulnerability in Microsoft Internet Security and Acceleration Server 2000 H.323 Filter Could Allow Remote Code Execution (816458):

b.) Vulnerability in Exchange Server 2003 Could Lead to Privilege Escalation (832759):

c.) Buffer Overrun in MDAC Function Could Allow Code Execution (832483):

Do you know this?

We can get complete information about each table in a database using the following syntax:

Execute sp_help <<tablename>>

But if we have hundreds of tables in a database then the above method would surely be a tedious way to do. So to over come that we could use the following query:

Execute sp_MSForeachTable @command1 = "sp_help '?'"

As sp_help lists information about a single table this sp_MSForeachTable displays all information about all tables within the existing database. Neat isn’t it?
Dear Reader,

Bitwise, the annual online programming contest organized by the Computer Science & Engg. Department Society, Indian Institute of Technology Kharagpur, India, is being conducted this year on Sunday, the 8th of February. Bitwise is an algorithm intensive programming contest which aims to provide programmers across the globe, a platform to compete, testing their ability to develop efficient algorithms to problems within a given time constraint.

It is a non-profit competition organized by 4th year undergraduate students of CSE, IIT Kharagpur.

Over the last three years, the contest has become extremely popular around the world. In Bitwise 2K3, over 900 teams participated, with a team from Singapore bagging the first prize.

The registration is free and open to all. Contestants solve a set of problems posted on a site using C or C++. The solutions are evaluated, not only on the basis of correctness, but also on execution time and space complexity. The top 50 contesting teams will receive prizes worth Rs. 60,000!!


For registration and more information please visit our website at:

If you know of anyone who would be interested in taking part in Bitwise 2K4, kindly forward this message to them.


Bitwise Organizing Committee
CSE, IIT Kharagpur

Tuesday, January 20, 2004

How to detect a 2-way mirror?

Not to scare you, but to make sure that you are aware. Many of the hotels and textile showrooms cheat the costumers & watch them privately :(


When we visit toilets, bathrooms, hotel rooms, changing rooms, etc., how many of you know for sure that the seemingly ordinary mirror hanging on the wall is a real mirror, or actually a 2-way mirror i.e., they can see you, but you can't see them). There have been many cases of people installing 2-way mirrors in female changing rooms or bathroom or bedrooms. It is very difficult to positively identify the surface by just looking at it. So, how do we determine with any amount of certainty what type of mirror we are looking at?


Place the tip of your fingernail against the reflective surface and if there is a GAP between your fingernail and the image of the nail, then it is a GENUINE mirror.

However, if your fingernail DIRECTLY TOUCHES the image of your nail, then BEWARE, IT IS A 2-WAY MIRROR!(there is someone seeing you from the other side). So remember, every time you see a mirror, do the "fingernail test."

It doesn't cost you anything. It is simple to do.

The reason there is a gap on a real mirror, is because the silver is on the back of the mirror UNDER the glass. Whereas with a two-way mirror, the silver is on the surface. Keep it in mind! Make sure and check every time you enter in hotel rooms. May be someone is making a film on you.

Ladies: Share this with your friends.
Men: Share this with your sisters, wife, daughters, friends, colleagues

Wednesday, January 14, 2004

Wanna move from PHP/JSP to ASP.NET?

As you know Microsoft has been developing a new tool to help developers move to ASP.NET called the ASP to ASP.NET Migration Assistant. You probably played around with it earlier on. On 9th Jan 2004 they have announced the Beta release of this tool.

The ASP to ASP.NET Migration Assistant is designed to help you convert ASP pages and applications to ASP.NET. It does not make the conversion process completely automatic, but it will speed up your project by automating some of the steps required for migration.

Here’s how to try the ASP to ASP.NET Migration Assistant Beta:

1) Visit to learn more and download the tool.
2) Visit to post your questions and feedback.

BTW – there is a PHP to ASP.NET and JSP to ASP.NET migration wizard too.

Source: Deepak Gulati ...

Tutorial and Presentation Creation Software...

Wink is a Tutorial and Presentation creation software, primarily aimed at creating tutorials on how to use software (like a tutor for MS-Word/Excel etc). Using Wink you can capture screenshots of your software, use images that you already have, type-in explanations for each step, create a navigation sequence complete with buttons, delays, titles etc and create a highly effective tutorial for your users.

Using Wink, you can create tutorials, by capturing screenshots, mouse movements and specifying your own explanations with them. And all this in a standard Windows-based UI with drag-and-drop editing makes it very easy to create high quality tutorials/documentation.

It is estimated that Macromedia Flash Player is installed in more than 90% of the PCs. Using Wink you can now create high quality tutorials/documentation which will be viewable across the web in all these users' desktops.

Similar applications sell for hundreds of dollars, while Wink is free with unrivaled features.

Download it free from here.

Saturday, January 10, 2004

Good book for beginners ....

OOP with Microsoft Visual Basic .NET and Microsoft Visual C# Step by Step by Robin A. Reynolds-Haertle. This Microsoft Press book is a good one for beginners.

Thursday, January 08, 2004

(Re)Initializing an identity column to 0

Reinitializing an identity column is of two folds. If there is no foregin key on the table where we want to reset the identity to 0 then the following process would work.

create table testTable1
  sno int identity,
  lastname varchar(25)

insert into testTable1 values('lastname1')

truncate table testTable1 -- This statement alone would do the trick for us :)

If there is a foreign key relationship then we can't use the above statement instead follow the steps explained below:

create table testTable1 -- example table 1
 sno int identity primary key,
 lastname varchar(25)

create table testTable2 -- example table 2
  sno int references testTable1(sno),
  email varchar(50)

insert into testTable1 values('lastname1')
insert into testTable2 values(1,'')

delete testTable2 -- remove all the records from the child table first
delete testTable1-- remove all the records from the parent table

After deleting the content in the table(s) you need to give the execute below statement.

dbcc checkident(testTable1,Reseed,0)

There are cases where we want to reinitialize ALL identity columns in a database. In such cases make use of the below script.

  'dbcc checkident (' + + ', Reseed, 0)' as 'Reset Identity for the whole database'
  sysobjects, syscolumns
Where = and
  syscolumns.colstat & 1 <> 0 and
  sysobjects.xtype = 'U' and <> N'dtproperties'

The result of the above query would be 'n' number of sql statements. Just copy those statements and execute it to reinitialize ALL identity columns in a database.

Wednesday, January 07, 2004

Test Drivers ...

Sample program which we write to test the newly written CLASS is called as drivers. It's always good to test your class with a driver before adding it into the actual program.

Find the product of a field ...

There isn't a prebuild function for finding the product of a field (or) set of values in SQL Server. But there is a workaround by using a combination of SUM(), POWER() and LOG10() functions. To read my complete TIP on this visit techtarget.

Needless to say, if you like this tip do rate it so that I could be in contention for winning the best tip of the month award :)

Comments ...

Today, I have made use of Haloscan a free, easy to use commenting system for my Blog. I think this would fecilitate the readers to add their comments to each post.

Protection bypass vulnerability in Ms-Word ...

Microsoft Word 2003 and 2002 contains a protection-bypass vulnerability. By performing a simple process outlined below, a user can unprotect a protected document without the use of a password cracker or other special tools. :(

This bug was discovered by Thorsten Delbrouck.

To see the bug for yourself follow the below demonstartion!!

1.) Open a protected document in Word.
2.) Choose the Save As Web Page (*.htm; *.html) option and close Word.
3.) Open the HTML document in any text editor.

4.) Search the <w:UnprotectPassword> tag for a line that looks like: <w:UnprotectPassword>ABCDEF01</w:UnprotectPassword>. Gather the password.

5.) Open the original .doc document with any hex editor.
6.) Search for hex values of the password (reverse order).
7.) Overwrite all four double-bytes with 0x00. Save, and close.

8.) Open the document in Word. Select Tools, Unprotect Document. Password is blank.


Tuesday, January 06, 2004

Want to know the Processor ID of a machine?

Use the below code snippet in your page to display the Processor ID of the machine. Don't forget to add "using System.Management;" at the top.

ManagementClass managementClass = new ManagementClass("Win32_Processor");
ManagementObjectCollection managementObj = managementClass.GetInstances();

foreach(ManagementObject mo in managementObj)
 strResult ="Processor " + mo["ProcessorId"].ToString();

Surviving a heart attack

Don’t know about the authenticity of this information, but better to leave no stone unturned!!

Let's say it's 6.15p m and you're driving home (alone of course), after an unusually hard day on the job. You're really tired, upset and frustrated. Suddenly you start experiencing severe pain in your chest that starts to radiate out into your arm and up into your jaw. You are only about five miles from the hospital nearest your home. Unfortunately you don't know if you’ll be able to make it that far. You have been trained in CPR, but the guy that taught the course did not tell you how to perform it on yourself.


Since many people are alone when they suffer a heart attack, without help, the person whose heart is beating improperly and who begins to feel faint, has only about 10 seconds left before losing consciousness. However, these victims can help themselves by coughing repeatedly and very vigorously. A deep breath should be taken before each cough, and the cough must be deep and prolonged, as when producing sputum from deep inside the chest. A breath and a cough must be repeated about every two seconds without let-up until help arrives, or until the heart is felt to be beating normally again. Deep breaths get oxygen into the lungs and coughing movements squeeze the heart and keep the blood circulating. The squeezing pressure on the heart also helps it regain normal rhythm. In this way, heart
attack victims make it to the nearest hospital. Tell as many other people as possible about this. It could save their lives!!

Sunday, January 04, 2004

New Virus ...

Two new viruses have been detected. One which is not so destructive is Jitux.A virus and the other one which is more troublesome is PE_QUIS.A worm. Check out the full artilce here.

Friday, January 02, 2004


For inserting a unique value for each record in a table, we can either create a GUID (Globally Unique IDentifier) with the help of the function NEWID() (or) create an IDENTITY column which would take care of inserting the unique value itself.

The major advantage of using GUIDs is that they are perfectly unique :) We can blindly use this if we are planning to consolidate records from different SQL Servers into a single table. The disadvantage is that the value of GUID are quite BIG. Hope it won't be too much if I say that this is "one" of the biggest datatypes in SQL Server. Due to that if we create an Indexes on a GUID field it would have a considerable performance hit. Other disadvantage which I see is it is not that easy to understand / remember a GUID value.

On the flip side, we can use IDENTITY column which would most probably be an Integer field. Due to the less space it occupy it has an upper hand when we compare this with GUID with respect to performance. However, since it is server-generated, an insert into the database requires that we query the database for the new key value. This also complicates the process of submitting new parent and child data.

Also, there is no standard for auto-increment functionality across databases. Relying on GUIDs can simplify some of these issues, since you're generating your key values at the client. If at all you are planning to port your SQL Server database to some other database then you might run into problems!! Not all databases natively support GUID. Even in those cases the work around is to cast the GUIDs as strings :)

So in my opinion we need to decide whether we can use GUID or IDENTITY column on a case by case basis.

Fetching IDENTITY value ...

The question is "How to fetch the IDENTITY value of the record which we INSERTED now?". Till the days of SQL Server 7.0 we used to rely on @@IDENTITY function. But in my experience that function isn't that reliable. i.e., @@IDENTITY isn't dependent on the current scope. Even if we have inserted some records in a different table it would fetch us that identity value ;) I am sure we wouldn't be interested in that.

From SQL 2K there is a new function by name SCOPE_IDENTITY which returns the last IDENTITY value produced on a connection and by a statement in the same scope. So its better to use SCOPE_IDENTITY in our select statement to retrieve the identity value for the record which we inserted now.


Passing data between pages ...

Some of the common methods of passing data between pages (web forms) are:

1. Query String
2. Session Variables
3. Server.Transfer

Querystring and Session variables has its own limitations. ( For example, you can't pass objects or sensitive data using Query string method. More over there are limitations on number of characters which can be passed on a URL etc.,). So Server.Transfer is the better method for passing data across pages.

Note: If you use Server.Transfer inside a Try..Catch Block it would throw an ThreadAbortException. Its not a bug that's how they have designed it to clean up the Stack :) In order to overcome that either opt for Server.Execute or Response.Redirect('page.aspx', false).