Is VARCHAR data type same as UTF-8?

Hello
Is data fields of varchar type internally encoded as UTF-8?
How is cyrillic text stored in varchar data fileds, as UTF-8 or not?No. Char, Varchar and Text are a CodePage representation.
If you don't want to have a lot of problems with Cyrillic and other things
like the Euro symbol; you should use nchar, nvarchar and ntext instead.
See:
http://msdn.microsoft.com/library/d...ataencoding.asp
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
<s_alexander04@.list.ru> wrote in message
news:1143694534.065217.68140@.i40g2000cwc.googlegroups.com...
> Hello
> Is data fields of varchar type internally encoded as UTF-8?
> How is cyrillic text stored in varchar data fileds, as UTF-8 or not?
>|||You can check windows region ,SQL Server settings and Database,Column
Collation.
To support globalization, you use Unicode data type such as
nchar,nvarchar,ntext.
"s_alexander04@.list.ru"?? ??? ??:

> Hello
> Is data fields of varchar type internally encoded as UTF-8?
> How is cyrillic text stored in varchar data fileds, as UTF-8 or not?
>

Is this bug with Convert?

I was trying to debug some DateTime.Now in a C# project and while debugging I found this.

In your Sql Management Studio, type this:

The 916 becomes 917. Why does my millisecond get screwed?

select convert(datetime, '2007-06-29 15:22:31:921') -- prints 2007-06-29 15:22:31.920

select convert(datetime, '2007-06-29 15:22:31:916') -- print 2007-06-29 15:22:31.917

That is because of the precision of the datetime data type 1/300 of a second. Check BOL for more info about datetime data type.

select convert(datetime, '2007-06-29 15:22:31:998')

go

AMB

Is there query I can write to remove empty tags from a xml field?

I want to remove empty tags:

<name></name>

from an xml type field in a table. Can I do it using a query and a modify?

Use the modify method and the XQuery delete instruction as follows (uses an XML variable but can of course also be done with a column of type XML):

Code Snippet

DECLARE @.xml xml;

SET @.xml = '<root>

<name/>

<name></name>

<element>

<name />

</element>

</root>';

SET @.xml.modify('

delete //*[not(node())]

');

SELECT @.xml;

|||

PERFECT!!! I am thrilled. Easy and simple and solved my problem.

Is there any way to insert image into DB without coding ?

I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.

See my response to your identical question posted in the [SQL Server Database Engine] forum.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

Is there any way to insert image into DB without coding ?

I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005 without coding. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.

Microsoft Access is a client (front-end) application that happens to also work with data storage.

SQL Server is a server (back-end) application that requires a client to move data in and out.

So the short answer is No. However, there are some client applications that do not appear to require 'coding' since it is done 'beneath the covers' for you.

You can connect Access to a SQL Server database, and then use your skills with Access to move data into and out of SQL Server.

|||

It is typically a better practice to store the images elsewhere and store a reference to the image in a database; give a look here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1010368&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362808&SiteID=1

(In a short while I will delete the other post that duplicates this one.)

As always, Arnie, thank you for your help with all of this.

Is there any way that you could search for a value in any nodes within your xml data type?

Hi everyone,

I was wondering if there is any way that you could search for a value in any nodes within your xml data type without actually knowing what nodes you have or how many there are.

Please provide sample query for it if possible. Thanks.

declare @.x xml

set @.x = '<root><x>Test1</x><y>Test2<z>Test3</z></y></root>'

select @.x.query('//*[text()="Test1"]')

select @.x.query('//*[text()="Test2"]')

select @.x.query('//*[text()="Test3"]')

Results:

<x>Test1</x>

(1 row(s) affected)

-

<y>Test2<z>Test3</z></y>

(1 row(s) affected)

--

<z>Test3</z>

(1 row(s) affected)

|||

An open ended querying of xml that has some "value" isn't trivial. For example, if your xml is semi-structured and has mixed nodes, the above query might return results you didn't expect. The following query will return the element foo. That is because foo has two text() nodes and the = is the existential operator. Again, this could be what you want anyway. Also, this query doesn't find the attribute bar.

select convert(xml, '<foo bar="abc">abc<bar/>abc</foo>').query('//*[text() = "abc"]')

However, depending on the structure of you XML, this might be fine.

If you want to include elements who's attributes match the target value, you can do the following:

select convert(xml, '<foo bar="abc"/> <baz>abc</baz> <foo bar="not"/>').query('//*[text() = "abc" or @.* = "abc"]')

Regards,

Galex

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
Lijun
Lijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
David Portas
SQL Server MVP

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
LijunLijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
David Portas
SQL Server MVP
--

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
LijunLijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
--
David Portas
SQL Server MVP
--

Is there an XMLTYPE in SQL 2000?

Oracle 9 has an XMLTYPE data type.

I am interested in inserting a formatted block of XML in a SQL 2000 column to record a set of data in one column.

Is there a way to do this, or a datatype that comes close?

Thanks.As far as I know you have to use NTEXT. The new version of SQLServer due out later this year addresses the XML data type.