7/31/2019 2778A_07

1/41

Module 7:

Querying Metadata, XML,and Full-Text Indexes

7/31/2019 2778A_07

2/41

Module 7: Querying Metadata, XML, and Full-TextIndexes

Querying Metadata

Overview of XML Querying XML Data

Overview of Full-Text Indexes

Querying Full-Text Indexes

7/31/2019 2778A_07

3/41

Lesson 1: Querying Metadata

What Is Metadata?

Compatibility Views System Catalog Views

System Catalog View Examples

Information Schema Views

Information Schema View Examples

Dynamic Management Views and Functions

Dynamic Management Views and Functions Examples

System Stored Procedures and Functions

7/31/2019 2778A_07

4/41

What Is Metadata?

Adds Context to Data

Hides Complexity From End Users

Used in Determination of Data Types

Reveals the Structure & Relationships Between Data

Determines Changes in Data and What Changes WereMade

Used for Type Checking, Data Validation and Formatting of Data

Definition: Metadata is Data about Data .

7/31/2019 2778A_07

5/41

Compatibility Views

Applications developed with previous versions of SQL Servermay use metadata views that are specific to that version

Compatibility views are provided for backward compatibilityfor such applications

Compatibility views should be used for backward compatibilityonly

Compatibility views do not provide information regardingfeatures that are new to the current version of SQL Server

SELECT *FROM sys.sysobjects;

Name Id Xtype Uid Info statusevents -414 V 4 0 0

event_notifications -413 V 4 0 0

triggers -412 V 4 0 0

procedures -411 V 4 0 0

foreign_key_columns -410 V 4 0 0

7/31/2019 2778A_07

6/41

System Catalog Views

Exposes all metadata

Provides the most efficient way to accesscore server metadata

Provides the most direct way to obtain,transform, and present customized forms of metadata

Contains descriptive names for views andcolumns

7/31/2019 2778A_07

7/41

System Catalog View Examples

SELECT name, type_descFROM sys.tables;

Name Type_desc

Product USER_TABLE

Employee USER_TABLE

Customer USER_TABLE

SELECT name, create_date,modify_date

FROM sys.views;

Name Create_date Modify_datevwProducts 7/12/2007 7/12/2007

vwCustomer 7/14/2007 3/22/2008

vwProducts 7/12/2007 2/3/2008

vwOrders 7/12/2007 7/12/2007

SELECT *FROM sys.objects;

name object_id principal_id schema_id

sysrowsetcolumns 4 NULL 4

sysrowsets 5 NULL 4

sysallocunits 7 NULL 4

sysfiles1 8 NULL 4

7/31/2019 2778A_07

8/41

Information Schema Views

Based on catalog view definitions in the ISO standard.

Provide an internal, system table-independent view of the SQLServer metadata.

Are ISO compliant so that Applications that use InformationSchema Views are portable between ISO compliant databasesystems.

InformationSchema

SELECT COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = N'Product';

7/31/2019 2778A_07

9/41

Information Schema View Examples

SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_SCHEMA = NSales';

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = N'Product';

COLUMN_NAME

Name

ProductNumber

MakeFlag

FinishedGoodsFlag

TABLE_NAME

vIndividualCustomervPersonDemographics

vSalesPerson

vStoreWithContacts

SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = NPerson';

TABLE_NAMEAddressType

StateProvince

BusinessEntity

ContactType

7/31/2019 2778A_07

10/41

Dynamic Management Views and Functions

Act as a mechanism to allow you to look at the internalworkings of SQL Server using TSQL

Provide an easy method for monitoring the internal stateand health of SQL Server

Provide information for a wide variety of categories

Category

Examples

View Function

Execution dm_exec_requests dm_exec_sql_text

Index dm_db_index_usage_stats dm_db_missing_index_columns

I/O dm_io_pending_io_requests dm_io_virtual_file_stats

Operating System dm_os_sys_info

7/31/2019 2778A_07

11/41

Dynamic Management Views and FunctionsExamples

SELECT count(*) as Cnt, Command

FROM sys.dm_exec_requestsGROUP BY Command

SELECT database_id, num_of_reads, num_of_writes

FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks'), 2);

Cnt Command

1 BRKR EVENT HNDLR

3 BRKR TASK1 CHECKPOINT

5 FSAGENT TASK

database_id num_of_reads num_of_writes

7 8 7

SELECT cpu_count, physical_memory_in_bytes, sqlserver_start_timeFROM sys.dm_os_sys_info

cpu_count physical_memory_in_bytes sqlserver_start_time

1 960995328 2008-08-14 12:41:30.423

7/31/2019 2778A_07

12/41

System Stored Procedures and Functions

Provide a simple way of performing complex queries via T-SQL.

SELECT COLUMNPROPERTY( OBJECT_ID('Person.Contact'),

'LastName','PRECISION') AS 'Column Length';

EXEC sp_columns @table_name = N'Department',@table_owner = N'HumanResources';

Common System StoredProcedures

sp_databases sp_tables sp_columns sp_statistics sp_pkeys sp_fkeys

Common SystemFunctions

DATABASEPROPERTY COLUMNPROPERTY OBJECTPROPERTY

COL_LENGTH DB_NAME DB_ID

7/31/2019 2778A_07

13/41

Demonstration: Querying Metadata

Querying Metadata Using System Catalog Views

Querying Metadata Using the Information Schema Querying Metadata Using Dynamic Management Views

Querying Metadata Using System Stored Procedures andFunctions

7/31/2019 2778A_07

14/41

Lesson 2: Overview of XML

What Is XML?

Technical Scenarios Where XML Is Used Business Scenarios Where XML Is Used

How SQL Server 2008 Implements XML

The XML Data Type

7/31/2019 2778A_07

15/41

What Is XML?

Definition: The Extensible Markup Language (XML) is anopen standard recommended by W3C for creating custommarkup languages.

Tag based. Tags are self

descriptive. Designers are free tocreate their own XMLstructures and tags toaccommodate their data. The XML standard does

not define the structuresor the content but onlythe format.

XML

RobertFrost

FrankBaker

Tag based. Tags are self

descriptive. Designers are free tocreate their own XMLstructures and tags toaccommodate their data. The XML standard does

not define the structuresor the content but onlythe format.

7/31/2019 2778A_07

16/41

Technical Scenarios Where XML Is Used

Scenario 1:Sharing structured data between systems

XML

Scenario 2:Sharing data on the Internet

Scenario 3:Encoding documents

Scenario 4:Serializing data

7/31/2019 2778A_07

17/41

Business Scenarios Where XML Is Used

Scenario 1:Insurance Claims

XML

Scenario 2:Automobile Manufacturer and Parts Suppliers I

Scenario 3:Automobile Manufacturer and Parts Suppliers II

Scenario 4:Content Management System

Scenario 5:Customer Survey

7/31/2019 2778A_07

18/41

How SQL Server 2008 Implements XML

The FOR XML T-SQL clause provides theability to serialize query results to XML format.

XML

The OpenXML function provides the ability todeserialize XML into a rowset view. This rowset

can then be queried as relational data.

XML

XQuery support allows query basedinteraction with data stored in XML format.

XML

XML Schema Collections provide the abilityto define XML schemas for typed XML storage.

XML

7/31/2019 2778A_07

19/41

The XML Data Type

The XML data type is a built-in data type in SQL Server for storing

and interacting with XML data

The XML data type supports five methods for interacting with thedata that it contains

Query Accepts XQuery statements to retrieve elements of the XML data

Value Retrieves a value of SQL type from an XML instance

Exists Determines if an XQuery statement returns results

Modify Is used to specify XML data modification updatestatements

Nodes Splits XML into multiple rows

7/31/2019 2778A_07

20/41

Lesson 3: Querying XML Data

Using For XML to Generate XML

Querying XML by Using OpenXML Querying XML Using XQuery

Generating XML-Based Reports

7/31/2019 2778A_07

21/41

Using For XML to Generate XML

SELECT Cust.CustomerID,OrderHeader.CustomerID,OrderHeader.SalesOrderID,OrderHeader.Status,Cust.CustomerType

FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeaderWHERE Cust.CustomerID = OrderHeader.CustomerIDORDER BY Cust.CustomerIDFOR XML AUTO {RAW(ElementName) | EXPLICIT}

7/31/2019 2778A_07

22/41

Querying XML by Using OpenXML

DECLARE @xml_text VARCHAR(4000), @i INT

SELECT @xml_text =''

EXEC sp_xml_preparedocument @i OUTPUT, @xml_text

SELECT * FROMOPENXML(@i, '/root/person') WITH (LastName nvarchar(50),

FirstName nvarchar(50))

EXEC sp_xml_removedocument @iLastName FirstName

White JohnsonGreen MarjorieCarson Cheryl

7/31/2019 2778A_07

23/41

Querying XML Using XQuery

SELECT Instructions.query('declare namespace AWMI="http://schemas.microsoft.com

/sqlserver/2004/07/adventureworks/ProductModelManuInstructions";

for $T in //AWMI:toollet $L := //AWMI:Location[.//AWMI:tool[.=data($T)]]return

') as ResultFROM Production.ProductModelwhere ProductModelID=7

Result

XQuery defines the FLWOR iteration syntax. FLWOR is the

acronym for for, let, where, order by, and return.

7/31/2019 2778A_07

24/41

Generating XML-Based Reports

XML Data Provider

SQL Server 2008

ASP.NET Web Service

7/31/2019 2778A_07

25/41

Demonstration: Using XML

Using For XML to Generate XML Results

Using OpenXML to Query XML Using XQuery to Query XML

7/31/2019 2778A_07

26/41

Lesson 4: Overview of Full-Text Indexes

What Are Full-Text Indexes?

How Full-Text Indexes Are Populated Full-Text Indexing and Querying Process

How to Implement Full-Text Indexes in SQL Server 2008

7/31/2019 2778A_07

27/41

What Are Full-Text Indexes?

Use Cases Exposing data for advanced web site searches

Definition: A full-text index is a special type of token-basedfunctional index that provides extended searchingcapabilities for text data.

Allowing fuzzy searches of product descriptions

Allowing wildcard searches of customer addresses

7/31/2019 2778A_07

28/41

How Full-Text Indexes Are Populated

Full population

Generally populated on initial creation Maintained using change tracking or incremental methods

Change tracking-based population

SQL Server tracks text values that have changed Full-Text indexes are updated for modified values

Incremental timestamp-based population

Updates index for rows added, updated, deleted since lastpopulation

7/31/2019 2778A_07

29/41

Full-Text Indexing and Querying Process

Data Table

Full-Text

IndexQuery

Query Processor

Results

How to Implement Full Text Indexes in SQL

7/31/2019 2778A_07

30/41

How to Implement Full-Text Indexes in SQLServer 2008

Determine the tables and columns that require full-text indexingaccording to business requirements

Enable full-text indexing in the database Use sp_fulltext_database enable

Create the full-text index for the required table(s) Use sp_fulltext_table

Add required column(s) to the index Use sp_fulltext_column

Activate and set population options for index Use sp_fulltext_table

Design and build queries using full-text query functions andpredicates according to business requirements

7/31/2019 2778A_07

31/41

Lesson 5: Querying Full-Text Indexes

Overview of Full-Text Search

The CONTAINS Predicate The FREETEXT Predicate

Full-Text Functions

Combining Full-Text Search and T-SQL Predicates

7/31/2019 2778A_07

32/41

Overview of Full-Text Search

Exact Search: WHERE sentence LIKE %run %

Results: We had to run in gym class The politician decided to run for office

Full-Text SearchResults: We had to run in gym class The politician decided to run for office My car is not running He had to rest after he ran a mile Doctors say running is a healthy hobby

7/31/2019 2778A_07

33/41

The CONTAINS Predicate

SELECT Name

FROM Production.ProductWHERE CONTAINS(Name, ' "Chain*" ');

Sample CONTAINS conditions

CONTAINS(Name, ' "Mountain" OR "Road" ') CONTAINS(Description, 'bike NEAR performance') CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ') CONTAINS(Description, 'ISABOUT (performance weight (.8),comfortable weight (.4), smooth weight (.2) )' )

7/31/2019 2778A_07

34/41

The FREETEXT Predicate

SELECT Title

FROM Production.DocumentWHERE FREETEXT (Document, 'vital safety components' );

Steps that FREETEXT takes:

Word- breaking: vital, safety, components Stemming: vital, safe, safety, components List of expansions: vital, important, safe, safety,

components, parts

7/31/2019 2778A_07

35/41

Full-Text Functions

Key Rank ProdID Name Desc

Key Table Product Table

SELECT Product.ProdID, Product.Name, Prod.Desc, Keys.Rank

FROM dbo.ProductINNER JOIN FREETEXTTABLE (dbo.Product, Desc,

safety harness',LANGUAGE 'English',2) AS KeysON Product.ProdID = Keys.[KEY];

7/31/2019 2778A_07

36/41

Combining Full-Text Search and T-SQL Predicates

SELECT Product.ProductDescriptionID, Product.Description, Keys.RankFROM Production.ProductDescription Product

INNER JOIN FREETEXTTABLE (Production.ProductDescription,[Description],

'safety',LANGUAGE 'English',2) AS KeysON Product.ProductDescriptionID = Keys.[KEY]

WHERE Product.QuantityAvailable > 0;

ProductDescriptionID Description Rank

513 All-occasion value bike with our basiccomfort and safety features. Offerswider, more stable tires for a ridearound town or weekend trip.

134

594 Travel in style and comfort. Designedfor maximum comfort and safety.Wide gear range takes on all hills.High-tech aluminum alloyconstruction provides durabilitywithout added weight.

67

7/31/2019 2778A_07

37/41

Demonstration: Full Text Index

Creating a Full Text Index

Querying a Full Text Index Using Predicates Querying a Full Text Index Using Functions

Lab: Querying Metadata XML and Full-Text

7/31/2019 2778A_07

38/41

Lab: Querying Metadata, XML, and Full TextIndexes

Exercise 1: Querying Metadata

Exercise 2: Querying XML data Exercise 3: Creating and Querying Full Text Indexes

Logon information

Virtual machine NY-SQL-01

User name AdministratorPassword Pa$$w0rd

Estimated time: 60 minutes

7/31/2019 2778A_07

39/41

Lab Scenario

You are the database administrator at Adventure Works. Thecompany requires that you perform an annual audit of thedatabase systems and their efficiency.

verify the metadata elements of the AdventureWorksdatabase

Retrieve the orders data in an XML format from an external

system Set up a full-text catalog for product descriptions and

execute several queries that verify the catalog

7/31/2019 2778A_07

40/41

Lab Review

What application business requirements can beaccomplished by using metadata queries?

What is the purpose of compatibility views?

What statement can be used to convert relational data toXML?

What is the difference between the FREETEXT andCONTAINS predicates?

7/31/2019 2778A_07

41/41

Module Review and Takeaways

Review Questions