MacLochlainns Weblog

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.

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.

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;

I’m always amazed at the questions that pop up for me. For example, how do you convert an Oracle script that creates my Video Store model to a Microsoft SQL Server script. It’s not very hard but there’s one big caveat, and that’s the fact that system_user is a reserved word. That means you can’t create the Access Control List (ACL) table with a system_user name. The alternative, would be to convert the system_user table name to database_user. That’s what I’ve done in this example.

It’s also important to note that this example uses Microsoft SQL Server’s sqlcmd in batch mode. Naturally, it presumes that you’ve created a student user with a trivial password of student, and a studentdb schema. Also, that you’ve granted privileges so everything works (if you need help on that check my earlier post on how to setup a studentdb schema).

The following is an example of conditionally dropping and then creating a system_user table in an Oracle schema. It uses a CASCADE CONSTRAINTS clause to eliminate dependencies with foreign key values.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT NULL
            FROM   user_tables
            WHERE  table_name = 'SYSTEM_USER') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE system_user CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT NULL
            FROM   user_sequences
            WHERE  sequence_name = 'SYSTEM_USER_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE system_user_s1';
  END LOOP;
END;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

--  Create SYSTEM_USER table.
CREATE TABLE system_user
( system_user_id            NUMBER        CONSTRAINT system_user_pk PRIMARY KEY
, system_user_name          VARCHAR2(20)  CONSTRAINT system_user_nn1 NOT NULL
, system_user_group_id      NUMBER        CONSTRAINT system_user_nn2 NOT NULL
, system_user_type          NUMBER        CONSTRAINT system_user_nn3 NOT NULL
, first_name                VARCHAR2(20)
, middle_name               VARCHAR2(20)
, last_name                 VARCHAR2(20)
, created_by                NUMBER        CONSTRAINT system_user_nn4 NOT NULL
, creation_date             DATE          CONSTRAINT system_user_nn5 NOT NULL
, last_updated_by           NUMBER        CONSTRAINT system_user_nn6 NOT NULL
, last_update_date          DATE          CONSTRAINT system_user_nn7 NOT NULL
, CONSTRAINT system_user_fk1  FOREIGN KEY (created_by)
  REFERENCES system_user (system_user_id)
, CONSTRAINT system_user_fk2  FOREIGN KEY (last_updated_by)
  REFERENCES system_user (system_user_id));
 
-- Create SYSTEM_USER_S1 sequence with a start value of 1001.
CREATE SEQUENCE system_user_s1 START WITH 1001;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT NULL
            FROM   user_tables
            WHERE  table_name = 'COMMON_LOOKUP') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE common_lookup CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT NULL
            FROM   user_sequences
            WHERE  sequence_name = 'COMMON_LOOKUP_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE common_lookup_s1';
  END LOOP;
END;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

--  Create COMMON_LOOKUP table.
CREATE TABLE common_lookup
( common_lookup_id            NUMBER
, common_lookup_context       VARCHAR2(30) CONSTRAINT nn_clookup_1 NOT NULL
, common_lookup_type          VARCHAR2(30) CONSTRAINT nn_clookup_2 NOT NULL
, common_lookup_meaning       VARCHAR2(30) CONSTRAINT nn_clookup_3 NOT NULL
, created_by                  NUMBER       CONSTRAINT nn_clookup_4 NOT NULL
, creation_date               DATE         CONSTRAINT nn_clookup_5 NOT NULL
, last_updated_by             NUMBER       CONSTRAINT nn_clookup_6 NOT NULL
, last_update_date            DATE         CONSTRAINT nn_clookup_7 NOT NULL
, CONSTRAINT pk_c_lookup_1    PRIMARY KEY(common_lookup_id)
, CONSTRAINT fk_c_lookup_1    FOREIGN KEY(created_by) REFERENCES system_user(system_user_id)
, CONSTRAINT fk_c_lookup_2    FOREIGN KEY(last_updated_by) REFERENCES system_user(system_user_id));
 
-- Create a non-unique index on a single column.
CREATE INDEX common_lookup_n1
  ON common_lookup(common_lookup_context);
 
-- Create a unique index based on two columns.
CREATE UNIQUE INDEX common_lookup_u2
  ON common_lookup(common_lookup_context,common_lookup_type);
 
-- Create COMMON_LOOKUP_S1 sequence with a start value of 1001.
CREATE SEQUENCE common_lookup_s1 START WITH 1001;

You can do the same thing for a database_user table in Microsoft SQL Server with the following syntax. Unfortunately, there isn’t a CASCADE CONSTRAINTS clause that we can append in Microsoft SQL Server. The script uses a dynamic SQL statement with a Common Table Expression (CTE) to generate a list of ALTER statements that drop foreign key constraints in the schema.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

/* Drop all foreign keys. */
USE studentdb;
 
/* Create a session variable to hold a command list. */
SELECT 'Create a session variable.' AS "Statement";
DECLARE @sql NVARCHAR(MAX) = N'';
 
/* Generate the command list to drop foreign key constraints. */
SELECT 'Generate dynamic SQL statements.' AS "Statement";
;WITH x AS
(SELECT N'ALTER TABLE ' +
          OBJECT_SCHEMA_NAME(parent_object_id) + N'.' +
          OBJECT_NAME(parent_object_id) + N' ' +
        N'DROP CONSTRAINT ' + name + N';' AS sqlstmt
 FROM     sys.foreign_keys)
SELECT @sql += sqlstmt FROM x;
 
/* Call the dynamically generated statements. */
SELECT 'Execute dynamic SQL statements.' AS "Statement";
EXEC sp_executesql @sql;
 
/* Conditionally drop tables. */
SELECT 'Conditionally drop studentdb.common_lookup table.' AS "Statement";
IF OBJECT_ID('studentdb.database_user','U') IS NOT NULL
  DROP TABLE studentdb.database_user;
 
/* Create a table with self-referencing foreign key constraints. */
SELECT 'Create studentdb.common_lookup table.' AS "Statement";
CREATE TABLE studentdb.database_user
( database_user_id        INT          NOT NULL IDENTITY(1,1) CONSTRAINT database_user_pk PRIMARY KEY
, database_user_name      VARCHAR(20)  NOT NULL
, database_user_group_id  INT          NOT NULL
, database_user_type      INT          NOT NULL
, first_name              VARCHAR(20)
, middle_name             VARCHAR(20)
, last_name               VARCHAR(20)
, created_by              INT          NOT NULL
, creation_date           DATE         NOT NULL
, last_updated_by         INT          NOT NULL
, last_update_date        DATE         NOT NULL
, CONSTRAINT database_user_fk1 FOREIGN KEY (created_by)
  REFERENCES studentdb.database_user (database_user_id)
, CONSTRAINT database_user_fk2 FOREIGN KEY (created_by)
  REFERENCES studentdb.database_user (database_user_id));
 
/* Conditionally drop common_lookup table. */
SELECT 'Conditionally drop studentdb.common_lookup table.' AS "Statement";
IF OBJECT_ID('studentdb.common_lookup','U') IS NOT NULL
  DROP TABLE studentdb.common_lookup;
 
/* Create a table with external referencing foreign key constraints. */  
SELECT 'Create studentdb.common_lookup table.' AS "Statement";
CREATE TABLE studentdb.common_lookup
( common_lookup_id            INT          NOT NULL  IDENTITY(1,1) CONSTRAINT common_lookup_pk PRIMARY KEY
, common_lookup_context       VARCHAR(30)  CONSTRAINT nn_clookup_1 NOT NULL
, common_lookup_type          VARCHAR(30)  CONSTRAINT nn_clookup_2 NOT NULL
, common_lookup_meaning       VARCHAR(30)  CONSTRAINT nn_clookup_3 NOT NULL
, created_by                  INT          CONSTRAINT nn_clookup_4 NOT NULL
, creation_date               DATE         CONSTRAINT nn_clookup_5 NOT NULL
, last_updated_by             INT          CONSTRAINT nn_clookup_6 NOT NULL
, last_update_date            DATE         CONSTRAINT nn_clookup_7 NOT NULL
, CONSTRAINT common_lookup_fk1 FOREIGN KEY(created_by)
  REFERENCES studentdb.database_user (database_user_id)
, CONSTRAINT common_lookup_fk2 FOREIGN KEY(last_updated_by)
  REFERENCES studentdb.database_user (database_user_id));

You can run it from a file by calling the sqlcmd utility. You’ll need to know several things to run it. First, you need to know your database instance. You can capture that from a query against the data dictionary or catalog. Just run the following from inside the Microsoft SQL Server Management Studio (SSMS):

SELECT @@SERVERNAME;

In my case, it shows the following, which is the machine’s hostname a backslash and SQLEXPRESS:

MCLAUGHLINSQL\SQLEXPRESS

The script uses sqltest.sql as a file name, and you can call it from the Windows shell environment like this:

sqlcmd -S MCLAUGHLINSQL\SQLEXPRESS -U student -P student -i C:\Data\MicrosoftSQL\sqltest.sql -o C:\Data\Microsoft\sqltest.out

As always, I hope this helps.

After you’ve installed Microsoft SQL Server 2012 and created a database schema, you still need to setup or configure the database schema. This is the recommended Microsoft SQL Server Best Practice.That means you must grant permissions to a user to work in the database schema, otherwise you get the following type of error message.

Msg 2760, Level 16, State 1, Line 1
The specified schema name "studentdb" either does not exist or you do not have permission to use it.

You receive the foregoing error message when trying to create a table in a database schema that hasn’t been assigned a user or role. This typically occurs after you create a new database schema, which has a <default> owner.

For those new to the idea of schemas (or scheme), they were introduced by Microsoft in SQL Server 2005. Here are some rules of thumb on schemas:

• Database schema names are distinct from user names.
• Multiple users may share a schema, which means it can hold objects owned by multiple users.
• Permissions to act inside the schema may be granted to individual users, and you have a varied set of privileges that you can assign to users.
• Ownership of objects in a schema can be transferred using the ALTER SCHEMA command.
• Ownership of a schema can be changed by using the ALTER AUTHORIZATION command.
• Database users can be dropped without changing objects in schemas, which is a big difference between Oracle and SQL Server.

The following example occurs when you try to create a table in a studentdb database schema. It doesn’t expose you to the SQL syntax but demonstrates how to manage the changes within the SQL Server Management Studio (SSMS).

By the way, you start the new schema creation process by right clicking on the Databases folder in the SQL Server Management Studio. Then, you give the new database schema a name in the New Database dialog; and click the OK button to complete the process.

The balance of the instructions show you how to create a user account that will map to your new database schema. It assumes you’ve installed it on your local machine and have privileges through local Windows Authentication to the System Administrator (sa) account. You create a student user account, assign the studentdb database schema, log off as the System Administrator, log in as the new user, conditionally drop a table from the studentdb schema, create a table in the studentdb schema, and query the results. It’s also possible to create the user first, database second, and assign the database as the default database for the user account.

1. The first screen asks you to authenticate as the System Administrator using Windows Authentication. Click the Connect button to connect to the SQL Server Management System (SMSS).

2. Click the Security folder and expand the subordinate list of folders. Right click on the Logins folder and click on the New Login option in the context menu. It launches a new dialog where you enter a user account.

3. On the General page of the Login – New dialog, enter a Login name and click the SQL Server authentication radio button. Clicking the radio button enables the Password and Confirm password fields where you enter the same password twice. Click the Default database drop down and choose the studentdb created earlier. Click the Server Roles page to continue.

4. On the Server Roles page, ensure that only the public server role is checked. Click on the User Mapping page to continue.

5. On the User Mapping page, click the Map check box for the studentdb database, enter student in the User field, and enter studentdb in the Default Schema field. Click the db_owner and public database role membership for studentdb. Click on the Securables page to continue.

6. There are no required changes on the Securables page. Check that screen matches the illustration at the left, and click on the Status page to continue.

7. There are no required changes on the Status page. Check that screen matches the illustration at the left, and click on the OK button to complete creating the user. Then, exit the SQL Server Management Studio as the System Administrator.

8. Launch the SQL Server Management Studio, choose SQL Server Authentication as the authentication method, and enter the student Login (user name) and Password before clicking the Connect button to continue.

9. The first time a user logs in, they must re-enter a valid password. After entering the password twice, click the OK button to continue.

10. The initial display shows only the Object Explorer. Click the New Query option in the menu toolbar.

11. Click on the Databases folder to expand the list and you will see the studentdb database schema displayed, as shown in the illustration on the left.

12. Enter a conditional drop of a conquistador table from the studentdb database schema. Click the Execute button from the toolbar menu to run the statement.

IF OBJECT_ID('studentdb.conquistador','U') IS NOT NULL
  DROP TABLE studentdb.conquistador;

13. Enter a CREATE statement for a conquistador table in the studentdb database schema. Click the Execute button from the toolbar menu to run the statement.

CREATE TABLE studentdb.conquistador
( conquistador_id   INT  NOT NULL  IDENTITY(1,1) CONSTRAINT pk_conquistador PRIMARY KEY
, conquistador      VARCHAR(30)
, actual_name       VARCHAR(30)
, nationality       VARCHAR(30));

14. Enter an INSERT statement to the conquistador table in the studentdb database schema. Click the Execute button from the toolbar menu to run the statement and see that it inserted 9 rows.

INSERT INTO conquistador
VALUES
 ('Juan de Fuca','Ioánnis Fokás','Greek')
,('Nicolás de Federmán','Nikolaus Federmann','German')
,('Sebastián Caboto','Sebastiano Caboto','Venetian')
,('Jorge de la Espira','Georg von Speyer','German')
,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian')
,('Wenceslao Linck','Wenceslaus Linck','Bohemian')
,('Fernando Consag','Ferdinand Konšcak','Croatian')
,('Américo Vespucio','Amerigo Vespucci','Italian')
,('Alejo García','Aleixo Garcia','Portuguese');

15. Click the studentdb folder to expand content, and you should see the conquistador table.

SELECT * FROM conquistador;

16. Click in the SQL Query frame and enter a query against the conquistador table. Click the Execute button to run the query and you should see the following output from the conquistador table.

As always, I hope this helps those trying to sort out how to solve a similar problem.

While testing the MySQL Workbench migration tool, I needed to install Microsoft SQL Server 2012 Express (the free one up to 8 GB of data). These are my notes on installing the product, and you can click on any image to see the full size resolution and details. (The approach using small snapshots on the left was suggested from somebody who uses the blog and felt these would be easier than large but slightly reduced image files.)

For MySQL readers, this was posted as part of a replicateable test case for Alfredo’s MySQL Workbench team. A Windows OS version of Bug 66516.

Installation Steps

For reference, I’ve already installed the pre-requisites of Windows PowerShell 2.0 and Microsoft .NET Framework 3.5. You’ll see that when you get to step #6.

1. The first screen gives you the appearance of a wizard format with the list of tasks on the left and the details on the right. Click the New SQL Server stand-alone installation or add features to an existing installation to install a copy of SQL Server 2012 Express with a sample database.

2. The Microsoft SQL Server 2012 installation firsts checks the rules for the installation. This screen lasts for about 20-30 seconds before you get the licensing dialog box.

3. There’s not much choice with this dialog but you must confirm you’ve read the license terms. If you want to keep Microsoft apprise about your platform leave the button check box enabled. It’s unchecked in my installation. 😉 After you address the check boxes, click the Next button to proceed with the installation.

4. This dialog should quickly scan for any product updates, which are minimal if you’ve downloaded the file from Microsoft’s download site. Mine was up to date and skipped downloading and extracting tasks. It does take a small amount of time to install the setup files, and you’re parked on this screen while that happens. Click the Install button to continue with the installation.

5. A second rule check runs to make sure anything from the software update is completed. Click the Next button to proceed.

6. This page presents the Features that you want installed. It installs everything but a local database by default. If you’re building a stand alone instance you should check the LocalDB check box.

7. After you check the LocalDB check box, the Features display shows that all features are selected. Click the Next button to continue.

8. This dialog lets you choose whether you install a default or named instance. You also provide the instance unique identifier (ID), and root directory. After making sure that’s completed, click the Next button ton continue.

9. This section lets you name and set Microsoft Service names for the database engine, reporting services, full-text filter daemon launche, and browser. My settings enable the database engine and reporting services as automatic, which means they start when the operating systems wakes. Click the Next button to continue with the install.

10. This dialog lets you set the authentication mode of Microsoft SQL Server. By default, Microsoft SQL Server uses Windows authentication mode. I recommend you opt for Mixed Mode by clicking the radio button and entering a password twice.

11. After entering the SQL Server Administrators’ password in the Server Configuration tab view, click the Data Directories tab to check the data directories.

12. On the Data Directories tab verify the installation directories. The 64-bit directory default root directory is: C:\Program Files\Microsoft SQL Server\

13. After inspecting the Data Directories, click the User Instances tab and enable the Users are allowed to run a separate instance of the Database Engine check box.

14. Clicking the FILESTREAM tab, you click the Enable FILESTREAM for Transact-SQL access check box. This enables FILESTREAM for Transact-SQL. Click the Next button to proceed.

15. This dialog of the wizard lets you configure the reporting services. Click the Install and configure radio button and then the Next button to proceed.

16. This dialog lets you set the error reporting. Click the Send Windows and SQL Server Error Reports to Microsoft if you want to advise them of errors. After making your notification decision, click the Next button to proceed.

17. The installation program takes several minutes to run, and this is a copy of the progress dialog. After the installation completes, the program automatically advances to the completion dialog.

18. When everything succeeds you’ll see a dialog like the one on the left. Click the Close button to complete the installation.

19. Click on the Start button, choose All Programs, choose Microsoft SQL Server 2012, and choose the SQL Server Management Studio that launches the SQL Server 2012 login screen.

20. This launches the Microsoft SQL Server Management Studio as an Administrator. This base image only displays the Object Explorer view.

21. Click the View Query button and it displays the SQL Query and Properties view.

Post Installation Configuration Step

After installing the Microsoft SQL Server 2012 Express Edition, I discovered that the pipe definition was incorrect and had to fix it, which was exactly like the fix for Microsoft SQL Server 2008 that I blogged about 3 years ago here.

If you want to set up databases that hold tables of their own as opposed to deploying everything in the dbo schema, you should read this post on configuring SQL Server 2012. It shows you how to create a database, user, and schema.

You’ll need to configure a Windows Data Name Source (DSN) for SQL Server, when you’re planning to connect Microsoft Excel to SQL Server or migrate data from SQL Server using MySQL Workbench.