Tuesday, June 29, 2010

Throw Vs Throw ex

Throw Ex
        // do some operation that can fail
   catch (Exception ex)
        // do some local cleanup
        // 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

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

Monday, June 28, 2010

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

Thursday, June 24, 2010

Differences between COALESCE and 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:-
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:-
Output :- 2
If Both argument are equal, then it will return NULL value.

For Example:-
Output :- NULL

Forms Of Normalization

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

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

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.

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

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.