data type lengths

I think you have accidently confused the number of characters the column will
hold versus the number of bytes required to store the characters in terms of
disk space.

~ SPARKER ~


"Smokey Grindel" wrote:

> Ok so nvarchar and varchar are unicode and ansi... well all the books I read
> say that you specify the length in number of bytes... so nvarchar takes 2
> bytes per char... varchar takes 1.... well they claim these are you you
> represent a 10 char in each
>
> varchar(10)
> nvarchar(20)
>
> now here is where I am confused... ok the varchar(10) takes 10 chars with no
> problems... but I can also put 20 chars into the nvarchar... why? all the
> books claim only 10 will fit... now if i put 21 I get a truncation error
> from sql server... what am I missing that the books are not telling me?
>
>
>
 >> Stay informed about: data type lengths 

"Smokey Grindel" wrote in message


> now here is where I am confused... ok the varchar(10) takes 10 chars with
> no problems... but I can also put 20 chars into the nvarchar... why? all
> the books claim only 10 will fit... now if i put 21 I get a truncation
> error from sql server... what am I missing that the books are not telling
> me?
>

I would find it surprising for any book to tell you that "nvarchar(x)" will
only STORE x/2 characters

Is far as I am aware, it has always been the case that
varchar(x) will hold a maximum of x characters (each occupying one 8-bit
byte)
and
nvarchar(x) will hold a maxium of x characters (each occupying two 8-bit
bytes)
therefore having a storage length of "2x"

See: http://msdn2.microsoft.com/en-us/library/ms186939.aspx

Also:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
(The SQL-92 standard)#

---

Are you able to able to provide a reference to any book / web-page that
indicates the opposite ?

Steven
 >> Stay informed about: data type lengths 

Well be suprised then... "SQL SERVER 2005: Implementation and Maintenance
Trainking Kit" book by Solid Quality Learning says and I quote "For example,
a char(10) can store a maximum 10 characters because each character requires
one byte of storeage, whereas an nchar(10) can store a maximum of five
characters because each Unicode character requires two bytes of storage."
every book ive read says something similar to that...

"Steven Wilmot" wrote in message

>
> "Smokey Grindel" wrote in message
>
>
>> now here is where I am confused... ok the varchar(10) takes 10 chars with
>> no problems... but I can also put 20 chars into the nvarchar... why? all
>> the books claim only 10 will fit... now if i put 21 I get a truncation
>> error from sql server... what am I missing that the books are not telling
>> me?
>>
>
> I would find it surprising for any book to tell you that "nvarchar(x)"
> will only STORE x/2 characters
>
> Is far as I am aware, it has always been the case that
> varchar(x) will hold a maximum of x characters (each occupying one
> 8-bit byte)
> and
> nvarchar(x) will hold a maxium of x characters (each occupying two
> 8-bit bytes)
> therefore having a storage length of "2x"
>
> See: http://msdn2.microsoft.com/en-us/library/ms186939.aspx
>
> Also:
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
> (The SQL-92 standard)#
>
> ---
>
> Are you able to able to provide a reference to any book / web-page that
> indicates the opposite ?
>
> Steven
>
 >> Stay informed about: data type lengths 

On Feb 12, 10:31 am, "Smokey Grindel" wrote:
> yep.. wish they'd have an updated download site or something we could get
> revisions for... the books say where to submit corrections to in them dont
> they? kinda remember MS press books having somewhere to do that..
>
> "Aaron Bertrand [SQL Server MVP]" wrote in messagenews:ulxb1JZbIHA.3484@TK2MSFTNGP06.phx.gbl...
>
> > Sounds backward to me... it is much easier (never mind financially
> > feasible) for them to update the PDF than a printed book. Especially if
> > you already own the book... it's not like a recall on a car.
>
> > Of course just like a printed book, they can't update a PDF you downloaded
> > a long time ago (or that was distributed on the CD with the book).
>
> > "Smokey Grindel" wrote in message
> >
> >> I'll look those up again, I've been reading these one at a time so its
> >> been a few weeks since ive been through the others... the one I am on now
> >> is is ISBN-13: 978-0-7356-2271-5 Page 113 last paragraph...
>
> >> The wierd thing I think the eBook versions that come with the books are
> >> out of date... I just went looking in the print book for that quote and
> >> its not there on that page... but the ebook has it there... maybe they
> >> just didnt update the PDF ebooks?
>
> >> The other error was in T-SQL Programming INSIDE Microsoft SQL SERVER 2005
> >> have to find that page though i dont have the ebook on hand for it but i
> >> know it was there in the ebook cant seem to find it in print though...
> >> maybe the ebooks are just messed up

see for yourself, and trust your own eyes more than any book:

create table #t(c varchar(10), n nvarchar(10))
insert #t(c,n) values('1234567890','1234567890')
--------------
success

insert #t(c,n) values('1234567890a','1234567890')
-----------------------
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

insert #t(c,n) values('1234567890','1234567890a')
--------------
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.

insert #t(c,n) values('1234567890','ÁÂ×ÇÄÅÖÚÉË')
--------------
success

insert #t(c,n) values('1234567890','ÁÂ×ÇÄÅÖÚÉËÌ')
---------------------------------------
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
 >> Stay informed about: data type lengths 

The book is wrong. The n dictates number of characters to allow, not the
number of bytes to restrict storage.


> Well be suprised then... "SQL SERVER 2005: Implementation and Maintenance
> Trainking Kit" book by Solid Quality Learning says and I quote "For
> example, a char(10) can store a maximum 10 characters because each
> character requires one byte of storeage, whereas an nchar(10) can store a
> maximum of five characters because each Unicode character requires two
> bytes of storage." every book ive read says something similar to that...
 >> Stay informed about: data type lengths 

Yeah figured its wrong when I read that something just didn't feel right
because I new I've entered that number as number of chars in the past for
nvarchar... but I just found 3 other books on my shelf by MS Press that say
the same thing.... is anyone proofing these books?....

"Aaron Bertrand [SQL Server MVP]" wrote in message

> The book is wrong. The n dictates number of characters to allow, not the
> number of bytes to restrict storage.
>
>
>> Well be suprised then... "SQL SERVER 2005: Implementation and
>> Maintenance Trainking Kit" book by Solid Quality Learning says and I
>> quote "For example, a char(10) can store a maximum 10 characters because
>> each character requires one byte of storeage, whereas an nchar(10) can
>> store a maximum of five characters because each Unicode character
>> requires two bytes of storage." every book ive read says something
>> similar to that...
>
>
 >> Stay informed about: data type lengths 

> Yeah figured its wrong when I read that something just didn't feel right
> because I new I've entered that number as number of chars in the past for
> nvarchar... but I just found 3 other books on my shelf by MS Press that
> say the same thing.... is anyone proofing these books?....

Book titles, page numbers? I have plenty of MS Press books on my shelf. I
don't have the book you originally mentioned though.
 >> Stay informed about: data type lengths 

I'll look those up again, I've been reading these one at a time so its been
a few weeks since ive been through the others... the one I am on now is is
ISBN-13: 978-0-7356-2271-5 Page 113 last paragraph...

The wierd thing I think the eBook versions that come with the books are out
of date... I just went looking in the print book for that quote and its not
there on that page... but the ebook has it there... maybe they just didnt
update the PDF ebooks?

The other error was in T-SQL Programming INSIDE Microsoft SQL SERVER 2005
have to find that page though i dont have the ebook on hand for it but i
know it was there in the ebook cant seem to find it in print though... maybe
the ebooks are just messed up



"Aaron Bertrand [SQL Server MVP]" wrote in message

>> Yeah figured its wrong when I read that something just didn't feel right
>> because I new I've entered that number as number of chars in the past for
>> nvarchar... but I just found 3 other books on my shelf by MS Press that
>> say the same thing.... is anyone proofing these books?....
>
> Book titles, page numbers? I have plenty of MS Press books on my shelf.
> I don't have the book you originally mentioned though.
>
 >> Stay informed about: data type lengths 

Sounds backward to me... it is much easier (never mind financially feasible)
for them to update the PDF than a printed book. Especially if you already
own the book... it's not like a recall on a car.

Of course just like a printed book, they can't update a PDF you downloaded a
long time ago (or that was distributed on the CD with the book).





"Smokey Grindel" wrote in message

> I'll look those up again, I've been reading these one at a time so its
> been a few weeks since ive been through the others... the one I am on now
> is is ISBN-13: 978-0-7356-2271-5 Page 113 last paragraph...
>
> The wierd thing I think the eBook versions that come with the books are
> out of date... I just went looking in the print book for that quote and
> its not there on that page... but the ebook has it there... maybe they
> just didnt update the PDF ebooks?
>
> The other error was in T-SQL Programming INSIDE Microsoft SQL SERVER 2005
> have to find that page though i dont have the ebook on hand for it but i
> know it was there in the ebook cant seem to find it in print though...
> maybe the ebooks are just messed up
 >> Stay informed about: data type lengths 

yep.. wish they'd have an updated download site or something we could get
revisions for... the books say where to submit corrections to in them dont
they? kinda remember MS press books having somewhere to do that..

"Aaron Bertrand [SQL Server MVP]" wrote in message

> Sounds backward to me... it is much easier (never mind financially
> feasible) for them to update the PDF than a printed book. Especially if
> you already own the book... it's not like a recall on a car.
>
> Of course just like a printed book, they can't update a PDF you downloaded
> a long time ago (or that was distributed on the CD with the book).
>
>
>
>
>
> "Smokey Grindel" wrote in message
>
>> I'll look those up again, I've been reading these one at a time so its
>> been a few weeks since ive been through the others... the one I am on now
>> is is ISBN-13: 978-0-7356-2271-5 Page 113 last paragraph...
>>
>> The wierd thing I think the eBook versions that come with the books are
>> out of date... I just went looking in the print book for that quote and
>> its not there on that page... but the ebook has it there... maybe they
>> just didnt update the PDF ebooks?
>>
>> The other error was in T-SQL Programming INSIDE Microsoft SQL SERVER 2005
>> have to find that page though i dont have the ebook on hand for it but i
>> know it was there in the ebook cant seem to find it in print though...
>> maybe the ebooks are just messed up
>
>
 >> Stay informed about: data type lengths 

And, while you gave does show how many characters can be stored in an
nvarchar(10) column, when you are actually using them, don't forget the N
for the nvarchar constants, eg,
insert #t(c,n) values('1234567890',N'ÁÂ×ÇÄÅÖÚÉË')

Tom

"Alex Kuznetsov" wrote in message

On Feb 12, 10:31 am, "Smokey Grindel" wrote:
> yep.. wish they'd have an updated download site or something we could get
> revisions for... the books say where to submit corrections to in them dont
> they? kinda remember MS press books having somewhere to do that..
>
> "Aaron Bertrand [SQL Server MVP]" wrote in
> messagenews:ulxb1JZbIHA.3484@TK2MSFTNGP06.phx.gbl...
>
> > Sounds backward to me... it is much easier (never mind financially
> > feasible) for them to update the PDF than a printed book. Especially if
> > you already own the book... it's not like a recall on a car.
>
> > Of course just like a printed book, they can't update a PDF you
> > downloaded
> > a long time ago (or that was distributed on the CD with the book).
>
> > "Smokey Grindel" wrote in message
> >
> >> I'll look those up again, I've been reading these one at a time so its
> >> been a few weeks since ive been through the others... the one I am on
> >> now
> >> is is ISBN-13: 978-0-7356-2271-5 Page 113 last paragraph...
>
> >> The wierd thing I think the eBook versions that come with the books are
> >> out of date... I just went looking in the print book for that quote and
> >> its not there on that page... but the ebook has it there... maybe they
> >> just didnt update the PDF ebooks?
>
> >> The other error was in T-SQL Programming INSIDE Microsoft SQL SERVER
> >> 2005
> >> have to find that page though i dont have the ebook on hand for it but
> >> i
> >> know it was there in the ebook cant seem to find it in print though...
> >> maybe the ebooks are just messed up

see for yourself, and trust your own eyes more than any book:

create table #t(c varchar(10), n nvarchar(10))
insert #t(c,n) values('1234567890','1234567890')
--------------
success

insert #t(c,n) values('1234567890a','1234567890')
-----------------------
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

insert #t(c,n) values('1234567890','1234567890a')
--------------
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.

insert #t(c,n) values('1234567890','ÁÂ×ÇÄÅÖÚÉË')
--------------
success

insert #t(c,n) values('1234567890','ÁÂ×ÇÄÅÖÚÉËÌ')
---------------------------------------
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
 >> Stay informed about: data type lengths 

However, that is not how the publisher does it. They will fix errors in the
printed book when they do a reprint, but it's not part of their scheduled
maintenance to regenerate the e-books.

FWIW, Inside SQL Server: The Storage Engine does not make this mistake.

Itzik has a list of errors posted at www.InsideTSQL.com, but I couldn't find
this error listed there.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com


"Aaron Bertrand [SQL Server MVP]" wrote in message

> Sounds backward to me... it is much easier (never mind financially
> feasible) for them to update the PDF than a printed book. Especially if
> you already own the book... it's not like a recall on a car.
>
> Of course just like a printed book, they can't update a PDF you downloaded
> a long time ago (or that was distributed on the CD with the book).
>
>
>
>
>
> "Smokey Grindel" wrote in message
>
>> I'll look those up again, I've been reading these one at a time so its
>> been a few weeks since ive been through the others... the one I am on now
>> is is ISBN-13: 978-0-7356-2271-5 Page 113 last paragraph...
>>
>> The wierd thing I think the eBook versions that come with the books are
>> out of date... I just went looking in the print book for that quote and
>> its not there on that page... but the ebook has it there... maybe they
>> just didnt update the PDF ebooks?
>>
>> The other error was in T-SQL Programming INSIDE Microsoft SQL SERVER 2005
>> have to find that page though i dont have the ebook on hand for it but i
>> know it was there in the ebook cant seem to find it in print though...
>> maybe the ebooks are just messed up
>
>
 >> Stay informed about: data type lengths 

Standard SQL has both byte and character length functions for things
like this.
 >> Stay informed about: data type lengths