THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search
Home Blogs Downloads   Opml View as RSS news feed in XML

Louis Davidson

2008: Declaring and instantiating a value

Ok, I admit it.  Sometimes the least important things are the most fun.  As I try to get my blog back up and kicking again after a few months of holiday fun coupled with some dreary personal life things (a death in the family and lots of sickness/busyness, mostly,) I felt the need to write about another little time saving feature that you might not have heard of. Ever if you have it is still cool.

This topic is declaring and instantiating a value in a single statement.  So what used to be:

DECLARE @i int
SET @i = 1

Can now be:

DECLARE @i int = 1

Ho hum, I had thought earlier when I first saw this.  You know, it saves me 3 keystrokes. But today, I was doing some writing in my book and I realized that it isn't just limited to literals (it had just never crossed my mind) so when I was creating my savepoint names for nested savepoints.  You can use literals and functions, whatever you need.

DECLARE @savepoint nvarchar(128) = cast(object_name(@@procid) AS nvarchar(125)) + cast(@@nestlevel AS nvarchar(3))

All in one line of code...clean, neat.  And when I was writing a trigger that I could do this:

DECLARE @rowsAffected int = @@rowcount, --stores the number of rows affected
               @msg varchar(2000) = '' --used to hold the error message

And save a couple of lines of code, and now, how much cleaner and effective is this code?  Not only does it save two lines of code to do the SET (or you could use SELECT), but it saves lines of whitespace too. 

Will this save a lot of time?  Probably not, but it is one of those long desired features that we SQL Server programmers are so glad to be getting.  In my case it is going to cost me time because I have to go back and re-edit places where I forgot about this syntax and didn't use it in my new book, which is one of the worst parts of writing.  When you mess up and forget something that is new and more or less essential, you can have to edit large amounts of code/text.

 The comment from Steve got me thinking if you could use a query to instatiate the value.   Survey says:

 create table fred
(
    value   char(1)
)
go
insert into fred
values ('a'),('b'),('c')
go
   
declare @value char(1) = (select MAX(value) from fred)

select @value

 YES! This returns 'c', just as you would expect.  Okay, so now this is even cooler.  It doesn't work with table types though. And since we are feature complete, I assume it probably won't (I didn't expect it too, but it never hurts to try.)

Published Saturday, March 01, 2008 7:40 PM by drsql
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

David Markle said:

No joke.  That's a serious addition to the system.  Anything that removes spurious lines of code from my stored procedures is quite welcome, thank you very much.  

Another two things I'd love to have in SQL Server:

1) Database--level constants.   I know you can sort of approximate this with functions, but it's just not the same.  I'd love to be able to write something like:

SELECT * FROM dbo.Coupons WHERE ExpirationDate = @@NEVER;  (Where @@NEVER is a user-defined database constant, perhaps representing the biggest DATETIME value SQL Server can store)

2) A way to declare a variable or a table variable as "whatever is in XXX table"... Something like:

DECLARE @CashMoney TYPE_OF(dbo.DrugSales.Benjamins);

I believe Oracle has something like this, but it's been a while since I've done PL/SQL...

March 2, 2008 10:49 AM
 

drsql said:

Have you posted these to the connect site (https://connect.microsoft.com/SQLServer/Feedback)?  You do and I will vote for them.  Both of these would be excellent time savers (though I am afraid number one would be abused something awful, but the second one is something  I have seen begged for quite often.  If you knew the exact syntax someone may have already posted the second one also.)

Thanks for the comment.

March 2, 2008 8:25 PM
 

steve dassin said:

>2) A way to declare a variable or a table variable as "whatever is in XXX table"... Now isn't this just cs 101, the idea of 'assigning' a value to a variable as you would an integer or string? But obviously there is no such concept of a table as a type which can be associated with a variable which encapsulates assignment like assigning an integer to a variable. Instead you have assignment without a variable, a rather crazy idea, in the form of a CTE. What you really want is simply this:

declare @MyTable=Orders

which is "declaring and instantiating a value in a single statement." Only for a table.

Now I do this all the time in dataphor, a relational system, that understands a type of table.

var MyTable:=Orders;

Variable MyTable by type inference inherits the table type of Orders which is nothing more than the column names and their datatypes. It also obtains the data of Orders. If you are at all familiar with LINQ you should see the similarity with it. This type of inheritance and assignment with a variable is just not possible in sql. You can't do things like this on an exclusive basis. Either 'all' tables are represented as a variable or they are not. The former is the relational model, the later is sql. Now MS had to move to a strongly typed runtime environment, net, to introduce the idea of encapsulating a table or better still a query in a variable. This is what a relational system would do. It is the same computer science. To represent a single table or query(code) in a single variable is what MS has been pursuing for over eight years. The equivalency of data and code as a representation in a variable. Impossible in sql. Unfortuneately MS went the object route instead of relational. So an sql table is a class is a variable is an object. A LINQ query with a particular projection is an anonymous type. There are no anonymous types in a relational system, only specific table types with different properties and uses from an anonymous type. And a class/entity is a different beast from a table (variable). But the foundation is the same, it's the same computer science. And all the connect feedback in the world won't change sql to be able to support a type of table variable.

Now I think within every sql programmer there lies a relational programmer waiting to pop out (especially when they realize all the great things they can do). You can download dataphor here:

http://databaseconsultinggroup.com/downloads/

The relational model, thinking in terms of variable types as well as sets, is the real alternative to the object model, LINQ/efm, in net. Sql folks will, I think, have a greater affinity for it than what MS is doing. And if LINQ/EFM collapses under the extra heavy weight required to support it the relational model may even look better:)

Much more on going relational at:

www.beyondsql.blogspot.com

And yes you are still using sql server and can communicate with it in t-sql just as you do now if you wish.

best,

steve

March 3, 2008 2:06 AM
 

steve dassin said:

(Apologies if post is repeated)

>2) A way to declare a variable or a table variable as "whatever is in XXX table"... Now isn't this just cs 101, the idea of 'assigning' a value to a variable as you would an integer or string? But obviously there is no such concept of a table as a type which can be associated with a variable which encapsulates assignment like assigning an integer to a variable. Instead you have assignment without a variable, a rather crazy idea, in the form of a CTE. What you really want is simply this:

declare @MyTable=Orders

which is "declaring and instantiating a value in a single statement." Only for a table.

Now I do this all the time in dataphor, a relational system, that understands a type of table.

var MyTable:=Orders;

Variable MyTable by type inference inherits the table type of Orders which is nothing more than the column names and their datatypes. It also obtains the data of Orders. If you are at all familiar with LINQ you should see the similarity with it. This type of inheritance and assignment with a variable is just not possible in sql. You can't do things like this on an exclusive basis. Either 'all' tables are represented as a variable or they are not. The former is the relational model, the later is sql. Now MS had to move to a strongly typed runtime environment, net, to introduce the idea of encapsulating a table or better still a query in a variable. This is what a relational system would do. It is the same computer science. To represent a single table or query(code) in a single variable is what MS has been pursuing for over eight years. The equivalency of data and code as a representation in a variable. Impossible in sql. Unfortuneately MS went the object route instead of relational. So an sql table is a class is a variable is an object. A LINQ query with a particular projection is an anonymous type. There are no anonymous types in a relational system, only specific table types with different properties and uses from an anonymous type. And a class/entity is a different beast from a table (variable). But the foundation is the same, it's the same computer science. And all the connect feedback in the world won't change sql to be able to support a type of table variable.

Now I think within every sql programmer there lies a relational programmer waiting to pop out (especially when they realize all the great things they can do). You can download dataphor here:

http://databaseconsultinggroup.com/downloads/

The relational model, thinking in terms of variable types as well as sets, is the real alternative to the object model, LINQ/efm, in net. Sql folks will, I think, have a greater affinity for it than what MS is doing. And if LINQ/EFM collapses under the extra heavy weight required to support it the relational model may even look better:)

Much more on going relational at:

www.beyondsql.blogspot.com

And yes you are still using sql server and can communicate with it in t-sql just as you do now if you wish.

best,

steve

March 3, 2008 3:15 AM
 

Steve Dassin said:

(Apologies if post is repeated)

>2) A way to declare a variable or a table variable as "whatever is in XXX table"... Now isn't this just cs 101, the idea of 'assigning' a value to a variable as you would an integer or string? But obviously there is no such concept of a table as a type which can be associated with a variable which encapsulates assignment like assigning an integer to a variable. Instead you have assignment without a variable, a rather crazy idea, in the form of a CTE. What you really want is simply this:

declare @MyTable=Orders

which is "declaring and instantiating a value in a single statement." Only for a table.

Now I do this all the time in dataphor, a relational system, that understands a type of table.

var MyTable:=Orders;

Variable MyTable by type inference inherits the table type of Orders which is nothing more than the column names and their datatypes. It also obtains the data of Orders. If you are at all familiar with LINQ you should see the similarity with it. This type of inheritance and assignment with a variable is just not possible in sql. You can't do things like this on an exclusive basis. Either 'all' tables are represented as a variable or they are not. The former is the relational model, the later is sql. Now MS had to move to a strongly typed runtime environment, net, to introduce the idea of encapsulating a table or better still a query in a variable. This is what a relational system would do. It is the same computer science. To represent a single table or query(code) in a single variable is what MS has been pursuing for over eight years. The equivalency of data and code as a representation in a variable. Impossible in sql. Unfortuneately MS went the object route instead of relational. So an sql table is a class is a variable is an object. A LINQ query with a particular projection is an anonymous type. There are no anonymous types in a relational system, only specific table types with different properties and uses from an anonymous type. And a class/entity is a different beast from a table (variable). But the foundation is the same, it's the same computer science. And all the connect feedback in the world won't change sql to be able to support a type of table variable.

Now I think within every sql programmer there lies a relational programmer waiting to pop out (especially when they realize all the great things they can do). You can download dataphor here:

http://databaseconsultinggroup.com/downloads/

The relational model, thinking in terms of variable types as well as sets, is the real alternative to the object model, LINQ/efm, in net. Sql folks will, I think, have a greater affinity for it than what MS is doing. And if LINQ/EFM collapses under the extra heavy weight required to support it the relational model may even look better:)

Much more on going relational at:

www.beyondsql.blogspot.com

And yes you are still using sql server and can communicate with it in t-sql just as you do now if you wish.

best,

steve

March 3, 2008 3:37 AM
 

Steve Dassin said:

(Apologies if post is repeated)

>2) A way to declare a variable or a table variable as "whatever is in XXX table"... Now isn't this just cs 101, the idea of 'assigning' a value to a variable as you would an integer or string? But obviously there is no such concept of a table as a type which can be associated with a variable which encapsulates assignment like assigning an integer to a variable. Instead you have assignment without a variable, a rather crazy idea, in the form of a CTE. What you really want is simply this:

declare @MyTable=Orders

which is "declaring and instantiating a value in a single statement." Only for a table.

Now I do this all the time in dataphor, a relational system, that understands a type of table.

var MyTable:=Orders;

Variable MyTable by type inference inherits the table type of Orders which is nothing more than the column names and their datatypes. It also obtains the data of Orders. If you are at all familiar with LINQ you should see the similarity with it. This type of inheritance and assignment with a variable is just not possible in sql. You can't do things like this on an exclusive basis. Either 'all' tables are represented as a variable or they are not. The former is the relational model, the later is sql. Now MS had to move to a strongly typed runtime environment, net, to introduce the idea of encapsulating a table or better still a query in a variable. This is what a relational system would do. It is the same computer science. To represent a single table or query(code) in a single variable is what MS has been pursuing for over eight years. The equivalency of data and code as a representation in a variable. Impossible in sql. Unfortuneately MS went the object route instead of relational. So an sql table is a class is a variable is an object. A LINQ query with a particular projection is an anonymous type. There are no anonymous types in a relational system, only specific table types with different properties and uses from an anonymous type. And a class/entity is a different beast from a table (variable). But the foundation is the same, it's the same computer science. And all the connect feedback in the world won't change sql to be able to support a type of table variable.

Now I think within every sql programmer there lies a relational programmer waiting to pop out (especially when they realize all the great things they can do). You can download dataphor here:

http://databaseconsultinggroup.com/downloads/

The relational model, thinking in terms of variable types as well as sets, is the real alternative to the object model, LINQ/efm, in net. Sql folks will, I think, have a greater affinity for it than what MS is doing. And if LINQ/EFM collapses under the extra heavy weight required to support it the relational model may even look better:)

Much more on going relational at:

www.beyondsql.blogspot.com

And yes you are still using sql server and can communicate with it in t-sql just as you do now if you wish.

best,

steve

March 3, 2008 3:58 AM
 

steve said:

(Apologies if post is repeated)

>2) A way to declare a variable or a table variable as "whatever is in XXX table"... Now isn't this just cs 101, the idea of 'assigning' a value to a variable as you would an integer or string? But obviously there is no such concept of a table as a type which can be associated with a variable which encapsulates assignment like assigning an integer to a variable. Instead you have assignment without a variable, a rather crazy idea, in the form of a CTE. What you really want is simply this:

declare @MyTable=Orders

which is "declaring and instantiating a value in a single statement." Only for a table.

Now I do this all the time in dataphor, a relational system, that understands a type of table.

var MyTable:=Orders;

Variable MyTable by type inference inherits the table type of Orders which is nothing more than the column names and their datatypes. It also obtains the data of Orders. If you are at all familiar with LINQ you should see the similarity with it. This type of inheritance and assignment with a variable is just not possible in sql. You can't do things like this on an exclusive basis. Either 'all' tables are represented as a variable or they are not. The former is the relational model, the later is sql. Now MS had to move to a strongly typed runtime environment, net, to introduce the idea of encapsulating a table or better still a query in a variable. This is what a relational system would do. It is the same computer science. To represent a single table or query(code) in a single variable is what MS has been pursuing for over eight years. The equivalency of data and code as a representation in a variable. Impossible in sql. Unfortuneately MS went the object route instead of relational. So an sql table is a class is a variable is an object. A LINQ query with a particular projection is an anonymous type. There are no anonymous types in a relational system, only specific table types with different properties and uses from an anonymous type. And a class/entity is a different beast from a table (variable). But the foundation is the same, it's the same computer science. And all the connect feedback in the world won't change sql to be able to support a type of table variable.

Now I think within every sql programmer there lies a relational programmer waiting to pop out (especially when they realize all the great things they can do). You can download dataphor here:

http://databaseconsultinggroup.com/downloads/

The relational model, thinking in terms of variable types as well as sets, is the real alternative to the object model, LINQ/efm, in net. Sql folks will, I think, have a greater affinity for it than what MS is doing. And if LINQ/EFM collapses under the extra heavy weight required to support it the relational model may even look better:)

Much more on going relational at:

www.beyondsql.blogspot.com

And yes you are still using sql server and can communicate with it in t-sql just as you do now if you wish.

best,

steve

March 3, 2008 4:24 AM
 

steve dassin said:

(Apologies if post is repeated)

>2) A way to declare a variable or a table variable as "whatever is in XXX table"... Now isn't this just cs 101, the idea of 'assigning' a value to a variable as you would an integer or string? But obviously there is no such concept of a table as a type which can be associated with a variable which encapsulates assignment like assigning an integer to a variable. Instead you have assignment without a variable, a rather crazy idea, in the form of a CTE. What you really want is simply this:

declare @MyTable=Orders

which is "declaring and instantiating a value in a single statement." Only for a table.

Now I do this all the time in dataphor, a relational system, that understands a type of table.

var MyTable:=Orders;

Variable MyTable by type inference inherits the table type of Orders which is nothing more than the column names and their datatypes. It also obtains the data of Orders. If you are at all familiar with LINQ you should see the similarity with it. This type of inheritance and assignment with a variable is just not possible in sql. You can't do things like this on an exclusive basis. Either 'all' tables are represented as a variable or they are not. The former is the relational model, the later is sql. Now MS had to move to a strongly typed runtime environment, net, to introduce the idea of encapsulating a table or better still a query in a variable. This is what a relational system would do. It is the same computer science. To represent a single table or query(code) in a single variable is what MS has been pursuing for over eight years. The equivalency of data and code as a representation in a variable. Impossible in sql. Unfortuneately MS went the object route instead of relational. So an sql table is a class is a variable is an object. A LINQ query with a particular projection is an anonymous type. There are no anonymous types in a relational system, only specific table types with different properties and uses from an anonymous type. And a class/entity is a different beast from a table (variable). But the foundation is the same, it's the same computer science. And all the connect feedback in the world won't change sql to be able to support a type of table variable.

Now I think within every sql programmer there lies a relational programmer waiting to pop out (especially when they realize all the great things they can do). You can download dataphor here:

http://databaseconsultinggroup.com/downloads/

The relational model, thinking in terms of variable types as well as sets, is the real alternative to the object model, LINQ/efm, in net. Sql folks will, I think, have a greater affinity for it than what MS is doing. And if LINQ/EFM collapses under the extra heavy weight required to support it the relational model may even look better:)

Much more on going relational at:

www.beyondsql.blogspot.com

And yes you are still using sql server and can communicate with it in t-sql just as you do now if you wish.

best,

steve

March 3, 2008 4:55 AM
 

drsql said:

Steve (and yes it was repeated, but I just kept the last one),

I think you missed the point of the comment (I know I did when I first read it.)  It isn't :

DECLARE @CashMoney TYPE_OF(<table>);

it is

DECLARE @CashMoney TYPE_OF(<table>.<column>);

In other words, you have a table:

CREATE TABLE fred

(

   value varchar(10)

)

DECLARE @value  TYPE_OF(fred.value)

So what you would end up with would be a variable of type varchar(10).  Now do:

ALTER TABLE fred ALTER COLUMN value varchar(100)

When the code gets recompiled, @value would be varchar(100).

>>And all the connect feedback in the world won't change sql to be able to support a type of table variable.<<

We already have the concept of a table variable in 2005.  You can declare:

declare @fred table(value char(1))

Taking the type_of ide a bit further along these same lines though, you would might get to do:

DECLARE @fred  TYPE_OF(fred)

Which would declare a table variable with the structure of the fred table, rather than having to type it out again., but either way, you wouldn't be talking about just the type.  

Taking it one step further, you would be able to do:

DECLARE @fred  TYPE_OF(fred) = (select * from fred)

and get a copy of the table into the variable, without having to code the tedious bits of SQL.  

You cannot use a query like this on a table instantiation, but you can use a query on a variable instantiation, which I have updated the post to note.

March 3, 2008 10:18 AM
 

Chuck Heinzelman said:

I'd love to see constants.  While you can simulate constants with a scalar UDF, you're most likely going to get a different query plan than you would by using a literal - even if the function is marked as deterministic.

March 3, 2008 6:19 PM
 

Steve Dassin said:

Hello Louis,

>I think you missed the point of the comment

Correcto, but my comment is still very relevant. The whole thrust of your comment  was right on! And proves my point of the existence in your head of a relational programmer trying to pop out from sql:)

Now the idea of  'DECLARE @value  TYPE_OF(fred.value)' is really just a special case of the idea of ' DECLARE @fred  TYPE_OF(fred) = (select * from fred)'. But the idea behind the ability to have these type of constructs rests on the truth of this:

>We already have the concept of a table variable in 2005.

But do we really have such a concept? Given two t-sql 'table variables':

declare @Fred table(value char(1))

insert @fred values ('A')

declare @Ethel table(value char(1))

insert @Ethel values ('C')

Now neither assignment nor comparison is possible:

set @Fred=@Ethel

if @Fred=@Ethel print 'Yes'

And the error message in both cases:

--Msg 137, Level 15, State 2, Line 11

--Must declare the scalar variable "@Fred".

underscores the idea that in the context of assignment and comparison only scalar values are recognized. So now you could live with the idea that a table variable is a different beast from an integer or string which both support assignment and comparison. Two different kinds of variables? But that's kind of crazy:) Now the only way for a compiler to perform assignment and do comparisons is to recognize the 'type' of variables involved. A variable can only be a variable of a specific type. And therein lies the answer. In declare @x int, we know int is the type.  In

declare @fred table(value char(1))

the assumption is 'table (value char(1))' is the type just like integer is the type. Wrong! This is where we've been had, a big gotcha:) It is not a type, if it was we could do assignments and comparisons. And even better @fred isn't even a variable! How could a variable exist without a type? It may be some sort of reference/pointer but it's not a variable in any way recognized in cs. There is no concept of a table variable in t-sql and no such concept of table type in sql. So what you rightfully wish for 'DECLARE @value  TYPE_OF(fred.value)', 'DECLARE @fred  TYPE_OF(fred) = (select * from fred)' makes no sense if there's is no type/variable for a table. This is why there's no 'TYPE_OF' of in t-sql/sql. Type of what?:) What your after are relational ideas like:

var Fred:typeof(Orders {OrderID,CustomerID,EmployeeID}):=

        Orders where ShipVia=3 {OrderID,CustomerID,EmployeeID};

which defines a variable of type table with a specific heading (columns/datatypes) and populates it with rows of data (of the same type). The idea of setting a variable based on a column in a table is based on recognizing a table type:

var Freds:typeof(Orders[].OrderID):=2;

Variable Fred is set to the type of scalar of OrderID, integer, and then set to a value. Welcome to my world:) I hope this all makes sense to you!

best,

steve

March 3, 2008 11:29 PM
 

steve dassin said:

Hello Louis,

>I think you missed the point of the comment

Correcto, but my comment is still very relevant. The whole thrust of your comment was right on! And proves my point of the existence in your head of a relational programmer trying to pop out from sql:)

Now the idea of  'DECLARE @value  TYPE_OF(fred.value)' is really just a special case of the idea of ' DECLARE @fred  TYPE_OF(fred) = (select * from fred)'. But the idea behind the ability to have these type of constructs rests on the truth of this:

>We already have the concept of a table variable in 2005.

But do we really have such a concept? Given two t-sql 'table variables':

declare @Fred table(value char(1))

insert @fred values ('A')

declare @Ethel table(value char(1))

insert @Ethel values ('C')

Now neither assignment nor comparison is possible:

set @Fred=@Ethel

if @Fred=@Ethel print 'Yes'

And the error message in both cases:

--Msg 137, Level 15, State 2, Line 11

--Must declare the scalar variable "@Fred".

underscores the idea that in the context of assignment and comparison only scalar values are recognized. So now you could live with the idea that a table variable is a different beast from an integer or string which both support assignment and comparison. Two different kinds of variables? But that's kind of crazy:) Now the only way for a compiler to perform assignment and do comparisons is to recognize the 'type' of variables involved. A variable can only be a variable of a specific type. And therein lies the answer. In declare @x int, we know int is the type.  In

declare @fred table(value char(1))

the assumption is 'table (value char(1))' is the type just like integer is the type. Wrong! This is where we've been had, a big gotcha:) It is not a type, if it was we could do assignments and comparisons. And even better @fred isn't even a variable! How could a variable exist without a type? It may be some sort of reference/pointer but it's not a variable in any way recognized in cs. There is no concept of a table variable in t-sql and no such concept of table type in sql. So what you rightfully wish for 'DECLARE @value  TYPE_OF(fred.value)', 'DECLARE @fred  TYPE_OF(fred) = (select * from fred)' makes no sense if there's is no type/variable for a table. This is why there's no 'TYPE_OF' of in t-sql/sql. Type of what?:) What your after are relational ideas like:

var Fred:typeof(Orders {OrderID,CustomerID,EmployeeID}):=

        Orders where ShipVia=3 {OrderID,CustomerID,EmployeeID};

which defines a variable of type table with a specific heading (columns/datatypes) and populates it with rows of data (of the same type). The idea of setting a variable based on a column in a table is based on recognizing a table type:

var Freds:typeof(Orders[].OrderID):=2;

Variable Fred is set to the type of scalar of OrderID, integer, and then set to a value. Welcome to my world:) I hope this all makes sense to you!

best,

steve

March 4, 2008 1:22 PM
 

Jcp said:

Sql server 2008

March 4, 2008 7:55 PM
 

drsql said:

Steve, I don't even vaguely want to get into a symantic battle over whether the table variable is a "real" variable.  I guess that discussion would be centered on "Does a variable of one type have to support every property that every other one does?"

Clearly the table type is a very limited type, though improving a very small amount in 2008 with the ability to pass it as a parameter to another object.  It doesn't support comparison, concatenation, but not all variables would, unless there is some formal definition of "variable" that I don't know.

>>the assumption is 'table (value char(1))' is the type just like integer is the type.<<

I don't assume that it is, nor do I, as the end user, really care *how* this happens, I am just asking that it behave more like what we desire to save keystrokes and upgrade costs.

But if you say that table (value char(1)) isn't a type, is something that is an unbounded array of 1 character string values a type?  Can it be then assigned to a variable.  Technically speaking, like you mention, @fred is really a pointer to a temp table, just like any pointer variable.  So clearly, the name of the operator TYPE_OF, could clearly be changed to something more technically correct :)

>>'DECLARE @value  TYPE_OF(fred.value)<<

I think of this as more of a *macro* of sorts, because the compile process would translate this to:

DECLARE @value char(1)

Since the point is to have the variable declared as the same type as the column, as a shorthand to having to go back into the code and manually declare it as char(2) if it changed.  

So no doubt, from an internal standpoint, you are no doubt very much correct, but from a usability standpoint (which is all that I care about, really.  When I do a SELECT statement, I don't care that my one statement is expanded into hundreds or even thousands of individual statements to do my bidding (in fact I do a little happy dance occasionally just because of that fact,)) it is the goal to have programmability improved a bit by saving a lookup to the system tables to find out the type of the value, while paying a small penalty for the compile process to do this for me.  

March 5, 2008 11:50 AM
 

Steve Dassin said:

Hello Louis,

Concerning your wish to have a variable declared as the same type as the column I found three Connect entries:

'Allow defining variables by existing column type instead of specific types'

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127043">https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127043

'Please, add support for complex data types in T-SQL, especially ability to derive types from existing database objects (tables, views and columns)'

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124506">https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124506

'Declare and initialise variables in a SQL statement'

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID

=309040

This wish goes back to early 2006 and is still active:) And I think you would also find this one interesting:

'Declare and initialise table variable using SELECT INTO'

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=316842

I do understand that you don't really care how these things are done. You just want the functionality. CJ Date has spent a lifetime arguing why you should care about how it's done and look how far its gotten him. So I guess I shouldn't feel so bad. I didn't anticipate I could alienate so many people by talking about what a variable is:) Interestingly in the 3rd one the MS guy talks of the types being 'inferred' from columns in a table. But we both know that's impossible, you can't infer, as in some type of inheritance, from something that is 'not' a variable:)  But I'll be nice and let it go here:) Hope all is well with you.

March 10, 2008 5:38 AM
 

drsql said:

"Alienate"?  Certainly not me.  I know what you mean and appreciate the discussion.  It isn't that I don't care how anything is done, but I have always subscribed to the philosophy that as a relational programmer you leave the internals alone and use the interface that is defined to do things in a set-based manner.  Kind of like how the mantra "cursors are evil" is really only true in the SQL syntax.  It is obvious that for every row and column that I deal with in a SELECT statement, *someone* down there in the bowels of the product is looping one record at a time fetching and manipulating one field at a time.

In 2008, you can have page and column level compression, but I don't "really" care from the standpoint that my code won't change significantly.  Of course, I am not blind to the implementation because unfortunately SQL Server is not perfect and can't (yet?) handle everything I throw at it.  However, if I do it "right" from a relational standpoint, then SQL Server can more readily (one hopes) do its job by working to a known standard.  

So what I want is for the SQL (or at least T-SQL) to deal with things in a manner as if they were variables, which will (for all intents and purposes) make them variables to those of us who use them.  Right now, they show some basic variable-like behavior (they aren't affected by transactions) but not in many cases (just like you have pointed out.)

I DEFINITELY appreciate the lower level programmers who deal with the bits/bytes/complex algorithms, who probably all have the same kinds of arguments when designing/implementing a feature.  In many ways I lament my inability to program in a lower level language because like many a nerd's dream, I would love to go to work on the product implementing features like this and all of the others.  But using the product in a manner that is fun and keeps my family in clothes/shelter/food/video games is good too :)

March 10, 2008 10:50 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites
©2006-2012 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement