Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

How has SQL Server not gone the way of the dinosaur?

  1. Mar 18, 2017 #1
    I use SQL Server at my job and for my personal projects (only because I'm familiar with it). Nevertheless I am always amazed at how the language doesn't provide any simple ways of solving common problems.

    For example, let's say I have some data which represents rows I want to insert or update in another table. I first have to create a temp table with the same column definition as the target table. This is a lot of busy-work when the target table has dozens of columns. Then I have to insert all this data into the temp table. Then I have to use a MERGE statement which involves a lot of syntax and, by the way, can't UPDATE more than 1 column! You are out of luck if you want to update both "EmailAddress" and "FirstName" for any MATCHED "UserId". I've seen users on MSDN and TechNet as far back as 2007 request to Microsoft that they change this. It remains the same.

    Another example. I want to iterate through a list of values in a column, basically the equivalent of

    Code (Text):

    for(int id in MyTable.Select(t => t.Id))
        // ...
    Here's how you to that in T-SQL:

    Code (Text):

        CREATE TABLE #ids (rn INT, id INT)
        INSERT INTO #ids (rn, id)
        FROM MyTable
        DECLARE @i INT = 0
        DECLARE @n INT = (SELECT COUNT(*) FROM #ids)
        WHILE(@i < @n)
             DECLARE @id INT = (SELECT TOP 1 id FROM #ids WHERE rn = @k)
             // ...
             @i = @i + 1
    and that's probably wrong and there's no way to step-through debug it.
  2. jcsd
  3. Mar 18, 2017 #2


    Staff: Mentor

    The power of sql is not in doing row by row stuff but in manipulating whole tables and views to get what you want. It's akin to manipulating sets of data collecting, transforming , grouping and/or sorting it to create a report or input to another program.

    Each SQL statement is a program unto itself especially the select statement.
  4. Mar 18, 2017 #3

    jack action

    User Avatar
    Science Advisor
    Gold Member

    You have to remember that SQL is a specialized program that is optimized to manipulate tables of data. So if your data is not already in a table, chances are that creating one will be required to optimize your job.
    I'm more familiar with MySQL, but it seems that T-SQL also allow updating a column list, such as this example:
    Code (T-SQL):
    MERGE Production.ProductInventory AS target
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
       JOIN Sales.SalesOrderHeader AS soh
       ON sod.SalesOrderID = soh.SalesOrderID
       AND soh.OrderDate = @OrderDate
       GROUP BY ProductID) AS source (ProductID, OrderQty)
    ON (target.ProductID = source.ProductID)
    WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
       THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,  
                       target.ModifiedDate = GETDATE()
    OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
       Deleted.Quantity, Deleted.ModifiedDate;
    For the 2nd example, you are not really fair in your comparison. First you are missing some declarations in your FOR...LOOP, like MyTable. You are also ordering your table in the T-SQL, but not in your FOR...LOOP. Can't you store your SELECT into a variable like @mytable that would act as your MyTable variable, for example? Then you would be able to access your data. Here is a good example, I think:
    Code (T-SQL):
    DECLARE @CursorVar CURSOR;

    SELECT LastName, FirstName
    FROM AdventureWorks2012.HumanResources.vEmployee
    WHERE LastName like 'B%';

    OPEN @CursorVar;

    FETCH NEXT FROM @CursorVar;
        FETCH NEXT FROM @CursorVar

    CLOSE @CursorVar;
    DEALLOCATE @CursorVar;
    Look into CURSOR as well for more info.
  5. Mar 20, 2017 #4
    I'm confused about what you are trying to do. Can you give an example of the before and after tables? I don't understand what you mean that you can't update multiple columns at once.

    Code (Text):

        FirstName = Table_B.FirstName
       Some_Table AS Table_A
       INNER JOIN Other_Table AS Table_B
           ON Table_A.UserId = Table_B.UserId
    Also, a lot of people are getting away from procedural SQL and using DOAs like Doctrine.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted