Help with SQL Server (NOT A programming question) [modified]

[Al Ortega]

When people start complain about performance, pretend to be in deep thought over what could be the cause and then suggest you change the char to bit and be a hero!

hth Al

[darkelv]

Or worse, store as integer where feb will forever have 28 days (calculating days in month on the spot using cases not a common function, and the code is spread out in ALL the modules, from A01.exe to A99.exe, plus SQL statement). Yeah, we have one of those. :)

[Jwalant Natvarlal Soneji]

Your right sir! Even a programmer like me also insist boolean simply because of its uses on the front end and ease of managiing inserts, updates and delete and most importantly selects. I have an impression that where expression on bit is faster then chars.

Jwalant Soneji (BE IT) (India) Mobile: +91 9969059127 At Winodows Live Spaces At Yahoo! 360 At BlogSpot

[Jwalant Natvarlal Soneji]

RoswellNX wrote:

naively assuming the DB can be trusted

:) We generally do that, and when found but, try to make the database value insertion more accurate and finding bug for the second time, checking values coming from db.

Jwalant Soneji (BE IT) (India) Mobile: +91 9969059127 At Winodows Live Spaces At Yahoo! 360 At BlogSpot

[djinnigt]

Although there is no absolute statement about a bit field adequately representing a boolean piece of data, 1 being true and 0 being false, that is exactly what the dotnet framework does. I doubt anybody would have used kilobytes to save a document stating something like "thou shalt use bit fields to store boolean data". Frankly, if your DBA won't see the obvious, you need to refer the point to a higher authority (a manager?) for arbitration. If such higher authority also does not see the obvious, then you have three options: (1) be miserable and refactor your business objects (2) fire your manager and hire a new one or (3) go work somewhere else (might be best for your mental health)! Good luck anyway.

[adamAFA46]

I'd look for a new job - surely there's some place to work in your town that's NOT run by a bunch of imbeciles.

[im_srini]

Hi Malhar, the reasons given by your 'DB' guy are lame, but that does not necessarily mean he is wrong, or that you're right. The easiest way is to come up with a simple test script to performance test both data-types. Anyways, I do not think that just because a column is a 'bit-field', SQL-Server will use an exact bit to store it, the bit is stored as part of a byte anyway. You would get lesser storage only if you have more than one bit-field column, where SQL-Server coalesces bits of other bit-field columns also into the same byte. But this coalescing of multiple columns into bytes can get expensive if your application is OLTP, and these columns are frequently updated. You also have the complications of NULLs - are the columns in question NULLable ? And if a bit-field column is NULLable, it couldn't still occupy only a single bit, could it ? :)

[PugwashXP]

Hi Mahlar Try http://groups.google.co.uk/group/microsoft.public.sqlserver/browse_thread/thread/d537c5669f83cf63/8207c4315bea2613?lnk=st&q=bit+versus+char(1)+for+boolean+in+SQL+server&rnum=2&hl=en#8207c4315bea2613[^] Because it's so obvious then it's hard to find arguments. The origin of this seems to be where a database is based on early technology where bit was either not available or optimised (early MySQL for example). It seems as if te DBA's only arguement is readability. As well as the above you also have no need to index, efficiency, supportable, easier to maintain, more scaleable and .... Good luck - you deserve to win Peter Smith

[SimulationofSai]

malharone wrote:

I am baffled by the suggestion of the DBA. In my past experience, I've never heard of such a wild idea.

This is not the first time i've heard of this. I'd not jump to the conclusion that the DBA is dumb. Atleast 2 good reasons for this are 1) A single BIT column in a table would still take 1 byte of storage. It's only when there are multiple BIT columns does SQL Server put those into a single byte

malharone wrote:

When an admin looks at the table, 'Y','N','y','n' are more obvious than 0 and 1.

2. This depends on who's ultimately going to access the DB and it's contents. I've seen applications that give end users a Query Analyzer type UI where they can construct SQL queries on their own to retrieve data. In such cases, a 'Y' or 'N' would be more meaningful to the operator, who might not be aware of the BIT column or it's intricacies. 3) This is a long shot, but still applicable. SQL Server 2005 implicitly converts True to 1 and False to 0, while all application data layers actually pass 0 or 1 when accessing a BIT column. This is ambiguous in itself. You could either send a True or 1, and if an application is DB incongnizant, this can lead to breakage. 4) Even BIT variables declared in T-SQL are implicitly converted to an INT with check clause. This is because the DB engine cannot store a domain value in a single BIT. The best arguments you can put forth are 1) BIT's are faster in seek and scans, because they fall under the "Precise" data types. 2) They are language, collation independent 3) They're implicitly constrained to accept only 2 values. If the check clause in the char column is dropped accidentally, the values get screwed up. 4) You can apply boolean operators on them without conversion. This is extremely useful if you're dealing with time series data and use a lot of "where" conditions in your SQL Good Luck.

SG Cause is effect concealed. Effect is cause revealed.

[Abhinav Tickoo]

If you use boolean type to store the boolean type data in SQL 2005 i think the data displayed in tables would be "True"/"False" which should be pretty understandable for your DBA as well as internally the data is maintained as 0 and 1 that makes your comparisons easier and you need not change the queries. hardrock

[Paul Watson]

Oh dear, you poor sap. There won't be much supporting documentation because it, your way, is so obviously the right way. I'd make my point in an email for historic reasons and then move on. You can only fall out about this, fight other battles rather.

regards, Paul Watson Ireland & South Africa

Shog9 wrote:

And with that, Paul closed his browser, sipped his herbal tea, fixed the flower in his hair, and smiled brightly at the multitude of cute, furry animals flocking around the grassy hillside where he sat coding Ruby on his Mac...

[jackal9501]

Yeah and probably the year as a char(2)!!!!:laugh: Seriously, let HIM see this post and all his friends too and when he walks past talked in hushed tones and point at him. See how long he stays. :)

[wultepjo]

Using a bit is fast en accepts only 0/1 (true or false visualised) and takes less space, surely if there are more bit fields in the table. In the front end is no error handling needed in case of using checkboxes. Using constraints produces overhead. Depending on the number of data written it can slow down data traffic. It slows down you app to because you have to do error handling on all booleans. More code needed! All the lost time are milliseconds but is a waste of time, also in developing. To my opinion you should use the correct datatypes. Someone giving me a work with char in stead of bit would loose a serious number of points. Storing int as char or varchar is even crazier. Int’s are meant for calculating are very fast in comparing to others, in search and for linking tables. It needs also less space.

[Marco Turrini]

May I post my resumé? :) I use bit for booleans, tinyint for enums and smalldatetime for datetime when seconds are irrilevant. Oh, and I use to unlock the office in the morning and lock it at night (but no, I don't clean it):-D

Marco Turrini

[Marco Turrini]

malharone wrote:

Furthermore, the "varchar" for boolean strangely made into the "SQL Standards" document in the company

1. Leave that company as soon as possible 2) Using char(1) for a boolean is stupid enough, but using varchar is even worse: varchars are introduced by two bytes used to specify the actual length of the data in the row, so a varchar(1) is actually consuming 3 bytes, not 1 as you would probably expect; on the other hand, bit fields use just 1 bit (and, if memory doesn't fail me, Sql Server can group them in bytes) 3) Using [var]char introduce wide possibilities for unnecessary errors and runtime exceptions.

Marco Turrini

[Jonas Hammarberg]

Sounds like it's time to move on -- "If you can't beat them, get out". rgds /Jonas

[Lost User]

That's so true. Bit are seen as True or False in Enterprise Manager. ORRRRRRRRR He should just make HIS own queries to see the data that says something like.... (CASE WHEN 0 THEN 'Please note DBA.. This is FAAALLLSSEEEE' WHEN 1 THEN 'This is TRUEEE DBAAA, TRUEEE' END) AS MyStupidDBAHelper HAHAHAHA

[Dave Thomson]

I agree on all points although I'll add that SQL Server will use a whole byte for a bit field and so a dba might consider having only one bit field as wasteful or something. I think the original post stated char(1) so the end result would be comparable but using bit would allow for 7 more bit fields in the table without any row size increase but the same 7 extra char(1) would add 7 more bytes, no brainer for me. Personally I use bit for boolean every time.

[kris allberry]

I agree with the Bit argument, but there is also anm argument for using a char(1). we use chars, and yes its been like it since before i started the compant, and it has cases the odd problem. but by using a char it makes the fiels nicely expandable. i just reciently in the last couple of days had to add a 3rd valud to a Y/N field for one custoner. so the database schema is shared, and now one customer has Y/N/X dont ask, but you know customers. also ive uses Y/N/[null] as a was of saying it is yes or no, but null is not set. so there can be time where this is aplicable, and like someone else said. the cost of a char is no more or less, and every /Net control can use what ever value you want, so no hard no foul. (till some newbie f's thing up by of cause). oh love the comment about storing the enum name thats class!!

[jgrogan]

malharone wrote:

So anyways ... any suggestions?

You could just tell him that you'll have to re-write all the existing code to support y/n and it'll probably take a month. This works best if it's done in front of his boss - shame him into submission.