The T-SQL Paradigm

  • Comments posted to this topic are about the item The T-SQL Paradigm

  • I think that SQL as a language is not really the problem. There are a lot of developers out there that really don’t do well in any language, but the quality of their work is more exposed in SQL where performance and accuracy is more of an issue.

    The number one problem that most developers have when working with SQL Server is not using the language, but developing a schema that models the data accurately and completely. Deficiencies in the design of the data are much harder to fix once they make it into a production environment, and they make it hard for developers to write fast, efficient queries to get the data they need. At the same time, many developers seem unaware of the long term implications of bad logical and physical designs, so the most important aspect of database development gets the least amount of attention. Bad design leads to developers spending a lot of time trying to stand SQL on its head to do things it shouldn’t have to do with a well designed schema.

    I remember a conversation with a developer where I questioned the fact that he used inappropriate data types and that none of his tables had a primary key. He dismissed this saying that he was designing for the “real world”, and couldn’t be bothered with things were of only “theoretical interest”.

  • I concur with the previous post, and would also add that the IDE's for T-SQL or SQL in general could do with a lot more features.

    SQL is still largely invented by the practitioner, instead of the science.

  • I don't think T-SQL or SQL is a bad language. Knowing how to write SQl doen't mean understanding what it means to write correct SQL. Most developers don't think of collections when trying to solve a question to the database. When jou think of collections you're able to write fast performing set-based queries.

  • I agree with Michael.

    In the early days of databases, IMS got man to the moon in a hierachical fashion. IMS is still around and scales to any size you want and is fast. However, like any hierarchical/OO DBMS, in order to get good performance you need to plan your access paths in advance.

    Relational databases were seen as a scientific answer to the problems of a hierachical database. The wonderful strength of a RDBMS is that you specify the set of data you want, and the RDBMS optimizer uses set-based mathematics to decide how to get it. This means your request can be sliced and diced to exploit parallelism, with the certainty the (barring bugs) the required result set can be assmbled from the various parallel streams. It also means that as business requirements change, the DBA can often respond by adding new indexes, re-partioning the data, etc, to give good performance to the business but without needing legacy applications to be re-written.

    As most technology moves in circles, people are now looking again at HDBMS. XML is inherently hierachical in nature, likewise objects. IMHO we are in a temporary position for a few years where people want to exploit XML but were not born when the original move from HDBMS to RDBMS was made and do not understand the reasons for the move. Theer is a perceived simplicity in aligning the data store with the object definition that people want to exploit. They can 'prove' that it is performant by putting a few hundred or thousand rows into it, and as the systems being built are often version 1 applications thare is little legacy of old design decisions that are no longer appropriate to the business.

    Give things a few years, and the dead weight of legacy applications that treated the data store more or less as a flat file, and 100-fold increase in data volumes and the problems of a HDBMS will be learned all over again. The mathematical basis of RDBMS will again be valued, and IT professionals will have a new time of plenty as XML-based databases get moved into a RDBMS so that the business can grow in the way it wants.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • I think it's all too easy to forget that SQL is an agreed standard, and a very efficient tool for carrying out the work for which it was designed.

    Applications often need to talk with databases; that's a fact of life. If a company decides to switch database vendors, how much application rewriting is required? Or developer retraining? OK, T-SQL is a diferent dialect to PL-SQL, but they're still both based closely on a standard, so movement between dialects is hardly rocket science.

    We're always going to have a VHS vs Betamax argument about technical tools, with proponents of various languages/applications/databases/hardware/operating systems and so on justifying their preferences. Undoubtedly, plenty of those justifications are based on sound technical evidence, but they don't automatically make all the alternatives invalid. SQL, and all its related dialects, do their job very well. At that point, whether or not it is strange is irrelevant.

    Semper in excretia, suus solum profundum variat

  • I agree with Michael, it's not that SQL is difficult, in fact I think T-SQL is quite easy, it's making it perform that can be seen as difficult, and that boils down to underlying design and an ability to think in sets.

    I don't do application development, so I would find C#, .Net, HTML 'difficult' because I don't know them. And as such I wouldn't try to develop anything in those languages. But your average developer has probably touched upon SQL in either college or some sort of dev training, so they think they can code in it. The real-world is also full of companies where there is no DBA, and the database work falls upon the developer with the most SQL experience, so what do you expect! Imagine an website designed and implemented by DBAs!

    As a DBA I've been fortunate to work with a number of developers who understand their own limitations. Sure they can write simple queries, but when it comes down to more complex stuff, they leave it to the database devs or DBAs. And they certainly steer clear of database design. They also understand the limitations of so-called 'silver bullet solutions' like ORM and LINQ to SQL. It's when you meet or hear of developers who want to be able to do it all in their 'paradigm' that you get these 'fun' debates.

    Is there a better way to build a query language? Probably.

    Is it going to be easy? Probably not.

    Theres got to be something in the fact that application development languages churn over at a rate of knots - they look nothing like they did 10, 15, 20 years ago - but SQL has remained relatively stable.

    Attempts have been made to make it easier for developers to code against databases, the latest failure being LINQ to SQL, so lets just accept that trying to make set-based querying fit the OO or procedural model, whilst it can be implemented, never really works.

    Kev

  • dphillips (4/3/2009)


    I concur with the previous post, and would also add that the IDE's for T-SQL or SQL in general could do with a lot more features.

    dphillips, like what?

    Kev

  • For a domain specific language, in this case the domain being data management, T-Sql gets the job done. Having worked with both it (in MS and Sybase) as well as weith Oracle's PL/SQL, it has feature level advantages as well as disadvantages, but I've not found it lacking unless you're trying to build the entire application within stored procs.

    I think those who say that it's missing features are using it in places where C# or others should play a stronger part. In this case you shoud be building application components or .NET assemblies (XP's in earlier SQL Server releases) that can extend the funcitonality of the lowly stored procs.

    In other words, T-SQL is a usefull tool, but it's not a hammer and the worlds not a nail. 🙂

  • Opinion from a developer. Okay, an OLD developer.

    I've been working with different query languages for quite a number of years starting with the dot prompt in dBase II. (I did say I'm old, didn't I?) I started learning T-SQL in MSSQL 6.5 and haven't looked back since. I think Microsoft has done a heck of a job with MSSQL. My experience with the "average" developer is they "haven't the time" to learn T-SQL and all it can do for them.

    As for myself, I'm a c# programmer along with a number of other languages. When it comes to extracting data from a database, I've tried all the latest and greatest including Linq, managed code for MSSQL, various IDEs for SQL development, and ultimately end up back in the current iteration of a query analyzer writing my queries, functions, and procs by hand.

    Shortcuts just for the sake of getting a job done faster are mostly detrimental to the overall effort. Learn the language. Apply it to some real-world examples. Hopefully you'll come to appreciate it.

  • I agree with Bob.Sargent comments.

    I go back 20 or so years to mainframe assembler, COBOL and JCL. I am currently a asp.net/vb.net developer and an "Application DBA".

    I am fine with SQL. I've never had a requirement that it didn't handle.

    Also, I hope that LINQ dies the quick death it deserves.:-P

    M

  • I've been developing web and win apps for the retail and financial market for many years and I've found SQL to be very valuable and T-SQL an easy language to work with.

    I've put a great deal of time learning as much about SQL as possible (to the point where I've often been asked for advice from DBAs in companies I've worked for!). This has resulted in me being able to write some great applications. In retrospect, if I didn't have the SQL knowledge I currently possess would those apps have been as good? Simple answer - No.

    I feel SQL can sometimes be given a low priority in the personal learning curve of developers. A message to those that do give it a low priority - give it a lot of focus and learn as much as possible and you'll come to find it time very well spent. You might not agree with that now.. but you will do!

  • Having started my db work in EXEC, REXX, RAMIS (sp?), and the like, then moving on to procedural programming languages in college, Modula-2 for the most part, after college moving to Sybase, and eventually MSSQL 4.3... I think I know the disconnect. Someone stated before, the problem is in the mindset. Having been the hired gun in a few shops (consultant), I can say that the biggest problem I see is developers who can not break free of the procedural, RBAR (thanks) mindset.

    The developers can not or will not think in sets.

    The most recent, and one of the most painful examples is a db tracking certain events. The db is ~600GB, grows about 10 GB a week, adding some 5M rows a day. For whatever reason, the app developers decided to CLUSTER the index on a GUID, and then they wrote an archival routine to remove old data via a cursor, row by row by row by...

    The data was coming in more than 10x faster than it could be deleted. I rewrote the procedure(s) and introduced small batches to the idea... Now, the test machines could delete everything they need to (on going) in about 30 minutes.

    Haven't heard if it's been tested in production yet.

    I should really finish that education I started... (Actually, I'm supposed to graduate this summer, it's only taken me 22 years.)

    Honor Super Omnia-
    Jason Miller

  • Amazing!!! I was just at a meeting recently where some of us "old guys" were talking about how these younger generations just seem to LOVE complexity and confusion. To suggest that T-SQL should be replaced with C#, or should be more C#-ish - well, now I have heard it all! Right, replace something very simple, with something totally confusing - great idea!

    T-SQL is fairly easy and more, you can be "good" at it without having to be "great" - that is, you can teach it to non-programmers and find they do some good basic work. SQL can be tricky, and does have it subtleties but still, I would agree that its easy to work with and probably the easiest after the XBase languages (which were all more English-like).

    This reminds me of the famous tale of two engineers (one from Microsoft) given the task of building something to take a person one block from their apartment, to the local corner store. The non-Microsoft engineer comes back with a skateboard, and the Microsoft guy comes back with the Space Shuttle. Yeah, they will both get you to the corner store, but one does it in less than minute with no complexity involved, and the other requires tons of fuel, tons of parts, and has to fly into space and return to just go one block. And of course, after you get your stuff at the store, the Space Shuttle needs to be towed to a launchpad to fly again. The problem these days is too many developers continue to build Space Shuttles where skateboards would do.

    I don't know where it all got lost - but simple is always better than complexity for the sake of "cool". I wish the generations of programmers that came after mine had learned that lesson - but sadly, they seem to have learned just the opposite...

    ...and this fellow's rant about T-SQL illustrates that quite clearly.

    There's no such thing as dumb questions, only poorly thought-out answers...
  • It's great for what it does, handle complex queries and set operations. It's less than great for procedural code, I like c++ but c# or VB will do.

    ...

    -- FORTRAN manual for Xerox Computers --

Viewing 15 posts - 1 through 15 (of 266 total)

You must be logged in to reply to this topic. Login to reply