Advanced SQL Interview Questions and Answers, Advanced SQL tutorial pdf, any recommended websites for sql puzzles, Best SQL Puzzles, Buy SQL Server Interview Questions Book Online at Low Price, COmples tSQL puzzles, Complex SQL Challenges, complex sql statement(puzzle), Complex tsql, Complex TSQL Challenge, convert string to proper case in sql server, Divide rows into two columns, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, Get Next Value Puzzle, Huge blank areas in database field, Improve SQL Skills, Interview Puzzles in SQL Server, Interview Qs.SQL SERVER Questions, Interview questions and Answers for MS SQL Server designing, Interview Questions and Answers For SQL, Interview questions on Joins, Interview Questions on SQL, Interview SQL Puzzles, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, Microsoft SQL Server interview questions for DBA, MS SQL Server interview questions, MSBI Interview Questions, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Puzzle SQL, puzzle sql developer, Puzzle SQl Server, Puzzles, PUzzles in SQL SERVER, Queries for SQL Interview, Remove Huge Multiple Spaces from a DB Fields, Remove Multiple Spaces from a DB Fields, Separate Int and char from a string column, Single Quote update SQL, SQL, SQL - The Pattern Puzzles, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Extreme Difficult Puzzle, SQL FAQ, SQL FAQs, sql group by only rows which are in sequence, SQL Interview Q & A, SQL Interview Questions, SQL Interview Questions - Part 2, SQL Interview Questions for SQL Professionals, SQL Joins, SQL Pattern Puzzles, SQL pl/sql puzzles, SQL prime number puzzle, SQL puzzle, SQL Puzzles, sql puzzles & answers, sql puzzles and answers free download, sql puzzles and answers pdf, sql puzzles for interview, sql puzzles oracle, SQL Queries, SQL Queries asked in interviews, SQL QUERY PUZZLES, SQL Query to Find Nth Highest Salary of Employee, SQL Questions, SQL Quiz, SQL Replace Puzzle, SQL Server - Common Interview Questions and Answers, SQL SERVER - Find Nth Highest Salary of Employee, SQL Server - General Interview Questions and Answers, sql server 2008 interview questions, SQL Server 2008 Interview Questions and Answers, SQL Server Database, SQL Server database developer interview questions and answers, sql server dba interview questions, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Developer T-SQL Interview Questions, SQL server filter index with LIKE and RIGHT function?, SQL Server Interview Puzzle, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview Questions - Part 1, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL Server Interview Questions And Answers.pdf, sql server interview questions by Pawan Khowal sql interview questions, SQL SERVER Interview questions pdf, SQL Server Interview Questions | MSBISKILLS.Com, SQL Server Interview Questions | MSBISKILLS.com Top 50 SQL Server Questions & Answers, SQL Server Interview Questions/Answers Part-1, SQL Server Puzzle, SQL SERVER Puzzles, SQL server Questions and Answers, SQL SERVER Tips, SQL Skills, SQL Sudoku, SQL Tips & Tricks, SQL Tips and Tricks, SQL Top clause, SQL Tough Puzzle, SQL Tricks, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLBI Interview Questions, SQLSERVER, SUM of grouped COUNT in SQL Query, T SQL Puzzles, T-SQL Challenge, T-SQL Interview Questions | SQL Server Interviews and Jobs, T-SQL Puzzle, T-SQL Server Interview Questions, T-SQL Tricky Puzzles, The Status Puzzle, Top 10 Frequently Asked SQL Query Interview Questions, TOP 100 SQL SERVER INTERVIEW QUESTIONS QUERIES, Top 50 SQL Server Interview Question for Testers, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Puzzle, TSQL Puzzles, TSQL Queries
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'. --
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?
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?
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?
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?
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.
-- Create TABLE test ( Id INT ,Val Varchar(10) ) GO INSERT INTO test VALUES ( 1 , 'a' ) , (2 , 'b') GO --
-- 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?
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?
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?
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?
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?
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.
|☛ SQL Advance Puzzles||https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/|
|☛ SQL Tricky Queries||https://msbiskills.com/sql-puzzles-finding-outputs/|
|☛ SQL Server Perfomance tuning Articles||https://msbiskills.com/sql-performance-tuning/|
|☛ SQL Server Articles||https://msbiskills.com/t-sql/|
|☛ SQL Interview Questions & Answers||https://msbiskills.com/sql-server-interview-questions/|
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:
My SQL Page on Facebook:
2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/
Enjoy !!! Keep Learning
Gregory Durand said:
8. What are the immediate steps your one should take as a DBA if your database is down suddenly?
First step would be to identify if the Instance is available. Then check if the database is indicating some corruption.
It might also be that some of the database files are missing (Disk issue). If the database is in a suspect mode after a loss of the LDF, it is possible to reattach the database by creating a new LDF. Unfortunately that means a loss of all transactions which have not been committed.
In fact there are several different scenariis in the case a database is suddenly down.
LikeLiked by 1 person
Pawan Kumar Khowal said:
Excellent answer sir as always. 🙂