SQL SERVER Interview Questions & Answers – SET 2 (40 Questions & Answers)

Download – SQL SERVER Interview Questions with Answers – Set 2 [40 Questions&Answers]

PREV

NEXT

Question21. Can you tell us different types of Isolation levels? Default Isolation level.

Answer –

There are five type of Isolation level in MS SQL Server
1. Read Committed (The Default Isolation Level of MS SQL Server)
2. Read Uncommitted
3. Repeatable Read
4. Serializable
5. Snapshot

Question22. How can you move the master database?

Answer – To move master database, you also have to move the Resource database. Microsoft states that the Resource database must reside in the same location as the Master database.

Follow the steps given @

https://msdn.microsoft.com/en-us/library/ms345408.aspx

Question23. When the lazywriter happens and what it’ll do?

Answer – The lazy writer is a process that periodically checks the available free space in the buffer cache between two checkpoints and ensures that there is always enough free memory. When the lazy writer determines free pages are needed in the buffer for better performance, it removes the old pages before the regular checkpoint occurs.

Ideally, Lazy Writes should be close to zero. That means that the buffer cache doesn’t have to free up dirty pages immediately, it can wait for the automatic check point.

For detailed explaination please click on the below URL-
http://www.sqlshack.com/sql-server-memory-performance-metrics-part-5-understanding-lazy-writes-free-list-stallssec-memory-grants-pending/

Question24. What are the properties of a transaction?

Answer – Atomicity Consistency Isolation Durability (ACID) is a concept referring to a database system’s four transaction properties: atomicity, consistency, isolation and durability.

Atomicity

Here either all the statements (whether an update, delete or insert) of the transaction will happen or not happen. To guarantee atomicity, SQL Server uses a Write Ahead Transaction Log. The log always gets written to first before the associated data changes. That way, if and when things go wrong, SQL Server will know how to rollback to a state where every transaction happened or didn’t happen.

Consistency

A transaction reaching its normal end, thereby committing its results, preserves the consistency of the database. If something bad happens then everything in the transaction will be rolled back. After each transaction DB should be in a consistent state.

Isolation

Events happening within a transaction must be hidden from other transactions running concurrently.

Durability

Once a transaction has been completed and has committed its results to the database, the system must guarantee that these results survive any subsequent malfunctions.

Question25. Why do some system functions require parenthesis and some do not require ?

Answer –

In SQL Server most of the system functions require parenthesis at the end. The examples can be GETDATE(), NEWID(), RAND(), ERROR_MESSAGE(), etc

Some functions do not need the parenthesis. The examples can be CURRENT_TIMESTAMP, CURRENT_USER, etc.

ANSI SQL standard functions do not need parenthesis.

SQL Server functions requires parenthesis

 

Question26. Why does Right Joins exists ?

Answer –

Personally I have never used right join.

As per Jeremiah Peschka – Convenience and optimization. Just because we can write our query as a LEFT OUTER JOIN, doesn’t mean that you should.

SQL Server provides a RIGHT OUTER JOIN showplan operator (http://msdn.microsoft.com/en-us/library/ms190390.aspx).

There are times when it’s going to be most efficient to use a right outer join. Leaving that option in the language 1) gives you the same functionality in the language that you have in the optimizer and 2) supports the ANSI SQL specification. There’s always a chance, in a sufficiently complex plan on a sufficiently overloaded SQL Server, that SQL Server may time out query compilation.

In theory, if you specify RIGHT OUTER JOIN instead of a LEFT OUTER JOIN, your SQL could provide SQL Server with the hints it needs to create a better plan. If you ever see this situation, though, you should probably blog about it 🙂

No programming task requires a join, but you can also write all of your queries using syntax like SELECT * FROM a, b, c, d WHERE (a.id = b.a_id OR b.a_id IS NULL) and still have perfectly valid, well-formed, and ANSI compliant SQL.

 

Question27. Does foreign Key slows down the insertion process in SQL Server ?

Answer – Hardly or negligible.

Please read excellent post below for details.

http://www.brentozar.com/archive/2015/05/do-foreign-keys-matter-for-insert-speed/

 

Question28. Where does SQL Server Agent save jobs?

In MSDB, jobs are stored in dbo.sysjobs.

dbo.sysjobsteps – Stores details of the individual steps

dbo.sysjobschedules – Stored schedules of the job

dbo.sysjobhistory – Job history is maintained here.

MSDB also contains other instance level objects such as alerts, operators and SSIS packages.

 

Question29. How to calculate the likely size of an OLAP cube from the Relational database size ?

Answer –

You can use a general rule of Analysis Services data being about 1/4 – 1/3 size of the same data stored in relational database.

Reference – https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6b16d2b2-2913-4714-a21d-07ff91688d11/cube-size-estimation-formula

Question30. SQL Query | Consider the below table below and write some queries to replace 0 by 1 and 1 by 0.

--

CREATE TABLE ZeroOne
(
	Id INT
)
GO

INSERT INTO ZeroOne VALUES (0),(1),(0),(1),(0),(0)

--

Answer – Check out some queries below-

************************  5 Methods are given below to achieve the required output ***********

SELECT * , CASE ID WHEN 0 THEN 1 ELSE 0 END Ids FROM ZeroOne

SELECT * , (Id - 1) * -1 Ids FROM ZeroOne

SELECT * , 1 - Id Ids FROM ZeroOne

SELECT * , IIF(ID=0,1,0) Ids FROM ZeroOne

SELECT Id, (Id+1/2 -1) * -1 Ids FROM ZeroOne

DECLARE @t AS INT = 1
SELECT Id, CHOOSE(@t,1,0) Ids FROM ZeroOne

--

PREV

NEXT