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")])') 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()) 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")])') GO
The Employee node has been removed:
You may have noticed the singleton designation (‘‘) 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.
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 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)','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!
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'
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:
WHERE TABLE_CATALOG = ‘AdventureWorks’
AND TABLE_NAME = ‘Contact’
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.
USE AdventureWorks2012 GO GRANT SELECT ON OBJECT::[Person].[Password] TO [dba_test] GO
CREATE USER [dba_test] FOR LOGIN [BUILTIN\Users] GO
USE AdventureWorks2012 GO GRANT EXECUTE ON OBJECT::dbo.uspGetBillOfMaterials TO [dba_test] GO
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 ___________________________________________________________________________________________________
Need to duplicate a table’s schema? Use a SELECT…INTO…FROM statement:
SELECT * INTO new_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
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.