Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Design Expand / Collapse
Author
Message
Posted Friday, September 24, 2010 3:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
UMG Developer (9/23/2010)
In addition the OrderNo column is NOT an identifier because it does not have a constraint defined on it to prevent duplicate values, or at least the DDL shown doesn't show a constraint.

Hm, not sure about this one. I tend to say that it actually IS an identifier column, because it has the IDENTITY property set. The fact that you can create an identity column without truly enforcing uniqueness is not very helpful of course.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/e1a7a238-4953-459b-af50-50f5c2b4878e.htm

Similar quote for ROWGUIDCOL:


ROWGUIDCOL
Specifies that the column is a row globally unique identifier column. ROWGUIDCOL can only be assigned to a uniqueidentifier column, and only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. ROWGUIDCOL cannot be assigned to columns of user-defined data types.

ROWGUIDCOL does not enforce uniqueness of the values stored in the column. Also, ROWGUIDCOL does not automatically generate values for new rows that are inserted into the table. To generate unique values for each column, either use the NEWID function on INSERT statements or specify the NEWID function as the default for the column. For more information, see NEWID (Transact-SQL)and INSERT (Transact-SQL).

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a1742649-ca29-4d9b-9975-661cdbf18f78.htm


P.S. --> As already denoted by Hugo, Documentation, especially BOL, is not always a 100% correct or precise. Therefore it probably "depends" as usual.


Best Regards,
Chris Büttner
Post #992563
Posted Friday, September 24, 2010 3:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 6,048, Visits: 8,328
Rune Bivrin (9/24/2010)
However, looking the table in the question I'd probably remove the NULL-ability on the cell phone numbers. Nullable VARCHAR often doesn't make sense. Particularly when they are entered in a classic GUI. How do you diffrentiate between a NULL phone number and a zero-length phone number?

See the reply by Toreador to see why the distinction between zero-length string and NULL can be very important.

As to the GUI - though I'd never call the "edit table" interface in SSMS an example of a good GUI, it does show you one way to handle this. A NULL is displayed as NULL (in italics, to show the difference with the string value 'NULL'), where a zero-length string is displayed as an empty cell. To change a value to a zero-length string, you select the contents and hit the Backspace or Deletet key. To change a value to NULL, you hit (IIRC) Ctrl-0.

I would not use the token NULL in an interface for end users, and I'd probably favor a button over a hard-to-remember key combo for setting a value to NULL, but you get the general idea.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #992565
Posted Friday, September 24, 2010 3:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
I guessed and got the question right just by using common sense without looking anything up in BOL.

The only complaint I have is that it should be worth more than 1 point because it's a multiple selection question and those are always harder to guess correctly.

If you were to completely guess a question with 5 radio buttons, the probability of you getting it correct is 20%. If you have 5 check boxes instead, the probability is 50%^5 or 3.125%. Or, thinking about it a different way, there are 32 separate combinations of answering that question so the chances are 1/32 or 3.125%.


Post #992566
Posted Friday, September 24, 2010 3:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 3,067, Visits: 955
Toreador (9/24/2010)
Rune Bivrin (9/24/2010)
However, looking the table in the question I'd probably remove the NULL-ability on the cell phone numbers. Nullable VARCHAR often doesn't make sense. Particularly when they are entered in a classic GUI. How do you diffrentiate between a NULL phone number and a zero-length phone number?


Surely in this case, an empty string means that they don't have a cell phone, NULL means you don't know whether they have or not.

NULL could also mean that I know they have a cell phone, but I don't know the number.

Exactly how often do you encounter a GUI where there's a little check box labeled "Don't know if there's a cell phone"? In a GUI it's just a text box with no text in it, and most of the time it doesn't matter why it's empty because all that's done with the contents is display it in reports and dialog boxes. In those cases NULL is just an annoying special case that adds no value.



Just because you're right doesn't mean everybody else is wrong.
Post #992569
Posted Friday, September 24, 2010 3:20 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
Rune Bivrin (9/24/2010)
Toreador (9/24/2010)
Rune Bivrin (9/24/2010)
However, looking the table in the question I'd probably remove the NULL-ability on the cell phone numbers. Nullable VARCHAR often doesn't make sense. Particularly when they are entered in a classic GUI. How do you diffrentiate between a NULL phone number and a zero-length phone number?


Surely in this case, an empty string means that they don't have a cell phone, NULL means you don't know whether they have or not.

NULL could also mean that I know they have a cell phone, but I don't know the number.

Exactly how often do you encounter a GUI where there's a little check box labeled "Don't know if there's a cell phone"? In a GUI it's just a text box with no text in it, and most of the time it doesn't matter why it's empty because all that's done with the contents is display it in reports and dialog boxes. In those cases NULL is just an annoying special case that adds no value.


Oh it could be important. Could be an indication that the person inputting the data skipped something and should be reprimanded. Say you have a series of forms that should be filled in to create a new customer. One of those forms contain the cell phone number. Now since the value is null that could mean that one form was skipped which could be bad.

/T
Post #992571
Posted Friday, September 24, 2010 3:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 1,782, Visits: 6,485
Rune Bivrin (9/24/2010)
most of the time it doesn't matter why it's empty because all that's done with the contents is display it in reports and dialog boxes. In those cases NULL is just an annoying special case that adds no value.


Most of the time, but not all.

You might have a table of staff information with no cell phone column, but need to add one. You add it as a NULL column meaning "we don't know yet", and ask users to provide the information when they next log in, and which point it's updated to either the number, or an empty string if they don't have one. Or if they don't answer (the ophone is at home and they don't know the number) it's left as NULL and they're asked again next time they log in.

If you don't have NULL columns then for this example you need a Not Null cell phone column, plus a not null bit column to indicate whether the question needs to be asked again.
Post #992572
Posted Friday, September 24, 2010 3:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 6,048, Visits: 8,328
Rune Bivrin (9/24/2010)
Exactly how often do you encounter a GUI where there's a little check box labeled "Don't know if there's a cell phone"?

If the distinction between "empty string" and "value is missing" (not necessarily unknown!) is important to the business, then the front end designers better make sure that there is some (preferably intuitive) way to make that distinction when entering data.
If it's not important, then the database developer should permit only one case (either by a NOT NULL constraint, or by a CHECK (column <> '') constraint, depending on what the reason is for leaving the column empty and what the expected behaviour is.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #992574
Posted Friday, September 24, 2010 3:25 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 3,067, Visits: 955
Hugo Kornelis (9/24/2010)

See the reply by Toreador to see why the distinction between zero-length string and NULL can be very important.

As to the GUI - though I'd never call the "edit table" interface in SSMS an example of a good GUI, it does show you one way to handle this. A NULL is displayed as NULL (in italics, to show the difference with the string value 'NULL'), where a zero-length string is displayed as an empty cell. To change a value to a zero-length string, you select the contents and hit the Backspace or Deletet key. To change a value to NULL, you hit (IIRC) Ctrl-0.

I would not use the token NULL in an interface for end users, and I'd probably favor a button over a hard-to-remember key combo for setting a value to NULL, but you get the general idea.

Sure. In the cases where the NULL would actually add value AND you can sell the concept to the users that they need to distinguish between the two.

It's back to avoiding unneccesary NULL:s. If you need them by all means use them. Just make sure that you have good a motivation why a column should be NULL-able.

I'm not a full time DBA; more of a developer who just happen to be heavily involved in modelling, and I can tell you that nullability is more of a headache in the application code than it is in the database and SQL code. If I hade a cent for every time an application blew up because a value was suddenly and unexpectedly NULL I'd be vacationing in the Bahamas right now.



Just because you're right doesn't mean everybody else is wrong.
Post #992575
Posted Friday, September 24, 2010 3:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 6,048, Visits: 8,328
Rune Bivrin (9/24/2010)
It's back to avoiding unneccesary NULL:s. If you need them by all means use them. Just make sure that you have good a motivation why a column should be NULL-able.

Exactly! I object to the advise to "avoid NULLs" (as given in Books Online). But I'll be the first that attempts to design without NULLs are not even half as bad as designs where every column allows NULLs. (And unfortunately, the latter is far more common than the former).

I hardly do any front-end work at all, I am mostly database developer and somewhat DBA, but I've done and seen enough to know that NULLs are, indeed, a pain in 3GL and OO languages. (Though most application problems do not come from the handling of NULLs being hard, but from the app developers having no clue)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #992584
Posted Friday, September 24, 2010 3:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:04 AM
Points: 176, Visits: 567
Isn't it if a field does not allow nulls, then a person can never go home until he gets the one small detail entered into the row. I imagine this can be avoided by having another reference table which adds another row for 'phone' when it is needed.
Post #992588
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse