Useful Sites

Design Patterns		Visual Studio shortcut keys
http://dofactory.com/Patterns/Patterns.aspx You can find most of the design patterns with C# examples here. Design patterns are grouped by the types and the names are provided as links so that we can easily move around.		http://dofactory.com/ShortCutKeys/ShortCutKeys.aspx You are familiar with many of Visual Studio's shortcut keys, but not all of them. Here is a handy reference that can make your .NET lifestyle easier and a lot more productive. The 'must-know' shortcut keys are high-lighted.

C# Coding Standards - and Naming Conventions		Database Connectionstrings
http://dofactory.com/reference/csharp-coding-standards.aspx Here are our C# coding standards, naming conventions, and best practices. Use these in your own projects and/or adjust these to your own needs.		http://dofactory.com/Connect/Connect.aspx Do you have a hard time remembering database connection strings? You are not alone! Here is an easy-to-use reference of connection strings for numerous databases and data stores.

e-Books From Packtpub

http://www.packtpub.com/support You can find lot of ebooks here like AJAX, JQuery, .Net, etc. You have to give your email id. The site will automatically forward the selected ebook to your emailid.

Sql Server Performance Tuning

Sql Server Performance Tuning

 

Sql Server stored procedure performance tuning
http://www.sql-server-performance.com/tips/stored_procedures_p1.aspx

 

Stored procedure performance optimization
http://bytes.com/topic/sql-server/answers/83488-stored-procedure-performance-optimization

 

SQL Server 2005: SQL Query Tuning (By Microsoft Tech Ed)
http://download.microsoft.com/documents/australia/teched2005/hol/HOL076.pdf

Other sites
http://www.mssqltips.com/category.asp?catid=9

2nd Highest Salary

Method 1

Correlated sub queries are more generalized to find n'th highest or nth lowest salary.


To find nth highest salary

SELECT * FROM Employee E1 WHERE n-1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

To find 2nd highest salary

SELECT * FROM Employee E1 WHERE 1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

To find 2nd lowest salary

SELECT * FROM Employee E1 WHERE 1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

To find nth lowest salary

SELECT * FROM Employee E1 WHERE n-1 =

(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

Method 2
 
HIGHEST 2nd Salary :


;WITH CTEs

AS(SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC) 'Rank',* FROM EMP)

SELECT Names,Salary FROM CTEs WHERE Rank=2

LOWESE 3Rd Salary :


;WITH CTEs

AS(SELECT DENSE_RANK() OVER(ORDER BY SALARY) 'Rank',* FROM EMP)

SELECT Names,Salary FROM CTEs WHERE Rank=3

Note: Desc is removed.