September 11, 2014 at 9:34 am
Luis Cazares (9/11/2014)
CELKO (9/11/2014)
The single line is a bouma; a semantic unit read as a whole. My PROGRAMMING STYLE goes into some of the research we collected at AIRMICS on this. I wish we had had the modern equipment for the eye movement studies ..The problem is that a column list is not a single unit, it's a list.
You'll find that reviewing lists will be easier if every item is in a single line. When counting the columns (and you'll need to count them more often than you could argue), having each column on it's own line will be a relief. Unless our brains don't match your studies.
This is actually a point of contention, and I see folks like it both ways. I personally like each column reference on its own line, because I can line up source and destination in a spreadsheet and do some work mapping columns etc, but I also see folks that like many columns per line.
I can see Mr. Celko's point about research indicating such, but I don't necessarily think that research result is as universally indicative of absolutes as he does, because more importantly than Mr. Celko's supposed absolutes is the wide variety of tasks we folks actually get stuck with in the real world, and the fact that we often don't get the perfect engineering that he demands, either from our vendors, our predecessors, or even from the folks asking for work to be done.
September 11, 2014 at 9:59 am
CELKO (9/11/2014)
...Blah blah blah blah
...
That's all I hear when Mr. Celko speaks. Sort of like the teacher in the Peanuts cartoon specials.
September 11, 2014 at 10:07 am
Lynn Pettis (9/11/2014)
CELKO (9/11/2014)
...Blah blah blah blah
...
That's all I hear when Mr. Celko speaks. Sort of like the teacher in the Peanuts cartoon specials.
But at least there is some entertainment value in the Peanuts version!
Don Urquhart
September 11, 2014 at 2:17 pm
Your little example is really nice, real life isn't like that.
Please find the errors in the following code:
INSERT INTO dbo.FactInternetSalesCopy
(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber,
OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber)
SELECT ProductKey, OrderDateKey, DueDateKey ShipDateKey, CustomerKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber,
OrderQuantity, UnitPrice ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
TotalProductCost, SalesAmount TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber
FROM AdventureWorksDW2008R2.dbo.FactInternetSales
Now find it in the same code formatted in a different way:
INSERT INTO dbo.FactInternetSalesCopy
(
ProductKey
,OrderDateKey
,DueDateKey
,ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber
,SalesOrderLineNumber
,OrderQuantity
,UnitPrice
,ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
,TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
)
SELECT ProductKey
,OrderDateKey
,DueDateKey
ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber
,SalesOrderLineNumber
,RevisionNumber
,OrderQuantity
,UnitPrice
ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
FROM AdventureWorksDW2008R2.dbo.FactInternetSales
Also, you're still thinking on the front-end and not the RDBMS. You should remember the third normal form: "all the attributes in a table are dependent on the primary key and only the primary key."
Meaning that it doesn't matter if you think that they belong together, they're completely independent from one another.
Having each column on its independent line, will help you if you need to insert a new set of columns and you don't want to reformat the code to make it fit in the screen.
If you want to group columns to form a bouma, you can do it as long as you understand that they're different attributes and don't belong to a sentence (a list is not a sentence).
September 11, 2014 at 2:41 pm
Luis Cazares (9/11/2014)
You should remember the third normal form: "all the attributes in a table are dependent on the primary key and only the primary key."
While I agree with most of what you are telling Joe, I think I have to challenge that one. That definition is not Third Normal Form. If you think it is, please explain how it's consistent with this table being in Third Normal form:-create cable Hoghouse (
pignumber int primary key,
pigname varchar(12) not null unique
) ;
That table is in EKNF so a fortiori it's in 3NF, but pigname -> pignumber is a functional dependency in that table and is not on the primary key. It isn't in BCNF, which is what your description comes closest to defining and is an extremely misguided normal form because, unlike EKNF, BCNF cannot always provide schemata whose keys prevent all anomalies arising from functional dependencies.
Sorry to be pedantic, but I care about normalisation and will challenge every error I see to try to get rid of some of the myths.
The correct definition of 3NF is "all attributes of the table except key attributes are functionally dependant only on attribute sets which include the whole of a candidate key".
The correct definition of BCNF is "all attributes of the table are functionally dependent only on sets which include the whole of a candidate key".
The correct definition of EKNF is "all attributes except elementary key attributes are dependent only on sets which include the whole of a candidate key".
Tom
September 11, 2014 at 2:50 pm
Thank you, Tom.
I guess I shouldn't have taken the quote from Wikipedia. My mistake.
PS. It's great that you can create cables on SQL :-D:hehe:
September 11, 2014 at 2:52 pm
Luis Cazares (9/11/2014)
Thank you, Tom.I guess I shouldn't have taken the quote from Wikipedia. My mistake.
PS. It's great that you can create cables on SQL :-D:hehe:
Ouch! I must get my typing back up to standard! :blush: 😛
Tom
September 11, 2014 at 4:29 pm
Luis Cazares (9/11/2014)
Your little example is really nice, real life isn't like that.Please find the errors in the following code:
INSERT INTO dbo.FactInternetSalesCopy
(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber,
OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber)
SELECT ProductKey, OrderDateKey, DueDateKey ShipDateKey, CustomerKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber,
OrderQuantity, UnitPrice ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
TotalProductCost, SalesAmount TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber
FROM AdventureWorksDW2008R2.dbo.FactInternetSales
Now find it in the same code formatted in a different way:
INSERT INTO dbo.FactInternetSalesCopy
(
ProductKey
,OrderDateKey
,DueDateKey
,ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber
,SalesOrderLineNumber
,OrderQuantity
,UnitPrice
,ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
,TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
)
SELECT ProductKey
,OrderDateKey
,DueDateKey
ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber
,SalesOrderLineNumber
,RevisionNumber
,OrderQuantity
,UnitPrice
ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
FROM AdventureWorksDW2008R2.dbo.FactInternetSales
Also, you're still thinking on the front-end and not the RDBMS. You should remember the third normal form: "all the attributes in a table are dependent on the primary key and only the primary key."
Meaning that it doesn't matter if you think that they belong together, they're completely independent from one another.
Having each column on its independent line, will help you if you need to insert a new set of columns and you don't want to reformat the code to make it fit in the screen.
If you want to group columns to form a bouma, you can do it as long as you understand that they're different attributes and don't belong to a sentence (a list is not a sentence).
I still prefer the top format so you don't have to scan so far to read, say, 80 columns. Generally, however, I use a consistent 5 columns (or less, if complex expressions are used) per line, thus making it easier to match things up.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 11, 2014 at 4:43 pm
ScottPletcher (9/11/2014)
Luis Cazares (9/11/2014)
Your little example is really nice, real life isn't like that.Please find the errors in the following code:
INSERT INTO dbo.FactInternetSalesCopy
(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber,
OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber)
SELECT ProductKey, OrderDateKey, DueDateKey ShipDateKey, CustomerKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber,
OrderQuantity, UnitPrice ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
TotalProductCost, SalesAmount TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber
FROM AdventureWorksDW2008R2.dbo.FactInternetSales
Now find it in the same code formatted in a different way:
INSERT INTO dbo.FactInternetSalesCopy
(
ProductKey
,OrderDateKey
,DueDateKey
,ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber
,SalesOrderLineNumber
,OrderQuantity
,UnitPrice
,ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
,TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
)
SELECT ProductKey
,OrderDateKey
,DueDateKey
ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber
,SalesOrderLineNumber
,RevisionNumber
,OrderQuantity
,UnitPrice
ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
FROM AdventureWorksDW2008R2.dbo.FactInternetSales
Also, you're still thinking on the front-end and not the RDBMS. You should remember the third normal form: "all the attributes in a table are dependent on the primary key and only the primary key."
Meaning that it doesn't matter if you think that they belong together, they're completely independent from one another.
Having each column on its independent line, will help you if you need to insert a new set of columns and you don't want to reformat the code to make it fit in the screen.
If you want to group columns to form a bouma, you can do it as long as you understand that they're different attributes and don't belong to a sentence (a list is not a sentence).
I still prefer the top format so you don't have to scan so far to read, say, 80 columns. Generally, however, I use a consistent 5 columns (or less, if complex expressions are used) per line, thus making it easier to match things up.
I prefer the latter (except with the commas at the end) as the former is too cluttered.
September 12, 2014 at 12:10 am
ScottPletcher (9/11/2014)
Luis Cazares (9/11/2014)
Your little example is really nice, real life isn't like that.Please find the errors in the following code:
INSERT INTO dbo.FactInternetSalesCopy
(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber,
OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber)
SELECT ProductKey, OrderDateKey, DueDateKey ShipDateKey, CustomerKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber,
OrderQuantity, UnitPrice ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
TotalProductCost, SalesAmount TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber
FROM AdventureWorksDW2008R2.dbo.FactInternetSales
Now find it in the same code formatted in a different way:
INSERT INTO dbo.FactInternetSalesCopy
(
ProductKey
,OrderDateKey
,DueDateKey
,ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber
,SalesOrderLineNumber
,OrderQuantity
,UnitPrice
,ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
,TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
)
SELECT ProductKey
,OrderDateKey
,DueDateKey
ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber
,SalesOrderLineNumber
,RevisionNumber
,OrderQuantity
,UnitPrice
ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
FROM AdventureWorksDW2008R2.dbo.FactInternetSales
Also, you're still thinking on the front-end and not the RDBMS. You should remember the third normal form: "all the attributes in a table are dependent on the primary key and only the primary key."
Meaning that it doesn't matter if you think that they belong together, they're completely independent from one another.
Having each column on its independent line, will help you if you need to insert a new set of columns and you don't want to reformat the code to make it fit in the screen.
If you want to group columns to form a bouma, you can do it as long as you understand that they're different attributes and don't belong to a sentence (a list is not a sentence).
I still prefer the top format so you don't have to scan so far to read, say, 80 columns. Generally, however, I use a consistent 5 columns (or less, if complex expressions are used) per line, thus making it easier to match things up.
Ugh no 🙂
I hate SQL code with all columns on one line. It's fine if you have less than 5 columns or so, but if you have 200 columns (like some dimensions have), all the monitor gets filled up with columns (and there are different sizes of monitors), so unless you have word wrap you need to scroll to the side.
True, 200 columns in a list will also cause you to scroll down and up, but at least it's easier to find mistakes or to find a specific column.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 13, 2014 at 5:25 am
So 200 columns in a list, and you split it to new lines at what, 80 characters? What is this a Ribbon Printer from 1970? Ah, you leave them all on the same line! Yea... doesn't work in the real world... you'll scroll right and lose any benefit. Oh, make it monitor width? What size monitor? Some collegues have 24" widescreens and others have 22" square monitors.
The REAL answer is this:
Follow the corporate Coding Standards!
Many companies have corporate coding standards that address this. For example "In a SELECT that returns data to a calling application/function/procedure, have no more than 5 columns on a single line, or more than 1 column per line if there are computations or aliases used"
Then another place they may have
"In a SELECT that is INSERTed into a Table have no more than 1 column per line"
Following Standards is more important than 1970's research. The more you follow the Standards, the easier it is to read code... and the more that "bad" code stands out like a flourescent orange painted streaker on the field at the Super Bowl. When you work in the code day in and day out for months, your eyes automatically take in the deviations from "normal". They also interpret the proper formatting much quicker.
And Mr. Celko, you have yet to explain why you deviate from ANSI standard on Dates and insist on making them VARCHAR instead of the appropriate DATE data type.
Koen Verbeeck (9/12/2014)
ScottPletcher (9/11/2014)
Luis Cazares (9/11/2014)
Your little example is really nice, real life isn't like that.Please find the errors in the following code:
INSERT INTO dbo.FactInternetSalesCopy
(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber,
OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber)
SELECT ProductKey, OrderDateKey, DueDateKey ShipDateKey, CustomerKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber,
OrderQuantity, UnitPrice ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost,
TotalProductCost, SalesAmount TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber
FROM AdventureWorksDW2008R2.dbo.FactInternetSales
Now find it in the same code formatted in a different way:
INSERT INTO dbo.FactInternetSalesCopy
(
ProductKey
,OrderDateKey
,DueDateKey
,ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber
,SalesOrderLineNumber
,OrderQuantity
,UnitPrice
,ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
,TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
)
SELECT ProductKey
,OrderDateKey
,DueDateKey
ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber
,SalesOrderLineNumber
,RevisionNumber
,OrderQuantity
,UnitPrice
ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
FROM AdventureWorksDW2008R2.dbo.FactInternetSales
Also, you're still thinking on the front-end and not the RDBMS. You should remember the third normal form: "all the attributes in a table are dependent on the primary key and only the primary key."
Meaning that it doesn't matter if you think that they belong together, they're completely independent from one another.
Having each column on its independent line, will help you if you need to insert a new set of columns and you don't want to reformat the code to make it fit in the screen.
If you want to group columns to form a bouma, you can do it as long as you understand that they're different attributes and don't belong to a sentence (a list is not a sentence).
I still prefer the top format so you don't have to scan so far to read, say, 80 columns. Generally, however, I use a consistent 5 columns (or less, if complex expressions are used) per line, thus making it easier to match things up.
Ugh no 🙂
I hate SQL code with all columns on one line. It's fine if you have less than 5 columns or so, but if you have 200 columns (like some dimensions have), all the monitor gets filled up with columns (and there are different sizes of monitors), so unless you have word wrap you need to scroll to the side.
True, 200 columns in a list will also cause you to scroll down and up, but at least it's easier to find mistakes or to find a specific column.
September 13, 2014 at 10:36 am
Post removed.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2014 at 11:05 am
TomThomson (9/11/2014)
Luis Cazares (9/11/2014)
You should remember the third normal form: "all the attributes in a table are dependent on the primary key and only the primary key."While I agree with most of what you are telling Joe, I think I have to challenge that one. That definition is not Third Normal Form. If you think it is, please explain how it's consistent with this table being in Third Normal form:-
create cable Hoghouse (pignumber int primary key,
pigname varchar(12) not null unique
) ;
That table is in EKNF so a fortiori it's in 3NF, but pigname -> pignumber is a functional dependency in that table and is not on the primary key. It isn't in BCNF, which is what your description comes closest to defining and is an extremely misguided normal form because, unlike EKNF, BCNF cannot always provide schemata whose keys prevent all anomalies arising from functional dependencies.
Sorry to be pedantic, but I care about normalisation and will challenge every error I see to try to get rid of some of the myths.
The correct definition of 3NF is "all attributes of the table except key attributes are functionally dependant only on attribute sets which include the whole of a candidate key".
The correct definition of BCNF is "all attributes of the table are functionally dependent only on sets which include the whole of a candidate key".
The correct definition of EKNF is "all attributes except elementary key attributes are dependent only on sets which include the whole of a candidate key".
Heh... I love it. You and Joe are both walking encyclopedias when it comes to things like this and that is meant as serious and honest compliment to you both.
I do have problems making developers understand such things so I invented the much simpler "DBPC" form of normalization. I simply explain that if I have to search for non-key attribute columns in multiple tables just to make (for example) a name change then "Death By Pork Chops" may be in their immediate future.:-P
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2014 at 12:37 am
CELKO (9/13/2014)
Interesting link, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply