1: SELECT

   2:     sys.xml_schema_collections.xml_collection_id    AS CollectionID,

   3:     sys.xml_schema_collections.name                 AS SchemaName,

   4:     sys.xml_schema_elements.name                    AS ElementName,

   5:     sys.xml_schema_attributes.name                  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:     sys.xml_schema_collections.name NOT LIKE 'sys'

Sample Result:

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

   1: -- DROP IF EXISTS

   2: IF    EXISTS (SELECT 1 

   3:               FROM  sys.xml_schema_collections 

   4:               WHERE name='SampleSchema')

   5:    DROP XML SCHEMA COLLECTION SampleSchema

   6:  

   7: CREATE XML SCHEMA COLLECTION SampleSchema AS 

   8: '<?xml version="1.0" encoding="utf-8"?>

   9: <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  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

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

   1:  

   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.

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.

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

   5:

   6: IF  EXISTS (

   7:     SELECT name

   8:     FROM sys.databases

   9:     WHERE name = N'testdb'

  10: )

  11: DROP DATABASE testdb

  12: GO

  13:

  14: CREATE DATABASE testdb

  15: GO

  16: USE testdb

  17: GO

  18:

  19: CREATE PROC UserStoredProcedure_Sample1

  20: AS

  21:     SELECT 'SQL Server rocks'

  22: GO

  23:

  24: CREATE PROC UserStoredProcedure_Sample2

  25: AS

  26:     SELECT 'SQL Server rocks'

  27: GO

  28:

  29:

  30:

  31:

  32: SET NOCOUNT ON

  33:

  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)

  38:

  39: DECLARE UserStoredProcedureCursor CURSOR SCROLL STATIC READ_ONLY FOR

  40: SELECT

  41:     SPECIFIC_NAME

  42: FROM

  43:     INFORMATION_SCHEMA.ROUTINES

  44:

  45: OPEN UserStoredProcedureCursor

  46:

  47: FETCH NEXT FROM UserStoredProcedureCursor

  48: INTO @UserStoredProcedure

  49: WHILE (@@FETCH_STATUS = 0) BEGIN

  50:        SET @Command = 'DROP PROCEDURE ' + @UserStoredProcedure

  51:

  52:          -- display; visual check

  53:          SELECT @Command

  54:

  55:        -- when you are ready to execute, uncomment below

  56:        EXEC (@Command)

  57:

  58:        FETCH NEXT FROM UserStoredProcedureCursor

  59:        INTO @UserStoredProcedure

  60: END

  61:

  62:

  63: CLOSE UserStoredProcedureCursor

  64: DEALLOCATE UserStoredProcedureCursor

  65:

  66: SET NOCOUNT OFF

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 »

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