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 much storage needed for bit datatypes?

Hi!

I'm investigating if it is an good idea to use some form of "bitmap index",

e.g. I have an normal table with 100-200 columns, but sometimes I just want to know:

"Is there an value in the cell" (...where col20 is not null)

So I will measure if I get some benifit, so If you tried this, let me hear, but otherwise see only the rest:

If I remember correctly SQL Server will group bit-columns in some way minimizing storage needs,

but my questions: how much storage is needed for an bit column:

1 or 2 bit (the second bit, because we can have three states: 0,1 and null)

Will 8 bit-columns need 1 byte or 2 byte?

I tried some tests with a number table (1mio rows), but It didn't give me the answer.

Best Regards

Bjorn

create table tmpbit (a bit)

create table tmpbit8 (a1 bit, a2 bit, a3 bit , a4 bit, a5 bit , a6 bit, a7 bit, a8 bit)

insert into tmpbit

select cast(1 as bit) from numbers

insert into tmpbit8

select cast(1 as bit) , cast(1 as bit) , cast(1 as bit) , cast(1 as bit) ,

cast(1 as bit) , cast(1 as bit) , cast(1 as bit) , cast(1 as bit)

from numbers

select object_id('tmpbit8') -- for input to dbcc

DBCC SHOWCONTIG scanning 'tmpbit' table...

Table: 'tmpbit' (1269891891); index ID: 0, database ID: 6

TABLE level scan performed.

- Pages Scanned................................: 1359

- Extents Scanned..............................: 171

- Extent Switches..............................: 170

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 99.42% [170:171]

- Extent Scan Fragmentation ...................: 1.75%

- Avg. Bytes Free per Page.....................: 1.8

- Avg. Page Density (full).....................: 99.98%

DBCC SHOWCONTIG scanning 'tmpbit8' table...

Table: 'tmpbit8' (1301892005); index ID: 0, database ID: 6

TABLE level scan performed.

- Pages Scanned................................: 1359

- Extents Scanned..............................: 171

- Extent Switches..............................: 170

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 99.42% [170:171]

- Extent Scan Fragmentation ...................: 0.58%

- Avg. Bytes Free per Page.....................: 1.8

- Avg. Page Density (full).....................: 99.98%

both tmp-tables uses 10,617 MB

If your table have 1-8 then the values are strored in 1 Byte. again 1-16 stored in 2 bytes...

ie,

CEILING(n number of BIT columns /8.0) Bytes will be occupied.

|||

Hi!

Yes, but how can db store null without using extra bit?

/Bjorn

|||It can't. In fact, the null bitmap is an entirely separate structure within the row. Inside Microsoft SQL Server 2005: The Storage Engine (Delaney) has a nice diagram on page 216, along with some explanations, but the main details are that the null bitmap lies after the fixed-length column data, but before the variable-length column data. Furthermore, in SQL Server 2005, a row always has a null bitmap, even if there are no nullable columns. The size of the bitmap is 1 byte per 8 columns, rounded up to the nearest byte.

how much storage does a null xml data type column use if...

1) it is in the same filegroup as the rest of the table?

2) it is stored in a filegroup separately from the rest of the table (I think this is allowed)?

moving this over to the database engine forum.sql

how many data can storage into sql ce

Hi,

i want to know how many data can storage into sql server compact edition. I've got a db into a pocket pc that has a table with about 2000 records inside; are they too records?

YOu are not limited in rows, you can store up to 4GB (which should be pretty enough for PocketDevices :-) )

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||but i think that a sql server ce on a pocket pc has some limitation, or not?|||

Please see this topic in SQL CE Books Online - http://msdn2.microsoft.com/en-us/library/ms172451.aspx

"The following table specifies the maximum size limitations for several database objects defined in Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) databases. "

|||ok