Archive

Archive for December 6, 2011

TSQL Interview Questions – Part 5

December 6, 2011 6 comments

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!!!

Categories: Interview Q Tags:

SQL Server’s weird behavior in error handling with transactions

December 6, 2011 2 comments

In my previous post I mentioned that SQL statements inside the BEGIN & COMMIT TRANSACTION block do not auto rollback the batch if an error occurs, and SET XACT_ABORT ON is required for auto rollbacking the transaction batch in such cases.

As I mentioned earlier in my previous post that by default the SET XACT_ABORT is OFF. There ar very few cases where the T-SQL statement that raised exception are rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

But When SET XACT_ABORT is ON, if a T-SQL statement raises an exception, then the entire transaction is terminated and rolled back.

But there is an exception for some errors, like for error level 16, example: Datatype Conversion error, the transaction batch is automatically rolled back.

–> Let’s check this with an example below:

-- Create a table and insert test some records
CREATE TABLE X (NBR INT PRIMARY KEY)

INSERT INTO X VALUES(1)
INSERT INTO X VALUES(2)

-- Tran t1 : PK violation error
BEGIN TRANSACTION T1
	INSERT INTO X VALUES(3)
	INSERT INTO X VALUES(3) -- Msg 2627, Level 14, State 1, Line 9, Violation of PRIMARY KEY constraint...
	INSERT INTO X VALUES(4)
COMMIT TRANSACTION T1

SELECT * FROM X
-- Results 4 records inserted above, the entire transaction batch was not rolled back, but only the 2nd INSERT statement.

-- To automatically rollback above transaction batch we will set XACT_ABORT to ON:
SET XACT_ABORT ON

BEGIN TRANSACTION T1
	INSERT INTO X VALUES(3)
	INSERT INTO X VALUES(3) -- Msg 2627, Level 14, State 1, Line 9, Violation of PRIMARY KEY constraint...
	INSERT INTO X VALUES(4)
COMMIT TRANSACTION T1

SET XACT_ABORT OFF

SELECT * FROM X
-- No results, Entire transaction batch is rolled back.

--// But for some types of errors setting the XACT_ABORT to ON is not required. Like for Datatype Conversion errors (level = 16). Let's check this:

-- Tran t2: Datatype conversion error
BEGIN TRANSACTION T2
	INSERT INTO X VALUES(5)
	INSERT INTO X VALUES('a') -- Msg 245, Level 16, State 1, Line 19, Conversion failed...
	INSERT INTO X VALUES(6)
COMMIT TRANSACTION T2

SELECT * FROM X
-- No results, Entire transaction batch is rolled back.

-- Final Cleanup
DROP TABLE X

Erland Sommarskog in his tech-site lists and distinguish such error types: http://www.sommarskog.se/error-handling-I.html#statementbatch

I also discussed this topic in MSDN TSQL forum & here is the link.

Categories: DB Concepts Tags: , , , ,

T-SQL Query for SORTing Numbers stored as String (VARCHAR)

December 6, 2011 2 comments

Many people come to me as a point of SQL reference and ask, “How can we sort the numbers if they are stored in a VARCHAR column in a table?”.
This morning one guy approached me and asked the same question again.
So this as a generic question I thought to post it in my blog.

Let’s check this by a simple example:

-- Create table and insert some test data:
DECLARE @StringNbrs TABLE (nbr VARCHAR(10))

INSERT INTO @StringNbrs
SELECT C
FROM (VALUES ('681'),
	('21'),
	('9'),
	('1'),
	('401'),
	('158'),
	('1000')) AS T(C)

-- Using simple ORDER BY clause:
SELECT nbr
FROM @StringNbrs
ORDER BY nbr

Output:-

nbr
1
1000
158
21
401
681
9

The above query with ORDER BY clause won’t sort the nbr column as required.

-- =====================================================================
-- Let's see how can we sort the nbr column with 2 methods given below:-
-- =====================================================================

-- Method #1: Using CAST/CONVERT with ORDER BY clause:
SELECT nbr
FROM @StringNbrs
ORDER BY CAST(nbr as INT)

-- Method #2: Another approach by using REPLICATE function in ORDER BY clause:
SELECT nbr
FROM @StringNbrs
ORDER BY REPLICATE('0',4-LEN(nbr)) + nbr

Sorted Output:-

nbr
1
9
21
158
401
681
1000
Categories: SQL Tips Tags: ,