A Patriotic SQL Script

XQuery Lab 2

In our first XQuery lab, we used the query() method to filter XML tags for specific criteria matches.  This time, let’s work on performing DML operations using an XML extension method – modify().

We again need some test data, so let’s create a table and insert a record of XML data:

CREATE TABLE EMPLOYEES (ID INT IDENTITY, Details XML)
GO
INSERT EMPLOYEES VALUES(
 '<Employees>
 <Employee ID="1" salary="49000">
 <Department>Sales</Department>
 </Employee>
 <Employee ID="2" salary="56000" />
 <Employee ID="3" salary="79000" />
 </Employees>
 '
)
GO

We now have one record of Employee details, in the form of an XML BLOB, in the Employees table:

Notice that not all employees have a Department node.  Let’s use the insert…into keywords to insert a node for employee #2:

UPDATE EMPLOYEES
SET Details.modify('insert <Department>Development</Department> into 
(/Employees/Employee[@ID=("2")])[1]')
GO

We have created a new Department node for employee #2:

We can also use the modify() method to replace values using the replace value of keywords.  Employee #1 should not be a member of the Sales department.  Let’s replace the Department node value with “Quality Assurance”:

UPDATE EMPLOYEES
SET Details.modify('replace value of 
(/Employees/Employee[@ID=("1")]/Department/text())[1] with ("Quality Assurance")')
GO

The Department node’s value has been replaced:

The modify() method also accommodates deleting nodes.  Let’s use it to remove the Employee node for employee #3:

UPDATE EMPLOYEES
SET Details.modify('delete (/Employees/Employee[@ID=("3")])[1]')
GO

The Employee node has been removed:

You may have noticed the singleton designation (‘[1]‘) we’ve used at the end of every node location specification.  This is required for the insert and replace value of keywords, to indicate which node instance will receive the change, even if there is only one instance of the node.  We did not need to use the singleton in our delete operation, as there was only one Employee node having an ID of 3.  Delete will remove the first instance that matches the criteria; the singleton is not required.

 

[XQuery Lab 1]

Find all XML columns in table

Two methods:

#1:

 

 

 

 

 

 

 

 

 

#2:

Get List of SQL Server Agent Job Names, Category Names

Need to get a quick list of all SQL Agent jobs, with their respective categories?  Join the sysjobs table to the syscategories table:

SELECT j.[job_id], j.[name] [job_name], j.[category_id], c.name [category_name]
FROM [msdb].[dbo].[sysjobs] j
JOIN [msdb].[dbo].[syscategories] c
ON j.category_id = c.category_id
ORDER BY 2

XQuery Lab 1

XQuery is a database programming language developed for filtering and manipulating XML BLOB data.  It can be especially useful for ad-hoc reporting.  In this post, I want to show a few examples of some very basic XQuery expressions against untyped XML data.

For our examples, we’ll need sample data.  Let’s create a simple table to hold XML data:

CREATE TABLE Avengers (ID INT IDENTITY, AdditionalInfo XML) 
GO

Now let’s populate the table with a few records:

INSERT INTO Avengers VALUES('
 <Person>
 <Names>
 <Name>
 <First>Tony</First>
 <Last>Stark</Last>
 </Name>
 </Names>
 <Aliases>
 <Alias type="Primary">Iron Man</Alias>
 <Alias type="Other">Shellhead</Alias>
 <Alias type="Other">Golden Avenger</Alias>
 </Aliases>
 </Person>'),
 ('
 <Person>
 <Names>
 <Name>
 <First>Bruce</First>
 <Last>Banner</Last>
 </Name>
 </Names>
 <Aliases>
 <Alias type="Primary">Hulk</Alias>
 <Alias type="Other">Joe Fixit</Alias>
 <Alias type="Other">Green King</Alias>
 <Alias type="Other">Green Goliath</Alias>
 <Alias type="Other">Holku</Alias>
 </Aliases>
 </Person>'),
 ('
 <Person> 
 <Names> 
 <Name> 
 <First>Thor</First>
 <Last>Odinson</Last> 
 </Name>
 </Names> 
 <Aliases>
 <Alias type="Primary">Thor</Alias>
 <Alias type="Other">Donald Blake</Alias>
 <Alias type="Other">Sigurd Jarlson</Alias>
 <Alias type="Other">Jake Olsen</Alias>
 </Aliases>
 </Person> 
 ')
GO

Upon running a SELECT * FROM Avengers, we see the contents of the new table:

You can see that we have 3 records of XML BLOBs.  To perform a simple query, let’s pull the primary Alias for each record:

SELECT AdditionalInfo.query('/Person/Aliases/Alias[@type="Primary"]') 
AS PrimaryAlias 
FROM Avengers
GO

We’ve used the query() XQuery method to do this – it gives us what we want, but we could make this more meaningful by associating the Person’s Name with the Alias, and also remove the XML tags:

SELECT AdditionalInfo.query('/Person/Aliases/Alias[@type="Primary"]/text()')  
AS PrimaryAlias, 
AdditionalInfo.value('(/Person/Names/Name/Last)[1]','VARCHAR(25)') 
AS LastName 
FROM Avengers
GO

This time we’ve added the use of the text() method to remove the XML tags from the PrimaryAlias results, and introduced the value() method to obtain the LastName values – we don’t require the query method for LastName.  The value() method requires a second paramter – data type.  We used VARCHAR, and that effectively converted the result’s XML type to VARCHAR.  We could also convert the PrimaryAlias result values to VARCHAR by wrapping the query in a CONVERT function call.

More XQuery to come!

[XQuery Lab 2]

Get List of Columns in a Table

There is no way to write a SQL Server SELECT statement, using the wildcard, to select all columns in a table except for any number of particular columns.  

For example, if you wanted to return all fields from the Person.Address table except for the City field, you cannot write a query like this:

SELECT ( * -City ) FROM Person.Address

If you need to select 99 of 100 columns in a table, you have to list all 99.  This can be a painful task.  For now, the best way to accomplish this is to generate a list of field names from the table, and copy them into your query.  Here’s how to do it.

Every database in SQL Server 2008 contains system views named sys.obects and sys.columns.  Sys.objects contains all database objects: system tables, user tables, constraints, views, etc.  Sys.columns contains all columns from every table, along with the same object_id of it’s parent object (the table).  By joining these system tables together, you can get a quick list of columns that you can copy and paste into your query:

USE AdventureWorks
GO
SELECT sc.name ColumnName
FROM sys.objects so
JOIN sys.columns sc
ON so.object_id = sc.object_id
WHERE so.name = 'Contact'

This is certainly one query to memorize!

To list columns from ANY user table, drop the table specification, add a filter on sys.objects for ‘type’, and include the table name by using the OBJECT_NAME function:

USE AdventureWorks
GO
SELECT OBJECT_NAME(sc.OBJECT_ID) TableName, sc.name ColumnName
FROM sys.objects so
JOIN sys.columns sc
ON so.object_id = sc.object_id
WHERE so.type = 'U'

This query can be taken even further by joining to the sys.types view, where data type metadata is stored:

USE AdventureWorks
GO
SELECT OBJECT_NAME(sc.OBJECT_ID) TableName, sc.name ColumnName,
t.name [DataType], sc.is_nullable Nullable, sc.is_identity Is_identity
FROM sys.objects so
JOIN sys.columns sc
ON so.object_id = sc.object_id
JOIN sys.types t
ON sc.user_type_id=t.user_type_id
WHERE so.type = 'U'

EDIT:

One reader brought up an alternative method to get a list of columns in a table, by using one of the information schema views.  I like his idea better than using sys.columns:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = ‘AdventureWorks’
AND TABLE_NAME = ‘Contact’

Grant permissions to SQL Server database users on specific objects

Need to allow a user access to only specific tables or other database objects, but not to the entire database? Get granular with the GRANT statement.

To apply granular permissions to a specific object, like a table, use the GRANT [permission] ON [object] TO [user] format.

To grant select permissions on a table to a database user:

USE AdventureWorks2012
GO
GRANT SELECT ON OBJECT::[Person].[Password] TO [dba_test]
GO

If your login has no User Mapping to any database user, you will receive an error like this:

Msg 15151, Level 16, State 1, Line 1

Cannot find the user ‘dba_test’, because it does not exist or you do not have permission.

… and a database user needs to be created for the login:

CREATE USER [dba_test] FOR LOGIN [BUILTIN\Users]
GO

___________________________________________________________________________________

To grant execute permissions on a stored procedure to a database user:

USE AdventureWorks2012
GO
GRANT EXECUTE ON OBJECT::dbo.uspGetBillOfMaterials TO [dba_test]
GO

http://msdn.microsoft.com/en-us/library/ms187965.aspx

Increasing the mimimum memory used per query

SQL Server’s sp_configure settings contains a setting named ‘min memory per query (KB)’. This setting allows you to set the default minimum amount of RAM that’s allocated for each query — but should be used with care. Like many SQL Server settings, the default setting is best for most situations. However, if you have RAM to burn, BOL offers that increasing this setting could have a positive impact on small queries:

“Increasing the value of min memory per query may improve performance for some small to medium-sized queries, but doing so could lead to increased competition for memory resources.”

Updates to the ‘min memory per query (KB)’ setting can be done like this:

--first, turn on advanced options
SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE
GO
--then, set new value - default value is 1024KB, this increases it to 2MB
SP_CONFIGURE 'min memory per query (KB)',2048
GO
RECONFIGURE WITH OVERRIDE
GO
--now, display new value
SP_CONFIGURE 'min memory per query (KB)'
GO
RECONFIGURE WITH OVERRIDE
GO
___________________________________________________________________________________________________

http://msdn.microsoft.com/en-us/library/ms181047.aspx

Copy table schema only

Need to duplicate a table’s schema?  Use a SELECT…INTO…FROM  statement:

SELECT * INTO new_tbl
FROM old_tbl

This will create a copy of the table, data and all.  What if you don’t want the data copied with the schema?  My friend Alessandro Basso uses this method:

SELECT * INTO new_tbl
FROM old_tbl
WHERE 1=2

Genius!

Grouping with ROW_NUMBER

Want to group a result set by only one field, but include others in the result set?  You can use the ROW_NUMBER function with its PARTITION BY parameter.

To play with this, let’s create some sample data:

Now we have a table with sample data:

Using the data we just created, we want to show only the most recent transaction for EACH customer.   We can do a simple GROUP BY, but if we include TransactionID in the result set, we are forced to get a record for each TransactionID, since it has to be contained in the GROUP BY’s list of non-aggregates:

We get the full result set – not what we want.  To include TransactionID (or any other field that may be in the table) in the result set, but grouping only on CustID, we can include the ROW_NUMBER function in the select list, using the PARTITION BY clause:

We get this:

Using the ROW_NUMBER function in the select list (with out the PARTITION BY parameter) simply gives us the unique row number for each record.  When we use the PARTITION BY clause, it groups on CustID.  The ORDER BY clause is required, and necessary for our results – this allows us to make the most recent SaleDate = row 1.

 

I give credit to Dan Humphries for showing examples of ROW_NUMBER here.

http://msdn.microsoft.com/en-us/library/ms186734.aspx