Name of the blog

Short description of the blog

SQL Server 2008 R2: What's New

With the May 2010 release of SQL Server 2008 R2, many database professionals wondered why Microsoft didn’t increment the product’s name to SQL Server 2010. Indeed, there is a quite a bit of new functionality in this latest release of SQL Server which, among other things, allows advanced interaction with Office 2010. 

Here’s the rundown on some of the new features available in SQL Server 2008 R2:

  • PowerPivot for Office 2010 allows you to work with large amounts of data in Excel by interacting directly with a SQL Server 2008 R2-backed SharePoint 2010 installation.
  • SQL Server Express has been upgraded to allow databases of up to 10GB in size (from a prior 4GB limit).
  • SQL Server Enterprise Edition now supports only 8 CPUs.
  • SQL Server 2008 R2 sysprep utility allows you to image a stand-alone instance of SQL Server for replicating installations.
  • Database mirroring has been enhanced to improve performance and allow automatic recovery from corrupted pages.
  • On-disk compression is now available for tables, indexes and indexed views, conserving disk space for infrequently used data.
  • Backup compression reduces the size of backups by up to 60%
  • The sparse column format is now available and optimizes storage for columns that contain at least 20% NULL values.
  • The new wide table format supports up to 30,000 columns.
  • Multiserver administration technology allows you to collect information and manage multiple servers in a central location.

Additionally, there are changes to the editions and pricing for SQL Server 2008 R2. You’ll want to be sure to review that information before deciding whether an upgrade is appropriate for your environment. 

Is an upgrade to SQL Server 2008 R2 appropriate for your enterprise? The answer will depend upon whether you need these features or are interested in those offered by the new Parallel Data Warehouse edition. If that’s the case, or you’re upgrading from SQL Server 2005, this may be the right time to pull the trigger on an upgrade. Otherwise, you may wish to sit this one out.

Difference between static class and singleton pattern

  • Singleton object stores in Heap but, static object stores in stack
  • We can clone the object of Singleton but, we can not clone the static class object
  • Unlike static classes, we can use singletons as parameters or objects.
  • we can implement interface with Singleton class but not with Static class.
  • Singleton class follow the OOP(object oriented principles) but not static class
  • Singleton class maintains state. It is thread safe.
  • we can dispose the objects of a singleton class but not of static class

The difference between the Singleton and Static is Singleton Class can have value when Class object instantiated between server and client, such a way if three client want to have a shared data between them Singleton can be used. Static are always just shared and have no instance but multiple references.

SQL Interview Questions with Answers

What is the difference between inner and outer join? Explain with example.

Inner Join

Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition (predicate).

Inner join returns rows when there is at least one match in both tables

If none of the record matches between two tables, then INNER JOIN will return a NULL set. Below is an example of INNER JOIN and the resulting set.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE emp.dept_id = dept.id
DepartmentEmployee
HR Inno
HR Privy
Engineering Robo
Engineering Hash
Engineering Anno
Engineering Darl
Marketing Pete
Marketing Meme
Sales Tomiti
Sales Bhuti
Outer Join

Outer Join can be full outer or single outer

Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).

Notice in our record set that there is no employee in the department 5 (Logistics). Because of this if we perform inner join, then Department 5 does not appear in the above result. However in the below query we perform an outer join (dept left outer join emp), and we can see this department.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)
DepartmentEmployee
HR Inno
HR Privy
Engineering Robo
Engineering Hash
Engineering Anno
Engineering Darl
Marketing Pete
Marketing Meme
Sales Tomiti
Sales Bhuti
Logistics  

The (+) sign on the emp side of the predicate indicates that emp is the outer table here. The above SQL can be alternatively written as below (will yield the same result as above):

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept LEFT OUTER JOIN EMPLOYEE emp
ON dept.id = emp.dept_id  

What is the difference between JOIN and UNION?

SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.

UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.

SELECT * FROM EMP1
UNION
SELECT * FROM EMP2;

What is the difference between UNION and UNION ALL?

UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows, even if one or more rows are duplicated to each other.

In the following example, I am choosing exactly the same employee from the emp table and performing UNION and UNION ALL. Check the difference in the result.

SELECT * FROM EMPLOYEE WHERE ID = 5
UNION ALL
SELECT * FROM EMPLOYEE WHERE ID = 5
IDMGR_IDDEPT_IDNAMESALDOJ
5.0 2.0 2.0 Anno 80.0 01-Feb-2012
5.0 2.0 2.0 Anno 80.0 01-Feb-2012
SELECT * FROM EMPLOYEE WHERE ID = 5
UNION 
SELECT * FROM EMPLOYEE WHERE ID = 5
IDMGR_IDDEPT_IDNAMESALDOJ
5.0 2.0 2.0 Anno 80.0 01-Feb-2012

What is the difference between WHERE clause and HAVING clause?

WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

To understand this, consider this example. 
Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:

SELECT * FROM DEPT WHERE ID > 3
IDNAME
4 Sales
5 Logistics

Next, suppose we want to see only those Departments where Average salary is greater than 80. Here the condition is associated with a non-static aggregated information which is “average of salary”. We will need to use HAVING clause here:

SELECT dept.name DEPARTMENT, avg(emp.sal) AVG_SAL
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)
GROUP BY dept.name
HAVING AVG(emp.sal) > 80
DEPARTMENTAVG_SAL
Engineering 90

As you see above, there is only one department (Engineering) where average salary of employees is greater than 80.

What is the difference among UNION, MINUS and INTERSECT?

UNION combines the results from 2 tables and eliminates duplicate records from the result set.

MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.

INTERSECT operator returns us only the matching or common rows between 2 result sets.

To understand these operators, let’s see some examples. We will use two different queries to extract data from our emp table and then we will perform UNION, MINUS and INTERSECT operations on these two sets of data.

UNION

SELECT * FROM EMPLOYEE WHERE ID = 5
UNION 
SELECT * FROM EMPLOYEE WHERE ID = 6
IDMGR_IDDEPT_IDNAMESALDOJ
5 2 2.0 Anno 80.0 01-Feb-2012
6 2 2.0 Darl 80.0 11-Feb-2012

MINUS

SELECT * FROM EMPLOYEE
MINUS
SELECT * FROM EMPLOYEE WHERE ID > 2
IDMGR_IDDEPT_IDNAMESALDOJ
1   2 Hash 100.0 01-Jan-2012
2 1 2 Robo 100.0 01-Jan-2012

INTERSECT

SELECT * FROM EMPLOYEE WHERE ID IN (2, 3, 5)
INTERSECT
SELECT * FROM EMPLOYEE WHERE ID IN (1, 2, 4, 5)
IDMGR_IDDEPT_IDNAMESALDOJ
5 2 2 Anno 80.0 01-Feb-2012
2 1 2 Robo 100.0 01-Jan-2012

What is Self Join and why is it required?

Self Join is the act of joining one table with itself.

Self Join is often very useful to convert a hierarchical structure into a flat structure

In our employee table example above, we have kept the manager ID of each employee in the same row as that of the employee. This is an example of how a hierarchy (in this case employee-manager hierarchy) is stored in the RDBMS table. Now, suppose if we need to print out the names of the manager of each employee right beside the employee, we can use self join. See the example below:

SELECT e.name EMPLOYEE, m.name MANAGER
FROM EMPLOYEE e, EMPLOYEE m
WHERE e.mgr_id = m.id (+)
EMPLOYEEMANAGER
Pete Hash
Darl Hash
Inno Hash
Robo Hash
Tomiti Robo
Anno Robo
Privy Robo
Meme Pete
Bhuti Tomiti
Hash  

The only reason we have performed a left outer join here (instead of INNER JOIN) is we have one employee in this table without a manager (employee ID = 1). If we perform inner join, this employee will not show-up.

How can we transpose a table using SQL (changing rows to column or vice-versa) ?

The usual way to do it in SQL is to use CASE statement or DECODE statement.

How to generate row number in SQL Without ROWNUM

Generating a row number – that is a running sequence of numbers for each row is not easy using plain SQL. In fact, the method I am going to show below is not very generic either. This method only works if there is at least one unique column in the table. This method will also work if there is no single unique column, but collection of columns that is unique. Anyway, here is the query:

SELECT name, sal, (SELECT COUNT(*)  FROM EMPLOYEE i WHERE o.name >= i.name) row_num
FROM EMPLOYEE o
order by row_num
NAMESALROW_NUM
Anno 80 1
Bhuti 60 2
Darl 80 3
Hash 100 4
Inno 50 5
Meme 60 6
Pete 70 7
Privy 50 8
Robo 100 9
Tomiti 70 10

The column that is used in the row number generation logic is called “sort key”. Here sort key is “name” column. For this technique to work, the sort key needs to be unique. We have chosen the column “name” because this column happened to be unique in our Employee table. If it was not unique but some other collection of columns was, then we could have used those columns as our sort key (by concatenating those columns to form a single sort key).

Also notice how the rows are sorted in the result set. We have done an explicit sorting on the row_num column, which gives us all the row numbers in the sorted order. But notice that name column is also sorted (which is probably the reason why this column is referred as sort-key). If you want to change the order of the sorting from ascending to descending, you will need to change “>=” sign to “<=” in the query.

As I said before, this method is not very generic. This is why many databases already implement other methods to achieve this. For example, in Oracle database, every SQL result set contains a hidden column called ROWNUM. We can just explicitly select ROWNUM to get sequence numbers.

How to select first 5 records from a table?

This question, often asked in many interviews, does not make any sense to me. The problem here is how do you define which record is first and which is second. Which record is retrieved first from the database is not deterministic. It depends on many uncontrollable factors such as how database works at that moment of execution etc. So the question should really be – “how to select any 5 records from the table?” But whatever it is, here is the solution:

 

In Oracle,

SELECT * 
FROM EMP
WHERE ROWNUM <= 5;

In SQL Server,

SELECT TOP 5 * FROM EMP;

Generic solution,

I believe a generic solution can be devised for this problem if and only if there exists at least one distinct column in the table. For example, in our EMP table ID is distinct. We can use that distinct column in the below way to come up with a generic solution of this question that does not require database specific functions such as ROWNUM, TOP etc.

SELECT  name 
FROM EMPLOYEE o
WHERE (SELECT count(*) FROM EMPLOYEE i WHERE i.name < o.name) < 5
name
Inno
Anno
Darl
Meme
Bhuti

I have taken “name” column in the above example since “name” is happened to be unique in this table. I could very well take ID column as well.

In this example, if the chosen column was not distinct, we would have got more than 5 records returned in our output.

Do you have a better solution to this problem? If yes, post your solution in the comment.

What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?

ROWNUM is a pseudo column present in Oracle database returned result set prior to ORDER BY being evaluated. So ORDER BY ROWNUM does not work.

ROW_NUMBER() is an analytical function which is used in conjunction to OVER() clause wherein we can specify ORDER BY and also PARTITION BY columns.

Suppose if you want to generate the row numbers in the order of ascending employee salaries for example, ROWNUM will not work. But you may use ROW_NUMBER() OVER() like shown below:

SELECT name, sal, row_number() over(order by sal desc) rownum_by_sal
FROM EMPLOYEE o
nameSalROWNUM_BY_SAL
Hash 100 1
Robo 100 2
Anno 80 3
Darl 80 4
Tomiti 70 5
Pete 70 6
Bhuti 60 7
Meme 60 8
Inno 50 9
Privy 50 10

What are the differences among ROWNUM, RANK and DENSE_RANK?

ROW_NUMBER assigns contiguous, unique numbers from 1.. N to a result set.

RANK does not assign unique numbers—nor does it assign contiguous numbers. If two records tie for second place, no record will be assigned the 3rd rank as no one came in third, according to RANK. See below:

SELECT name, sal, rank() over(order by sal desc) rank_by_sal
FROM EMPLOYEE o
nameSalRANK_BY_SAL
Hash 100 1
Robo 100 1
Anno 80 3
Darl 80 3
Tomiti 70 5
Pete 70 5
Bhuti 60 7
Meme 60 7
Inno 50 9
Privy 50 9

DENSE_RANK, like RANK, does not assign unique numbers, but it does assign contiguous numbers. Even though two records tied for second place, there is a third-place record. See below:

SELECT name, sal, dense_rank() over(order by sal desc) dense_rank_by_sal
FROM EMPLOYEE o
nameSalDENSE_RANK_BY_SAL
Hash 100 1
Robo 100 1
Anno 80 2
Darl 80 2
Tomiti 70 3
Pete 70 3
Bhuti 60 4
Meme 60 4
Inno 50 5
Privy 50 5

Observer Pattern in ASP.NET

What is Observer Pattern ?

  • Observer Pattern is a Behavioural Pattern which allows one-to-many dependencies between objects so that when one object state changes, all dependent objects can be notified and updated automatically.
  • The Observer Pattern is used to implement communication between objects. Since the state of an object (we'll call it subject) is constantly changing, the Observer pattern allows us to define a one to many dependency between other objects (we'll call them observers) to communicate with them letting all the observers know that a subject has changed its state.
  • This allows us to notify all observers with a single mechanism.
  • The Observer Pattern is set up in the publisher/subscriber pattern, and is excellent for implementing distributed event handling systems and such. This pattern also goes a long way towards removing the coupling between the subject & observer with the use of the IObserver interface.
  • The observer design pattern defines a one to many dependency between an object and its dependents. The dependency is created in order to inform the dependents that the object changed its state and therefore the dependents can react to the change.
  • A very good example of such behavior is the blogging systems were subscribers are notified whenever a blogger published a new post.

http://www.c-sharpcorner.com/uploadfile/shinuraj587/observer-pattern-in-C-Sharp/

http://dotnet.dzone.com/articles/design-patterns-c-observer

http://msdn.microsoft.com/en-us/library/Ee817669(pandp.10).aspx

http://www.codeproject.com/Articles/42255/Observer-Pattern-in-ASP-NET

http://wiki.asp.net/page.aspx/490/observer-pattern/

http://msdn.microsoft.com/en-us/library/ms998543.aspx

http://msdn.microsoft.com/en-us/library/ee850490.aspx

What is difference between Abstraction and Encapsulation

Abstraction 

Abstraction allows us to represent complex real world in simplest manner. It is process of identifying the relevant qualities and behaviors an object should possess, in other word represent the necessary feature without representing the back ground details. Abstraction is a process of hiding work style of an object and showing only those information which are required to understand the object. Abstraction means putting all the variables and methods in a class which are necessary.

Encapsulation

It is a process of hiding all the internal details of an object from the outside real world. The word Encapsulation, like Enclosing into the capsule. It restrict client from seeing its internal view where behavior of the abstraction is implemented. In Encapsulation, generally to hide data making it private and expose public property to access those data from outer world. Encapsulation is a method for protecting data from unwanted access or alteration. Encapsulation is the mechanism by which Abstraction is implemented.

Difference between Abstraction and Encapsulation

Abstraction is a process. It is the act of identifying the relevant qualities and behaviors an object should possess. Encapsulation is the mechanism by which the abstraction is implemented.

Abstraction  Encapsulation
Abstraction solves the problem in the design level. Encapsulation solves the problem in the implementation level.
Abstraction is used for hiding the unwanted data and giving only relevant data. Encapsulation is hiding the code and data into a single unit to protect the data from outer world.
Abstraction is set focus on the object instead of how it does it. Encapsulation means hiding the internal details or mechanics of how an object does something.
Abstraction is outer layout in terms of design. 
For Example: - Outer Look of a iPhone, like it has a display screen.
Encapsulation is inner layout in terms of implementation.
For Example: - Inner Implementation detail of a iPhone, how Display Screen are connect with each other using circuits

Sealed Keyword in DOT NET

sealed Keyword

  • The sealed modifier can be applied to classes, instance methods and properties.
  • A sealed class cannot be inherited.
  • A sealed method overrides a method in a base class, but itself cannot be overridden further in any derived class.
  • When applied to a method or property, the sealed modifier must always be used with override.

 

Use the sealed modifier in a class declaration to prevent inheritance of the class, as in this example:

sealed class SealedClass 
{
    public int x; 
    public int y;
}
  • It is an error to use a sealed class as a base class or to use the abstract modifier with a sealed class.
  • Structs are implicitly sealed; therefore, they cannot be inherited.

Example

// cs_sealed_keyword.cs
using System;
sealed class SealedClass
{
    public int x;
    public int y;
}

class MainClass
{
    static void Main()
    {
        SealedClass sc = new SealedClass();
        sc.x = 110;
        sc.y = 150;
        Console.WriteLine("x = {0}, y = {1}", sc.x, sc.y);
    }
}

Output :

x = 110, y = 150

 

In the preceding example, if you attempt to inherit from the sealed class by using a statement like this:

class MyDerivedC: SealedClass {} // Error

you will get the error message:

'MyDerivedC' cannot inherit from sealed class 'SealedClass'.

 

Reference

sealed (C# Reference)

What is difference between static class and sealed class

Sealed classes:

  1. Can create instances, but cannot inherit
  2. Can contain static as well as nonstatic members.

 

Static classes:

  1. Can neither create their instances, nor inherit them
  2. Can have static members only.

 

What is difference between abstract class and an interface

Feature

Interface

Abstract class

Multiple inheritance

A class may inherit several interfaces.

A class may inherit only one abstract class.

Default implementation

An interface cannot provide any code, just the signature.

An abstract class can provide complete, default code and/or just the details that have to be overridden.

Access Modfiers An interface cannot have access modifiers for the subs, functions, properties etc everything is assumed as public An abstract class can contain access modifiers for the subs, functions, properties

Core VS Peripheral

Interfaces are used to define the peripheral abilities of a class. In other words both Human and Vehicle can inherit from a IMovable interface.

An abstract class defines the core identity of a class and there it is used for objects of the same type.

Homogeneity

If various implementations only share method signatures then it is better to use Interfaces.

If various implementations are of the same kind and use common behaviour or status then abstract class is better to use.

Speed

Requires more time to find the actual method in the corresponding classes.

Fast

Adding functionality (Versioning)

If we add a new method to an Interface then we have to track down all the implementations of the interface and define implementation for the new method.

If we add a new method to an abstract class then we have the option of providing default implementation and therefore all the existing code might work properly.

Fields and Constants No fields can be defined in interfaces An abstract class can have fields and constrants defined

Cheap MLB Jerseys maillot de foot pas cher www.fotballdrakter.org