JOINS and Integers vs. Indexed "Strings".

I was told that, when possible, use integer fields for the equality comparison in INNER JOINS. Today someone suggested that using character fields that are indexed should be just as efficient.What do you think?

TIA,

barkingdog

It is the size of the index key which is most important. The larger your index key is, the more pages should be processed (and probably read from the hard disk: this is the slowest operation) while executing query. And, of course, there are some overhead comparing to strings in terms of collation. So, integer field is more effective in most cases.

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 date and time

Hi all,
I need some help with joining two fields of type datetime, one with date
relevancy and the other with time.
If i join the integer part of date field with the fraction part of time
field, the joined datetime is not the same.
What's the trick here?
TIA, JozzaOne way... taking date from @.a, time from @.b
declare @.a datetime, @.b datetime
set @.a = getdate()-1
set @.b = dateadd(hh,5,getdate())
select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
convert(varchar(10),@.a,101))
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Can you show us an example? What do you mean by "fraction part of time?"
Keith Kratochvil
"Jozza" <hmm@.hmm.com> wrote in message
news:lrcjg.3576$oj5.1220262@.news.siol.net...
> Hi all,
> I need some help with joining two fields of type datetime, one with date
> relevancy and the other with time.
> If i join the integer part of date field with the fraction part of time
> field, the joined datetime is not the same.
> What's the trick here?
> TIA, Jozza
>|||I thought that datetime is stored the way that integer part of a float
represents the date and the fraction part represents the time.
So adding them together would join them. But it doesn't seem to be the case
on SLQ server.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OvXKJ0hjGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Can you show us an example? What do you mean by "fraction part of time?"
> --
> Keith Kratochvil
>
> "Jozza" <hmm@.hmm.com> wrote in message
> news:lrcjg.3576$oj5.1220262@.news.siol.net...
>|||Converting fields to varchar, concatenate strings and convert it back to
datetime does the trick. (which was not exactly what your exemple was, but i
got the idea)
Is there any other way where i could add fields together in mathematical
terms, because i suspect there could be and error in string conversions when
different locale formats are used. Or am i wrong?
Thanks, Jozza
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:22B16AB3-DF86-4878-B181-727F79448589@.microsoft.com...
> One way... taking date from @.a, time from @.b
> declare @.a datetime, @.b datetime
> set @.a = getdate()-1
> set @.b = dateadd(hh,5,getdate())
> select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
> convert(varchar(10),@.a,101))
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Well concatenating the strings might lead to wrong date format if the string
format changes. Thats why I didn't go for the concatenation.
And the example I gave was in mathematical terms :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||After looking at the example a little bit longer i realize that you are
absolutely correct.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:33203119-C451-4C10-9733-F8D9EA1B0229@.microsoft.com...
> Well concatenating the strings might lead to wrong date format if the
> string
> format changes. Thats why I didn't go for the concatenation.
> And the example I gave was in mathematical terms :)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>

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 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