Joins versus relationships

If a database has relationships establshed between all of the tables
via primary and foreign key constraints, why isn't is possible to make
a SELECT statement across multiple tables without using a JOIN?

If the system knows the relationsip schema already why are JOINS
required?

Thanks,
HCHi

It is not always that easy! Not every database is fully normalised and there
can be mutiple relationships or missing ones. Doing the extra work to figure
out the relationship is going to take extra time and resource.

Also, how do you declare the different types of JOIN?

John

"H Cohen" <harris_cohen@.yahoo.com> wrote in message
news:1545331c.0408150629.1ffa0575@.posting.google.c om...
> If a database has relationships establshed between all of the tables
> via primary and foreign key constraints, why isn't is possible to make
> a SELECT statement across multiple tables without using a JOIN?
> If the system knows the relationsip schema already why are JOINS
> required?
> Thanks,
> HC|||"H Cohen" <harris_cohen@.yahoo.com> wrote in message
news:1545331c.0408150629.1ffa0575@.posting.google.c om...
> If a database has relationships establshed between all of the tables
> via primary and foreign key constraints, why isn't is possible to make
> a SELECT statement across multiple tables without using a JOIN?
> If the system knows the relationsip schema already why are JOINS
> required?
> Thanks,
> HC

Well, for a start what type of join would it be - inner, outer, cross? And
what would you join on - you might not want to join on col1 = col2, you
might want to join on col1 < col2, or col1-1 = col2 etc. Or you might want
to join on non-key columns. And you would have to assume that every database
is normalised, there is only one possible relationship between each pair of
tables, and all the relationships are enforced correctly, which is unlikely
to be true all the time.

If you specify what you want explicitly then it's clear to others reading
your code what you intended, and it also makes it easier to handle schema
changes and other code changes without the added confusion of the system
'automagically' doing things for you.

I suspect you're thinking mainly of the simplest possible case - join two
tables with an inner join using an equality comparison. While I suppose you
could introduce some kind of meta-syntax to avoid fully typing out the
primary key column names, that would be a false economy compared to the
potential issues, and of course it wouldn't work at all in some of the cases
I mention above.

Simon|||>> If a database has relationships establshed between all of the
tables via primary and foreign key constraints, why isn't is possible
to make a SELECT statement across multiple tables without using a
JOIN? <<

UNH?? A SELECT statement with two or more tables in the FROM clause
has at least a CROSS JOIN in it, even without a WHERE clause.

>> If the system knows the relationship schema already why are JOINS
required? <<

For the same reason you have to do math to get answers from numbers.
This makes no sense. Are you thinking about an old network database
like IMS or IDMS, or whatever that had pointer chains to navigate
along pre-defined acces paths?|||HC,

I believe Oracle and possibly other database systems implement something like NATURAL JOIN which infers a join condition of equality on
all like-named columns, but SQL Server always requires the join condition to be supplied.

Steve Kass
Drew University

H Cohen wrote:

> If a database has relationships establshed between all of the tables
> via primary and foreign key constraints, why isn't is possible to make
> a SELECT statement across multiple tables without using a JOIN?
> If the system knows the relationsip schema already why are JOINS
> required?
> Thanks,
> HC

Joins on same table

I'm having two general problems trying to do a JOIN. I have a table with
three fields {Code, Date, Amount}. Code+Date is a unique key. I'm trying
to get a rowset with 1) one row for each unique Code+Date pair, 2) and
with each row containing, {Code, Amount for Date-A and Amount for
Date-B}. Basically, I want to create two temp tables with the Amounts for
a specified Date and then Join them.

The problems are
1) I'm trying to do this in SQL-Server 7 with a single stantment, and
2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
returned with NULLs.

Anybody have any wisdom on this??
ThanksThe following gets me what I want, using Temp tables. I'm just trying to
figure out how to combine the Selects into a single statment.

Thanks

----------------

--temp with each Code
Drop Table #T0;
Select Code
Into #T0
From tblSearch
Order by Code;

--temp with amounts for 1st date
Drop Table #T1;
Select Code, Date, Amount
Into #T1
From tblSearch
Where Date = 20031102
Order by Code, Date;

-- amounts for 2nd date
Drop Table #T2;
Select Code, Date, Amount
Into #T2
From tblSearch
Where Date = 20031103
Order by Code, Date;

--put everything together
Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
'd2' from #T0
Left Outer Join #T1
On #T0.Code = #T1.Code
Left Outer Join #T2
On #T0.Code = #T2.Code
Order By #T0.Code|||[posted and mailed, please reply in news]

Chris (chris@.hicom.net) writes:
> I'm having two general problems trying to do a JOIN. I have a table
> with three fields {Code, Date, Amount}. Code+Date is a unique key.
> I'm trying to get a rowset with 1) one row for each unique Code+Date
> pair, 2) and with each row containing, {Code, Amount for Date-A and
> Amount for Date-B}. Basically, I want to create two temp tables with
> the Amounts for a specified Date and then Join them.
> The problems are
> 1) I'm trying to do this in SQL-Server 7 with a single stantment, and
> 2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
> returned with NULLs.

Just rewriting the temp-table thing you had with derived tables
gives:

SELECT DISTINCT #T0.Code, #T1.Date, #T1.Amount 'd1',
#T2.Date, #T2.Amount 'd2'
FROM tblSearch #T0
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031102') AS #T1
ON #T0.Code = #T1.Code
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031103') AS #T2
ON #T0.Code = #T2.Code
ORDER BY #T0.Code

But if I understand this correctly, it seems that you could get away with:

SELECT Code = coalesce(a.Code, b.Code), a.Date, d1 = a.Amount,
b.Date, d2 = b.Amount
FROM tblSearch a
FULL JOIN tblSearch b ON a.Code = b.Code
AND a.Date = b.Date
AND a.Date = '20031102'
AND b.Date = '20031103'

All this works on SQL7.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Chris,

You can replace temp tables with derived tables. Alternatively, as
the temp tables are selecting from the same table tblSearch, you can
also re-write the query using CASE. Note I'm using a mssqlserver
non-standard syntax. I just find it easier to read.

"Date=CASE when Date = 20031102 then Date else null end"
instead of
"CASE when Date = 20031102 then Date else null end as Date"

SELECT
Distinct
Code,
Date=CASE when Date = 20031102 then Date else null end,
Amount=CASE when Date = 20031102 then Amount else null end,
Date=CASE when Date = 20031103 then Date else null end,
Amount=CASE when Date = 20031103 then Amount else null end,
FROM tblSearch
ORDER BY code

> --temp with each Code
> Drop Table #T0;
> Select Code
> Into #T0
> From tblSearch
> Order by Code;
> --temp with amounts for 1st date
> Drop Table #T1;
> Select Code, Date, Amount
> Into #T1
> From tblSearch
> Where Date = 20031102
> Order by Code, Date;
> -- amounts for 2nd date
> Drop Table #T2;
> Select Code, Date, Amount
> Into #T2
> From tblSearch
> Where Date = 20031103
> Order by Code, Date;
> --put everything together
> Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
> 'd2' from #T0
> Left Outer Join #T1
> On #T0.Code = #T1.Code
> Left Outer Join #T2
> On #T0.Code = #T2.Code
> Order By #T0.Code|||The derived table approach gets me what I want -- one row per Code.

It seems that Coalesce doesn't help reduce the normal number of rows from
the Join.

Thanks very much for looking for a solution.|||The derived tables gets both Amounts into the same row, while the Case
still results in two (Distinct) rows.

I need to get a better SQL reference -- the book I'm using does not cover
derived tables.

Thanks very much.|||"Chris" <chris@.hicom.net> wrote in message news:<2bcNb.32425$G04.6661104@.news4.srv.hcvlny.cv.net>...
> The derived tables gets both Amounts into the same row, while the Case
> still results in two (Distinct) rows.
> I need to get a better SQL reference -- the book I'm using does not cover
> derived tables.
> Thanks very much.

Use GROUP if you want combine them into the same row. DISTINCT only
filters the rows.

SELECT
Code,
Date=max(CASE when Date = 20031102 then Date else null end),
Amount=max(CASE when Date = 20031102 then Amount else null end),
Date=max(CASE when Date = 20031103 then Date else null end),
Amount=max(CASE when Date = 20031103 then Amount else null end)
FROM tblSearch
GROUP BY code
ORDER BY code|||Chris (chris@.hicom.net) writes:
> It seems that Coalesce doesn't help reduce the normal number of rows
> from the Join.

That's right. The coalesce() function takes a list of values as parameters,
and return the first value in the list that is not NULL. Since the second
query included a full join, any of a.code and b.code could be NULL, so be
sure that we had a value here, I used coalesce(a.Code, b.Code).

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Excellent!

-- Thanks|||louis nguyen (louisducnguyen@.hotmail.com) writes:
> SELECT
> Code,
> Date=max(CASE when Date = 20031102 then Date else null end),
> Amount=max(CASE when Date = 20031102 then Amount else null end),
> Date=max(CASE when Date = 20031103 then Date else null end),
> Amount=max(CASE when Date = 20031103 then Amount else null end)
> FROM tblSearch
> GROUP BY code
> ORDER BY code

Note that date literals requires quotes. 20031103 is a number, and
attempt to convert it to datetime results in overflow.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

JOINing the same table

This is probably something simple I'm missing, but here it is anyway.
I have a table called "Employee". Primary key is "pk_EmployeeID".
There is a foreign key field called "fk_SupervisorID" which relates to
pk_EmployeeID. The object is to pull an employee's supervisor from the
same table.
I can't seem to get past a basic SELECT statement to run more complex
queries. Here's what I'm trying:
SELECT Employee.LastName AS EmpLastName, Sup.LastName AS SupLastName
FROM Employee
' JOIN Employee AS Sup ON Sup.fk_SupervisorID =
Employee.pk_EmployeeID
I've tried inner joins, outer joins, left, right... you name it. The
results I get are always putting the "Employee's" last name in the
Supervisor's (SupLastName) column.
A LEFT JOIN duplicates the supervisors giving me more records than is
actually in the table (which I thought would occur for a RIGHT JOIN)
and a RIGHT JOIN gives me the correct record count, still with botched
name fields. INNER JOIN also botches the name fields but does what it
is supposed to by not including the few records that don't have a
supervisor.
Any ideas?
Thanks in advance!!--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I believe you should have something like this:
CREATE TABLE Employees (
EmployeeID integer not null primary key ,
Name varchar(20) not null ,
- -- ... other columns ...
SupervisorID integer references Employees (EmployeeID)
)
SELECT E.LastName AS EmpLastName, S.LastName AS SupLastName
FROM Employees As E INNER JOIN Employees AS S
ON E.SupervisorID = S.EmployeeID
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQgfl54echKqOuFEgEQJwFwCggJAWohcqvWQK
QWUNBHdVeliRvDUAoIod
3gHlfBy2yj0p8/J4KRWPjffP
=TMY3
--END PGP SIGNATURE--
Wally wrote:
> This is probably something simple I'm missing, but here it is anyway.
> I have a table called "Employee". Primary key is "pk_EmployeeID".
> There is a foreign key field called "fk_SupervisorID" which relates to
> pk_EmployeeID. The object is to pull an employee's supervisor from the
> same table.
> I can't seem to get past a basic SELECT statement to run more complex
> queries. Here's what I'm trying:
> SELECT Employee.LastName AS EmpLastName, Sup.LastName AS SupLastName
> FROM Employee
> ' JOIN Employee AS Sup ON Sup.fk_SupervisorID =
> Employee.pk_EmployeeID
> I've tried inner joins, outer joins, left, right... you name it. The
> results I get are always putting the "Employee's" last name in the
> Supervisor's (SupLastName) column.
> A LEFT JOIN duplicates the supervisors giving me more records than is
> actually in the table (which I thought would occur for a RIGHT JOIN)
> and a RIGHT JOIN gives me the correct record count, still with botched
> name fields. INNER JOIN also botches the name fields but does what it
> is supposed to by not including the few records that don't have a
> supervisor.|||Are you saying you have a primary key that is been referenced as a
foreignkey in the same table? WHYyyyy' Anyways, to solve your problem
for now, here is the query.....
select E.LastName as EmpLastName,
(Select LastName as SupLastName from Employees Where employeeID =
E.SupervisorID)
from Employee E|||Query Builder wrote:
> Are you saying you have a primary key that is been referenced as a
> foreignkey in the same table? WHYyyyy' Anyways, to solve your
problem
> for now, here is the query.....
No... there is no actual reference between the fields. As far as SQL
Server is concerned, pk_SupervisorID is an indexed primary key.....
and fk_SupervisorID is just another unreferenced column with data in
it. The relation of the two fields only occurs in reports that the
front-end calls for.

> select E.LastName as EmpLastName,
> (Select LastName as SupLastName from Employees
> Where employeeID = E.SupervisorID)
> from Employee E
FYI: Your SELECT statement worked fine, but for some odd reason it
didn't label the SupLastName field.
Thanks!|||> SELECT E.LastName AS EmpLastName, S.LastName
> AS SupLastName
> FROM Employees As E INNER JOIN Employees AS S
> ON E.SupervisorID = S.EmployeeID
That worked too. Thanks!
And as I originally said... it was something small that I was missing.
:-|
Oh well... Thanks again!

Joining Tables..

Hai all,
I am having three table and i need to join them...
First table : client [key field : clientid]
Second Table : Address [key field : addressid]
Third Table : contact [key field : forid]
the problem is having the third table like this :
ForId ContactTypeId ContactNo
-- -- --
ABC Phone 123
ABC Email abc@.abc.com
ABC Fax 00123456
XYZ Phone 123
XYZ Email xyz@.xyz.com
XYZ Fax 00123456
on joining i need the result should like
Clientid Address Phone Email Fax
-- -- -- -- --
123 asdcvb 123 abc@.abc.com 00123123
576 sdfsds 123 xyz@.xyz.com 00123456
Can anyone provide me the query plz?
Looking forward for the reply...
Thanx in advance..hi
send us the complete DDL and referencing key, so that we can give u an
accurate solution
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||>> Third Table : contact [key field : forid]
It is a huge problem so rather than masking the flaw with a complicated
query, restructuring the schema would be a better solution. Based on your
narrative, here is how:
CREATE TABLE new_tbl (
customer_id CHAR(3) NOT NULL PRIMARY KEY,
phone_nbr CHAR(10) NOT NULL,
email CHAR(40) NOT NULL
CHECK ( CHARINDEX( '@.', email ) > 1 )
fax CHAR(10) NOT NULL );
Now do:
INSERT new_tbl ( customer_id, phone_nbr, email, fax )
SELECT ForId,
MAX( CASE ContactTypeId WHEN 'Phone' THEN ContactNo
END ) AS "Phone",
MAX( CASE ContactTypeId WHEN 'Email' THEN ContactNo
END ) AS "Email",
MAX( CASE ContactTypeId WHEN 'Fax' THEN ContactNo
END ) AS "Fax"
FROM tbl
GROUP BY ForId ;
Once this is done sucessfully, dump the ill-designed table:
DROP TABLE contact ;
Once you have this new schema, your query should be as simple as having a
join. If due to some reason the table cannot be changed/deleted, then
consider using the SELECT portion in the above INSERT statement for warping
a short term kludge.
Anith

Joining tables based on string key - bad idea?

The data already in the tables allows me to obtain what I need by drawing a relationship based on two columns that have nvarchar values but I have noticed that generally tables are related through integer keys. I want to know whether there are downsides to doing this, specifically if the join condition could 'wrongly' fail due to the string nature of the join criteria and thus cause missing rows in my resulting table.

Its typically not that great of an idea because it is going to take SQL SErver longer to join on these fields because they are larger than integer fields. The larger the field value, the longer it takes to compare (and in this case, join).
Tim|||

As Tim indicated, string values for JOINs is generally not a good idea. It has to do with how many bytes of data that has to be stored and read from the indexes. The shorter the values, the quicker index searching becomes.

However, if the string values are 'short' ( < 10 characters ), and the columns are indexed, it will most likely perform fine for you. The variables include the total number of rows in the table, amount of table activity, etc. I would NOT allow these string keys to be easily (if ever) changed.

|||

If you use character columns...

it's also a good idea to put adequate constraints on the columns or define foreign references to help ensure the quality of the data, so you don't end up with broken relationships

Joining different data types

I have a table, I'll call contacts, where the primary key is called id and
it's an integer field. I have another table, I'll call tempcontacts, and
it's primary key is id but it's a guid for many reasons I won't go into now.
These 2 tables are never combined in the same query.
Table 3 is a ClientDetails table, with the ClientID a varchar to accommodate
either a guid or an integer. This works okay until I try to do a query with
joins, I can't join either table's id with the ClientDetails
ClientID/varchar field. Can I use convert or cast in my query? Or how can
I weed out, for instance, only the numeric ClientID's, then join it.
Thanks for your help, I'm really not sure what to do about this.You can do both, but converting the joined columns will lead into
performace issues.
HTH, jens Suessmeyer.|||Uniqueidentifier has a higher data-type precedence that varchar, so when
comparing the varchar field to the uniqueidentifier field, SQL tries to
implicitly convert the varchar field to uniqueidentifier. You get the error
when it tries to convert one of the ClientID values that represents an
integer into uniqueidentifier.
Try
SELECT
....
FROM Contacts
INNER JOIN ClientDetails ON Contacts.id =
CASE WHEN ISNUMERIC(ClientDetails.ClientID) = 1 THEN ClientDetails.ClientID
ELSE -10 END
--The key thing here is that the CASE expression returns the value of
ClientDetails.ClientID to -10 whenever ClientID is not numeric (in other
words, whenver it is a uniqueidentifier). I chose -10 because I'm guessing
there are no negative values of ID in your contacts table. If there are,
adjust accordingly.
For the other table.
SELECT
...
FROM tempcontacts
INNER JOIN ClientDetails ON tempcontacts.id =
CASE WHEN ISNUMERIC(ClientDetails.ClientID) = 0 THEN ClientDetails.ClientID
ELSE NEWID() END
Again, the logic here is that for any integer values of
ClientDetails.ClientID, the value for the uniqueidentifier will be set to a
value returned by NEWID(), which (hopefully) will not match anything in
tempcontacts.id.
dew" wrote:

> I have a table, I'll call contacts, where the primary key is called id and
> it's an integer field. I have another table, I'll call tempcontacts, and
> it's primary key is id but it's a guid for many reasons I won't go into no
w.
> These 2 tables are never combined in the same query.
> Table 3 is a ClientDetails table, with the ClientID a varchar to accommoda
te
> either a guid or an integer. This works okay until I try to do a query wi
th
> joins, I can't join either table's id with the ClientDetails
> ClientID/varchar field. Can I use convert or cast in my query? Or how ca
n
> I weed out, for instance, only the numeric ClientID's, then join it.
> Thanks for your help, I'm really not sure what to do about this.
>
>

joining a table to a user-defined function?

Suppose I have a SQL Server table named 'gadget'. 'gadget' has an integer field named 'gadget_key', which is the primary key of the table.

Now suppose I have a user-defined function named 'udf_gadget_values'. This function takes as its input parameter an integer variable named '@.nGadgetKey'. This function returns a table which will always contain exactly one record. This one record has one field named 'nGadgetKey', which contains the same value that was passed to the function in parameter '@.nGadgetKey'

I would like to join the table 'gadget' with the table that function 'udf_gadget_values' returns kind of like this:

SELECT TOP 10 *
FROM gadget, udf_gadget_values(gadget.gadget_key)

The purpose of this query is to get the top 10 records from 'gadget', as well as the values associated with each record, as returned by the function.

The real issue is this: how do I pass gadget.gadget_key to the function as an input parameter? Or if this will not work, is there an alternative?

Hi,

This syntax is neither supported in Yukon nor Shiloh. I believe the problem is that the output rowset cannot be materialized until the function is evaluated, and yet, the function cannot be evaluated until the output rowset is materialized.

In Yukon we've introduced a new relational operator called CROSS APPLY that you could use in scenarios like this. The LHS of CROSS APPLY is a table source and the RHS is a table-valued function. The formal input parameters of the function can be bound to actual column values materialized in the LHS rowset. In other words, for each row of the LHS, evaluate the function on the RHS and JOIN the results to the LHS, resulting in >=1 row in the ultimate output rowset. In essense, it solves the problem described above by assigning a formal and well-defined evaluation strategy to the LHS and RHS of the CROSS APPLY.

It would look like this:

select * from gadget cross apply udf_gadget_values(gadget_key)

Moreover, in Yukon, we've changed the parser to allow function input parameters to bind to correlated subqueries in FROM clause and in the projection list. The examples below illustrate:

-- Yukon : works
-- Shiloh: !works
select * from gadget where exists
(select * from udf_gadget_values(gadget.gadget_key))

-- Yukon : works
-- Shiloh: !works
select *
, (select gadget_desc
from dbo.udf_gadget_values(gadget.gadget_key))
as function_value
from gadget

Regards,
Clifford Dibble
Program Manager, SQL Server

JOIN, GROUP BY, or HAVING question

Consider the following two tables:

ProjectHours
hoursID {primary key}
employeeID {foreign key}
ProjectDate
ProjectID
ProjectHours

DataComplete
DataCompleteID {primary key}
employeeID {foreign key}
CompleteDate

The first table should be self-explanatory. The second table is there to let me know that a particular employee has entered all of the data for a given date. This tells me that I can include this data in reports and charts.

Here's where I'm having troubles ...

I want to calculate the average hours worked by an employee on a project during a given time period. For example, what is the average number of hours John worked on Project X during the past week? The tricky part is that the employee/date combo must also be found in the
DataComplete table.

Problem: The following SQL statement averages ALL of the data even for dates NOT included in the DataComplete table:

SELECT
AVG(h.ProjectHours) AS avg_hours
FROM
ProjectHours h
JOIN
DataComplete d
ON
h.employeeID = d.employeeID
WHERE
d.employeeID = 123
AND d.CompleteDate >= '7/26/2003'
AND d.CompleteDate <= '8/1/2003'
AND h.ProjectID = 8

How do I get aggregate info only for dates found in the DataComplete table?I see you have a primary key on your ProjectHours table, but what is the NATURAL key?

Do employeeID, ProjectDate, and ProjectID constitute a unique records? If so, try this:

SELECT
AVG(h.ProjectHours) AS avg_hours
FROM
ProjectHours h
INNER JOIN DataComplete d
ON h.employeeID = d.employeeID
AND h.ProjectDate = d.CompleteDate
WHERE
d.employeeID = 123
AND d.CompleteDate >= '7/26/2003'
AND d.CompleteDate <= '8/1/2003'
AND h.ProjectID = 8

If this is not the case, I suspect you will need to modify (normalize) your table design to do what you want to do.

blindman

Join tables

hi
I got a confusing problem.I have 2 tables (Table_1 , Table_2) whit relation
On table_1.key and table_2.fkey.I need to get a table contains information from
2 table : title , key , fkey for mindate,mindate , describtion of mindate.
so I tried to write a function for returning (select top(1) * from table_2 order by date) so only
I need to connect this function to table_1. Here for running function I need to send Key to function
and I dont know how I can do that because wnehe I try to join them I get Error message.in seccond try I made a procedure
like this : SELECT Table_1.title, Table_1.[Key], Table_2.fkey, Table_2.date, Table_2.describtion
FROM Table_1 INNER JOIN
Table_2 ON Table_1.[Key] = Table_2.fkey where Table_2.[key]=(select top(1) Table_2.[key] from table_2 where fkey=Table_1.[key] order by date )

it works perfectly but gets more time to run whene we have 2000 records.Speed goes down ...

table_1:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[title] [nchar](10) COLLATE Arabic_CI_AS NULL,
[Key] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[Key] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

table_2:
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
[date] [datetime] NULL,
[fkey] [int] NULL,
[key] [int] IDENTITY(1,1) NOT NULL,
[describtion] [nchar](10) COLLATE Arabic_CI_AS NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[key] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Table_2] WITH CHECK ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([fkey])
REFERENCES [dbo].[Table_1] ([Key])
GO
ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]

data:
insert into table_1 (title) values('Title1')
insert into table_1 (title) values('Title2')
insert into table_1 (title) values('Title3')

insert into table_2 (date,fkey,describtion) values('2006/10/11',1,'dis 1')
insert into table_2 (date,fkey,describtion) values('2006/10/12',1,'dis 2')
insert into table_2 (date,fkey,describtion) values('2006/10/14',1,'dis 3')
insert into table_2 (date,fkey,describtion) values('2006/10/12',2,'dis 4')
insert into table_2 (date,fkey,describtion) values('2006/10/10',2,'dis 5')
insert into table_2 (date,fkey,describtion) values('2006/10/12',2,'dis 6')
insert into table_2 (date,fkey,describtion) values('2006/10/11',3,'dis 7')
insert into table_2 (date,fkey,describtion) values('2006/10/13',3,'dis 8')
insert into table_2 (date,fkey,describtion) values('2006/10/12',3,'dis 9')
insert into table_2 (date,fkey,describtion) values('2006/10/11',3,'dis 10')

resault :
Title1 1 1 2006-10-11 00:00:00.000 dis 1
Title2 2 2 2006-10-10 00:00:00.000 dis 5
Title3 3 3 2006-10-11 00:00:00.000 dis 7

hai,

First you can create a view

create view mindatelist as
select min(date) as mindate,fkey from Table_2
group by fkey

then run below query..

SELECT Table_1.title, Table_1.[Key], Table_2.fkey, Table_2.date, Table_2.describtion
FROM Table_1
INNER JOIN Table_2 ON Table_1.[Key] = Table_2.fkey
inner join mindatelist on mindatelist.fkey=Table_1.[Key] and Table_2.date=mindatelist.mindate

Next check the performance of this query...

Jefy

|||the resault is rong because we get 2 rows for key # 3
Title1 1 1 2006-10-11 00:00:00.000 dis 1
Title2 2 2 2006-10-10 00:00:00.000 dis 5
Title3 3 3 2006-10-11 00:00:00.000 dis 7
Title3 3 3 2006-10-11 00:00:00.000 dis 10|||

Koosha:

One thing to understand is that your code without the scalar function will run faster than with the scalar function; there is a certain amount of additional overhead that goes with the scalar function pluse the optimizer doesn't optimize scalar functions well. I tried a few things to try to speed up your query. First, I ran your query to get a performance baseline. I then modified the query into what is listed below and tested with the sample data you provided to verify that it qualitatively looked correct.

Next, I generated 2048 entries for table_1 and 32767 entries for table_2 and benchmarked under these circumstances. This is a really small sample set to benchmark with, but I still think the results will at least be indicative. I then compared the modified code to the original code; the time reduction associated with the new code was about 70%; the IO reduction of the new code was about 99%. This does NOT mean that the code is optimized, but it does mean that it is improved -- at least under my particular test circumstances.

Next, I did an experiment to see if a cover index might improve performance. The cover index reduced IO by a very thin 8% or so. Execution time was reduced by about 30%. I would suggest that if this function is not critical that I would probably NOT implement a cover index -- I just don't think it is going to be worth the overhead. Here is the query:

Dave

select t1.title,
t1.[key],
t2.fkey,
t2.date,
t2.describtion
from table_1 t1
inner join
( select [key],
fkey,
date,
describtion,
row_number () over
( partition by fkey
order by date, [key]
) as seq
from table_2
) t2
on t1.[key] = t2.fkey
and t2.seq = 1

|||it works perfectly.thanks for ur helping

Join Table Key to Multiple Table Names

Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:

I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.

That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.

Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.

ThxWouldn't it be a lot less trouble to have one table for stock, one table for jobs, and one table to show job-stock-usage? That way you could have a practical infinity of jobs and stocks with only three tables.

This is a fundamental database design process called normalization. It is the key to maintaining your sanity as your projects grow!

-PatP|||I totally agree...I don't like this design whatsoever but its all I have to work with because I didn't design it and it's the way things are being done right now.|||I believe that particular schema is taken directly from Chapter 6, page 142 of the ever popular and best-selling book, "WORLD'S WORST DATABASE DESIGNS".

You are going to have to use dynamic SQL to solve this. Essentially, you will construct your SQL statement as a string concatenating the value of jobID in as the table name, and then execute the string.

Developers like that ought to be shot.|||thx for the reply blindman. I was leaning that way but I'm fairly new to using ms sql. I'm currently reading up on dynamic sql and seeing how it works. I need to generate a view out of this somehow.

Thx again|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)|||-----
lol|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)I don't know that it has ever been officially published, it is more of a "work in progress" kind of thing. Every time they think they're ready to publish, somebody runs in yelling "You've got to see this one" and they go right back to editing!

-PatP|||73% of the database designs in that book, WORLD'S WORST DATABASE DESIGNS, were written by php programmers, who design tables like that all the time

18% of them were written by people who've spent too much time at dbdebumph.com and have drunk the koolaid -- not a null in sight!!

9% were written by oracle developers who thought that storing a whole nested table inside a field was a neat idea and supports their concept of object-oriented encapsulation|||Man, you NAILED it with #3!!! I'll buy you a round (12 pack or a bottle of your choice) whenever you're in town!!! Though I see so much of #1 that it's not even funny any more...I wish images from Unreal Tornament were real sometimes...|||thank you, thank you, i'll be here all week, try the veal and don't forget to tip your waitress

:cool:|||no sooner do i say it, and another example of a design in the first category pops up: this thread (http://forums.devshed.com/t199186/s.html)

happens all the time|||This is SO ironic...I just declined an offer from a real estate marketing company which excercised a similar design "strategy" ... They even asked me at the interview how I would resolve this situation... Of course my answer was to FIRE the designer of the current database first ;)

join stored procedure and view

Hi everybody
I have this stored procedure called flydate
CREATE PROCEDURE FlyDate AS
declare @.gencalendar table (cal_date datetime primary key)
declare @.p_date datetime
set @.p_date =getdate()

while @.p_date > DateAdd(mm, -3, GetDate()) BEGIN
insert into @.gencalendar(cal_date)
VALUES(@.p_date)

--getdate
SET @.p_date = DateAdd(d, -1, @.p_date)
END

select cal_date AS KF_DATE,0 AS KF_STATUS from @.gencalendar
GO

-which returns all the date for the past three month
and this is my view
CREATE VIEW dbo.rpt_Kids
AS
SELECT TOP 100 PERCENT
KF_ID,dbo.just_date_formal(KF_DATE) as KF_DATE,KF_STATUS FROM dbo.KIDS
order by Year(KF_date) DESC,Month(KF_date) DESC,Day(KF_date) DESC

just_date_formal function
CREATE FUNCTION [dbo].[just_date_formal](@.dtvalue datetime)
RETURNS nvarchar(40)
AS
BEGIN
DECLARE @.display nvarchar(40)
SET @.display = CAST(DATEPART(dd, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(mm, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(yyyy, @.dtvalue) AS nvarchar) + ', ' + CAST(DATENAME(dw, @.dtvalue) AS nvarchar)
RETURN @.display
END

this returns all the date on which error is generated.on other dates on which no error message is generated.
now i want to join both of them so that i get dates of error message and those dates also on which error message is not generated
like

23 june 2005 error
22 june 2005
21 june 2005 error

it should return all dates regardless errror is there or not

Hi,

You directly cannot JOIN the output of a stored procedure in a FROM clause. Stored procedures cannot be used in contexts that require relational expressions. I realize this seems odd at first, since you *can* return a rowset from a stored procedure. However, the reasons we disallow it in TSQL are

(a) The shape of the rowset cannot be determined ahead of time (there is no metadata anywhere that describes the table returned by a stored proc, and besides, the shape returned might depend on the run-time execution path though the proc ... if (condition) select * from somewhere else select * from somewherelese)

and (b) a stored procedure can return > 1 result set.

You have a few options available

1) You can capture the output of the stored procedure into a temporary table using the INSERT INTO EXEC syntax. The code would looks something like:

create table #tempcal(KF_DATE datetime, KF_STATUS int)
go

insert into #tempcal exec FlyDate
go

now you can JOIN on #tempcal.

2) You can refactor your proc to make it into a table-valued function, which you *can* use in a JOIN. You will have to factor-out the non-deterministic getdata() and pass them in as parameters. The code would look something like this

create function FlyDateFunc(@.p_date datetime, @.p_today datetime)
returns @.gencalendar table (KF_DATE datetime primary key, KF_STATUS int)
as
begin
while @.p_date > DateAdd(mm, -3, @.p_today)
begin
insert into @.gencalendar values (@.p_date, 0)
set @.p_date = DateAdd(d, -1, @.p_date)
end

return
end
go

select * from FlyDateFunc(getdate() , getdate())
go

Does this answer your question?
Thanks

|||

Hi,

I have an extra complication to this problem. I have SP's that create dynamic columns based on the parameters they get. So i don't know in advance what my #table should look like. Is there any way to do this without knowing the columns of the #table? (like the select * into #tmp from table1 but then using EXEC? )

I have developed SP's that create dyn columns. Now i want to use these same SP's in SSRS, but that want's to know the column names in advance. So i want to store the results of the SP in a #table and then unpivot that to send it to RS. Think that'll work?

[edit] the SP's use dynamic SQL to get the results

Regards Gert-Jan

join stored procedure and view

Hi everybody
I have this stored procedure called flydate
CREATE PROCEDURE FlyDate AS
declare @.gencalendar table (cal_date datetime primary key)
declare @.p_date datetime
set @.p_date =getdate()

while @.p_date > DateAdd(mm, -3, GetDate()) BEGIN
insert into @.gencalendar(cal_date)
VALUES(@.p_date)

--getdate
SET @.p_date = DateAdd(d, -1, @.p_date)
END

select cal_date AS KF_DATE,0 AS KF_STATUS from @.gencalendar
GO

-which returns all the date for the past three month
and this is my view
CREATE VIEW dbo.rpt_Kids
AS
SELECT TOP 100 PERCENT
KF_ID,dbo.just_date_formal(KF_DATE) as KF_DATE,KF_STATUS FROM dbo.KIDS
order by Year(KF_date) DESC,Month(KF_date) DESC,Day(KF_date) DESC

just_date_formal function
CREATE FUNCTION [dbo].[just_date_formal](@.dtvalue datetime)
RETURNS nvarchar(40)
AS
BEGIN
DECLARE @.display nvarchar(40)
SET @.display = CAST(DATEPART(dd, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(mm, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(yyyy, @.dtvalue) AS nvarchar) + ', ' + CAST(DATENAME(dw, @.dtvalue) AS nvarchar)
RETURN @.display
END

this returns all the date on which error is generated.on other dates on which no error message is generated.
now i want to join both of them so that i get dates of error message and those dates also on which error message is not generated
like

23 june 2005 error
22 june 2005
21 june 2005 error

it should return all dates regardless errror is there or not

Hi,

You directly cannot JOIN the output of a stored procedure in a FROM clause. Stored procedures cannot be used in contexts that require relational expressions. I realize this seems odd at first, since you *can* return a rowset from a stored procedure. However, the reasons we disallow it in TSQL are

(a) The shape of the rowset cannot be determined ahead of time (there is no metadata anywhere that describes the table returned by a stored proc, and besides, the shape returned might depend on the run-time execution path though the proc ... if (condition) select * from somewhere else select * from somewherelese)

and (b) a stored procedure can return > 1 result set.

You have a few options available

1) You can capture the output of the stored procedure into a temporary table using the INSERT INTO EXEC syntax. The code would looks something like:

create table #tempcal(KF_DATE datetime, KF_STATUS int)
go

insert into #tempcal exec FlyDate
go

now you can JOIN on #tempcal.

2) You can refactor your proc to make it into a table-valued function, which you *can* use in a JOIN. You will have to factor-out the non-deterministic getdata() and pass them in as parameters. The code would look something like this

create function FlyDateFunc(@.p_date datetime, @.p_today datetime)
returns @.gencalendar table (KF_DATE datetime primary key, KF_STATUS int)
as
begin

while @.p_date > DateAdd(mm, -3, @.p_today)
begin
insert into @.gencalendar values (@.p_date, 0)
set @.p_date = DateAdd(d, -1, @.p_date)
end

return
end
go

select * from FlyDateFunc(getdate() , getdate())
go

Does this answer your question?
Thanks

|||

Hi,

I have an extra complication to this problem. I have SP's that create dynamic columns based on the parameters they get. So i don't know in advance what my #table should look like. Is there any way to do this without knowing the columns of the #table? (like the select * into #tmp from table1 but then using EXEC? )

I have developed SP's that create dyn columns. Now i want to use these same SP's in SSRS, but that want's to know the column names in advance. So i want to store the results of the SP in a #table and then unpivot that to send it to RS. Think that'll work?

[edit] the SP's use dynamic SQL to get the results

Regards Gert-Jan

join problem

Hi,
I have problem with joining.
DDL:
CREATE TABLE Items (
item_code INTEGER NOT NULL,
item_description VARCHAR(50) NOT NULL,
PRIMARY KEY (item_code)
);
CREATE TABLE VAT_Groups (
vat_group CHAR(1) NOT NULL,
vat_description CHAR(20) NOT NULL,
PRIMARY KEY (vat_group)
);
CREATE TABLE VAT_Percents (
vat_group CHAR(1) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
vat_percent NUMERIC(5,2) NOT NULL,
PRIMARY KEY (vat_group, start_date),
FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
);
CREATE TABLE Items_VAT_History (
item_code INTEGER NOT NULL,
vat_group CHAR(1) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
PRIMARY KEY (item_code, vat_group, start_date),
FOREIGN KEY (item_code) REFERENCES Items (item_code),
FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
);
Sample data:
INSERT INTO Items VALUES (1, 'Vegetables');
INSERT INTO Items VALUES (2, 'Vine');
INSERT INTO Items VALUES (3, 'Milk');
INSERT INTO VAT_Groups VALUES ('E', 'Common VAT');
INSERT INTO VAT_Groups VALUES ('C', 'Lower VAT');
INSERT INTO VAT_Percents VALUES ('E', '2004-01-01', '2004-12-31', 20.00);
INSERT INTO VAT_Percents VALUES ('E', '2005-01-01', NULL, 18.00);
INSERT INTO VAT_Percents VALUES ('C', '2004-01-01', NULL, 8.00);
INSERT INTO Items_VAT_History VALUES (1, 'E', '2004-01-01', '2005-06-30');
INSERT INTO Items_VAT_History VALUES (1, 'C', '2005-07-01', NULL);
INSERT INTO Items_VAT_History VALUES (2, 'E', '2004-01-01', NULL);
INSERT INTO Items_VAT_History VALUES (3, 'C', '2004-01-01', NULL);
Desired Result:
item_code start_date end_date vat_percnt
---
1 2004-01-01 2004-12-31 20.00
1 2005-01-01 2005-06-30 18.00
1 2005-07-01 NULL 8.00
2 2004-01-01 2004-12-31 20.00
2 2005-01-01 NULL 18.00
3 2004-01-01 NULL 8.00
or equally good result:
item_code start_date end_date vat_percnt
---
1 2004-01-01 2004-12-31 20.00
1 2005-01-01 2005-06-30 18.00
1 2005-07-01 NULL 8.00
2 2004-01-01 2004-12-31 20.00
2 2005-01-01 NULL 18.00
3 2004-01-01 2004-12-31 8.00
3 2005-01-01 NULL 8.00
Thanks
Srdjan MijatovHi
Try:
SELECT i.item_code,
CONVERT(CHAR(10),CASE WHEN h.start_date >= p.start_date THEN h.start_date
ELSE P.start_date END, 121) AS start_date,
CONVERT(CHAR(10),CASE WHEN ISNULL(h.end_date,'29991231') <=
ISNULL(p.end_date,'29991231') THEN h.end_date ELSE p.end_date END,121) AS
end_date,
p.vat_percent
FROM Items i
JOIN Items_VAT_History h on i.item_code = h.item_code
JOIN VAT_Percents P on h.vat_group = p.vat_group
AND ( ( h.start_date >= p.start_date AND h.start_date <=
ISNULL(p.end_date,'29991231') )
OR ( h.end_date <= ISNULL(p.end_date,'29991231') and
ISNULL(h.end_date,'29991231') >= p.start_date )
OR ( h.start_date <= p.start_date AND ISNULL(h.end_date,'29991231') >=
ISNULL(p.end_date,'29991231') )
)
ORDER BY i.item_code, start_date
John
"Srdjan Mijatov" wrote:

> Hi,
> I have problem with joining.
>
> DDL:
> CREATE TABLE Items (
> item_code INTEGER NOT NULL,
> item_description VARCHAR(50) NOT NULL,
> PRIMARY KEY (item_code)
> );
> CREATE TABLE VAT_Groups (
> vat_group CHAR(1) NOT NULL,
> vat_description CHAR(20) NOT NULL,
> PRIMARY KEY (vat_group)
> );
> CREATE TABLE VAT_Percents (
> vat_group CHAR(1) NOT NULL,
> start_date DATE NOT NULL,
> end_date DATE,
> vat_percent NUMERIC(5,2) NOT NULL,
> PRIMARY KEY (vat_group, start_date),
> FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
> );
> CREATE TABLE Items_VAT_History (
> item_code INTEGER NOT NULL,
> vat_group CHAR(1) NOT NULL,
> start_date DATE NOT NULL,
> end_date DATE,
> PRIMARY KEY (item_code, vat_group, start_date),
> FOREIGN KEY (item_code) REFERENCES Items (item_code),
> FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
> );
>
> Sample data:
> INSERT INTO Items VALUES (1, 'Vegetables');
> INSERT INTO Items VALUES (2, 'Vine');
> INSERT INTO Items VALUES (3, 'Milk');
> INSERT INTO VAT_Groups VALUES ('E', 'Common VAT');
> INSERT INTO VAT_Groups VALUES ('C', 'Lower VAT');
> INSERT INTO VAT_Percents VALUES ('E', '2004-01-01', '2004-12-31', 20.00);
> INSERT INTO VAT_Percents VALUES ('E', '2005-01-01', NULL, 18.00);
> INSERT INTO VAT_Percents VALUES ('C', '2004-01-01', NULL, 8.00);
> INSERT INTO Items_VAT_History VALUES (1, 'E', '2004-01-01', '2005-06-30');
> INSERT INTO Items_VAT_History VALUES (1, 'C', '2005-07-01', NULL);
> INSERT INTO Items_VAT_History VALUES (2, 'E', '2004-01-01', NULL);
> INSERT INTO Items_VAT_History VALUES (3, 'C', '2004-01-01', NULL);
>
> Desired Result:
> item_code start_date end_date vat_percnt
> ---
> 1 2004-01-01 2004-12-31 20.00
> 1 2005-01-01 2005-06-30 18.00
> 1 2005-07-01 NULL 8.00
> 2 2004-01-01 2004-12-31 20.00
> 2 2005-01-01 NULL 18.00
> 3 2004-01-01 NULL 8.00
> or equally good result:
> item_code start_date end_date vat_percnt
> ---
> 1 2004-01-01 2004-12-31 20.00
> 1 2005-01-01 2005-06-30 18.00
> 1 2005-07-01 NULL 8.00
> 2 2004-01-01 2004-12-31 20.00
> 2 2005-01-01 NULL 18.00
> 3 2004-01-01 2004-12-31 8.00
> 3 2005-01-01 NULL 8.00
>
>
> Thanks
> Srdjan Mijatov
>|||Thank you, its working.
I tried to figure out that complex join condition

> AND ( ( h.start_date >= p.start_date AND h.start_date <=
> ISNULL(p.end_date,'29991231') )
> OR ( h.end_date <= ISNULL(p.end_date,'29991231') and
> ISNULL(h.end_date,'29991231') >= p.start_date )
> OR ( h.start_date <= p.start_date AND ISNULL(h.end_date,'29991231') >=
> ISNULL(p.end_date,'29991231') )
> )
Then I run query without it and it is working agian.
Srdjan

join on key SOMETIMES.....?

I need to join a table on a field with conditions.
----------------

--current join
LEFT OUTER JOIN GL on
(
(LINKS.accthigh >= GL.acctnu)
and
(LINKS.acctLow <= GL.acctnu)
)
and LINKS.dept = GL.dept and
and LINKS.fund = GL.fund

--needed something like this...but I am sure not correct syntax
LEFT OUTER JOIN GL on
(
(LINKS.accthigh >= GL.acct)
and
(LINKS.acctLow <= GL.acct)
)
if links.deptnu <> -1
LINKS.dept = GL.deptnu and
if links.fundnu <> -1
LINKS.fund = GL.fundNu

----------------
The field LINKS.fund or LINKS.dept can have a value of -1 which means to join beyond the dept or fund boundaries (ie any fund or any dept).

Is this possible or do I need to looking for a new approach?

Thanks for your help!
-Rtry this:LEFT OUTER
JOIN GL
on LINKS.accthigh >= GL.acct
and LINKS.acctLow <= GL.acct
and LINKS.dept =
( case when links.deptnu <> -1
then GL.deptnu
else LINKS.dept end )
and LINKS.fund =
( case when links.fundnu <> -1
then GL.fundNu
else LINKS.fund end )|||Thats exactly what I was looking for!

Thanks again for the help!

GBY,
-R

join on column with different data types

Hi.
I have a query where a single column is used as join key.
In TableA the column is nvarchar and in TableB it is varchar.
TableB has a clustered index on that column but the join will do a full
table scan
on TableB.
When I convert the coresponding column in TableA to varchar the query use
the index.
The join query will not do an implicit convert between varchar and
nvarchar.
Perhaps as designed.
Where can I find documentation on this issue.
I have been searching books online and Googles but haven't found it.
Sqlserver 2000 or 7.0
--
/dg
----
Dan van Ginhoven
SchlumbergerSema AB
S-412 97 GÖTEBORG Sweden
Phone +46 317 51 44 13
Mob/Cell +46 708 51 44 13convert(varchar(10, column_tableA) = column_tableB
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uwiH8IuPDHA.2480@.tk2msftngp13.phx.gbl...
> Hi.
> I have a query where a single column is used as join key.
> In TableA the column is nvarchar and in TableB it is varchar.
> TableB has a clustered index on that column but the join will do a full
> table scan
> on TableB.
> When I convert the coresponding column in TableA to varchar the query use
> the index.
> The join query will not do an implicit convert between varchar and
> nvarchar.
> Perhaps as designed.
> Where can I find documentation on this issue.
> I have been searching books online and Googles but haven't found it.
> Sqlserver 2000 or 7.0
> --
> /dg
> ----
> Dan van Ginhoven
> SchlumbergerSema AB
> S-412 97 GÖTEBORG Sweden
> Phone +46 317 51 44 13
> Mob/Cell +46 708 51 44 13
>|||There is a chart which shows which data types are implicitly or explicitly
convertable in books on line search for convert... The chart indicates that
SQL can implicitly convert between nchar/nvarchar and char/varchar
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uwiH8IuPDHA.2480@.tk2msftngp13.phx.gbl...
> Hi.
> I have a query where a single column is used as join key.
> In TableA the column is nvarchar and in TableB it is varchar.
> TableB has a clustered index on that column but the join will do a full
> table scan
> on TableB.
> When I convert the coresponding column in TableA to varchar the query use
> the index.
> The join query will not do an implicit convert between varchar and
> nvarchar.
> Perhaps as designed.
> Where can I find documentation on this issue.
> I have been searching books online and Googles but haven't found it.
> Sqlserver 2000 or 7.0
> --
> /dg
> ----
> Dan van Ginhoven
> SchlumbergerSema AB
> S-412 97 GÖTEBORG Sweden
> Phone +46 317 51 44 13
> Mob/Cell +46 708 51 44 13
>|||Hi Wayne!
Yes I have seen the chart. It surprises me a bit that the query didn't use
the index.
It may be bug.
I´m looking for a text that describes in what situations the Query Planner
will not use an
index, but will do a full table scan. I think I have seen it once.
One example is when a query contains <column> like '%value%'
it won´t use an index on that column to solve the query.
/dg|||> Yes I have seen the chart. It surprises me a bit that the query didn't use
> the index.
> It may be bug.
Nope, it is how SQL Server works. Although you don't have to write any code
for an implicit conversion SQL Server still converts one datatype to another
when it creates the execution plan. If you run the following code in Query
Analyzer and look at the execution plan you will see that #B.B is converted
before the two tables are joined:
CREATE TABLE #A (A nvarchar(20))
GO
CREATE TABLE #B (B varchar(20))
GO
SELECT * FROM #A
INNER JOIN #B
ON #A.A = #B.B
GO
DROP TABLE #A, #B
GO
Why is #B.B converted and not #A.A? That is determined by the Data Type
Precedence. varchar has a lower Data Type Precedence than nvarchar, so
varchar gets converted. (You can find the complete list of the data type
precedence in Books Online under Data Type Precedence).
Because #B.B is used in a function (when it is converted), the Query
Optimizer can't use any indexes on the column and has to use a table scan.
As you saw when the other (nvarchar) column in the join is explicitly
converted, the varchar column won't be implicitly converted and the index on
the varchar column can be used.
About a text: Kalen Delaney has written a series of articles for SQL Server
Magazine (www.sqlmag.com) about which search conditions can make use of
indexes and which don't, and there is also a bit about it in her book Inside
SQL Server 2000.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uPHxWevPDHA.1720@.TK2MSFTNGP11.phx.gbl...
> Hi Wayne!
> Yes I have seen the chart. It surprises me a bit that the query didn't use
> the index.
> It may be bug.
> I´m looking for a text that describes in what situations the Query
Planner
> will not use an
> index, but will do a full table scan. I think I have seen it once.
> One example is when a query contains <column> like '%value%'
> it won´t use an index on that column to solve the query.
> /dg
>