Archive

Archive for December, 2011

Using IDENTITY function with SELECT statement in SQL Server

December 15, 2011 5 comments

In MS BOL for IDENTITY columns it is mentioned that [link]: “It creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.”

But experimenting with IDENTITY function I learnt this new thing with is not mentioned (or I could not find) anywhere in MS BOL that: “IDENTITY function can also be used with a SELECT statement but with some condition.”

Let’s check what’s that condition is:

USE [AdventureWorks]
GO

-- Let's use Person.Contact table in our SELECT statement and add a new column as IDENTITY function to it:
SELECT 
	IDENTITY (INT, 100, 5) AS NEW_ID, 
	ContactID, Title, 
	FirstName, 
	MiddleName, 
	LastName, 
	EmailAddress, 
	Phone
FROM Person.Contact

Error Msg thrown:
Msg 177, Level 15, State 1, Line 1
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

As per the error msg we cannot add an IDENTITY column to a SELECT query. The SELECT should be followed by an INTO clause. This way a new table will be created and records will be entered with the new IDENTITY column.

-- Let's add INTO clause in SELECT statement:
SELECT 
	IDENTITY (INT, 100, 5) AS NEW_ID, 
	ContactID, 
	Title, 
	FirstName, 
	MiddleName, 
	LastName, 
	EmailAddress, 
	Phone
INTO #tempTable
FROM Person.Contact

Again an Error Msg:
Msg 8108, Level 16, State 1, Line 1
Cannot add identity column, using the SELECT INTO statement, to table ‘#tempTable’, which already has column ‘ContactID’ that inherits the identity property.

Completely agree with this!!! You cannot have 2 IDENTITY columns in a single table.

-- So we will remove the ContactID column as we want to have new ID column.
SELECT 
	IDENTITY (INT, 100, 5) AS NEW_ID, 
	Title, 
	FirstName, 
	MiddleName, 
	LastName, 
	EmailAddress, 
	Phone
INTO #tempTable
FROM Person.Contact
-- This worked perfectly.

-- Now let's check the newly created temp table and inserted records:
select * from #tempTable

-- Output below shows selected records from Person.Contact table with a new column having values starting from 100 with a gap of 5:

-- Final Cleanup
DROP TABLE #tempTable

 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 

Check the same demo here in YouTube:


Query XML with different languages or different UNICODE encodings [UTF-8 & UTF-16]

December 9, 2011 1 comment

While creating XML documents we should take care of the UTF encoding if we are dealing with NON-US or international data. Non-English foreign languages fall in different ranges and takes more memory size compared to English language and thus require different encoding schemes, as follows:

>> UTF-8: represents an octet (8 bit) loosless encoding of Unicode characters. UTF-8 encodes each Unicode character as a variable number of 1 to 4 octets, where the number of octets depends on the integer value assigned to the Unicode character. It is an efficient encoding of Unicode documents that use mostly US-ASCII characters because it represents each character in the range U+0000 through U+007F as a single octet. UTF-8 is the default encoding for XML.

In an XML prolog the encoding is represented as:
‘<?xml version=”1.0″ encoding=”UTF-8″ ?>’

>> UTF-16: encoding is the 16-bit encoding of Unicode. It use 2 bytes per character (and sometimes combines two pairs), it makes implementation easier, but looks a bit overkill for Western languages encoding.

In an XML prolog the encoding is represented as:
‘<?xml version=”1.0″ encoding=”UTF-16″ ?>’

Let’s check both the encodings and issues if they are not used correctly:

-- ===================================================
-- XML containing NON Unicode English characters only:
-- ===================================================
DECLARE @xml as XML
SET @xml =
'<?xml version="1.0" encoding="utf-8"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Happy Birthday</subject>
	<body>Wish you a very very Happy Birth Day!!!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO
 Correct expected output:-

TO	FROM	SUBJECT		BODY
Gaurav	Garvit	Happy Birthday	Wish you a very very Happy Birth Day!!!

Let’s see how this works with international languages. Let’s change the language to French with same UTF-8 encoding:

-- ============================================
-- When XML contains Unicode French characters:
-- ============================================
DECLARE @xml as XML
SET @xml =
'<?xml version="1.0" encoding="utf-8"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Joyeux anniversaire</subject>
	<body>Je vous souhaite une très très heureux anniversaire!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO

We get an Error Message:
Msg 9420, Level 16, State 1, Line 2
XML parsing: line 6, character 31, illegal xml character

As a NON-English language (French) is used to so we have to use the UTF-16 encoding:

-- Let's change the encoding to UTF-16, i.e. for UNICODE characters:
DECLARE @xml as XML
SET @xml =
'<?xml version="1.0" encoding="utf-16"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Joyeux anniversaire</subject>
	<body>Je vous souhaite une très très heureux anniversaire!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO

Still getting the Error Message:
Msg 9402, Level 16, State 1, Line 2
XML parsing: line 1, character 39, unable to switch the encoding

Now, as we are dealing with UNICODE data so the XML string should also be of UNICODE type, so I’ll prefix ‘N’ before the string starts in SET statement:

-- Add N for to make the xml string Non-Unicode:
DECLARE @xml as XML
SET @xml =
N'<?xml version="1.0" encoding="utf-16"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Joyeux anniversaire</subject>
	<body>Je vous souhaite une très très heureux anniversaire!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO
Correct expected output:-
TO	FROM	SUBJECT			BODY
Gaurav	Garvit	Joyeux anniversaire	Je vous souhaite une très très heureux anniversaire!

As per MS BOL: “SQL Server stores XML data using the UTF-16 encoding scheme. Because UTF-16 data is variable-width, it is processed according to a byte-oriented protocol. This means that UTF-16 data can be treated in a way that is independent of the byte ordering on different computers (little endian versus big endian). Therefore, UTF-16 is well-suited for traversing different computers that use different encodings and byte-ordering systems. Because XML data is typically shared widely across networks, it makes sense to maintain the default UTF-16 storage of XML data in your database, and when you export XML data to clients.”

More info on: http://msdn.microsoft.com/en-US/library/ms145866%28v=SQL.90%29.aspx

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

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.

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: ,