Visual C++ and LINQ - is it Needed?

Esther Schindler who is the editor of the email newsletter DevSource Update recently wrote about Visual C++ and Microsoft’s LINQ Project that adds the ability to use SQL code directly in VB and C# programs (emphasis mine):



Ever since the PDC, developers and the press have been talking about the LINQ technology that Microsoft showed off. The overwhelming response appears to be positive, and from all I can tell (I haven’t taken the time to read the tech docs) it seems to be justified. But I can’t help it if I see the plot holes in a movie, and it’s my job to ask the questions that everybody is trying not to ask. After all, what we saw at the PDC wasn’t a product. It wasn’t even a technology preview. We (rather gratefully) got a peek at what the Smart Folks at Microsoft are scribbling on their white boards. This is the time to point out any problems — when they can be incorporated into the final design documents.


For instance, I carefully noted that, in all the LINQ demos, nobody so much as mentioned C++. (A Microsoft PR person later told me that each language group sets its own priorities, and the Visual C++ team hasn’t decided yet where LINQ will fit.) Also, everybody has asked programmers about the wondrous capabilities of LINQ; what do the DBAs think? I realize that DBAs and programmers rarely drink at the same bars, but still…!


My response to her concerns about LINQ and Visual C++ is:



You should use the right tool for the right job. Anyone programming databases with C++ is almost certainly using the wrong tool. VB.NET and C# are much better tools for doing database development. Hopefully this is so obvious I don’t have to explain why.


She continued with (emphasis mine):



The larger question, though, is about the much-vaunted ability for a Visual Studio programmer to access databases without knowing SQL or XML particularly well. I’m wary of anything that promises “ease of use” by assuring someone that it’s okay to remain ignorant. I remember, far too well, how the computer industry promised to make computers “idiot-proof.” The result? We now have a lot of idiots using computers. Sometimes, making things simpler _isn’t_ a good idea.


I’m not saying that LINQ is a bad thing. Far from it: I suspect it’s an elegant solution of the “Duh, why didn’t we think of this before?” variety. However, this is the time to raise the uncomfortable questions, and I don’t see very many people doing so.


Uh, as far as I can see, LINQ doesn’t hide SQL or XML, it just gives a much cleaner syntax for accessing that can be syntax checked at compile time.  A developer would still need to write SQL and to understand XML. For example, this looks like to me it requires knowing SQL:



Dim custs() As Customer = …
Dim waCusts = Select c.Name, c.City From c In custs Where c.State = “WA”


Did Esther really look at LINQ before writing this editorial?  Maybe people aren’t asking the questions because the questions are not relevent?

Anyone know of a T-SQL Code Formatter?

I know I’m the tool man (with Xtras.Net) but I can’t seem to find a T-SQL code formatter and wondered if anyone else knows about one? 

I’m got someone inhouse who writes his SELECT, INSERT, and UPDATE statements in stored procs as if his monitor were infinitely wide, and only ever uses the tab or enter key if forced to do so.  His SQL code has no whitespace, and his statements never line up; whenever I have to touch anything he does I have to spend 15 to 30 minutes reformatting his code.  He also won’t comment his code telling me "I’m in development on it which is why I don’t format or comment; I’ll clean it up later when its finished" which of course never happens. I’m pretty much a standard’s zealot yet no matter what I do, I can’t get him to change.  I feel like the pious Baptist minister whose teenage daughter drinks, smokes, listens to rock music, and is having sex with the entire football team! 

We just had a big blow up for the nth time about it and his response to me was "Then adopt my style, I like it better, I can’t read your code when you format it." (If you want to see how I format T-SQL, you can read some of read my prior posts.)  Jeesh I’m frustrated.  Do any of you have to deal with this situation?  Is it me?  Any thoughts about how to get him to feel like there is value to working with standards everyone on a team can agree on?

Does anyone know of a T-SQL code formatting tool and standards management tool I can run on an entire SQL Server database?

Features I REALLY want in TSQL, Part #3

Refactoring.


Yes, I want refactoring in TSQL/SQL Server. What do I mean by Refactoring in SQL?


Specifically, I want to be able to evolve my database schema without breaking the application. For me at least, I frequently evolve the naming of my database schema as I’m adding features to deployed apps, but it is really hard to know for sure that I’ve updated everything that depends on those changes.


I have seen tools that generally hunt down all the references to tables, views, stored procedures, fields, etc. within SQL Server objects and update them for you. Some tools even look outside in VB.NET or C# code. But those techniques are, by their very nature, incomplete. I mean, what about dynamic SQL?


So what do I think is needed?


The first needed feature would be aliases for SQL Server objects. If I could define an alias for any SQL Server object such as a table, view, stored procedure, field, etc and have it behave when referenced exactly as the object it aliased, I would have part of what I need. For example, I envision you could define a alias called [Item] on the table [Inventory] like so:


CREATE TABLE ALIAS Item FROM Inventory

Then the following two SQL statements would be identical in function:


SELECT * FROM Inventory
SELECT * FROM Item

As would these SQL statements:


UPDATE Inventory SET Price= Price * 1.01
UPDATE Item SET Price= Price * 1.01

The second feature required would be the ability to tag an object as having all accesses to it logged:


ALTER TABLE ALIAS Item LOG ACCESS

Once you tagged a SQL Server object with “log access”, SQL Server would write a record to a special log table every time that object was touched in any way. Here is a potential Access Log Structure:


CREATE TABLE AccessLog
(
ObjectName nvarchar(255),
DateAccessed datetime,
AppName nvarchar(100),
ConnectionString nvarchar(255),
ConnectionID int,
ConnectionName nvarchar(100),
ExecuteStatement nvarchar(8000),
ExecuteID int,
ExecuteName nvarchar(100)
)

If you note some of the fields in my hypothetical access log you’ll see data that currently does not exist to be logged, such as ConnectionName and ExecuteID. A third “nice to have” feature would be the ability to give unique IDs and unique names in VB.NET and C# code where a connection is made and every place in code where a SQL statement is executed just like providing error numbers in error handlers.


With the first two features I could rename any SQL Server object, create an alias to that object using its old name, and then mark the alias to be logged. Then I could run my app, watch the log file, and see if I missed any references to the old names. If so I could easily use the logged data to track down the places I missed, especially if I had the third feature that allowed me to annotate ADO.NET calls with reference information. I could even deploy an app and let it run for a month or two while monitoring the access logs to make sure I didn’t miss anything.


I sure do wish SQL Server had those features. Don’t you?

Tags: ,

Features I REALLY want in TSQL, Part #2

This next feature isn’t exactly a TSQL feature, and it is not highly visionary, but after spending literally the past eight (8) hours (I kid you not) using SQL tools to manipulate data and evolve some table structures, I remembered it is something I really would like to see.



What I’d like to see added are Default Aliases for tables and fields.



To illustrate the value this feature would provide, let’s use a simple query. The following query is one take verbatim from SQL Enterprise Manager after clicking on tables and fields:


SELECT     dbo.tblVendor.Name, dbo.tblProduct.Name AS Expr1, dbo.tblItem.Name AS Expr2
FROM dbo.tblVendor INNER JOIN
dbo.tblProduct ON dbo.tblVendor.ID = dbo.tblProduct.VendorID INNER JOIN
dbo.tblItem ON dbo.tblProduct.ID = dbo.tblItem.ProductID

Before using the query I always have to edit it. I start by giving each table a simple alias to clean up the query:


SELECT     v.Name, p.Name AS Expr1, i.Name AS Expr2
FROM dbo.tblVendor v INNER JOIN
dbo.tblProduct p ON v.ID = p.VendorID INNER JOIN
dbo.tblItem i ON p.ID = i.ProductID

Next I give the fields meaningful aliases instead of those annoying “ExprN” aliases:


SELECT     v.Name AS VendorName, p.Name AS ProductName, i.Name AS ItemName
FROM dbo.tblVendor v INNER JOIN
dbo.tblProduct p ON v.ID = p.VendorID INNER JOIN
dbo.tblItem i ON p.ID = i.ProductID

Lastly I typically copy to SQL Query Analyzer and reformat so I can actually read and understand the query, and I also prefix the tables with database names for readability and maintainability assuming I plan to save the query in a stored proc, view, or function:


SELECT
v.Name AS VendorName,
p.Name AS ProductName,
i.Name AS ItemName
FROM
Products..tblVendor v
INNER JOIN Products..tblProduct p ON v.ID = p.VendorID
INNER JOIN Products..dbo.tblItem i ON p.ID = i.ProductID

Whew! That’s an awful lot of time spent just to get a clean workable query. Yes I know, some people say I am too anal, but my brain can’t process much at any one time so I always have to simplify and clean things up like this. Anyway, if SQL Server had default aliases for fields and tables and a few configuration options, a future SQL IDE could automagically add all my table and field aliases and database prefixes.



Now I know the first comment I’ll get will be someone saying: “But your suggestions makes no sense because you can’t be sure not to have potential conflicts among your tables and fields such as when you have a tblItem and tblInvoice and you set each to use “i” as the default alias.” To which my reply will be “So what? I’m proposing default aliases for fields and tables as “hints” for SQL IDEs, not as something people would depend on for robustness!



I would envision a future SQL IDE would have a set of configuration options something like this (my configuration options imply multiple default aliases for tables and fields that could be used in case of conflict with the primary default alias):




  • Always alias tables with their default alias:

    • [  ] Yes
    • [  ] No

  • How to handle table alias conflicts

    • [  ] Append a numeral: i.e. “i”, “i1″, “i2″, etc.
    • [  ] Use secondary default: i.e. try “i” then try “inv”, etc.
    • [  ] Ask Me

  • Default field aliases

    • [  ] Always alias fields
    • [  ] Only alias fields when field conflicts with another field

  • How to handle field alias conflicts

    • [  ] Append a numeral: i.e. “i”, “i1″, “i2″, etc.
    • [  ] Use secondary default: i.e. try “ItemName” then try “AlternateItemName”, etc.
    • [  ] Ask Me

  • Always prefix tables with database names

    • [  ] Yes
    • [  ] No

A really saavy Microsoft usability tester looking over my shoulder as I worked with SQL day in and day out would come up with the set of features I describe here because it would really reduce the amount of time I have to spend doing rote tasks in SQL.  Beyond that, I’d love to see configuration options that specify how to format SQL statements, but that’s beyond the scope of anything I’d like to post today.

Tags: ,

Features I REALLY want in TSQL, Part #1

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:


SELECT
ID,
Name,
Company,
Street,
City,
State,
PostCode,
Country
FROM
tblCustomer

INSERT
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

The first feature I’d like to see is “Lists” for use as both “Field Lists” “Value Lists.” For example:
CREATE FIELDLIST flCustomer AS
ID NOINSERT NOUPDATE,
Name,
Company,
Street,
City,
State,
PostCode,
Country
Using the above field and value lists, we could make the SELECT, INSERT, and UPDATE statements much more maintainable:
SELECT FIELDLIST
flCustomer
FROM
tblCustomer

INSERT
tblCustomer
FIELDLIST
flCustomer
VALUELIST
vlMikeSchinkel

UPDATE
tblCustomer
SET FIELDLIST
flCustomer
VALUES
(
‘Mike Schinkel’,
‘Xtras, Inc.’,
‘1360 Seaboard Industrial Blvd’,
‘Atlanta’,
‘GA’,
‘30318′,
‘USA’
)
WHERE
ID=12345

Also, it would be useful to be able to create a valuelist from a function:
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
[email protected]
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?” :-)

Tags: ,