Thursday, January 31, 2013

Singleton Design Pattern

0 comments Posted by Rahul Kharde at 9:32 PM

"A class of which only a single instance can exist"
  • Singletons can be lazy loaded. Only when it is actually needed. That's very handy if the initialization includes expensive resource loading or database connections.
  • Singletons offer an actual object.
  • Singletons can be extended into a factory. The object management behind the scenes is abstract so it's better maintainable and results in better code.
You can use it to create a connection pool. It’s not wise to create a new connection every time a program needs to write something to a database; instead, a connection or a set of connections that are already a pool can be instantiated using the Singleton pattern.

For example,

public class Singleton {
   private static Singleton instance;  

   private Singleton(){}

   public static Singleton getInstance() {
     if (instance == null)
       instance = new Singleton();
     return instance;
   }
 }

Read More »

Wednesday, January 30, 2013

Difference Temporary Table and Table Variable –SQL SERVER?

0 comments Posted by Rahul Kharde at 1:32 AM

Temporary Table
Table Variable
create table #T (…)
declare @T table (…)
Temporary Tables are real tables so you can do things like CREATE INDEX,
Table variable is not real table but you can have indexes by using PRIMARY KEY or UNIQUE constraints.
CREATE TABLE statement.
SELECT INTO statement.
DECLARE statement  Only
Maximum 116 characters.
Maximum 128 characters
Temp tables might result in stored procedures being recompiled,
Table variables will not.
#temp_tables are created explicitly when the TSQL CREATE TABLE statement is encountered and can be dropped explicitly with DROP TABLE or will be dropped implicitly when the batch ends.
@table_variables are created implicitly when a batch containing a DECLARE @.. TABLE statement is executed (before any user code in that batch runs) and are dropped implicitly at the end.
User-defined data types and XML collections must be in tempdb to use
Can use user-defined data types and XML collections.
Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
Automatically at the end of the batch.
Last for the length of the transaction. Uses more than table variables.
Last only for length of update against the table variable. Uses less than temporary tables.
Creating temp table and data inserts cause procedure recompilations.
Stored procedure recompilations Not applicable.
Data is rolled back
Data not rolled back
Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.
The SET IDENTITY_INSERT statement is supported.
The SET IDENTITY_INSERT statement is not supported.
INSERT statement, including INSERT/EXEC.
SELECT INTO statement.
INSERT statement (SQL 2000: cannot use INSERT/EXEC).
PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.
Indexes can be added after the table has been created.
Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.

Example

CREATE TABLE #Temp
(
          Col1 INT IDENTITY,
          Col2 VARCHAR(100)
)

DECLARE @Temp TABLE
(
          Col1 INT IDENTITY,
          Col2 VARCHAR(100)
)

INSERT INTO #Temp(Col2) select 'Temp Table'
INSERT INTO @Temp(Col2) select 'Table Variable'

SELECT * FROM #Temp
SELECT * FROM @Temp

DROP TABLE #Temp


Read More »

Tuesday, January 29, 2013

How to use rank function in SQL Server ?

0 comments Posted by Rahul Kharde at 9:38 PM
The ROW_NUMBER () function in SQL Server  returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.


The RANK() function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.


The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.


The NTILE() function in SQL Server return distributes the rows in an ordered partition into a specified number of groups.

Example
CREATE TABLE Students(
      Stud_ID     INT IDENTITY(1,1),
      Stud_Name   VARCHAR(100),
      Stud_Mark   INT
)

INSERT INTO Students(Stud_Name,Stud_Mark)
VALUES('a',60),
('b',94),
('c',70),
('d',63),
('e',60),
('f',60),
('g',94),
('h',47),
('i',70),
('j',60)


SELECT Stud_ID,Stud_Name,Stud_Mark,
      ROW_NUMBER() OVER (ORDER By Stud_Mark) as 'ROW NUMBER',
      RANK()                   OVER (ORDER By Stud_Mark) as 'RANK',
      DENSE_RANK()  OVER (ORDER By Stud_Mark) as 'DENSE RANK',
      NTILE(2)                 OVER (ORDER By Stud_Mark) as 'NTILE'
FROM Students


Read More »

Monday, January 28, 2013

What is the difference between static, internal and public constructors?

0 comments Posted by Rahul Kharde at 8:25 AM
Static Constructor
  • Static constructor runs only ones - before the first use of the class and it can access only the static members of the class
  • Used to initialize the static members of a class.
  • Can’t access non-static members.
  • Executes before the first instance of a class. We can’t determine the time of execution.
  • Executes by the CLR not by the object of a class.
  • There are no parameterized static constructors since it is handled by the CLR not by the object.
  • Time of execution might be at the loading of contained assembly.

Public Constructor

  • Public constructor runs every time when you create an object of the class using new 
  • Public one can be called from other assemblies as well.
  • Used to restrict a class to be instantiated and to be inherited.
  • Used whenever a class contains only static members.

Internal Constructor
  • The internal constructor is only accessible to types in the same assembly
  • Internal is just another access modifier for the constructor above. It can also be private as well. This is exactly the same as access modifiers for other functions.

Example

    class ClassA
    {
        static ClassA()
        {
        }

        public ClassA()
        {
        }

        internal ClassA()
        {
        }
    }



Read More »
 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top