Friday, June 29, 2007

A UDF to convert the seconds into MM:SS

A UDF to convert the seconds into MM:SS Format:

Here is the Code:

CREATE FUNCTION [dbo].[fn_SEC2MIN]( @Sec as int )


IF ( @SEC >= 60 )
SET @i_MM = FLOOR(@Sec / 60 )
SET @i_SS = @Sec % 60
SET @i_SS = @Sec
SET @return =right('00' + cast( @i_MM AS Varchar), 2 )+':'+ right('00' + cast( @i_SS AS Varchar), 2 )

RETURN @return

Here is the test query for the UDF:

print dbo.fn_SEC2MIN ( 120)

Phone Number Formatting UDF

The following UDF will give you the Formatted Phone Number:

CREATE FUNCTION [dbo].[fn_PhoneNoFormat]( @Phone as char(10) )

Return '('+substring(@phone,1,3)+') '+substring(@phone,4,3)+'-'+substring(@phone,7,4)


Here is the Test Query:

print dbo.fn_PhoneNoFormat( '1234567890')

UDF to Get the DD Part of the last Date of Month

-- =============================================
A UDF to get the DD part of the last date of the month according to the date passed into its parameter
-- =============================================
CREATE FUNCTION [dbo].[fn_DD_OF_Last_Date_OF_Month]( @myDate as datetime)

SET @return =0
Declare @myDate2 datetime

SET @mydate2=dateadd(m,1,@myDate)-day(dateadd(m,1,@myDate))

SET @return= cast (datepart(dd,@myDate2) as int)

RETURN @return

The following query is to test the UDF:

select [dbo].[fn_DD_OF_Last_Date_OF_Month]( '03/13/2005')

Thursday, June 21, 2007



Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is functionally the same as the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

       Advantages of TRUNCATE TABLE :

  1.              Less transaction log space is used.The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
  2.              Fewer locks are typically used. When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
  3.             Without exception, zero pages are left in the table.  After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

           Restrictions of TRUNCATE TABLE:

  1. TRUNCATE statements do not fire triggers.
  2. Records removed by the TRUNCATE TABLE statement cannot be restored.
  3. You can not specify a WHERE clause to narrow down the rows.
  4. you can not  retain the identity counter.
  5. You cannot specify a WHERE clause in a TRUNCATE TABLE statement. It is all or nothing.
  6. You can not usee TRUNCATE TABLE when:

  •                        Are referenced by a FOREIGN KEY constraint.
  •                       Participate in an indexed view.
  •                       Are published by using transactional replication or merge replication.


  • DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as            maintaining log sequence number (LSN) information.  
  • You can also specify a WHERE clause to narrow down the rows to be deleted.
  • When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the            empty pages requiring manual release using DBCC SHRINKDATABASE (db_name). 
  •             If you need to retain the IDENTITY counter then se the DELETE statement.
  • When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following         statements can be used to achieve the same result as TRUNCATE TABLE:
DELETE from "table_name" 
DBCC CHECKIDENT("table_name", RESEED, "reseed_value")

 To remove the table definition in addition to its data, use the DROP TABLE statement.
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain.

Finding the Missing GAPS with in a Table

Create table tb_GAPS
Sno int not null
--// Populate dummy records in the table:

Insert tb_GAPS values (15)
Insert tb_GAPS values (10)
Insert tb_GAPS values (3)
Insert tb_GAPS values (5)
Insert tb_GAPS values (9)
Insert tb_GAPS values (11)

Declare @MAX as int
Declare @Start as int
Declare @SRN as int

SET @Start =1

select @MAX=max(Sno) from tb_GAPS
--select @Start=min(Sno) from tb_GAPS


WHILE (@Start < = @MAX )
select @SRN=sno from tb_GAPS where sno = @Start
IF (@SRN = 0)
INSERT into @m_TempTable values(@Start)
SET @Start = @Start+1

select * from @m_TempTable

Wednesday, June 20, 2007

SQL Server @ Derived Tables

Derived Tables:

The derived tables are just like the temporary tables in SQL Server,
these are created on the fly by using the SELECT statement and
these reflect similar as of the temporary tables or views.

A derived table is created in the memory on the fly and only the
outer SELECT query can have its reference.

The syntax us like :

SELECT * FROM ( SELECT * FROM table_name) AS alias

NOTE: The alias at the end is part of the syntax and it is necessary.
Otherwise you will face an syntax Error.

The concept of Derived Tables was introduced in the SQL Server 2000,
Now in SQL server 2005 the similar and little advanced concept is
introduced with the name of COMMON TABLE EXPRESSIONS for
RECURSIVE QUERIES. Hopefully I will post on this concept sooner.

if else Construct VS Switch Construct

Both the IF/IF ELSE  and switch statements are used to execute the pecific block of code depending upon the true condition. But both of them have their own pros & cons. Here are some I noticed.

  • when you have more than two conditional expressions based on a single variable of numeric type/Character Type.
  • because it makes the code more structured, systematic and easy to understand.
  • In multiple if statements the conditions are to checked as many times the if statements are written where as in switch conditiion the condition is checked only once and jumps to required block.

  • When you need to use the logical operators in CONDITION EXPRESSION, like you need to use && and || operators. Means your condition expression is not based on single variable.
  • Its not possible to use switch when our cases are floating points or strings.
  1. In if-else construct: An expression is evaluated and the code is selected based on the truth value of the expression while in switch construct: An expression is evaluated and the code is selected based on the value of the expression.
  2. Each if has its own logical expression to be evaluated as true or false; while each case is referring back to the original value of the expression in the switch statement.
  3. The variables in the expression may evaluate to a value of any type, either an int or a char or an Object; while in switch construct the expression must evaluate to an int/char.
  4. There is one subtle but very important difference between the if-else-if ladder and the switch statement. That difference involves the break statement. In the if-else-if ladder, no matter what, only one of the blocks of code is executed. In the switch construct, if the break statement is omitted, the flow of execution will go forward into the next block. Leaving out a break is sometimes useful, but pretty rare. If you are missing a break statement in any block, make sure you intended to leave it out.

C++ Pointers VS JAVA Refrences

Here are some of the differences i found while googling.
  • Java references are closer to C++ pointers than to C++ references. Adifference between Java references and C++ pointers is that you can perform arithmetric operations on C++ pointers while you can't do arithmetic operations on Java references.
    In C/C++ e.g.:
int* a = new int[10];
int* b = a + 1;
int* c = b - 1;

*b = 2; // Modifies a[1]
*c = 1; // Modifies a[0]
  • A pointer is an object containing the address in memory of another Object while reference is an alias for another object.
  • A pointer can be re-seated (i.e., pointed at a different object); a
    reference cannot.
  • A reference must be initialized when it is created; a pointer can be uninitialized.
  • References "refer" to some object, and are "bound" to an object during creation, and are used as aliases to that object. Because references are aliases, the "contents" of a reference is actually the contents of the object they are bound to. Pointers "point" to an object. The "contents" of a pointer is the address of an Object. Pointers are not "bound" to any object; rather, they simply "point" to an object. The value of a pointer can be changed during its lifetime, causing it to "point" to different objects at different times. References are "bound" to an object, and it cannot be rebound during its lifetime.
  • "use references when you can, use pointers when you must."

Exploring the SpVoice Class of MS SAPI 5.1 to use different available features for TTS

This is a simple example that will elaborate how to use the different feature of SpVoive class of SAPI 5.1 to make your applications speak out. Once I needed to use Microsoft SAPI in one of the project while helping a dearest one. I really ammazed you can make use of TTS with in very short time. This example will show you the following features of TTS of Microsoft SAPI 5.1 in C#.

  1. Play
  2. Pause
  3. Rate ( Speed of Speech / Speaker)
  4. Volume ( Volume of the speaker )
  5. Using all of the available Voices of SAPI
  6. Convert the provided text to wav file and save it.

Using the code

To make use of the code you will need to install the Microsoft Spech API 5.1 and you can download it from the Microsoft's site freely. This is the link of that :

After installing the Microsoft SAPI 5.1 just download the source and open it in Visual Studio 2005. Before compiling or builiding the project add refrence to the following DLL file (Interop.SpeechLib.dll)that will provide you the speechLib namespace that holds all the classes for TTS and SR. You can find this DLL in the bin directory of the source project.

Now I will just give you the snipts of the code thaat is providing the different features.

Play / Speak

To play/Speak a text, create an object of SpVoice class and all its speak method that will speak out the text provided to it. Code example is following:

SpVoice speech = new SpVoice();

speech.Speak("Hello It is a Microsoft Speech API test application", SpeechVoiceSpeakFlags.SVSFlagsAsync);

Pause and Resume
To pause your current stream playing just call the Pause method and to resume the current stream you can use Resume method of SpVoice class like,


To set the speed of the speaker you can use the Rate method of the SpVoice class. You can use a track bar to modify the speech rate. and set the following properties of the trackbar values :Minimum = - 10 and Maximum = 10. and you can capture the changed value on Scroll event of the trackbar.

speech.Rate = speechRate; // speechRate ranges from -10 to 10.

You can use the volume method of SpVoice to set the volume of the speech like:

speech.Volume = volume; // volume ranging from 0 to 100

You can use the getVoices method of the SpVoice class to get the available voices in MS SAPI 5.1. It will return tokens as object of ISpeechObjectToken. You can populate them in a comboBox so that you can change it on rum time from the ComboBox. You should populate this comboBox while Initializing the components for the windows form or in onLoad() method of the windows form. The example of this is given below:

foreach (ISpeechObjectToken Token in speech.GetVoices(string.Empty, string.Empty))
// Populate the ComboBox Entries ..
cmbVoices.Items.Add(Token.GetDescription(49)); // Here cmbVoices is an ComboBox object.
cmbVoices.SelectedIndex = 0;

Converting to WAV File
You can convert the text into WAV file rather than speaking it out. The following code snipt will provide you the complete functionality for making the WAV file for the spoken text.

{ SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "All files (*.*)*.*wav files (*.wav)*.wav";
sfd.Title = "Save to a wave file";
sfd.FilterIndex = 2;
sfd.RestoreDirectory = true;
if (sfd.ShowDialog() == DialogResult.OK)
SpeechStreamFileMode SpFileMode = SpeechStreamFileMode.SSFMCreateForWrite;
SpFileStream SpFileStream = new SpFileStream();
SpFileStream.Open(sfd.FileName, SpFileMode, false);
speech.AudioOutputStream = SpFileStream;
speech.Rate = speechRate;
speech.Volume = volume;
speech.Speak(tbspeech.Text, SpeechVoiceSpeakFlags.SVSFlagsAsync);
MessageBox.Show("There is some error in converting to Wav file.");

Download Sample Code:

You can download the sample code form the following link:

On the way to Competetion

On the way to Competetion

Welcom to the New World!!

Hey Everybody,
                             I hope every one of you is in god health and enjoying their pals. May Allah Almight keep showering his blessing on all of us. I really miss and love the MYRSH TEAM.