What are the most common SQL anti-patterns? [closed]

by

I am consistently disappointed by most programmers’ tendency to mix their UI-logic in the data access layer:

SELECT
    FirstName + ' ' + LastName as "Full Name",
    case UserRole
        when 2 then "Admin"
        when 1 then "Moderator"
        else "User"
    end as "User's Role",
    case SignedIn
        when 0 then "Logged in"
        else "Logged out"
    end as "User signed in?",
    Convert(varchar(100), LastSignOn, 101) as "Last Sign On",
    DateDiff('d', LastSignOn, getDate()) as "Days since last sign on",
    AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' +
        City + ', ' + State + ' ' + Zip as "Address",
    'XXX-XX-' + Substring(
        Convert(varchar(9), SSN), 6, 4) as "Social Security #"
FROM Users

Normally, programmers do this because they intend to bind their dataset directly to a grid, and its just convenient to have SQL Server format server-side than format on the client.

Queries like the one shown above are extremely brittle because they tightly couple the data layer to the UI layer. On top of that, this style of programming thoroughly prevents stored procedures from being reusable.

More Related Contents:

  1. select yesterday date in where condition
  2. What is SQL injection? [duplicate]
  3. Condition within JOIN or WHERE
  4. Index for finding an element in a JSON array
  5. Database design for a survey [closed]
  6. Is there a LastIndexOf in SQL Server?
  7. Table-less UNION query in MS Access (Jet/ACE)
  8. What is best tool to compare two SQL Server databases (schema and data)? [duplicate]
  9. Best way to check for “empty or null value”
  10. How to avoid multiple function evals with the (func()).* syntax in a query?
  11. SQL QUERY replace NULL value in a row with a value from the previous known value
  12. When or why would you use a right outer join instead of left?
  13. How to connect to SQL Server from another computer?
  14. Equivalent of Oracle’s RowID in SQL Server
  15. Get the default values of table columns in Postgres?
  16. How to avoid Duplicate values for INSERT in SQL?
  17. Months between two dates
  18. How do I use SQL’s GETDATE() and DATEADD() in a Linq to SQL expression?
  19. Why can’t I use alias in a count(*) “column” and reference it in a having clause?
  20. How to find third or nᵗʰ maximum salary from salary table?
  21. sql group by only rows which are in sequence
  22. Generating random strings with T-SQL
  23. How to find sum of multiple columns in a table in SQL Server 2005?
  24. Compare Strings ignoring accents in SQL (ORACLE)
  25. SELECT max(x) is returning null; how can I make it return 0?
  26. How to return default value from SQL query
  27. Is this a good way to model address information in a relational database?
  28. Conditional aggregation performance
  29. Retrieve Oracle last inserted IDENTITY
  30. RegEx to Detect SQL Injection

Leave a Comment