Howdy,
Is SQL 2000 clustering on windows 2000 any good ??
We are looking at spending *quite* a bit of money to implement it, but I need opinions of what its like from the people that actually use it & look after it.
e.g.
DOES IT WORK LIKE IT SHOULD????
Is it reliable?
Is it resource hungry?
Are there lots of bugs?
All repsonses very welcome. No response too small.
Thanks for your help.
Cheers,
SG.I can't offer too much advise except to say,... a few of the projects I have been involved in have used it. I have never had to set it up or admin it or anything. I have heard that setup can be a pain in the butt, but once it is going things tend to run okie.
As a developer I have never had any problems with what others have set up in the way of clusters.|||Hey,
This is a bit long, so I apologise for that,
but this is some text I found on www.sqlteam.com which I think is particularly relevant in this case:
//direct paste from: http://www.sqlteam.com/item.asp?ItemID=5137
Notes from the Field
I sent out an email to a few people that I know run a cluster in production and have done so for a while. Here's the information I got back from JohnDeere:
I have found the biggest shortfall to be the lack of monitoring tools for Microsoft Cluster Services. There is no built in notification if a node fails, but at least on Win2K you can install the cluster admin tool on your local workstation so you don't have to go to the server room. Terminal services does not work in the cluster.
Be sure your backup strategy will work in a cluster and be sure it will work for Sql server. Our original strategy was Compaq's virtual replicator. It does not work in a cluster and I was never sure how these snapshots would work for SQL server. We switched to a Spectra 10000 4 drive AIT backup unit.
If your cluster is not fully redundant make sure you have the spare parts in stock or on a quick delivery time frame. Our cluster is not redundant but Compaq has all the parts on a 4 hour delivery time. (SAN controllers, SAN Switch, fiber cable, disk drives We have one hot spare but none on the shelf) The basic idea is to survive a CPU fault but if any of the shared disk pieces fail and you have no redundancy the whole cluster is down. (Management really does not like this)
Be sure to mention only start and stop services from the cluster administrator. Never start or stop SQL or its related services from enterprise manager, Sql service control or the services applet from control panel. The only way I have been able to recover from this is a reboot of both nodes on the cluster.
One of the biggest issues on our cluster is disk contention. You have 2 servers fighting for time on a single physical disk cabinet. In my cluster if I start 2 backups at the same time. The time to backup doubles or If we start a major import operation on node1 the performance of node2 suffers. (graz: I'm lucky. We have two physical disk cabinets.)
I would also mention hardware sizing (# of CPU's & RAM). Make sure one of your servers has enough horsepower to run both instances of SQL Server in the event of a fail over. In the 7 world this is a little different but in 2000 you are running 2 separate instances of mssql.exe and its associated programs.
If you cluster 2000 you must name the instances and change the port from 1433 on one of the instances. If you don't have MDAC 2.6 then you must alias the non-1433 server via client network utility|||Actually, re-reading it, lol, the first paragraph is all i needed to paste (sorry it's been a long day), and this forum doesn't seem to have an edit function. Oh well... ops *slaps himself*|||it must have been a long day, check the edit link at the bottom right of your post. ;)|||The company that I'm working for has the SQL cluster servers with active to active. Like rokslide said, the setup is very tough, especially for active to active. We had Microsoft consultants to set up and cost much time and money. After it is up, that is really good.|||Oh yea, the one that says Edit, LOL...
*slaps himself again*
I thought I'd used it before, couldn't believe it when I couldn't find it.
Just think, if I can't find the edit button on this forum, imagine the quality of my code, lol (hm, i better get testing tomorrow, lol)
Showing posts with label bit. Show all posts
Showing posts with label bit. Show all posts
Sunday, March 25, 2012
Wednesday, March 7, 2012
Does Boolean DataType Exists in SQL server
Does boolean data type exists in Sequel if y how? if N y?
How can i opt for boolean ( nearest ) ?
I've used Bit with not null
i want 2 display column of bit datatype into Checkbox .. Is is possible??
Whenver i bind the datatype of bit for checkbox it is throwing an error??
HELP ME HELP MEBit works fine as a boolean field, I don't have any problems. It is most likely a problem with nulls in your data. You can bind the bit filed to the checked property of a checkbox and you can put that checkbox in a repeater or datagrid using TemplateColumn.
Can you show your code. Or have you already resolved this issue. I know it is a bit old.
Does bit data type function as boolean?
Hi,
I have some applications working fine with Sql Server 7 and Jet 4.
I'm doing some tests with Sql Server 2000 to port these applications to it. I'm having some troubles to query tables using a bit field. For example:
These query worked fine in Sql Server 7:
"Select <fields> from <table> where <boolean/bit field>"
Now I have to do this way:
"Select <fields> from <table> where <bit field> = 1"
Why does this happen? Sql Server 2000 doesn't recognize a boolean value anymore? It's easier and more comprehensive (to me) to use the first way.
Does anyone know why this is happening? Does Sql Server work different than older versions?
Thanks!
Fabianohttp://dbforums.com/arch/43/2002/4/343538
Originally posted by fabiano
Hi,
I have some applications working fine with Sql Server 7 and Jet 4.
I'm doing some tests with Sql Server 2000 to port these applications to it. I'm having some troubles to query tables using a bit field. For example:
These query worked fine in Sql Server 7:
"Select <fields> from <table> where <boolean/bit field>"
Now I have to do this way:
"Select <fields> from <table> where <bit field> = 1"
Why does this happen? Sql Server 2000 doesn't recognize a boolean value anymore? It's easier and more comprehensive (to me) to use the first way.
Does anyone know why this is happening? Does Sql Server work different than older versions?
Thanks!
Fabiano|||nice thread, sbaru
especially joe celko's advice not to use BIT
did you notice that no one actually answered the question "...so the correct, non-proprietary datatype for representing a boolean value is...?"
fabiano, your question "Sql Server 2000 doesn't recognize a boolean value anymore?" is not quite accurate -- i don't think it ever did
if you can change the datatype, you might consider a numeric field like TINYINT or INTEGER instead of BIT
see also Booleans in SQL Server (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid556536_tax285649,00.html)
rudy|||Thanks Rudy and Sbaru,
I've been advised to use tinyint (or some integer variant) to do it. I have some doubts about use tinyint instead of bit to represent a boolean type as follows:
1. Didn't bit type created to represent boolean values? Why does it exist? It what kind of situation am I going to use it?
2. Is bit an optimized type to work with?
3. I know it doesn't make sense to summarize or group by a bit field but it's very common to use this type of field to count records in a table or query by it.
Ex:
Employees that have dependents:
"Select Cod_emp from Employees Where Dep_emp"
Count the number of these employees:
"Select Count(Cod_emp) from Employees Where Dep_emp"
These queries above are common in some situations.
Note that I don't need to explicit "Dep_emp = 1" Or "Dep_emp = 0", it's just "Dep_emp". The query is clearer to read.
I have many queries this way. If I change the bit types to tinyint will they work fine or I will have to change the structure of all my queries?
Thanks for your time!
Fabiano|||1) no, BIT was created to store bits
2) depends what you mean by "optimized"
3) "Note that I don't need to explicit "Dep_emp = 1" Or "Dep_emp = 0", it's just "Dep_emp". The query is clearer to read."
whether it is clearer to read is a matter of opinion
"Dep_emp" by itself means nothing to me
"Dep_emp = 1" means nothing to me either
if the purpose of the Dep_emp column is to indicate whether the employee belongs to the department, then i suggest that it is not at all clear, and in fact is poor design
but yes, you are going to have to change the structure of all your queries
rudy|||It doesn't work that way..
USE Northwind
GO
CREATE TABLE myTable99 (col1 bit)
GO
INSERT INTO myTable99(Col1) SELECT 1 UNION ALL SELECT 0
GO
--Doesn't work
SELECT * FROM myTable99 WHERE Col1 TRUE
--Works
SELECT * FROM myTable99 WHERE Col1 = 1
GO
DROP TABLE myTable99
GO
And what is dep_emp...it must be predefined somewhere..
Also it sounds like all of your SQL code is in the app...now would be a good time to turn them in to stored procedures|||I understood. I'm going to change some queries in my applications.
I've already have most of my queries in stored procedures and views, and these queries use boolean fields in criteria search the way I've described.
Returning to those doubts I've mentioned:
1) Yes. Bit fields were created to store bits. But bit value seems to use in boolean cases. When do I need to store pure bits? If the type was byte instead of bit it would make sense, but bit? Bit value accepts only 0 or 1, True or False, Yes or No or some variance of it. That's why I've thought in use it to store this type of value. But it's ok! I've understood that it won't work this way.
2) When I've mentioned "optimized" I wanted to know if Sql Server treat this type of field in a different way. Do you know if it proceeds?
3) The field "Dep_emp" was not the better example. Think in a gender field to store "male" or "female" where "true" could be "female" and so on. In this case a query like "Select Name_cus from Customers Where Gen_cus" would return just the females ones in a case of a promotion for the female customers for example. In this case, untill now, I didn't have to explicit "Where Gen_cus = 1". I've been used just "Where Gen_cus" that it seems to be clearer once you think in "boolean way".
My doubt is why did MS change the way of treatment of bit fields in Sql Server 2000? Any other DBMS or older versions of Sql Server recognizes the ANSI-SQL "Where <boolean field>" without the need of explicit the true or false value. Once it's a boolean field it doesn't make sense to explicit the value, right? It's true or false, nothing more. I thought this type of field was created to treat these situations in a "optimized" way, even just to clear the queries.
This way of treatment of boolean values helps us even more than this. In a table that stores the quantity of dependents of a employee (like children, parents, ...) you will be able to query for employees that have dependents just using the boolean way, for example:
"Select Cod_emp from Employess Where Dependents_emp"
Or
"Select Cod_emp from Employees Where Not Dependents_emp"
Note that I don't need to explicit "Where Dependents_emp > 0" or "Where Dependents_emp = 0". I don't need, in this case, to know how many dependents each employee has, just to know who they are. There're many reasons for this kind of query. This way of query worked not just in bit fields but in all numeric fields. Any value different than 0 was treated as true.
The biggest problem is that I'll need to separate my apps in 2 versions. One for any DBMS and one just for Sql Server 2000. I'm not worried about the performance using a "native" Sql of each DBMS. I'm worried about the portability of my apps to any DBMS, that's why I often use ANSI-SQL, even because the performance boost is not too sensitive.
Thanks again!
Fabiano|||i could be wrong, but i don't think sql server has ever supported boolean
it goes against everything we've come to expect from microsoft, that they would have a standard feature in an early database and then stop supporting it -- that just doesn't make sense
as far as your rather creative way of using an integer such as number of dependents in a WHERE clause as a boolean...
"Any value different than 0 was treated as true" ?
that may be the way it works in mysql, but it certainly is not standard behaviour, as far as i know, and all i can say is, i hope i never have to support one of your apps, because that is certainly counter-intuitive
at least, to me ;)|||Hi Rudy,
Please forgive me if it seemed that I've been rude or arrogant. Believe, I was not intended to. My english is very poor and I don't know how to expressing my words very well. I've been just "translating" my "portuguese thoughts" in "english words" and I suppose you got me wrong.
It seems to me that you got angry about anything I've said in your words:
"...whether it is clearer to read is a matter of opinion
Dep_emp by itself means nothing to me
Dep_emp = 1 means nothing to me either..."
That's not possible that you couldn't see the mean of these codes. It's very clear. You don't need to know the total structure of the project to suppose that it's a query that brings rows where "Dep_emp" is "true". Just it!
--
"...and in fact is poor design."
How can you say something like that without the knowledge enough about the project?
--
"i could be wrong, but i don't think sql server has ever supported boolean..."
Yes. You're wrong. The bit field was treated as boolean untill Sql Server 7. My apps stopped to work only when I upgraded to Sql Sever 2000.
--
"as far as your rather creative way of using an integer such as number of dependents in a WHERE clause as a boolean..."
The more experience more creativity, indeed.
--
"Any value different than 0 was treated as true ?
that may be the way it works in mysql, but it certainly is not standard behaviour, as far as i know..."
I think you don't have a basic knowledge about another RDBMS than Sql Server 2000. This behavior has been standard at least in Jet, Sql Server (untill 7), MySql and Postgre SQL. I'm working with all these ones and I can say that they work fine.
--
"...i hope i never have to support one of your apps, because that is certainly counter-intuitive."
Yes. You're totally right. My apps need more "creativity" and I think you've not got it enough.
--
Please take a good look at the links below (they're all from Microsoft as you can see):
http://support.microsoft.com/default.aspx?scid=kb;en-us;129803
"0=False, non-zero=True"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vadatBoolean.asp
"When numeric data types are converted to Boolean values, 0 becomes False and all other values become True."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_7uzy.asp
"Converting to bit promotes any nonzero value to 1."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp
"Microsoft SQL Server optimizes the storage used for bit columns."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledbsql/9_ole_13_7s6r.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledbsql/9_ole_13_10ha.asp
*Sqloledb mapps bit data type to boolean data type according to the table shown.
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/trans/ch20.asp
"Efficient datatypes
...Narrow columns make narrow tables, and narrow tables allow you to store more rows on a single data page...
...If your table contains a series of flags with Yes/No or False/True type of information, the best datatype for such columns is BIT. This type is especially effective when you have several columns of type BIT in a table..."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbls7/html/vblrfvbspec10_2a.asp
"...A zero numeric value converts to the literal False. All other numeric values convert to the literal True."
--
You can see that I've followed the MS' suggestions. That's why my disappointment with the change of treatment of the bit type.
I'm not saying Sql Server is a bad RDBMS. I use it for a long time and I like its performance and stability. I often suggest it for my customers. Then you don't need to get personally offended. It was not intension. Sorry Rudy.
Fabiano
IT Solution Engineer|||fabiano, i was not angry, and i am not angry now
i've been working with databases since the mid '70s, and i do have plenty of experience with them
what is "creative" to you may not seem so to someone else
you are entitled to your opinion, and i am entitled to mine, which i have arrived at after working with many different databases for many decades
i have plenty of creativity, my friend
good luck to you sir
rudy
p.s. you say sql server 7 "treated" BIT as BOOLEAN, but that's not the same thing as supporting BOOLEAN|||Come on Rudy, admit it! Everything you know you learned by scanning the back cover of "Databases For Dummies" at Barne's And Noble!
It's time to face the music!
:p|||sheeeeeesh
flame wars...
Why are people soooooooo sensitive...
Is it the ability to lash out in anonymity ?
And fabiano...chill
Please forgive me if it seemed that I've been rude or arrogant. Believe, I was not intended to. My english is very poor and I don't know how to expressing my words very well. I've been just "translating" my "portuguese thoughts" in "english words" and I suppose you got me wrong.
That translating part I find goes both ways...with the expression, and the interpretation...
Take everything with a grain of salt...nobody at the sites (well with the exception of a few) are not here, not to help...
Now back to the thing...
Where is your code stored? In an Access front end? VB? Stored procedures?
And Can you post the DDL and or code so we can have a look?
Maybe only a Blindman (get the pun) can interpret something we haven't seen.
I'm very curious as to what you have there (It's the only way we learn too)
Thanks|||How I was asking for help (I know nobody here is obligated to, it's just a forum) and it seemed Rudy didn't like my insistence in some points. I was testing nobody, just debating to see if I could do something not to need changing my whole code.
I have some codes in sp, views, using the execute method of ado in vb and some dlls in mts. The job will be enormous considering all projects and tecnologies involved with.
I think I don't need to post any part of the code. The doubt is simple. Once it doesn't matter if I'm querying from a sp, an execute method or view, Sql Server will be always the "interpreter". If the query was "Select <something> from <some table> Where <some field>", it would be interpreted the same way, independently the method used.
I know "treat by" and "supporting" are different things, but the mean of it is the same. If I can have a condition search like "Where <bField>" and the RDBMS returns the records means that it treated the content of that field as boolean, even the RDBMS doesn't support boolean types (and that's it what have been done by earlier versions of Sql Server).
I don't have all of that experience because I began to work with projects design at the mid' 80s (10 years latter). Even so I think the time is not the only manner to measure the ability of a professional. There're plenty of professionals that don't worry about their evolution.
But it's ok. I've interpreted wrong all of those words I'm sure. Forgive me.
About the anonymity I think there's something wrong. Fabiano is my true name. Brasil, Gois, Goinia is my true physical location. If the problem is a telephone number here it goes: +55 (62) 259-7567 (Commercial. I'll be there from January, 5). I don't speak english very well (as you can see) but if we have patience I guess we can understand each other.
Fabiano
Entitled to my opinion: IT Solution Engineer|||fabiano, your english is fine, that was never the problem, in fact, your english is way better than my portuguese
;)
i understand your frustration, and all i can say is, you have learned a valuable lesson about creativity versus flexibility and maintainability
i practice what i call "defensive sql" and WHERE BITFIELD by itself is not very defensive, as you have found out
good luck with your upgrade
rudy|||Rudy,
You don't need to know portuguese once I came for help in a foreign forum (for me ;)). It's my obligation to know how to express my doubts, I know.
With the improvement of my english I can see clearly now. It was not your (or Brett's) fault, sorry. It was seemed to me that you've been ironic in your answers, but was not.
I respect your knowledge. Unfortunatelly the earlier versions of Sql Server did work the way I told, but it's not the problem. That sintax I've demonstrated is ANSI-SQL and not a "native" one.
I've been using ANSI-SQL thinking the same way of yours "defensive sql". To have not problems at all (unless the performance was critical, although it would be the last thing I was change).
I think that I'll need to call for mommy... :)
Fabiano|||I like Rudy's concept of "defensive SQL". Its certainly possible to be so creative in a search for the elegant solution that it becomes difficult for even experience SQL programmers to decipher your logic later on. What I refuse to do, though, is to dumb down my code so that it is readable by any VB developer that comes along. Efficiency and functionality are still of higher importance.
At least thats what I read in "SQL For Idiots".
blindman :cool:|||Fabiano,
Where's Goias?
Getting ready for New Years eve?
And is that really an ANSI Compliant SQL?
Anyone got a link to them?
Still dusting off my 6.5 book to take a look see...|||Originally posted by fabiano
That sintax I've demonstrated is ANSI-SQL and not a "native" one.i don't know if your syntax actually was ANSI
i think ANSI syntax requires you to say WHERE BOOLFIELD IS TRUE or WHERE BOOLFIELD IS FALSE
you left off the IS TRUE part ;)
(i could be wrong -- i don't have an intimate knowledge of the ANSI standard, and the Mimer SQL Validator (http://developer.mimer.se/validator/index.htm) appears to be down at the moment)
in any case, i do not see the value in using ANSI sql intended for one data type (BOOLEAN) on a different datatype (BIT)
i.e. WHERE BITFIELD IS TRUE wouldn't have worked with sql server 7 BIT fields anyway, right?
rudy|||Hey...pretty exotic...
http://www.brazil.travelmall.com/travelmall/region/Brasillia+and+Goias
Got any 6 month consulting gigs opening up?
Sounds like you'll need a hand or two...|||Hmmm. This is from that link:
Here are some points that will make your trip more pleasant:
1) Take good care in your personal hygiene and dress to avoid ridicule and to fit in with the locals.
Never been to good with that "dress to avoid ridicule" thing... ;-)|||I liked this one
Generally be wary of what you do to avoid offending or causing trouble.|||Hmm. They must have a big sign out front that says "No Americans allowed".|||HEY!
I resemble that remark!|||http://docs.zf.jcu.cz/sql1992.txt
"8.12 <search condition>
...
Format
<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ] "
* it seems to be optional to specify the truth value.
"...
Leveling Rules
1) The following restrictions apply for Intermediate SQL:
a) A <boolean test> shall not specify a <truth value>."
* here's a recommendation.
"...
24)Subclause 8.12, "<search condition>":
a) A <boolean test> shall not specify a <truth value>."
* that's the recommendation again.
"...
12.4 Calls to a <procedure>
...
TYPE;
type BIT is array (NATURAL range <>) of BOOLEAN;"
* here's saying that each bit is a boolean value.
----
This doc reproduces the entire content of ANSI-SQL-92 Standard.
How my english is not at all good, I've transported a part of the doc above to be interpreted.
What it seems to be clear is that BIT and BOOLEAN are synonymous.
This way Sql Server 7 seems to be ANSI-SQL-92 Compliant and Sql Server 2000 is not. I say that because Sql Server 7 works fine with my code, no problems.
For example. If you have a table like this:
Cus_id as Integer, Cus_Name as Varchar(50), Cus_gen as Bit
*Cus_gen: (0) male, (1) female
With some records like these:
1, Rudy, 0
2, Fabiano, 0
3, MCrowley (Ozzy Osbourne), 0
4, Brett Kaiser, 0
5, Suzy, 1
You can query females this way (Sql Server 7):
Select Cus_name Where Cus_gen
But using Sql Server 2000 you'll need to do this way:
Select Cus_name Where Cus_gen = 1
Once the value of "Cus_gen" is a boolean (or bit) you don't need to specify the boolean value, it doesn't make sense, it's true or false, nothing more.
For example, in VB (or any language) to do a loop through the records of a table you do something like that:
<open your recordset>
While Not adoRS.Eof
<do something>
adoRS.MoveNext
Wend
I do not need to specify:
While Not adoRS.Eof = True
or
While adoRS.Eof = False
Or a condition to verify whether a number is odd or even:
If X Mod 2 then
<even>
Else
<odd>
End If
I don't need to do:
If X Mod 2 <> 0 then
And so on. I've been used thus. This way we save a processor time taking out the need of the comparison "<> 0" or "=True" and, at the same time, you let your app smaller.
I don't remeber where I've read about these tips. I'll look for it later. I remember just something about "how you optimize your code" or something like that. I guess was at MS.com, don't remember.
In many cases the bandwidth of 100 Mbits of a huge LAN is not enough to provide a good performance, than we have to use the creativity to improve it. Worse when you working on an eCommerce. I use the standards to develop but I try to maintain my apps the minimun code as possible.
I'm not trying to explain my point of view. I'm trying to know if the way I work is actually a good way or if there's better ways to do it.
Thanks!
Fabiano|||congratulations on digging up the 1992 standard
again, all i can say is, too bad you have to rewrite all your code
want to know how i design male/female columns?
instead of
... Where Cus_gen
i prefer
... Where Cus_gen = 'F'
but that's just me, eh
rudy|||My e-mail is: fabianomaiafranco@.hotmail.com (it's my messenger passport too).
Thanks!!!
Fabiano
The Pretentious ;)
I have some applications working fine with Sql Server 7 and Jet 4.
I'm doing some tests with Sql Server 2000 to port these applications to it. I'm having some troubles to query tables using a bit field. For example:
These query worked fine in Sql Server 7:
"Select <fields> from <table> where <boolean/bit field>"
Now I have to do this way:
"Select <fields> from <table> where <bit field> = 1"
Why does this happen? Sql Server 2000 doesn't recognize a boolean value anymore? It's easier and more comprehensive (to me) to use the first way.
Does anyone know why this is happening? Does Sql Server work different than older versions?
Thanks!
Fabianohttp://dbforums.com/arch/43/2002/4/343538
Originally posted by fabiano
Hi,
I have some applications working fine with Sql Server 7 and Jet 4.
I'm doing some tests with Sql Server 2000 to port these applications to it. I'm having some troubles to query tables using a bit field. For example:
These query worked fine in Sql Server 7:
"Select <fields> from <table> where <boolean/bit field>"
Now I have to do this way:
"Select <fields> from <table> where <bit field> = 1"
Why does this happen? Sql Server 2000 doesn't recognize a boolean value anymore? It's easier and more comprehensive (to me) to use the first way.
Does anyone know why this is happening? Does Sql Server work different than older versions?
Thanks!
Fabiano|||nice thread, sbaru
especially joe celko's advice not to use BIT
did you notice that no one actually answered the question "...so the correct, non-proprietary datatype for representing a boolean value is...?"
fabiano, your question "Sql Server 2000 doesn't recognize a boolean value anymore?" is not quite accurate -- i don't think it ever did
if you can change the datatype, you might consider a numeric field like TINYINT or INTEGER instead of BIT
see also Booleans in SQL Server (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid556536_tax285649,00.html)
rudy|||Thanks Rudy and Sbaru,
I've been advised to use tinyint (or some integer variant) to do it. I have some doubts about use tinyint instead of bit to represent a boolean type as follows:
1. Didn't bit type created to represent boolean values? Why does it exist? It what kind of situation am I going to use it?
2. Is bit an optimized type to work with?
3. I know it doesn't make sense to summarize or group by a bit field but it's very common to use this type of field to count records in a table or query by it.
Ex:
Employees that have dependents:
"Select Cod_emp from Employees Where Dep_emp"
Count the number of these employees:
"Select Count(Cod_emp) from Employees Where Dep_emp"
These queries above are common in some situations.
Note that I don't need to explicit "Dep_emp = 1" Or "Dep_emp = 0", it's just "Dep_emp". The query is clearer to read.
I have many queries this way. If I change the bit types to tinyint will they work fine or I will have to change the structure of all my queries?
Thanks for your time!
Fabiano|||1) no, BIT was created to store bits
2) depends what you mean by "optimized"
3) "Note that I don't need to explicit "Dep_emp = 1" Or "Dep_emp = 0", it's just "Dep_emp". The query is clearer to read."
whether it is clearer to read is a matter of opinion
"Dep_emp" by itself means nothing to me
"Dep_emp = 1" means nothing to me either
if the purpose of the Dep_emp column is to indicate whether the employee belongs to the department, then i suggest that it is not at all clear, and in fact is poor design
but yes, you are going to have to change the structure of all your queries
rudy|||It doesn't work that way..
USE Northwind
GO
CREATE TABLE myTable99 (col1 bit)
GO
INSERT INTO myTable99(Col1) SELECT 1 UNION ALL SELECT 0
GO
--Doesn't work
SELECT * FROM myTable99 WHERE Col1 TRUE
--Works
SELECT * FROM myTable99 WHERE Col1 = 1
GO
DROP TABLE myTable99
GO
And what is dep_emp...it must be predefined somewhere..
Also it sounds like all of your SQL code is in the app...now would be a good time to turn them in to stored procedures|||I understood. I'm going to change some queries in my applications.
I've already have most of my queries in stored procedures and views, and these queries use boolean fields in criteria search the way I've described.
Returning to those doubts I've mentioned:
1) Yes. Bit fields were created to store bits. But bit value seems to use in boolean cases. When do I need to store pure bits? If the type was byte instead of bit it would make sense, but bit? Bit value accepts only 0 or 1, True or False, Yes or No or some variance of it. That's why I've thought in use it to store this type of value. But it's ok! I've understood that it won't work this way.
2) When I've mentioned "optimized" I wanted to know if Sql Server treat this type of field in a different way. Do you know if it proceeds?
3) The field "Dep_emp" was not the better example. Think in a gender field to store "male" or "female" where "true" could be "female" and so on. In this case a query like "Select Name_cus from Customers Where Gen_cus" would return just the females ones in a case of a promotion for the female customers for example. In this case, untill now, I didn't have to explicit "Where Gen_cus = 1". I've been used just "Where Gen_cus" that it seems to be clearer once you think in "boolean way".
My doubt is why did MS change the way of treatment of bit fields in Sql Server 2000? Any other DBMS or older versions of Sql Server recognizes the ANSI-SQL "Where <boolean field>" without the need of explicit the true or false value. Once it's a boolean field it doesn't make sense to explicit the value, right? It's true or false, nothing more. I thought this type of field was created to treat these situations in a "optimized" way, even just to clear the queries.
This way of treatment of boolean values helps us even more than this. In a table that stores the quantity of dependents of a employee (like children, parents, ...) you will be able to query for employees that have dependents just using the boolean way, for example:
"Select Cod_emp from Employess Where Dependents_emp"
Or
"Select Cod_emp from Employees Where Not Dependents_emp"
Note that I don't need to explicit "Where Dependents_emp > 0" or "Where Dependents_emp = 0". I don't need, in this case, to know how many dependents each employee has, just to know who they are. There're many reasons for this kind of query. This way of query worked not just in bit fields but in all numeric fields. Any value different than 0 was treated as true.
The biggest problem is that I'll need to separate my apps in 2 versions. One for any DBMS and one just for Sql Server 2000. I'm not worried about the performance using a "native" Sql of each DBMS. I'm worried about the portability of my apps to any DBMS, that's why I often use ANSI-SQL, even because the performance boost is not too sensitive.
Thanks again!
Fabiano|||i could be wrong, but i don't think sql server has ever supported boolean
it goes against everything we've come to expect from microsoft, that they would have a standard feature in an early database and then stop supporting it -- that just doesn't make sense
as far as your rather creative way of using an integer such as number of dependents in a WHERE clause as a boolean...
"Any value different than 0 was treated as true" ?
that may be the way it works in mysql, but it certainly is not standard behaviour, as far as i know, and all i can say is, i hope i never have to support one of your apps, because that is certainly counter-intuitive
at least, to me ;)|||Hi Rudy,
Please forgive me if it seemed that I've been rude or arrogant. Believe, I was not intended to. My english is very poor and I don't know how to expressing my words very well. I've been just "translating" my "portuguese thoughts" in "english words" and I suppose you got me wrong.
It seems to me that you got angry about anything I've said in your words:
"...whether it is clearer to read is a matter of opinion
Dep_emp by itself means nothing to me
Dep_emp = 1 means nothing to me either..."
That's not possible that you couldn't see the mean of these codes. It's very clear. You don't need to know the total structure of the project to suppose that it's a query that brings rows where "Dep_emp" is "true". Just it!
--
"...and in fact is poor design."
How can you say something like that without the knowledge enough about the project?
--
"i could be wrong, but i don't think sql server has ever supported boolean..."
Yes. You're wrong. The bit field was treated as boolean untill Sql Server 7. My apps stopped to work only when I upgraded to Sql Sever 2000.
--
"as far as your rather creative way of using an integer such as number of dependents in a WHERE clause as a boolean..."
The more experience more creativity, indeed.
--
"Any value different than 0 was treated as true ?
that may be the way it works in mysql, but it certainly is not standard behaviour, as far as i know..."
I think you don't have a basic knowledge about another RDBMS than Sql Server 2000. This behavior has been standard at least in Jet, Sql Server (untill 7), MySql and Postgre SQL. I'm working with all these ones and I can say that they work fine.
--
"...i hope i never have to support one of your apps, because that is certainly counter-intuitive."
Yes. You're totally right. My apps need more "creativity" and I think you've not got it enough.
--
Please take a good look at the links below (they're all from Microsoft as you can see):
http://support.microsoft.com/default.aspx?scid=kb;en-us;129803
"0=False, non-zero=True"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vadatBoolean.asp
"When numeric data types are converted to Boolean values, 0 becomes False and all other values become True."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_7uzy.asp
"Converting to bit promotes any nonzero value to 1."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp
"Microsoft SQL Server optimizes the storage used for bit columns."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledbsql/9_ole_13_7s6r.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledbsql/9_ole_13_10ha.asp
*Sqloledb mapps bit data type to boolean data type according to the table shown.
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/trans/ch20.asp
"Efficient datatypes
...Narrow columns make narrow tables, and narrow tables allow you to store more rows on a single data page...
...If your table contains a series of flags with Yes/No or False/True type of information, the best datatype for such columns is BIT. This type is especially effective when you have several columns of type BIT in a table..."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbls7/html/vblrfvbspec10_2a.asp
"...A zero numeric value converts to the literal False. All other numeric values convert to the literal True."
--
You can see that I've followed the MS' suggestions. That's why my disappointment with the change of treatment of the bit type.
I'm not saying Sql Server is a bad RDBMS. I use it for a long time and I like its performance and stability. I often suggest it for my customers. Then you don't need to get personally offended. It was not intension. Sorry Rudy.
Fabiano
IT Solution Engineer|||fabiano, i was not angry, and i am not angry now
i've been working with databases since the mid '70s, and i do have plenty of experience with them
what is "creative" to you may not seem so to someone else
you are entitled to your opinion, and i am entitled to mine, which i have arrived at after working with many different databases for many decades
i have plenty of creativity, my friend
good luck to you sir
rudy
p.s. you say sql server 7 "treated" BIT as BOOLEAN, but that's not the same thing as supporting BOOLEAN|||Come on Rudy, admit it! Everything you know you learned by scanning the back cover of "Databases For Dummies" at Barne's And Noble!
It's time to face the music!
:p|||sheeeeeesh
flame wars...
Why are people soooooooo sensitive...
Is it the ability to lash out in anonymity ?
And fabiano...chill
Please forgive me if it seemed that I've been rude or arrogant. Believe, I was not intended to. My english is very poor and I don't know how to expressing my words very well. I've been just "translating" my "portuguese thoughts" in "english words" and I suppose you got me wrong.
That translating part I find goes both ways...with the expression, and the interpretation...
Take everything with a grain of salt...nobody at the sites (well with the exception of a few) are not here, not to help...
Now back to the thing...
Where is your code stored? In an Access front end? VB? Stored procedures?
And Can you post the DDL and or code so we can have a look?
Maybe only a Blindman (get the pun) can interpret something we haven't seen.
I'm very curious as to what you have there (It's the only way we learn too)
Thanks|||How I was asking for help (I know nobody here is obligated to, it's just a forum) and it seemed Rudy didn't like my insistence in some points. I was testing nobody, just debating to see if I could do something not to need changing my whole code.
I have some codes in sp, views, using the execute method of ado in vb and some dlls in mts. The job will be enormous considering all projects and tecnologies involved with.
I think I don't need to post any part of the code. The doubt is simple. Once it doesn't matter if I'm querying from a sp, an execute method or view, Sql Server will be always the "interpreter". If the query was "Select <something> from <some table> Where <some field>", it would be interpreted the same way, independently the method used.
I know "treat by" and "supporting" are different things, but the mean of it is the same. If I can have a condition search like "Where <bField>" and the RDBMS returns the records means that it treated the content of that field as boolean, even the RDBMS doesn't support boolean types (and that's it what have been done by earlier versions of Sql Server).
I don't have all of that experience because I began to work with projects design at the mid' 80s (10 years latter). Even so I think the time is not the only manner to measure the ability of a professional. There're plenty of professionals that don't worry about their evolution.
But it's ok. I've interpreted wrong all of those words I'm sure. Forgive me.
About the anonymity I think there's something wrong. Fabiano is my true name. Brasil, Gois, Goinia is my true physical location. If the problem is a telephone number here it goes: +55 (62) 259-7567 (Commercial. I'll be there from January, 5). I don't speak english very well (as you can see) but if we have patience I guess we can understand each other.
Fabiano
Entitled to my opinion: IT Solution Engineer|||fabiano, your english is fine, that was never the problem, in fact, your english is way better than my portuguese
;)
i understand your frustration, and all i can say is, you have learned a valuable lesson about creativity versus flexibility and maintainability
i practice what i call "defensive sql" and WHERE BITFIELD by itself is not very defensive, as you have found out
good luck with your upgrade
rudy|||Rudy,
You don't need to know portuguese once I came for help in a foreign forum (for me ;)). It's my obligation to know how to express my doubts, I know.
With the improvement of my english I can see clearly now. It was not your (or Brett's) fault, sorry. It was seemed to me that you've been ironic in your answers, but was not.
I respect your knowledge. Unfortunatelly the earlier versions of Sql Server did work the way I told, but it's not the problem. That sintax I've demonstrated is ANSI-SQL and not a "native" one.
I've been using ANSI-SQL thinking the same way of yours "defensive sql". To have not problems at all (unless the performance was critical, although it would be the last thing I was change).
I think that I'll need to call for mommy... :)
Fabiano|||I like Rudy's concept of "defensive SQL". Its certainly possible to be so creative in a search for the elegant solution that it becomes difficult for even experience SQL programmers to decipher your logic later on. What I refuse to do, though, is to dumb down my code so that it is readable by any VB developer that comes along. Efficiency and functionality are still of higher importance.
At least thats what I read in "SQL For Idiots".
blindman :cool:|||Fabiano,
Where's Goias?
Getting ready for New Years eve?
And is that really an ANSI Compliant SQL?
Anyone got a link to them?
Still dusting off my 6.5 book to take a look see...|||Originally posted by fabiano
That sintax I've demonstrated is ANSI-SQL and not a "native" one.i don't know if your syntax actually was ANSI
i think ANSI syntax requires you to say WHERE BOOLFIELD IS TRUE or WHERE BOOLFIELD IS FALSE
you left off the IS TRUE part ;)
(i could be wrong -- i don't have an intimate knowledge of the ANSI standard, and the Mimer SQL Validator (http://developer.mimer.se/validator/index.htm) appears to be down at the moment)
in any case, i do not see the value in using ANSI sql intended for one data type (BOOLEAN) on a different datatype (BIT)
i.e. WHERE BITFIELD IS TRUE wouldn't have worked with sql server 7 BIT fields anyway, right?
rudy|||Hey...pretty exotic...
http://www.brazil.travelmall.com/travelmall/region/Brasillia+and+Goias
Got any 6 month consulting gigs opening up?
Sounds like you'll need a hand or two...|||Hmmm. This is from that link:
Here are some points that will make your trip more pleasant:
1) Take good care in your personal hygiene and dress to avoid ridicule and to fit in with the locals.
Never been to good with that "dress to avoid ridicule" thing... ;-)|||I liked this one
Generally be wary of what you do to avoid offending or causing trouble.|||Hmm. They must have a big sign out front that says "No Americans allowed".|||HEY!
I resemble that remark!|||http://docs.zf.jcu.cz/sql1992.txt
"8.12 <search condition>
...
Format
<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ] "
* it seems to be optional to specify the truth value.
"...
Leveling Rules
1) The following restrictions apply for Intermediate SQL:
a) A <boolean test> shall not specify a <truth value>."
* here's a recommendation.
"...
24)Subclause 8.12, "<search condition>":
a) A <boolean test> shall not specify a <truth value>."
* that's the recommendation again.
"...
12.4 Calls to a <procedure>
...
TYPE;
type BIT is array (NATURAL range <>) of BOOLEAN;"
* here's saying that each bit is a boolean value.
----
This doc reproduces the entire content of ANSI-SQL-92 Standard.
How my english is not at all good, I've transported a part of the doc above to be interpreted.
What it seems to be clear is that BIT and BOOLEAN are synonymous.
This way Sql Server 7 seems to be ANSI-SQL-92 Compliant and Sql Server 2000 is not. I say that because Sql Server 7 works fine with my code, no problems.
For example. If you have a table like this:
Cus_id as Integer, Cus_Name as Varchar(50), Cus_gen as Bit
*Cus_gen: (0) male, (1) female
With some records like these:
1, Rudy, 0
2, Fabiano, 0
3, MCrowley (Ozzy Osbourne), 0
4, Brett Kaiser, 0
5, Suzy, 1
You can query females this way (Sql Server 7):
Select Cus_name Where Cus_gen
But using Sql Server 2000 you'll need to do this way:
Select Cus_name Where Cus_gen = 1
Once the value of "Cus_gen" is a boolean (or bit) you don't need to specify the boolean value, it doesn't make sense, it's true or false, nothing more.
For example, in VB (or any language) to do a loop through the records of a table you do something like that:
<open your recordset>
While Not adoRS.Eof
<do something>
adoRS.MoveNext
Wend
I do not need to specify:
While Not adoRS.Eof = True
or
While adoRS.Eof = False
Or a condition to verify whether a number is odd or even:
If X Mod 2 then
<even>
Else
<odd>
End If
I don't need to do:
If X Mod 2 <> 0 then
And so on. I've been used thus. This way we save a processor time taking out the need of the comparison "<> 0" or "=True" and, at the same time, you let your app smaller.
I don't remeber where I've read about these tips. I'll look for it later. I remember just something about "how you optimize your code" or something like that. I guess was at MS.com, don't remember.
In many cases the bandwidth of 100 Mbits of a huge LAN is not enough to provide a good performance, than we have to use the creativity to improve it. Worse when you working on an eCommerce. I use the standards to develop but I try to maintain my apps the minimun code as possible.
I'm not trying to explain my point of view. I'm trying to know if the way I work is actually a good way or if there's better ways to do it.
Thanks!
Fabiano|||congratulations on digging up the 1992 standard
again, all i can say is, too bad you have to rewrite all your code
want to know how i design male/female columns?
instead of
... Where Cus_gen
i prefer
... Where Cus_gen = 'F'
but that's just me, eh
rudy|||My e-mail is: fabianomaiafranco@.hotmail.com (it's my messenger passport too).
Thanks!!!
Fabiano
The Pretentious ;)
Sunday, February 26, 2012
Does anyone have sql2005 EE running on 64 bit Itanium server?
I don't think mine is using all of the memory it could on the server. I've read that the Task Manager does not give you an accurate measure of the memory usage but 130 MB max does not seem right on a 12 GB server. I've set the minimum to 2 GB in SSMS. The account that is running sqlservice has Lock Pages in Memory privileges. I've turned on AWE in SSMS just to be safe. We are experiencing high CPU usage and my theory is that it is caused my unnecessary I/O due to not being able to cache. The serve has Windows 2003 sp1. The SQL2005 Buffer Manager AWE Lookup maps\sec, stolen maps\sec, unmap calls/sec, unmap pages/sec, write maps/sec are all 0.
Any help would be appreciated. Below is the out from DBCC MEMORYSTATUS. This server also has an instance of sql2000 EE on it with max memory set to 5 GB and it seems to be running fine.
Memory Manager KB
--
VM Reserved 12687344
VM Committed 87304
AWE Allocated 3211008
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
--
VM Reserved 12681712
VM Committed 81752
AWE Allocated 3211008
MultiPage Allocator 19864
SinglePage Allocator 272960
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4240
MultiPage Allocator 4984
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
- --
VM Reserved 12615680
VM Committed 16384
AWE Allocated 3211008
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 1128
(7 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 88
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 672
MultiPage Allocator 96
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
- --
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 320
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
- --
VM Reserved 3904
VM Committed 3904
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1280
MultiPage Allocator 472
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 728
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 112
MultiPage Allocator 320
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 56
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLQERESERVATIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 331616
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3416
MultiPage Allocator 11360
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_OBJCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 69400
MultiPage Allocator 672
(7 row(s) affected)
CACHESTORE_SQLCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 166824
MultiPage Allocator 32
(7 row(s) affected)
CACHESTORE_PHDR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17944
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 456
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1784
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2192
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2800
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 872
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 256
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 168
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 544
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1824
MultiPage Allocator 48
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 288
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
- --
VM Reserved 8192
VM Committed 8192
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 20856
MultiPage Allocator 0
(7 row(s) affected)
Buffer Distribution Buffers
--
Stolen 13626
Free 28271
Cached 33153
Database (clean) 315604
Database (dirty) 2557
I/O 0
Latched 5
(7 row(s) affected)
Buffer Counts Buffers
--
Committed 393216
Target 393216
Hashed 318166
Stolen Potential 297983
External Reservation 28759
Min Free 64
Visible 393216
Available Paging File 2528231
(8 row(s) affected)
Procedure Cache Value
--
TotalProcs 2358
TotalPages 31877
InUsePages 425
(3 row(s) affected)
Global Memory Objects Buffers
--
Resource 257
Locks 2610
XDES 42
SETLS 2
SE Dataset Allocators 4
SubpDesc Allocators 2
SE SchemaManager 273
SQLCache 505
Replication 2
ServerGlobal 48
XP Global 2
SortTables 2
(12 row(s) affected)
Query Memory Objects Value
--
Grants 1
Waiting 0
Available (Buffers) 241218
Maximum (Buffers) 282670
Limit 282670
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 295470
(11 row(s) affected)
Small Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 12800
Maximum (Buffers) 12800
Limit 12800
(5 row(s) affected)
Optimization Queue Value
--
Overall Memory 2581880832
Target Memory 2209619968
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway Value
--
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 500000
Threshold 500000
(6 row(s) affected)
Medium Gateway Value
--
Configured Units 2
Available Units 2
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 row(s) affected)
MEMORYBROKER_FOR_CACHE Value
-- --
Allocations 33157
Rate 2
Target Allocations 301911
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-- --
Allocations 963
Rate 14
Target Allocations 269729
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-- --
Allocations 41452
Rate 21351
Target Allocations 315171
Future Allocations 70667
Last Notification 1
(5 row(s) affected)
What "edition" of 2003 is installed. You need to have Enterprise or Data Center installed for 12gb of RAM. See http://www.microsoft.com/windowsserver2003/evaluation/features/comparefeatures.mspx.Did you set PAE in the Windows boot.ini? If you right click on "My Computer" and select "Properties", how much memory does it show and does it say "Physical Address Extension" after the RAM?
2005 only allocates memory when needed, instead of 2000 which just grabs it all when the service starts.|||
win 2003 EE sp1.
The server doesn't seem to have a boot.ini file. Does 64 bit itanium servers usually have one?
The my computer reading is 11.9 GB of Ram. It does not say "Physical Address Extendsions"
The sql2000 instance is using 6 GB of ram so AWE should be working but it isn't for sql2005.
We use a lot of table variable in 2005 that are stored in memory if it is available an I think sql is thinking there isn't any and writing them to the tempdb instead.
Thanks for responding to my post
|||I forgot in 64bit you don't set PAE, that is only a 32 bit problem.In SQL 2005 tempdb usage was completely changed. It now stores a lot of temp data that 2000 had in RAM, in tempdb.
See: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx|||I read that one yesterday. "System" has locked pages in memory rights. Our instance is running under "Local System" so it should be doing this, right.|||I talked to someone at Microsoft and they said to ignore Task Manager reading (nice). You can tell SQL memory usage by using Performance Monitor object SQL Memory Management counters "Target Server Memory" and "Total Server Memory". So, unless you are a DBA and know about this Task Manager misleads you.
Does anyone have an ForEachColumn Procedure?
Hi
Does anyone have an ForEachColumn Procedure?
(a bit like the MSforeachdb and MSforeachtable sps)
This should be quite generic for anyone working with real life data coming in from other systems.
I need to fill out the rows with missing values in historic records from the previous current record.
(sometimes even from a previous historic record)
And sometimes the current record doesn't have data in the field, so I shouldn't get the data from current -1.
Can solve most of the complexities of the filling out part.
But it needs to be applied for (most of) my 57 columns,and then there is even some differently formatted data just beyond the horizon, so maybe I should try to get a more generic applicable solution.
Did anyone try a ForEachColumnExcept procedure
CREATE PROCEDURE ForEachColumnExcept
@.TableName varchar(200)
, @.ExceptionList varchar(8000) = '' -- the few ones to exclude
, @.DelimterInExceptionlist varchar(10) = ','
AS
...
END
the difficulty here is getting the columns from the databse's data-dictionary
)haven't done that before, but I think I'll find that out.
Or even:
CREATE PROCEDURE ForEachColumnInList
@.TableName varchar(200)
, @.ColumnList varchar(8000) -- all the ones to include
, @.DelimterInColumnList varchar(10) = ','
AS
...
END
Cheers
Drionot sure what u finally want to do with the column list, this will however generate a column list & data type based on params u declared
set @.ExceptionList = ','+ @.ExceptionList + ','
select Column_name,Data_Type from information_schema.columns
where table_name=@.TableName and column_name not in (case when charindex(','+column_name+',',@.ExceptionList) > 0 then column_name else '' end)
Does anyone have an ForEachColumn Procedure?
(a bit like the MSforeachdb and MSforeachtable sps)
This should be quite generic for anyone working with real life data coming in from other systems.
I need to fill out the rows with missing values in historic records from the previous current record.
(sometimes even from a previous historic record)
And sometimes the current record doesn't have data in the field, so I shouldn't get the data from current -1.
Can solve most of the complexities of the filling out part.
But it needs to be applied for (most of) my 57 columns,and then there is even some differently formatted data just beyond the horizon, so maybe I should try to get a more generic applicable solution.
Did anyone try a ForEachColumnExcept procedure
CREATE PROCEDURE ForEachColumnExcept
@.TableName varchar(200)
, @.ExceptionList varchar(8000) = '' -- the few ones to exclude
, @.DelimterInExceptionlist varchar(10) = ','
AS
...
END
the difficulty here is getting the columns from the databse's data-dictionary
)haven't done that before, but I think I'll find that out.
Or even:
CREATE PROCEDURE ForEachColumnInList
@.TableName varchar(200)
, @.ColumnList varchar(8000) -- all the ones to include
, @.DelimterInColumnList varchar(10) = ','
AS
...
END
Cheers
Drionot sure what u finally want to do with the column list, this will however generate a column list & data type based on params u declared
set @.ExceptionList = ','+ @.ExceptionList + ','
select Column_name,Data_Type from information_schema.columns
where table_name=@.TableName and column_name not in (case when charindex(','+column_name+',',@.ExceptionList) > 0 then column_name else '' end)
Tuesday, February 14, 2012
DoCmd.RunSQL uses what library?
I'm in the process of removing all DAO code from a ADP project. I had
a bit of it sprinkled through the thousands of lines of VBA code.
To start with, I'm trying to decide whether or not I have to remove or
change this line of code...
DoCmd.RunSQL "SET NOCOUNT ON"
For one, does RunSQL use DAO? If not, what does it use?
For another, if this does use DAO, what is the appropriate replacement
that uses ADODB?
Thanks!
Maury
RunSQL should be part of the Access object library.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
> I'm in the process of removing all DAO code from a ADP project. I had
> a bit of it sprinkled through the thousands of lines of VBA code.
> To start with, I'm trying to decide whether or not I have to remove or
> change this line of code...
> DoCmd.RunSQL "SET NOCOUNT ON"
> For one, does RunSQL use DAO? If not, what does it use?
> For another, if this does use DAO, what is the appropriate replacement
> that uses ADODB?
> Thanks!
> Maury
a bit of it sprinkled through the thousands of lines of VBA code.
To start with, I'm trying to decide whether or not I have to remove or
change this line of code...
DoCmd.RunSQL "SET NOCOUNT ON"
For one, does RunSQL use DAO? If not, what does it use?
For another, if this does use DAO, what is the appropriate replacement
that uses ADODB?
Thanks!
Maury
RunSQL should be part of the Access object library.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
> I'm in the process of removing all DAO code from a ADP project. I had
> a bit of it sprinkled through the thousands of lines of VBA code.
> To start with, I'm trying to decide whether or not I have to remove or
> change this line of code...
> DoCmd.RunSQL "SET NOCOUNT ON"
> For one, does RunSQL use DAO? If not, what does it use?
> For another, if this does use DAO, what is the appropriate replacement
> that uses ADODB?
> Thanks!
> Maury
DoCmd.RunSQL uses what library?
I'm in the process of removing all DAO code from a ADP project. I had
a bit of it sprinkled through the thousands of lines of VBA code.
To start with, I'm trying to decide whether or not I have to remove or
change this line of code...
DoCmd.RunSQL "SET NOCOUNT ON"
For one, does RunSQL use DAO? If not, what does it use?
For another, if this does use DAO, what is the appropriate replacement
that uses ADODB?
Thanks!
MauryRunSQL should be part of the Access object library.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
> I'm in the process of removing all DAO code from a ADP project. I had
> a bit of it sprinkled through the thousands of lines of VBA code.
> To start with, I'm trying to decide whether or not I have to remove or
> change this line of code...
> DoCmd.RunSQL "SET NOCOUNT ON"
> For one, does RunSQL use DAO? If not, what does it use?
> For another, if this does use DAO, what is the appropriate replacement
> that uses ADODB?
> Thanks!
> Maury
a bit of it sprinkled through the thousands of lines of VBA code.
To start with, I'm trying to decide whether or not I have to remove or
change this line of code...
DoCmd.RunSQL "SET NOCOUNT ON"
For one, does RunSQL use DAO? If not, what does it use?
For another, if this does use DAO, what is the appropriate replacement
that uses ADODB?
Thanks!
MauryRunSQL should be part of the Access object library.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
> I'm in the process of removing all DAO code from a ADP project. I had
> a bit of it sprinkled through the thousands of lines of VBA code.
> To start with, I'm trying to decide whether or not I have to remove or
> change this line of code...
> DoCmd.RunSQL "SET NOCOUNT ON"
> For one, does RunSQL use DAO? If not, what does it use?
> For another, if this does use DAO, what is the appropriate replacement
> that uses ADODB?
> Thanks!
> Maury
Subscribe to:
Posts (Atom)