November 24, 2015 at 8:01 am
Hany Helmy (11/24/2015)
Yes valid, but definitely not recommended, I hope that MS will prevent this code from being created by mistake in future releases.
How would you propose to do that, without affecting other valid, probably not recommended either, but sometimes necessary or useful forms?
Unnecessary restriction is no substitute for due diligence in coding and review.
November 24, 2015 at 8:05 am
Hugo Kornelis (11/24/2015)
The correct answer is missing: "The table doesn't have a primary key". It's also suspect that all columns are nullable.From the answer options I deduced that I needed to focus on the table name only; perhaps you can change the question text to reflect that?
A primary key is not a requirement for a table; nor is a non-nullable column.
Absent a specified coding standards document, a table with no primary key and all nullable columns is not inherently unacceptable. You may not like it, but your preference (understandable though it may be) doesn't make this table definition wrong.
November 24, 2015 at 8:10 am
sknox (11/24/2015)
Hugo Kornelis (11/24/2015)
The correct answer is missing: "The table doesn't have a primary key". It's also suspect that all columns are nullable.From the answer options I deduced that I needed to focus on the table name only; perhaps you can change the question text to reflect that?
A primary key is not a requirement for a table; nor is a non-nullable column.
Absent a specified coding standards document, a table with no primary key and all nullable columns is not inherently unacceptable. You may not like it, but your preference (understandable though it may be) doesn't make this table definition wrong.
It's not preference, it's simply good design.
The design of the table is wrong and no real table should have it.
The syntax is completely correct.
November 24, 2015 at 8:17 am
Luis Cazares (11/24/2015)
It's not preference, it's simply good design.
Agreed.
Luis Cazares (11/24/2015)
The syntax is completely correct.
As Luis pointed out, that is the whole point of the question. We could argue good design from now until eternity, but not syntax. The syntax works.
November 24, 2015 at 8:45 am
sknox (11/24/2015)
Hugo Kornelis (11/24/2015)
The correct answer is missing: "The table doesn't have a primary key". It's also suspect that all columns are nullable.From the answer options I deduced that I needed to focus on the table name only; perhaps you can change the question text to reflect that?
A primary key is not a requirement for a table; nor is a non-nullable column.
Absent a specified coding standards document, a table with no primary key and all nullable columns is not inherently unacceptable. You may not like it, but your preference (understandable though it may be) doesn't make this table definition wrong.
A table with no primary key is simply unacceptable in a relational database. It is unfortunate that SQL Server and many other so-called RDBMSs allow such a thing while calling themselves relational database management systems.
Since all columns being nullable precludes having a primary key, that is not acceptable in a relational database either.
So I tend to agree with Hugo on this one, although I think the title ("the dotted table") indicated quite clearly that those isuere not what the question was about, so this question must have been about T-Sql tables (hence not constrained to be about a table representing a relation as defined in the relational model).
Tom
November 24, 2015 at 8:47 am
don't drop your framework or view on table design.
If I'm staging data here, pulling from unclean sources, do I need a PK or non-nullable columns? It's a perfectly valid design, depending on requirements.
While I would say this is not recommended, I'd also be hesitant to say this would never pass a code review. After 25 years in this business, I am wary of always or never anything.
November 24, 2015 at 12:00 pm
Steve Jones - SSC Editor (11/24/2015)
don't drop your framework or view on table design.If I'm staging data here, pulling from unclean sources, do I need a PK or non-nullable columns? It's a perfectly valid design, depending on requirements.
While I would say this is not recommended, I'd also be hesitant to say this would never pass a code review. After 25 years in this business, I am wary of always or never anything.
Yeah but isn't that using a butter knife as a screwdriver? Certainly you can, and many people and organizations do, use the database server as the staging area in an ETL or similar process, but that is not what an RDBMS is all about.
Also, never use absolutes!
Don Simpson
November 24, 2015 at 12:59 pm
sknox (11/24/2015)
Hugo Kornelis (11/24/2015)
The correct answer is missing: "The table doesn't have a primary key". It's also suspect that all columns are nullable.From the answer options I deduced that I needed to focus on the table name only; perhaps you can change the question text to reflect that?
A primary key is not a requirement for a table; nor is a non-nullable column.
Technically speaking, a primary key is not a requirement. But a candidate key is, as a result of Codd's second rule (the guaranteed access rule).
The fact that SQL Server allows non-relational tables to be created does not make it correct.
November 24, 2015 at 1:55 pm
DonlSimpson (11/24/2015)
Yeah but isn't that using a butter knife as a screwdriver? Certainly you can, and many people and organizations do, use the database server as the staging area in an ETL or similar process, but that is not what an RDBMS is all about.
This is exactly what you'd want to do in a relational database when you are loading a warehouse. Not always, but often. There are advantages to manipulating batches of data inside an RDBMS rather than in a tool. I'd argue it's not a butter knife as a screwdriver at all
November 25, 2015 at 5:41 am
sknox (11/24/2015)
Hany Helmy (11/24/2015)
Yes valid, but definitely not recommended, I hope that MS will prevent this code from being created by mistake in future releases.How would you propose to do that, without affecting other valid, probably not recommended either, but sometimes necessary or useful forms?
Unnecessary restriction is no substitute for due diligence in coding and review.
For example to allow only single period in Table Name.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
November 25, 2015 at 6:48 am
I tend to agree that the question should be 'What is syntactically wrong with this table?'
Because the syntax may work, the table in question is wrong on so many level it borders on funny. Dots in the name, no PK, table and column names just bad in general...
November 25, 2015 at 9:16 am
They have not made a manual large enough with which to beat the developer / DBA who would create a table like this in Production.
November 25, 2015 at 9:19 am
DonlSimpson (11/24/2015)
Also, never use absolutes!
The only rule of thumb I use is "rules of thumb are usually wrong"
November 25, 2015 at 7:47 pm
Recce70 (11/25/2015)
They have not made a manual large enough with which to beat the developer / DBA who would create a table like this in Production.
Why would I want to beat a developer with a manual just because he writes a non-relational table definition? T-SQL is not a relational calculus, it has many non-relational extensions. SQL Server is not restricted to supporting purely relational databases, it supports non-relational constructs too. Situations in which it is useful to go non-relational (Steve's example of staging data imports is a good one) are not unusual. That's why I say that I agree with Hugo that such things are not acceptable in a relational database; but at the same time I believe that they are nevertheless acceptable in SQL Server and are actually rather useful, because some of the time I'm not terribly concerned about being relational. It's a bit like believing that the only sane semantcis for functional programming languages is lazy at the same time as requiring the language to have syntax that allows one to specify that some arguments of some functions have to be treated eagerly instead of lazily (just try to find a sane way to build a database - relational or not - in a functional language without eager constructs - it is of course possible, provided you (a) have unlimited storage capacity and (b) don't care how slow anything is, but I've never met either of those happy circumstances).
Tom
December 6, 2015 at 3:11 am
Hugo Kornelis (11/24/2015)
The correct answer is missing: "The table doesn't have a primary key". It's also suspect that all columns are nullable.From the answer options I deduced that I needed to focus on the table name only; perhaps you can change the question text to reflect that?
Hi Hugo ,
This QOD remembers me the QOD of 2015/11/18 ( about sub-schemas ) with a long post from you about the correctness.
I think that persons who have been wrong to answer have not read the title of this QOD ==> "Dotted table". I think it was a clue about to the good choice. This title eliminates the of the discussion about the bad way used to create the table ( no real key , ... ) which was not the real topic of the QOD. To test some T-SQL statements , I simplifies the structure of a "test" table even if I create violations of the rules of Codd ( forms 1 , 2 and 3 ) . For me , this question was an easy but interesting one. It is interesting to notice that the structure of the table was oversimplified but not the real topic of the QOD.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply