How much storage space does nvarchar() eats? Always max?

Hello, everybody,

I am in need of storing long pieces of text in database, length between 2000 and 4000 characters (nvarchar limit).

If I have a column in a table:

my_text nvarchar(2000)

does this mean that in database the size used will always be 2000*2=4000 bytes at least (+ rounding), independent on what the real length of the text stored is?

Anoter alternative - storing texts in files is expected to become unmanageable very soon, so I would like to keep to the db.

Or, would it be reasonable to have such a table:

my_text_1 nvarchar(500),
my_text_2 nvarchar(500),
my_text_3 nvarchar(500),
my_text_4 nvarchar(500)

Or declaration already means the space for a column is reserved even if it is nullable?

My DBs are MS SQL Server 2000/2005.As I understand it the amount of space used is limited to the size of the data you have in the nvarchar field. So "hello world" will take up the same amouint of space in an nvarchar(20) as it does in an nvarchar(200).|||But why use unicode? Do you really need it?

How many columns is ok?

Hello!

I have a database table containing about 40 columns, mostly bit data type, some nvarchar and smallint columns...

Is it ok to have so many columns in one table and how does the number of columns affect database perfomance? Wouldn't it be better to split this table?

Thank You!
JannoPossible? Yes. OK. Not really.

My guess is that you need to work on normalizing your database. For example, all those bit datatypes might be better served off in their own table, with a vertical not horizontal, design. This would much more easily allow you to add traits (bits) in the future.

What is the nature of the table, and what is being stored in all of those BIT columns?|||Pjmcb,

Basically it's a databese for flat rental site. Those BIT columns are used to store True/False type values.

In case I'm going to store those values verticaly in a separate table... could you please explain me how does it exactly works - I'm a SQL Server newbie...

Ads Table structure:
------------
AdIDint
AdTypesmallint
UserIDnvarchar
Activebit

AdDetails Table structure:
------------
AdIDint
Areasmallint
StreetNamenvarchar
Neighbourhoodnvarchar
Citynvarchar50
PostCodenvarchar15
AvailableFromdatetime
Rentmoney8
MinimumStaysmallint
Depositmoney8
PropertyTypenvarchar
Floorsmallint2
Parkingbit1
AdditionalCostmoney
Bathroomssmallint2
Bedroomssmallint2
Gasbit1
Cablebit1
TVbit1
Phonebit1
Closetbit1
Furnishedbit1
AirConditioningbit1
Bathbit1
Fireplacebit1
Balconybit1
Elevatorbit1
Washerbit1
KitchenAccessbit1
Storagebit1
Gardenbit1
Poolbit1
Gymnbit1|||You might have a second table, and call it, say Traits.

In this table, you might include each of the bit traits, along with an ID for each trait.

Then, you could have a join table to link each particular trait with each type of apartment. You would only have records in the join table for those traits that applied to a specific apartment.

You might have a second table for manetary items, with a similar join table, and a third with rooms or similar information.

With your join tables for these last two items, you could include additional information. For example, you might have:

Rooms
--
RoomID
RoomName
RoomDesc

(which might contain: Bedroom; Bathroom; Closets; Laundry Room; Kitchen; Living Room)

AdRooms
--
AdID
RoomID
RoomCount

Obviously, I'm throwing this out off the top of my head. But when you need to run an ad featuring an apartment with a SunRoom, all you need to do is add a record to your Rooms, table, and assign that value to your new apartment. You don't have to go in and change your database design.

Your retrieval of data might be a bit more complicated (actually, I would argue that ultimately, once you get comfortable with SQL, it will be easier), but growing the application will be much easier.|||I completely agree in principle with normalizing this, but it does depend on how the data is being used. if every single ad always lists each of those categories with a yes/no, it may make sense to leave it denormalized. bits don't hurt you that much as far as performance, the table is still relatively narrow. normalizing does make maintenance much better, as you point out, when you want to add additional attributes. it also makes the query more complex, although not necessarily slower. so those are just some tradeoffs I would keep in mind.

cs

How increasing maximum allowed (4000)

Hi All
i Have Var Type nvarchar in my TRIGGER
The maximum allowed (4000) i need (8000) or more
How can i do this
ThanksI just learned that you could try nvarchar(MAX) and than insert your data
using the ntext declaration. I know this can be done in SQL 2005 not sure
about SQL 2000. Else you could just use ntext in the database too.
-Mark
"Taha" <taha105@.hotmail.com> wrote in message
news:eKYGcsb6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Hi All
> i Have Var Type nvarchar in my TRIGGER
> The maximum allowed (4000) i need (8000) or more
> How can i do this
> Thanks
>
>|||Note that you cannot declare variables of type (N)TEXT in SQL Server 2000.
If you need access to more characters than declare two variables of type
NVARCHAR(4000) and perform your operations.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Mark Nijhof" <Mark@.Nijhof.com> wrote in message
news:%23kgux2b6FHA.1420@.TK2MSFTNGP09.phx.gbl...
>I just learned that you could try nvarchar(MAX) and than insert your data
>using the ntext declaration. I know this can be done in SQL 2005 not sure
>about SQL 2000. Else you could just use ntext in the database too.
> -Mark
> "Taha" <taha105@.hotmail.com> wrote in message
> news:eKYGcsb6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>|||Thank you for reply
i use SQL 2000. and when i try your id i get this message
"The text, ntext, and image data types are invalid for local variables."
i thenk dosnot work
any id
thanks
"Mark Nijhof" <Mark@.Nijhof.com> wrote in message
news:%23kgux2b6FHA.1420@.TK2MSFTNGP09.phx.gbl...
>I just learned that you could try nvarchar(MAX) and than insert your data
>using the ntext declaration. I know this can be done in SQL 2005 not sure
>about SQL 2000. Else you could just use ntext in the database too.
> -Mark
> "Taha" <taha105@.hotmail.com> wrote in message
> news:eKYGcsb6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>|||NVarChar is the unicode (2 bytes per char) version of the VarChar (1 byte
per char) data type. If you don't need unicode support, then VarChar
supports a maximum of 8000 chars.
"Taha" <taha105@.hotmail.com> wrote in message
news:eKYGcsb6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Hi All
> i Have Var Type nvarchar in my TRIGGER
> The maximum allowed (4000) i need (8000) or more
> How can i do this
> Thanks
>
>