Sql Server Dba Interview Questions And Answers Pdf

16. Explain “NOT NULL Constraint” in Sql Server?
Answer: “NOT NULL Constraint” is used in a column to make sure the value in the column is not null. If this constraint has not set then by default columns will accept NULL values too.

17. Why use Sub Query in Sql Server and List out types of Sub Queries?
Answer: Sub Queries are queries within a query. The parent or outer query is being called as the main query and the inner query is called as inner query or subquery. Different types of Sub Queries are

• Correlated – It is not an independent subquery. It is an inner query which is referred by the outer query.
• Non-Correlated – It is an independent subquery. It can be executed even without an outer query.

18. What are user-defined functions (UDFs) in Sql Server?
Answer: User-Defined functions are being used to handle complex queries.

There are two types of user-defined functions –

• Scalar – This type of functions are used for returning a single scalar value.
• Table-Valued – This type of function are used for returning a table which has a list of rows. Sql supports data type called table which is used here for returning a table.

19. List out the difference between Union and Union All in Sql Server?
Answer: Union is used to combine all result sets and it removes the duplicate records from the final result set obtained, unlike Union All which returns all the rows irrespective of whether rows are being duplicated or not.
Union checks the number of columns given in the SELECT statement should be equal or not and the datatypes are also should be same and same applied to UnionAll.

20. Explain “@@ROWCOUNT” and “@@ERROR” in Sql Server?
Answer: @@ROWCOUNT – Used to return the number of rows affected in the table due to the last statement.
@@ERROR – Used to return the error code which is occurred due to the last SQL statement. ‘0’ means there are no errors.

21. Why use Cursor in Sql Server?
Answer: The cursor is used in case of row traversal. This can be considered as a pointer pointing to one row at a time in the list of rows. Cursors can be used for retrieval, removal or addition of records in a table.

22. List all types of constraints in Sql Server?
Answer:
Below is the list of constraints in Sql Server –

• NOT NULL
• DEFAULT
• CHECK
• PRIMARY KEY
• FOREIGN KEY
• UNIQUE

23. Why use IDENTITY in Sql Server?
Answer: IDENTITY is used for a column to auto increment the value of the column in a table and it is mainly used with Primary Key.

24. What are the differences between Union, Intersect, and Minus operators?
Answer: 

• Union operator is used to combining all the results or records of the table and it removes the duplicate values.
• Interact operator is used to returning the common list of records between two result sets.
  Minus operator is used to getting the list of records from the first result set and which is not there in the second result set.

25. Explain “ROW_NUMBER()” in Sql Server with an example?
Answer: “ROW_NUMBER()” is used to return a sequential number of each row within a given partition. “1” will be the first position. “Partition By” and “Order By” can be used along with “ROW_NUMBER()”. Below is the example for the same
SELECT ROW_NUMBER() OVER(ORDER BY EmpSalary DESC) AS Row FROM Employees WHERE Emp Name Name IS NOT NULL

26. What are the differences between “ROW_NUMBER()”, “RANK()” and “DENSE_RANK()”?
Answer: “ROW_NUMBER” – Used to return a sequential number of each row within a given partition.
“RANK” – Used to returns a new row number for each distinct row in the result set and it will leave a number gap in case of duplicates.
“DENSE_RANK” – Used to returns a new row number for each distinct row in the result set and it will not leave any number gap in case of duplicates.

27. Explain about Link Server in Sql Server?
Answer: Linked Server is used to enable execution of OLEDB data sources in remote servers. With Linked servers, we can create easy SQL statements which will allow remote data to be joined, combined and retrieved with data in local.

28. What are the advantages of user-defined functions over stored procedures in Sql Server?
Answer: User-Defined functions can be used in SELECT/WHERE/HAVING clauses whereas stored procedure cannot be called. In the case of table-valued functions, the returned Table.

29. Why use “NoLock” in Sql Server?
Answer: “No Lock” is used for unlocking the rows which are locked by some other transaction. Once after the rows are committed or rolled back no need to use No Lock. For example.

30. What is the significance of master, tempdb and model databases?
Answer:

• master – This database will have data and catalog of all the databases of SQL Server instance.
• tempdb – tempdb database will have temporary objects like local and global temporary tables and stored procedures as well.
• the model – model database is mainly used for creating new user databases.

31. Explain about unique identifier data type in Sql Server?
Answer: Unique Identifier datatype mainly used for primary key columns of the tables or any other columns which need to have unique Ids. “NEWID()” function can be used for generating a unique identifier for the column. Unique Identifiers are also named as GUIDs.

32. Why use “PIVOT” in Sql Server?
Answer: Pivot table automatically count, sort and total the data in a table or spreadsheet and used to create a separate table for displaying summarized data.

33. Explain Alternate key, Candidate Key and Composite Key in Sql Server?
Answer:

• Alternate Key – To identify a row uniquely we can have multiple keys one of them is called primary key and rest of them are called alternate keys.
• Candidate Key – Set of fields or columns which are uniquely identified in a row and they constitute candidate keys.
• Composite Key – One key formed by combining at least two or more columns or fields.

34. How to use “DROP” keyword in Sql Server and Give an example?
Answer: “DROP” keyword is used to drop either Index or database or table. Below is a list of Sql statements using Drop keyword.

Dropping Index
DROP INDEX my_index
Dropping Database
DROP DATABASE my_database
Dropping Table
DROP TABLE my_table
deleted when the connection that created it is closed.

35. Can SQL servers link to other servers?
Answer: SQL server can be joined to any database which owns OLE-DB provider to provide a link. Example: Oracle holds OLE-DB provider which has a link to unite among the SQL server club.

36. What are subquery and its properties?
Answer:

• A subquery is a query which can be nested inside the main query like Select, Update, Insert or Delete statements. This can be used when the expression is allowed. Properties of the subquery can be defined as.
• A sub query should not have ordered by clause.
• A subquery should be placed in the right-hand side of the comparison operator of the main query.
• A subquery should be enclosed in parenthesis because it needs to be executed first before the main query.
• More than one subquery can be included.

37. What are the kinds of subquery?
Answer: There are 3 kinds of subquery –

• The query which returns only one row is Single row subquery.
• Which returns multiple row is a multiple-row subquery.
• Which returns multiple columns to the main query is multiple-column subqueries. Beside that subquery returns, the Chief query will be performed.

38. What is the SQL server agent?
Answer: The SQL Server agent performs an active role in day to day duties of SQL server manager (DBA). Server agent’s goal is to achieve the jobs simply with the scheduler motor which provides our jobs to work at proposed date and time.

39. What are scheduled tasks in SQL Server?
Answer: Scheduled jobs are practiced to automate methods that can be operated on a cataloged event at a constant interval. This scheduling of jobs benefits to decrease human interference throughout the night time and feed can be produced at an appropriate time. A user can further order the jobs in which it allows to be produced.

40. What is COALESCE in SQL Server?
Answer: COALESCE is used to return the first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.

41. If you are given access to a SQL Server, how do you find if the SQL Instance is a named instance or a default instance?
Answer: I would go to the SQL Server Configuration Manager. In the left pane of the tool, I would select SQL Server Services, the right side pane displays all of the SQL Server Services/components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.

42. What structure can you implement for the database to speed up table reads?
Answer :
A) Follow the rules of DB tuning we have to:

1. properly use indexes ( different types of indexes)
2. properly locate different DB objects across different tablespaces, files and so on.
3. create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB,

43. What are statistics, under what circumstances they go out of date, how do you update them?
Answer: A) Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. The query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

• If there is a significant change in the key values in the index.
• If a large amount of data in an indexed column has been added, changed, or removed (that is if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated.
• The database is upgraded from a previous version Q) Could you please some items which you may see in an execution plan indicating the query is not optimized.
• Index Scan or Table Scan.
• Hash Joins.
• Thick arrows (indicating large work tables).
• Parallel streams (Parallelism).
• Bookmark lookup (or key lookup).

44. In what sequence SQL statement is processed?
Answer: The clauses of the select are processed in the following sequence

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause
• TOP clause

45. Can we add an identity column to decimal datatype?
Answer: YES, SQL Server support this

What is the difference between LEFT JOIN with WHERE clause & LEFT JOIN with nowhere clause?
OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically.

46. What is meant by Active – Passive and Active-Active clustering setup?
Answer: 

An Active- Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. The other node, called as Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.

An Active-Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up (after fixing the issue that caused the node failure). The instance is then failed over back to its designated node.

47. How do you generate file output from SQL?
Answer: While using SQL Server Management Studio or Query Analyzer, we have an option in Menu BAR. QUERTY >> RESULT TO >> Result to FILE

How do you prevent SQL Server from giving you informational messages during and after a SQL statement execution?
SET NOCOUNT OFF

48. What is the importance of a recovery model?
Answer :
Primarily, the recovery model is chosen keeping in view the amount of data loss one can afford to. If one expects to have minimal or no data loss, choosing the Full recovery model is a good choice. Depending on the recovery model of a database, the behavior of database log file changes. I would recommend you read more material on log backups and log file behavior and so on to understand in depth.

49. What is Index, cluster index and nonclustered index?
Answer: 

Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore the table may have only one clustered index.

Non-Clustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index do not consist of the data pages. instead, the leaf node contains index rows.

• Write down the general syntax for a SELECT statement covering all the options.
• Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax).

50. What are the different authentication modes in SQL Server and how can you change authentication mode?
Answer: SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred to as Mixed Mode. To change the Authentication mode, read one of my blogs Changing SQL Server Authentication Mode.