Some of the frequently asked Interview Questions in SQL Server are as follows:
1) What is SQL Server?
Microsoft SQL Server is a Relational Database Management System (RSBMS) produced by Microsoft. It's primary query language Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) used by both Microsoft and Sybase.
2) What is the difference between VARCHAR and NVARCHAR.
a) VarChar is a variable length string of ASCII characters, which take one Byte of space. Where as, NVarChar is a variable length string of UNICODE characters, which take two Bytes of space.
b) The maximum size of VarChar columns is 8,000 characters. Where as, the maximum size of NVarChar columns is 4,000 characters.
c) NVarChar requires 1 byte to represent a character. Where as, VarChar requires 2 bytes to represent a character.
c) NVarChar supports wider range of characters than VarChar.
3) How to query the database to get all the Table names?
SELECT * FROM information_schema.tables where Table_type='BASE TABLE';
Or
SELECT * FROM sysobjects WHERE type='u';
4) How to query the database to get all the Stored Procedure names?
SELECT * FROM sysobjects WHERE type='p';
5) How to query all the column names of a table?
SELECT * FROM syscolumns WHERE object_name(syscolumns.id)='Tblzone';
6) How to query all the column names, data types and length of a table?
SELECT syscolumns.name "Column Name", systypes.name "Type", syscolumns.length "Length" FROM syscolumns, systypes WHERE object_name(syscolumns.id)='TblStudent' AND systypes.usertype=syscolumns.usertype;
Or
SELECT * FROM INFORMATION_SCHEMA.columns where table_name='TblSupportCall'
7) How to select all the current databases in Sql Server?
SELECT name FROM master..sysdatabases
Or
SELECT CATALOG_NAME AS DataBaseName FROM INFORMATION_SCHEMA.SCHEMATA
Or
EXEC sp_databases
Or
EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName'
Or
EXEC sp_MSForEachDB 'Print ''?'''
8) Write and SQL query to retrieve the 2nd highest salary from the emp table.
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 2 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
Or
SELECT MIN(mark)
FROM student
WHERE mark IN(
SELECT TOP 3 mark
FROM student
ORDER BY mark DESC)
9) Write a query to get the name of the employee who is getting the highest salary from an employee table.
SELECT EmpName FROM Emp
WHERE Salary =(SELECT MAX(Salary) FROM Emp)
10) When do you get this error message "String or Binary data would be truncated"?
This error message appears when you try to insert a string with more characters than the column can maximal accommodate.
11) Which TCP/IP port does SQL Server run on?
SQL Server runs on port 1433 but we can also change it for better security.
12) What is SQL Inner Join?
Inner Join is used to retrieve only matching data from two or more table.
13) What is SQL Left Outer Join?
It retrieves all the records from left most table (irrespective of the condition specified) and retrieves only matching records from the right most table and assigns NULL for unmatched fields.
14) What is SQL Right Outer Join?
It retrieves all the records from right most table (irrespective of the condition specified) and retrieves only matching records from the left most table and assigns NULL for unmatched fields. (Opposite to LEFT OUTER JOIN).
15) What is SQL Full Outer Join?
It retrieves records from both the table (irrespective of the condition) and for unmatched fields it assigns NULL.
Thanks & Regards
Paritosh Mohapatra
Microsoft MVP (ASP.Net/IIS)
DotNetSpider MVM