40 - SQL SERVER Interview Questions

1. What is Database?

Ans. Database stores data.

2. What is Database Table?

Ans. It stores data in the form of Rows and Columns.

3. What is a Primary Key?

Ans.
Primary Key uniquely identifies each row/record in the Database Table.
Primary Key must contain unique values.
Primary Key column can not contain a NULL value.
The table can have only one Primary Key.
Primary key does not allow duplicate values.

4. What is Normalization?

Ans. Normalization is the process of designing a database in such a way so that it contains minimum redundancy of data. Generally, this process divides the Database into two or more tables.

5. What's the difference between a primary key and a unique key?

Ans. Primary key does not allow NULL value while a unique key allows NULL value.

6. What is the difference between Truncate and delete command?

Ans.
1. Truncate is much faster than delete.
2. Truncate removes data permanently while delete data is stored in a log file and can be recovered.
3. In Truncate command we can not use where clause but in delete command, we can use it.

7. What's the maximum size of a row?

Ans. 8060 bytes

8. Which TCP/IP port does SQL Server run on?

Ans. 1433

9. Can stored procedure be recursive?

Ans. yes

10. What is Stored Procedure?

Ans. Stored Procedure is a set of SQL statements. Its Compiled first and then stored into Database. It provides code reuse, security.

Syntax of Creating Stored Procedure:
CREATE PROCEDURE [dbo].name_of_sp
@parameter1 VARCHAR(100)
,@parameter2 VARCHAR(200) OUTPUT
AS
BEGIN
DECLARE @parameter3 VARCHAR(100)
SET @parameter3 = ' Your development resources.'
IF @parameter1 IS NOT NULL AND LEN(@parameter1) > 1
SELECT @parameter2 =
+ @parameter1
+ @parameter3
ELSE SELECT @parameter2 = 'Helo'
RETURN
END
GO

11. How to get unique values from table?

Ans. Using DISTINCT we can select unique values from the table.

12. Write SQL query to get the name of employees whose salary is within a range of 5,000 to 10,000.

Ans. SELECT NAME FROM EMPLOYEE WHERE SALARY BETWEEN 5000 AND 10000;

13. Write SQL query to get the name of employees that starts with character A.

Ans. SELECT NAME FROM EMPLOYEE WHERE NAME LIKE A%;

14. Write SQL query to get the name of employees whose salary is least.

Ans. SELECT NAME FROM EMPLOYEE WHERE SALARY IN (SELECT MIN(SALARY) FROM EMPLOYEE);

15. Write SQL query to get the name of employees whose salary is maximum.

Ans. SELECT NAME FROM EMPLOYEE WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEE);

16. Write SQL query to get the total of the salary of all employees.

Ans. SELECT SUM(SALARY) FROM EMPLOYEE;

17. Define types of joins.

Ans. 1. Inner join
Outer join (left, right , full);
Cross join

18. How to combine output of multiple queries?

Ans. Using UNION clause we can combine output of multiple queries.

19. What are the transaction properties?

Ans.
1. A : Atomicity
2. C : Consistency
3. I : Isolation
4. D : Durability

20. What is the difference between UNION and UNIONALL?

Ans. Union will remove the duplicate rows from the result set while Union all doesn't.

21. What is view in sql server?
Ans. View is a virtual table which contains columns from one or more table in the database.

22. What is the difference between functions and stored-procedure?
Ans. Function must return a value while stored procedure may return 0 or n values.
        We can call function in stored procedure but we can not call stored procedure in functions.
        Functions have input parameters while stored procedures can have input/output parameters.

23. What do you mean by table in SQL Server?
Ans. Table in SQL server forms the data in rows and columns.

24. Provide the syntax to create table in SQL Server?
Ans. Create table TableName (columnname1 datatype, columnname2 datatype)

25. What is the record in the Database?
Ans. Record is called the row in the table which stores data in an ordered form.

26. What are different DDL commands in SQL?
Ans. CREATE
         ALTER
         DROP
         TRUNCATE
         RENAME

27. What are different DML commands in SQL?
Ans. INSERT
         SELECT
         UPDATE
         DELETE

28. What are different DCL commands in SQL?
Ans. GRANT
         DENY
         REVOKE

29. What are different TCL commands in SQL?
Ans. COMMIT
         ROLLBACK

30. What are the limitations of IDENTITY column?
Ans. We can not update value of an IDENTITY column.
         We can create IDENTITY column only on int field.

31. What are the types of subqueries?
Ans. Single Row : It returns only one row.
         Multiple Rows : It returns multiple rows.
         Multiple Column : It returns multiple columns.

32. What is the use of FLOOR function?
Ans. FLOOR function is used to round up a non integer value to its previous least integer.
         For Example FLOOR(7.8) will return 7.

33. What is a temp table?
Ans. temp table will store the data temporarily.

34. What is a Trigger?
Ans. Trigger is an event which occurred when particular SQL statement is executed like INSERT / UPDATE / DELETE.

35. How to list the triggers in a database?
Ans. Select * from sys.objects where type='tr'

36. What are the Magic tables in SQL Server?
Ans. During the execution of DML queries SQL creates the Magic tables to hold the values of the DML oprations. These tables are used in Triggers for data transaction.

37. What is ISNULL Operator?
Ans ISNULL operator is used check whether the value is NULL or NOT NULL.

38. What is SELECT INTO statement?
Ans. SELECT INTO statement is used to copy data from one table to another.
        For Example :
        Select * into tmpTable from EmpTbl.

39. How to avoid duplicate values in queries?
Ans SELECT DISTINCT is used to return unique values.

40. What is the difference between Rename and Alias?
Ans. Rename is the permanent name change of the table and Alias is the temporary name given to the table.