June 25, 2012 at 10:10 am
Oh, wait. Got another one.
Someone uses INT for a True-False / Yes-No / 0-1 column instead of BIT.
June 25, 2012 at 10:14 am
Brandie Tarvin (6/25/2012)
Stefan Krzywicki (6/25/2012)
Revenant (6/25/2012)
Sean Lange (6/25/2012)
Stefan Krzywicki (6/25/2012)
Don't you love columns with "number" in the name that are typed as char or varchar?Or the date ones...
InsertedDateTime int
BAH!!!
There should be a stretch of hard labor for crimes like this.
Or for having leading 0s in your data.
I can actually see the leading zeros because of certain business scenarios, but all the others, and Jason's Varchar(1)? The offenders are sentenced "to the pain" by porkchop launcher.
My "favorite" is people (not DBAs) who insist on using GUIDs because that's the thing that everyone does, and not because they actually need unique identifiers.
That and people who use character types for money / date storage, then wonder why they can't use math and date functions on the column, or why it won't convert properly. (How did ABC get into my date column?)
The "business scenario" that resulted in leading zeroes is simply letting business people assign numbers to things. Naturally they didn't bother checking to see if there was already a method.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 25, 2012 at 10:15 am
L' Eomot Inversé (6/20/2012)
The thread seems very quiet. I've been away three days and there are only 62 new posts on it.But I may not find the energy to read even those few posts.
Life has suddenly changed. Our daughter is in hospital and very unlikely to come out again. We originally thought it was something that that would be fixed quickly, and didn't worry much, but the medical team discovered it was not quite so simple late last week. Ann and I flew back to the UK on Friday night and I spoke to Nikki at the hospital by phone on Saturday, and she seemed fairly OK, so I planned to go up and see her on Monday. Then on Sunday I got a call from the hospital - it wasn't just not quite so simple, but a good deal worse. I drove up on Monday morning and spent most of 3 days visiting there, mostly with Nikki but also talking to the medical team. Early this afternoon teh medical team's view changed from "difficult to deal with" to "not curable, only limited palliative measures possible, unlikely to be able to return home". Her mother, Ann, was too ill to travel on Monday so I came back this afternoon to pick her up and will drive back up there with her tomorrow, so that she too can visit.
I'll add to the prayers and sympathies on this.
I would like to recommend that you get a hold of a Dianetics auditor. I've seen "no possible recovery" per doctors be turned into "we don't understand it so we'll call it spontaneous remission/cure" many times. And, if the worst comes to be, we're very good at grief counselling as well (as rough as it may be to confront that as a result of the situation, it's better to confront and deal with than not to). Many will work with you for free, if you so desire.
I wish I could be there to help. I've gotten many people through these kind of things. While I can't be there, there are almost certainly others who can. If you need help finding someone, and are willing to give it a try, please let me know.
- 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
June 25, 2012 at 10:19 am
SQLRNNR (6/25/2012)
Sean Lange (6/25/2012)
Stefan Krzywicki (6/25/2012)
Don't you love columns with "number" in the name that are typed as char or varchar?Or the date ones...
InsertedDateTime int
BAH!!!
Or VarChar(1)
Wait though - is it nullable? Because in that case CHAR(1) makes no sense.....
----------------------------------------------------------------------------------
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?
June 25, 2012 at 10:22 am
Matt Miller (#4) (6/25/2012)
SQLRNNR (6/25/2012)
Sean Lange (6/25/2012)
Stefan Krzywicki (6/25/2012)
Don't you love columns with "number" in the name that are typed as char or varchar?Or the date ones...
InsertedDateTime int
BAH!!!
Or VarChar(1)
Wait though - is it nullable? Because in that case CHAR(1) makes no sense.....
But does a nullable CHAR(1) take an additional 2 bytes of storage to store the length?
June 25, 2012 at 10:23 am
Matt Miller (#4) (6/25/2012)
SQLRNNR (6/25/2012)
Sean Lange (6/25/2012)
Stefan Krzywicki (6/25/2012)
Don't you love columns with "number" in the name that are typed as char or varchar?Or the date ones...
InsertedDateTime int
BAH!!!
Or VarChar(1)
Wait though - is it nullable? Because in that case CHAR(1) makes no sense.....
Why would NULL matter?
CHAR(1) doesn't have extra overhead for the flexibility of string length. VARCHAR(1) adds that extra string length flexibility, therefore has added overhead. In a apples to apples comparison, there's no reason to use VARCHAR(1) with or without column nullability issues.
EDIT: 2 bytes, as Lynn said. And add that per ROW, so for 1 million rows, 2 million extra bytes...
June 25, 2012 at 10:24 am
Brandie Tarvin (6/25/2012)
Matt Miller (#4) (6/25/2012)
SQLRNNR (6/25/2012)
Sean Lange (6/25/2012)
Stefan Krzywicki (6/25/2012)
Don't you love columns with "number" in the name that are typed as char or varchar?Or the date ones...
InsertedDateTime int
BAH!!!
Or VarChar(1)
Wait though - is it nullable? Because in that case CHAR(1) makes no sense.....
Why would NULL matter?
CHAR(1) doesn't have extra overhead for the flexibility of string length. VARCHAR(1) adds that extra string length flexibility, therefore has added overhead. In a apples to apples comparison, there's no reason to use VARCHAR(1) with or without column nullability issues.
VARCHAR(1) = Scary DBA Wrath
I just go ballistic. It's a wonder that the logical modeling team at my previous employer didn't pay for a hit man.
"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
June 25, 2012 at 10:35 am
Brandie Tarvin (6/25/2012)
Matt Miller (#4) (6/25/2012)
SQLRNNR (6/25/2012)
Sean Lange (6/25/2012)
Stefan Krzywicki (6/25/2012)
Don't you love columns with "number" in the name that are typed as char or varchar?Or the date ones...
InsertedDateTime int
BAH!!!
Or VarChar(1)
Wait though - is it nullable? Because in that case CHAR(1) makes no sense.....
Why would NULL matter?
CHAR(1) doesn't have extra overhead for the flexibility of string length. VARCHAR(1) adds that extra string length flexibility, therefore has added overhead. In a apples to apples comparison, there's no reason to use VARCHAR(1) with or without column nullability issues.
EDIT: 2 bytes, as Lynn said. And add that per ROW, so for 1 million rows, 2 million extra bytes...
Because the CHAR data type is defined as a fixed length type. What's the "length" of NULL?
We have enough issues with folks beating up on NULL, I really don't see the value in making it even harder by messing up our data types.
Also for what it's worth, BOL states that nullable CHAR column are TREATED as varchar. You're not saving any bytes either way.
If ANSI_PADDING is OFF when a char NULL column is created, it behaves like a varchar column with ANSI_PADDING set OFF: trailing blanks are truncated.
----------------------------------------------------------------------------------
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?
June 25, 2012 at 10:42 am
Brandie Tarvin (6/25/2012)
Why would NULL matter?
CHAR(1) doesn't have extra overhead for the flexibility of string length. VARCHAR(1) adds that extra string length flexibility, therefore has added overhead. In a apples to apples comparison, there's no reason to use VARCHAR(1) with or without column nullability issues.
EDIT: 2 bytes, as Lynn said. And add that per ROW, so for 1 million rows, 2 million extra bytes...
Point #2: what if the UI gave you back and EMPTY string (i.e. '')? Are we changing what we get back? Are we preventing that from being saved?
I get that most times it should NOT be used, but the old saber of damocles every time you see it just doesn't wash IMO.
----------------------------------------------------------------------------------
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?
June 25, 2012 at 10:44 am
Matt Miller (#4) (6/25/2012)
Because the CHAR data type is defined as a fixed length type. What's the "length" of NULL?
One byte in the case of a char(1)
Char(1) - 1 byte if null, 1 byte if not null.
Varchar(1) - 0 bytes if null, 3 bytes if not null.
If enough of that column is null for that to save space, make it a char(1) sparse.
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
June 25, 2012 at 10:44 am
Matt Miller (#4) (6/25/2012)
Brandie Tarvin (6/25/2012)
Matt Miller (#4) (6/25/2012)
SQLRNNR (6/25/2012)
Sean Lange (6/25/2012)
Stefan Krzywicki (6/25/2012)
Don't you love columns with "number" in the name that are typed as char or varchar?Or the date ones...
InsertedDateTime int
BAH!!!
Or VarChar(1)
Wait though - is it nullable? Because in that case CHAR(1) makes no sense.....
Why would NULL matter?
CHAR(1) doesn't have extra overhead for the flexibility of string length. VARCHAR(1) adds that extra string length flexibility, therefore has added overhead. In a apples to apples comparison, there's no reason to use VARCHAR(1) with or without column nullability issues.
EDIT: 2 bytes, as Lynn said. And add that per ROW, so for 1 million rows, 2 million extra bytes...
Because the CHAR data type is defined as a fixed length type. What's the "length" of NULL?
We have enough issues with folks beating up on NULL, I really don't see the value in making it even harder by messing up our data types.
Also for what it's worth, BOL states that nullable CHAR column are TREATED as varchar. You're not saving any bytes either way.
If ANSI_PADDING is OFF when a char NULL column is created, it behaves like a varchar column with ANSI_PADDING set OFF: trailing blanks are truncated.
I just ran this as a test:
SET ANSI_PADDING OFF;
GO
CREATE TABLE dbo.CharTest1 (
C1 CHAR(1) NULL);
GO
CREATE TABLE dbo.CharTest2 (
C1 CHAR(1) NOT NULL);
GO
INSERT INTO dbo.CharTest1 (C1)
SELECT 'A'
FROM dbo.Numbers;
INSERT INTO dbo.CharTest2 (C1)
SELECT 'A'
FROM dbo.Numbers;
-- checked the table storage size here for both
TRUNCATE TABLE dbo.CharTest1;
INSERT INTO dbo.CharTest1 (C1)
SELECT NULL
FROM dbo.Numbers;
-- rechecked storage size
SQL 2008 R2.
Same storage size for all three permutations. 10,001 rows for all tests.
- 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
June 25, 2012 at 11:00 am
GilaMonster (6/25/2012)
Matt Miller (#4) (6/25/2012)
Because the CHAR data type is defined as a fixed length type. What's the "length" of NULL?One byte in the case of a char(1)
Char(1) - 1 byte if null, 1 byte if not null.
Varchar(1) - 0 bytes if null, 3 bytes if not null.
If enough of that column is null for that to save space, make it a char(1) sparse.
Not asking at the physical level. Logically, if something is NULL, it has no length: how can that concept reconcile with a "fixed" or defined length column? Are we now saying it's defined length some of the times, and other times not?
Nullable CHAR(N) regardless of length make no sense, IMO. never mind if you might save a couple of byte of physical storage.
----------------------------------------------------------------------------------
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?
June 25, 2012 at 11:08 am
Matt Miller (#4) (6/25/2012)
GilaMonster (6/25/2012)
Matt Miller (#4) (6/25/2012)
Because the CHAR data type is defined as a fixed length type. What's the "length" of NULL?One byte in the case of a char(1)
Char(1) - 1 byte if null, 1 byte if not null.
Varchar(1) - 0 bytes if null, 3 bytes if not null.
If enough of that column is null for that to save space, make it a char(1) sparse.
Not asking at the physical level. Logically, if something is NULL, it has no length: how can that concept reconcile with a "fixed" or defined length column? Are we now saying it's defined length some of the times, and other times not?
Nullable CHAR(N) regardless of length make no sense, IMO. never mind if you might save a couple of byte of physical storage.
Actually, if you define "NULL" as "unknown value" as opposed to "no value", then one could define a nullable char(1) column as "we may not know what value it should have, but if we did, we know it would be 1 character". "NULL" is supposed to mean "unknown value", so it's logically sound to think of it that way.
(We're getting technical on The Thread. Steve must be on vacation. Checking the editorials ... yep, looks like.)
- 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
June 25, 2012 at 11:12 am
How about those Mets?
Oh, wait, wrong thread for that too, sorry! 😛
June 25, 2012 at 11:14 am
GSquared (6/25/2012)
Actually, if you define "NULL" as "unknown value" as opposed to "no value", then one could define a nullable char(1) column as "we may not know what value it should have, but if we did, we know it would be 1 character". "NULL" is supposed to mean "unknown value", so it's logically sound to think of it that way.(We're getting technical on The Thread. Steve must be on vacation. Checking the editorials ... yep, looks like.)
I belong to the school that NULL represents the absence of value, so it's not even an "unknown value".
You're right though - I will relinquish the soapbox, and let the thread go back to non-technical :). No need to revisit old grounds here.
----------------------------------------------------------------------------------
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?
Viewing 15 posts - 36,646 through 36,660 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply