SQL Server Interview Questions & Answers – SET 12(10 Questions & Answers)

1. Why is extra comma giving error while declaring table variable and not for create table command?

Example – 1

--

E.g. 
CREATE TABLE a234 ( Id Int , )

OUTPUT
Commands completed successfully.

--

Example – 1

--                            

DECLARE @a234  TABLE ( Id INT , )

OUTPUT 

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'TABLE'.

--

Ans:-

Consider this as bug. Please check out what MS said about this- “This was resolved some time ago as “won’t fix” but we didn’t explain why. Simply, this seems pretty harmless, and not worth fixing in a service pack. We may consider fixing this in a future release.”

2. How you can move tempdb and templog to another drive?

Ans:-

Execute the below commands:

--                            

ALTER DATABASE tempDB 
	MODIFY FILE ( NAME = tempdev, FILENAME = 'Newpath........\tempDB\data\tempdb.mdf' )
GO

ALTER DATABASE tempDB 
	MODIFY FILE ( NAME = templog, FILENAME = 'Newpath........\tempDB\log\templog.ldf' )
GO

--

Note – After executing the above commands you need to stop and start SQL Server.

3. Can shrinking the .ldf file will cause fragmentation?

Ans:-

No it will not. The log file is sequential and can’t be fragmented. Also note that you should use only one log file as multiple
log files would only be used sequentially anyway.

4. Should we provide SQL Server file Autogrowth say 2 MB or should be provide that in percentage?

Ans:-

A fixed amount of Autogrowth is good. But 2MB is very low and bad. We should the increase the size to 64MB. It should be good enough for most databases.

Note – Do not ever use % growth on any file. Always use fixed value.

5. How you can prevent the usage of SELECT * from a table?

Ans:-

In this case you need to follow the steps below-

1. Create a view like below-

--                            

SELECT * ,  CAST('a' AS INT) aaerrror
FROM yourTableName

--

2. Remove SELECT access from your table i.e. yourTableName

3. GRANT SELECT permission to the new view created.

Sample:-

--                            

Create TABLE test
(
	 Id INT
	,Val Varchar(10)
)
GO

INSERT INTO test VALUES ( 1 , 'a' ) , (2 , 'b')
GO

--

Create View

--                            

CREATE VIEW Vwtest
AS
SELECT * ,  CAST('a' AS INT) aaerrror
FROM test

--

So when the user execute the select * from Vwtest they will recieve the error. See example below-

--                            

SELECT * FROM Vwtest

Output

Msg 245, Level 16, State 1, Line 87
Conversion failed when converting the varchar value 'a' to data type int.

--

But when you will provide the column names you will get proper output. Check below.

--                            

SELECT id,val FROM VWTest

OUTPUT

id          val
----------- ----------
1           a
2           b

(2 rows affected)

--

6. Can users access database in Read-Only mode?

Ans:-

Yes they can but they would not be able to make changes to the database. Meaning DDL (creating, modifying or removing objects) or DML that modifies the data in the database (inserts, updates or deletes) are not allowed.

7. What is the difference between lock and deadlock?

Ans:-

From Microsoft – When multiple users or applications access the same data at the same time, locking prevents them from making simultaneous changes to the data. Locks are managed internally by the Microsoft SQL Server Compact Database Engine. The locks are automatically acquired and released based on actions taken by the user.

It’s normal for tasks to sometimes have to wait for locks to clear. It’s called “blocking”, and again, it’s perfectly normal.

Read more from – https://technet.microsoft.com/en-us/library/ms172975(v=sql.110).aspx

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.

In that case, SQL is forced to kill all but one of the deadlocked tasks.

Read more from – https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

8. What are the immediate steps your one should take as a DBA if your database is down suddenly?

Ans:-

I am not a DBA but you can start by checking logs – Windows Event Log and SQL Server Error Log for error entries. Then you need to bring the server and the SQL Server instance online.

9. My Query is using LOOP JOIN and it is very slow? What should I do?

Ans:-

Well in this case you can use JOIN hint ( Read more from – https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-join ). A LOOP JOIN can be very costly in cases where the optimizer’s row estimation is incorrect. If the number of input tables are high and the query is complex then it is more likely that SQL is to mis-estimate the number of rows.

I would recommend to use below.

--                            

OPTION ( HASH JOIN, MERGE JOIN )

--

This will allows SQL to decide either a MERGE or a HASH JOIN is used, depending on which will perform better. Note when you mention this SQL will eliminate the LOOP join.

Other option you can do is to provide the JOIN explicitly like INNER HASH JOIN.

Also note that more details can only be shared depending on what execution plan your query has.

10. Should we install SQL Server engine on a dedicated server or NOT?

Ans:-

SQL Server engine should be installed on a dedicated server. Also do not install anything on this server but SSMS.

Note – Since this is the most important server so we will have to keep it the clean with very less people touching it.

Author Introduction: Pawan Khowal

Pawan is a SQL Server Expert. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com