SQL Server -Find Nth Highest Salary


Problem: Find Nth highest salary in Minimal time.

Solution: 

Interviewer often asks to  find Nth Highest Salary in an interview.Many solutions are available on internet to do so.Couple of  commonly used solutions are Solution 1 and Solution 2

All these solutions are bit slow.Using CTE you can find the Nth highest salary very efficiently and impress your interviewer. 

  • Create a sample table and  insert sample data in it

CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘pds’, 10000)
INSERT INTO #T1 VALUES (2, ‘vicky’, 10000)
INSERT INTO #T1 VALUES (3, ‘divyan’, 30000)
INSERT INTO #T1 VALUES (4, ‘deepak’, 40000)
INSERT INTO #T1 VALUES (5, ‘anu’, 50000)
INSERT INTO #T1 VALUES (6, ‘jai’, 60000)
INSERT INTO #T1 VALUES (7, ‘abhi’, 20000)
INSERT INTO #T1 VALUES (8, ‘disha’, 40000)
INSERT INTO #T1 VALUES (9, ‘anika’, 60000)
INSERT INTO #T1 VALUES (10, ‘vijay’, 50000)

  • My Version of Query Using CTE

declare @n smallint;

set @n =4

;with cte as (SELECT name,salary,
                     dense_rank() OVER( ORDER BY salary DESC) AS rowid
              FROM   #t1
              )

SELECT *
FROM   cte
WHERE  rowid = @n

  • Comparison stats with other Solutions

SQL Server -Window XP useful commands for DBA


run>msinfo32

It gets the system information like OS,version name,components and many more.

run>sqlservermanager11.msc

Opens the SQL server configuration manager if the latest SQL server installed on the machine is SQL 2012

run>sqlservermanager10.msc

open the SQL server configuration manager if the latest SQL server installed on the machine is SQL 2008

run>compmgmt.msc

It opens the computer management window which in turn used for user management,event viewer,Disk management etc.

run>inetmgr

It opens the IIS window which int-run used for managing FTP,Web sites and SMTP

run>cmd>osql -L

It fetches the list of all the SQL server installed on the current network.

run>cmd>netstat -na|find "1433"

Finds the status of the 1433 port

run>c:\windows\system32\drivers\etc\hosts

The host file path.In case client SSMS is not able to connect to the SQL server using the server name but can connect using the IP address then try making an entry(associate IP address with the Server name) into this file.

run>secpol.msc

open the local security setting window which is used to set security options,IP security,Password policy etc.

SQL Server -Find currently Running jobs


If you want to find the currently ruining jobs then following queries will help you.

List All currently running SQL agent jobs:

exec msdb..sp_help_job @execution_status=1

List extra information about you job:

exec msdb..sp_help_job @Job_name=<yourjobname>

possible values of @execution_status

Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retires.
4 Idle.
5 Suspended.
7 Performing completion actions.

Links:

http://msdn.microsoft.com/en-us/library/ms186722(SQL.105).aspx

SQL Server 2005 -Trouble Shooting Client To Server Connection


Applies on : SQL Server 2005 and above

Tested on:SQL Server 2005

1) Check Server Property

1.1 Right Click On Server—>Properties—>Connections—>Select Allow remote Connections to

Server

If Problem Still Exists Then

 1.2. Right Click on Server–>Properties–>Security–>Select SQL Server and Windows

Authentication Mode.

2) Security   

      Check the Login (Server level Permission) and User (Database Level Permission)  Access.

3) SQL Server Configuration

3.1  Click on Start–>All Programs–>SQL Server 2005 –> Configuration Tools–> Select

SQL Server  Configuration Manager–>Check for Services running and for SQL Browser running.

3.2  Click on Start–>All Programs–>SQL Server 2005 –> Configuration Tools–> Select

SQL Server  Configuration Manager–>Select SQL Server Network Configuration

–>Click on Protocols For Instance

* TCP/IP — Enabled

* IP Address — Server Address

* TCP/IP Port — 1433

4) Firewall Configuration

Run Firewall.Cpl–>Click on Exception Tab

4.1)Add SQL Server Port

Click on Add Port–>Add Name as SQL Server–>Add Port as 1433

 4.2) Add SQL Server Program

Click on Add Program–>  Browse C:\Program Files\Microsoft SQL

Server\90\Shared\sqlbrowser.exe

4.3)Add SQL Server Program

Click on Add Program–>Browse C:\Program Files\Microsoft SQL

Server\MSSQL\MSSQL\Bin\sqlservr.exe