Tags
Cardinality, Fragmentation, GUID, Hash Join, In which scenarios we should not use CTE's, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Merge Join, Nested Loop, Objective Puzzle, Physical join in SQL, Physical joins in SQL Server, puzzle sql developer, Puzzles, Queries for SQL Interview, Red Flags, Red Flags in SQL, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Interview Questions, SQL Joins, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, statistics, statistics in SQL, statisticsin SQL SERVER, T SQL Puzzles, TOP Clause, Tricky Questions, TSQL, TSQL Interview questions, TSQL Queries, Unique Index vs Unique Constraint
Advance SQL SERVER Interview Questions – Set 1
Recently I have attended a SQL Server Architect Interview. There were 3 interviewers & since the requirement is for SQL Architect, they asked all the questions from SQL only. Please find some of the questions below. Will post answers to these questions tomorrow.
| Question |
| What are statistics? Where they are used and how to check statistics. |
| What are the types of Fragmentations |
| Can we use GUID as Primary key in a table ? |
| Difference between Unique Index vs Unique Constraint |
| What are RED Flags in SQL Server and what is there usage ? |
| What are the types of physical joins in SQL Server ? What are the different join operators in SQL Server? |
| What is a Latch in SQL Server ? |
| Difference between Latch and Lock .. |
| 2014 New Features in DB Engine. |
| In which scenarios we should not use CTE’s. |
| What do you mean by Cardinality in SQL Server. |
| What are Histogram and Density Vector. |
| Why we can’t put Order by inside the view? |
| What is a cross apply and how to use this? |
| Can we use more than one CTE in a single select query? |
| I have opened a nested transaction inside an outer transaction, if i do rollback which transaction will be undone? |
| How do you know the total transaction count? |
| I have created a table variable can we use it in a nested stored procedure? If not what is the scope of a table variable? |
| If suppose we have a user defined data type. If we can modify the length of the data type does it effects in all places across the database? |
| Can we fire a trigger manually? |
| What are the magic tables? Do we have “Updated” magic table? |
| What is the difference between UnionAll and Merge? |
| Basic difference between stored procedure and user defined function? |
| See we have a simple query that’s calling a static function, like “Select * from employee where joiningdate < getstaticdate()”? Does it call function for every time or only for matched rows? How you tune this query? |
| Why should we use CTE? |
| What is the difference between sub query and correlated query |
| What is the difference between pessimistic locking and optimistic locking? |
| What is XACT_ABORT ON? |
| How to filter nested stored procedure code from profiler? |
| What happens when a rollback happens in inside a nested stored procedure? |
| What is a bitmap index? |
| How to avoid bookmark lookup in execution plan? |
| When index scan happens? |
| Does temp tables and table variables both stored in tempdb? |
| Why cursors are so costly? |
| Can we call a procedure from a function? |
| Can we write DML inside a function? |
| What is the best value for MAXDOP value? |
| Which is better “Left Outer” Or “NOT EXIST”? |
| How to find the statistics are outdated? |
| How to find the query running on a given SPID? |
| What are the limitations on “SET ROWCOUNT”? |
| While creating a “Include” columns on which basis we should consider main column and include columns? |
| How to find the last statistics update date? |
| An indexed view is referring only one base table. Both view and table are having index defined on them. Which index would be utilized when a query executed against the table. |
| I have an indexed view, now base table data has been modified, does the modified data reflected in view automatically? |
| Does “TRUNCATE” DDL or DML command? |
| I have written a recursive CTE, it’s been running infinitely and failed with an error. The requirement is that the recursion required for 45000 times. How could you be able to handle the situation? |
| What is index reorganization? |
| How sql engine knows which index has to be used while dealing with indexed views? |
| How to prevent bad parameter sniffing? What exactly it means? |
| What dll file that handle the transaction logs in logshipping? |
| How to find all dependent objects of a table? |
| Which is better a CTE or a subquery? Why? |
| How to rebuild a master database? Ho to restore a master database? |
| Any alternative to triggers? |
| Top performance tuning tools |
| What events need to be added to capture execution plan in sql profiller? |
| What is the new lock escalation in sql 2008? |
| What is a filtered index? |
| What happens when a transaction runs on SQL server? Let’s say simple update statement “Update Table set col1 = value where col2 = value” |
| What is fragmentation? How it happens? |
| I have a log file which is of 250 GB. Log is full. We don’t have a disk space on any other drive for creating .ndf, auto growth is ON , and essentially there are no options to allocate new space for the file. What’s your action plan? |
| How B-Tree formed for Clustered and non clustered indexes? |
| How B-Tree forms for indexes with included column? |
| Does alzebrizer tree stores in memory for stored procedures, views and constraints? |
| How to design TempDB files? And what is the limit? |
| How VLF’s created for tempDB? |
| When the checkpoint can happen? What it exactly do? |
| When the lazywriter happens and what it’ll do? |
| What is memory grant in SQL Server? |
| Why resourceDB introduced? |
| How to move master database? |
| How to rebuild master database and what are the considerations? |
| What is a boot page? |
| What is .TUF file? What is the significance of the same? Any implications if the file is deleted? |
| What are the different shrink options? |
| How do you know whether statistics are latest or expired? |
| How to create linked server |
| Can you give some examples for One to One, One to Many and Many to Many relationships? |
| How to capture a trace from production without any impact on performance? |
| How to capture the long running queries? |
| You have any idea on Table Partitions? |
| You have got a request to execute a query which is an “Update” query. That update is updating 5 million rows, after an hour it’s still executing and you are getting lot of requests from report users that their things are getting slow down. What’s your action plan? |
| See we have a view which is getting data from different tables. One day it’s starts executing infinitely. I have seen no blocking , no bulk operation happened. I have stopped all jobs and maintenance plans on the server. No one is connected to the database but still it’s been taking longer time. What might be the possible reasons? |
| Can you explain sql server transaction log architecture? |
| What are the phases of sql server database restore process? |
| Do you have any idea about sparse column? |
| We have a procedure which is running fine till today afternoon. Suddenly it started taking long time to execute and there of it leads to a timeout error from application. What might be happening? How you troubleshoot? |
| What are the critical issues you faced in your career? |
| How to change the port number for SQL Server? Default port no of SQL SERVER |
| Difference between ISNULL and COAELSCE |
| Difference between partition by and patindex |
| What is the difference between CROSS / OUTER APPLY AND JOINS in T-SQL? |
| What is stuff function? Difference between stuff and replace? |
| We have a query which is running fine in development but facing performance issues at production. You got the execution plan from production DBA. Now you need to compare with the development execution plan. What is your approach? |
| What is lock Escalation |
| How do you retrieve random 5 rows from a table |
| What exactly you check in the query execution plan window? |
| What is the output of len(1234.56) |
| Where in MS SQL Server is ’100’ equal to ‘0’? |
| Explain clearly about all the normal forms with proper example |
| I have a table with millions of rows. I want to retain only last 5% of the rows ? How do u do it ? (Expectation :- transfer last 5% to temp table |
| Considerations while designing a DB |
| Considerations while creating tables and its naming conventions |
| Explain transaction log in detail |
| I have a 500 GB Table which is performing slow ? Your plan of action ? |
| Explain Column Store Index |
| Explain Isolation Levels ? Default one ? |
Happy Learning..
Improving my SQL BI Skills 
Hello There,
Gratitude for putting up this prolific article! You truly make everything a cake walk. Genuinely good stuff, saving time and energy.
An autonomous transaction creates, when called from another transaction, an independent and separate transaction that can issue commits without the affecting the calling transaction.
In some definitions of sys.view and sys.functions I found very interesting construction OpenRowset(TABLE , .., ..).
When I try use with construction in my T-SQL, I get syntax errors.
But I can’t find any information about with statement in BOL and Internet.
Is it some undocumented or internal feature? Or I can use it.
I read multiple articles and watched many videos about how to use this tool – and was still confused! Your instructions were easy to understand and made the process simple.
Thanks,
Ivan
LikeLike