जगदीश खोलिया: November 2011

Friday, November 25, 2011

How to call a specific base constructor in C#?

How to call a specific base constructor in C#?
What is a Constructor? - It is a method that gets invoked when an instance of a class is created. In case a class has plenty of constructors, i.e. there are plenty of overloaded constructors, in such a scenario, it is still possible to invoke a specific base constructor. But there is a special way, as explicit calls to a base constructor is not possible in C#. See code below:
C# Example
public class dotnetClass
{
public dotnetClass()
{
// The constructor method here
}
// Write the class members here
}

// below shows how to overload a constructor
public class dotnetClass
{
public dotnetClass()
{
// This constructor is without a parameter
// Constructor #1
}

public dotnetClass(string name)
{
// This constructor has 1 parameter.
// Constructor #2

}
}
This constructor gets executed when an object of this class is instantiated. This is possible in C#. Calling a specific constructor will depend on how many parameters, and what parameters match a specific constructor. Note that a compile time error may get generated when 2 constructors of the same signature are created.

We may make use of the this keyword and invoke a constructor. See code example below.
this("some dotnet string");
//This will call Constructor #2 above
What is the use of the base keyword. Suppose we have a derived class named dotnetderivedclass. If this derived class is to invoke the constructor of a base class, we make use of the base keyword. See code example below on how to use a base keyword to invoke the base class constructor.
public class dotnetClass
{
public dotnetClass()
{
// The 1st base class constructor defined here
}

public dotnetClass(string Name)
{
// The 2nd base class constructor defined here
}
}

public class dotnetderivedclass : dotnetClass
// A class is being inherited out here
{
public dotnetderivedclass()
{
// dotnetderivedclass 1st constructor defined here
}

public dotnetderivedclass(string name):base(name)
{
// dotnetderivedclass 2nd constructor defined here
}
}
Note that we have used the base keyword in the sample code above. The sequence of execution of the constructors will be as follows:
public dotnetClass() method -> public dotnetderivedclass() method

The above sequence triggers when there is no initializer to the base class, and thus it triggers the parameterless base class constructor. The other base class constructor may also get invoked when we pass a parameter while defining it.

Wednesday, November 23, 2011

Magic Tables in SQL Server

What are Magic Tables in SQL Server?

In SQL server magic table is nothing more than an internal table which is created by the SQL server to recover recently inserted, deleted and updated data into SQL server database. That is when we insert or delete any record from any table in SQL server then recently inserted or deleted data from table also inserted into inserted magic table or deleted magic table with help of which we can recover data which is recently used to modify data into table either use in delete, insert or update to table. Basically there are two types of magic table in SQL server namely: inserted and deleted, update can be performed with help of these twos. Generally we cannot see these two table, we can only see it with the help Trigger’s in SQL server.
 
Whenever a trigger fires in response to the INSERT,DELETE,or UPDATE statement,two special tables are created.These are the inserted and the deleted tables.They are also referred to as the magic tables.These are the conceptual tables and are similar in structure to the table on which trigger is defined(the trigger table).

While using triggers these Inserted & Deleted tables (called as magic tables) will be created automatically.

The inserted table contains a copy of all records that are inserted in the trigger table.

The deleted table contains all records that have been deleted from deleted from the trigger table.

Whenever any updation takes place,the trigger uses both the inserted and deleted tables.

When we insert any record then that record will be added into this Inserted table initially, similarly while updating a record a new entry will be inserted into Inserted table & old value will be inserted into Deleted table. In the case of deletion of a record then it will insert that record into the Deleted table.

Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns will cause an error.
Let’s see the demonstration of inserted magic table in SQL Server:

INSERTED Magic Table:

When we insert record into table then SQL server automatically created ‘inserted’ magic table and recently inserted record are available in this table, If we want to recover this data which are recently inserted then we can access this record with the help of Trigger’s. Let’s see the demonstration of inserted magic table in SQL Server.

Example:

--- CREATE TABLE TO DEMONSTRATION OF INSERT MAGIC TABLE ----
CREATE TABLE INSERT_MAGIC
(
ID INT,
TRIGGER_MESSAGE VARCHAR(50)
 )
 -------DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE
  CREATE TRIGGER TRI_MAGIC_INSERT
  ON USERLOGIN
  INSTEAD OF INSERT
  AS
  BEGIN
  DECLARE @ID INT
  SELECT @ID = (SELECT ID FROM inserted)
  INSERT INTO INSERT_MAGIC VALUES (@ID,'RECORD ADDED')
  END
  GO

Note: Here ‘inserted’ is insert magic table

DELETED Magic Table:

When we delete record from the table then SQL automatically create a deleted magic table which holds are deleted record from original table if we want to recover it then we can access that data from deleted magic table. Let’s see demonstration of recover data from deleted magic table.

Example: Creating Trigger for Deleted Magic table

-------DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE
  CREATE TRIGGER TRI_MAGIC_DELETE
  ON USERLOGIN
  INSTEAD OF INSERT
  AS
  BEGIN
  DECLARE @ID INT
  DECLARE @NAME VARCHAR(50)
  SELECT @ID = (SELECT ID FROM deleted)
  SELECT @NAME = (SELECT NAME FROM deleted)
  INSERT INTO INSERT_MAGIC VALUES (@ID,@NAME)
  END
  GO

Page Life Cycle Events

1. OnInit (Init) -- Inializes all Child controls on the Page

2. LoadControlState -- Loads the Control State

3. LoadViewState -- Loads the View State

4. LoadPostData -- Control properties are set according to the received form data

5. Load -- Actions can be performed on the controls as all the pre-activities are complete by this time

6. RaisePostDataChangedEvent -- This event is raised if data has been changed in previous and Current Postbacks.

7. RaisePostBackEvent -- This event handles the user action that caused the Postback.

8. PreRender (OnPreRender) -- This event takes place between postback and saving viewstate. Till this stage changes will be saved to the control.

9. SaveControlState -- Self Explanatory

10. SaveViewState -- Self Explanatory

11. Render -- Generates artifacts at Client side (HTML,DHTML,Scripts) to properly display controls

12. Dispose -- Releases unmanaged Resource ( Database connections, file handles)

13. Unload -- Releases managed Resources ( Instances created by CLR)

Tuesday, November 22, 2011

UNION vs UNION ALL?

 Why UNION ALL is faster than UNION?

 

UNION and UNION ALL are used to combine data or record sets from two different tables. One could combine more than 2 tables. Just like JOINS, UNION combines data into a single record-set but vertically by adding rows from another table. JOINS combine data horizontally by adding columns from another table.
UNION insures you get DISTINCT records from both the tables.
UNION ALL pulls out all records from both the tables with duplicates.
SQL Scripts below proves why UNION ALL is faster than UNION:
1-- Create First table: #tempTable1
2select FirstName, LastName into #tempTable1
3from Person.Contact
4where ContactID <= 100 -- Create Second table: #tempTable2 select FirstName, LastName into #tempTable2 from Person.Contact where ContactID > 100 and ContactID <= 200
Comparison:
1SET STATISTICS PROFILE ON
2
3-- Using UNION - Query Cost: 35%
4select * from #tempTable1
5UNION
6select * from #tempTable2
Query Plan for UNION:
  |--Sort(DISTINCT ORDER BY:([Union1008] ASC, [Union1009] ASC))
       |--Concatenation
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))
1-- Using UNION ALL - Query Cost: 13%
2select * from #tempTable1
3UNION ALL
4select * from #tempTable2
Query Plan for UNION ALL:
  |--Concatenation
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))
1-- Using UNION ALL with DISTINCT - Query Cost: 52%
2select DISTINCT * from #tempTable1
3UNION ALL
4select DISTINCT * from #tempTable2
Query Plan for UNION ALL with DISTINCT:
  |--Concatenation
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable1].[FirstName] ASC,
[tempdb].[dbo].[#tempTable1].[LastName] ASC))
       |    |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable2].[FirstName] ASC,
[tempdb].[dbo].[#tempTable2].[LastName] ASC))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))
1SET STATISTICS PROFILE OFF
2
3-- Final Cleanup
4drop table #tempTable1
5drop table #tempTable2
The Query plan for:
- UNION ALL SELECT above shows it just does a concatenation.
- UNION SELECT above shows it does a SORT on final concatenation.
- UNION ALL with DISTINCT SELECT above shows it does a SORT of individual tables than does the final concatenation.
The Query cost with:
- UNION ALL = 13%
- UNION = 35%
- UNION ALL with DISTINCT = 52%
This proves that:
  • UNION ALL is faster and more optimized than UNION. But this does not mean you use UNION ALL in every scenario.
  • UNION is not equivalent to “UNION ALL with DISTINCT”.
referenced from http://sqlwithmanoj.wordpress.com/2010/12/

Monday, November 21, 2011

Primary keys without Clustered Index

Primary keys without Clustered Index ...

Clustered Index :
A Clustered index determines the physical order of data in a table and is particularly efficient on columns that are often searched for ranges of values.
The leaf nodes of a clustered index contain the data pages.
There can be only one clustered index per table.
"primary key" is the ideal column for a clustered index
Clustered indexes are good for range searches.

Nonclustered Index :
Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
There can be multiple non-clustered indexes per table.
"Unique Key" is the ideal column for a nonclustered index
Non-clustered indexes are good for random searches.

As everyone of us know, by default if we create a Primary Key (PK) field in a table it would create Clustered Index automatically. I have been frequently asked by some of blog readers on "Is there a way to create PK fields without clustered Index?". Actually there are three methods by which we can achieve this. Let me give you the sample for both the methods below:

Method 1:

Using this method we can specify it while creating the table schema itself.

Create Table Empolyee
(
EmpID int Identity not null primary key nonclustered,
Name varchar(30),
PhoneNo int null
)
Go

Method 2:

Using this method also we could specify it while creating the table schema. It just depends on your preference.

Create Table Employee
(
 EmpID int Identity not null primary key nonclustered,
Name varchar(30),
PhoneNo int null
Constraint pk_parent primary key nonclustered (EmpID)
)
Go

Method 3:

If at all you already have a table which have a clustered index on a PK field then you might want to opt for this method.

Step 1: Find the contraint name

sp_helpconstraint Employee

/*
This way we could find out the constraint name. Lets assume that our constraint name is PK_Employee_22568A79
*/

Step 2: First drop the existing constaint

Alter table Employee drop constraint PK_Employee_22568A79

Step 3: Add the new nonclustered index to that field now

Alter table Employee add constraint PK_EmpIdNew primary key nonclustered (EmpID )

Difference between TRUNCATE, DELETE and DROP

Difference between TRUNCATE, DELETE and DROP?

DELETE & TRUNCATE are two TSQL commands used to remove records from a particular table. But they differ in how they execute and operate.
DELETE: (http://msdn.microsoft.com/en-us/library/ms189835.aspx)
- Removes some or all rows from a table.
- A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
- Causes all DELETE triggers on the table to fire.
- It deallocated records row-by-row in transaction logs and thus is slower than TRUNCATE.
- According to MS BOL, if a table is a heap or no clustered index is defined than the row-pages emptied are not deallocated and remain allocated in the heap. Thus no other object can reuse this associated space. Thus to deallocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
- Thus it requires more locks and database resources.
- This is a DML command as its is just used to manipulate/modify the table data. It does not change the property of a table.
TRUNCATE: (http://msdn.microsoft.com/en-us/library/ms177570.aspx)
- Removes all rows from a table.
- Does not require a WHERE clause, not allowed here.
- Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
- No triggers are fired on this operation because it does not log individual rows.
- It deallocates data pages instead of rows in transaction logs, thus is faster than DELETE.
- Thus it also requires less number of locks.
- TRUNCATE is not possible when a table is reference by a Foreign Key or tables used in replication or with Indexed views.
- This is a DDL command as its resets identity columns, deallocates data pages and empty them for use of other objects in the database.
Note: It is a misconception among some people that TRUNCATE cannot be rolled back. But in reality TRUNCATE operation can be ROLLED BACKED. Thus DELETE & TRUNCATE both can be rollbacked if provided inside a transaction.
DROP: (http://msdn.microsoft.com/en-us/library/ms173790.aspx)
The DROP TABLE command removes one or more table(s) from the database.
- All related data, indexes, triggers, constraints, and permission specifications for those tables are dropped by them operation.
- Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.
- Cannot drop a table that is referenced by any Foreign Key constraint.
- According to MS BOL, Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

Thursday, November 17, 2011

Reverse a string using CTE in sql

DECLARE @StringToReverse VARCHAR(55)
    SET @StringToReverse = 'jagdish kholiya'   
    ;WITH cte AS (
          SELECT @StringToReverse AS string, CAST('' AS VARCHAR(55)) AS revStr, LEN(@StringToReverse) AS ln        
           UNION ALL
          SELECT SUBSTRING(string,0,ln) AS string, CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS revStr, ln-1 AS ln
          FROM cte
          WHERE ln >= 1)
    SELECT @StringToReverse AS String, revStr FROM cte WHERE ln = 0

Finnding & Removing Duplicate records from a table

There are lots of questions/posts about dealing with duplicate records in many SQL Server forums. Many of these questions are asked in “SQL Server Interviews” and many developers starting their carrier in database programming find it challenging to deal with. Here we will see how we can deal with such records.
Duplicate data is one of the biggest pain points in our IT industry, which various projects have to deal with. Whatever state-of-art technology and best practices followed, the big ERP, CRM, SCM and other inventory based database management projects ends up in having duplicate & redundant data. Duplicate data keeps on increasing by manual entries and automated data loads. Various data leads getting pumped into system’s databases without proper deduping & data cleansing leads to redundant data and thus duplicated record-sets.
Data cleansing requires regular exercise of identifying duplicates, validating and removing them. To minimize these type of scenarios various checks and filters should also be applied before loading new leads into the system.
Lets check this by a simple exercise how we can identify & remove duplicate data from a table:
1USE [AdventureWorks]
2GO
3 
4-- Insert some sample records from Person.Contact table of [AdventureWorks] database:
5SELECT TOP 10 ContactID, FirstName, LastName, EmailAddress, Phone
6INTO DupContacts
7FROM Person.Contact
8 
9SELECT * FROM DupContacts

1-- Insert some duplicate records from the same list inserted above.
2INSERT INTO DupContacts
3SELECT TOP 50 PERCENT FirstName, LastName, EmailAddress, Phone
4from DupContacts
5 
6SELECT * FROM DupContacts

1-- Insert some more duplicate records from the same list inserted above.
2INSERT INTO DupContacts
3SELECT TOP 20 PERCENT FirstName, LastName, EmailAddress, Phone
4from DupContacts
5 
6SELECT * FROM DupContacts

01--// Identify Duplicate records & delete them:-
02 
03-- Method #1: by using ROW_NUMBER() function:
04;WITH dup as (
05SELECT ContactID, FirstName, LastName, EmailAddress, Phone,
06ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY ContactID) AS NumOfDups
07FROM DupContacts)
08SELECT * FROM dup
09WHERE NumOfDups > 1
10ORDER BY ContactID

1-- Remove/Delete duplicate records:
2;WITH dup as (
3SELECT ContactID, FirstName, LastName, EmailAddress, Phone,
4ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY ContactID) AS NumOfDups
5FROM DupContacts)
6DELETE FROM dup
7WHERE NumOfDups > 1
8 
9SELECT * FROM DupContacts

1-- Method #2: by using SELF-JOIN:
2SELECT DISTINCT a.ContactID, a.FirstName, a.LastName, a.EmailAddress, a.Phone
3FROM DupContacts a
4JOIN DupContacts b
5ON a.FirstName = b.FirstName
6AND a.LastName = b.LastName
7AND a.ContactID > b.ContactID

1-- Remove/Delete duplicate records:
2DELETE a
3FROM DupContacts a
4JOIN DupContacts b
5ON a.FirstName = b.FirstName
6AND a.LastName = b.LastName
7AND a.ContactID > b.ContactID
8 
9SELECT * FROM DupContacts

1-- Method #3: by using AGGREGATES & Sub-QUERY:
2SELECT * FROM DupContacts
3WHERE ContactID NOT IN (SELECT MIN(ContactID)
4FROM DupContacts
5GROUP BY FirstName, LastName)

1-- Remove/Delete duplicate records:
2DELETE FROM DupContacts
3WHERE ContactID NOT IN (SELECT MIN(ContactID)
4FROM DupContacts
5GROUP BY FirstName, LastName)
6 
7SELECT * FROM DupContacts



 
Referenced from : http://sqlwithmanoj.wordpress.com/category/sql-tips/