November 25, 2009 at 5:32 am
Am I getting old, picky and a bit pedantic or others becoming lazy?
I was recently reviewing a (third party) developed database and came across a field defined as nvarchar(1). It would I was told only be holding a “Y” or “N” and that there were no changes that will be made to the field. I asked, somewhat naively, why not use a bit datatype and have the application do the small conversion from Y/N to 1/0 and was told “No-one does that sort of thing anymore – it’s old-hat”.
Have I been wasting my time over the past few years? Or should we be storing three times the amount of data we need to?
OK rant over – back to the rest of the day
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
November 25, 2009 at 6:44 am
ironically a month or two ago i got a ticket to create a table in a non-critical database and one of the columns was for Y/N but i think the person specified a bit column or something like that. i asked for clarification.
i'm in the process of building an administrator DW in SQL 2005 and using bigint in a lot of identity columns. i know it's more memory but i don't want to be bothered with a possible conversion down the road. and hardware is so cheap these days it's amazing. next year our baseline new SQL server purchases will have at least 32GB of RAM and the pricing i've looked at suggests we'll probably just buy it with 64GB.
November 25, 2009 at 6:48 am
I wasn't aware that using the correct data type for a given value was "old fashioned"... well, OK, with all the horrific tables coming out of these ORM tools, maybe it is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2009 at 7:11 am
Personally for a Y/N column, I'd use CHAR(1). Yes, it could be a BIT, but that requires additional logic in every query and unless there are other bit columns in the table it won't save any space. An nvarchar(1) (which takes 4 bytes of space) is most definitely a poor choice.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 25, 2009 at 7:14 am
I generally use bit for that. Would use char(1) if it were to be Y/N.
I got used to using bit fields because my first app used Access as the front end, and checkboxes work nicely on bit fields. Better than a drop-down for Y/N or radio buttons.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 25, 2009 at 8:22 am
I typically use a Char(1) as well. It works nicely for the Y/N or 0/1 or allows for the field to have additional codes added. I have seen needs change in the past and the field needed to change from Y/N for instance to something like Y/N/M (maybe) or something far more abstract.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 25, 2009 at 2:17 pm
I agree with what Gail said and for the very same reasoning. However, I use TINYINT for things like this because it doesn't require any logic at all to translate "Y" to "1" and "N" to "0" if I want to know how many said "Yes". That would seem to support the argument to use the BIT datatype but you can't use a simple SUM on BIT datatypes.
Just avoid (at all costs) column names that require "negative" logic. For example, if I were building a reporting table on about customers for a membership store, I'd never make a column called IsNotMember because that would require some thought on other people's part to make the realization the a "0" means the customer IS a member. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 25, 2009 at 2:23 pm
As long as it's only for Y/N, then Char(1) or NCHAR(1) is fine I suppose. That said - if it's also nullable, you can call it whatever you like - it ACTUALLY is a Nvarchar(1) or varchar(1). Otherwise, good thoughts.
My turkey didn't get pardoned today, so it's due to be drowned then burned by noon tomorrow.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 5, 2010 at 7:44 am
GilaMonster (11/25/2009)
Personally for a Y/N column, I'd use CHAR(1). Yes, it could be a BIT, but that requires additional logic in every query and unless there are other bit columns in the table it won't save any space. An nvarchar(1) (which takes 4 bytes of space) is most definitely a poor choice.
Can't disagree with any of that. But I would add a check constraint to ensure that only the 4 values 'Y' and 'N' could be used (that's two values if for some reason the collation is case sensitive).
This is one of the (rare) cases where use of NCHAR(1) would be wrong (even in the environments I'm used to, where NCHAR is usually mandatory).
Tom
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply