Catalog View over Information_Schema in Microsoft SQL Server 2008

Basically Catalog View was first introduced in SQL Server 2005. It provided insight view of database objects. Previously way back, we used to get this kind of insight tour via system tables, system SPs or Information_Schema view. Catalog has few advantages over others.

Many of the old System SPs and System tables are removed and Information_Schema is given in newer version also but just for the backward compatibility. It will be deprecated in future version for sure. Microsoft insists to use Catalog view over Information_Schema. You can have much better functionality in Catalog view and can do almost everything which you can do with Information_Schema. Let us look at one simple example which will show you columns of all tables or for those table which meets criteria in WHERE clause in SELECT statement.

–use of catalog view
SELECT
s.name AS schema_name,
t.name AS table_name,
t.type_desc AS table_type,
c.name AS column_name,
c.column_id,
ty.name AS data_type_name,
c.is_nullable
FROM sys.schemas s
INNER JOIN sys.tables t
ON s.schema_id = t.schema_id
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.types ty
ON c.system_type_id = ty.system_type_id
AND c.user_type_id = ty.user_type_id
where s.name=‘Production’ and t.name=‘ProductReview’
GO
–use of Information_Schema
select
t.TABLE_SCHEMA as schema_name,
t.TABLE_NAME as table_name,
t.TABLE_TYPE as table_type,
c.COLUMN_NAME as column_name,
c.ORDINAL_POSITION as column_id,
c.DATA_TYPE as data_type_name,
c.IS_NULLABLE as is_nullable
from INFORMATION_SCHEMA.TABLES as t
join INFORMATION_SCHEMA.COLUMNS as c
on t.TABLE_SCHEMA=c.TABLE_SCHEMA and t.TABLE_NAME=c.TABLE_NAME
where t.TABLE_SCHEMA=‘Production’ and t.TABLE_NAME=‘ProductReview’
 BTW, I have written very small note of Catalog View in past too, if you would like to read it, Click Here.

I forced myself to write this article as I still see people are so used to with Information_Schema even this is almost 5 years SQL Server 2005 introduced and even two years SQL Server 2008 is introduced (since beta). I would suggest please stop using Information_Schema and grab the power of new Catalog View.



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Catalog View over Information_Schema in Microsoft SQL Server 2008

Basically Catalog View was first introduced in SQL Server 2005. It provided insight view of database objects. Previously way back, we used to get this kind of insight tour via system tables, system SPs or Information_Schema view. Catalog has few advantages over others.

Many of the old System SPs and System tables are removed and Information_Schema is given in newer version also but just for the backward compatibility. It will be deprecated in future version for sure. Microsoft insists to use Catalog view over Information_Schema. You can have much better functionality in Catalog view and can do almost everything which you can do with Information_Schema. Let us look at one simple example which will show you columns of all tables or for those table which meets criteria in WHERE clause in SELECT statement.

–use of catalog view
SELECT
s.name AS schema_name,
t.name AS table_name,
t.type_desc AS table_type,
c.name AS column_name,
c.column_id,
ty.name AS data_type_name,
c.is_nullable
FROM sys.schemas s
INNER JOIN sys.tables t
ON s.schema_id = t.schema_id
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.types ty
ON c.system_type_id = ty.system_type_id
AND c.user_type_id = ty.user_type_id
where s.name=‘Production’ and t.name=‘ProductReview’
GO
–use of Information_Schema
select
t.TABLE_SCHEMA as schema_name,
t.TABLE_NAME as table_name,
t.TABLE_TYPE as table_type,
c.COLUMN_NAME as column_name,
c.ORDINAL_POSITION as column_id,
c.DATA_TYPE as data_type_name,
c.IS_NULLABLE as is_nullable
from INFORMATION_SCHEMA.TABLES as t
join INFORMATION_SCHEMA.COLUMNS as c
on t.TABLE_SCHEMA=c.TABLE_SCHEMA and t.TABLE_NAME=c.TABLE_NAME
where t.TABLE_SCHEMA=‘Production’ and t.TABLE_NAME=‘ProductReview’
 BTW, I have written very small note of Catalog View in past too, if you would like to read it, Click Here.

I forced myself to write this article as I still see people are so used to with Information_Schema even this is almost 5 years SQL Server 2005 introduced and even two years SQL Server 2008 is introduced (since beta). I would suggest please stop using Information_Schema and grab the power of new Catalog View.



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

CONTAINSTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 6)



This is sixth part of the series of Integrated Full Text Search in Microsoft SQL Server 2008. If you want to study the topic in depth, Please read my past five articles in this series. Below are the links of the same.



Note: I am using the same table & data which I have created in my past articles. So if you are directly landed to this article, kindly get the create table script from second article listed above.

 Since we had already looked at FREETEXTTABLE, CONTAINSTABLE is not something new. It is also work at TVF but it has bit more functionality than FREETEXTTABLE. You have to provide table name, column(s) name and search condition. CONTAINSTABLE can accept all search condition which is valid in CONTAINS predicate.


CONTAINSTABLE also return KEY and RANK column like FREETEXTTABLE which you can join with your original table. You can refer FREETEXTTABLE’s article for more details.

Let us see some examples for CONTAINSTABLE in Microsoft SQL Server 2008.

–this T-SQL will return only key value and rank
SELECT
[KEY],
[RANK]
FROM CONTAINSTABLE
(
FTSTest,
TestDescription,
N’FORMSOF(INFLECTIONAL, volatiles)’
);
GO

–this is sample search of CONTAINSTABLE
–with FORMSOF clause which we used in CONTAINS also in previous article
Select t2.* FROM CONTAINSTABLE
(
FTSTest,
TestDescription,
N’FORMSOF(INFLECTIONAL, volatiles)’
) as t1 join FTSTest t2 on t1.[key]=t2.Id
GO

–there is one more useful clause, ISABOUT, which you can use with CONTAINS and CONTAINSTABLE
–You can give weight of the search word between 0.0 to 1.0
–search word with greater weight comes up with higher rank so that you can sort it easily.
SELECT
ct.[RANK],
ct.[KEY],
pm.[TestName],
pm.[TestDescription]
FROM CONTAINSTABLE
(
ftsTest,
TestDescription,
N’ISABOUT(volatile WEIGHT(0.1), General WEIGHT(1.0))’
) ct
INNER JOIN FTSTest pm
ON ct.[KEY] = pm.ID
ORDER BY ct.[RANK] DESC;
GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

CONTAINSTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 6)



This is sixth part of the series of Integrated Full Text Search in Microsoft SQL Server 2008. If you want to study the topic in depth, Please read my past five articles in this series. Below are the links of the same.



Note: I am using the same table & data which I have created in my past articles. So if you are directly landed to this article, kindly get the create table script from second article listed above.

 Since we had already looked at FREETEXTTABLE, CONTAINSTABLE is not something new. It is also work at TVF but it has bit more functionality than FREETEXTTABLE. You have to provide table name, column(s) name and search condition. CONTAINSTABLE can accept all search condition which is valid in CONTAINS predicate.


CONTAINSTABLE also return KEY and RANK column like FREETEXTTABLE which you can join with your original table. You can refer FREETEXTTABLE’s article for more details.

Let us see some examples for CONTAINSTABLE in Microsoft SQL Server 2008.

–this T-SQL will return only key value and rank
SELECT
[KEY],
[RANK]
FROM CONTAINSTABLE
(
FTSTest,
TestDescription,
N’FORMSOF(INFLECTIONAL, volatiles)’
);
GO

–this is sample search of CONTAINSTABLE
–with FORMSOF clause which we used in CONTAINS also in previous article
Select t2.* FROM CONTAINSTABLE
(
FTSTest,
TestDescription,
N’FORMSOF(INFLECTIONAL, volatiles)’
) as t1 join FTSTest t2 on t1.[key]=t2.Id
GO

–there is one more useful clause, ISABOUT, which you can use with CONTAINS and CONTAINSTABLE
–You can give weight of the search word between 0.0 to 1.0
–search word with greater weight comes up with higher rank so that you can sort it easily.
SELECT
ct.[RANK],
ct.[KEY],
pm.[TestName],
pm.[TestDescription]
FROM CONTAINSTABLE
(
ftsTest,
TestDescription,
N’ISABOUT(volatile WEIGHT(0.1), General WEIGHT(1.0))’
) ct
INNER JOIN FTSTest pm
ON ct.[KEY] = pm.ID
ORDER BY ct.[RANK] DESC;
GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

CONTAINS Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 5)


This is fifth part of the series of Integrated Full Text Search in Microsoft SQL Server 2008. If you want to study the topic in depth, Please read my past four articles in this series. Below are the links of the same.

We have already looked at FREETEXT and FREETEXTTABLE predicate in iFTS using Microsoft SQL Server 2008. Now, this is time to look at one more interesting predicate “CONTAINS” in iFTS.
CONTAINS is a more advanced version of FREETEXT predicate. It can accept column(s), search condition like FREETEXT and search simple words, other than that, it can search close words to the word specified into search criteria, thesaurus, synonyms, inflectional words. CONTAINS predicate don’t automatically search inflectional words but you have to use FORMSOF clause with it.

Let us see some practical example to make concept clearer.

Note: I am using the same table & data which I have created in my past articles. So if you are directly landed to this article, kindly get the create table script from second article.

–you won’t get any data as inflectional words are not searched by default
–NOTE: we have Volatiles as a plural and in our data, it is singular.
SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,‘Volatiles’)

–you will get results as you are making exact search
SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,‘Volatile’)

–enabling inflectional word search so you can get data with singular forms of words
–even if you put plural in search criterial and vice versa
SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,N’FORMSOF(INFLECTIONAL,Volatiles)’)

–now we knew that we have three row as a results when we search Volatile word
–now I want to be more specific, I just want those row which has “Volatile” near “Again”
–you will get only one row as a results
SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,N’Volatile NEAR Again’)

 
CONTAINS is really lot more powerful than FREETEXT search. Isn’t it?

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

FREETEXTTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 4)

This is fourth part of the series of Integrated Full Text Search in Microsoft SQL Server 2008. If you want to study the topic in depth, Please read my past three article in this series. Below are the links of the same.

BTW, I am going to use the same table I have created in my previous article so for practical exercise get the table and script from that article if you are directly landing to this page.
If you have read above articles than you must be clear with FREETEXT predicate. Now, we will continue over the topic of this article.
If you are SQL Server developer then you might know TVF. FREETEXTTABLE is working something like it. You have to use FREETEXTTABLE in FROM clause only, just like a regular table or TVF. It will accept three things as below.
1.)    Table Name
2.)    Column Name(s)
3.)    Search Text
It will not return any row with real value rather it will return two columns.
1.)    [KEY]
2.)    [RANK]
First column [KEY] will return the key value of the Index column you have in your original table so that you can join this [KEY] column to your original table and get real full row whenever it is needed.
Second column [RANK] will return relevance ranking of the search text in the column(s) you have specified in FREETEXTTABLE.
So now this is really enough theory, isn’t it? Let’s have some real practical task.
–as per explaination given above,
–below query will return just key and rank. you can confirm
–whether you get right key or not based on your search criteria
select [Key],[Rank] from FREETEXTTABLE(FTSTest,TestDescription,‘Volatile’)
GO

–just commpare [KEY] with ID column of our FTSTest
Select * from FTSTest
GO

–now we can get real value by making join of FREETEXTTABLE and FTSTest
SELECT T2.* FROM FREETEXTTABLE(FTSTest,TestDescription,‘Volatile’) as t1 JOIN FTSTest as T2
ON T1.[KEY]=T2.Id
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Read typed XML in SQL Server 2008

So far, I have written few articles on XML topic, before we move further, I would like to summarize those here with the respected links so if anybody interested, can have look at it.

Apart from above four XML article, today I would like to introduce, how you can read typed XML in Microsoft SQL Server 2008.

I have one very small example to share with you. Have a look.

SET ANSI_NULLS ON
DECLARE @MyXML Xml
SET @MyXml =  

‘<?xml version=”1.0″ encoding=”utf-8″?>
      <L:Clients xmlns:L=”http://sqlhub.com/client/&#8221; >
            <L:Body> 
                  <TestGroup xmlns=”TestGroup”>   
                        <Test1>TCLP VOA</Test1>   
                        <Test2>TCLP-SVOA</Test2>   
                        <Test3>Metals Group1</Test3>  
                  </TestGroup>
                  <TestGroup xmlns=”TestGroup”>   
                        <Test1>Cynide</Test1>   
                        <Test2>Mercury</Test2>   
                        <Test3>TO-15</Test3>  
                  </TestGroup>  
            </L:Body>
      </L:Clients>’

;WITH XMLNAMESPACES (http://sqlhub.com/client/&#8217; as L,
                              ‘TestGroup’ as TG)
SELECT      T.c.value(‘(TG:Test1)[1]’, ‘varchar(20)’) as Test1,
            T.c.value(‘(TG:Test2)[1]’, ‘varchar(20)’) as Test2,
            T.c.value(‘(TG:Test3)[1]’, ‘varchar(20)’) as Test3
FROM      @MyXml.nodes(‘(L:Clients/L:Body/TG:TestGroup)’) T(c)
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com