Pages

Friday 30 March 2012

SQL SERVER - Different Ways To Identify Structure of a Table

We can know the structure of the table to extract the Column names, types, max length, etc. using following different methods.

In new query analyser window type the name of a table, highlight the name of the table and then press ALT + F1

EXEC SP_HELP 'TableName'

EXEC SP_COLUMNS 'TableName'

SELECT * FROM information_schema.columns WHERE table_name = 'TableName'


Generate SQL Script option from Enterprise Manager or Management Studio
On the Choose Objects page, Right click on database name -> select option 'Tasks' -> Generate Scripts select the objects to be included in the script.

Tuesday 27 March 2012

SQL SERVER - STUFF() Function


The STUFF function inserts a given string into another string. This function deletes a specified length of characters from the first string at the start position and then inserts the second string into the first string at the start position.

General Syntax:
STUFF ( CharacterExpression , StartPos , length ,CharacterExpression )

Example:
SELECT STUFF('FunctionStuff()', 1, 8, 'ExampleOf')
GO

The result of this query is: 
-----------------------
ExampleOfStuff()

CharacterEexpression
It is an expression of character data.  CharacterEexpression can be a constant, variable, or column of either character or binary data.

StartPos  
Is an integer/bigint value that specifies the location to start deletion and insertion.

length  
Is an integer/bigint that specifies the number of characters to delete.

It returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.

The use of this function can be found to get Comma Separated Values

SQL SERVER - Find Database File and Log Path

I need to find the place where I have all the .mdf and .ldf files are stored. The following is the simple SQL query to find out where .mdf and .ldf files are stored. This list helps to find database file and log path.

SELECT name, physical_name AS current_file_location
FROM sys.master_files

Following is the output for the files used by SQL Server instance.















For more details, please visit: http://msdn.microsoft.com/en-us/library/ms186782.aspx

Sunday 18 March 2012

SQL SERVER - Second Highest Salary Of Employee From Each Department


Using DENSE_RANK()  function we can get second highest salary from an Employee table for each department. The following is the solution to get 2nd highest salary from Employee table for each department:  

DECLARE @Employees TABLE
(
EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Barry','IT', 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Jones','IT', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Billy','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Ralph','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Aaron','Finance', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Barney','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Callum','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Oliver','Finance', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Anderson','HR', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Martin','HR', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Garcia','HR', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Caine','HR', 10000)


-- Find the second highest salary of employee from each department

;WITH CTE AS

(

   SELECT DENSE_RANK() OVER( PARTITION BY Department ORDER BY Salary DESC ) as [Rank],

   EmployeeName,
   Department,
   Salary
   FROM @Employees
)
SELECT
x.EmployeeName,
x.Department,
x.Salary
FROM CTE x
WHERE x.[Rank]  = 2
ORDER BY x.Salary
The output of the above query is: 

Second highest salary from each department


Saturday 10 March 2012

SQL SERVER - Find Second Highest Salary Of Employee



How to get second highest salary from an Employee table, the following solution is to get 2nd highest salary from Employee table:  


DECLARE @Employees TABLE
(
EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Barry','IT', 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Jones','IT', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Billy','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Ralph','IT', 50000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Aaron','Finance', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Barney','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Callum','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Oliver','Finance', 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Anderson','HR', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Martin','HR', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Garcia','HR', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('Caine','HR', 10000)

-- Find the second highest salary
;WITH CTE AS
(
   SELECT DENSE_RANK() OVER( ORDER BY Salary DESC ) as [Rank],
   EmployeeName,
   Department,
   Salary
   FROM @Employees
)
SELECT
x.EmployeeName,
x.Department,
x.Salary
FROM CTE x
WHERE x.[Rank]  = 2
ORDER BY x.Salary

The output of the above query is: 


Second Highest Salary Of Employee









In this SQL query I have used the DENSE_RANK() function,  this function will assign rank continuously and it will always return consecutive integers. It will assign rank to the records as per the condition used in <order by> clause for a partition without any gaps in the ranking values. The ranking values will be same as of RANK() i.e. if the <order by> condition satisfies for more than one record then it will assign the same rank to each record that satisfies the same condition.


When DENSE_RANK() function executes, it will start assigning ranks from the first record starting from the integer value 1 to each record, until it got more than one record satisfying same condition for the rank i.e. if more than one record ties for a rank value, each tied records will be assigned same rank. In DENSE_RANK () the rank preserve its values even if there are more than one record ties for the same rank, but in  RANK () function this is not the case it does not always return consecutive integers.


By using DENSE_RANK(), we can get list of all employees getting Nth salary from the employee table.