SQL Server: Common Interview Questions

1. What is the difference between Delete and Truncate in SQL Server?
2. What is trigger? Write syntax of trigger.
3. What is the difference between “char” and “varchar”?
4. What is function in Sql server? How many types of functions?
5. What is stored procedure? Write syntax of stored procedure?
6. How parameter is passed to sql function and how call a function?
7. What is input and output parameter in stored procedure? How it works?
8. Write syntax to create a table.
9. What is view? What is the difference between table and view?
10. What is constraint? Tell different type of constraint and its functionality.
11. Discuss different type of joining. How it works?
12. How left outer joining works?
13. How inner join works? Give an example.
14. How outer join works? Give an example.
15. How right outer join works? Give an example.
16. How full outer join works? Give an example?
17. How Cross Join works? Give an example?
18. Which one faster – Inner join or Cross Join?
19. How do you move all data to another table using query?
20. Find out the maximum salary from a salary table?
21. How do you find number of employee from employee table?
22. What is commit and rollback? How it works?
23. What do you know about transaction block? How it works?
24. How primary key constraints, foreign key constraints and default constraints works?
25. How not null constraints, check constraints, rules constraint and unique constraints works?
26. What are DDL and DML? Give example.
27. How distinct keyword works?
28. What do you know about sql wildcard? Give example.
29. How to select top 10 employees from employee table?
30. How “between” and “in” operator works?
31. Union / Union all, requirement and difference.
32. What is sub query? Give an example.
33. How “having” clause works?
34. How “group by” clause works?
35. Why “Where” clause is needed?

Recovering SQL Server Database from Suspect Mode

Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server

Reason for database to go into suspect mode:

1.    Data files or log files are corrupt.

2.    Database server was shut down improperly

3.    Lack of Disk Space

4.    SQL cannot complete a rollback or roll forward operation.

How to recover database from suspect mode:

1.    Change the status of your database. Suppose database name is “BluechipDB”

          EXEC sp_resetstatus '';

            Example: EXEC sp_resetstatus 'BlueChipDB'

2. Set the database in “Emergency” mode

ALTER DATABASE <Database Name> SET EMERGENCY;

Example: ALTER DATABASE BlueChipDB SET EMERGENCY

3. Check the database for any inconsistency

DBCC CHECKDB('');

Example: DBCC checkdb('BlueChipDB')

4. If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Example: ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

5. For safety, take the backup of the database.

6. Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

Example: DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

7. Finally, bring the database in MULTI USER mode

ALTER DATABASE <Database Name> SET MULTI_USER;

ALTER DATABASE [BlueChipDB]  SET MULTI_USER

8. Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

Install SQL Server 2008 R2 – Step by Step

We often fall problem to install SQL Server 2008 R2. Here is the step by step procedure to install SQL Server 2008 R2. Microsoft .NET Framework 3.5 SP1 is the pre-requisite for SQL Server 2008 R2. If it is not installed in your computer, install it before proceed to the setup procedure.

Step 1: Run DVD of SQL Server 2008 R2 or Open DVD and click setup.exe

Step 2: Select Installation and Click New installation or add features to an existing installation.

Step 3: After completing pre installation check, press Ok.

Step 4: Type Product Key and click “Next”.

Step 5: Select License Term and click “Next”.

Step 6: Click “Install” in the following screen.

Step 7: Setup performed Support Rules Validation. If no error found, click “Next”. Turn off your windows firewall from control panel to eliminate warning of windows firewall of following screen.

Step 8: Select Setup Role – SQL Server Feature Installation. Click Next.

Step 9: Select Features or Click “Select All” and click “Next”.

Step 10: Click “Next” of Installation Rule windows.

Step 11: Select Default Instance for simplicity and click “Next”.

Step 12: Click “Next” after confirming disk space requirement.

Step 13: In the Server Configuration Screen click “Use the same account for all SQL Server Services”

Step 14: In the Pop up screen type user name with domain name and password.  Then click Ok.

Step 15: Click “Next” in the Server Configuration Screen.

Step 16: Select Mixed Mode, type password and confirm password. Click “Add Current user” and click Next in Database Engine Configuration Screen.

Step 17:  Click “Add Current User” & “Next” in Analysis Service Configuration Screen.

Step 18: Select Native Mode & Click Next in Reporting service configuration screen.

Step 19: Click “Next” in error reporting screen.

Step 20: Click “Next” in the following screen.

Step 21: Click “Install” in the following screen.

Installation progressing……

Step 22: Complete Installation. Click Close button.

Simple Queries in SQL Server

•  Get Column Name, Data Type and Data Length of a table. Here hrm_employee is a table name.

SELECT COLUMN_NAME 'Column Name',

DATA_TYPE 'Data Type',

CHARACTER_MAXIMUM_LENGTH 'Maximum Length'

FROM information_schema.columns

WHERE TABLE_NAME = 'hrm_employee'

After executing the query, you will see all column name of hrm_employee table with data type and length.

Create, Drop, Insert, Update, Delete and Select – The Basic SQL

This topic for those who are very beginner of SQL. Here I will show some basic SQL by Microsoft SQL Server.

1.       Create a table: Here I created a table name hrm_employee. Here emp_gid is int type, primary key and identity. Identity means its value will increment automatically by the sql server. You don’t need to insert it’s value.

--Create Table : hrm_employee---

CREATE TABLE hrm_employee

(

      emp_gid INT PRIMARY KEY IDENTITY,

      emp_fullnm VARCHAR(100),

      emp_nicknm VARCHAR(50),

      emp_designation VARCHAR(100)

)

2.       Insert data in hrm_employee table: In this occasion, I have inserted 3 data i.e 3 rows. If you see in closer look, you will see that I didn’t insert any value of emp_gid because it increments its value automatically because it is an identity column.

---Insert data ito table: hrm_employee----

-- Insert data i.e. row-1 ---

INSERT INTO hrm_employee

(

      emp_fullnm

      , emp_nicknm

      , emp_designation

)

VALUES

(

      'Md. Mahedee Hasan'

      , 'Mahedee'

      , 'Senior Software Engineer'

)    

-- Insert data i.e. row-2 ---

INSERT INTO hrm_employee

(

      emp_fullnm

      , emp_nicknm

      , emp_designation

)

VALUES

(

      'Md. Asrafuzzaman'

      , 'Emon'

      , 'Senior Software Engineer'

)    

-- Insert data i.e. row-3 ---

INSERT INTO hrm_employee

(

      emp_fullnm

      , emp_nicknm

      , emp_designation

)

VALUES

(

      'Md. Khondakar Enamul Haque'

      , 'Rony'

      , 'Broadcast Engineer'

)    

4. Infromation Retrive: Get all information of hrm_employee. Here I showed two ways to retrive data and I suggest to use second one. Second one is best practiced.

--Get all data from hrm_employee--

SELECT * FROM hrm_employee

--or

SELECT emp_gid, emp_fullnm, emp_nicknm, emp_designation FROM hrm_employee

5.  Update hrm_employee: Is very easy to update a row or multiple rows against a condition. Lets look on the following query.

--update data from table: hrm_employee --

UPDATE hrm_employee

SET emp_fullnm = 'M.K. Enamul Haque'

, emp_designation = 'Senior Broadcast Engineer'

WHERE emp_gid = 3

6. Delete Information: If you want to delete information from hrm_employee against a condition follow the query.

---Delete data from table: hrm_employee---

DELETE FROM hrm_employee WHERE emp_gid = 3

7. Drop Table: Drop table means remove table for database. Use the query to drop hrm_employee table.

---Drop table i.e remove table from Database--

DROP TABLE hrm_employee

How to find the size of all tables in SQL Server database

SQL Server gives you everything its stored procedure sp_spaceused. Unfortunately this SP does not support iterating over all tables in a database, so we needed to leverage another (undocumented) Stored Procedure sp_msForEachTable.


SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- Find DB size.
EXEC sp_spaceused

-- Create a table to counts row and sizes.
CREATE TABLE #tbl
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #tbl EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT * FROM #tbl

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #tbl

DROP TABLE #tbl

After executing this, you will get the size of all tables in your database.