Store Procedures Vs User Defined Functions

Store Procedures		User Defined Functions
1	A stored procedure is a program (or procedure) which is physically stored within a database.	A user-defined function is a routine that encapsulates useful logic for use in other queries.
2	Procedure can return zero or n values.	Function returns only one value which is mandatory.
3	Procedure can have Input, Output Parameter.	UDF can have only Input Parameters.
4	Procedure allow select as well as DML statement in it	Functions allow only select statement in it.
5	Procedure can not be called from function.	Functions can be called from Procedure
6	We can go for transaction management in procedure	In function We can’t
7	Procedures can not be utilized in a select statement	Functions can be embedded in a select statement.

Where Vs Having

WHERE		HAVING
1	It is used for restricting rows from table.	It is used for restricting rows which are Grouped.
2	No need to use Group by clause when you are using Where clause.	You have to use Group by Clause When you are using Having Clause.
3	Example : SELECT ENAME,SALARY FROM EMP WHERE SALARY>10000 It returns all employee whose salary > 10000	SELECT DEPTNO,SUM(SALARY) FROM EMP GROUP BY DEPTNO HAVING SUM(SALARY)>10000 It returns all department whose total salary > 10000

Rollup Vs Cube

Rollup		Cube
1	The ROLLUP operator is useful for generating reports that contain subtotals and totals	The Cube operator is useful for generating reports that contain totals
2	ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.	CUBE generates a result set showing aggregates for all combinations of values in the selected columns.
3	Example : SELECT DEPTNO,JOB,SUM(SALARY) FROM EMPLOYEES GROUP BY DEPTNO,JOB WITH ROLLUP It will give u total and subtotal of all department as well as job.	Example : SELECT DEPTNO,JOB,SUM(SALARY) FROM EMPLOYEES GROUP BY DEPTNO,JOB WITH CUBE It will give u total of all departments.

Charindex Vs Patindex

CharIndex		PatIndex
1	Returns the Starting position of specified Expression in Character String.	Returns the Starting position of First Occurrence of a pattern in Specified Expression, Or Zeros if Pattern is not found.
2	In Charindex you can specify Start location.	In PatIndex you can not specify starting location. It will return you First Occurrence of a pattern
3	SELECT CHARINDEX(‘;’,’KRISHNA;RADHA’) Output : 8 It will give u position of Semicolon.	Example : SELECT PATINDEX(‘%A%’,’KRISHNA;RADHA’) Output : 7 It will give u position of First occurrence of A.

System Datatype Vs User Defined Datatype

System Datatype		User Defined Datatype
1	System Data type is defined by the System.	User Defined Data type defined by the User.
2	There are more than 25 System Data type available in SQL Server.	User Defined Data type is also System data type, just name of that Data type is Changed by the User.
3	Example : Col1 BIGINT In this Example BIGINT is a System Data type	Example : SP_ADDTYPE ‘pk’, BIGINT In this Example ‘PK’ is User Defined Data type. You can use PK Anywhere instead of BIGINT.

Truncate Vs Delete

Truncate		Delete
1	It is DDL(Data Definition Language) Statement.	It is DML(Data Manipulation Language) Statement.
2	It Delete all rows from table.	You Can Delete All rows as well as specified rows using Delete.
3	You can not Rollback all raw.	Using Rollback you can retrieve all rows from the point of last commit.
4	It release all raw including Memory space, Just Structure remains there.	It delete all raw but, not Memory space.
5	Example : Truncate Table Emp	Example : Delete From Emp Where Empno=7369

Small DateTime vs DateTime

Small DateTime		DateTime
1	It Occupies 4 byte Size	It Occupies 8 byte Size
2	The precision of small datetime is one minute	The precision of datetime is 3.33 milli Seconds
3	It Stores Date From 1st January 1900 through 6th June 2079. Which is usually more than enough	It Stores Date From 1st January 1753 through 31st December 9999.