Finding a Leap Year

We all very well know what a leap year is? A year which has 366 days is called a leap year. But how many of us know how do you know which year is a leap year? Do you know which calendar year will have 29 days in the month of February?

Most of us have a very wrong perception of a Leap year. If you ask any person how do you calculate a Leap year? His obvious reply will be divide the year by 4, if it gets divided then it is a leap year. Well that seems to be very simple, so why do we need a function to calculate a leap year?

Actually leap year is some thing more then divide by 4. First you need to check whether a year gets divided by 4, if it gets divided by 4 then ensure that it does not get divided by 100, if it does not get divided by 100 then it is a leap year. But, if it gets divided by 100 then it should also get divided by 400 in order to be a leap year.


This function takes year as an input and returns 1, if it is a leap year and 0 if it is not a leap year.


CREATE OR REPLACE FUNCTION IsLeapYear
(
p_year IN NUMBER
)
RETURN NUMBER

IS

v_chkLeapYr NUMBER;
X NUMBER;

BEGIN

--**Leap Year Calculation

SELECT DISTINCT MOD(p_year, 4) INTO v_chkLeapYr FROM DUAL;

IF v_chkLeapYr = 0 THEN
IF mod(p_year,100) = 0 THEN --* If divided by 4 and not by 100 then LEAP YEAR
IF mod(p_year,400) = 0 THEN --* If divided by 4 and 100 then it should also get divided by 400 then LEAP YEAR
X := 366;
ELSE
X := 365;
END IF;
ELSE
X := 366;
END IF;
ELSE
X := 365;
END IF;

If x = 366 Then
Return (1); --* Lear Year
Else
Return (0);
End If;


END IsLeapYear;

SQL Server: Function vs Stored Procedure

Functions are referred as UDF (User Defined Functions) in SQL Server. There where no UDF till SQL Server 7.0. UDF was first introduced in SQL Server 2000.

Some of the difference between UDF and Stored procedure given in SQL Magazine are as follows:

1) A UDF must return a value-a single result set. A stored procedure can return a value-or even multiple result sets-but doesn't have to.

2) You can use a UDF directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can't use a stored procedure in a SELECT statement.

3) A UDF can't use a nondeterministic function such as GETDATE(), NEWID(), or RAND(), whereas a stored procedure can use such functions. A nondeterministic function is one that can return a different result given the same input parameters.

4) A UDF can't change server environment variables; a stored procedure can.

5) A UDF always stops execution of T-SQL code when an error occurs, whereas a stored procedure continues to the next instruction if you've used proper error handling code.

Oracle: Function vs Stored Procedure

Difference between procedure and funtion:

1. Function always have a returns value where as procedure does not have a return value, but you can use OUT parameter for this purpose.
2. Function can be used in SELECT statement where as procedure can not. eg. SELECT GetName(v_id) FROM DUAL;
3. You can have DML(Insert, Update, Delete) DDL(TRUNCATE, Drop), TCL(COMMIT, ROLLBACK) in a function but then you can not call that function in any SQL query.
   

Formatting SQL Code

Tired of formatting your code manually. Just try this online tool, its free!!
http://www.dpriver.com/pp/sqlformat.htm

Oracle: EXISTS vs IN

We have a general perception that EXISTS is faster then IN. This is not always true. There can be some cases where IN performs better then EXISTS.

This depends on the size of the tables (and data queried) Using "IN", the optimizer generally runs the subquery first, then joins with the main dataset (nested loops, merge, whatever). If the table in the subquery is small, and the table in the main query is large, then IN usually makes sense.

Using "EXISTS", the optimizer generally runs the main query first, then applies that dataset to the subquery. If the table in the subquery is large, and the table in the main query is small, then EXISTS usually makes sense.

By the way, Full Table Scan are not necessarily bad. If you are querying more than, say 15 - 20%, of the data in a table the Full Table Scan may be faster. Two Full Table Scans with a Hash Join are frequently gobs faster than a Nested Loops type of plan.