and Full-Text Indexes

roomagitatedInternet και Εφαρμογές Web

8 Δεκ 2013 (πριν από 3 χρόνια και 10 μήνες)

78 εμφανίσεις

Module 7:

Querying Metadata, XML,
and Full
-
Text Indexes

Module 7: Querying Metadata, XML, and Full
-
Text
Indexes


Querying Metadata


Overview of XML


Querying XML Data


Overview of Full
-
Text Indexes


Querying Full
-
Text Indexes

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

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 Were
Made


Used for Type Checking, Data Validation and Formatting of
Data

Definition: Metadata is Data about Data
.

Compatibility Views


Applications developed with previous versions of SQL Server®
may use metadata views that are specific to that version


Compatibility views are provided for backward compatibility
for such applications


Compatibility views should be used for backward compatibility
only


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

SELECT *

FROM sys.sysobjects;

Name

Id

Xtype

Uid

Info

status

events

-
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

System Catalog Views


Exposes all metadata


Provides the most efficient way to access
core server metadata


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


Contains descriptive names for views and
columns

System Catalog View Examples

SELECT name, type_desc

FROM 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_date

vwProducts

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

Information Schema Views


Based on catalog view definitions in the ISO standard.


Provide an internal, system table
-
independent view of the SQL
Server® metadata.


Are ISO compliant so that Applications that use Information
Schema Views are portable between ISO compliant database
systems.

Information

Schema

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = N'Product';

Information Schema View Examples

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.VIEWS

WHERE TABLE_SCHEMA = N‘Sales';


SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = N'Product';


COLUMN_NAME

Name

ProductNumber

MakeFlag

FinishedGoodsFlag

TABLE_NAME

vIndividualCustomer

vPersonDemographics

vSalesPerson

vStoreWithContacts

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = N‘Person';


TABLE_NAME

AddressType

StateProvince

BusinessEntity

ContactType

Dynamic Management Views and Functions


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


Provide an easy method for monitoring the internal state
and 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

Dynamic Management Views and Functions
Examples

SELECT count(*) as Cnt, Command

FROM sys.dm_exec_requests

GROUP 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 TASK

1

CHECKPOINT

5

FSAGENT TASK

database_id

num_of_reads

num_of_writes

7

8

7

SELECT cpu_count, physical_memory_in_bytes, sqlserver_start_time

FROM sys.dm_os_sys_info

cpu_count

physical_memory_in_bytes

sqlserver_start_time

1

960995328

2008
-
08
-
14 12:41:30.423

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 Stored
Procedures



sp_databases


sp_tables


sp_columns


sp_statistics


sp_pkeys


sp_fkeys

Common System
Functions



DATABASEPROPERTY


COLUMNPROPERTY


OBJECTPROPERTY


COL_LENGTH


DB_NAME


DB_ID


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 and
Functions

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

What Is XML?




Definition: The Extensible Markup Language (XML) is an
open standard recommended by W3C for creating custom
markup languages.


Tag based.


Tags are self
descriptive.


Designers are free to
create their own XML
structures and tags to
accommodate their data.


The XML standard does
not define the structures
or the content but only
the format.

XML

<people>


<person>


<first_name>Robert</first_name>


<last_name>Frost</last_name>


</person>


<person>


<first_name>Frank</first_name>


<last_name>Baker</last_name>


</person>

</people>


Tag based.


Tags are self
descriptive.


Designers are free to
create their own XML
structures and tags to
accommodate their data.


The XML standard does
not define the structures
or the content but only
the format.

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


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


How SQL Server® 2008 Implements XML


The
FOR XML

T
-
SQL clause provides the

ability to serialize query results to XML format.

XML


The
OpenXML

function provides the ability to

deserialize XML into a rowset view. This rowset

can then be queried as relational data.

XML


XQuery

support allows query based

interaction with data stored in XML format.

XML


XML Schema Collections
provide the ability

to define XML schemas for typed XML storage.

XML

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 the
data 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 update
statements

Nodes

Splits XML into multiple rows

Lesson 3: Querying XML Data


Using For XML to Generate XML


Querying XML by Using OpenXML


Querying XML Using XQuery


Generating XML
-
Based Reports

Using For XML to Generate XML

<Cust CustomerID="1" CustomerType="S">


<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />


<OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />


<OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />


<OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />

</Cust>

SELECT Cust.CustomerID,


OrderHeader.CustomerID,


OrderHeader.SalesOrderID,


OrderHeader.Status,


Cust.CustomerType

FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader

WHERE Cust.CustomerID = OrderHeader.CustomerID

ORDER BY Cust.CustomerID

FOR XML AUTO {RAW(‘ElementName’) | EXPLICIT}

Querying XML by Using OpenXML

DECLARE @xml_text VARCHAR(4000), @i INT


SELECT @xml_text =

'<root><person LastName="White" FirstName="Johnson"/>

<person LastName="Green" FirstName="Marjorie"/>

<person LastName="Carson" FirstName="Cheryl"/></root>'


EXEC sp_xml_preparedocument @i OUTPUT, @xml_text


SELECT * FROM


OPENXML(@i, '/root/person') WITH (LastName nvarchar(50),






FirstName nvarchar(50))


EXEC sp_xml_removedocument @i

LastName

FirstName

White

Johnson

Green

Marjorie

Carson

Cheryl

Querying XML Using XQuery

SELECT Instructions.query('


declare namespace AWMI="http://schemas.microsoft.com
/sqlserver/2004/07/adventureworks/ProductModelManuInstructions";


for

$T in //AWMI:tool


let

$L := //AWMI:Location[.//AWMI:tool[.=data($T)]]


return


<tool desc="{data($T)}" Locations="{data($L/@LocationID)}"/>

') as Result

FROM Production.ProductModel

where ProductModelID=7

Result

<tool desc=“hammer” Locations=“30”/>

XQuery

defines the
FLWOR

iteration syntax. FLWOR is the

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

Generating XML
-
Based Reports

XML Data Provider

SQL Server® 2008

ASP.NET Web Service

Demonstration: Using XML


Using For XML to Generate XML Results


Using OpenXML to Query XML


Using XQuery to Query XML

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

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
-
based
functional index that provides extended searching
capabilities for text data.


Allowing fuzzy searches of product descriptions


Allowing wildcard searches of customer addresses

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 last
population

Full
-
Text Indexing and Querying Process

Data Table

Full
-
Text


Index

Query

Query Processor

Results

How to Implement Full
-
Text Indexes in SQL
Server® 2008


Determine the tables and columns that require full
-
text indexing
according 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 and
predicates according to business requirements

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

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 Search

Results:


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

The CONTAINS Predicate

SELECT Name

FROM Production.Product

WHERE 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) )' )

The FREETEXT Predicate

SELECT Title

FROM Production.Document

WHERE 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”

Full
-
Text Functions

Key

Rank

ProdID

Name

Desc

Key Table

Product Table

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

FROM
dbo.Product


INNER JOIN
FREETEXTTABLE
(
dbo.Product
,
Desc
,


‘safety
harness',LANGUAGE

'English',2) AS Keys


ON
Product.ProdID

= Keys.[KEY];

Combining Full
-
Text Search and T
-
SQL Predicates

SELECT Product.ProductDescriptionID, Product.Description, Keys.Rank

FROM Production.ProductDescription Product


INNER JOIN
FREETEXTTABLE

(Production.ProductDescription,
[Description],


'safety',LANGUAGE 'English',2) AS Keys


ON Product.ProductDescriptionID = Keys.[KEY]

WHERE Product.QuantityAvailable > 0;

ProductDescriptionID

Description

Rank

513

All
-
occasion value bike with our basic
comfort and safety features. Offers
wider, more stable tires for a ride
around town or weekend trip.

134

594

Travel in style and comfort. Designed
for maximum comfort and safety.
Wide gear range takes on all hills.
High
-
tech aluminum alloy
construction provides durability
without added weight.

67

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
Indexes


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

Administrator

Password

Pa$$w0rd

Estimated time:
60

minutes

Lab Scenario

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


verify the metadata elements of the AdventureWorks
database


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

Lab Review


What application business requirements can be
accomplished by using metadata queries?


What is the purpose of compatibility views?


What statement can be used to convert relational data to
XML?


What is the difference between the FREETEXT and
CONTAINS predicates?

Module Review and Takeaways


Review Questions