how to get second max salary in sql

select min(sal) as sal from emp where sal   in 
(select distinct top 2 sal from emp with (nolock) order by sal desc ) 


---------------------------------

how to get the column names and corresponding datatypes in particular table using SQL Server

SELECT column_name 'Column Name',

data_type 'Data Type',

character_maximum_length 'Maximum Length'

FROM information_schema.columns

SQL Union Operator | SQL Union ALL Operator | Difference between Union and Union ALL Operators in SQL |

Union Operators are used to combine the result of two or more select queries into single result set.

SQL UNION Operator:

SQL Union Operator is used to combine the result of two or more select statement queries into single result set. The Union Operator is used to select only distinct values from two tables.

what is stored procedure in Sql server | what are the advantages of using stored procedures in sql server

A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure

Different Types of Indexes in SQL Server | Difference between Clustered Indexes and Non-Clustered Indexes in SQL Server

An index can be created in a table to increase the performance of application and we can get the data more quickly and efficiently. Let’s see an example to illustrate this point suppose now we are reading book in that I need to check the information for dbmanagement to get this information I need to search each page of the book because I don’t know in which page that word

Cursors in SQL Server

Cursors can be considers as named result sets which allow a user to move through each record one by one. SQL Server 2000 provides different types of cursors to support different type of scrolling options.  

SQL SERVER Ranking Functions - RANK, DENSE_RANK, NTILE, ROW_NUMBER SQL query to delete duplicate rows SQL Server CTE(Common Table Expression) and Recursive Queries CTE Recursive query for data hierarchy(Parent Child hierarchy) Different methods of SQL queries to insert data in tables SQL query to display all columns with datatypes for a given Table name SQL query to check two tables have identical data SQL query to search a string in database Schema SQL query to display total number of rows for each table in database SQL query to delete duplicate rows

Create a table EmpDtl1 with some duplicate rows as shown below to to understand different methods of delete duplicate rows

create table EmpDup(empid int,name varchar(20))

How To Delete a null record

The code below will show you how exactly you delete a row with a NULL value. You can not use =NULL but you have to use IS NULL

CREATE TABLE #TestDeleteNull (id INT identity, SomeDate DATETIME)
INSERT #TestDeleteNull VALUES(GETDATE())

Delete Duplicate rows from the table.

Delete Duplicate rows from the table.
Suppose there is a table called "EmployeeTable" which have some duplicate records.
There is a three way to delete the duplicate rows.First way to delete duplicate rows :

How To Copy Data and Structure of One Table To Another New Tables

select * into newtable from originaltable where 1=1

Write a SQL Query to find first day of month?

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

Write a query to convert all the letters in a word to upper case

SELECT UPPER('test')

Write a query to round up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.

Find duplicate rows in a table?

Find duplicate rows in a table? OR I have a table with one column which has many records which are not distinct. I need to find the distinct values from that column and number of times it’s repeated.

How to know how many tables contains empno as a column in a database?

Hi Friends ,i am writing this article  to know how many tables contains empno as a column in a database?

How To Find age from date of birth

Hi Friends i am writing this article to find the age from date of birth

Returning String Instead of NULL:

Consider a situation in which your database table contains NULL and you don't want to return NULL but some message. Like suppose you have a Person table and a Phone Table and a person does not have a phone number so we can easily return a message saying "No Phone Number Found" instead of returning a NULL.

How can I find the last day of the month?

Hi friends i am writing this article to find the last day of the month.I think it may useful to you.

What is a deadlock “GRANT” and “REVOKE’ statements in SQL Server

What is a deadlock in SQL Server?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.

Add default value to existing column (SQL)

In SQL Server there are two ways to add a column with a default value.



Add Default Value to Existing Column

Using SQL Data Definition Language (DDL) to Create Data Tables and Other Database Objects

Using the CREATE TABLE Statement to Create Tables

Tables are the primary structures used to hold data in a relational database. In a typical multi-user environment, the database administrator (dba) creates the tables that serve as the data stores for the organization's data. Users normally create their own temporary tables used to store data extracted from the main organizational tables.

What is meant cluster and Non cluster index?

A clustered index is a special type of index that reorders 
the way records in the table are physically stored. 
Therefore table can have only one clustered index. The leaf 
nodes of a clustered index contain the data pages.

Difference between HAVING and WHERE Clause

Answer in one line is : HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

Delete Duplicate Records – Rows

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

Find and Delete duplicate records in a table

Many times you can face problem of duplicate records in table.So How would you identify and delete duplicate records in a table?

Difference between Truncate and Delete in SQL

Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.

Difference between stored procedure and function

Fundamental difference between Stored procedure vs User Functions:

• Procedure may return none or more values.Function must always return one value either a scalar value or a table.
• Procedure have input,output parameters.Functions have only input parameters.
• Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
• Functions can be called from procedure.Procedures cannot be called from function.
• Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
• Transaction management possible in procedure but not in function.

 Below are few differences between Stored Procedure and Function

1) Stored procedure are compiled for first time and compiled format is saved and executes compiled code when ever it is called. But function is compiled and executed every time it is called.

2) Function must return a value but in stored procedure it is optional.

3) Function takes one input parameter it is mandatory but stored procedure may take o to n input parameters.

4) Functions can be called from select statement, but stored procedures can not be called from select statement.

5) We can build logic in functions and we can even break complex logic in to methods.

6) We can use try catch statements in stored procedures but in functions we can not use.

7) We can not use insert,delete,update and create statements in functions but in stored procedures we can use those statements.

8 ) Functions can have only input parameters but stored procedure can have input and out put parameters

A stored procedure is a program (or procedure) which is 
physically stored within a database. They are usually written in 
a proprietary database language like PL/SQL for Oracle database 
or PL/PgSQL for PostgreSQL. The advantage of a stored procedure 
is that when it is run, in response to a user request, it is run 
directly by the database engine, which usually runs on a separate
database server. As such, it has direct access to the data it 
needs to manipulate and only needs to send its results back to 
the user, doing away with the overhead of communicating large 
amounts of data back and forth.


User-defined function
=====================

A user-defined function is a routine that encapsulates useful 
logic for use in other queries. While views are limited to a 
single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

1>Procedure can return zero or n values whereas function can return one value which is mandatory.

2>Procedures can have input,output parameters for it whereas functions can have only input parameters.

3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.

4>Functions can be called from procedure whereas procedures cannot be called from function.

5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6>We can go for transaction management in procedure whereas we can't go in function.

7>Procedures can not be utilized in a select statement whereas function can be embedded in a select statement. 

Explain the different types of joins

There are 5 types of joins available in sql server

1) inner join
2) outer join( left outer join,Right outer join)
3) Full Join
4) Cross Join
5) Self Join

Write query to get date in dd/mm/yyyy format?

In SQL Server used Cast or Convert function to Format DateTime value or column into a specific date format.Both function are used to convert datetime to varchar or string.

CAST function Syntax: CAST(expression as data_type)

Let's convert current date time to varchar

select cast(getdate() as varchar)

SQL Server Integration Services

   Maintain DTS packages in SQL Server 2005

What if you've migrated to SQL Server 2005 and want to hold off upgrading your DTS packages to SSIS? In this tip, you'll see how to edit, maintain and develop DTS packages in SQL Server.

Development

   Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005

Understanding and working with date/time data types in SQL Server can be complicated. Learn the basics of working with DATETIME and SMALLDATETIME in SQL Server 2005, along with an overview of TIMESTAMP, a data type often confused with these two primary date/time data types.

security

   Granting permissions in SQL Server 2005

New security features in SQL Server 2005 make it easier to manage and grant SQL Server permissions on a more granular basis. Get an an overview of user-schema separation and granular server permissions, as well as a new function to help you identify which permissions are available to particular users.

Backup and recovery

   Selecting a SQL Server recovery model

Your SQL Server offers three recovery models for each database: full recovery, simple recovery and bulk-logged recovery. These determine how much data loss is acceptable in case of a failure, along with what types of backup and restore functions are allowed. Find out which option will suit you best when you are attempting to select a SQL Server recovery model.

Native SQL Server tools

  SQL Profiler: Features and setup in SQL Server 2005

SQL Profiler can be considered the best native SQL Server resource because, for one thing, it understands micro-level processing on any SQL Server. Get an introduction to SQL Profilerand its features and setup processes.

Performance tuning basics

 Update SQL Server table statistics for performance kick

There is plenty to learn about using auto and manual update commands and avoiding false statistics -- two of many techniques that will fine-tune SQL Server statistics and boost performance.

What is the differences in Sql server?

  Differences between varchar and nvarchar in SQL Server

The broad range of data types in SQL Server can sometimes throw people through a loop, especially when data types like varchar and nvarchar seem to be highly interchangeable. Learn how these two data types are stored differently, how to mix and match them and what changes were made to varchar and nvarchar in SQL Serve 2005r.

Benefits of Stored Procedures

Benefit	Explanation of benefit
Modular programming	You can write a stored procedure once, then call it from multiple places in your application.

Create a Table

1. Ensuring you have the right database expanded, right click on the "Tables" icon and select "New Table...":

SQL SERVER interview questions

1) General Questions of SQL SERVER

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables.