Very Computer

Quote:>> ..my app will never be international, is there any reason why I might

It is better to stick with non-UNICODE types.

Quote:>> My colleague said nvarchar and nchar datatypes are "more effecient"

AFAIK, there is nothing with proves his claim.

Quote:>> My understanding of the text datatype is that it is only preferable

That is pretty reasonable

Quote:>>...there any other rules I'm missing? Any to do with effeciency etc?<<

--
- Anith
(Please respond only to newsgroups)

Hi

My understanding of the var- datatypes is that they are preferable when the
values in the column will vary alot. And not needed when the column data is
expected to be fairly consistent. Other than this, neither one is preferable
over the other. Have I got this right?

My understanding of the n- variables is that they are unicode datatypes. As
my app will never be international, is there any reason why I might want to
use or avoid them? My colleague said nvarchar and nchar datatypes are "more
effecient" than the varchar and char equivilents. Is he right? I don't see
why this should be the case.

My understanding of the text datatype is that it is only preferable when the
data is larger than 8k.

Are there any other rules I'm missing? Any to do with effeciency etc?

Thanks in advance

Sam

Quote:> My colleague said nvarchar and nchar datatypes are "more
> effecient" than the varchar and char equivilents. Is he right?

http://www.aspfaq.com/2354

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.

if (by some chance) I have a space symbol(s), that are prefixing or
postfixing value in a varchar column, how can I detect it?

I was horrified to see, that:
1) post spaces are excluded from Length function (if prefixed, then
included), e.g.:

select Len('string1') = 7
select Len('string1                 ') = 7
but select Len('       string1') = 15 )

2) postfix space does not affect joins, like and equal statements:
e.g.
where col1 = 'string1' will happily get: 'string1 '
where col1 like 'string1' will happily get: 'string1 '

There should be easy way to distinguish between 'string1   '
and 'string1' in varchar column, but how?

3) What if the varchar value is postfixed with any CTRL symbols/esqape
sequences?

How can I see them in SQL query window and cut them out, without
third-party tools?

Thank you in advance,
Alexei

Quote:> There should be easy way to distinguish between 'string1   '
> and 'string1' in varchar column, but how?

Note that DATALENGTH does not ignore trading spaces, regardless of
ansi_padding setting, e.g.

SET ANSI_PADDING ON
SELECT LEN('foo    ')
SELECT DATALENGTH('foo    ')

SET ANSI_PADDING OFF
SELECT LEN('foo    ')
SELECT DATALENGTH('foo    ')

returns
3
7
3
7

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.

Thanks all of you for clarifying that.

Sam