Lookups - multiple hits

An underlying assumption of the lookup task seems to be that there will be a unique record matching the lookup criteria. However what happens when more than one record is matched? Does it return the designated field of the first record encountered, or does it error out? Is there a way to specify what SHOULD happen in such a case? Is there a way to specify secondary criteria if the primary returns multiple hits?

Related to this - suppose that my lookup returns 2 data points, but one of them is null. It seems that the lookup should appear successful, and rows can then be redirected based on which field was null via the Truncation setting.
For example, say I'm matching on "name" and returning "Id" and "description". The name is found, and there's a valid Id, but the description is null.
Would this result in a successful lookup with the "description" truncated, or would the lookup fail?

Thanks!
PhilIn full cache mode the lookup will report a warning and use one of the records. In partial or no cache it will use one of the records with no warning. I say one of the records because this is an implementation detail and is subject to change.

You can always specify more than one join column so that you will not get multiple hits. Obviously, this is not exactly what you are asking for because it sounds like you only want the secondary match to occur if the primary is a dupe. This the lookup does not do.

NULL does not mean truncation. In the scenario you specify the lookup would be considered successful with a description that happens to be NULL. Trunctation is when you try to put a string that contains 100 characters into a column that is less than 100 characters.

Thanks,
Matt

looking value in table

Hello there
I have huge table with at least 20,000,000 records, On that I have Field1
who has unique values.
The same value sometimes shown on the same table on Field10 but not on the
same record and it isn't unique.
i need to build query that returs all the records with Field1Who exist
somewere on Field10.
Any query that i build took more then 1 hour to work.
How can i do it on query that will run fast?Roy, shalom
CREATE TABLE #Test (c1 INT NOT NULL PRIMARY KEY,c2 INT)
INSERT INTO #Test VALUES (1,10)
INSERT INTO #Test VALUES (2,1)
INSERT INTO #Test VALUES (3,4)
INSERT INTO #Test VALUES (4,5)
INSERT INTO #Test VALUES (5,4)
INSERT INTO #Test VALUES (6,7)
INSERT INTO #Test VALUES (7,10)
INSERT INTO #Test VALUES (8,1)
SELECT c1 FROM #Test
WHERE EXISTS (SELECT * FROM #Test T WHERE #Test.c1=T.c2)
Try create index on c1,c2 and see what is going on
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:e6sex1PbGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Hello there
> I have huge table with at least 20,000,000 records, On that I have Field1
> who has unique values.
> The same value sometimes shown on the same table on Field10 but not on the
> same record and it isn't unique.
> i need to build query that returs all the records with Field1Who exist
> somewere on Field10.
> Any query that i build took more then 1 hour to work.
> How can i do it on query that will run fast?
>|||From what you had given, field1 is the primary key and it should have been
cluster-indexed. If so, then just do this
and try to execute the query
Create a non-clustered index on Field 10 alone
Hope this helps.
--
"Roy Goldhammer" wrote:

> Hello there
> I have huge table with at least 20,000,000 records, On that I have Field1
> who has unique values.
> The same value sometimes shown on the same table on Field10 but not on the
> same record and it isn't unique.
> i need to build query that returs all the records with Field1Who exist
> somewere on Field10.
> Any query that i build took more then 1 hour to work.
> How can i do it on query that will run fast?
>
>

Looking for table / view that will tell me if I need to reinitialize subscription

I have kind of unique situation. I am running Merge replication. In one of my publications I am only publishing procedures/functions/views. By design, these do not change that often, but when a programmability object changes, it is scripted in a way so that:

1. The article is dropped from the publication

2. the object is then changed

3. The article is added back to the publication

My question is: Is there a table or view that the subscriber or publisher can see that could tell me if reinitialization needs to occur. I am looking at adding an automated script at the subscriber that makes the determiniation and automatically reinitializes the subscription. My alternative is to force the subscriber to reinitialize every time when synchronizing with this publication, even if nothing has changed because the process has to be automated.

Thanks,

Bill

Are you using SQL 2000 and 2005? In SQL 2005, you don't need to drop/recreate articles in order to change the schema. You can directly do ALTER TABLE/VIEW/FUNCTION. For more info, please take a look at BOL http://msdn2.microsoft.com/en-us/library/ms151870.aspx.

Peng

|||I am using 2005, but that will not work, as the publication only contains programmability objects, the underlying base tables are in another publication.|||Not sure what you mean by that will not work. What Peng means is that when you need to change one your progammability article like stored proc or view etc, you can just run alter view or alter proc ... and this DDL action will be replicated when using SQL 2005. so you dont need to drop the article, alter it and readd it. Hence you will not even need to reinitialize your subscriptions.|||When you execute a DDL statement against a view that: is in a publication without any tables in that publication, the query will run but will not complete execution.|||This is a known issue and the workaround is to add a dummy table in that publication.|||When you say create a dummy table, what exactly do you mean? Is this just the "real" table name with one column or something else?|||

Correct, create a non-necessary table that means absolutely nothing. i.e.

create table dbo.t1 (col1 int primary key, col2 int)

Add this as an article to your publication, then your DDL statements should work.