data type lengths

I am new in SQL. I have a question about an INSERT INTO Statement which I cannot figure it. Please help me.
Here is what I did.

CREATE TABLE titles
(
title_id CHAR(3) NOT NULL,
title_name VARCHAR(20) NOT NULL,
type VARCHAR(10) ,
pub_id CHAR(3) NOT NULL,
pages INTEGER ,
price DECIMAL(5,2) ,
sales INTEGER ,
pubdate DATE ,
contract SMALLINT NOT NULL,
CONSTRAINT pk_titleS PRIMARY KEY(title_id)
);

INSERT INTO titles(title_id, title_name,type,pub_id,pages,price,sales,pubdate,contract)
VALUES('T01','1977!','history','P01',107,21.99,566,DATE'2000-08-01',1);
HERE IS WHAT I GOT AFTER I ran it

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '2000-08-01'.

> On Tuesday, February 12, 2008 8:59 AM 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?


>> On Tuesday, February 12, 2008 9:09 AM Steven Wilmot wrote:

>> "Smokey Grindel" wrote in message
>>
>>
>>
>> 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


>>> On Tuesday, February 12, 2008 9:12 AM AlejandroMes wrote:

>>> Smokey Grindel,
>>>
>>> varchar(n) / nvarchar(n)
>>>
>>> n - number of characters
>>>
>>> nvarchar uses 2 bytes percharacters, so:
>>>
>>> varchar(10) - could use a maximum of 10 bytes
>>> nvarchar(10) - could use a maximum of 20 bytes
>>>
>>>
>>> AMB
>>>
>>>
>>>
>>> "Smokey Grindel" wrote:


>>>> On Tuesday, February 12, 2008 9:22 AM SpamFreePleas wrote:

>>>> 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:


>>>>> On Tuesday, February 12, 2008 9:43 AM Smokey Grindel wrote:

>>>>> 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
>>>>>


>>>>>> On Tuesday, February 12, 2008 9:47 AM Aaron Bertrand [SQL Server MVP] wrote:

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


>>>>>>> On Tuesday, February 12, 2008 9:55 AM Smokey Grindel wrote:

>>>>>>> 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
>>>>>>>


>>>>>>>> On Tuesday, February 12, 2008 9:56 AM Aaron Bertrand [SQL Server MVP] wrote:

>>>>>>>> Book titles, page numbers? I have plenty of MS Press books on my shelf. I
>>>>>>>> do not have the book you originally mentioned though.


>>>>>>>>> On Tuesday, February 12, 2008 10:17 AM Smokey Grindel wrote:

>>>>>>>>> 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
>>>>>>>>>


>>>>>>>>>> On Tuesday, February 12, 2008 11:13 AM Aaron Bertrand [SQL Server MVP] wrote:

>>>>>>>>>> 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
>>>>>>>>>>


>>>>>>>>>>> On Tuesday, February 12, 2008 11: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 message
>>>>>>>>>>>


>>>>>>>>>>>> On Tuesday, February 12, 2008 1:06 PM Tom Cooper wrote:

>>>>>>>>>>>> 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:
>>>>>>>>>>>>
>>>>>>>>>>>> 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.


>>>>>>>>>>>>> On Tuesday, February 12, 2008 6:13 PM Kalen Delaney wrote:

>>>>>>>>>>>>> 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
>>>>>>>>>>>>>


>>>>>>>>>>>>>> On Tuesday, February 12, 2008 9:23 PM Smokey Grindel wrote:

>>>>>>>>>>>>>> I'd really hope the book on the internals of SQL Server does not make that
>>>>>>>>>>>>>> mistake I need to buy that book... gona have to put it on the order list
>>>>>>>>>>>>>> at work..


>>>>>>>>>>>>>>> On Wednesday, February 13, 2008 8:15 PM Alex Kuznetsov wrote:

>>>>>>>>>>>>>>> On Feb 12, 10:31 am, "Smokey Grindel" wrote:
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> enews:ulxb1JZbIHA.3484@TK2MSFTNGP06.phx.gbl...
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> ed
>>>>>>>>>>>>>>> ow
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> 05
>>>>>>>>>>>>>>> i
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> 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','=C1=C2=D7=C7=C4=C5=D6=DA=C9=CB')
>>>>>>>>>>>>>>> --------------
>>>>>>>>>>>>>>> success
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> insert #t(c,n) values('1234567890','=C1=C2=D7=C7=C4=C5=D6=DA=C9=CB=CC')
>>>>>>>>>>>>>>> ---------------------------------------
>>>>>>>>>>>>>>> Msg 8152, Level 16, State 4, Line 1
>>>>>>>>>>>>>>> String or binary data would be truncated.
>>>>>>>>>>>>>>> The statement has been terminated.


>>>>>>>>>>>>>>>> On Wednesday, February 13, 2008 8:16 PM --CELKO-- wrote:

>>>>>>>>>>>>>>>> Standard SQL has both byte and character length functions for things
>>>>>>>>>>>>>>>> like this.


>>>>>>>>>>>>>>>>> On Wednesday, December 01, 2010 12:24 PM Iain Hambleton wrote:

>>>>>>>>>>>>>>>>> There is slightly more to this also -
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> If you use SP_HELP to find the length of a column, it retrieves the length of VARCHAR columns in characters, but the length of NVARCHAR columns in bytes.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> So if SP_HELP reports an NVARCHAR column's length to be 100, it will only be able to store 50 characters. This can be a source of confusion.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> See this page for more info:
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> http://www.dbforums.com/microsoft-sql-server/1610696-sql_help-tablenam...rong-re
 >> Stay informed about: data type lengths 

On Wed, 06 Apr 2011 20:39:04 GMT, Seng wrote:

>I am new in SQL. I have a question about an INSERT INTO Statement which I cannot figure it. Please help me.
>Here is what I did.
>
>CREATE TABLE titles
>(
> title_id CHAR(3) NOT NULL,
> title_name VARCHAR(20) NOT NULL,
> type VARCHAR(10) ,
> pub_id CHAR(3) NOT NULL,
> pages INTEGER ,
> price DECIMAL(5,2) ,
> sales INTEGER ,
> pubdate DATE ,
> contract SMALLINT NOT NULL,
> CONSTRAINT pk_titleS PRIMARY KEY(title_id)
>);
>
>INSERT INTO titles(title_id, title_name,type,pub_id,pages,price,sales,pubdate,contract)
>VALUES('T01','1977!','history','P01',107,21.99,566,DATE'2000-08-01',1);
>HERE IS WHAT I GOT AFTER I ran it
>
>Msg 102, Level 15, State 1, Line 2
>Incorrect syntax near '2000-08-01'.

Remove "DATE".

[snip]

Sincerely,

Gene Wirchenko
 >> Stay informed about: data type lengths 

Seng ( ) writes:
> I am new in SQL. I have a question about an INSERT INTO Statement which
> I cannot figure it. Please help me.
> Here is what I did.
>
> CREATE TABLE titles
> (
> title_id CHAR(3) NOT NULL,
> title_name VARCHAR(20) NOT NULL,
> type VARCHAR(10) ,
> pub_id CHAR(3) NOT NULL,
> pages INTEGER ,
> price DECIMAL(5,2) ,
> sales INTEGER ,
> pubdate DATE ,
> contract SMALLINT NOT NULL,
> CONSTRAINT pk_titleS PRIMARY KEY(title_id)
> );
>
> INSERT INTO titles(title_id, title_name,type,pub_id,pages,price,sales,pubdate,contract)
> VALUES('T01','1977!','history','P01',107,21.99,566,DATE'2000-08-01',1);
> HERE IS WHAT I GOT AFTER I ran it
>
> Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near '2000-08-01'.

Where did you find that syntax? PostgreSQL? Actually, I believe that
this valid ANSI SQL, but alas it is not implemented in SQL Server. So
you need to take out DATE.


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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
 >> Stay informed about: data type lengths