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

[PIEBALDconsult]

Or, worse, integers. The third-party products I have to use have so many different ways of doing the same things.

[Colin Angus Mackay]

malharone wrote:

So as of now, I look like a clueless idiot!

Then show management this thread with lots of highly respected professionals disagreeing with your DBA.

---

Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website

[Miszou]

From SQL Server Books Online (SQL Server 2000 documentation): bit Consists of either a 1 or a 0. Use the bit data type when representing TRUE or FALSE, or YES or NO. For example, a client questionnaire that asks if this is the client's first visit can be stored in a bit column From SQL Server 2005 Documentation: The Microsoft SQL Server 2005 Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on. The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0. I don't think you're going to find much in the way of documentation to support your argument, since it's so obvious - kind of like asking why we don't store numbers in varchar fields... :doh: Good Luck :)

---

Sunrise Wallpaper Project | The StartPage Randomizer | The Windows Cheerleader

[Marc Clifton]

malharone wrote:

So anyways ... any suggestions?

Yeah. Tell him that dba is only one letter away from daa - dumb-assed administrator. Marc

Thyme In The Country
Interacx
My Blog

[Paul Conrad]

I go with true/false. The other way has the potential to cause trouble down the road.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

[Lost User]

My limitied experiance has tought me that somethings are just not worth the effort of arguing about. A bit field would probably be better but his solution will work as well. Save your energy for bigger issues

[Kelly Herald]

It looks like that DBA learned by using MS Access X| Access uses the Y/N values for its boolean fields.

Kelly Herald Software Developer

[_Damian S_]

I agree... just make sure you have sent an email etc to the appropriate people letting them know that you disagree with the design decision, but have been overruled by the senior person. Make sure you keep a copy yourself. That way, when it all turns to crap, you have *some* protection for your backside...

------------------------------------------- Don't walk in front of me, I may not follow; Don't walk behind me, I may not lead; Just bugger off and leave me alone!!

[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. :)