Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Guest editorial: SQL Code Metrics


Guest editorial: SQL Code Metrics

Author
Message
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 2937
Comments posted to this topic are about the item Guest editorial: SQL Code Metrics


Best wishes,

Phil Factor
Simple Talk
Megistal
Megistal
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1608 Visits: 2555
As always, I do like your personal way to write articles.
Just wanted to let you know it!
kevin77
kevin77
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1749 Visits: 1098
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.



Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 2937
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14371 Visits: 9729
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
Nicole Bowman
Nicole Bowman
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 1615
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.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36012 Visits: 18730
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
My Blog: www.voiceofthedba.com
DPhillips-731960
DPhillips-731960
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1158 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?
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14371 Visits: 9729
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
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search