Best practise of storing a column which is of string type

Hi,

Hi I am currently using SQL Server 2005.

Whats the difference between nvarchar & varchar datatypes in SQL server and which is opted best to store strings.

Thanks,

Uma Ramiya

nvarchar is used commonly used to store national characters / unicode characters. so if your front end application accepts unicode characters to be stored to your db then you should use nvarchar. if not use varchar instead
|||

I use ASp.Net 2.0 as front end. In that case what should I use

Thanks,

Uma Ramiya

|||its not dependent on asp.net. it depends on the localization of your application e.g. does your asp.net application can be viewed, transalated and/or accepts inputs with chinese , korean characters, etc. then you should use nvarchar. if you application only stores english characters then use varchar
|||

If you want to store english (latin based) string then you can use varchar.

If you want to store non-english string (ex. Japanese, Hindi, Tamil etc) then you have to use NVarchar.

Nvarchar uses Unicode (which supports all the alphabets/numbers)

Varchar Uses ASCII CODE (which supports only english alphabets/numbers)

Nvarchar each char occupies 2 Byte

Varchar each char occupies 1 Byte

Nvarchar max length in SQL Server 2000 = 4000

Varchar max length in SQL Server 2005 = 8000

If your app supports (or planned to support on future) Globilization/Localization then use the NVarchar

Otherwise Varchar is more enough..

Best practices for Unicode column supporting mixed languages and searching

(I posted this to sqlserver.server newsgroup but did not get a helpful
response)
Globalization gurus,
The problem is storing in a single column character data from mixed
languages and then providing a search capability to find the best match
given a search string in some arbitrary language. The column, of course, is
Unicode data type with some collation. What is recommendation for table
design for efficiency?
As an example, consider an international directory of business names and a
function to search for a name "like" <some string> where <some string> is
user input in any language. Since collation determines comparison rules it
seems the appropriate collation is one that best matches the language of the
search string. Further, to facilitate matching it seems appropriate to relax
restrictions such as case and accent sensitivity. That is a strict binary
comparison is not "user friendly." However there are performance
implications when the collation of the search string does not match the
collation of the database column.
Has anyone solved this problem or know of a good discussion?
Thank you,
SethSeth,
Hmm... This seem a very good fit for SQL Server 2005 Full-Text Search (FTS).
Note, that I'm NOT recommending SQL Server 2000 FTS for this as there have
been specific SQL FTS enhancements for these requirements that are not in
SQL 2000. Specifically, in SQL Server 2005, you can define multiple
languages in one Unicode column and then do CONTAINS or FREETEXT and specify
the language (via LCID) on a per query basis, for example:
select * from table where
contains(*,'formsof(inflectional,"englishword")',language 1033) OR
contains(*,'formsof(inflectional,"chineseword")',language 2052)
The above query will find the englishword or chineseword and inflectional
variations from the same FT-enable column. You can also use this query with
CONTAINSTABLE or FREETEXTTABLE and use the RANK value. As for table design,
that depend upon what you're storing and what combined column you want
returned to your searchers.
You can also control the accent sensitivity via
"CREATE FULLTEXT CATALOG <FT_Catalog_Name> WITH ACCENT_SENSITIVITY = [OFF |
ON]
However, FTS is not case sensitive, but based upon research in to how people
search, case sensitivity is usually viewed as a hindrance as most internet
search engines are case-insensitive as well. Why do you need this level of
control?
Feel free to email me directly if you'd like to continue this discuss in
more details.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Seth" <seth@.no*spam.com> wrote in message
news:Orp0SUb2FHA.2472@.TK2MSFTNGP12.phx.gbl...
> (I posted this to sqlserver.server newsgroup but did not get a helpful
> response)
> Globalization gurus,
> The problem is storing in a single column character data from mixed
> languages and then providing a search capability to find the best match
> given a search string in some arbitrary language. The column, of course,
> is
> Unicode data type with some collation. What is recommendation for table
> design for efficiency?
> As an example, consider an international directory of business names and a
> function to search for a name "like" <some string> where <some string> is
> user input in any language. Since collation determines comparison rules it
> seems the appropriate collation is one that best matches the language of
> the
> search string. Further, to facilitate matching it seems appropriate to
> relax
> restrictions such as case and accent sensitivity. That is a strict binary
> comparison is not "user friendly." However there are performance
> implications when the collation of the search string does not match the
> collation of the database column.
> Has anyone solved this problem or know of a good discussion?
> Thank you,
> Seth
>
>|||If anyone has ideas on non-FTS solutions I would appreciate that as well.
John,
Good idea!
I have read about FTS enhancements in SQL Server 2005 but I admit to having
an aversion to FTS and dismissed it as an option prematurely. It is
definitely worth consideration.
Seth
--
"John Kane" wrote in message news:OVefrPe2FHA.1140@.tk2msftngp13.phx.gbl...
Seth,
Hmm... This seem a very good fit for SQL Server 2005 Full-Text Search (FTS).
Note, that I'm NOT recommending SQL Server 2000 FTS for this as there have
been specific SQL FTS enhancements for these requirements that are not in
SQL 2000. Specifically, in SQL Server 2005, you can define multiple
languages in one Unicode column and then do CONTAINS or FREETEXT and specify
the language (via LCID) on a per query basis, for example:
select * from table where
contains(*,'formsof(inflectional,"englishword")',language 1033) OR
contains(*,'formsof(inflectional,"chineseword")',language 2052)
The above query will find the englishword or chineseword and inflectional
variations from the same FT-enable column. You can also use this query with
CONTAINSTABLE or FREETEXTTABLE and use the RANK value. As for table design,
that depend upon what you're storing and what combined column you want
returned to your searchers.
You can also control the accent sensitivity via
"CREATE FULLTEXT CATALOG <FT_Catalog_Name> WITH ACCENT_SENSITIVITY = [OFF |
ON]
However, FTS is not case sensitive, but based upon research in to how people
search, case sensitivity is usually viewed as a hindrance as most internet
search engines are case-insensitive as well. Why do you need this level of
control?
Feel free to email me directly if you'd like to continue this discuss in
more details.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Seth" <seth@.no*spam.com> wrote in message
news:Orp0SUb2FHA.2472@.TK2MSFTNGP12.phx.gbl...
> (I posted this to sqlserver.server newsgroup but did not get a helpful
> response)
> Globalization gurus,
> The problem is storing in a single column character data from mixed
> languages and then providing a search capability to find the best match
> given a search string in some arbitrary language. The column, of course,
> is
> Unicode data type with some collation. What is recommendation for table
> design for efficiency?
> As an example, consider an international directory of business names and a
> function to search for a name "like" <some string> where <some string> is
> user input in any language. Since collation determines comparison rules it
> seems the appropriate collation is one that best matches the language of
> the
> search string. Further, to facilitate matching it seems appropriate to
> relax
> restrictions such as case and accent sensitivity. That is a strict binary
> comparison is not "user friendly." However there are performance
> implications when the collation of the search string does not match the
> collation of the database column.
> Has anyone solved this problem or know of a good discussion?
> Thank you,
> Seth
>
>

Best Practices for RS and VSS

We are starting to use VSS2005 for storing projects and rdl files. We are
mainly using it for the versioning capabilities in VSS. Does anyone have any
tips for best practices on how to utilize Visual Studio 2005 and VSS 2005?
IF anyone has a link or two to sites that go over this topic that would be
helpful too.
The problem have had so far is trying to check out the rdl files through VSS
and then open them in Visual Sutdio to edit them. Whenever I have checked
out a file in VSS and then opened the file using the options in VSS to get to
Visual Studio (devenv) I either get a message stating that the carriage
return is messd up in the file or it opens the rdl code... not the designer
window.
If I check out the file in VSS and then open the file through Visual Studio
without using VSS to get to Visual Studio, everything works fine, but that
just seems a little too manual. Thanks in advacne for your help.On Jul 5, 4:50 pm, bsod55 <bso...@.discussions.microsoft.com> wrote:
> We are starting to use VSS2005 for storing projects and rdl files. We are
> mainly using it for the versioning capabilities in VSS. Does anyone have any
> tips for best practices on how to utilize Visual Studio 2005 and VSS 2005?
> IF anyone has a link or two to sites that go over this topic that would be
> helpful too.
> The problem have had so far is trying to check out the rdl files through VSS
> and then open them in Visual Sutdio to edit them. Whenever I have checked
> out a file in VSS and then opened the file using the options in VSS to get to
> Visual Studio (devenv) I either get a message stating that the carriage
> return is messd up in the file or it opens the rdl code... not the designer
> window.
> If I check out the file in VSS and then open the file through Visual Studio
> without using VSS to get to Visual Studio, everything works fine, but that
> just seems a little too manual. Thanks in advacne for your help.
This is a kind-of undocumented territory. This link might be of
assistance.
http://geekswithblogs.net/VROD/archive/2006/11/22/97817.aspx
Also, if VSS 2005 is not suitable, you could try Subversion or some
other Source Control software.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Best practice for storing long text fields

As we all know, there is a 8060 bytes size limit on SQL Server rows. I
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. I am given to review a design, which
esentially suggests moving the text columns to a separate TextFields
table. The TextFields table will have two columns - a unique reference
and a VARCHAR (4000) column, thus allowing us to crossreference with
the original record. My first impresion is that I'd rather use the SQL
Server 'text' DB type instead, which would allow me the same
functionality with much less effort and possibly better performance.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.
Any advise will be well appreciated.
TzankoTzanko wrote:

Quote:

Originally Posted by

As we all know, there is a 8060 bytes size limit on SQL Server rows. I
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. I am given to review a design, which
esentially suggests moving the text columns to a separate TextFields
table. The TextFields table will have two columns - a unique reference
and a VARCHAR (4000) column, thus allowing us to crossreference with
the original record. My first impresion is that I'd rather use the SQL
Server 'text' DB type instead, which would allow me the same
functionality with much less effort and possibly better performance.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.

I hear that VARCHAR(MAX) is the new TEXT, but it's only available
in SQL 2005.|||Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

As we all know, there is a 8060 bytes size limit on SQL Server rows.

Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.

Quote:

Originally Posted by

I have a table which requires a number of text fields (5 or 6).

Do these text fields hold the same text that spans fields, or are
they different texts?

Quote:

Originally Posted by

I am given to review a design, which esentially suggests moving the text
columns to a separate TextFields table. The TextFields table will have
two columns - a unique reference and a VARCHAR (4000) column, thus
allowing us to crossreference with the original record.

If they are different texts they should be in different columns, or you
should have some type column telling them apatt.

Quote:

Originally Posted by

My first impresion is that I'd rather use the SQL Server 'text' DB type
instead, which would allow me the same functionality with much less
effort and possibly better performance.

Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.

But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.

If the columns are different texts, I see little point to use the
text data type.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Are you saying that in SQL 2000 you can Span VarChar's into multiple columns
automatically? If so how?

Cheers, @.sh

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983CEED4849AEYazorman@.127.0.0.1...

Quote:

Originally Posted by

Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

>As we all know, there is a 8060 bytes size limit on SQL Server rows.

>
Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.
>

Quote:

Originally Posted by

>I have a table which requires a number of text fields (5 or 6).

>
Do these text fields hold the same text that spans fields, or are
they different texts?
>

Quote:

Originally Posted by

>I am given to review a design, which esentially suggests moving the text
>columns to a separate TextFields table. The TextFields table will have
>two columns - a unique reference and a VARCHAR (4000) column, thus
>allowing us to crossreference with the original record.

>
If they are different texts they should be in different columns, or you
should have some type column telling them apatt.
>

Quote:

Originally Posted by

>My first impresion is that I'd rather use the SQL Server 'text' DB type
>instead, which would allow me the same functionality with much less
>effort and possibly better performance.

>
Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.
>
But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.
>
If the columns are different texts, I see little point to use the
text data type.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?

No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Cool!

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983D9BE59C16AYazorman@.127.0.0.1...

Quote:

Originally Posted by

@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

>Are you saying that in SQL 2000 you can Span VarChar's into multiple
>columns automatically? If so how?

>
No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Many thnaks for your replies.

Just to clarify the issue:
The requirement is to create a table that has say 6 columns which store
strings (such as Description, Notes, etc.) Each of these 6 columns
should store a char string of max length of 4000 characters. The
problem is that SQL Server 2000 will not work if I simply defined the
columns as varchar(4000) as at some point the row size reaches the page
size of 8060 and this generates an error. There is a 8060 bytes limit
on SQL Server 2000 rows. Note that I am not trying to store the same
string into 6 different columns spanning from column to column. I have
a separate string to store in each column.

The question:
What is the best way to implement this in SQL Server 2000. In
particular I am looking at two options: Setting each of the 6 columns
to be of type 'text'. Looking at the documentation, it appears that
this would behave for as long as each string is not longer than 4000
characters and I am happy to have this limit. It however is unpleasant
to use the text type for longer than 4000 char strings, as in this case
I understand there are some specific ways of handling the data. Option
two is to create a new LongStrings table with 2 columns - long unique
number and varchar(4000). Each string is stored in this LongStrings
table and is crosreferenced (by using the unique ID) with its original
cell in its original table. Now I'd preffer option 1 (provided I do not
have to do anything special to handle the strings) and would like to
avoid option 2 because it is not easy to write queries to get the data.

Second question is what is the situation with SQL Server 2005. I
understand I can simply define the columns as varchar(max) and do not
have to do anything special. Has someone used this successfully and can
you confirm it ste case?

Thanks for your help.

Tzanko

@.sh wrote:

Quote:

Originally Posted by

Cool!
>
>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983D9BE59C16AYazorman@.127.0.0.1...

Quote:

Originally Posted by

@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?

No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

The question:
What is the best way to implement this in SQL Server 2000. In
particular I am looking at two options: Setting each of the 6 columns
to be of type 'text'. Looking at the documentation, it appears that
this would behave for as long as each string is not longer than 4000
characters and I am happy to have this limit. It however is unpleasant
to use the text type for longer than 4000 char strings, as in this case
I understand there are some specific ways of handling the data. Option
two is to create a new LongStrings table with 2 columns - long unique
number and varchar(4000). Each string is stored in this LongStrings
table and is crosreferenced (by using the unique ID) with its original
cell in its original table. Now I'd preffer option 1 (provided I do not
have to do anything special to handle the strings) and would like to
avoid option 2 because it is not easy to write queries to get the data.

The best in my opinion is to create two or three new tables and rename
the existing tbable, and the create a view that unifies them all. Then in
SQL 2005 you can scrap the view, and move the columns back to the mother
table. Very litte code would actually be affected.

If the key of the table is (cola, colb) the new tables should also have
the keys (cola, colb). Simply, what you do is that you split the columns
over several tables.

You should consider text or varchar(max) if you really need to fit more
than 8000 characters.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Best Practice for storing Blog text

Hi..

I am working on creating a blog for my site(not yet ready). Here's the idea --

I will have an 'Add a blog' page where it has a textarea and a submit button. I will write the blog in this textarea and hit submit. It will be added to the database and I will retrieve it in the actual blog page and bind it to a datalist. The blogtext even has the <br><font> tags etc... but then i'll hv to reframe the keywords with [keyword] I guess..

Is this the correct practice? Is storing blogtext in a SQL db a good idea..? Is there a better way of doing the same?

Thanks.

Sure, storing the blog data in SQL Server database is a usual thing, like the blog module in DNN. But anyway, beside of doing this funny thing, perhaps there is already (I assume so) a blog module out there for free with the source code which can be downloaded (with source code) and can be customized by yourself.

HTH, Jens Suessmeyer.

|||

Sure, storing the blog data in SQL Server database is a usual thing, like the blog module in DNN. But anyway, beside of doing this funny thing, perhaps there is already (I assume so) a blog module out there for free with the source code which can be downloaded (with source code) and can be customized by yourself.

HTH, Jens Suessmeyer.

|||

Thank you for the info. I have googled around a bit but found nothing.. hmmm.. if you happen to have a link, then lemme know.

Thanks.

|||

Did you have a look in www.sourceforge.net ? Once searched I found some entries, perhaps dasBlog: http://swik.net/dasBlog/SourceForge.net%3A+Project+File+Releases%3A+newtelligence+dasBlog+Community+Edition/dasblogce+dasBlog+1.8.5223.2+released+%28Mon%2C+07+Nov+2005+14%3A53%3A25+GMT%29/thr

HTH, jens Suessmeyer.

Best Practice for SQL2005 CRUD of RSS

Has anybody determined an optimal strategy when storing RSS in the database?
Here are some qualifying reuse considerations once the RSS is saved to the
database:
* the XML needs to be accessible for searching the channel and feed item
descriptions and likely other elements of the feed so query results may be
returned to the page.
* the XML needs to be retrieved and loaded into a Wizard control for editing
and updating nodal values and then resaved to the database.
* the structure of the XML may need to be modified by editors who want
support for various
namespaces for enclosures and so on.
I like this MSDN document [1] but I'm still wondering what the optimal
method to save the XML to the database is and what the optimal method to
retrieve the XML is to optimally support the considerations which qualify
reuse?
Does "your" experience with RSS coincide with the methodologies discussed in
that MSDN document? Hasn't anybody written a best practice document for SQL
Server CRUD of RSS and ASP.NET?
Your comments?
<%= Clinton Gallagher
NET csgallagher AT metromilwaukee.com
URL http://clintongallagher.metromilwaukee.com/
MAP 432'17"N 882'37"W : 432'17"N 882'37"W
[1]
http://msdn.microsoft.com/library/de...xmloptions.asp
Hello clintonG,

> * the XML needs to be accessible for searching the channel and feed
> item descriptions and likely other elements of the feed so query
> results may be returned to the page.
Generally speaking, I'm only in favor of using the XML Data type if your
going to use the data as XML. That means that you are going to query it with
XQuery, you want/need to valid it against an suitable schema and you're okay
spending the disk space to store the primary and potentially secondary XML
indices.
So much depends on how and what you want to query for and how as to how you
want to store it. For example. you could get by with varchar(max) or nvarchar(max)
rather then XML if you use Free-Text searches on the body. Or you could just
the XML type with an Index on it.

> * the XML needs to be retrieved and loaded into a Wizard control for
> editing and updating nodal values and then resaved to the database.
There's nothing on this that help me pick one data type of over the other
here.

> * the structure of the XML may need to be modified by editors who want
> support for various
> namespaces for enclosures and so on.
Then if you pick the XML type, you'll probably have an issue with schema
binding since columnar instances can be bound to a either 0 or 1 XML schema
collection at a time. Text BLOB storage works around this course since it
never binds to any XML schema collection.

> I like this MSDN document [1] but I'm still wondering what the optimal
> method to save the XML to the database is and what the optimal method
> to retrieve the XML is to optimally support the considerations which
> qualify reuse?
Loading an saving would probably be done using the ADO or ADO.NET which,
for all practical purposes, treats XML and the other text BLOBs as the same
thing.

> Does "your" experience with RSS coincide with the methodologies
> discussed in that MSDN document?
Generally speaking, yes.

> Hasn't anybody written a best
> practice document for SQL Server CRUD of RSS and ASP.NET?
No, its probably a little too niche for MS to do.
Cheers,
kt
|||Hello Kent, thanks for replying inline...
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:f2b9912a78538c8b2b35980cce0@.news.microsoft.co m...
> Hello clintonG,
>
> Generally speaking, I'm only in favor of using the XML Data type if your
> going to use the data as XML. That means that you are going to query it
> with XQuery, you want/need to valid it against an suitable schema and
> you're okay spending the disk space to store the primary and potentially
> secondary XML indices.
> So much depends on how and what you want to query for and how as to how
> you want to store it. For example. you could get by with varchar(max) or
> nvarchar(max) rather then XML if you use Free-Text searches on the body.
> Or you could just the XML type with an Index on it.
I'm going to have to use the XML data type -- and -- nvarchar(max). The XML
data type because I will need to return an entire feed expresses as XML when
responding to an AJAX function or a webservice that returns a feed and an
nvarchar(max) because the Primary and three secondary indices of the XML
data type are insufficient to expose an entire feed to search by query.
I'm validating the data on the server when the channel and feed items are
created but I may still need a schema when?

> There's nothing on this that help me pick one data type of over the other
> here.
It looks like I need to get the contents of the nvarchar(max) and populate
an XmlDocument object which is stored in memory to allow each node to be
accessible to each step of a Wizard control that is used to edit any given
node of the feed's XML. I don't know of a more efficient choice that would
allow me to get a node of the XML document on demand and load it into the
step of a Wizard control. Do you?

> Then if you pick the XML type, you'll probably have an issue with schema
> binding since columnar instances can be bound to a either 0 or 1 XML
> schema collection at a time. Text BLOB storage works around this course
> since it never binds to any XML schema collection.
Can I build a broadly scoped schema that declares all possible data types
for all of the known namespaces that I intend to support when extending RSS
and then use whichever data types the context of the feed requires or does
the schema map the entire XML file and then barfs when the XML file does not
contain all of the elements and respective data types that have been
declared in the schema?

> Loading an saving would probably be done using the ADO or ADO.NET which,
> for all practical purposes, treats XML and the other text BLOBs as the
> same thing.
I'll be using the XmlTextWriter and a SQL Provider with a stored procedure
to save the XML as nvarchar(max) until I learn there may be a better way.
I don't mind writing a lot of code. I just hate writing a lot of code I
would not have had to write after discovering I didn't know or gtry to learn
about a more efficient or easier way to write it. Thanks for your replies...
<snip />
<%= Clinton

Best Practice for SQL2005 CRUD of RSS

Has anybody determined an optimal strategy when storing RSS in the database?
Here are some qualifying reuse considerations once the RSS is saved to the
database:
* the XML needs to be accessible for searching the channel and feed item
descriptions and likely other elements of the feed so query results may be
returned to the page.
* the XML needs to be retrieved and loaded into a Wizard control for editing
and updating nodal values and then resaved to the database.
* the structure of the XML may need to be modified by editors who want
support for various
namespaces for enclosures and so on.
I like this MSDN document [1] but I'm still wondering what the optimal
method to save the XML to the database is and what the optimal method to
retrieve the XML is to optimally support the considerations which qualify
reuse?
Does "your" experience with RSS coincide with the methodologies discussed in
that MSDN document? Hasn't anybody written a best practice document for SQL
Server CRUD of RSS and ASP.NET?
Your comments?
<%= Clinton Gallagher
NET csgallagher AT metromilwaukee.com
URL http://clintongallagher.metromilwaukee.com/
MAP 432'17"N 882'37"W : 432'17"N 882'37"W
[1]
http://msdn.microsoft.com/library/d...5xmloptions.aspHello clintonG,

> * the XML needs to be accessible for searching the channel and feed
> item descriptions and likely other elements of the feed so query
> results may be returned to the page.
Generally speaking, I'm only in favor of using the XML Data type if your
going to use the data as XML. That means that you are going to query it with
XQuery, you want/need to valid it against an suitable schema and you're okay
spending the disk space to store the primary and potentially secondary XML
indices.
So much depends on how and what you want to query for and how as to how you
want to store it. For example. you could get by with varchar(max) or nvarcha
r(max)
rather then XML if you use Free-Text searches on the body. Or you could just
the XML type with an Index on it.

> * the XML needs to be retrieved and loaded into a Wizard control for
> editing and updating nodal values and then resaved to the database.
There's nothing on this that help me pick one data type of over the other
here.

> * the structure of the XML may need to be modified by editors who want
> support for various
> namespaces for enclosures and so on.
Then if you pick the XML type, you'll probably have an issue with schema
binding since columnar instances can be bound to a either 0 or 1 XML schema
collection at a time. Text BLOB storage works around this course since it
never binds to any XML schema collection.

> I like this MSDN document [1] but I'm still wondering what the optimal
> method to save the XML to the database is and what the optimal method
> to retrieve the XML is to optimally support the considerations which
> qualify reuse?
Loading an saving would probably be done using the ADO or ADO.NET which,
for all practical purposes, treats XML and the other text BLOBs as the same
thing.

> Does "your" experience with RSS coincide with the methodologies
> discussed in that MSDN document?
Generally speaking, yes.

> Hasn't anybody written a best
> practice document for SQL Server CRUD of RSS and ASP.NET?
No, its probably a little too niche for MS to do.
Cheers,
kt|||Hello Kent, thanks for replying inline...
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:f2b9912a78538c8b2b35980cce0@.news.microsoft.com...
> Hello clintonG,
>
> Generally speaking, I'm only in favor of using the XML Data type if your
> going to use the data as XML. That means that you are going to query it
> with XQuery, you want/need to valid it against an suitable schema and
> you're okay spending the disk space to store the primary and potentially
> secondary XML indices.
> So much depends on how and what you want to query for and how as to how
> you want to store it. For example. you could get by with varchar(max) or
> nvarchar(max) rather then XML if you use Free-Text searches on the body.
> Or you could just the XML type with an Index on it.
I'm going to have to use the XML data type -- and -- nvarchar(max). The XML
data type because I will need to return an entire feed expresses as XML when
responding to an AJAX function or a webservice that returns a feed and an
nvarchar(max) because the Primary and three secondary indices of the XML
data type are insufficient to expose an entire feed to search by query.
I'm validating the data on the server when the channel and feed items are
created but I may still need a schema when?

> There's nothing on this that help me pick one data type of over the other
> here.
It looks like I need to get the contents of the nvarchar(max) and populate
an XmlDocument object which is stored in memory to allow each node to be
accessible to each step of a Wizard control that is used to edit any given
node of the feed's XML. I don't know of a more efficient choice that would
allow me to get a node of the XML document on demand and load it into the
step of a Wizard control. Do you?

> Then if you pick the XML type, you'll probably have an issue with schema
> binding since columnar instances can be bound to a either 0 or 1 XML
> schema collection at a time. Text BLOB storage works around this course
> since it never binds to any XML schema collection.
Can I build a broadly scoped schema that declares all possible data types
for all of the known namespaces that I intend to support when extending RSS
and then use whichever data types the context of the feed requires or does
the schema map the entire XML file and then barfs when the XML file does not
contain all of the elements and respective data types that have been
declared in the schema?

> Loading an saving would probably be done using the ADO or ADO.NET which,
> for all practical purposes, treats XML and the other text BLOBs as the
> same thing.
I'll be using the XmlTextWriter and a SQL Provider with a stored procedure
to save the XML as nvarchar(max) until I learn there may be a better way.
I don't mind writing a lot of code. I just hate writing a lot of code I
would not have had to write after discovering I didn't know or gtry to learn
about a more efficient or easier way to write it. Thanks for your replies...
<snip />
<%= Clinton