Dataphor SQL RAC (Relational Application Companion)

A site of hope for those looking for a true relational database system

Tuesday, March 04, 2008

Is there really a table variable in sql server?

Is there really a table variable in Sql Server 2005?
I say no! 

The following is a response to the authors comment in his blog post:

Louis Davidson (drsql)
2008: Declaring and instantiating a value 

concerning his assertion of a table variable. As of now the author has not yet accepted my comment
for his blog  You can read the full blog article for another interesting comment I made
that was accepted 

The authors (drsql) comment:

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:



   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 

Here is my response:

Hello Louis,
>I think you missed the point of the comment 
Correct, 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.
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! 


Servidores said...

Hello. This post is likeable, and your blog is very interesting, congratulations :-). I will add in my blogroll =). If possible gives a last there on my blog, it is about the Servidor, I hope you enjoy. The address is A hug.

Anonymous said...

Can anyone recommend the well-priced Endpoint Security program for a small IT service company like mine? Does anyone use or How do they compare to these guys I found recently: [url=] N-able N-central service management
[/url] ? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!

Anonymous said...

Keep working ,great job!

Anonymous said...

I will not concur on it. I over nice post. Especially the designation attracted me to study the intact story.

Anonymous said...

Genial post and this fill someone in on helped me alot in my college assignement. Say thank you you on your information.

Anonymous said...

Genial post and this post helped me alot in my college assignement. Gratefulness you seeking your information.

Anonymous said...

Easily I assent to but I contemplate the post should secure more info then it has.

Anonymous said...

I would appreciate more visual materials, to make your blog more attractive, but your writing style really compensates it. But there is always place for improvement

Anonymous said...

split augmentin pillsallegra ultram stimula perscription drugs

[url=]buy levitra online dream pharmaceutical[/url]

Anonymous said...


Safety is always Mansour Engineering highest design goal. Layout, materials, systems and processes are selected or specified to meet or exceed code compliance.

[url=] click here to go to Mansour Engineering[/url]

Anonymous said...

i truly adore all your posting kind, very charming.
don't quit and keep creating in all honesty , because it just very well worth to look through it.
excited to look over much more of your stories, cheers :)

qishaya said...

one day i went shopping outside,and in an ed hardy store,I found some kinds of ed hardy i love most they are Your website is really good Thank you for the information ed hardy ed hardy ed hardy clothing ed hardy clothing ed hardy shoes ed hardy shoes don ed hardy don ed hardy ed hardy clothes ed hardy clothes ed hardy bags ed hardy bags ed hardy swimwear ed hardy swimwear ed hardy jeans ed hardy jeans ed hardy mens ed hardy mens Thank you for the information

Anonymous said...

Good day

[url= ] audio Explanation of the English Quran [/url]

Islam is well-suited only to the Arabs because:

most Muslims are Arabs

the Qur'an is in Arabic

The first reason for this misconception is far from the truth. Of the more than one billion Muslims around the world, only 18% are Arab. The most populous Muslim country in the world is Indonesia. The second most populous Muslim country is Bangladesh. Neither of these countries is Arab.

Islam is the fastest growing major religion in the world (Encyclopedia Britannica). It has adherents on all the populated continents, and is accessible to Arabs and non-Arabs alike. In the United States, Muslims are expected to become the largest religious minority around the turn of the century, outstripping the Jews.

The universal appeal of Islam to Arabs and non-Arabs comes in spite of the Qur'an being in Arabic. Allah states in the Qur'an that this way of life called Islam is for all people (translation follows),

[21:107] And We (Allah) have not sent you (Muhammad) but as a mercy to the worlds.

[34:28] We have not sent you but as a universal (Messenger) to men, giving them glad tidings, and warning them (against sin), but most men understand not.

Moreover, the Creator does not use the color or mother tongue of people to judge them, be they Arab or not. Instead, it is the level of their awareness of Allah which is the criterion,

[49:13] O mankind! We created you from a single (pair) of a male and a female, and made you into nations and tribes, that you may know each other (not that you may despise each other). Verily the most honored of you in the sight of Allah is (he who is) the most righteous of you. And Allah has full knowledge and is well acquainted (with all things)."

The choice of Arabic as the language of the Qur'an is explained very simply and clearly (translation follows),

[41:44] And if We (Allah) had made it a Qur'an in a foreign tongue, they would certainly have said: "Why have not its communications been made clear? What! a foreign (tongue) and an Arab!" Say: It is, to those who believe, a guidance and a healing; and (as for) those who do not believe, there is a heaviness in their ears and it is obscure to them; these shall be called to from a far-off place.

However, it is also important to emphasize that the Qur'an in its revealed form in Arabic is the literal Word of the Creator - but any translation is not. Each translation is more accurately called a translation of an interpretation, for every translator includes his or her own bias.

For more details [url= ]click her[/url]


All of us will die one day INCLUDING YOU.

so before you die you must find out where the HELL you are going too.

You must find out

who is our savior Jesus or ?

You may sleep tonight and never get up in the morning?

You may die today.

You may die within a week

You may die within a month

you may die within a year

you may die within the next ten years

one thing for sure

You will die

so find out how is our savior so that he may save you.

Anonymous said...

portable ebook reader ebook shareware [url=]internet marketing free ebook[/url] free ebook reader for mac

Anonymous said...

stormreach ebook torrent free wholesale ebook [url=]free ebook business[/url] greyhawk ebook

Anonymous said...

sirham photo free software what is for the record software [url=]memorex dvd label software[/url] temperture sensors software apple
[url=]Multimedia & Entertainment - Download OEM, Software Sale, OEM Software[/url] xfi software guards for system address

Anonymous said...

computer software operating systems microsoft windows garden design pc software [url=]wine industry club software[/url] memory mixer software for scrapbooking
[url=]Multimedia & Entertainment - Software Store[/url] windows mobile print software

Anonymous said...

tax form accounting software trucking tanner software [url=]gamemaster software[/url] palm e2 free software
[url=]Other - Software Store[/url] computer software alpharretta ga

Anonymous said...

xbrl software free blackberry 9000 desktop software [url=]windows vista and avery designpro software[/url] outdoor public event permit software programs
[url=]Office Tools - Software Store[/url] winlogin error unknown software exception

Anonymous said...

colorado state board of pharmacy pharmacy technician jobs utah [url=]krogers pharmacy[/url]
montreal pharmacy federal pharmacy law hospitals [url=]cytotec[/url]
canadian pharmacy paraguay own your own pharmacy [url=]pharmacy math calculations[/url]
pharmacy job iowa medicare part d pharmacy network exclusion [url=]flomax[/url]

Anonymous said...

teenage dating tips for parents bengali dating
online dating in utah [url=]internet dating website search spy[/url] kinky cheating wives dating sites
ohio dating site [url=]cincinnati interracial dating[/url] boy dating games [url=]Pikoon[/url] dating colombiana

About Me

My photo
Phoenix, Arizona, United States