The T-SQL Paradigm
-
James Goodwin (4/8/2009)
I haven't yet come across any rational as to why the == operator was chosen as the equality operator instead of overloading the = operator and relying on context to see if its assignment or equality.
If you overload the = operator then you can't do:
if ((A=B)==(C=D))
Because:
if((A==B)==(C==D))
Is a different, but legitmate statement. While I would probably never use the first condition, I could imagine a circumstance where I would want to use the second one [Which, admittedly, is just a brief form of if (((A==B) && (C==D)) || ((A!=B) && (C!=D))) ]
It also makes all the equality operators have 2 characters while bitwise operators have 1 character which is a useful mnemonic.
But the real reason is probably that it made the grammar easier.
--
JimFive
I love these technical language discussions!
Yeah, you're right, your example does it make it clear why it would be desirable to have separate equality and assignment operators. If the equality and assignment operators were the same I couldn't do the following:
A = B = C = D = 0; //assign 0 to all variables
Given the frequency of mistakes of using = and == maybe Pascal's := and = would be better; but that's water under the bridge now.
-
Timothy (4/8/2009)
Given the frequency of mistakes of using = and == maybe Pascal's := and = would be better; but that's water under the bridge now.
Pascal was a doofus I have to deal with that idiotic := assignment thing in PL/SQL. [whine]Anytime you have to hit the shift key for anything but a capital letter is a pain. That key combo (shift+[anything but letter]) is a pain.[/whine]
Seriously though, =/== makes more sense for assignment/equality to me than :=/= (I feel like I'm typing an emoticon).
--Paul Hunter
-
Completely eludes me what's so wrong with = == := != <> etc, etc, they're all still faster than typing
ASSIGN THE VALUE OF A TO B
IS A THE SAME AS B
IS A DIFFERENT FROM B
...
Peter Edmunds ex-Geek
-
You are right about being faster. Far less typing.
I think that there is a point that we have ignored though. I was thinking about the differences between COBOL and APL. It will take a short paragraph to illustrate the point.
COBOL is "Common Business Oriented Language" while APL is "A Programming Language". Admiral Hopper had a hand in both. COBOL was intended to solve business problems while being as much like English as possible. APL was intended to be a general purpose language that was as UNLIKE any Human language as is possible. To our focus, SQL is "Structured Query Language". Notice that these languages have a different intent. Kernegan and Ritchey had two different computers, made by different companies, and wanted a way to share source code. PHP started as "Personal Home Page". On and on. These languages each started with a different INTENT.
There is your paragraph. While forming that paragraph in my head something struck me. Each of the languages has an intent. Then there is the nature of computer languages vs human languages. In the first case these are designed for US to communicate to the computer while in the second they are evolved for US to communicate with each other. There is something foundational here.
I think that part of my problem with ASSIGNMENT and EQUALITY operators is visual as far as reading. The = vs == is often hard to spot given certain monitors, type faces, and resolutions. This is further complicated by the that neither is WRONG. Each expression is valid in and of itself. That you can have an assignment expression in an IF statement and test the result of that causes certain things. SQL does not exactly support that. You can have an INSERT that has a SELECT but not the other way round.
Then to T-SQL there was a recent change. SET @a = 0 was changed to allow @a = 0 without the word SELECT in front of it. Quite a long time ago BASIC let go of the word LET.
Note that that many of these languages are command word oriented. The first word tells what the statement is supposed to do. If the rest of the statement does not fall in line then the statement itself can fail at the parser.
Oh, and the A=B=C=D=0 thing can have some very interesting twists if each of the objects is of slightly different types. Given an evil B then A might not be zero at all. 🙂
SET A,B,C,D = 0 is quite clear.
ATBCharles Kincaid
-
I like the KISS principle. Keep It Simple Stupid. What is easier to read: A = B and B = C or A==B && B==C. Why do I need to remember all sorts of symbols for operators like &&, ||, != etc when I can use simple english to write code. I wonder who decided that these heavy technical languages has to be so difficult. Is it maybe because it makes the programmers look very intelligent in the eyes of "other" people.
When I started programming I used to use this very technical way of programming until someone came to me and said: "Hey, Keep It Simple, Stupid!" Well, that's my two cents worth.
:-PManie Verster
Developer
Johannesburg
South AfricaI can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster) -
Charles Kincaid (4/9/2009)
COBOL is "Common Business Oriented Language" while APL is "A Programming Language". Admiral Hopper had a hand in both. COBOL was intended to solve business problems while being as much like English as possible. APL was intended to be a general purpose language that was as UNLIKE any Human language as is possible. To our focus, SQL is "Structured Query Language". Notice that these languages have a different intent. Kernegan and Ritchey had two different computers, made by different companies, and wanted a way to share source code. PHP started as "Personal Home Page". On and on. These languages each started with a different INTENT.There is your paragraph. While forming that paragraph in my head something struck me. Each of the languages has an intent. Then there is the nature of computer languages vs human languages. In the first case these are designed for US to communicate to the computer while in the second they are evolved for US to communicate with each other. There is something foundational here.
I think that you've hit the nail on the head!:smooooth:
That was an excellent overview and reminder that each language serves a different purpose. I don't believe that a general purpose, fit-all-scenarios language will ever be developed that will ever be as effective as the ones developed to suit their own individual tasks. Can there be a blending of language features? Of course, but I don't foresee the day when "one language to rule them all" will ever come into being. Just as the toolbox analogy was given earlier, there will never be a single tool created that will replace all tools in the toolbox. Though some may have similar features, each has their own design and purpose and no other tool will work as effectively as the one designed for just that purpose.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com -
Why do I need to remember all sorts of symbols for operators like &&, ||, != etc when I can use simple english to write code. I wonder who decided that these heavy technical languages has to be so difficult. Is it maybe because it makes the programmers look very intelligent in the eyes of "other" people.
No, really, it just makes the grammar easier to define and therefore the compiler easier to write.
It might be noted that C.S. is a branch of (from) mathematics. The early programmers were mathematicians who were used to using (and inventing) symbols for new operations. There is nothing inherently more difficult about using && instead of 'and' they are both just symbols for an operation. However, it is visually easier to pick out the operator when it doesn't look just like the operands. It is also easier to mentally parse a line when operators are not overloaded.
Which of these statements:
if(xpos & ypos)
if(xpos && ypos)
Is equivalent to:
if(xpos and ypos)
--
JimFive
-
James Goodwin (4/9/2009)
Which of these statements:if(xpos & ypos)
if(xpos && ypos)
Is equivalent to:
if(xpos and ypos)
Both! LOL
Well, depends on the data types involved and how stringent a type checker your compiler is.
-
I have to add, on the subject of "=" having two purposes instead of just one, I really love reading complaints about "overloading operators". The phrase itself defeats the very argument it is used to support. At least, I'm pretty sure these people aren't complaining about phone company employees who are carrying too much weight on their backs, nor about over-worked market speculators, and so on.
The human mind is geared towards many-meanings-one-word. Insisting that "overloaded operators" are bad is pretty much saying, "computer programmers aren't capable of normal human thought processes".
Makes me laugh.
- 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
-
Aaron N. Cutshall (4/9/2009)
Just as the toolbox analogy was given earlier, there will never be a single tool created that will replace all tools in the toolbox. Though some may have similar features, each has their own design and purpose and no other tool will work as effectively as the one designed for just that purpose.Now it's you hitting it on the head. (Thanks for the complement by the way. I'm checking if this might be a good Phd thesis.)
I very much DON'T want a cell phone (mobile) that's a movie camera and editing studio, word processor, game console and controller, that can also act as a carpenters level. I have a few of those multi-tools you see. I got one from my dad. It's a hammer with nesting screwdrivers in the handle. It's a poor hammer and not that great of a set of screwdrivers.
When I want kitchen knives for food preparation I turn to my set of Cutco. I know that they say you CAN do a bunch of things. They demo cutting leather. They also show using their scissors to cut pennies into copper cloverleaf sculptures. (Me deface U.S. currency? Not on your life. That's still a felony, right?) I could trim my hedge with the knives but I have purpose built trimmers that do the job a might easier.
ATBCharles Kincaid
-
Jeff Moden (4/4/2009)
And, you're absolutely correct... some simple additions such as TRUNC(Date) would make life a bit easier. On the other hand, I hope to never see DECODE in T-SQL...Always write a Trunc function nowadays - easier to remember than the floor cast as float or whatevr equivalent, and makes code more readable too. Think that the reason it's in orable though, is that the average O dev would try and find some way of using a cursor to deliver the same result
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them. -
andrew gothard (4/9/2009)
would try and find some way of using a cursor to deliver the same resultROFL
Peter Edmunds ex-Geek
-
andrew gothard (4/9/2009)
Jeff Moden (4/4/2009)
And, you're absolutely correct... some simple additions such as TRUNC(Date) would make life a bit easier. On the other hand, I hope to never see DECODE in T-SQL...Always write a Trunc function nowadays - easier to remember than the floor cast as float or whatevr equivalent, and makes code more readable too. Think that the reason it's in orable though, is that the average O dev would try and find some way of using a cursor to deliver the same result
Ummm... I'd be real careful about making that recommendation... First, despite the fact that the INT function is a bit faster, I actually agree with the others about future date compatibility and the DateAdd/DateDiff method isn't difficult at all to remember. It's also extemely obvious when encountered in code. Wrapping those two high speed functions in a UDF doesn't save that much typing and it can cause a real performance problem. For example, on my single cpu box, the simple act of wrapping the 2 date functions in a UDF causes a 717% reduction in performance so far as duration goes and a 1562% increase in CPU usage.
Of course, I don't expect anyone to take what I say as the gospel. So, test it yourself on a million row table. Here's the code....
Here's the function...
CREATE FUNCTION dbo.TruncDate
(@Date DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN (SELECT DATEADD(dd,DATEDIFF(dd,@Date,0),0))
END
Here's the million row table...
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 100,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (SomeID)
... here's the test I ran against a profiler trace for batch completions...
--===== Direct date functions
DECLARE @Bitbucket DATETIME
SELECT @Bitbucket = DATEADD(dd,DATEDIFF(dd,SomeDate,0),0)
FROM dbo.JBMTest
GO
--===== Function
DECLARE @Bitbucket DATETIME
SELECT @Bitbucket = dbo.TruncDate(SomeDate)
FROM dbo.JBMTest
GO
... and, finally, here's the trace results from multiple runs of the code under test. The recommendation of "Always write a Trunc function nowadays" isn't one I'd necessarily follow...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Notice that the code window doubled up the word "ADD". That'll likely show up as an error if you copy the code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (4/9/2009)
Ummm... I'd be real careful about making that recommendation... First, despite the fact that the INT function is a bit faster, I actually agree with the others about future date compatibility and the DateAdd/DateDiff method isn't difficult at all to remember. It's also extemely obvious when encountered in code. Wrapping those two high speed functions in a UDF doesn't save that much typing and it can cause a real performance problem. For example, on my single cpu box, the simple act of wrapping the 2 date functions in a UDF causes a 717% reduction in performance so far as duration goes and a 1562% increase in CPU usage.
Of course, I don't expect anyone to take what I say as the gospel. So, test it yourself on a million row table. Here's the code....
Here's the function...
CREATE FUNCTION dbo.TruncDate
(@Date DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN (SELECT DATEADD(dd,DATEDIFF(dd,@Date,0),0))
END
... and, finally, here's the trace results from multiple runs of the code under test. The recommendation of "Always write a Trunc function nowadays" isn't one I'd necessarily follow...
Bit of a typing error there - meant to write "I always". Now you know my issues with more typing than required. True - what you say though, not only in this case, but in most "Always" is a word that should be considered before you use it. Then nearly always discarded.
I tend to use the following in my trunc func as well - it's a bit slippier than using the date functions.
CREATE FUNCTION dbo.Trunc
(@datDate DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN CAST(
FLOOR( CAST( @datDate AS FLOAT ) )
AS DATETIME
)
END
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 15 posts - 136 through 150 (of 266 total)
You must be logged in to reply to this topic. Login to reply