SQL Server Internals | SQL with Manoj

Using IDENTITY function with SELECT statement in SQL Server

December 15, 2011 6 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:

Categories: DB Concepts, SQL Tips Tags: IDENTITY, SELECT IDENTITY INTO

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

Categories: XML Tags: big endian, encoding, little endian, UTF-16, UTF-8, XML, xml version

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: BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK, SET XACT_ABORT ON, XACT_ABORT

SQL Basics – Working with VIEWs in SQL Server

December 2, 2011 1 comment

Views in SQL Server and other RDBMSs are simply Virtual Tables or Stored Queries. Views are like tables, contains rows and columns, but do not store any data within. Instead they use the Query that is defined to create the view to show data from one or more tables.

–> A view can be used for:

1. Simplifying a complex query, give it a simple name, and use it like a table.

2. Providing security by restricting the table access and showing only selected Columns and Rows via Views.

3. Providing compatibility with other client systems that cannot be changed.

–> Now let’s see how Views helps with above points:

Point #1: Consider Query below, it will be difficult and time consuming to write the same query every time. Also due to human error at times you may not be able to get same or desired results, because you can commit mistake or forget something while writing the same logic.

USE [AdventureWorks2014]
GO

SELECT 
	 P.BusinessEntityID AS EmpID
	,P.Title
	,CONCAT(P.FirstName, ' ', P.MiddleName, ' ', P.LastName) AS EmployeeName
	,P.Suffix
	,E.BirthDate
	,CASE 
		WHEN E.Gender = 'M' THEN 'Male' 
		ELSE 'Female' 
	 END as Gender
	,IIF(E.MaritalStatus = 'S', 'Single', 'Married') as MaritalStatus
FROM Person.Person P
JOIN [HumanResources].[Employee] E
ON E.BusinessEntityID = P.BusinessEntityID

… here we have Joined 2 tables and selected only required columns. You will also notice that we have changed the column names (alias), and created calculated columns, like EmployeeName, Gender & MaritalStatus.

So rather than writing this query every time, its better to store this query as a View, like below:

CREATE VIEW dbo.vwPersonEmployee
AS
SELECT 
	 P.BusinessEntityID AS EmpID
	,P.Title
	,CONCAT(P.FirstName, ' ', P.MiddleName, ' ', P.LastName) AS EmployeeName
	,P.Suffix
	,E.BirthDate
	,CASE 
		WHEN E.Gender = 'M' THEN 'Male' 
		ELSE 'Female' 
	 END as Gender
	,IIF(E.MaritalStatus = 'S', 'Single', 'Married') as MaritalStatus
FROM Person.Person P
JOIN [HumanResources].[Employee] E
ON E.BusinessEntityID = P.BusinessEntityID
GO

… and simply execute the view instead of the query now onwards, like:

SELECT * FROM dbo.vwPersonEmployee

Point #2: The above View uses 2 tables Person.Person & HumanResources.Employee. Now if you want a user to have restricted access to these 2 tables, but also want the user to query View to get desired and restricted data, then you can GRANT access only to the View, like:

CREATE USER userView WITHOUT LOGIN;

GRANT SELECT ON dbo.vwPersonEmployee TO userView;
GO

EXECUTE AS USER = 'userView';

SELECT * FROM dbo.vwPersonEmployee 	-- Displays View data

SELECT * FROM Person.Person				-- ERROR: The SELECT permission was denied on the object
SELECT * FROM HumanResources.Employee	-- ERROR: The SELECT permission was denied on the object

REVERT;
GO

DROP USER userView
GO

… here when the user executes the View he can see the data, but with only selected columns. But if he tries to use tables, he will get error.

Point #3: Now let’s say a client application which was getting data from an old table, let’s say dbo.Person, with following columns: PersonID, PersonFirstName, PersonLastName. Now on the new DB system the table is replaced by a new table Person.Person with different column names. This will make the system unusable and unstable.

But with the use of Views we can fill the gap, by creating a new View with name dbo.Person on top of Person.Person, and aliasing new columns with old column names, like:

CREATE VIEW dbo.Person
AS
SELECT
	 BusinessEntityID as PersonID
	,FirstName as PersonFirstName
	,LastName as PersonLastName
FROM Person.Person
GO

… so by this way the client application can talk to the new table by hitting the View instead of the Table.

–> Dropping/Deleting Views:

DROP VIEW dbo.vwPersonEmployee
DROP VIEW dbo.Person

Categories: DB Concepts, SQL Basics Tags: Create View, SQL Views

Adding Namespaces in XML | Querying XML with Namespaces (SQL Server)

November 2, 2011 1 comment

Namespaces like in every language provides a way to avoid conflict with element names. Here we will see how can we add Namespace to an XML. In previous posts we learned how to query XMLs, but without Namespaces. Here we will see how can we query XML containing Namespaces.

>> Creating an XML with Namespace prefix:

USE [AdventureWorks]
GO

;WITH XMLNAMESPACES ('uri' as ns)
select TOP 2 [ContactID], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailAddress], [Phone] 
from AdventureWorks.Person.Contact 
FOR XML PATH('Contact'), ROOT('ns:Person.Contact'), ELEMENTS XSINIL

Output:- XML with the Namespace prefix

<ns:Person.Contact 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns:ns="uri">
  <Contact>
    <ContactID>1</ContactID>
    <Title>Mr.</Title>
    <FirstName>Gustavo</FirstName>
    <MiddleName xsi:nil="true" />
    <LastName>Achong</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>gustavo0@adventure-works.com</EmailAddress>
    <Phone>398-555-0132</Phone>
  </Contact>
  <Contact>
    <ContactID>2</ContactID>
    <Title>Ms.</Title>
    <FirstName>Catherine</FirstName>
    <MiddleName>R.</MiddleName>
    <LastName>Abel</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>catherine0@adventure-works.com</EmailAddress>
    <Phone>747-555-0171</Phone>
  </Contact>
</ns:Person.Contact>

>> Querying the XML with Namespaces:

declare @xml xml

set @xml = '
<ns:Person.Contact 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns:ns="uri">
  <Contact>
    <ContactID>1</ContactID>
    <Title>Mr.</Title>
    <FirstName>Gustavo</FirstName>
    <MiddleName xsi:nil="true" />
    <LastName>Achong</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>gustavo0@adventure-works.com</EmailAddress>
    <Phone>398-555-0132</Phone>
  </Contact>
  <Contact>
    <ContactID>2</ContactID>
    <Title>Ms.</Title>
    <FirstName>Catherine</FirstName>
    <MiddleName>R.</MiddleName>
    <LastName>Abel</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>catherine0@adventure-works.com</EmailAddress>
    <Phone>747-555-0171</Phone>
  </Contact>
</ns:Person.Contact>'

-- Declaring namespaces in XQuery
SELECT 
	T.C.value('ContactID[1]', 'INT') as [ContactID],
	T.C.value('Title[1]', 'VARCHAR(8)') as [Title],
	T.C.value('FirstName[1]', 'VARCHAR(50)') as [FirstName],
	T.C.value('MiddleName[1]', 'VARCHAR(50)') as [MiddleName],
	T.C.value('LastName[1]', 'VARCHAR(50)') as [LastName],
	T.C.value('Suffix[1]', 'VARCHAR(10)') as [Suffix],
	T.C.value('EmailAddress[1]', 'VARCHAR(50)') as [EmailAddress],
	T.C.value('Phone[1]', 'VARCHAR(50)') as [Phone]
from @xml.nodes('declare namespace ns="uri"; //ns:Person.Contact/Contact') T(C)

-- Using WITH XMLNAMESPACES
;WITH XMLNAMESPACES ('uri' as ns)
SELECT 
	T.C.value('ContactID[1]', 'INT') as [ContactID],
	T.C.value('Title[1]', 'VARCHAR(8)') as [Title],
	T.C.value('FirstName[1]', 'VARCHAR(50)') as [FirstName],
	T.C.value('MiddleName[1]', 'VARCHAR(50)') as [MiddleName],
	T.C.value('LastName[1]', 'VARCHAR(50)') as [LastName],
	T.C.value('Suffix[1]', 'VARCHAR(10)') as [Suffix],
	T.C.value('EmailAddress[1]', 'VARCHAR(50)') as [EmailAddress],
	T.C.value('Phone[1]', 'VARCHAR(50)') as [Phone]
from @xml.nodes('//ns:Person.Contact/Contact') as T(C)

Categories: XML Tags: declare namespace, WITH XMLNAMESPACES, XML

Newer Entries Older Entries