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 123»»»

Guest editorial: SQL Code Metrics Expand / Collapse
Author
Message
Posted Tuesday, February 17, 2009 1:43 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 587, Visits: 2,552
Comments posted to this topic are about the item Guest editorial: SQL Code Metrics


Best wishes,

Phil Factor
Simple Talk
Post #658294
Posted Tuesday, February 17, 2009 11:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 1,431, Visits: 2,104
As always, I do like your personal way to write articles.
Just wanted to let you know it!
Post #658708
Posted Tuesday, February 17, 2009 11:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:41 PM
Points: 1,733, Visits: 1,071
Nobody has replied to this yet? Well maybe this might spark some debate.

The thing I started thinking about while reading this article is how terrible of a programming language SQL is and the lack of tools to help you write code. I know you can write a bad program in any programming language, but I don't know if we should even be considering SQL as a programming language. For example, why doesn't SQL have the concept of an array? It's the most basic data structure there is. Also, up until SQL Server 2008, we've never had Intellisense!

Back when the .NET Framework and C# were released in 2001 it came with a rich IDE, numerous tools, and the ability to document your code using what's called "Xml Documentation". That documentation then becomes part of the tool tip when using Intellisense. Why doesn't SQL have that capability!?

You also talked about always learning new, perhaps better ways of doing things. While I don't disagree that we should always be learning, I do see it as a problem. If most of you are like me, we tend to try and find new ways of doing things to help keep us interested, make it challenging, and to learn new things. However, I believe that is a main factor of what constitutes bugs and performance problems. For example, I recently came across an article here entitled "Custom Pagination in SQL Server 2005". I thought it was a great article and exactly what I needed. Then I started reading some of the comments and some of the "other ways to do it". And that got me thinking. Why are there so many ways do it? Which one is the best? Why do we keep reinventing the wheel? In my opinion, paging a result set is a very old and very common problem. Why isn't there a standard/best practice way to do it? Why isn't there a tool to help you do it?

You begin by stating, "SQL has a wonderful way of teaching us humility." And you're right. But it shouldn't be that way. SQL needs to go the way of the Dodo bird. In my opinion, it has been stale and archaic for years. New language features, framework functions, and tools need to become part of the "standard" of SQL. Forget about backwards compatibility, SQL is in need of a revolution.



Post #658709
Posted Tuesday, February 17, 2009 12:15 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 587, Visits: 2,552
Thanks for breaking the silence, both of you.

It is difficult to compare SQL with a conventional language. It is like comparing C# to Postscript. Writing SQL gives me more pleasure than any other language, because one can hammer together an application very quickly. It may not look pretty, but it will do a job of work. I've served by time crafting applications in C, Pascal, C++ and so on, but the lingering after-effect is an impatience to get things up and running quickly.

I think that today is an exciting time for anyone programming with relational databases because we are continually having to rethink what is possible. I agree that SQL has its flaws. It doesn't even allow you to use the whole relational model. However it will do me fine for the time being.

I think SQL Server's lack of IDE is a historical problem. It was bought-in by Microsoft to fill a gap in the product line, and, as Sybase, was designed to be portable across different operating systems. Everybody expected Microsoft to develop tools as rich as Sybase's Powerbuilder. It never seemed to happen, for some reason. There were several strategies that never really worked, and we never got anything with the richness of Powerbuilder or Business Objects. SQL Server has always remained culturally different from the rest of the Microsoft stable, but that is more the fault of Microsoft than the SQL Language, I think.



Best wishes,

Phil Factor
Simple Talk
Post #658732
Posted Tuesday, February 17, 2009 12:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
On the point of the article, about having a "code checker" for SQL, I don't think it would really be possible, at this point, to automate such a system. Databases and procs and such are far too business-specific.

How, for example, would a standard checker know if a proc is taking too long to run, or if it's something that's expected to take a long time, and it's okay/good in this case?

How would a "lines of code" check help in situations like "you have inline UDFs in your Where clause"?

Now, something that could find things like that might be a good idea, but I don't see it replacing running a server-side trace and querying the results in various ways.

On the subject of SQL "needing to go away", I have to ask what you would replace it with. It's a pretty darn effective way to access data. I've used OODBs, and so far as I can tell, they universally stink for anything but building simple web apps on top of, and are a nightmare to build reports on.


- 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
Post #658748
Posted Tuesday, February 17, 2009 2:19 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 3:19 PM
Points: 188, Visits: 1,564
I like the idea of a code checker but I agree with GSquared that it probably isn't currently possible. There would have to be consensus on what is best practice, allowances for hardware effects etc etc.

I like T-SQL even though there are a few short comings. I think that some of the issues are really with the tools that are meant to make our lives easier. Intellisense is a great addition to our toolset but I wonder if that will allow some people to become lazy in their work. Without Intellisense we have to remember things and know the hows and whys. This won't affect the people that always make the effort of course but may allow those who perhaps wouldn't have even tried before to have a stab and make a mess that the diligent people will have to clean up later. Hhmm quite thought provoking, advances making it easier to do things either well or poorly which brings us back around to a code checker!

Cheers


Nicole Bowman

Nothing is forever.
Post #658887
Posted Tuesday, February 17, 2009 2:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,181, Visits: 15,626
Phil,

A fantastic last sentence and a great editorial. I too am not sure that we can build a great code checker for SQL, but there are definitely some basics that could be added. Something to warn about cross joins, something that has intellisense like SQLPrompt from Red Gate or Management Studio 2008, check for cursors, look for subqueries that don't make sense. Likely it would be more of a "warn-er" than a "checker."

I do like the simplicity of SQL and I think it's a good language for working with sets of data. Definitely a different paradigm than many other languages.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #658901
Posted Tuesday, February 17, 2009 3:21 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 10, 2012 6:08 PM
Points: 1,156, Visits: 801
Phil Factor (2/17/2009)
... SQL Server has always remained culturally different from the rest of the Microsoft stable, but that is more the fault of Microsoft than the SQL Language, I think.


On most Microsoft development tools, I can easily see cause and reason and have cheered the changes and advancements. I too have observed the seeming gap in the feature set for the IDE-like space in Microsoft SQL Server.

They have put great effort into the BI space, both graphically and functionally, to include the .NET framework and all of the power there... but seemingly not much in the SQL editor.

Seriously, why does it seem to have this "cultural" gap?
Post #658933
Posted Tuesday, February 17, 2009 3:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
If you had something that would pop up and tell you, "this query involves a cross-join", or "this query will involve multiple table scans of large tables", or something like that, in the way that it will tell you about "index hints force..." and so on, in the Messages tab when you execute the create/alter command for a proc, that might be helpful. You could ignore it if it's expected/desired, but it might help catch a few of the eggregious errors.

A DBA at a prior job was having a heck of a time trying to figure out why his query was returning 25,000 rows when he was expecting more like 6. Turned out he was using single-letter table aliases in the From clause, and one of his joins used the same alias on both sides, because both tables began with the same letter. The alias was correct, so it wasn't giving an error message, but the join was "a.column = a.column", which made it a cross join. Hard to spot in the middle of a complex query, so easy for the eye to slide right past it, especially when most people see what they expect instead of what's actually there. (Over 90% of people fail on simple errors when tested for proofreading jobs. Not specific knowledge of the spelling of specific words, or judgement rulings on punctuation, but really simple stuff like "the the" being read as "the". People see what they expect.)

For that kind of thing, a warning about a cross join and the line number it appeared on would have been quite useful. Would have saved about five minutes of frustrating review. For that, a code checker could be quite nice.

Maybe RedGate should start the R&D on it. If they haven't already.


- 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
Post #658934
Posted Tuesday, February 17, 2009 3:49 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:06 AM
Points: 176, Visits: 728
Steve Jones - Editor (2/17/2009)
Phil,
I too am not sure that we can build a great code checker for SQL, but there are definitely some basics that could be added. Something to warn about cross joins, something that has intellisense like SQLPrompt from Red Gate or Management Studio 2008, check for cursors, look for subqueries that don't make sense. Likely it would be more of a "warn-er" than a "checker."


I think a "warn-er", as Steve has suggested, is a great idea. There are many "obvious" bad ways of coding T-SQL, and a "warn-er" could be used to bring these to the attention of the code writer, much like how the grammar checker in Word works. When you get a warning, you could choose to heed or ignore it.


Brad M. McGehee
DBA
Post #658949
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse