what is a ntext(16) 1

[RSX02]

Hi
I have a database that it is not me who created it.
I have a field in one table that is declared as ntext with a length of 16. I thought it was as a nvarchar(16) but I can see record in it with more than 16 characters..
Thanks in advance

 

[JamesLean]

ntext is used to store unicode character data of more than 4000 characters. The length refers to the number of bytes rather than the number of characters.

If you don't need to store more than 4000 characters then I would use nvarchar(4000). It is a lot easier to work with than the binary (text, ntext, image) data types.

Also, if you don't need to store unicode data then varchar columns can hold up to 8000 characters.

--James

 

[RSX02]

ohhhh!
Is it why I can not declare a parameter in a stored procedure ntext? If I have to use that type of data in my database and I want to declare a variable that can hold its value...how should I declare my variable?
Thanks in advance

 

[JamesLean]

No, you can't use text/ntext for local variables or parameters.

--James

 

[RSX02]

If I declare my parameter as nvarchar(4000) will it work?

 

[JamesLean]

Yes. But obviously you will only be able to pass in 4000 characters ;-)

--James

 

[RSX02]

About how many character ntext(16) is?
Thanks in advance

 

[JamesLean]

Sorry, I've misled you slightly here. You can't use text or ntext for local variables but you CAN use them for stored proc parameters.

ntext will store up to 1,073,741,823 characters.

FYI, all this info (and more) is in Books Online.

--James

 

[RSX02]

James
I tried to put this parameters in my StoredProcedure
but it doesn't work:

@NoteContent ntext(16)

I've got an error message as follow
Error 2716: Column or parameter #5: Cannot specify a column width on data type ntext. Parameter '@NoteContent' has an invalid data type.

???

 

[TheBugSlayer]

RSX02m James is saying that you cannot declare a NTEXT variable in your stored procedure but you can have a stored procedure parameter declared as NTEXT.

 

[JamesLean]

As the error suggests, you don't have to specify the size of an ntext type, it's always the same:

CREATE PROC myproc
  @notecontent ntext
AS
...

--James