Throw Vs Throw ex

Throw	Throw Ex
try{         // do some operation that can fail    }    catch (Exception ex)    {         // do some local cleanup         throw;    }	try    {         // do some operation that can fail    }    catch (Exception ex)    {         // do some local cleanup         throw ex;    }
It preserve the Stack information with Exception	It Won't Send Stack information with Exception
This is called as "Rethrow"	This is called as "Breaking the Stack"
If want to throw new exception, throw new ApplicationException("operation failed!");	If want to throw new exception, throw new ApplicationException("operation failed!",ex);

Finalize Vs Dispose

Finalize	Dispose
Implicit way to destroy the object	Explicit Way to Destroy Object
It Cannot Override	It Can be Override
It freed up the memory used by the object only when scope of the expired	Dispose is forcibly freed up the memory
It is called by GC at runtime	It is called by user
If you call finalize, it will move to finalize queue, GC will finalize the object by freed up the memory	It will directly freed up the memory
It will be handling by GC, no inconsistent error occur	Error will occur, because it is not controlled by GC

Functions (UDF) Vs Stored Procedures (SP)

Functions (UDF)	Stored Procedures (SP)
Execute Functions thru SQL Statement	It Can not be used in SQL Statements
UDF run thru SQL SELECT	SP run thru EXECUTE OR EXEC
You can't use DDL, DML statements in UDF	It is possible in SP
It always return values	It May or May not return value
UDF return single resultset	SP can return multiple resultset
Function can't return XML output	SP can return XML output
UDF can have only Input Parameter	SP can have Input & Output Parameter
SP can not call thru Functions	Function can be called from SP
Exception handling is not Possible	Exception Handling is possible

Composite Key / Concatenated Key / Aggregate Key:

Composite Key is a primary key, combination of more than one column to maintain unique record. It is also known as Concatenated Key or Aggregate Key

For example:

CREATE TABLE InvoiceDetails (

      InvoiceNo numeric(18,0),

      itemNumber numeric(18,0),

      productId  int,

      quantity   numeric(18,2),

      PRIMARY KEY (InvoiceNo, itemNumber))

InvoiceDetails table may have a composite key on InvoiceNo & itemNumber

Referential integrity

It is a Database concept, it maintain the consistent relation between parent and child tables. When one table contains FK, it may not allow inserting any values in key columns of the child tables which is not existed in parents table. It supports cascading update and cascading delete, which ensures the changes made to the child table will be reflected in parent table as well.

How to List All the Databases from SQL Server

You can use any of the following queries to retrieve list of all databases

SELECT name FROM sys.databases

SELECT name FROM sys.sysdatabases

Or you can use the following system stored procedure

EXEC sp_databases

EXEC sp_helpdb

Difference Between Star Schema and Snowflake Schema

Star Schema	Snowflake Schema
De-Normalized Data Structure	Normalized Data Structure
Category wise Single Dimension Table	Dimension table split into many pieces
More data dependency and redundancy	less data dependency and No redundancy
No need to use complicated join	Complicated Join
Query Results Faster	Some delay in Query Processing
No Parent Table	It May contain Parent Table
Simple DB Structure	Complicated DB Structure
View Star Schema Structure	View Snowflake Schema Structure

SQL Server 2008 – Enterprise Vs Standard

Reference: http://www.microsoft.com/sqlserver/2008/en/us/compare-std-ent.aspx

Differences between COALESCE and NULLIF

COALESCE	NULLIF
It Accepts N Parameters, It returns first Not Null Values For Example:- select coalesce(NULL,2,NULL,3) Output :- 2	It Accepts 2 Parameters, if both are not equal then it will return first value. For Example:- SELECT NULLIF(1,2) Output :- 1
First argument can be NULL, but both argument can not NULL value	First argument can not be NULL input
If Both argument are equal, then it will return first value. For Example:- SELECT NULLIF(2,2) Output :- 2	If Both argument are equal, then it will return NULL value. For Example:- SELECT NULLIF(2,2) Output :- NULL

Forms Of Normalization

This is continuation of my previous post "What is Normalization"

http://blog-mstechnology.blogspot.com/2010/06/what-is-normalization.html

I hope following Brief Notes about Normalization will be helpful to you,

First Normal Form (1NF): 

·         No repeating group of data items

·         Each column should contain specific values

·         Each row is equivalent logical record

·         Relation should contains records of identical format

Second Normal Form (2NF):

·         Foreign Key Relation Ship

·         Each data items in a record is functionally dependent on the key of that record

·         Each record uniquely identified with the Key

·         No Redundant data

·         It should full fill the 1NF

Third Normal Form (3NF):

·         First Full fill second normal form

·         Example Fact Table

·         All non-key functionally dependent on primary key

Boyce-Codd Normal Form (BCNF):

·         Eliminate redundancy

·         It adheres both 2NF AND 3NF

Fourth Normal Form (4NF):

·         Eliminate multi dependency

Fifth Normal Form (5NF):

·         Eliminate cyclic dependency

Sixth Normal Form (6NF):

·         It satisfies No Non Trivial join at all,

·         Extending relational model, temporal dimensions

Domain Key Normal Form (DKNF):

·         No constraints other than domain constraints

How to Insert Identity Column in SQL Server?

IDENTITY property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements. it exposes the row identity property of a column. it will generate the number to maintain the unique records.

For Example, assume we have a Employee table

CREATE TABLE [dbo].[Employee](

            [EmpID] [int] IDENTITY(1,1) NOT NULL,

            [EmpName] [varchar](50) NOT NULL

) ON [PRIMARY]

When we insert the values of Empname then EmpID will be generated as a sequence number, as defined in the table schema. Assume we are inserting the following records,

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('BABU')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('RAMESH')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('MANI')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('VIJAY')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('VINODH')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('KUMAR')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('VARATHA')

INSERT INTO [dbo].[Employee]([EmpName]) VALUES ('JOTHI')

Now if you check the values of the table, It will be display with identity values as shown in exhibit

SELECT * FROM Employee

This identity is always generated by the system. We can not insert this value directly using INSERT statement.

If you insert directly, it will thru the error like

INSERT INTO dbo.[Employee](EmpID,EmpName) VALUES(1, 'BABU')

Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table 'Employee' when IDENTITY_INSERT is set to OFF.

if you want insert manually or insert the identity value from other table, then we need to Turn off the table property IDENTITY_INSERT. We can do this, as given in the example.

SET IDENTITY_INSERT [Employee] ON

INSERT INTO dbo.[Employee](EmpID,EmpName) VALUES(10, 'VENKAT')

SET IDENTITY_INSERT dbo.[Employee] OFF

This will be helpful, when you delete and insert some record in the middle of the sequence of identity.
This is not recommandable, becoz If you insert the Existing sequence, it will accept. You can run the last script many times. So handle with careful.