SQL SERVER - Refreshing the IntelliSense Cache

Today, I have created one table and tried to create a stored procedure to insert data into this new table in another tab, when I type INSERT INTO then the Intellisense drop down doesn't present me with my new table.

There are cases where the local cache used by IntelliSense becomes stale. Refreshing the cache is easy but not necessarily obvious.

There are two ways to refresh the cache:

1. Hit Ctrl+Shift+R
2. Go to Edit -> IntelliSense -> Refresh Local Cache and

If IntelliSense is not working still, then make sure you have IntelliSense enabled, to check this from the T-SQL Query editor window of current database,
1. Go to Tools -> Options -> Text Editor -> Transact-SQL -> General -> IntelliSense
2. Go to Query -> IntelliSense should be selected
3. Also verify that the T-SQL Editor does not launch in SQLCMD Mode, to check go to Tools -> Options -> Query Execution -> SQL Server -> General, make sure “By default, open new queries in SQLCMD mode” is unchecked.
or Go to Query ->  SQLCMD Mode  should not be selected


Still IntelliSense is not working then sounds like you have a corrupt installation, then re-installing the tools would be recommended, in case something was installed after the fact to change the binaries.


Also make sure you are connected to SQL Server 2008 Edition, because IntelliSense does not work with the previous versions of SQL Server.

SQL SERVER - Inline Variable Assignment in Sql Server 2008

In SQL 2005 variable assignment is done by:

DECLARE @CurrentDate_in_2005 DATETIME
SET @CurrentDate_in_2005 = GETDATE()
SELECT @CurrentDate_in_2005 AS CurrentDate_in_2005


But in SQL Server  2008 we can do it in one line:

DECLARE @CurrentDate_in_2008 DATETIME = GETDATE()
SELECT @CurrentDate_in_2008 AS CurrentDate_in_2008

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.

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

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

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.