Hi i have var which has more than 8000 characters

Hi i heard that in sql server 2005 they introduced MAX to replace text data
type like strtemp varchar(MAX)... i have sql server 2005 and i am trying to
use it but does seem like working ... i guess the only reason is... my
database server still on sql server 2000 but on client i have sql server 200
5
where i am creating store procs tables view etcsss.
kindly any one can tell me how to solve the problem more than 8000 ... thank
samjad,
Can you tell us what are you trying to accomplish?.
AMB
"amjad" wrote:
> Hi i heard that in sql server 2005 they introduced MAX to replace text dat
a
> type like strtemp varchar(MAX)... i have sql server 2005 and i am trying t
o
> use it but does seem like working ... i guess the only reason is... my
> database server still on sql server 2000 but on client i have sql server 2
005
> where i am creating store procs tables view etcsss.
> kindly any one can tell me how to solve the problem more than 8000 ... thanks[/col
or]|||Hi i have two database one table called source and the other is dest...
dest is identifical of source table... both table has 104 fields when their
changed in source table then i wrote a script to track the changes and updat
e
the dest table... their is too many fields in both table like i am writing
dynamic sql query
first i create a cursor which has columnsname in while loop i do some thing
like
SET @.SQL='SELECT ' + @.DestID + ' FROM ' + @.Source_Table + 'INNER JOIN ' +
@.Full_DesTable_Name + ' ON ' + @.SourceID + ' = ' +
@.DestID + 'Where ('
fetch next from cur into @.columnname
while
begin
set @.sql=@.sql + @.Source_Table + '.[' + @.columnname + ']<> ' +
@.Full_DesTable_Name + '.[' + @.columnname + '] Or '
fetch next from cur into @.columnname
end
due to large amount of columns and each columns name is more than 80
character produce a larger string which in my case is 12000
if i dont do that method i have to create view to get changed fields ...in
which i have to set up each field in source to each field in destination mea
n
i have to write a very large where clause which is not possible... this
store proc create a sql query for me where i get the column info..... and
then put in that string in such a way which generate ...
ok i have done this method with two varchar(8000) variable but if i can do
that in one variable that will be great thanks
"Alejandro Mesa" wrote:
> amjad,
> Can you tell us what are you trying to accomplish?.
>
> AMB
> "amjad" wrote:
>|||This frustrates me at times as well. One idea I've had, but have never
put into practice is to store my pieces of my statements as rows in a
temporary table, then have some dynamic sql to build up the dynamic sql
in the table into a format exec (@.sql1 + @.sql2... etc.) then run that,
but at this point it's getting silly, so I've never actually tried it.
It might make for quite a re-usable bit of code though ;)
Cheers
Will|||amjad,
May be functions "checksum" or "binary_checksum" can help you.
Example:
use northwind
go
create table t1 (
c1 int primary key,
c2 char(1)
)
go
create table t2 (
c1 int primary key,
c2 char(1)
)
go
create view v1
as
select c1, c2, binary_checksum(*) as c3
from t1
go
create view v2
as
select c1, c2, binary_checksum(*) as c3
from t2
go
insert into t1 values(1, 'a')
insert into t1 values(2, 'b')
insert into t1 values(3, 'A')
insert into t2 values(1, 'a')
insert into t2 values(2, 'a')
insert into t2 values(3, 'a')
select v1.*, v2.*
from v1 inner join v2
on v1.c1 = v2.c1
where v1.c3 != v2.c3
go
drop view v1, v2
drop table t1, t2
go
I can not assure that this method is reliable.
AMB
"amjad" wrote:
> Hi i have two database one table called source and the other is dest...
> dest is identifical of source table... both table has 104 fields when the
ir
> changed in source table then i wrote a script to track the changes and upd
ate
> the dest table... their is too many fields in both table like i am writing
> dynamic sql query
> first i create a cursor which has columnsname in while loop i do some thin
g
> like
> SET @.SQL='SELECT ' + @.DestID + ' FROM ' + @.Source_Table + 'INNER JOIN ' +
> @.Full_DesTable_Name + ' ON ' + @.SourceID + ' = ' +
> @.DestID + 'Where ('
> fetch next from cur into @.columnname
> while
> begin
> set @.sql=@.sql + @.Source_Table + '.[' + @.columnname + ']<> ' +
> @.Full_DesTable_Name + '.[' + @.columnname + '] Or '
> fetch next from cur into @.columnname
> end
> due to large amount of columns and each columns name is more than 80
> character produce a larger string which in my case is 12000
>
> if i dont do that method i have to create view to get changed fields ...i
n
> which i have to set up each field in source to each field in destination m
ean
> i have to write a very large where clause which is not possible... this
> store proc create a sql query for me where i get the column info..... and
> then put in that string in such a way which generate ...
> ok i have done this method with two varchar(8000) variable but if i can do
> that in one variable that will be great thanks
> "Alejandro Mesa" wrote:
>

Help-Can I use 4 GB memory by SQL Server2000?

There is a Server with 4G Memory. I installed a SQL Server 2000 standard
version on it.
I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
other version because i don't want to take this risk.
Thank you very much.
True. Standard edition is limited to 2GB
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Damon" <Damon@.china.com> wrote in message
news:3YChh.69507$YV4.69275@.edtnps89...
> There is a Server with 4G Memory. I installed a SQL Server 2000 standard
> version on it.
> I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
> How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
> other version because i don't want to take this risk.
> Thank you very much.
>
|||Thank you Kevin.
Is there any solution to use 4 GB memory by sql2000?
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:uL6CwcuIHHA.4760@.TK2MSFTNGP03.phx.gbl...
> True. Standard edition is limited to 2GB
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Damon" <Damon@.china.com> wrote in message
> news:3YChh.69507$YV4.69275@.edtnps89...
>
|||Upgrade to Enterprise Edition.
Configuration -Maximum Capacity Specifications
2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Damon" <Damon@.china.com> wrote in message
news:cuDhh.69523$YV4.18832@.edtnps89...
> Thank you Kevin.
> Is there any solution to use 4 GB memory by sql2000?
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:uL6CwcuIHHA.4760@.TK2MSFTNGP03.phx.gbl...
>
|||Damon (Damon@.china.com) writes:
> Thank you Kevin.
> Is there any solution to use 4 GB memory by sql2000?
Upgrade to Enterprise Edition of SQL 2000.
Which is quite more expensive than moving to SQL 2005 Standard, I believe.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989DEE520DF4DYazorman@.127.0.0.1...
> Damon (Damon@.china.com) writes:
> Upgrade to Enterprise Edition of SQL 2000.
> Which is quite more expensive than moving to SQL 2005 Standard, I believe.
Would a "Named Instance" on SQL 2000 Standard be able to use most of the
remaining half of the 4GB? When I expirimented with named instances back in
2001 - and decided not to use them in a production environment - I only had
2GB in the server.
Even if possible I'm not sure this would be a good solution, just wondering
what the behavior is.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The OS will require some memory I leave 512 MB as a minimum. The remaining
memory could be divided between the various instances of SQL Server,
equally, or one instance getting 2GB and the other only 1.5GB, etc.
You would need to use the Server setting for MAX Server Memory on each
instance.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Russ Rose" <russrose@.hotmail.com> wrote in message
news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns989DEE520DF4DYazorman@.127.0.0.1...
> Would a "Named Instance" on SQL 2000 Standard be able to use most of the
> remaining half of the 4GB? When I expirimented with named instances back
> in 2001 - and decided not to use them in a production environment - I only
> had 2GB in the server.
> Even if possible I'm not sure this would be a good solution, just
> wondering what the behavior is.
>
>
|||"Russ Rose" <russrose@.hotmail.com> wrote in message
news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns989DEE520DF4DYazorman@.127.0.0.1...
> Would a "Named Instance" on SQL 2000 Standard be able to use most of the
> remaining half of the 4GB? When I expirimented with named instances back
> in 2001 - and decided not to use them in a production environment - I only
> had 2GB in the server.
Note for say a website, as I recall, you need a CPU license for each
physical CPU in the machine for each instance (in Standard).
However, this probably won't necessarily help. It depends on the OS.
If you're running Windows 2000 Standard, you're limited to 4 GB of physical
RAM anyway.
Now, 2 gig of physical RAM can be given to a process (3 gig if compiled with
the right options, but that raises other issues here.)
So, if you have 5 gig of physical RAM and an OS that can address all of
that, in theory this could support 2 processes each using 2 gig of physical
RAM with 1 gig reserved for the OS.
If you have less memory, obviously this won't work as well.
Quite honestly, I think your best bet if you really need the memory (and
while I'm always a fan of more memory, sometimes it's just not worth it) is
to move to SQL 2005.

> Even if possible I'm not sure this would be a good solution, just
> wondering what the behavior is.
>
>
|||Thank you every body!
Now, I am going to move to SQL 2005.
Merry Christmas.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:MdRhh.917$yx6.284@.newsread2.news.pas.earthlin k.net...
> "Russ Rose" <russrose@.hotmail.com> wrote in message
> news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...
>
> Note for say a website, as I recall, you need a CPU license for each
> physical CPU in the machine for each instance (in Standard).
> However, this probably won't necessarily help. It depends on the OS.
> If you're running Windows 2000 Standard, you're limited to 4 GB of
> physical RAM anyway.
> Now, 2 gig of physical RAM can be given to a process (3 gig if compiled
> with the right options, but that raises other issues here.)
> So, if you have 5 gig of physical RAM and an OS that can address all of
> that, in theory this could support 2 processes each using 2 gig of
> physical RAM with 1 gig reserved for the OS.
> If you have less memory, obviously this won't work as well.
> Quite honestly, I think your best bet if you really need the memory (and
> while I'm always a fan of more memory, sometimes it's just not worth it)
> is to move to SQL 2005.
>
>

Help-Can I use 4 GB memory by SQL Server2000?

There is a Server with 4G Memory. I installed a SQL Server 2000 standard
version on it.
I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
other version because i don't want to take this risk.
Thank you very much.True. Standard edition is limited to 2GB
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Damon" <Damon@.china.com> wrote in message
news:3YChh.69507$YV4.69275@.edtnps89...
> There is a Server with 4G Memory. I installed a SQL Server 2000 standard
> version on it.
> I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
> How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
> other version because i don't want to take this risk.
> Thank you very much.
>|||Thank you Kevin.
Is there any solution to use 4 GB memory by sql2000?
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:uL6CwcuIHHA.4760@.TK2MSFTNGP03.phx.gbl...
> True. Standard edition is limited to 2GB
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Damon" <Damon@.china.com> wrote in message
> news:3YChh.69507$YV4.69275@.edtnps89...
>|||Upgrade to Enterprise Edition.
Configuration -Maximum Capacity Specifications
2000
http://msdn.microsoft.com/library/d...br />
8dbn.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Damon" <Damon@.china.com> wrote in message
news:cuDhh.69523$YV4.18832@.edtnps89...
> Thank you Kevin.
> Is there any solution to use 4 GB memory by sql2000?
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:uL6CwcuIHHA.4760@.TK2MSFTNGP03.phx.gbl...
>|||Damon (Damon@.china.com) writes:
> Thank you Kevin.
> Is there any solution to use 4 GB memory by sql2000?
Upgrade to Enterprise Edition of SQL 2000.
Which is quite more expensive than moving to SQL 2005 Standard, I believe.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989DEE520DF4DYazorman@.127.0.0.1...
> Damon (Damon@.china.com) writes:
> Upgrade to Enterprise Edition of SQL 2000.
> Which is quite more expensive than moving to SQL 2005 Standard, I believe.
Would a "Named Instance" on SQL 2000 Standard be able to use most of the
remaining half of the 4GB? When I expirimented with named instances back in
2001 - and decided not to use them in a production environment - I only had
2GB in the server.
Even if possible I'm not sure this would be a good solution, just wondering
what the behavior is.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||The OS will require some memory I leave 512 MB as a minimum. The remaining
memory could be divided between the various instances of SQL Server,
equally, or one instance getting 2GB and the other only 1.5GB, etc.
You would need to use the Server setting for MAX Server Memory on each
instance.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Russ Rose" <russrose@.hotmail.com> wrote in message
news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns989DEE520DF4DYazorman@.127.0.0.1...
> Would a "Named Instance" on SQL 2000 Standard be able to use most of the
> remaining half of the 4GB? When I expirimented with named instances back
> in 2001 - and decided not to use them in a production environment - I only
> had 2GB in the server.
> Even if possible I'm not sure this would be a good solution, just
> wondering what the behavior is.
>
>|||"Russ Rose" <russrose@.hotmail.com> wrote in message
news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns989DEE520DF4DYazorman@.127.0.0.1...
> Would a "Named Instance" on SQL 2000 Standard be able to use most of the
> remaining half of the 4GB? When I expirimented with named instances back
> in 2001 - and decided not to use them in a production environment - I only
> had 2GB in the server.
Note for say a website, as I recall, you need a CPU license for each
physical CPU in the machine for each instance (in Standard).
However, this probably won't necessarily help. It depends on the OS.
If you're running Windows 2000 Standard, you're limited to 4 GB of physical
RAM anyway.
Now, 2 gig of physical RAM can be given to a process (3 gig if compiled with
the right options, but that raises other issues here.)
So, if you have 5 gig of physical RAM and an OS that can address all of
that, in theory this could support 2 processes each using 2 gig of physical
RAM with 1 gig reserved for the OS.
If you have less memory, obviously this won't work as well.
Quite honestly, I think your best bet if you really need the memory (and
while I'm always a fan of more memory, sometimes it's just not worth it) is
to move to SQL 2005.

> Even if possible I'm not sure this would be a good solution, just
> wondering what the behavior is.
>
>|||Lines: 71
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: 68.145.111.181
X-Trace: edtnps90 1166550467 68.145.111.181 (Tue, 19 Dec 2006 10:47:47 MST)
NNTP-Posting-Date: Tue, 19 Dec 2006 10:47:47 MST
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:22144
Thank you every body!
Now, I am going to move to SQL 2005.
Merry Christmas.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:MdRhh.917$yx6.284@.newsread2.news.pas.earthlink.net...
> "Russ Rose" <russrose@.hotmail.com> wrote in message
> news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...
>
> Note for say a website, as I recall, you need a CPU license for each
> physical CPU in the machine for each instance (in Standard).
> However, this probably won't necessarily help. It depends on the OS.
> If you're running Windows 2000 Standard, you're limited to 4 GB of
> physical RAM anyway.
> Now, 2 gig of physical RAM can be given to a process (3 gig if compiled
> with the right options, but that raises other issues here.)
> So, if you have 5 gig of physical RAM and an OS that can address all of
> that, in theory this could support 2 processes each using 2 gig of
> physical RAM with 1 gig reserved for the OS.
> If you have less memory, obviously this won't work as well.
> Quite honestly, I think your best bet if you really need the memory (and
> while I'm always a fan of more memory, sometimes it's just not worth it)
> is to move to SQL 2005.
>
>

Help-Can I use 4 GB memory by SQL Server2000?

There is a Server with 4G Memory. I installed a SQL Server 2000 standard
version on it.
I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
other version because i don't want to take this risk.

Thank you very much.True. Standard edition is limited to 2GB

--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Damon" <Damon@.china.comwrote in message
news:3YChh.69507$YV4.69275@.edtnps89...

Quote:

Originally Posted by

There is a Server with 4G Memory. I installed a SQL Server 2000 standard
version on it.
I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
other version because i don't want to take this risk.
>
Thank you very much.
>

|||Thank you Kevin.
Is there any solution to use 4 GB memory by sql2000?

"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.comwrote in message
news:uL6CwcuIHHA.4760@.TK2MSFTNGP03.phx.gbl...

Quote:

Originally Posted by

True. Standard edition is limited to 2GB
>
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
>
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
>
>
"Damon" <Damon@.china.comwrote in message
news:3YChh.69507$YV4.69275@.edtnps89...

Quote:

Originally Posted by

>There is a Server with 4G Memory. I installed a SQL Server 2000 standard
>version on it.
>I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
>How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
>other version because i don't want to take this risk.
>>
>Thank you very much.
>>

>
>

|||Upgrade to Enterprise Edition.

Configuration -Maximum Capacity Specifications
2000
http://msdn.microsoft.com/library/d..._ar_ts_8dbn.asp
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

"Damon" <Damon@.china.comwrote in message
news:cuDhh.69523$YV4.18832@.edtnps89...

Quote:

Originally Posted by

Thank you Kevin.
Is there any solution to use 4 GB memory by sql2000?
>
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.comwrote in message
news:uL6CwcuIHHA.4760@.TK2MSFTNGP03.phx.gbl...

Quote:

Originally Posted by

>True. Standard edition is limited to 2GB
>>
>--
>Kevin Hill
>3NF Consulting
>http://www.3nf-inc.com/NewsGroups.htm
>>
>Real-world stuff I run across with SQL Server:
>http://kevin3nf.blogspot.com
>>
>>
>"Damon" <Damon@.china.comwrote in message
>news:3YChh.69507$YV4.69275@.edtnps89...

Quote:

Originally Posted by

>>There is a Server with 4G Memory. I installed a SQL Server 2000 standard
>>version on it.
>>I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
>>How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
>>other version because i don't want to take this risk.
>>>
>>Thank you very much.
>>>

>>
>>

>
>

|||Damon (Damon@.china.com) writes:

Quote:

Originally Posted by

Thank you Kevin.
Is there any solution to use 4 GB memory by sql2000?

Upgrade to Enterprise Edition of SQL 2000.

Which is quite more expensive than moving to SQL 2005 Standard, I believe.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns989DEE520DF4DYazorman@.127.0.0.1...

Quote:

Originally Posted by

Damon (Damon@.china.com) writes:

Quote:

Originally Posted by

>Thank you Kevin.
>Is there any solution to use 4 GB memory by sql2000?

>
Upgrade to Enterprise Edition of SQL 2000.
>
Which is quite more expensive than moving to SQL 2005 Standard, I believe.

Would a "Named Instance" on SQL 2000 Standard be able to use most of the
remaining half of the 4GB? When I expirimented with named instances back in
2001 - and decided not to use them in a production environment - I only had
2GB in the server.

Even if possible I'm not sure this would be a good solution, just wondering
what the behavior is.

Quote:

Originally Posted by

>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||The OS will require some memory I leave 512 MB as a minimum. The remaining
memory could be divided between the various instances of SQL Server,
equally, or one instance getting 2GB and the other only 1.5GB, etc.

You would need to use the Server setting for MAX Server Memory on each
instance.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

"Russ Rose" <russrose@.hotmail.comwrote in message
news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...

Quote:

Originally Posted by

>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns989DEE520DF4DYazorman@.127.0.0.1...

Quote:

Originally Posted by

>Damon (Damon@.china.com) writes:

Quote:

Originally Posted by

>>Thank you Kevin.
>>Is there any solution to use 4 GB memory by sql2000?

>>
>Upgrade to Enterprise Edition of SQL 2000.
>>
>Which is quite more expensive than moving to SQL 2005 Standard, I
>believe.

>
Would a "Named Instance" on SQL 2000 Standard be able to use most of the
remaining half of the 4GB? When I expirimented with named instances back
in 2001 - and decided not to use them in a production environment - I only
had 2GB in the server.
>
Even if possible I'm not sure this would be a good solution, just
wondering what the behavior is.
>

Quote:

Originally Posted by

>>
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>
>

|||"Russ Rose" <russrose@.hotmail.comwrote in message
news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...

Quote:

Originally Posted by

>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns989DEE520DF4DYazorman@.127.0.0.1...

Quote:

Originally Posted by

>Damon (Damon@.china.com) writes:

Quote:

Originally Posted by

>>Thank you Kevin.
>>Is there any solution to use 4 GB memory by sql2000?

>>
>Upgrade to Enterprise Edition of SQL 2000.
>>
>Which is quite more expensive than moving to SQL 2005 Standard, I
>believe.

>
Would a "Named Instance" on SQL 2000 Standard be able to use most of the
remaining half of the 4GB? When I expirimented with named instances back
in 2001 - and decided not to use them in a production environment - I only
had 2GB in the server.

Note for say a website, as I recall, you need a CPU license for each
physical CPU in the machine for each instance (in Standard).

However, this probably won't necessarily help. It depends on the OS.

If you're running Windows 2000 Standard, you're limited to 4 GB of physical
RAM anyway.

Now, 2 gig of physical RAM can be given to a process (3 gig if compiled with
the right options, but that raises other issues here.)

So, if you have 5 gig of physical RAM and an OS that can address all of
that, in theory this could support 2 processes each using 2 gig of physical
RAM with 1 gig reserved for the OS.

If you have less memory, obviously this won't work as well.

Quite honestly, I think your best bet if you really need the memory (and
while I'm always a fan of more memory, sometimes it's just not worth it) is
to move to SQL 2005.

Quote:

Originally Posted by

>
Even if possible I'm not sure this would be a good solution, just
wondering what the behavior is.
>

Quote:

Originally Posted by

>>
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>
>

|||Thank you every body!
Now, I am going to move to SQL 2005.

Merry Christmas.

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.comwrote in message
news:MdRhh.917$yx6.284@.newsread2.news.pas.earthlin k.net...

Quote:

Originally Posted by

>
"Russ Rose" <russrose@.hotmail.comwrote in message
news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...

Quote:

Originally Posted by

>>
>"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
>news:Xns989DEE520DF4DYazorman@.127.0.0.1...

Quote:

Originally Posted by

>>Damon (Damon@.china.com) writes:
>>>Thank you Kevin.
>>>Is there any solution to use 4 GB memory by sql2000?
>>>
>>Upgrade to Enterprise Edition of SQL 2000.
>>>
>>Which is quite more expensive than moving to SQL 2005 Standard, I
>>believe.

>>
>Would a "Named Instance" on SQL 2000 Standard be able to use most of the
>remaining half of the 4GB? When I expirimented with named instances back
>in 2001 - and decided not to use them in a production environment - I
>only had 2GB in the server.

>
>
Note for say a website, as I recall, you need a CPU license for each
physical CPU in the machine for each instance (in Standard).
>
However, this probably won't necessarily help. It depends on the OS.
>
If you're running Windows 2000 Standard, you're limited to 4 GB of
physical RAM anyway.
>
Now, 2 gig of physical RAM can be given to a process (3 gig if compiled
with the right options, but that raises other issues here.)
>
So, if you have 5 gig of physical RAM and an OS that can address all of
that, in theory this could support 2 processes each using 2 gig of
physical RAM with 1 gig reserved for the OS.
>
If you have less memory, obviously this won't work as well.
>
Quite honestly, I think your best bet if you really need the memory (and
while I'm always a fan of more memory, sometimes it's just not worth it)
is to move to SQL 2005.
>
>

Quote:

Originally Posted by

>>
>Even if possible I'm not sure this would be a good solution, just
>wondering what the behavior is.
>>

Quote:

Originally Posted by

>>>
>>--
>>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>>
>>Books Online for SQL Server 2005 at
>>http://www.microsoft.com/technet/pr...oads/books.mspx
>>Books Online for SQL Server 2000 at
>>http://www.microsoft.com/sql/prodin...ions/books.mspx

>>
>>

>
>

Help-Can I use 4 GB memory by SQL Server2000?

There is a Server with 4G Memory. I installed a SQL Server 2000 standard
version on it.
I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
other version because i don't want to take this risk.
Thank you very much.True. Standard edition is limited to 2GB
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Damon" <Damon@.china.com> wrote in message
news:3YChh.69507$YV4.69275@.edtnps89...
> There is a Server with 4G Memory. I installed a SQL Server 2000 standard
> version on it.
> I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
> How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
> other version because i don't want to take this risk.
> Thank you very much.
>|||Thank you Kevin.
Is there any solution to use 4 GB memory by sql2000?
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:uL6CwcuIHHA.4760@.TK2MSFTNGP03.phx.gbl...
> True. Standard edition is limited to 2GB
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Damon" <Damon@.china.com> wrote in message
> news:3YChh.69507$YV4.69275@.edtnps89...
>> There is a Server with 4G Memory. I installed a SQL Server 2000 standard
>> version on it.
>> I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
>> How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
>> other version because i don't want to take this risk.
>> Thank you very much.
>|||Upgrade to Enterprise Edition.
Configuration -Maximum Capacity Specifications
2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Damon" <Damon@.china.com> wrote in message
news:cuDhh.69523$YV4.18832@.edtnps89...
> Thank you Kevin.
> Is there any solution to use 4 GB memory by sql2000?
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:uL6CwcuIHHA.4760@.TK2MSFTNGP03.phx.gbl...
>> True. Standard edition is limited to 2GB
>> --
>> Kevin Hill
>> 3NF Consulting
>> http://www.3nf-inc.com/NewsGroups.htm
>> Real-world stuff I run across with SQL Server:
>> http://kevin3nf.blogspot.com
>>
>> "Damon" <Damon@.china.com> wrote in message
>> news:3YChh.69507$YV4.69275@.edtnps89...
>> There is a Server with 4G Memory. I installed a SQL Server 2000 standard
>> version on it.
>> I heard that SQLServer2000 could use only up to 2GB memory. Is it true?
>> How can I use those 4GB memory? I can not upgrade to SQL Server 2005 or
>> other version because i don't want to take this risk.
>> Thank you very much.
>>
>|||Damon (Damon@.china.com) writes:
> Thank you Kevin.
> Is there any solution to use 4 GB memory by sql2000?
Upgrade to Enterprise Edition of SQL 2000.
Which is quite more expensive than moving to SQL 2005 Standard, I believe.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989DEE520DF4DYazorman@.127.0.0.1...
> Damon (Damon@.china.com) writes:
>> Thank you Kevin.
>> Is there any solution to use 4 GB memory by sql2000?
> Upgrade to Enterprise Edition of SQL 2000.
> Which is quite more expensive than moving to SQL 2005 Standard, I believe.
Would a "Named Instance" on SQL 2000 Standard be able to use most of the
remaining half of the 4GB? When I expirimented with named instances back in
2001 - and decided not to use them in a production environment - I only had
2GB in the server.
Even if possible I'm not sure this would be a good solution, just wondering
what the behavior is.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||The OS will require some memory I leave 512 MB as a minimum. The remaining
memory could be divided between the various instances of SQL Server,
equally, or one instance getting 2GB and the other only 1.5GB, etc.
You would need to use the Server setting for MAX Server Memory on each
instance.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Russ Rose" <russrose@.hotmail.com> wrote in message
news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns989DEE520DF4DYazorman@.127.0.0.1...
>> Damon (Damon@.china.com) writes:
>> Thank you Kevin.
>> Is there any solution to use 4 GB memory by sql2000?
>> Upgrade to Enterprise Edition of SQL 2000.
>> Which is quite more expensive than moving to SQL 2005 Standard, I
>> believe.
> Would a "Named Instance" on SQL 2000 Standard be able to use most of the
> remaining half of the 4GB? When I expirimented with named instances back
> in 2001 - and decided not to use them in a production environment - I only
> had 2GB in the server.
> Even if possible I'm not sure this would be a good solution, just
> wondering what the behavior is.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||"Russ Rose" <russrose@.hotmail.com> wrote in message
news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns989DEE520DF4DYazorman@.127.0.0.1...
>> Damon (Damon@.china.com) writes:
>> Thank you Kevin.
>> Is there any solution to use 4 GB memory by sql2000?
>> Upgrade to Enterprise Edition of SQL 2000.
>> Which is quite more expensive than moving to SQL 2005 Standard, I
>> believe.
> Would a "Named Instance" on SQL 2000 Standard be able to use most of the
> remaining half of the 4GB? When I expirimented with named instances back
> in 2001 - and decided not to use them in a production environment - I only
> had 2GB in the server.
Note for say a website, as I recall, you need a CPU license for each
physical CPU in the machine for each instance (in Standard).
However, this probably won't necessarily help. It depends on the OS.
If you're running Windows 2000 Standard, you're limited to 4 GB of physical
RAM anyway.
Now, 2 gig of physical RAM can be given to a process (3 gig if compiled with
the right options, but that raises other issues here.)
So, if you have 5 gig of physical RAM and an OS that can address all of
that, in theory this could support 2 processes each using 2 gig of physical
RAM with 1 gig reserved for the OS.
If you have less memory, obviously this won't work as well.
Quite honestly, I think your best bet if you really need the memory (and
while I'm always a fan of more memory, sometimes it's just not worth it) is
to move to SQL 2005.
> Even if possible I'm not sure this would be a good solution, just
> wondering what the behavior is.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||Thank you every body!
Now, I am going to move to SQL 2005.
Merry Christmas.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:MdRhh.917$yx6.284@.newsread2.news.pas.earthlink.net...
> "Russ Rose" <russrose@.hotmail.com> wrote in message
> news:5_-dnWp8N8-s1BrYnZ2dnUVZ_u63nZ2d@.comcast.com...
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns989DEE520DF4DYazorman@.127.0.0.1...
>> Damon (Damon@.china.com) writes:
>> Thank you Kevin.
>> Is there any solution to use 4 GB memory by sql2000?
>> Upgrade to Enterprise Edition of SQL 2000.
>> Which is quite more expensive than moving to SQL 2005 Standard, I
>> believe.
>> Would a "Named Instance" on SQL 2000 Standard be able to use most of the
>> remaining half of the 4GB? When I expirimented with named instances back
>> in 2001 - and decided not to use them in a production environment - I
>> only had 2GB in the server.
>
> Note for say a website, as I recall, you need a CPU license for each
> physical CPU in the machine for each instance (in Standard).
> However, this probably won't necessarily help. It depends on the OS.
> If you're running Windows 2000 Standard, you're limited to 4 GB of
> physical RAM anyway.
> Now, 2 gig of physical RAM can be given to a process (3 gig if compiled
> with the right options, but that raises other issues here.)
> So, if you have 5 gig of physical RAM and an OS that can address all of
> that, in theory this could support 2 processes each using 2 gig of
> physical RAM with 1 gig reserved for the OS.
> If you have less memory, obviously this won't work as well.
> Quite honestly, I think your best bet if you really need the memory (and
> while I'm always a fan of more memory, sometimes it's just not worth it)
> is to move to SQL 2005.
>
>> Even if possible I'm not sure this would be a good solution, just
>> wondering what the behavior is.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>

Help: why SQL 2005 is slow?

Hi,
During the weekend, I heard that SQL Server 2000 is very slow with tables co
ntaining rows over millions. So I did some
tests with our new database on SQL Server 2005 Standard Edition. The machine
is Windows 2003 Standard Server box
with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID configuration. It
has drives: C: with 11GB free space
and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main table, Items
, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in varchar(2500). Th
ere is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the followings
:
========================================
==============
No. SQL Statement Tim
e
========================================
= =========
1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
----
-- --
2 SELECT COUNT(*) FROM Items 13 minute
s
----
-- --
3 ALTER TABLE Items
ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 hours 50 minute
s
----
-- --
4 SELECT COUNT(*) FROM Items 20 minute
s
----
-- --
5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
========================================
==============
The first 2 statements were run without primary key in the table. The statem
ents #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the performa
nce of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows?
2. Do I have to do something to improve the performance with such simple sta
tement when the number of rows>1 million or >10 millions?
3. Is SQL Server 2005 the right one to handle a database with such big table
, or should I consider DB2 9 or Oracle 10g?
Thank you
HongboHongbo wrote:
> Hi,
> During the weekend, I heard that SQL Server 2000 is very slow with
> tables containing rows over millions. So I did some
> tests with our new database on SQL Server 2005 Standard Edition. The
> machine is Windows 2003 Standard Server box
> with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID
> configuration. It has drives: C: with 11GB free space
> and D: with 169GB free space. SQL 2005 was installed on D: drive.
> The size of the database is 10GB containing 47 tables. The main table,
> Items, contains 96 columns and 30 millions rows.
> All columns are in varchar data type, and 2 of them are in
> varchar(2500). There is no any index setup in the table neither.
> The time consumed for some SQL statements with this table are the
> followings:
> ========================================
==============
> No. SQL
> Statement Time
> ========================================
= =========
> 1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
> ----
--
> --
> 2 SELECT COUNT(*) FROM Items 13
> minutes
> ----
--
> --
> 3 ALTER TABLE Items
> ADD rid INT PRIMARY KEY IDENTITY(1,1) 10
> hours 50 minutes
> ----
--
> --
> 4 SELECT COUNT(*) FROM Items 20
> minutes
> ----
--
> --
> 5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
> ========================================
==============
> The first 2 statements were run without primary key in the table. The
> statements #4 and #5 were run after "rid" was added as primary key.
> This is the first time I work with a database in such size. But the
> performance of SQL Server 2005 surprised me.
> Would you please tell me:
> 1. Is such performance normal with such number of rows?
> 2. Do I have to do something to improve the performance with such
> simple statement when the number of rows>1 million or >10 millions?
> 3. Is SQL Server 2005 the right one to handle a database with such big
> table, or should I consider DB2 9 or Oracle 10g?
> Thank you
> Hongbo
Do I read this right, you have 30 million rows an no indexes? I would
think about adding a few to help you out. SQL 2005 can handle that with
proper design and hardware.
I believe there is a tremendous amount of paging going on there to
accommodate your full table scans and on a single drive, hence your poor
performance.
Ryan Sanders
http://ryanlsanders.blogspot.com