This is new feature to Sql Server 2005
Why we should use Common table expression :-
1. Can use multiple sub queries in user friendly manner
2. Avoid complexity
3. Initially define the sub query and after call this sub query again and again in base query multiple time like a table name 
1. DO consider using asynchronous controllers for long running requests.
ASP.NET’s threading pool has a default limit of 12 concurrent worker threads per CPU. When the requests overload the server’s ability to process these requests, a queue is built up of requests. For example, any request which takes a considerable amount of time waiting for external resources, such as database or large file operations. These external requests block the thread they occupy for the entire wait period. When this queue gets too large (5000 requests pending), the server starts responding with 503 (server too busy) errors.
In ASP.NET 4 the number of concurrent threads is set by default to 5000. While it is possible to increase the default limits, there is a better way to mitigate long running requests from tying up threads, modifying the long running requests to run asynchronously. ASP.NET MVC enables you to implement asynchronous controllers for this purpose. For more information about how to implement an asynchronous controller, see Using an Asynchronous Controller in ASP.NET MVC.
2. DO use an OutputCache filter for static pages.
Use OutputCache attribute when you are returning less frequently updated data; a good candidate may be your home page. You can use this technique for both HTML and JSON data types. When using it, only specify the cache profile name; do not specify anything else. If you need to fine tune caching, use the output cache section of the Web.config file.
For example, the OutputCache attribute is attached to Dashboard action method in the following code.
[AcceptVerbs(HttpVerbs.Get), OutputCache(CacheProfile = "Dashboard")]
public ActionResult Dashboard(string userName, StoryListTab tab, OrderBy orderBy, int? page)
{
...
}
In the Web.config file, the duration is fine tuned to 15 seconds.
3. DON'T overuse session, instead use TempData for short lived (intra-request) storage.
4. DO consider partial page updates using AJAX for reducing bandwidth.
5. Performance Recommendations
Performance is a multi-faceted problem for web-sites, as a myriad of bottlenecks can affect performance including:
Database
--------
Inefficient queries
Incorrectly placed indexes
Non-normalized design
Bandwidth problems
------------------
Large request size (affected by individual large images, .css, .js, .html, etc.)
Content that references many other items, such as multiple script, CSS, or image files
Slow connection speed
Processing power
----------------
Server: expensive operations
Client: poorly written javascript
6. DO use ASP.NET special resource folders and resource files.
While writing your web pages add an ASP.NET project folder for globalized content (App_GlobalResources) and for localized content for a given view (App_LocalResources). In each of these folders, you should add a resource (.resx) file that you should name according to the controller name. In other words, if your controller is named SubmissionPipeline, the resource file should be named SubmissionPipeline.resx.
7. Localization and Globalization Recommendations
Globalization is the process of making a product multi-lingual, where localization is the process of adapting a global product for a particular language and country. To develop a web application that supports globalization and localization, keep at least one rule in mind. Do not use hard-code strings in views.
8. DO guard against common attack vectors.
Website security needs to concern all web developers writing enterprise class websites and services. There are a host of well known attack vectors that you should know about. These attack vectors include (but are not limited to):
Cross-site scripting (XSS) attacks
SQL injection
Cross-site Request Forgery (XSRF)
Improperly implementing model binding
Explain using family twin scenario:
These are the conditions(in dbo.Person table- Self join with multiple conditions) -
1. different personID so have to use not equal both two person ids
2. twins mother and father same
3. and date of birth not equal to null
Join with the same table again and again recursively. Following images illustrate this joining concept using family scenario. 
we cannot use (=) to evaluate some value to null
Have to use IS NULL
**** Coalesce Function ***
Ex :
Select coalesce(null, 1+null, 1+2, 'xx') -> output=3
