Hallo,
I have a table with 3 columns:
table tbl_user_class
===============
user_id (int) PK
class_id (varchar(20)) PK
class_value(varchar(100))
values are like so:
user_id class_id class_value
======================
1 firstname Rogier
1 lastname Doekes
2 firstname Mary
3 lastname Smith
....
I would like to create result set in the following format
user_id firstname lastname
====================
1 Rogier Doekes
2 Mary Smith
.....
How do I accomplish this? I tried using CASE WHEN statements but the best I could come up with was this:
1 Rogier null
1 null Doekes
2 Mary null
2 null Smith
when I did the following t-SQL statement:
select userID,
CASE WHEN classID = 'firstname' THEN classvalue END as 'firstname',
CASE WHEN classID = 'lastname' THEN classvalue END as 'lastname'
FROM tbl_user_class
Thanks for any help,
-Rogier DoekesUSE Northwind
GO
CREATE TABLE myTable99([user_id] int, class_id varchar(15), class_value varchar(15))
GO
INSERT INTO myTable99([user_id], class_id, class_value)
SELECT 1, 'firstname', 'Rogier' UNION ALL
SELECT 1, 'lastname', 'Doekes' UNION ALL
SELECT 2, 'firstname', 'Mary' UNION ALL
SELECT 2, 'lastname', 'Smith' UNION ALL
SELECT 3, 'firstname', 'Brett' UNION ALL
SELECT 4, 'lastname', 'Kaiser'
GO
SELECT a.[user_id], b.FirstName, c.LastName
FROM (SELECT Distinct [User_id] FROM myTable99) AS a
LEFT JOIN (SELECT [user_id], class_value as FirstName FROM myTable99 WHERE class_id = 'firstname') AS b
ON a.[user_id] = b.[user_id]
LEFT JOIN (SELECT [user_id], class_value as LastName FROM myTable99 WHERE class_id = 'lastname') AS c
ON a.[user_id] = c.[user_id]
GO
DROP TABLE myTable99
GO|||that does the job,
Thanks a lot Brett
-Rogier
Showing posts with label int. Show all posts
Showing posts with label int. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
creating more efficient queries
Using SS2000. I see queries created using things like
where ziprequestid = cast(' + @.@.requestid + ' as int)
or
left([prop_mkey2],5) = BranchData.zipCode
or
where branch + left(tblFollowup.prop_mkey2, 5)
in(select distinct cast(branch_new as varchar) + cast(zip as varchar) from
tblZipReassign_New where zipziprequestid = ' + @.@.requestid + '))'
Are using things like "left" and "cast" always going to slow a query down?
Is it always going to be more efficient to say create a column for the
zipcode and populate it and index it instead of using "left([prop_mkey2],5)"
to parse it out of another column?
Thanks,
--
Dan D.This is a complex question, because the answer depends on so many other
factors. It is safe (although not certain) to say that it will never be
slower to replace such complex expressions with simpler, pre-calculated and
indexed values. But whether it will be actually be faster... Well, that
depends on the number of Page reads the query processor needs to do to
perform the query, and the answer to that is extremely complex. For example
in some cases, the optimizer maya decide NOT to use an index, even when one
is available, because, in it's judgement, reading teh table completely (a
table scan) will require fewer Page IOs than traversing the index once for
each of the records in the resultset. This is often the case for querys
which will return a "sizeable" percentage of the entire table. The
percentage threshold where this phenomenom occurs is surprisingly low.
(although it is dependant on the depth (number of levels) in the index, it
can be a s low as 5% of the records in the table.
Another phenomenom is that which occurs when the only fields the query
processor needs to read in order to process the query happen to be in some
index. This si then called a "Covering" index, and it means the processor
only needs to read the index, and not the table itself. This changes what
the optimizer will do as well.
ANyway, your best bet is get a bool on SQL Query Tuning and optimization.
"Dan D." wrote:
> Using SS2000. I see queries created using things like
> where ziprequestid = cast(' + @.@.requestid + ' as int)
> or
> left([prop_mkey2],5) = BranchData.zipCode
> or
> where branch + left(tblFollowup.prop_mkey2, 5)
> in(select distinct cast(branch_new as varchar) + cast(zip as varchar) from
> tblZipReassign_New where zipziprequestid = ' + @.@.requestid + '))'
> Are using things like "left" and "cast" always going to slow a query down?
> Is it always going to be more efficient to say create a column for the
> zipcode and populate it and index it instead of using "left([prop_mkey2],5
)"
> to parse it out of another column?
> Thanks,
> --
> Dan D.|||Thanks CBretana. That helps.
"CBretana" wrote:
> This is a complex question, because the answer depends on so many other
> factors. It is safe (although not certain) to say that it will never be
> slower to replace such complex expressions with simpler, pre-calculated an
d
> indexed values. But whether it will be actually be faster... Well, that
> depends on the number of Page reads the query processor needs to do to
> perform the query, and the answer to that is extremely complex. For examp
le
> in some cases, the optimizer maya decide NOT to use an index, even when on
e
> is available, because, in it's judgement, reading teh table completely (a
> table scan) will require fewer Page IOs than traversing the index once for
> each of the records in the resultset. This is often the case for querys
> which will return a "sizeable" percentage of the entire table. The
> percentage threshold where this phenomenom occurs is surprisingly low.
> (although it is dependant on the depth (number of levels) in the index, it
> can be a s low as 5% of the records in the table.
> Another phenomenom is that which occurs when the only fields the query
> processor needs to read in order to process the query happen to be in som
e
> index. This si then called a "Covering" index, and it means the processor
> only needs to read the index, and not the table itself. This changes wha
t
> the optimizer will do as well.
> ANyway, your best bet is get a bool on SQL Query Tuning and optimization.
> "Dan D." wrote:
>|||yr welcome! IN re-reading what I posted, I realized that I implied but did
not explicitly say, one important thing...
During the first phase of query optimization, (called ANalysis) the query
optimizer searches your query for anything that can be used to reduce the
number of page IOs it must perform. Any clause, or expression, in a where
clause or join condition, that can be used, is called a Search Argument
(SARG). A SARG limits the Page IOs that need to be made when it specifies
anb exact match, a range of values, or a conjunction of two SARGS with an
AND. An individual SARG MUST be a Column variable acting on a constant
expression (or variable that can be resolved to a constant) ANY expression
that has a column value being operated on by a function cannot be a SARG.
i.e,
HireDate > DateAdd(day, -30, getdate()) IS a SARG, but
DateAdd(day, 30 HireDate) > getdate() IS NOT a SARG
in the former, the expression
DateAdd(day, -30, getdate()) will be resolved to a constant, and compared
with the value of the HireDate column. If HireDate is in an index, the index
can be used. In the latter case a Table scan ahs tp be done, because the
query processor has to read every HireDate in the table to perform the
function DateAdd(day, 30 HireDate) on each value.
Hope this helps...
"Dan D." wrote:
> Thanks CBretana. That helps.
> "CBretana" wrote:
>|||That does help. As a general rule I try to get people to store something lik
e
DateAdd(day, -30, getdate()) in a variable and then use the variable in the
query. I didn't know that DateAdd(day, -30, getdate()) could still use the
index if it is in the right place in the query.
Thanks.
"CBretana" wrote:
> yr welcome! IN re-reading what I posted, I realized that I implied but di
d
> not explicitly say, one important thing...
> During the first phase of query optimization, (called ANalysis) the query
> optimizer searches your query for anything that can be used to reduce the
> number of page IOs it must perform. Any clause, or expression, in a where
> clause or join condition, that can be used, is called a Search Argument
> (SARG). A SARG limits the Page IOs that need to be made when it specifies
> anb exact match, a range of values, or a conjunction of two SARGS with an
> AND. An individual SARG MUST be a Column variable acting on a constant
> expression (or variable that can be resolved to a constant) ANY expressio
n
> that has a column value being operated on by a function cannot be a SARG.
> i.e,
> HireDate > DateAdd(day, -30, getdate()) IS a SARG, but
> DateAdd(day, 30 HireDate) > getdate() IS NOT a SARG
> in the former, the expression
> DateAdd(day, -30, getdate()) will be resolved to a constant, and compar
ed
> with the value of the HireDate column. If HireDate is in an index, the ind
ex
> can be used. In the latter case a Table scan ahs tp be done, because the
> query processor has to read every HireDate in the table to perform the
> function DateAdd(day, 30 HireDate) on each value.
> Hope this helps...
> "Dan D." wrote:
>
where ziprequestid = cast(' + @.@.requestid + ' as int)
or
left([prop_mkey2],5) = BranchData.zipCode
or
where branch + left(tblFollowup.prop_mkey2, 5)
in(select distinct cast(branch_new as varchar) + cast(zip as varchar) from
tblZipReassign_New where zipziprequestid = ' + @.@.requestid + '))'
Are using things like "left" and "cast" always going to slow a query down?
Is it always going to be more efficient to say create a column for the
zipcode and populate it and index it instead of using "left([prop_mkey2],5)"
to parse it out of another column?
Thanks,
--
Dan D.This is a complex question, because the answer depends on so many other
factors. It is safe (although not certain) to say that it will never be
slower to replace such complex expressions with simpler, pre-calculated and
indexed values. But whether it will be actually be faster... Well, that
depends on the number of Page reads the query processor needs to do to
perform the query, and the answer to that is extremely complex. For example
in some cases, the optimizer maya decide NOT to use an index, even when one
is available, because, in it's judgement, reading teh table completely (a
table scan) will require fewer Page IOs than traversing the index once for
each of the records in the resultset. This is often the case for querys
which will return a "sizeable" percentage of the entire table. The
percentage threshold where this phenomenom occurs is surprisingly low.
(although it is dependant on the depth (number of levels) in the index, it
can be a s low as 5% of the records in the table.
Another phenomenom is that which occurs when the only fields the query
processor needs to read in order to process the query happen to be in some
index. This si then called a "Covering" index, and it means the processor
only needs to read the index, and not the table itself. This changes what
the optimizer will do as well.
ANyway, your best bet is get a bool on SQL Query Tuning and optimization.
"Dan D." wrote:
> Using SS2000. I see queries created using things like
> where ziprequestid = cast(' + @.@.requestid + ' as int)
> or
> left([prop_mkey2],5) = BranchData.zipCode
> or
> where branch + left(tblFollowup.prop_mkey2, 5)
> in(select distinct cast(branch_new as varchar) + cast(zip as varchar) from
> tblZipReassign_New where zipziprequestid = ' + @.@.requestid + '))'
> Are using things like "left" and "cast" always going to slow a query down?
> Is it always going to be more efficient to say create a column for the
> zipcode and populate it and index it instead of using "left([prop_mkey2],5
)"
> to parse it out of another column?
> Thanks,
> --
> Dan D.|||Thanks CBretana. That helps.
"CBretana" wrote:
> This is a complex question, because the answer depends on so many other
> factors. It is safe (although not certain) to say that it will never be
> slower to replace such complex expressions with simpler, pre-calculated an
d
> indexed values. But whether it will be actually be faster... Well, that
> depends on the number of Page reads the query processor needs to do to
> perform the query, and the answer to that is extremely complex. For examp
le
> in some cases, the optimizer maya decide NOT to use an index, even when on
e
> is available, because, in it's judgement, reading teh table completely (a
> table scan) will require fewer Page IOs than traversing the index once for
> each of the records in the resultset. This is often the case for querys
> which will return a "sizeable" percentage of the entire table. The
> percentage threshold where this phenomenom occurs is surprisingly low.
> (although it is dependant on the depth (number of levels) in the index, it
> can be a s low as 5% of the records in the table.
> Another phenomenom is that which occurs when the only fields the query
> processor needs to read in order to process the query happen to be in som
e
> index. This si then called a "Covering" index, and it means the processor
> only needs to read the index, and not the table itself. This changes wha
t
> the optimizer will do as well.
> ANyway, your best bet is get a bool on SQL Query Tuning and optimization.
> "Dan D." wrote:
>|||yr welcome! IN re-reading what I posted, I realized that I implied but did
not explicitly say, one important thing...
During the first phase of query optimization, (called ANalysis) the query
optimizer searches your query for anything that can be used to reduce the
number of page IOs it must perform. Any clause, or expression, in a where
clause or join condition, that can be used, is called a Search Argument
(SARG). A SARG limits the Page IOs that need to be made when it specifies
anb exact match, a range of values, or a conjunction of two SARGS with an
AND. An individual SARG MUST be a Column variable acting on a constant
expression (or variable that can be resolved to a constant) ANY expression
that has a column value being operated on by a function cannot be a SARG.
i.e,
HireDate > DateAdd(day, -30, getdate()) IS a SARG, but
DateAdd(day, 30 HireDate) > getdate() IS NOT a SARG
in the former, the expression
DateAdd(day, -30, getdate()) will be resolved to a constant, and compared
with the value of the HireDate column. If HireDate is in an index, the index
can be used. In the latter case a Table scan ahs tp be done, because the
query processor has to read every HireDate in the table to perform the
function DateAdd(day, 30 HireDate) on each value.
Hope this helps...
"Dan D." wrote:
> Thanks CBretana. That helps.
> "CBretana" wrote:
>|||That does help. As a general rule I try to get people to store something lik
e
DateAdd(day, -30, getdate()) in a variable and then use the variable in the
query. I didn't know that DateAdd(day, -30, getdate()) could still use the
index if it is in the right place in the query.
Thanks.
"CBretana" wrote:
> yr welcome! IN re-reading what I posted, I realized that I implied but di
d
> not explicitly say, one important thing...
> During the first phase of query optimization, (called ANalysis) the query
> optimizer searches your query for anything that can be used to reduce the
> number of page IOs it must perform. Any clause, or expression, in a where
> clause or join condition, that can be used, is called a Search Argument
> (SARG). A SARG limits the Page IOs that need to be made when it specifies
> anb exact match, a range of values, or a conjunction of two SARGS with an
> AND. An individual SARG MUST be a Column variable acting on a constant
> expression (or variable that can be resolved to a constant) ANY expressio
n
> that has a column value being operated on by a function cannot be a SARG.
> i.e,
> HireDate > DateAdd(day, -30, getdate()) IS a SARG, but
> DateAdd(day, 30 HireDate) > getdate() IS NOT a SARG
> in the former, the expression
> DateAdd(day, -30, getdate()) will be resolved to a constant, and compar
ed
> with the value of the HireDate column. If HireDate is in an index, the ind
ex
> can be used. In the latter case a Table scan ahs tp be done, because the
> query processor has to read every HireDate in the table to perform the
> function DateAdd(day, 30 HireDate) on each value.
> Hope this helps...
> "Dan D." wrote:
>
Sunday, February 19, 2012
creating index on calculated field in table -sql 2000
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2
]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegroups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2
]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2
]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegroups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2
]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug
creating index on calculated field in table -sql 2000
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
...
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ? How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.
You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegr oups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
...
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ? How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.
|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!
|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
...
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ? How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.
You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegr oups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
...
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ? How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.
|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!
|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug
creating index on calculated field in table -sql 2000
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 = something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegroups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 = something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegroups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug
Creating Foreign Key constraint
I have two tables; each has multi-column primary keys. I need to create a foreign key relationship between the two tables.
Sales:
DKey, int, PK
OKey,int, PK
RKey,int, PK
...
Rep:
Bkey, int, PK, identity
Rkey, int, PK
...
When I try to like Rkey from both tables as a foreign key relationship, I get an error that the columns in one table "do not match an existing primary key or UNIQUE constraint. (I've tried it both ways and get the same error.)
How can I link these two tables?
Thanks.
Make an unique index on Rkey from Rep or Sales depends on the table you can delegate "Primary Key" (use Database Diagram for usability)
|||
Since you did not include rationale for the key selection, this is going to be just a guess.
The purpose of a Primary Key is to uniquely identify a row of data. It looks like each Rep is uniquely identified with the IDENTITY field BKey. I'm not sure why you have RKey also involved in the Primary Key for Rep.
You could recreate the Primary key for Rep using only the Bkey, and then add the Bkey field to Sales to relate Sales to Rep.
OR, if RKey is unique, use it as a Primary Key.
However, if for some silly reason you HAVE to have a combination of Bkey and Rkey as the Primary key in Rep, you will have to add both columns to Sales in order to create the relationship.
As a side note, the use of Bkey, Rkey, etc. for column names is very 'odd', and not only adds confusion, but will be difficult to maintain the pattern for all tables. A 'best practice' naming convention for an IDENTITY field (such as Bkey) is to use the table name and the suffix [ID]. So a preferred name for Bkey would be RepID. The name instantly communicates where the field comes from and what it is, expecially useful when it is added to another table.
|||I was trying to simplify the question....I probably should have included more details. I'm sorry, I never know how much detail to go into.
What I'm actually trying to do is create a many to many link between:
Sales:
DateKey, int, PK
OrganizationKey, int, PK
CustomerKey, int, PK
RepBridgeKey, int, PK
....
RepBridge:
RepBridgeKey, int, PK, identity
RepKey, int, PK
Rep:
RepKey, int, PK, identity
.....
I can link RepBridge and Rep via RepKey but not RepBridge with Sales via RepBridgeKey.
From your answer, it looks like I would have to include RepKey in my Sales table and do the FK link on both fields?
Thanks.
|||
No, I think you can only make an unique index on RepBridgeKey from RepBridge (is possible because is identity)
You can make the following links vis-a-vis your vision of table schema:
Rep <RepKey-->RepBridge
RepBridge <RepBridgeKey>Sales
|||John,
In RepBridge, I'm still not sure why you need RepKey as part of the Primary Key. It seems like BridgeRepKey is a unique identifier and would adequately serve as the PK (and the FK in Sales).
RepBridgeKey 'should' be all you need.
However, if, as said earlier, there is a business reason to complicate the Primary key in BridgeRep by adding RepKey -then you will have to add RepKey to Sales -at which point, the RepBridge table seems unnecessary. You would have to duplicate the rows in Sales for each Rep if there are multiple Reps involved with a single Sale.
Your sales table probably needs a better defined primary key. Typically a unique sales identifier is created -something like an INVOICE number, SalesOrder number, etc. In your Sales table above, only one sale would be allowed per day per Rep. That may be reasonable, but is it realistic? I would prefer a [SalesID int IDENTITY Primary Key] field
Typically, a 'bridging' table will have an IDENTITY field as PK, and the PK's from BOTH others tables as FK.
|||RepBridge
RepBridgeKey, int IDENTITY PK
RepKey FK to Rep
SalesKey Fk to Sales
I see what you're saying....the bridge table does not need the RepKey as part of its primary key. I was thinking that each new sales rep would have to trigger a new bridge record but this can be done with only one PK identity field.
There is a reason behind the key fields in the sales table, but perhaps I shall also revisit that side issue.
Thanks again.
Subscribe to:
Posts (Atom)