ProAnswers.org

What are the common antipatterns you've seen in SQL?

All of us who work with relational databases have learned (or are learning) that SQL is different. Eliciting the desired results, and doing so efficiently, involves a tedious process partly characterized by learning unfamiliar paradigms, and finding out that some of our most familiar programming patterns don’t work here. What are the common antipatterns you’ve seen in SQL?

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.