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?
Hi, Mike: You may already be aware of this, but you can accomplish your "alias" feature today using Views. For example, if you create a View named Item and define it as SELECT * FROM Inventory, then you can SELECT * FROM Item and UPDATE Item SET Price = Price * 1.01, just as you suggest.
I would, however, like intelligent renaming to be built into SQL Server. If I rename Item to something else, I’d still have to go through and change the name in my stored procs and code manually (or with a third-party tool).
Phil Weber>> but you can accomplish your "alias" feature today using Views
Thanks for the comment Phil, but actually you can only accomplish with a view a fraction of what I envision my "aliases" would allow. Views only provide "read-only" aliases. I’m looking for "writable" aliases. A view can’t provide an alias for a stored procedure nor for a function nor for another other object besides a table or other view. Further, a view won’t provide an alias for a field name that would allow it to be updated (unless I totally misunderstand.)
Also, views don’t allow the logging which is the other critical feature. If I don’t know if a name is still be used, how can I ever get rid of it?
"Views only provide ‘read-only’ aliases. I’m looking for ‘writable’ aliases…A view won’t provide an alias for a field name that would allow it to be updated…"
Au contraire, mon ami. Try this: Create a view in Northwind named "Slaves":
CREATE VIEW dbo.Slaves
AS
SELECT EmployeeID AS ID, LastName, FirstName, TitleOfCourtesy AS Salutation
FROM dbo.Employees
Now go into Query Analyzer and execute the following:
UPDATE Slaves SET Salutation = ‘Mrs.’ WHERE ID = 1
Well, look at that! :-) The underlying column (TitleOfCourtesy) of the underlying table (Employees) was updated based on the value of the underlying key field (EmployeeID), even though all three were aliased.
"A view can’t provide an alias for a stored procedure nor for a function nor for another other object besides a table or other view."
Actually, a view can alias tables, functions and other views, but you’re right, I know of no way to alias a stored procedure. But can’t a function or a view do anything a stored proc can?
You got me, I didn’t know an aliased field in a view could be updated.
Still, a view doesn’t give me 100% of what I’m trying to achieve. Let’s assume a table called "Product." It has a field called "Description", but for consistency with names in other tables I want to change it to "Name." Now I *could* rename "Product" to "Product2" and then create a view called "Product" on Products2, and in that view alias "Description" with "Name", but then that’s a hack. Why do I need to create another object "Product2"; that just polutes the namespace and creates further maintenance artifacts. The aliases I proposed could handle it elegantly. And a view still doesn’t offer the logging capability I proposed so I can verify when it is used and when it is not.
As for view being able to do anything a stored proc can? No, it can’t alias an existing stored proc, which is exactly what I need it to do. I proposed this for refactoring, and in refactoring you can’t assume a given set of conventions were used (like no stored procs); after all, isn’t that the point of refactoring, to clean up old code?
As for views aliasing functions, unless I miss my guess, they can’t alias scalar functions and behave the same. For example, assume Foo():
USE tempdb
CREATE FUNCTION Foo()
RETURNS int
BEGIN
RETURN 1
END
Here is a stupid example showing it in use:
SELECT GETDATE() WHERE Tempdb.dbo.Foo()=1
Yes you can alias it:
CREATE VIEW TmpFoo AS SELECT Tempdb.dbo.Foo() AS x
But now how are you going to use TmpFoo() in the same context that you used Foo()? None of these work:
SELECT GETDATE() WHERE Tempdb.dbo.TmpFoo()=1
SELECT GETDATE() WHERE dbo.TmpFoo()=1
SELECT GETDATE() WHERE Tempdb..TmpFoo()=1
And a view can’t duplicate Users or Roles or Rules or Defaults, or User Defined Data Types (I thought about explicitly mentioning them, but then I thought "Nah, everyone will assume those too." I guess not. :)
So in summary, my reason for proposing aliases was so a DBA/developer could make a name change without breaking any code, and then monitor the access logs to see if the old name was still used. Yes a view can do some of the things my proposed aliases can do, but a view does not do everything I need, and in the cases it does it is not elegent which would lead to more maintenance problems, not less.
When all you have is a hammer, every problem looks like a nail. But I’m not satisfied with just a hammer; I want a screwdriver and a wrench too! :)
silly, silly, silly boy…
you should use stored procedures to access your db objects. you can have your “aliases” there as variables and it’s easy to identify what you have to update after a rename using sp_depend stored proc. also, if you need to log access to your tables, you can easily do it by creating a table you have so bravely designed and a little store procedure that writes all the details there for you. this stored proc would be called from all stored procs that access your tables.
@Greg: silly, silly, silly boy… you should use stored procedures to access your db objects.
Ah, just what I need. A zealot who doesn’t realize that there are often more than one applications that update a table.
But isn’t that a good definition of a zealot; someone who doesn’t and usually isn’t willing the recognize the valid concerns of others? ;-)