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 Max instance and database supported in SQL Server ?

How many Max instance and database supported in SQL Server 7 / 2000/ 2005.
Take a look at "capacity specifications-SQL Server" in the BOL
"Nagendra Rao" <Nagendra Rao@.discussions.microsoft.com> wrote in message
news:FF9FA3D5-EB0C-412E-AC13-889CE3FC7A9E@.microsoft.com...
> How many Max instance and database supported in SQL Server 7 / 2000/ 2005.
|||2000 it is 16
vinu
"Nagendra Rao" <Nagendra Rao@.discussions.microsoft.com> wrote in message
news:FF9FA3D5-EB0C-412E-AC13-889CE3FC7A9E@.microsoft.com...
> How many Max instance and database supported in SQL Server 7 / 2000/ 2005.
|||16 instances on 2000, 50 on sql 2005 unclusted 25
clustered --http://www.microsoft.com/technet/technetmag/issues/2006/03/SQLQA/?related=y
For number of databases it is
32,767 --http://msdn2.microsoft.com/en-us/library/ms143432.aspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nagendra Rao" <Nagendra Rao@.discussions.microsoft.com> wrote in message
news:FF9FA3D5-EB0C-412E-AC13-889CE3FC7A9E@.microsoft.com...
> How many Max instance and database supported in SQL Server 7 / 2000/ 2005.

How many Max instance and database supported in SQL Server ?

How many Max instance and database supported in SQL Server 7 / 2000/ 2005.Take a look at "capacity specifications-SQL Server" in the BOL
"Nagendra Rao" <Nagendra Rao@.discussions.microsoft.com> wrote in message
news:FF9FA3D5-EB0C-412E-AC13-889CE3FC7A9E@.microsoft.com...
> How many Max instance and database supported in SQL Server 7 / 2000/ 2005.|||2000 it is 16
vinu
"Nagendra Rao" <Nagendra Rao@.discussions.microsoft.com> wrote in message
news:FF9FA3D5-EB0C-412E-AC13-889CE3FC7A9E@.microsoft.com...
> How many Max instance and database supported in SQL Server 7 / 2000/ 2005.|||16 instances on 2000, 50 on sql 2005 unclusted 25
clustered --http://www.microsoft.com/technet/technetmag/issues/2006/03/SQLQA/?related=y
For number of databases it is
32,767 --http://msdn2.microsoft.com/en-us/library/ms143432.aspx
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nagendra Rao" <Nagendra Rao@.discussions.microsoft.com> wrote in message
news:FF9FA3D5-EB0C-412E-AC13-889CE3FC7A9E@.microsoft.com...
> How many Max instance and database supported in SQL Server 7 / 2000/ 2005.

How many Max instance and database supported in SQL Server ?

How many Max instance and database supported in SQL Server 7 / 2000/ 2005.Take a look at "capacity specifications-SQL Server" in the BOL
"Nagendra Rao" <Nagendra Rao@.discussions.microsoft.com> wrote in message
news:FF9FA3D5-EB0C-412E-AC13-889CE3FC7A9E@.microsoft.com...
> How many Max instance and database supported in SQL Server 7 / 2000/ 2005.|||2000 it is 16
vinu
"Nagendra Rao" <Nagendra Rao@.discussions.microsoft.com> wrote in message
news:FF9FA3D5-EB0C-412E-AC13-889CE3FC7A9E@.microsoft.com...
> How many Max instance and database supported in SQL Server 7 / 2000/ 2005.|||16 instances on 2000, 50 on sql 2005 unclusted 25
clustered --http://www.microsoft.com/technet/technetmag/issues/2006/03/SQLQA
/?related=y
For number of databases it is
32,767 --http://msdn2.microsoft.com/en-us/library/ms143432.aspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nagendra Rao" <Nagendra Rao@.discussions.microsoft.com> wrote in message
news:FF9FA3D5-EB0C-412E-AC13-889CE3FC7A9E@.microsoft.com...
> How many Max instance and database supported in SQL Server 7 / 2000/ 2005.

How Many Columns Are Too Many?

Greetings,
I was just wondering if anyone can tell me what the max number of columns
you should put into a table is? I am writing an ASP.NET API that is using a
preexisting SQL Server backend. One of the existing tables is over 320
columns long. The servers are dual processor high end machines and the
traffic will be relatively low right now (about 5 users off and on a few
times every day). The test API I wrote works great with little or no pauses
but I am a little concerned about future traffic requirements if this API
takes off and there are consideraly more users.
Any comments and/or suggestions will be greatly appreciated.
TIA
Dennis Pike
Chief Technology Engineer
ErgoSoft Solutions
this question is a bit difficult to answer.
Generally you want to normalize your database but we need to know what it is
you're doing....
Greg Jackson
PDX, Oregon
|||BOL Lists the maximum "Columns per base table" at 1,024.
As for traffic, make sure that you are specifying column names explicitly in
your SELECT statements (no SELECT *) to ensure that you are only pulling
back the columns you actually need. You might even consider using Stored
Proc's if you have some very commonly used queries in your app.
"nomadic1" <nomadic1@.discussions.microsoft.com> wrote in message
news:984D499A-A30A-4E2E-B998-B8C43B562993@.microsoft.com...
> Greetings,
> I was just wondering if anyone can tell me what the max number of columns
> you should put into a table is? I am writing an ASP.NET API that is using
> a
> preexisting SQL Server backend. One of the existing tables is over 320
> columns long. The servers are dual processor high end machines and the
> traffic will be relatively low right now (about 5 users off and on a few
> times every day). The test API I wrote works great with little or no
> pauses
> but I am a little concerned about future traffic requirements if this API
> takes off and there are consideraly more users.
> Any comments and/or suggestions will be greatly appreciated.
> TIA
> --
> Dennis Pike
> Chief Technology Engineer
> ErgoSoft Solutions

How Many Columns Are Too Many?

Greetings,
I was just wondering if anyone can tell me what the max number of columns
you should put into a table is? I am writing an ASP.NET API that is using a
preexisting SQL Server backend. One of the existing tables is over 320
columns long. The servers are dual processor high end machines and the
traffic will be relatively low right now (about 5 users off and on a few
times every day). The test API I wrote works great with little or no pauses
but I am a little concerned about future traffic requirements if this API
takes off and there are consideraly more users.
Any comments and/or suggestions will be greatly appreciated.
TIA
--
Dennis Pike
Chief Technology Engineer
ErgoSoft Solutionsthis question is a bit difficult to answer.
Generally you want to normalize your database but we need to know what it is
you're doing....
Greg Jackson
PDX, Oregon|||BOL Lists the maximum "Columns per base table" at 1,024.
As for traffic, make sure that you are specifying column names explicitly in
your SELECT statements (no SELECT *) to ensure that you are only pulling
back the columns you actually need. You might even consider using Stored
Proc's if you have some very commonly used queries in your app.
"nomadic1" <nomadic1@.discussions.microsoft.com> wrote in message
news:984D499A-A30A-4E2E-B998-B8C43B562993@.microsoft.com...
> Greetings,
> I was just wondering if anyone can tell me what the max number of columns
> you should put into a table is? I am writing an ASP.NET API that is using
> a
> preexisting SQL Server backend. One of the existing tables is over 320
> columns long. The servers are dual processor high end machines and the
> traffic will be relatively low right now (about 5 users off and on a few
> times every day). The test API I wrote works great with little or no
> pauses
> but I am a little concerned about future traffic requirements if this API
> takes off and there are consideraly more users.
> Any comments and/or suggestions will be greatly appreciated.
> TIA
> --
> Dennis Pike
> Chief Technology Engineer
> ErgoSoft Solutions

How Many Columns Are Too Many?

Greetings,
I was just wondering if anyone can tell me what the max number of columns
you should put into a table is? I am writing an ASP.NET API that is using a
preexisting SQL Server backend. One of the existing tables is over 320
columns long. The servers are dual processor high end machines and the
traffic will be relatively low right now (about 5 users off and on a few
times every day). The test API I wrote works great with little or no pauses
but I am a little concerned about future traffic requirements if this API
takes off and there are consideraly more users.
Any comments and/or suggestions will be greatly appreciated.
TIA
--
Dennis Pike
Chief Technology Engineer
ErgoSoft Solutionsthis question is a bit difficult to answer.
Generally you want to normalize your database but we need to know what it is
you're doing....
Greg Jackson
PDX, Oregon|||BOL Lists the maximum "Columns per base table" at 1,024.
As for traffic, make sure that you are specifying column names explicitly in
your SELECT statements (no SELECT *) to ensure that you are only pulling
back the columns you actually need. You might even consider using Stored
Proc's if you have some very commonly used queries in your app.
"nomadic1" <nomadic1@.discussions.microsoft.com> wrote in message
news:984D499A-A30A-4E2E-B998-B8C43B562993@.microsoft.com...
> Greetings,
> I was just wondering if anyone can tell me what the max number of columns
> you should put into a table is? I am writing an ASP.NET API that is using
> a
> preexisting SQL Server backend. One of the existing tables is over 320
> columns long. The servers are dual processor high end machines and the
> traffic will be relatively low right now (about 5 users off and on a few
> times every day). The test API I wrote works great with little or no
> pauses
> but I am a little concerned about future traffic requirements if this API
> takes off and there are consideraly more users.
> Any comments and/or suggestions will be greatly appreciated.
> TIA
> --
> Dennis Pike
> Chief Technology Engineer
> ErgoSoft Solutions

How large is 4 GB

Hi, All.

New to SQL. Just downloaded SQL server Express 2005, Web doc says its max limit is 4GB,

Need to know how large a 4GB can hold. The data shall be like holding company info, say: company names, locations, line of business, phone, etc. It needs full-text search on it.

Is this Express is the choice to go?

P.S. I might duplicate the posting, just to get wide_based input

Thanks.

Hi Larry,

I suppose the question is: how many rows of company data do you wish to store?

I can type a paragraph on how to perform some capacity planning (rows+index etc) but I don't think this is really what you're after...

If you really want to get a quick and dirty idea, why not build a few tables with the columns you want to record (company name etc), and insert a few dozen rows with some basic indexes? From this, I think you'll have a better idea. Then, when you want to get detailed, come back and post again :)

Cheers

Rob

|||

Thanks Robert

I might just do that. Concern is if SQL is worth time to learn if I take SQL server, since eventually I need a language tool that can analyze the data, (and the users statistics as well) not just for query. 2nd concern is if 4GB can handle scaling.

SQL is not analytical tool as Focus, as stated by en.wikipedia.org site page, which puts Focue as an only both analytical and query tool, but not SQL.

Any comments on that?

|||

Hi Larry,

To address your concerns over the 4gb size limit of MSSQL 2005 express: if and when the database size of performance reaches the limites imposed by express, you can always upsize to either Workgroup, Standard or Enterprise edition. Please refer to http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx for a feature comparision between the editions.

I'm not familiar with Focus; my only comment here would be that MSSQL is a DBMS, not an analysis tool. That said however, I would defintely have a look at Analysis Services (a componenent of SQl Server) if it is primarily OLAP that you're interested in.

If you did decide to learn MSSQL, I think you would find your skills to be portable. Yes, microsoft has augmented the ansi SQL standard with extensions (T-SQL), but the core implementation of sql within SQL Server is completely ansi compliant (especially as of 2005).

There is also the little point of SQL Server being the premier DBMS on the market...

Cheers

Rob

|||

Larry, 4 GB is large enough for most applications. It is certainly not enough for data warehousing (we are talking 50 to 500 GB here, or even more) or for ERP systems (we are talking 5 to 25 GB here, typical cases). I have seen many applications with only around 30 to 300 MB database size, typically those applications which are developed internally within the company, or those applications which started as Access databases.
I would certainly advise you to try SQL Express. Without trying you won't know what it can or can not do. It would take about 30 minutes to install and within a few hours you will understand the taste of it. As for the analytical side, as Robert mentioned, there is Analysis Services.
Vincent

|||

Robert Varga, Vinival, Hi.

These are the kind of answers that can put me on a perspective on SQL as my choice. I appreciate it.

Nice One!

how large is 4 GB

Hi, All.

New to SQL. Just downloaded SQL server Express 2005, Web doc says its max limit is 4GB,

Need to know how large a 4GB can hold. The data shall be like holding company info, say: company names, locations, line of business, phone, etc. It needs full-text search on it.

Is this Express is the choice to go?

4 GB is a LOT. First of all, it's 4294967296 characters. But it's compressed in the database, so it's actually more.

|||

My concern is, it is web based, once start running on web, it just might soon reach the limit. I've never done database before./(or serious programming for that matter.). So need to know a clue for a comparison. say, for an example, how many books of yellow-page-like data type that a 4 GB of MS SQL SERVER Express can hold?

Thanks

|||

Honestly, you're worrying about nothing. Did you see how big the number was that I quoted ? It won't be an issue.

|||

Really?

BTW 4GB is 4 gigabyte, which equal to 4 billion characters, if character set is one byte wide. If I go international, I need cut that number in half.

|||

Yeah, I know that unicode is half that, but I didn't want to muddy the waters. If it's 4 billion exactly, that's not a real gigabyte. Whatever.

Either way, I still think you're worried over nothing. Use SQL Express, and if you run into trouble, migrate to SQL Server. Don't worry about stuff before you need to - 4 gig is a lot of data.

How is 4GB Max Database size applied?

Is the 4GB Database size calculated by totalling up the size of all the databases in an instance OR against each individual DB in an instance (e.g. you can have an unlimited number of DB each up to 4GB in size)?
TIA
Antony

Hi Antony,

It is calculated based on individual databases and includes only user data. You can have as many 4 GB database as your HD allows and as is reasonable maintainable within the other Express limits of 1 CPU and 1 GB of memory.

Mike

|||Whats is the size of the other version of sql 2005

How get Fist Value of tabale

How get Fist Value of tabale

I know how to get min, max value of table I wont Fist Value of tabale PL Help Me

Did you try the First function in Reporting Services ?

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/da44d746-c75d-465a-81ea-20bc0fb3c74f.htm

Jens k. Suessmeyer.

http://www.sqlserver2005.de