Archive for March 15th, 2009

SQLXML : How to List Columns That Have the XML Data Type

   1: -- this lists the table catalog, table name, 
   2: -- column name, and data type
   3: SELECT 
   4:     TABLE_CATALOG,
   5:     TABLE_NAME,
   6:     COLUMN_NAME, 
   7:     DATA_TYPE 
   8: FROM 
  10: WHERE 
  11:     DATA_TYPE = 'xml'


   1: -- this lists the corresponding schemas
   2: SELECT 
   3:     DISTINCT
   4:     OBJECT_NAME(sys.columns.object_id)        AS 'TableName',
   5:                    AS 'ColName',
   6:            AS 'Schema' 
   7: FROM 
   8:     sys.columns
   9:     LEFT JOIN     sys.xml_schema_collections 
  10:     ON sys.columns.xml_collection_id = sys.xml_schema_collections.xml_collection_id
  11: ORDER BY 
  12:     OBJECT_NAME(sys.columns.object_id),    

VN:F [1.9.22_1171]
Rating: 9.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQLXML : How to List Schema Elements and Attributes

   1: SELECT
   2:     sys.xml_schema_collections.xml_collection_id    AS CollectionID,
   3:                 AS SchemaName,
   4:                    AS ElementName,
   5:                  AS AttributeName
   6: FROM
   7:     sys.xml_schema_collections
   8:     INNER JOIN sys.xml_schema_attributes
   9:     ON sys.xml_schema_collections.xml_collection_id =  sys.xml_schema_attributes.xml_collection_id
  10:     INNER JOIN sys.xml_schema_elements
  11:     ON sys.xml_schema_collections.xml_collection_id = sys.xml_schema_elements.xml_collection_id
  12: WHERE
  13: NOT LIKE 'sys'

Sample Result:

SQLXML Sample Result - Elements and Attribute Names

VN:F [1.9.22_1171]
Rating: 8.0/10 (5 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)

SQLXML : How to Create an XML Schema

The following is an example of how to create an XML Schema in SQL Server.

   2: IF    EXISTS (SELECT 1 
   3:               FROM  sys.xml_schema_collections 
   4:               WHERE name='SampleSchema')
   8: '<?xml version="1.0" encoding="utf-8"?>
   9: <xsd:schema xmlns:xsd="">
  10:   <xsd:element name="Sample">
  11:     <xsd:complexType>
  12:       <xsd:attribute name="SampleID" type="xsd:integer" />
  13:       <xsd:attribute name="Name" type="xsd:string" />
  14:       <xsd:attribute name="Description" type="xsd:string" />
  15:     </xsd:complexType>
  16:   </xsd:element>
  17: </xsd:schema>'


To check your XML Schema:

Method 1: Go to your database > Programmability > Types > XML Schema Collections

SQLXML - XML Schema Collection

Method 2: You can use the xml_schema_namespace function to query the schema from within SSMS

   2: SELECT
   3:    xml_schema_namespace(N'dbo',N'SampleSchema') 


Later on I will post additional sample schemas which use different SQL XML data types.

VN:F [1.9.22_1171]
Rating: 8.9/10 (7 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
   1: SELECT * 
   2: FROM  sys.xml_schema_collections 
   3: WHERE name='NameOfSchema'

VN:F [1.9.22_1171]
Rating: 10.0/10 (2 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Hooked on Japanese Green Teas

I love Japanese Green teas. I am not a tea connoisseur but I can tell the difference between a good Japanese tea and a not so good one.

I think I started checking out tea stores and online tea shops 2-3 years ago. I have found a really good online Japanese tea shop, Hibiki-an, where I purchase my tea from. Yups, my tea is flown directly from Japan. They also used to have free international shipping if you purchase $30 USD (I think) or more. Right now though they increased that price to $38 USD (ouch).

My favorites are:

Genmaicha – the Genmaicha tea with small matcha balls is my ultimate favorite tea. Love love love it. Excerpt from the Hibiki-an site:

Genmaicha is a mellow blend of roasted and puffed brown rice grains and Sencha that has been well-loved by green tea fans in Japan for centuries.

Our Genmaicha Matcha-iri is a wonderful blend of Sencha, puffed brown rice, and Matcha. The Matcha powder lightly coats the Sencha leaves and rice, adding a fresh and mellow taste. Due to the rice, this tea has the rich and hearty flavor of roasted grains or coffee but the freshness of Matcha and Sencha. It is very well balanced, full flavored and smooth.

Added Matcha, it becomes mellow taste and its brewed water is light green color like Gyokuro’s.

In many cases Genmaicha is made from a mix of puffed rice and low grade Sencha, low grade Nibancha (tea from the second harvest of the year) or Sanbancha (tea from the third harvest of the year). Our Genmaicha Matcha-iri is made from only Ichibancha (tea from the first harvest of the year) and is available at a reasonable price. So we are certain that its taste and aroma is much more flavorful and smooth than any other Genmaicha.

Genmaicha Matcha – iri is enjoyed as a popular everyday tea in Japan, so we have priced it just right for your everyday enjoyment!

Sencha – the sencha is a very fresh, flavorful green tea, and the one from Hibiki-an is definitely the highest quality one I’ve tasted to date.

Gyokuro -  it has a subtle sweetness and buttery taste to it. Yum yum. I usually get the Superior one, I have yet to try the Super Premium, but that will have to come later. The price point is way above my budget.

I have yet to try Hibiki-an’s matcha and houjicha teas; I’m sure I’d be ordering those next.

If you’re planning to get the teas, I suggest you also look at getting Chazutsu, a stainless steel airtight tea container that has both an outer and an inner lid. Perfect for keeping that tea freshness.

And nope, I’m not getting paid to blog about Hibiki-an :) I wish I was, or I wish I’d get some freebie yummy Japanese green teas. But I’m not. I just happen to love their teas.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

How to Drop All Stored Procedures in Your Database

One of my dev friends asked me how he can drop all the stored procedures in his test database.

One way to do it is by using a cursor to get the names of the stored procedures in a system table, INFORMATION_SCHEMA table or DMV, and then executing a dynamic query that drops the sproc.

   1: -- this sets up the test database
   2: -- Drop the database if it already exists
   3: USE master
   4: GO
   6: IF  EXISTS (
   7:     SELECT name
   8:     FROM sys.databases
   9:     WHERE name = N'testdb'
  10: )
  11: DROP DATABASE testdb
  12: GO
  14: CREATE DATABASE testdb
  15: GO
  16: USE testdb
  17: GO
  19: CREATE PROC UserStoredProcedure_Sample1
  20: AS
  21:     SELECT 'SQL Server rocks'
  22: GO
  24: CREATE PROC UserStoredProcedure_Sample2
  25: AS
  26:     SELECT 'SQL Server rocks'
  27: GO
  34: -- to do this we have to use EXEC instead of sp_executesql
  35: -- sp_executesql does not accept a DROP command in the SQL String
  36: DECLARE @UserStoredProcedure    VARCHAR(100)
  37: DECLARE @Command                    VARCHAR(100)
  40: SELECT
  42: FROM
  45: OPEN UserStoredProcedureCursor
  47: FETCH NEXT FROM UserStoredProcedureCursor
  48: INTO @UserStoredProcedure
  50:        SET @Command = 'DROP PROCEDURE ' + @UserStoredProcedure
  52:          -- display; visual check
  53:          SELECT @Command
  55:        -- when you are ready to execute, uncomment below
  56:        EXEC (@Command)
  58:        FETCH NEXT FROM UserStoredProcedureCursor
  59:        INTO @UserStoredProcedure
  60: END
  63: CLOSE UserStoredProcedureCursor
  64: DEALLOCATE UserStoredProcedureCursor
VN:F [1.9.22_1171]
Rating: 7.1/10 (23 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 3 votes)

This is a follow up post to : Why SQL Fragmentation Remains High

As mentioned in this previous post, our dilemma was we were rebuilding every index on a nightly basis, and :

  1. the process proves to be very resource intensive
  2. we don’t see the benefit. The fragmentation for some of the indexes remain high.

After some digging, this is what we found:

Rebuild the index only if there will be at least 1000 pages affected. If < 1000 pages will be affected, fragmentation will not really be affected and potentially would remain high.

This is the script that we now use to selectively identify which indexes to rebuild, which indexes to reorganize, and which indexes to leave alone. This script uses the following criteria in determining which action to take:

  • current fragmentation %
  • number of pages used by the index

The current logic is:

  • reorganize index : if fragmentation is > 10 % but < 30% and number of pages > 1000
  • rebuild index : if fragmentation is > 30 and number of pages > 1000

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.9/10 (25 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 3 votes)

Why Index Fragmentation Remains High

In one of companies I worked for, we used to do nightly index rebuilds. What was baffling is even after the nightly rebuilds, the fragmentation for some of the tables remain high.

I did some digging; here’s an explanation on why some index fragmentations remain high.

Both Paul Randal and Kalen Delaney (highly respected, very reputable SQL Server developers/trainers) suggest to rebuild the index only if there’s at least 1000 pages affected. If < 1000 pages, they say fragmentation will not really be affected and potentially would remain high.

I checked the "questionable" tables in our databases, and yes, Paul and Kalen were right on the ball. These pages had <1000 pages each.

  • tableA has 9 pages and initial fragmentation of 875. After rebuilding, fragmentation flip flops between 66% and 77%, and never improves beyond 66%
  • tableB has almost 95000 pages and initial fragmentation of 69%. After rebuilding indexes, fragmentation drops to 0.01%. Succeeding rebuilds keep fragmentation to 0.01%

This makes sense, because for smaller tables an index will not really help – SQL Server will usually prefer to do a table scan.

I will post the script that I used to determine and apply selective index rebuilds.


Follow Up: Script is posted in

A More Effective Selective Index Rebuild/Reorganize Strategy

VN:F [1.9.22_1171]
Rating: 9.8/10 (8 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)