Finding the Missing GAPS with in a Table

-- // CREATE A TABLE

Create table tb_GAPS

(

Sno int not null

)

Go

--// 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)

------------------

--// HERE IS THE CODE

Declare @MAX as int

Declare @Start as int

Declare @SRN as int

SET @Start =1

SET @SRN =0

select @MAX=max(Sno) from tb_GAPS

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

DECLARE @m_TempTable AS TABLE (

[SNO] [INT]

)

WHILE (@Start < = @MAX )

BEGIN

select @SRN=sno from tb_GAPS where sno = @Start

IF (@SRN = 0)

BEGIN

INSERT into @m_TempTable values(@Start)

END

SET @Start = @Start+1

SET @SRN =0

END

select * from @m_TempTable

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.

A SWITCH STATEMENT IS BETTER WHEN

• 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.
  

AN IF/IF-ELSE STATEMENT IS BETTER WHEN

• 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.
  

DIFFERENCES

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.