qualifying table variables

hi all!

I am using table variables instead of creating a temp table because it seems to be faster

But now I need qualify the table variable so I can join it with another table having a field with same name of a field from the table variable. U know if I can do that?

ex: with temp table

create table #tmp... (F1...)
#tmp.f1

with table variable

declare @.temp table(...
@.table.f1 - can´t do it

the first question is if I can join the table variable with another table and how to do that qualifying the variable table, that is, putting the name of the var temp with the field, because the other table has a field with same name

thank to all and happiness for all 2004Hi all

I think I found the solution

It is not possible to qualify the talble variable because it is not part of a persistent table, so I cant assing table variables,

I saw it in

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_66w5.asp

it is the same as in sql 2000 BOL

thanks. so I have to use a temp table to qualify it if I want to join the table. My thinking is that I can join table variables

hope it is useful to u|||Hi all!

Thanks for having the time to read it

I was wrong and found the solution.

I can join and qualify a table var, only aliasing the table as in

insert into @.table... join temp.field...
from @.vartemp temp

--

here temp is the alias of @.vartemp, so I can use this alias instead of @.vartemp when, for example, there are two fields with same name in two tables, and one is @.vartemp

Now, once solved it, my problem is with UPDATETEXT. I am updating a text field and need to qualify the @.vartable in order to update it using a pointer to the text field. Since there are not any FROM clause as above, I dont know where to qualify the @.vartable (tried qualifying in DECLARE, but cant do it there)

UPDATETEXT @.vartemp.pointer - cant use @.vartemp, need an alias, but where to give the alias?

Thanks all!

qry wont use index

I've got a query which just won't use an index, instead, it does a table scan on a specific column. Basically the query is:

select count(*)
from table1 t1
inner join table2 t2 on (t1.myref = t2.myref)
where myint2 in (1,2,3)
and (myval between -1 and -2 or myint1 = 1)

In actual work, the 'myval between' uses variables which could be null, same goes for myint1. The values above are the values that I use to examine. According to the analyzer, a table scan is performed on myint2 (the in stuff), however, there's an index on myint2 also in combination with myint1.
I've tried to re-create the setup by creating the table1 and table2, including the indexes. Unfortunately, in the re-created setup, the index is used.

EDIT: Oddly, the OR ruins the plan to use an index: when leaving out the 'and (myval...)', the index is used.

What's going on?The first OR operation in a query (in this case, the IN clause) makes an index scan difficult. The second OR operation makes the index scan impossible (at least using present technology).

-PatP|||how's that? when changing
where myint2 in (1,2,3)
into
where myint2 = 1
I still get a table scan.

Besides, I do get an index-scan in the re-created setup.
I tried a defrag, reindex AND recompute statistics. It just wont show up.
I really don't get it.|||Hogtie the optimizer and try it. Use SET FORCEPLAN ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_8mni.asp) and hint the index. See if you get a result set while you are young enough to still care.

Let me know what you find out, I'm curious now!

-PatP|||hah! now your stuck! :>
the optimizer came up empty and even with the index hint (0) it still does a full table scan. The trouble is that the column resides in the facts-table (its a warehouse db) and in production it gets a 80% hit according to the exection plan (76% in dev). I'm beginning to wonder if the amount of indexes specified on the facts is too much (47 columns, 26 indexes).|||oh btw: it takes 14 minutes to come back with a rowcount of 85.
The forceplan option and index(0) hint does take the percentage down to 28% but it's still a table scan (forcedindex). btw: in the analyzer, execution plan, I've got these little round yellow circles on the tablescan, index, nested loops etc. I don't see 'em in BOL. Happen to know what their about?|||Unfortunately, nothing changed over the weekend.
I even changed the complete query to a simple count(*) with a single where-clause in which a single value is specified.
Still a full table scan, even with index-hint and forceplan set.

q; varchar(MAX)

varchar(MAX)
Is there any advantage/disadvantage using varchar(MAX) instead of
varchar(1000)Jim,
No technical disadvantage, but it makes your intent less clear. Datatypes
should ideally indicate the intent of the data. (Rather like someone just
making everything VARCHAR, rather than INT, DATETIME, etc.)
Of course, if you actually go beyond the 8K page boundary in the amount of
data, then there is a natural impact on performance for the extra I/O
involved with the large datatype.
RLF
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:910C5A5C-7376-4EB1-BF67-7ECE7C26E10E@.microsoft.com...
> varchar(MAX)
> Is there any advantage/disadvantage using varchar(MAX) instead of
> varchar(1000)
>|||On Fri, 28 Sep 2007 04:36:00 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>varchar(MAX)
>Is there any advantage/disadvantage using varchar(MAX) instead of
>varchar(1000)
VARCHAR(MAX) is a wonderful new feature when used correctly.
If your data should not be longer than 1000 characters, and your
programs and reports are written to only handle 1000 characters, then
the major disadvantage to varchar(max) is that you could end up with
data in the table that the code can not handle. There is also some
overhead to handling varchar(max) that is avoided with varchar(1000),
though not much if you keep the data short enough that the (max) was
not needed.
And using varchar(max) when it is not needed is just plain sloppy. To
me it says someone couldn't be bothered to set the length correctly.
The next person who comes along may not realize that there is an
implicit limit. Or they will know there must be one, but not be able
to find what it is.
Roy Harvey
Beacon Falls, CT|||In addition to the other responses, there is an advantage to keep the
defined size below 800 bytes, because values of more than 800 bytes
cannot be indexed or grouped.
--
Gert-Jan
JIM.H. wrote:
> varchar(MAX)
> Is there any advantage/disadvantage using varchar(MAX) instead of
> varchar(1000)