Archive for the ‘SQL Server 2014’ Category
GROUP BY Quirk
It’s always interesting to see how others teach SQL courses. It can be revealing as to whether they understand SQL or only understand a dialect of SQL. In this case, one of my old students was taking a graduate course in SQL and the teacher was using MySQL. The teacher made an issue of using ANSI SQL:1999 or SQL3 and asked the following question, which I suspect is a quiz bank question from a textbook:
“How would you get all students’ names and for each student the number of courses that the
student has registered for?”
They referenced the MySQL 5.7 documentation for the GROUP BY and SQL:1999 as if MySQL implemented the ANSI SQL:1999 specification defined the standard. I didn’t know whether to laugh or cry because they were referring to MySQL 5.7 when we’re all using MySQL 8 and anybody who’s worked in more than MySQL knows that the behavior for a GROUP BY in MySQL can work without listing the necessary non-aggregated columns in the SELECT-list.
For example, their working solution, which is from the instructor and the author of their MySQL textbook the correct perspective of ANSI:1999 behavior. It doesn’t matter that their solution is actually based on ANSI:1992 not ANSI:1999 because it will only succeed because of a quirk of MySQL:
SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; |
While it works in MySQL, it doesn’t work because it conforms to an ANSI standard. It works in MySQL, notwithstanding that standard because it violates the standard.
In Oracle, PostgreSQL, and SQL Server, it raises an exception. For example, Oracle raises the following exception:
SELECT a.studentname
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression |
The correct way to write the GROUP BY is:
SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
Then, it would return:
Student Name Course IDs ------------------------------ ---------- Montgomery Scott 1 Leonard McCoy 2 James Tiberus Kirk 3 |
For reference, here’s a complete test case for MySQL:
/* Drop table conditionally. */ DROP TABLE IF EXISTS students; /* Create table. */ CREATE TABLE students ( studentID int unsigned primary key auto_increment , studentName varchar(30)); /* Drop table conditionally. */ DROP TABLE IF EXISTS courses; /* Create table. */ CREATE TABLE courses ( courseid int unsigned primary key auto_increment , coursename varchar(40)); /* Drop table conditionally. */ DROP TABLE IF EXISTS registeredcourses; /* Create table. */ CREATE TABLE registeredcourses ( courseid int unsigned , studentid int unsigned ); /* Insert into students. */ INSERT INTO students ( studentName ) VALUES ('James Tiberus Kirk') ,('Leonard McCoy') ,('Montgomery Scott'); /* Insert into courses. */ INSERT INTO courses ( coursename ) VALUES ('English Literature') ,('Physics') ,('English Composition') ,('Botany') ,('Mechanical Engineering'); /* Insert into registeredcourses. */ INSERT INTO registeredcourses ( studentid , courseid ) VALUES (1,1) ,(1,3) ,(1,4) ,(2,2) ,(2,5) ,(3,4); /* Check global sql_mode to ensure only_full_group_by is set. */ SELECT @@GLOBAL.SQL_MODE; /* Query with a column not found in the SELECT-list. */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; /* Query consistent with ANSI SQL:1992 */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
and, another complete test case for Oracle:
/* Drop tabhe unconditionallly. */ DROP TABLE students; /* Create table. */ CREATE TABLE students ( studentID NUMBER PRIMARY KEY , studentName VARCHAR(30)); /* Drop table unconditionally. */ DROP TABLE courses; /* Create table. */ CREATE TABLE courses ( courseid NUMBER PRIMARY KEY , coursename VARCHAR(40)); /* Drop table unconditionally. */ DROP TABLE registeredcourses; /* Create table. */ CREATE TABLE registeredcourses ( courseid NUMBER , studentid NUMBER ); /* Insert values in student. */ INSERT INTO students ( studentid, studentName ) VALUES (1,'James Tiberus Kirk'); INSERT INTO students ( studentid, studentName ) VALUES (2,'Leonard McCoy'); INSERT INTO students ( studentid, studentName ) VALUES (3,'Montgomery Scott'); /* Insert values in courses. */ INSERT INTO courses ( courseid, coursename ) VALUES (1,'English Literature'); INSERT INTO courses ( courseid, coursename ) VALUES (2,'Physics'); INSERT INTO courses ( courseid, coursename ) VALUES (3,'English Composition'); INSERT INTO courses ( courseid, coursename ) VALUES (4,'Botany'); INSERT INTO courses ( courseid, coursename ) VALUES (5,'Mechanical Engineering'); /* Insert values into registeredcourses. */ INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,1); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,3); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,4); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,2); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,5); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (3,4); /* Non-ANSI SQL GROUP BY statement. */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; /* ANSI SQL GROUP BY statement. */ SELECT a.studentname AS "Student Name" , COUNT(b.courseid) AS "Course IDs" FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
I hope this helps those learning the correct way to write SQL.
Variable Scope
SELECT-list, there are rules for how you can assign the SELECT-list values. The process is more or less an all or nothing approach when assigning values to a local variable.
The rule is quite simple for scalar variables because the SELECT-list may contain multiple values but assignments must be made one at a time. You can’t mix retrieving values with assignments. Lets say you try to write the following block of T-SQL code:
1 2 3 4 5 | SELECT 'Execution Scope' AS "Statement"; DECLARE @base NVARCHAR(MAX) = N''; WITH x AS (SELECT 'Chilly' AS cold, 'Burning up' AS hot) SELECT @base += cold, hot FROM x; SELECT @base AS "Result"; |
Msg 141, Level 15, State 1, Server DESKTOP-4U7EN27, Line 3 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. |
1 2 3 4 5 | SELECT 'Execution Scope' AS "Statement"; DECLARE @base NVARCHAR(MAX) = N''; WITH x AS (SELECT 'Chilly' AS cold, 'Burning up' AS hot) SELECT @base += cold FROM x; SELECT @base AS "Result"; |
SELECT-list, the assignment statement appends the value associated with the column name or alias, which acts like a key in a dictionary. While a dictionary is a collection of name and value pairs, you can use the name of any dictionary element as a key to return only one value from the dictionary. That is the value that the name identifies in the dictionary.
You can put a GO on line 6 in an interactive session or you can put the five lines into a T-SQL script file and call it from the sqlcmd utility. You can run the script file with the following syntax, assuming you have a student user with student as its password working against items in the studentdb schema:
sqlcmd -Ustudent -Pstudent -dstudentdb -y40 -itestScope.sql -otestScope.out |
GO command. T-SQL script files are bundles that execute in a single execution scope unless you embed the GO command.
Then, you can display the testScope.out file as follows from the command line (in a cmd session):
type testScope.out |
Statement --------------- Execution Scope (1 rows affected) Result ---------------------------------------- Chilly (1 rows affected) |
SQL Server XQuery
I promised my students an example of writing xquery statements in Microsoft SQL Server. This post builds on two earlier posts. The first qualifies how to build a marvel table with source data, and the second qualifies how you can create an XML Schema Collection and insert relational data into an XML structure.
You can query a sequence with xquery as follows:
DECLARE @x xml; SET @x = N''; SELECT @x.query('(1,2,(10,11,12,13,14,15)),-6'); |
It returns:
1 2 10 11 12 13 14 15 -6 |
You can query a sequence with an xquery FLOWR statement. FLOWR stands for: FOR, LET, ORDER BY, WHERE, and RETURN. A sample xquery with a FLOWER statement is:
DECLARE @x xml; SET @x = N''; SELECT @x.query('for $i in ((1,2,(10,11,12,13,14,15)),-6) order by $i return $i'); |
It returns:
-6 1 2 10 11 12 13 14 15 |
You can query the entire node tree with the following xquery statement because it looks for the occurrence of any node with the /* search string:
DECLARE @x xml; SET @x = N'<marvel> <avenger_name>Captain America</avenger_name> </marvel>'; SELECT @x.query('/*'); |
You can query the avenger_name elements from the marvel_xml table with the following syntax:
SELECT xml_table.query('/marvel/avenger_name') FROM marvel_xml; |
It returns the following set of avenger_name elements:
<avenger_name>Hulk</avenger_name> <avenger_name>Iron Man</avenger_name> <avenger_name>Black Widow</avenger_name> <avenger_name>Thor</avenger_name> <avenger_name>Captain America</avenger_name> <avenger_name>Hawkeye</avenger_name> <avenger_name>Winter Soldier</avenger_name> <avenger_name>Iron Patriot</avenger_name> |
You can query the fourth avenger_name element from the marvel_xml table with the following xquery statement:
SELECT xml_table.query('/marvel[4]/avenger_name') FROM marvel_xml; |
It returns the following avenger_name element:
<avenger_name>Thor</avenger_name> |
You can use the value() function to verify an avenger_name element exists:
SELECT CASE WHEN xml_table.value('(/marvel[4]/avenger_name)','nvarchar') = 'T' THEN 'Exists' END AS "Verified" FROM marvel_xml WHERE id = 3; |
The query returns the Exists string when it finds a valid avenger_name element. You have a number of other tools to query results sets from the XML node tree.
I hope this helps my students and anybody interested in writing xquery-enable queries.
Insert into XML Column
Working through Chapter 7 of the Querying Microsoft SQL Server 2012 book for Microsoft’s Exam 70-461, I found the XML examples incomplete for my students. I decided to put together a post on how to create:
- An XML Schema Collection type.
- A table that uses an XML Schema Collection as a column’s data type.
- An example on how you can transfer the contents of a table into the XML Schema Collection.
This post assumes you understand the basics about XML structures. If you’re unfamiliar with XML, please note that everything within it is case sensitive unlike SQL. You raise exceptions when the case of your XML fails to match the case of your XML Schema Collection definitions. I raised the following exception by using a Marvel element tag in title case when the XML Schema Collection uses a lowercase marvel element tag:
Msg 6913, Level 16, State 1, Line 2 XML Validation: Declaration not found for element 'Marvel'. Location: /*:Marvel[1] |
The basic marvel table is defined in this earlier blog post. To ensure you don’t run into conflicts with previously existing objects, you can delete the marvel table with the following syntax:
1 2 | IF OBJECT_ID('studentdb.marvel_xml','U') IS NOT NULL DROP TABLE marvel_xml; |
There is no predefined function that lets you conditionally drop the XML Schema Collection from the data base. The alternative is to query the Microsoft SQL Server data catalog for the existence of a row before dropping the XML Schema Collection, like this:
1 2 3 4 | IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'MarvelXMLTable') DROP XML SCHEMA COLLECTION MarvelXMLTable; ELSE SELECT 'Not found.'; |
You can now create the MarvelXMLTable XML Schema Collection with the following syntax:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE XML SCHEMA COLLECTION MarvelXMLTable AS N'<?xml version="1.0" encoding="UTF-16"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"> <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" /> <xsd:element name="Marvel"> <xsd:complexType> <xsd:sequence> <xsd:element name="marvel_id" type="sqltypes:int" /> <xsd:element name="avenger_name" type="sqltypes:nvarchar" /> <xsd:element name="first_name" type="sqltypes:nvarchar" /> <xsd:element name="last_name" type="sqltypes:nvarchar" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>'; |
After creating the XML Schema Collection, you can confirm whether it works correctly with the following statements. It will fail when you use semicolons on the DECLARE or SET lines, so avoid them as shown below:
1 2 3 4 5 6 7 8 | DECLARE @marvel AS XML(MarvelXMLTable) SET @marvel = '<marvel> <marvel_id>1</marvel_id> <avenger_name>Falcon</avenger_name> <first_name>Sam</first_name> <last_name>Wilson</last_name> </marvel>' SELECT @marvel; |
After creating and verifying the integrity of the XML Schema Collection, you can create a marvel_xml table. The xml_table column of the marvel_xml table uses a strongly-typed XML type, as shown:
1 2 3 | CREATE TABLE marvel_xml ( id INT IDENTITY(1,1) CONSTRAINT marvel_xml_pk PRIMARY KEY , xml_table XML(MarvelXMLTable)); |
You can write an INSERT statement with single element like this:
1 2 3 4 5 6 7 8 9 | INSERT INTO marvel_xml ( xml_table ) VALUES ('<Marvel> <marvel_id>9</marvel_id> <avenger_name>Falcon</avenger_name> <first_name>Sam</first_name> <last_name>Wilson</last_name> </Marvel>'); |
You can insert two elements with an INSERT statement like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | INSERT INTO marvel_xml ( xml_table ) VALUES ('<Marvel> <marvel_id>9</marvel_id> <avenger_name>Falcon</avenger_name> <first_name>Sam</first_name> <last_name>Wilson</last_name> </Marvel> <Marvel> <marvel_id>10</marvel_id> <avenger_name>Scarlet Witch</avenger_name> <first_name>Wanda</first_name> <last_name>Witch</last_name> </Marvel>'); |
There’s a lot of typing to insert XML literal values. It a lot easier to transfer relational data from a table to an XML type. Unfortunately, Microsoft didn’t make it very easy. The FOR XML AUTO doesn’t work for two reasons. First, the FOR XML AUTO clause doesn’t render the correct XML structure. Second, the INSERT statement disallows nested SELECT queries that include the FOR XML AUTO clause.
Here’s the way you insert relational data into an XML type column:
1 2 3 4 5 6 7 8 9 10 11 12 13 | DECLARE @xml_elements NVARCHAR(MAX); SET @xml_elements = N''; WITH x AS (SELECT CONCAT(N'<marvel>' ,N'<marvel_id>',marvel_id,N'</marvel_id>' ,N'<avenger_name>',avenger_name,N'</avenger_name>' ,N'<first_name>',first_name,N'</first_name>' ,N'<last_name>',last_name,N'</last_name>' ,N'</marvel>') AS element FROM marvel) SELECT @xml_elements += element FROM x; INSERT INTO marvel_xml (xml_table) VALUES (@xml_elements); |
Line 1 declares an xml_element session variable. Line 2 initializes the xml_element session variable as an empty string. The WITH clause on lines 3 through 10 creates a Common Table Expression (CTE) with the valid XML structure for all rows in the marvel table. The nested SELECT statement on line 11 returns data from the CTE and adds returned row to the session-level string variable. Finally, the INSERT statement on lines 12 and 13 inserts the XML table into the xml_table column.
You can then query the table with the following statement:
SELECT xml_table FROM marvel_xml; |
Click on the return type in the SQL Server Management Studio (SSMS), and the XML structure will expand to show this:
<marvel> <marvel_id>1</marvel_id> <avenger_name>Hulk</avenger_name> <first_name>Bruce</first_name> <last_name>Banner</last_name> </marvel> <marvel> <marvel_id>2</marvel_id> <avenger_name>Iron Man</avenger_name> <first_name>Tony</first_name> <last_name>Stark</last_name> </marvel> <marvel> <marvel_id>3</marvel_id> <avenger_name>Black Widow</avenger_name> <first_name>Natasha</first_name> <last_name>Romanoff</last_name> </marvel> <marvel> <marvel_id>4</marvel_id> <avenger_name>Thor</avenger_name> <first_name>Thor</first_name> <last_name>Odinsson</last_name> </marvel> <marvel> <marvel_id>5</marvel_id> <avenger_name>Captain America</avenger_name> <first_name>Steve</first_name> <last_name>Rogers</last_name> </marvel> <marvel> <marvel_id>6</marvel_id> <avenger_name>Hawkeye</avenger_name> <first_name>Clint</first_name> <last_name>Barton</last_name> </marvel> <marvel> <marvel_id>7</marvel_id> <avenger_name>Winter Soldier</avenger_name> <first_name>Bucky</first_name> <last_name>Barnes</last_name> </marvel> <marvel> <marvel_id>8</marvel_id> <avenger_name>Iron Patriot</avenger_name> <first_name>James</first_name> <last_name>Rhodey</last_name> </marvel> |
As always, I hope this post benefits those who read it.
Functions disallow NDS
My students asked if you could embed an OFFSET x ROWS FETCH NEXT y ROWS ONLY clause in a SQL Server T-SQL user-defined function. The answer is no, it isn’t Oracle (yes, you can do that in Oracle Database 12c with an NDS statement). There’s an example in Chapter 2 of my Oracle Database 12c PL/SQL Programming book if you’re interested. I also demonstrate a different approach to SQL Server T-SQL table functions in this older post. However, an attempt to add the clause to a SQL Server T-SQL function, like this:
CREATE FUNCTION studentdb.getBatch (@rows AS INT ,@offset AS INT) RETURNS @output TABLE ( marvel_id INT , avenger_name VARCHAR(30) , first_name VARCHAR(20) , last_name VARCHAR(20)) AS BEGIN /* Insert the results into the table variable. */ INSERT @output SELECT marvel_id , avenger_name , first_name , last_name FROM studentdb.marvel OFFSET (@offset - 1) ROWS FETCH NEXT @rows ROWS ONLY; /* Return the table variable from the function. */ RETURN; END; |
Throws the following errors trying to compile the function:
Msg 102, Level 15, State 1, Procedure getBatch, Line 16 Incorrect syntax near '@offset'. Msg 153, Level 15, State 2, Procedure getBatch, Line 16 Invalid usage of the option NEXT in the FETCH statement. |
If you have a strong background in Oracle and can sort through the dynamic SQL syntax for T-SQL, you might try re-writing the function to use the EXEC SP_EXECUTESQL @var; command. That rewrite that attempts to use NDS (Native Dynamic SQL) would look like this:
CREATE FUNCTION studentdb.getBatch (@rows AS INT ,@offset AS INT) RETURNS @output TABLE ( marvel_id INT , avenger_name VARCHAR(30) , first_name VARCHAR(20) , last_name VARCHAR(20)) AS BEGIN DECLARE /* Declare a variable for a dynamic SQL statement. */ @stmt VARCHAR(400); /* Assign the SQL statement to a variable. */ SET @stmt = N'SELECT marvel_id ' + N', avenger_name ' + N', first_name ' + N', last_name ' + N'FROM studentdb.marvel ' + N'OFFSET ' + (@offset - 1) + N' ' + N'ROWS FETCH NEXT ' + @rows + N' ROWS ONLY;'; BEGIN /* Insert the results into the table variable. */ INSERT @output EXEC sp_executesql @stmt; END; /* Return the table variable from the function. */ RETURN; END; |
Throws the following exception because you can’t use dynamic dispatch inside a T-SQL function:
Msg 443, Level 16, State 14, Procedure getBatch, Line 23 Invalid use of a side-effecting operator 'INSERT EXEC' within a function. |
On the other hand you can rewrite the statement with a BETWEEN operator and it works somewhat like an OFFSET and FETCH operation. That refactored function would be written as follows:
CREATE FUNCTION studentdb.getBatch (@rowa AS INT ,@rowb AS INT) RETURNS @output TABLE ( marvel_id INT , avenger_name VARCHAR(30) , first_name VARCHAR(20) , last_name VARCHAR(20)) AS BEGIN /* Insert the results into the table variable. */ INSERT @output SELECT marvel_id , avenger_name , first_name , last_name FROM studentdb.marvel WHERE marvel_id BETWEEN @rowa AND @rowb; /* Return the table variable from the function. */ RETURN; END; |
It doesn’t raise an exception. You can call the table function like this:
SELECT * FROM getBatch(2,3); |
It returns the two rows for Iron Man and Black Widow. As always, I hope this helps.
If you want to create the test case, here’s the script you need:
SELECT 'Conditionally drop studentdb.marvel table.' AS "Statement"; IF OBJECT_ID('studentdb.marvel','U') IS NOT NULL DROP TABLE studentdb.marvel; SELECT 'Create studentdb.marvel table.' AS "Statement"; CREATE TABLE studentdb.marvel ( marvel_id INT NOT NULL IDENTITY(1,1) CONSTRAINT marvel_pk PRIMARY KEY , avenger_name VARCHAR(30) NOT NULL , first_name VARCHAR(20) NOT NULL , last_name VARCHAR(20) NOT NULL); /* Insert the rows. */ INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Hulk','Bruce','Banner'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Iron Man','Tony','Stark'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Black Widow','Natasha','Romanoff'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Thor','Thor','Odinsson'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Captain America','Steve','Rogers'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Hawkeye','Clint','Barton'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Winter Soldier','Bucky','Barnes'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Iron Patriot','James','Rhodey'); /* Query the contents of the MARVEL table. */ SELECT * FROM studentdb.marvel; |
Subscribe via RSS