I believe SQL was invented back in the 70’s back by E.F.Codd (don’t shoot me if I’m wrong; I’m not a historian.) Given the collective wisdom about programming back then, SQL is an amazing language.
I probably use TSQL more often than any other because of ad-hoc queries into my company’s SQL Server database. However today when I use it, I so often ask myself “What were they thinking when they designed SQL?!?“, which isn’t fair but that doesn’t keep me from being frustrated by its shortcomings.
Per chance you read my post about Developer Bias you’ll remember that my bias is strongly towards maintainability. And maintainability is where SQL needs great improvements. I’m going to start a multi-part series of posts about what I’d like to see in TSQL, with hopes it might inspire some of the Microsoft TSQL team for future versions.
As you probably know, many SQL commands use lists of fields and values: SELECT, INSERT, and UPDATE to name a few. For example:
The first feature I’d like to see is “Lists” for use as both “Field Lists” “Value Lists.” For example:SELECT
ID,
Name,
Company,
Street,
City,
State,
PostCode,
Country
FROM
tblCustomerINSERT
tblCustomer
(
Name,
Company,
Street,
City,
State,
PostCode,
Country
)
VALUES
(
‘Mike Schinkel’,
‘Xtras, Inc.’,
‘1360 Seaboard Industrial Blvd’,
‘Atlanta’,
‘GA’,
‘30318’,
‘USA’
)UPDATE
tblCustomer
SET
Name= ‘Mike Schinkel’,
Company= ‘Xtras, Inc.’,
Street= ‘1360 Seaboard Industrial Blvd’,
City= ‘Atlanta’,
State= ‘GA’,
PostCode= ‘30318’,
Country= ‘USA’
WHERE
ID=12345
Using the above field and value lists, we could make the SELECT, INSERT, and UPDATE statements much more maintainable:CREATE FIELDLIST flCustomer AS
ID NOINSERT NOUPDATE,
Name,
Company,
Street,
City,
State,
PostCode,
Country
Also, it would be useful to be able to create a valuelist from a function:SELECT FIELDLIST
flCustomer
FROM
tblCustomerINSERT
tblCustomer
FIELDLIST
flCustomer
VALUELIST
vlMikeSchinkelUPDATE
tblCustomer
SET FIELDLIST
flCustomer
VALUES
(
‘Mike Schinkel’,
‘Xtras, Inc.’,
‘1360 Seaboard Industrial Blvd’,
‘Atlanta’,
‘GA’,
‘30318’,
‘USA’
)
WHERE
ID=12345
DECLARE @MikeSchinkel list
SELECT @MikeSchinkel= ValueList(
‘Mike Schinkel’,
‘Xtras, Inc.’,
‘1360 Seaboard Industrial Blvd’,
‘Atlanta’,
‘GA’,
‘30318’,
‘USA’)INSERT
tblCustomer
FIELDLIST
flCustomer
VALUELIST
@MikeSchinkel
FieldLists/ValueLists would also be useful as PROCEDURE parameters:
CREATE PROCEDURE prcUpdateCustomer
(
@CustomerID int,
@Customer LIST flCustomer
)
AS
PRINT ‘Updating Customer ‘ + @Customer.Name
UPDATE
tblCustomers
SET FIELDLIST
flCustomer
TO VALUELIST
@Customer
WHERE
ID=@CustomerID
RETURN
Of course my exact syntax might need polishing, but with these extensions I think you’d see TSQL source code become much more maintainable. “Microsoft TSQL team: Are you listening?” :-)
Yes, but is this really necessary since SQL Server 2005 will support stored procedures written in C# and VB?
Wow… that seems like a great idea. Almost seems like a domain object stored in the DB instead of your business layer. I wonder if this concept breaks some of the set-logic used by relational db’s?
I hope the MS SQL team is listening to you!
>> Yes, but is this really necessary since SQL Server 2005 will support stored procedures written in C# and VB?
hehe. C#/VB stored procs won’t directly access fields in this way, as far as I know. Also, have you seen how they plan to implment stored procedures in C# and VB?!? I have read about it, and I am not impressed (I plan to blog about it on a future blog.)
>> Wow… that seems like a great idea.
Thanks. That’s my strength; great ideas. (My weakness is my inability to get them implemented. :)
>> Almost seems like a domain object stored in the DB instead of your business layer. I wonder if this concept breaks some of the set-logic used by relational db’s?
As I see it, if required it could even be implemented at a higher level so that the engine could compose existing SQL statements before passing them on to be processed, so I doubt it could break any set logic.
This is an interesting idea and it does make for readability of one’s SQL statements.
The only issue I take is that you are targeting your enhancement idea only to Transact-SQL instead of proposing a change to the SQL standard.
BEGIN RANT
Since I, like you, take issue with non-maintainable code, one of the things that I find contribute to this is vendor-specific enhancements to language standards.
One aspect of a code maintainer’s life is being asked to move existing applications from one system to another, or even upgrading software versions. These language extensions tend to be the gotcha’s that take up a lot of time because one’s tendency is to say "oh that part of the system is written in [SQL, C++, JAVA], so that should convert fairly easily from version X to version Y]" only to get tripped up because the one "enhancement" that a specific vendor made is not compatbile with the standard or another’s implementation of a similar feature.
END RANT
So, since this idea of yours has great merit, I suggest a more formal write-up of it and a sumbission of it to the the standards groups (I don’t have time to look up which one is handling SQL right now).
>> The only issue I take is that you are targeting your enhancement idea only to Transact-SQL instead of proposing a change to the SQL standard.
Point taken. I mentioned this to a Microsoft employee at SQL Connections in Orlando and a fellow attendee said "Why not for the entire SQL standard?"
Here’s why I push for TSSQL first: I believe good improvements come much faster when driven by a vendor as a defacto standard then waiting for the standards committee to address something. Except in the early days of the web, standards committees are always slow. If left for the SQL standards committee, my fears are I won’t see the feature for another 5 or 10 years. I need it today.
I think standards committees are best to see what vendors have created that sticks, and then creating a standard from that. Sure, it leaves some non-standard legacy code in implementations, but it gets the features to the developers who need it much sooner.
So in summary I do believe it should go into the SQL standard but I think the path sooner than later to get there is adding to TSQL.
>> Since I, like you, take issue with non-maintainable code, one of the things that I find contribute to this is vendor-specific enhancements to language standards. One aspect of a code maintainer’s life is being asked to move existing applications from one system to another, or even upgrading software versions.
I guess that is where I get selfish. We’ve a 100% SQL Server shop, partly to reduce the problems associated with interoperability. I do get your point and see your pain, but selfishly if I could get the feature into TSQL it would help me greatly.
Once again; I’m a huge believe in standards, but I believe more strongly in defacto standards than annointed ones.
>> So, since this idea of yours has great merit, I suggest a more formal write-up of it and a sumbission of it to the the standards groups
Ah, I wish I had the time for this. Unfortunately, this blog post was more time than I could probably afford to devote to it.
Nice ideas but easily accomplished in SQL server if you use the system views and bit of dynamic SQL.
The one feature that SQL Server really needs is UPSERT. SQL-99 compliancy wouldn’t be bad either.
>> Nice ideas but easily accomplished in SQL server
How?
>> if you use .. bit of dynamic SQL
I suppose, but that’s like saying a programming language doesn’t need to be object-oriented if you’ve got a preprocessor. Sure, you can emulate the same thing in some cases, but dynamic SQL is weak in at least two key areas: performance and robustness. Since dynamic SQL never gets precompiled your performance can suffer, but far more importantly dynamic SQL has no built-in error checking until run-time, which suffers huge robustness penalties in my book.
Also, dynamic SQL is only a technique, and one of my reasons for wanting these features is so that SQL IDES and other tools like MS-Access will begin to support the features. SQL IDEs and other tools won’t support any home-grown dynamic SQL hacks.
At best, dynamic SQL is a partial workaround to what I’m trying to accomplish above.
>#/VB stored procs won’t directly access fields in this way, as far as I know. Also, have you seen how they plan to implment stored procedures in C# and VB?!? I have read about it, and I am not impressed (I plan to blog about it on a future blog.)
I confess I haven’t read about the actual implementation, I’ll be watching what you post about it as this new feature sounded like a great idea. I’m struggling with T-SQL!