TSQL Interview Questions – Part 5
Next part of TSQL Interview Questions, contd. from my previous post.
101. What are the required/mandatory parameters that have to be passed with RAISEERROR statement?
– message _id or message_str
– severity, and
– state
102. Difference between RAISERROR and THROW
RAISERROR vs THROW: https://sqlwithmanoj.com/2012/12/20/new-throw-statement-in-sql-server-2012-vs-raiserror/
103. What severity level errors are managed in TRY-CATCH block?
A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. Check about TRY-CATCH here: https://sqlwithmanoj.com/2010/06/16/try-catch-exception-handling/
104. What are row constructors?
Also known as Table Value Constructor: https://sqlwithmanoj.com/2015/02/16/sql-basics-what-are-row-constructors/.
Also check Method #5 of this post: https://sqlwithmanoj.com/2010/10/15/multiple-ways-to-insert-records-in-a-table/
105. What is SCD (Slowly Changing Dimensions)? What are its types?
Check this YouTube tutorial on SCD: v=aTnr00NnlXE
106. How many types of internal joins are there in SQL Server?
– Nested loop join
– Merge join
– Hash join
More about these: https://sqlwithmanoj.com/2010/10/06/physical-join-vs-logical-join/
107. What are pages in SQL Server? How many types of pages are there?
A page in SQL Server is an 8 KB data storage area. There are 8 types of pages, check details here: https://sqlwithmanoj.com/2011/10/12/what-are-records-pages-extents-heap/
108. How many data sources are available in SSIS?
– DataReader Source in 2005 & ADO NET Source in 2008 & above.
– Excel Source
– Flat File Source
– OLE DB Source
– Raw File Source
– Script Component
– XML Source
109. How will you deploy an SSIS package in testing, staging & production environments?
The dtutil is the command prompt utility which is used to manage SSIS packages.
This utility can copy, move, delete or verify the existence of a package.
More on: http://msdn.microsoft.com/en-us/library/ms162820.aspx
110. Where will you use Conditional Split transformation?
More info: http://msdn.microsoft.com/en-us/library/ms137886.aspx
111. What is live lock, deadlock and what is Lock escalation?
Check this YouTube tutorial on Deadlock: v=V_KLPWHfYFU
112. If you are working on a SQL database and if suddenly a developer changes the code and your queries results start giving errors, how will you check using a T-SQL query (on system tables) that what has changed in the database.
select o.name, o.object_id, o.modify_date, c.text
from sys.objects o
join sys.syscomments c
on o.object_id = c.id
113. Difference between Nested loops join, Merge join & Hash Join.
Check all differences here: https://sqlwithmanoj.com/2010/10/06/physical-join-vs-logical-join/
114. What are Surrogate Keys in a Data Warehouse?
Surrogate Keys are UNIQUE, SEQUENTIAL and MEANINGLESS generated integer numbers to identify every record in a Dimension table uniquely. The are used to JOIN Dimension tables with Fact tables.
… I’ll be adding more questions on upcoming post.
Comments welcome!!!
Good stuff!
Awesome! This is a great place for SQL users.
hi manoj Ji, is this question and answers valid for year 2020?
Yes Madhu, all these questions are based on SQL basics, so these are be valid.