We are a nonprofit organization needing an MSSQL server
able to pass the silly 2 gb limit. What is the cheapest
solution? The only computer which needs to access the
server is that running it.I am the IS Manager for a non-profit also. We were in the same boat, and the
way I got through this was to go to a site called http://www.techsoup.com.
At this site you can register your agency using your 501c3 and Charitable
Status number, then you can purchase software, including MS items, for VERY
cheap rates. I got SBS2k, 45 licenses, and Visio for $98.00. Only catch is
that you can only do it once every 2 years, so have a good plan of what you
need when you do it. Let me know if I can be of any help. Been in the NP
world for awhile and it does offer challenges.
"Brian Cody" <bjc9019@.rit.edu> wrote in message
news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> We are a nonprofit organization needing an MSSQL server
> able to pass the silly 2 gb limit. What is the cheapest
> solution? The only computer which needs to access the
> server is that running it.|||The cheapest option is to purchase Enterprise Edition using the Server + CAL
model with very few CALs.
I don't know why you consider the 2GB limit "silly". It cost Microsoft
millions of dollars to develop, test, tune, and support the wierd mechanisms
for getting around the x86 architectural limitations that lead to a 2GB user
virtual address space. It makes sense for them to charge for all the extra
work that benefits only a modest portion of the installed base. Its true
that in a few more years, as Itanium and/or AMD-64 dominate the server
space, that the 2GB limit will become arbitrary. And at that point it will
also make sense for Microsoft to change its packaging/licensing.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Brian Cody" <bjc9019@.rit.edu> wrote in message
news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> We are a nonprofit organization needing an MSSQL server
> able to pass the silly 2 gb limit. What is the cheapest
> solution? The only computer which needs to access the
> server is that running it.|||Why, specifically, do you need to surpass to 2GB limitiation, are your
queries running out of memory?
<op ed>
"> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm...."
sounds like someone coming from the public sector.
</op ed>
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> Sounds like someone is paid by Microsoft
> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm....
>
> "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > The cheapest option is to purchase Enterprise Edition using the Server +
> CAL
> > model with very few CALs.
> >
> > I don't know why you consider the 2GB limit "silly". It cost Microsoft
> > millions of dollars to develop, test, tune, and support the wierd
> mechanisms
> > for getting around the x86 architectural limitations that lead to a 2GB
> user
> > virtual address space. It makes sense for them to charge for all the
> extra
> > work that benefits only a modest portion of the installed base. Its
true
> > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > space, that the 2GB limit will become arbitrary. And at that point it
> will
> > also make sense for Microsoft to change its packaging/licensing.
> >
> > --
> > Hal Berenson, SQL Server MVP
> > True Mountain Group LLC
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||Brian
When I read this, I thought the 2GB limit you were referring to was the
database size restriction imposed by MSDE, but other people's posts seemed
to imply that you were talking about the 2GB memory limitation imposed in
all editions except for SQL Server Enterprise Edition running on Windows
2000.
Can you clarify which 'silly 2GB limit' you are concerned about?
Thanks
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Brian Cody" <bjc9019@.rit.edu> wrote in message
news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> We are a nonprofit organization needing an MSSQL server
> able to pass the silly 2 gb limit. What is the cheapest
> solution? The only computer which needs to access the
> server is that running it.|||Could you tell Kevin? LOL
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:%23gLLmkdXDHA.208@.tk2msftngp13.phx.gbl...
> Why, specifically, do you need to surpass to 2GB limitiation, are your
> queries running out of memory?
> <op ed>
> "> ...cost them millions of dollars to develop something that makes them
> > Billions of dollars. Hmmmm...."
> sounds like someone coming from the public sector.
> </op ed>
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
> news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> > Sounds like someone is paid by Microsoft
> >
> > ...cost them millions of dollars to develop something that makes them
> > Billions of dollars. Hmmmm....
> >
> >
> > "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> > news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > The cheapest option is to purchase Enterprise Edition using the Server
+
> > CAL
> > > model with very few CALs.
> > >
> > > I don't know why you consider the 2GB limit "silly". It cost
Microsoft
> > > millions of dollars to develop, test, tune, and support the wierd
> > mechanisms
> > > for getting around the x86 architectural limitations that lead to a
2GB
> > user
> > > virtual address space. It makes sense for them to charge for all the
> > extra
> > > work that benefits only a modest portion of the installed base. Its
> true
> > > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > > space, that the 2GB limit will become arbitrary. And at that point it
> > will
> > > also make sense for Microsoft to change its packaging/licensing.
> > >
> > > --
> > > Hal Berenson, SQL Server MVP
> > > True Mountain Group LLC
> > >
> > >
> > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > We are a nonprofit organization needing an MSSQL server
> > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > solution? The only computer which needs to access the
> > > > server is that running it.
> > >
> > >
> >
> >
>|||If you actually saw how many resources they staff to develop and maintain
the sql server product line you would understand that it costs them far more
than a few million dollars to get you this product. And at this point in
time I doubt if sql server is even turning a profit but I could be wrong.
--
Andrew J. Kelly
SQL Server MVP
"John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> Sounds like someone is paid by Microsoft
> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm....
>
> "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > The cheapest option is to purchase Enterprise Edition using the Server +
> CAL
> > model with very few CALs.
> >
> > I don't know why you consider the 2GB limit "silly". It cost Microsoft
> > millions of dollars to develop, test, tune, and support the wierd
> mechanisms
> > for getting around the x86 architectural limitations that lead to a 2GB
> user
> > virtual address space. It makes sense for them to charge for all the
> extra
> > work that benefits only a modest portion of the installed base. Its
true
> > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > space, that the 2GB limit will become arbitrary. And at that point it
> will
> > also make sense for Microsoft to change its packaging/licensing.
> >
> > --
> > Hal Berenson, SQL Server MVP
> > True Mountain Group LLC
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||hal started it
;)
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> Brian
> When I read this, I thought the 2GB limit you were referring to was the
> database size restriction imposed by MSDE, but other people's posts seemed
> to imply that you were talking about the 2GB memory limitation imposed in
> all editions except for SQL Server Enterprise Edition running on Windows
> 2000.
> Can you clarify which 'silly 2GB limit' you are concerned about?
> Thanks
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Brian Cody" <bjc9019@.rit.edu> wrote in message
> news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > We are a nonprofit organization needing an MSSQL server
> > able to pass the silly 2 gb limit. What is the cheapest
> > solution? The only computer which needs to access the
> > server is that running it.
>|||Started what? We still don't know what the original poster was asking about.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> hal started it
> ;)
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > Brian
> >
> > When I read this, I thought the 2GB limit you were referring to was the
> > database size restriction imposed by MSDE, but other people's posts
seemed
> > to imply that you were talking about the 2GB memory limitation imposed
in
> > all editions except for SQL Server Enterprise Edition running on Windows
> > 2000.
> >
> > Can you clarify which 'silly 2GB limit' you are concerned about?
> >
> > Thanks
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||started us on the tangent regarding memory instead of database size :)
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> Started what? We still don't know what the original poster was asking
about.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > hal started it
> >
> > ;)
> >
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > Brian
> > >
> > > When I read this, I thought the 2GB limit you were referring to was
the
> > > database size restriction imposed by MSDE, but other people's posts
> seemed
> > > to imply that you were talking about the 2GB memory limitation imposed
> in
> > > all editions except for SQL Server Enterprise Edition running on
Windows
> > > 2000.
> > >
> > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > >
> > > Thanks
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > We are a nonprofit organization needing an MSSQL server
> > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > solution? The only computer which needs to access the
> > > > server is that running it.
> > >
> > >
> >
> >
>|||Although I was once employed by Microsoft (and don't try to hide it...Google
searches reveal all) I am neither a current Microsoft employee nor should
anything I say be taken as representing Microsoft in any way.
The SQL Server product may make a billion dollars a year, but that's not the
question. The question is, how much incremental revenue does the millions
of dollars spent on this one feature generate?
I know your thought is a common one for people, but what Microsoft is doing
here is just good common business sense. Increased investment in any
product has to be justified by a return on that investment (ie, greater
revenue of sufficient size to generate greater profit). To generate the
revenue you either increase unit sales (volume), or you increase the value
you are offering and charge accordingly. One solution would be to only
invest your development resources on projects that you expect to generate
increased unit volume. In that case, features such as AWE support, failover
clusters, materialized views, support for more than 4 processors, etc. just
would never have been done. The resources would instead have been assigned
to things like adding VB stored procedures, more Access compatibility,
smaller footprint for downloads, etc. All good things, by the way. You
could invest in the features that don't offer a unit volume increase, put
them in the base product, and then raise the price for the base product.
But that penalizes all the people who don't need the additional features,
which means it penalizes most people, and could actually lower unit volume
resulting in an overall reduction in revenue. The third solution is to
create a premium product that is designed so that those who need the low
volume features are the ones who pay for them. Microsoft went with solution
#3. I guess there is a fourth solution, which is to increase the investment
but not increase revenue accordingly. This is called the "going out of
business" option. Many companies, particularly database companies, have
successfully executed on option #4.
The features in Enterprise Edition are generally those which are extremely
expensive to develop yet lead to a negligable increase in unit sales. This
is the case because those features primarily apply to low-volume
environments like high-end servers purchased, installed and maintained by
Enterprise IT organizations. Occasionally someone wants just one of the
features and doesn't really need the rest of Enterprise Edition, and then
the packaging doesn't seem to make sense. Well, for them I guess it
doesn't. No solution is going to make everyone happy. Any business tries
to maximize the applicability of their offerings to customers without
letting their costs get out of control. I hope that Microsoft has done this
with SQL Server, though certainly its packaging is not perfect for everyone.
One other interesting point. At the time that Microsoft made >2GB an
Enterprise Edition feature 1GB of memory was going for $100K or more and
thus clearly 3GB was a very low volume situation. Today 1GB goes for a few
hundred dollars and 3GB servers are mainstream. Hopefully Microsoft will
take this into account the next time it changes SQL Server packaging.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"John C. Harris, MPA" <harris1214@.tampabay.rr.com> wrote in message
news:ez2YRidXDHA.2360@.TK2MSFTNGP12.phx.gbl...
> Sounds like someone is paid by Microsoft
> ...cost them millions of dollars to develop something that makes them
> Billions of dollars. Hmmmm....
>
> "Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
> news:%23lzu2RdXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > The cheapest option is to purchase Enterprise Edition using the Server +
> CAL
> > model with very few CALs.
> >
> > I don't know why you consider the 2GB limit "silly". It cost Microsoft
> > millions of dollars to develop, test, tune, and support the wierd
> mechanisms
> > for getting around the x86 architectural limitations that lead to a 2GB
> user
> > virtual address space. It makes sense for them to charge for all the
> extra
> > work that benefits only a modest portion of the installed base. Its
true
> > that in a few more years, as Itanium and/or AMD-64 dominate the server
> > space, that the 2GB limit will become arbitrary. And at that point it
> will
> > also make sense for Microsoft to change its packaging/licensing.
> >
> > --
> > Hal Berenson, SQL Server MVP
> > True Mountain Group LLC
> >
> >
> > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > We are a nonprofit organization needing an MSSQL server
> > > able to pass the silly 2 gb limit. What is the cheapest
> > > solution? The only computer which needs to access the
> > > server is that running it.
> >
> >
>|||But are we sure that is a tangent?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:uhfZwpeXDHA.2568@.tk2msftngp13.phx.gbl...
> started us on the tangent regarding memory instead of database size :)
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> > Started what? We still don't know what the original poster was asking
> about.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > > hal started it
> > >
> > > ;)
> > >
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > > Brian
> > > >
> > > > When I read this, I thought the 2GB limit you were referring to was
> the
> > > > database size restriction imposed by MSDE, but other people's posts
> > seemed
> > > > to imply that you were talking about the 2GB memory limitation
imposed
> > in
> > > > all editions except for SQL Server Enterprise Edition running on
> Windows
> > > > 2000.
> > > >
> > > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > > >
> > > > Thanks
> > > >
> > > > --
> > > > HTH
> > > > --
> > > > Kalen Delaney
> > > > SQL Server MVP
> > > > www.SolidQualityLearning.com
> > > >
> > > >
> > > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > > We are a nonprofit organization needing an MSSQL server
> > > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > > solution? The only computer which needs to access the
> > > > > server is that running it.
> > > >
> > > >
> > >
> > >
> >
> >
>|||No, not yet. But I think your instinct was correct, let's see if Brian
responds.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uEdMlteXDHA.2392@.TK2MSFTNGP10.phx.gbl...
> But are we sure that is a tangent?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:uhfZwpeXDHA.2568@.tk2msftngp13.phx.gbl...
> > started us on the tangent regarding memory instead of database size :)
> >
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> > > Started what? We still don't know what the original poster was asking
> > about.
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > > > hal started it
> > > >
> > > > ;)
> > > >
> > > >
> > > > --
> > > > Kevin Connell, MCDBA
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > > > Brian
> > > > >
> > > > > When I read this, I thought the 2GB limit you were referring to
was
> > the
> > > > > database size restriction imposed by MSDE, but other people's
posts
> > > seemed
> > > > > to imply that you were talking about the 2GB memory limitation
> imposed
> > > in
> > > > > all editions except for SQL Server Enterprise Edition running on
> > Windows
> > > > > 2000.
> > > > >
> > > > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > > > >
> > > > > Thanks
> > > > >
> > > > > --
> > > > > HTH
> > > > > --
> > > > > Kalen Delaney
> > > > > SQL Server MVP
> > > > > www.SolidQualityLearning.com
> > > > >
> > > > >
> > > > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > > > We are a nonprofit organization needing an MSSQL server
> > > > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > > > solution? The only computer which needs to access the
> > > > > > server is that running it.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Kalen's right, he could have been talking about the 2GB database limit in
MSDE. But since he didn't mention MSDE, and this isn't the MSDE newsgroup,
I went with the memory limit as what he was asking about.
I happen to agree that the 2GB database limit in MSDE is silly. But getting
around that one is much easier and cheaper. Either split your data over
multiple databases in a single MSDE instance, or (in this case) spend under
a $1000 (even if you aren't a non-profit) to get Standard Edition + 5 CALs.
ATTENTION 501(c)(3)s: The cheapest way for you to get Microsoft software is
to find a Microsoft employee who donates to you and ask them to donate
software instead of cash. The employee can purchase the software at the
employee store at a dramatic discount, and donation to a 501(c)(3) is one of
the things they are allowed to do with that software. Microsoft will even
match the donation with additional software. I know charities whose entire
offices have been outfitted this way. So get your fundraising people moving
:-)
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OwS5OjeXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> Started what? We still don't know what the original poster was asking
about.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:#M15BfeXDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > hal started it
> >
> > ;)
> >
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:eQ$790dXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > > Brian
> > >
> > > When I read this, I thought the 2GB limit you were referring to was
the
> > > database size restriction imposed by MSDE, but other people's posts
> seemed
> > > to imply that you were talking about the 2GB memory limitation imposed
> in
> > > all editions except for SQL Server Enterprise Edition running on
Windows
> > > 2000.
> > >
> > > Can you clarify which 'silly 2GB limit' you are concerned about?
> > >
> > > Thanks
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Brian Cody" <bjc9019@.rit.edu> wrote in message
> > > news:034701c35d11$a5e1c610$a101280a@.phx.gbl...
> > > > We are a nonprofit organization needing an MSSQL server
> > > > able to pass the silly 2 gb limit. What is the cheapest
> > > > solution? The only computer which needs to access the
> > > > server is that running it.
> > >
> > >
> >
> >
>
Showing posts with label silly. Show all posts
Showing posts with label silly. Show all posts
Wednesday, March 7, 2012
Sunday, February 12, 2012
Char vs Varchar
Hi,
This question may sound silly,but please comment.
Please tell me a situation where char should be used and not varchar.
Let us assume that we are dealing with non unicode characters.
Well, I find varchar is always smarter than char, so why char?
Thanks!!
Rudrafixed length identifier fields? even though smart numbers are stupid.|||use CHAR(n) instead of VARCHAR(n) when n<4
consider using CHAR instead of VARCHAR when there's only one non-null VARCHAR in the table
what did you mean by "smarter" anyway?|||fixed length identifier fields? even though smart numbers are stupid.
Yea, I have seen those in fixed length identifier fields,any more use of char?
Well, in database designing which attributes are assigned as char?
If we don't use all the characters tehn its a mess...what are the best situation to use char? Plz comment...
It may seem a silly one but I think it has an important significance in database designing...:)
Thanks!!
Joydeep|||use CHAR(n) instead of VARCHAR(n) when n<4
Yea thats a very good point...:rolleyes:
and
consider using CHAR instead of VARCHAR when there's only one non-null VARCHAR in the table
that should be applied when and only when n<4.Isn't it?
ok,thank you for those info.
what did you mean by "smarter" anyway
by smarter I mean to say varchar though variable length does provide more efficient storage than char and also doesn't need any trim functions to compare...and many more are there...:)
Thanks!!
Joydeep|||yes, those are advantages for VARCHAR, good points
there really isn't any reason to have CHAR, when you think about it
i'm guessing it must be an historic relic from back in the days when databases were a lot less efficient handling VARCHARs|||i'm guessing it must be an historic relic from back in the days when databases were a lot less efficient handling VARCHARs
LOL,:D
rudy.ca... its cool and your pic too :)
Thanks again r937|||Oh heavens, there are lots of reasons for using the CHAR datatype. It is far more efficient when dealing with "character indicators" which are short, fixed length strings (like Y/N, M/F, etc). CHAR is also better for moving data back and forth between today's equipment and yesterday's equipment... It is practically impossible to deal with variable length columns in Z/OS, and many of us still have to deal with things like that.
In general, I prefer to use VARCHAR, but there are times and reasons to use CHAR, and I wouldn't want to be without it as a choice.
-PatP|||Oh heavens, there are lots of reasons for using the CHAR datatype. It is far more efficient when dealing with "character indicators" which are short, fixed length strings (like Y/N, M/F, etc). CHAR is also better for moving data back and forth between today's equipment and yesterday's equipment... It is practically impossible to deal with variable length columns in Z/OS, and many of us still have to deal with things like that.
-PatP
And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fields.:rolleyes:
Thanks Pat
Joydeep|||And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fields.:rolleyes:
Thanks Pat
Joydeep
I would say using CHAR in the zipcode is a good idea. I'm in Canada and we have postal codes that contain letters and numbers. Not only that, they are 6 characters long! I've seen some pretty bad e-commerce sites that wouldn't let me put in my address because their "zip code" field wouldn't let me enter the last character of my postal code.|||And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fieldsWell, the rule about using CHAR when length < 4 really applies to variable length strings less than four characters. Any time you have a fixed-length string (such as a five digit zip code or a nine digit social security number) CHAR is more appropriate and more efficient than VARCHAR.|||Well, the rule about using CHAR when length < 4 really applies to variable length strings less than four characters.i do believe i said that quite early in the thread :)
Any time you have a fixed-length string (such as a five digit zip code ...in this particular case VARCHAR(37) would've been way better, since it would allow you to store 9-digit (or 10 character, if you store the dash between the 5 digits and the 4) with absolutely no change to your database or your app
whereas with CHAR(5) for the zip code, you're screwed
another fine example of the one of the many benefits of VARCHAR
;)|||i do believe i said that quite early in the threadGreat advice is worth saying twice, eh?
in this particular case VARCHAR(37) would've been way better, since it would allow you to store 9-digit (or 10 character, if you store the dash between the 5 digits and the 4) with absolutely no change to your database or your appI'm a strong believer in storing ZIP and ZIP4 as separate fields. Pesky normalization habits of mine...|||I'm a strong believer in storing ZIP and ZIP4 as separate fields. Pesky normalization habits of mine...oh you silly man
okay, either you are consistent and silly, or else you are inconsistent and pragmatic, but please don't use "normalization" as an excuse for rationalize it either way
do you put house number in a separate column? i.e. not address1='123 sesame st' but address1_number='123', address1_street='sesame st'
do you put apartment/suite number in a separate column?
do you put zip code into a different table? after all, it's in a one-to-many relationship with addresses, so if a zip code changes, wouldn't you want to use a surrogate key instead?
and really, the 4-digit zip code suffix is functionally dependent on the 5-digit zip code prefix, so if you have those two columns side by side in the same row, what does that do for your normalization efforts?
address fields are NOTORIOUSLY the wrong example to use when discussing normalization
:)|||I always enjoy a goo d pedantic discussion...
Speaking of OS/390 z/OS
varchar is still painful in DB2 for the Client and/or COBOL Sprocs?
I'm about to launch a new dev project there and am in the middle of building the model soon...and they want free form description columns out the but at 300 bytes...
I need to talk them down to 255 to avoid LONG datatypes, but since they have so many, I was hoping to use varhcar.
I'll use char to make life easier, because I really don't care about DASD all that much...I just imagine speed will be impacted because of the misuse of the buffers...|||I'll use char to make life easier, because I really don't care about DASD all that much...I just imagine speed will be impacted because of the misuse of the buffers...
Is there anybody who cares for Domain Integrity? I think there should be some specific norms for database designing and normalization.Then the fuss about char and varchar implementation should have been gone..;)
Joydeep|||Is there anybody who cares for Domain Integrity? I think there should be some specific norms for database designing and normalization.Then the fuss about char and varchar implementation should have been gone..;)
Joydeep
Really now.
At the moment, I just want to slam the damn thing into production and make the deadline.
If this were SQL Server I was working on, it wouldn't be a problem.
I think I'm gonna go with:
, COL1 CHAR(255) NOT NULL WITH DEFAULT
I want my developers to be happy...actually I want my developers to be productive and accurate, i.e. I don't want code blowing up all over the place...
Ever seen an external COBOL Stored Procedure for DB2 OS/390?
And they've implemented some heavy duty Changeman procedures...they can't even compile code unless it's in a package...|||Really now.
At the moment, I just want to slam the damn thing into production and make the deadline.
If this were SQL Server I was working on, it wouldn't be a problem.
I think I'm gonna go with:
, COL1 CHAR(255) NOT NULL WITH DEFAULT
I want my developers to be happy...actually I want my developers to be productive and accurate, i.e. I don't want code blowing up all over the place...
Ever seen an external COBOL Stored Procedure for DB2 OS/390?
And they've implemented some heavy duty Changeman procedures...they can't even compile code unless it's in a package...
I agree with you for the above case.But don't you think database designing should involve a greater time than the rest of the jobs in production? Meeting deadlines is always a headache,but do you think we could sacrifice the dedicated time of designing for the sake of deadline only? :)
Joydeep|||do you put house number in a separate column? i.e. not address1='123 sesame st' but address1_number='123', address1_street='sesame st'I'd do it in a second if it didn't place an undo burden on the person entering the data, and if there were a simple method of ensuring data entry integrity. Many business processes (such as bulk mail discounts) require the address to be parsed and sorted in a specific manner.
do you put zip code into a different table? after all, it's in a one-to-many relationship with addresses, so if a zip code changes, wouldn't you want to use a surrogate key instead?I would absolutely do that if I had additional zip code attributes to store, such as demographics. Heck, I might do it just to ensure the validity of the zip codes that are entered. Yeah, it IS a one-to-many relationship, whether you choose to materialize the data or not.
and really, the 4-digit zip code suffix is functionally dependent on the 5-digit zip code prefix, so if you have those two columns side by side in the same row, what does that do for your normalization efforts?Yes, for stricty relationtional integrity zip4 codes should be a subtable of zip, and only the foreign key to zip4 should be stored in the adress table. But most applications do not require and cannot efficiently enforce these rules on the users. Asking the user to separte ZIP from ZIP4, however, is a pretty small request, and greatly facilitates grouping and sorting by zip code when zip4 is not required.
address fields are NOTORIOUSLY the wrong example to use when discussing normalizationAu contraire! The notorious unreliability of address fields make them a great cautionary tale against storing multiple attributes in a single column.|||Well, let's see.
The business hired a management team to develop the specs. I have been going through them and we are working out the kinks. The requirements are what they are, but the business keeps saying they don't know what they want specifically.
I'm ok with that, and I've already made modifications to tha model. It's pretty sound, but there are some definete kluges in there.
Also, I have the management team to blame.
I will still be worrying about performance and data integrity...but the integrity might take a hit in some places...
I'm gonna start a new thread|||Well, let's see.
The business hired a management team to develop the specs. I have been going through them and we are working out the kinks. The requirements are what they are, but the business keeps saying they don't know what they want specifically.
I'm ok with that, and I've already made modifications to the model. It's pretty sound, but there are some definete kluges in there.
Also, I have the management team to blame.
I'm gonna start a new thread
Yea, thats a common problem.They don't even clear up the functional areas properly.That makes the case complicated.Too many groups spoil the broth.
I think you need more patience than technical expertise here...:D
Good luck!!
Joydeep|||Trick is to not care so much
This question may sound silly,but please comment.
Please tell me a situation where char should be used and not varchar.
Let us assume that we are dealing with non unicode characters.
Well, I find varchar is always smarter than char, so why char?
Thanks!!
Rudrafixed length identifier fields? even though smart numbers are stupid.|||use CHAR(n) instead of VARCHAR(n) when n<4
consider using CHAR instead of VARCHAR when there's only one non-null VARCHAR in the table
what did you mean by "smarter" anyway?|||fixed length identifier fields? even though smart numbers are stupid.
Yea, I have seen those in fixed length identifier fields,any more use of char?
Well, in database designing which attributes are assigned as char?
If we don't use all the characters tehn its a mess...what are the best situation to use char? Plz comment...
It may seem a silly one but I think it has an important significance in database designing...:)
Thanks!!
Joydeep|||use CHAR(n) instead of VARCHAR(n) when n<4
Yea thats a very good point...:rolleyes:
and
consider using CHAR instead of VARCHAR when there's only one non-null VARCHAR in the table
that should be applied when and only when n<4.Isn't it?
ok,thank you for those info.
what did you mean by "smarter" anyway
by smarter I mean to say varchar though variable length does provide more efficient storage than char and also doesn't need any trim functions to compare...and many more are there...:)
Thanks!!
Joydeep|||yes, those are advantages for VARCHAR, good points
there really isn't any reason to have CHAR, when you think about it
i'm guessing it must be an historic relic from back in the days when databases were a lot less efficient handling VARCHARs|||i'm guessing it must be an historic relic from back in the days when databases were a lot less efficient handling VARCHARs
LOL,:D
rudy.ca... its cool and your pic too :)
Thanks again r937|||Oh heavens, there are lots of reasons for using the CHAR datatype. It is far more efficient when dealing with "character indicators" which are short, fixed length strings (like Y/N, M/F, etc). CHAR is also better for moving data back and forth between today's equipment and yesterday's equipment... It is practically impossible to deal with variable length columns in Z/OS, and many of us still have to deal with things like that.
In general, I prefer to use VARCHAR, but there are times and reasons to use CHAR, and I wouldn't want to be without it as a choice.
-PatP|||Oh heavens, there are lots of reasons for using the CHAR datatype. It is far more efficient when dealing with "character indicators" which are short, fixed length strings (like Y/N, M/F, etc). CHAR is also better for moving data back and forth between today's equipment and yesterday's equipment... It is practically impossible to deal with variable length columns in Z/OS, and many of us still have to deal with things like that.
-PatP
And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fields.:rolleyes:
Thanks Pat
Joydeep|||And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fields.:rolleyes:
Thanks Pat
Joydeep
I would say using CHAR in the zipcode is a good idea. I'm in Canada and we have postal codes that contain letters and numbers. Not only that, they are 6 characters long! I've seen some pretty bad e-commerce sites that wouldn't let me put in my address because their "zip code" field wouldn't let me enter the last character of my postal code.|||And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fieldsWell, the rule about using CHAR when length < 4 really applies to variable length strings less than four characters. Any time you have a fixed-length string (such as a five digit zip code or a nine digit social security number) CHAR is more appropriate and more efficient than VARCHAR.|||Well, the rule about using CHAR when length < 4 really applies to variable length strings less than four characters.i do believe i said that quite early in the thread :)
Any time you have a fixed-length string (such as a five digit zip code ...in this particular case VARCHAR(37) would've been way better, since it would allow you to store 9-digit (or 10 character, if you store the dash between the 5 digits and the 4) with absolutely no change to your database or your app
whereas with CHAR(5) for the zip code, you're screwed
another fine example of the one of the many benefits of VARCHAR
;)|||i do believe i said that quite early in the threadGreat advice is worth saying twice, eh?
in this particular case VARCHAR(37) would've been way better, since it would allow you to store 9-digit (or 10 character, if you store the dash between the 5 digits and the 4) with absolutely no change to your database or your appI'm a strong believer in storing ZIP and ZIP4 as separate fields. Pesky normalization habits of mine...|||I'm a strong believer in storing ZIP and ZIP4 as separate fields. Pesky normalization habits of mine...oh you silly man
okay, either you are consistent and silly, or else you are inconsistent and pragmatic, but please don't use "normalization" as an excuse for rationalize it either way
do you put house number in a separate column? i.e. not address1='123 sesame st' but address1_number='123', address1_street='sesame st'
do you put apartment/suite number in a separate column?
do you put zip code into a different table? after all, it's in a one-to-many relationship with addresses, so if a zip code changes, wouldn't you want to use a surrogate key instead?
and really, the 4-digit zip code suffix is functionally dependent on the 5-digit zip code prefix, so if you have those two columns side by side in the same row, what does that do for your normalization efforts?
address fields are NOTORIOUSLY the wrong example to use when discussing normalization
:)|||I always enjoy a goo d pedantic discussion...
Speaking of OS/390 z/OS
varchar is still painful in DB2 for the Client and/or COBOL Sprocs?
I'm about to launch a new dev project there and am in the middle of building the model soon...and they want free form description columns out the but at 300 bytes...
I need to talk them down to 255 to avoid LONG datatypes, but since they have so many, I was hoping to use varhcar.
I'll use char to make life easier, because I really don't care about DASD all that much...I just imagine speed will be impacted because of the misuse of the buffers...|||I'll use char to make life easier, because I really don't care about DASD all that much...I just imagine speed will be impacted because of the misuse of the buffers...
Is there anybody who cares for Domain Integrity? I think there should be some specific norms for database designing and normalization.Then the fuss about char and varchar implementation should have been gone..;)
Joydeep|||Is there anybody who cares for Domain Integrity? I think there should be some specific norms for database designing and normalization.Then the fuss about char and varchar implementation should have been gone..;)
Joydeep
Really now.
At the moment, I just want to slam the damn thing into production and make the deadline.
If this were SQL Server I was working on, it wouldn't be a problem.
I think I'm gonna go with:
, COL1 CHAR(255) NOT NULL WITH DEFAULT
I want my developers to be happy...actually I want my developers to be productive and accurate, i.e. I don't want code blowing up all over the place...
Ever seen an external COBOL Stored Procedure for DB2 OS/390?
And they've implemented some heavy duty Changeman procedures...they can't even compile code unless it's in a package...|||Really now.
At the moment, I just want to slam the damn thing into production and make the deadline.
If this were SQL Server I was working on, it wouldn't be a problem.
I think I'm gonna go with:
, COL1 CHAR(255) NOT NULL WITH DEFAULT
I want my developers to be happy...actually I want my developers to be productive and accurate, i.e. I don't want code blowing up all over the place...
Ever seen an external COBOL Stored Procedure for DB2 OS/390?
And they've implemented some heavy duty Changeman procedures...they can't even compile code unless it's in a package...
I agree with you for the above case.But don't you think database designing should involve a greater time than the rest of the jobs in production? Meeting deadlines is always a headache,but do you think we could sacrifice the dedicated time of designing for the sake of deadline only? :)
Joydeep|||do you put house number in a separate column? i.e. not address1='123 sesame st' but address1_number='123', address1_street='sesame st'I'd do it in a second if it didn't place an undo burden on the person entering the data, and if there were a simple method of ensuring data entry integrity. Many business processes (such as bulk mail discounts) require the address to be parsed and sorted in a specific manner.
do you put zip code into a different table? after all, it's in a one-to-many relationship with addresses, so if a zip code changes, wouldn't you want to use a surrogate key instead?I would absolutely do that if I had additional zip code attributes to store, such as demographics. Heck, I might do it just to ensure the validity of the zip codes that are entered. Yeah, it IS a one-to-many relationship, whether you choose to materialize the data or not.
and really, the 4-digit zip code suffix is functionally dependent on the 5-digit zip code prefix, so if you have those two columns side by side in the same row, what does that do for your normalization efforts?Yes, for stricty relationtional integrity zip4 codes should be a subtable of zip, and only the foreign key to zip4 should be stored in the adress table. But most applications do not require and cannot efficiently enforce these rules on the users. Asking the user to separte ZIP from ZIP4, however, is a pretty small request, and greatly facilitates grouping and sorting by zip code when zip4 is not required.
address fields are NOTORIOUSLY the wrong example to use when discussing normalizationAu contraire! The notorious unreliability of address fields make them a great cautionary tale against storing multiple attributes in a single column.|||Well, let's see.
The business hired a management team to develop the specs. I have been going through them and we are working out the kinks. The requirements are what they are, but the business keeps saying they don't know what they want specifically.
I'm ok with that, and I've already made modifications to tha model. It's pretty sound, but there are some definete kluges in there.
Also, I have the management team to blame.
I will still be worrying about performance and data integrity...but the integrity might take a hit in some places...
I'm gonna start a new thread|||Well, let's see.
The business hired a management team to develop the specs. I have been going through them and we are working out the kinks. The requirements are what they are, but the business keeps saying they don't know what they want specifically.
I'm ok with that, and I've already made modifications to the model. It's pretty sound, but there are some definete kluges in there.
Also, I have the management team to blame.
I'm gonna start a new thread
Yea, thats a common problem.They don't even clear up the functional areas properly.That makes the case complicated.Too many groups spoil the broth.
I think you need more patience than technical expertise here...:D
Good luck!!
Joydeep|||Trick is to not care so much
Subscribe to:
Posts (Atom)