Pros / Cons to this approach

I have a requirement where I need to perform a query for position
information. But for some types of entries, I need to "expand" the row
to include additional position rows. Let me explain with an example:

An index is a security that is made up of components where each
component has a "weight" or a number of shares. So if I have 1 share of
the index, I have X shares of each component.

AAPL is an Equity, CSCO is an Equity, SPY is an Index. Lets say that
SPY has one component, AAPL, with shares being 10. (1 share of SPY = 10
shares of AAPL).

So, I do some trading and I end up with positions as follows:

+10 AAPL
-5 CSCO
+2 SPY

The query I need returns:

+10 AAPL
-5 CSCO
+2 SPY
+20 AAPL (from 2 SPY * 10 shares)

which becomes (after grouping):

+30 AAPL
-5 CSCO
+2 SPY

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

Based on that criteria and the following schema (and sample data):

-- Drop tables
DROP TABLE [SecurityMaster]
DROP TABLE [Position]
DROP TABLE [IndexComponent]

-- Create tables
CREATE TABLE [SecurityMaster] (
[Symbol] VARCHAR(10)
, [SecurityType] VARCHAR(10)
)

CREATE TABLE [Position] (
[Account] VARCHAR(10)
, [Symbol] VARCHAR(10)
, [Position] INT
)

CREATE TABLE [IndexComponent] (
[IndexSymbol] VARCHAR(10)
, [ComponentSymbol] VARCHAR(10)
, [Shares] INT
)

--Populate tables
INSERT INTO [SecurityMaster] VALUES ('AAPL', 'Equity')
INSERT INTO [SecurityMaster] VALUES ('MSFT', 'Equity')
INSERT INTO [SecurityMaster] VALUES ('MNTAM', 'Option')
INSERT INTO [SecurityMaster] VALUES ('CSCO', 'Equity')
INSERT INTO [SecurityMaster] VALUES ('SPY', 'Index')

INSERT INTO [Position] VALUES ('001', 'AAPL', 10)
INSERT INTO [Position] VALUES ('001', 'MSFT', -5)
INSERT INTO [Position] VALUES ('001', 'CSCO', 10)
INSERT INTO [Position] VALUES ('001', 'SPY', 15)
INSERT INTO [Position] VALUES ('001', 'QQQQ', 21)
INSERT INTO [Position] VALUES ('002', 'MNTAM', 10)
INSERT INTO [Position] VALUES ('002', 'APPL', 20)
INSERT INTO [Position] VALUES ('003', 'SPY', -2)

INSERT INTO [IndexComponent] VALUES ('SPY', 'AAPL', 25)
INSERT INTO [IndexComponent] VALUES ('SPY', 'CSCO', 50)
INSERT INTO [IndexComponent] VALUES ('QQQQ', 'AAPL', 33)

-- *****************************

-- Based on the rules:
-- 1) Index positions appear like other positions (account /
symbol) pair, but
-- its components show up as new rows of account (of index),
symbol (equal
--to component symbol), position (equal to shares * index position)
-- 2) One row for each account / symbol pair (GROUP BY account and
symbol, SUM position)

-- Expected output (without grouping) (sorted by account / symbol)
-- 001 AAPL 10
-- 001 AAPL 375 (component shares * index position) (25
* 15) (SPY)
-- 001 AAPL 693 (component shares * index position) (33
* 21) (QQQQ)
-- 001 CSCO 10
-- 001 CSCO 750 (component shares * index position) (50
* 15) (SPY)
-- 001 MSFT -5
-- 001 QQQQ 21
-- 001 SPY 15

-- 002 AAPL 20
-- 002 MNTAM 10

-- 003 AAPL -50 (component shares * index position) (25
* -2) (SPY)
-- 003 CSCO -100 (component shares * index position) (50
* -2) (SPY)
-- 003 SPY -2

-- Expected output (with grouping account / symbol) (sorted by account
/ symbol)
-- 001 AAPL 1078
-- 001 CSCO 760
-- 001 MSFT -5
-- 001 QQQQ 21
-- 001 SPY 15

-- 002 AAPL 20
-- 002 MNTAM 10

-- 003 AAPL -50
-- 003 CSCO -100
-- 003 SPY -2

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

Is a UNION the best way to perform the query. What are the pros and
cons? What, if any, is a better way?

SELECT
[Account], [Symbol], SUM([Position]) AS [Position]
FROM
(
SELECT[Account], [Symbol] , [Position]
FROM[Position]

UNION ALL

SELECTP.[Account] , IC.[ComponentSymbol] AS [Symbol] , (P.[Position] *
IC.[Shares]) AS [Position]
FROM[IndexComponent] IC
JOIN[Position] P
ONP.[Symbol] = IC.[IndexSymbol]
) D
GROUP BY[Account], [Symbol]
ORDER BY[Account], [Symbol](JayCallas@.hotmail.com) writes:
> I have a requirement where I need to perform a query for position
> information. But for some types of entries, I need to "expand" the row
> to include additional position rows. Let me explain with an example:
> An index is a security that is made up of components where each
> component has a "weight" or a number of shares. So if I have 1 share of
> the index, I have X shares of each component.

Now, this sounds funny to me, because in our system you can also define
indexes. However, indexes are virtual - you can never have a position
in an index directly. (But you can have a position in a derivative that
has the index as its contract base.)

> Is a UNION the best way to perform the query. What are the pros and
> cons? What, if any, is a better way?

There might be other alternatives, but I think the UNION query is fine.
Since I was given this query in my lap, I ran out of fantasy of trying
something else.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Not all index can have positions. For instance, the Dow Jones
Industrial Average is an an index but you cannot buy shares of it, only
shares of the components. But there is a class of indices called ETF
(Exchange Traded Fund) that you can buy and sell shares of. Here is a
link for the definition of an ETF,
http://www.investorwords.com/1755/ETF.html. (There are probably better
ones out there but this gives the basics.)|||(JayCallas@.hotmail.com) writes:
> Not all index can have positions. For instance, the Dow Jones
> Industrial Average is an an index but you cannot buy shares of it, only
> shares of the components. But there is a class of indices called ETF
> (Exchange Traded Fund) that you can buy and sell shares of. Here is a
> link for the definition of an ETF,
> http://www.investorwords.com/1755/ETF.html. (There are probably better
> ones out there but this gives the basics.)

I think we have those in Sweden as well. I would guess that our
customers handle them as stocks. At least I have not heard of any
requirement to add any support for them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I expect the DOW JONES Industrial Average Index to rocket
up past 12500 by early 2006. Today it closed at 10530.

I won't be surprised if there is a Santa Claus Rally this year (2005).

Good luck,
Steve|||Stocks rally up early this coming week (Nov 7, 2005)

DOW +100 >
NAS +35 >
watch it happen
Good luck,
Steve

Proper use of Inner Join with nested select?

ok, i am a novice w/ sql queries, so this will probably be cake for
most of you if i can explain it properly.
I am trying to run a query against 2 tables, tbPlayers and tbResults
that are joined one to many by a PlayerId field. This query is used to
retrieve standings of poker tournament results, 1 record for each
player, an sum of money won from all tournaments, and a count of how
many times they have won any amount of money from a tournament.
SELECT tbPlayers.Name,
Sum(tbResults.MoneyWon) as [Prize Money]
(Select Count(MoneyWon) FROM tbResults WHERE MoneyWon > 0) as Cashes
FROM tbPlayers
INNER JOIN tbResults on tbResults.PlayerId = tbPlayers.Id
GROUP BY Players.Name
This query runs, but the value retrieved for 'Cashes' is incorrect, as
it brings back the count of ALL records in the table instead of just
those associated with a singe PlayerId.
Any thoughts would be greatly appreciated! (and i'll happily offer up a
free version of my Poker Tournament Director application once its ready
for beta ... which is soon!)Try something like this:
declare @.Player table (PlayerId int, name varchar(20))
insert @.Player values (1, 'jeff')
insert @.Player values (2, 'ed')
declare @.Results table (PlayerId int, MoneyWon int )
insert @.Results values(1, 100)
insert @.Results values(1, 0)
insert @.Results values(1, 1000)
insert @.Results values(2, -100)
insert @.Results values(2, 50)
SELECT p.Name,
Sum(r.MoneyWon) as [Prize Money],
Sum(case when MoneyWon > 0 then 1 else 0 end) as Cashes
FROM @.Player p
INNER JOIN @.Results r on p.PlayerId = r.PlayerId
GROUP BY p.Name|||Perfect!! Thank you - you the man!|||BriskDuck@.gmail.com wrote:
> ok, i am a novice w/ sql queries, so this will probably be cake for
> most of you if i can explain it properly.
> I am trying to run a query against 2 tables, tbPlayers and tbResults
> that are joined one to many by a PlayerId field. This query is used to
> retrieve standings of poker tournament results, 1 record for each
> player, an sum of money won from all tournaments, and a count of how
> many times they have won any amount of money from a tournament.
> SELECT tbPlayers.Name,
> Sum(tbResults.MoneyWon) as [Prize Money]
> (Select Count(MoneyWon) FROM tbResults WHERE MoneyWon > 0) as Cashes
> FROM tbPlayers
> INNER JOIN tbResults on tbResults.PlayerId = tbPlayers.Id
> GROUP BY Players.Name
> --
> This query runs, but the value retrieved for 'Cashes' is incorrect, as
> it brings back the count of ALL records in the table instead of just
> those associated with a singe PlayerId.
> Any thoughts would be greatly appreciated! (and i'll happily offer up a
> free version of my Poker Tournament Director application once its ready
> for beta ... which is soon!)
>
Try this :
SELECT tbPlayers.Name,
Sum(tbResults.MoneyWon) as [Prize Money],cashes.[WinCount]
FROM tbPlayers
INNER JOIN tbResults on tbResults.PlayerId = tbPlayers.Id
INNER JOIN (SELECT [PlayerId],COUNT([MoneyWon]) AS [WinCount] FROM
tbResults GROUP BY [PlayerId]) AS Cashes ON tbResults.[PlayerId] =
cashes.[PlayerId]
GROUP BY tbPlayers.Id,tbPlayers.[Name],cashes.WinCount
-JayDial|||JeffB wrote:
> Try something like this:
>
> declare @.Player table (PlayerId int, name varchar(20))
> insert @.Player values (1, 'jeff')
> insert @.Player values (2, 'ed')
> declare @.Results table (PlayerId int, MoneyWon int )
> insert @.Results values(1, 100)
> insert @.Results values(1, 0)
> insert @.Results values(1, 1000)
> insert @.Results values(2, -100)
> insert @.Results values(2, 50)
> SELECT p.Name,
> Sum(r.MoneyWon) as [Prize Money],
> Sum(case when MoneyWon > 0 then 1 else 0 end) as Cashes
> FROM @.Player p
> INNER JOIN @.Results r on p.PlayerId = r.PlayerId
> GROUP BY p.Name
>
Whoa nevermind, forget mine. This looks much better! ;)sql

Proper use of Event Notifications

Hi.

I'm developing an app that uses Service Broker queues to allow a customer to create "events" that fire using a timer or a query notification. When these events fire, a message is sent to a Service Broker queue for processing. Because there is much managed code involved in processing these messages, I decided to use the External Activator application and an Event Notification to process these messages. My question is "what is the difference between using the External Activator application to launch another application (which simply RECEIVEs a message from the target queue and processes it) and creating a windows service that simply monitors the target queue (with a WAITFOR = -1 clause) and processes it?"

I guess I'm not sure how using the QUEUE_ACTIVATION Event Notification is really helping me.

Thanks,

Chris

If all you need is a single instance of your service and don't mind it running all the time, you could implement this as a Windows Service that does a WAITFOR with no timeout. But if you want multiple instances of your service to be dynamically activated depending on the rate of incoming messages and how quickly your service is able to consume them, the external activator becomes useful. The main purpose of the external activator is to make services scalable.

The external activator is also capable of monitoring multiple queues, each configured with its own service program. So if you had 10 services, you do not need to have 10 windows services running even when queues are idle. You will have a single external activator running which will dynamically launch the service programs as messages arrive.

Hope that helps,

Rushi

|||

Rushi,

After doing some more digging into the External Activator, I understand more clearly now. It seems that the scalability benefits are the real key for us. That and doing a WAITFOR with an indefinite timeout isn't so easy in a Windows Service.

Thanks,

Chris

|||The external activator does some of the hard things, like maintaining a recovery log so that if the process was to terminate and it came back up, it would recover state and not miss any notifications thus ensuring that queued messages do not get orphaned.

Proper Nouns

Hi All,
We are using a FORMSOF(INFLECTIONAL) query and trying to establish why we
get results returned for a search on "Luke" but no results for "Lukes"?
Whereas "Rebuke" and "Rebukes" returns the same number of results.
Is there a dictionary or similar that we can add to to ensure that plural
forms of proper nouns are treated as any other plural?
Thanks all, and regards,
Fin_D
Fin_D,
Could you post the full output of -- SELECT @.@.version -- as this is very
helpful in troubleshooting SQL FTS issues. Specifically, the OS Platform
(Win2K, WinXP, or Win2003) provides the wordbreaker dll that affect the
results from such as SQL FTS query. On Windows Server 2003 (Win2003), for
the proper noun / name "Luke" will return:
IWordFormSink::PutAltWord: cwc 4, 'Luke'
IWordFormSink::PutWord: cwc 6, 'Luke's'
but not Lukes as the proper name Luke is not the same as Lukes, i.e.. Luke
!= Lukes.
As for a dictionary of proper names, there are several that are on the
Internet (for example, "Quick Lookup - Dictionary and Reference Tool" -
http://www.trancreative.com/quicklookup.aspx?page=dicts), that you can
download and import into a SQL table to further refine your search's input.
However, a quick search via Google
(http://www.google.com/search?&q=dict...+luke+%2Blukes) turns up few
results for "Lukes" vs. "Luke's".
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Fin D" <findarato@.hotmail.com> wrote in message
news:86038C53-58F6-443A-9CF4-B3873EBEADF1@.microsoft.com...
> Hi All,
> We are using a FORMSOF(INFLECTIONAL) query and trying to establish why we
> get results returned for a search on "Luke" but no results for "Lukes"?
> Whereas "Rebuke" and "Rebukes" returns the same number of results.
> Is there a dictionary or similar that we can add to to ensure that plural
> forms of proper nouns are treated as any other plural?
> Thanks all, and regards,
> --
> Fin_D
|||John,
Thanks, we have enough information from your post. Much appreciated
Fin_D.
"John Kane" wrote:

> Fin_D,
> Could you post the full output of -- SELECT @.@.version -- as this is very
> helpful in troubleshooting SQL FTS issues. Specifically, the OS Platform
> (Win2K, WinXP, or Win2003) provides the wordbreaker dll that affect the
> results from such as SQL FTS query. On Windows Server 2003 (Win2003), for
> the proper noun / name "Luke" will return:
> IWordFormSink::PutAltWord: cwc 4, 'Luke'
> IWordFormSink::PutWord: cwc 6, 'Luke's'
> but not Lukes as the proper name Luke is not the same as Lukes, i.e.. Luke
> != Lukes.
> As for a dictionary of proper names, there are several that are on the
> Internet (for example, "Quick Lookup - Dictionary and Reference Tool" -
> http://www.trancreative.com/quicklookup.aspx?page=dicts), that you can
> download and import into a SQL table to further refine your search's input.
> However, a quick search via Google
> (http://www.google.com/search?&q=dict...+luke+%2Blukes) turns up few
> results for "Lukes" vs. "Luke's".
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Fin D" <findarato@.hotmail.com> wrote in message
> news:86038C53-58F6-443A-9CF4-B3873EBEADF1@.microsoft.com...
>
>

Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
================================================== =====
SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid =
commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
================================================== =======
TIA
Kay
hi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ================================================== =====
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ================================================== =======
>
> TIA
> Kay
>
>
|||Its tough to tell you without more information about these tables, their keys
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itself.
HTH
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ================================================== =====
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ================================================== =======
>
> TIA
> Kay
>
>
sql

Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
========================================
===============
SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid =
commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
========================================
=================
TIA
Kayhi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ========================================
===============
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ========================================
=================
>
> TIA
> Kay
>
>|||Its tough to tell you without more information about these tables, their key
s
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itsel
f.
HTH
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ========================================
===============
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ========================================
=================
>
> TIA
> Kay
>
>

Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
======================================================= SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid = commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
=========================================================
TIA
Kayhi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:
> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> =======================================================> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid => commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> =========================================================>
> TIA
> Kay
>
>|||Its tough to tell you without more information about these tables, their keys
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itself.
HTH
"Kay" wrote:
> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> =======================================================> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid => commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> =========================================================>
> TIA
> Kay
>
>

proper case for name in sql server

hi,
a field with all capital letters for last name and frist name -- DAVID
JONES
I need a funcation taht I could retrive it from sql query analyzer
select propercase(field1) from names. it returns "David Jones"
THnaksHi
A google search would turn up many hits such as
http://vyaskn.tripod.com/code/propercase.txt but it doesn't cater for all
names e.g MacNeil, McNeil, O'Neill etc..
John
"mecn" wrote:
> hi,
> a field with all capital letters for last name and frist name -- DAVID
> JONES
> I need a funcation taht I could retrive it from sql query analyzer
> select propercase(field1) from names. it returns "David Jones"
> THnaks
>
>

Proper Application of a Subquery

In order to expand my skills in SQL for a database I am working, I developed
a conceptual query with which I am having some difficulty. The problem can
be represented as three tables: tblApartments, tblResidents, tblPhone.
tblResidents links to tblApartments through a field called AptNum. Current,
past, and future residents are stored in tblResidents and catagorized by a
field called Status. Phone numbers (e.g. home, emergency, work, etc) are
linked to the appropriate resident via a field called ResID. tblApartment
contains details about the apartment like building number and floor.
Now let's say I want to get a list of names and work phone numbers for
people that lived in building #3. The query I came up with to get the names
looks like:
SELECT tblApartments.Building, tblApartments.AptNum,
tblResidents.Name, tblResidents.AptNum, tblResidents.Status
FROM tblApartments INNER JOIN tblResidents ON
tblApartments.AptNum = tblResidents.AptNum
WHERE tblApartments.Building = 3 AND
tblResidents.Status = "Moved"
What I can't get a handle on is how to add work phone numbers. I am
assuming a subquery is the correct construct and would be of the form
SELECT tblResidents.ResID, tblPhone.ResID, tblPhone.Number, tblPhone.Type
FROM tblResidents INNER JOIN tblPhone ON
tblResidents.ResID = tblPhone.ResID
WHERE tblPhone.Type = "Work"
However, I don't have a clue how to integrate this into the overall query!
Am I barking up the wrong tree with this structure? If not, any help or
references to online FAQs or tutorials would be greatly appreciated. (I did
look at some on-line references, but it did not help!)
Any help will be greatly appreciated!!
Thanks!
DonDon wrote:
> In order to expand my skills in SQL for a database I am working, I develop
ed
> a conceptual query with which I am having some difficulty. The problem ca
n
> be represented as three tables: tblApartments, tblResidents, tblPhone.
> tblResidents links to tblApartments through a field called AptNum. Curren
t,
> past, and future residents are stored in tblResidents and catagorized by a
> field called Status. Phone numbers (e.g. home, emergency, work, etc) are
> linked to the appropriate resident via a field called ResID. tblApartment
> contains details about the apartment like building number and floor.
> Now let's say I want to get a list of names and work phone numbers for
> people that lived in building #3. The query I came up with to get the nam
es
> looks like:
> SELECT tblApartments.Building, tblApartments.AptNum,
> tblResidents.Name, tblResidents.AptNum, tblResidents.Stat
us
> FROM tblApartments INNER JOIN tblResidents ON
> tblApartments.AptNum = tblResidents.AptNum
> WHERE tblApartments.Building = 3 AND
> tblResidents.Status = "Moved"
> What I can't get a handle on is how to add work phone numbers. I am
> assuming a subquery is the correct construct and would be of the form
> SELECT tblResidents.ResID, tblPhone.ResID, tblPhone.Number, tblPhone.Type
> FROM tblResidents INNER JOIN tblPhone ON
> tblResidents.ResID = tblPhone.ResID
> WHERE tblPhone.Type = "Work"
> However, I don't have a clue how to integrate this into the overall query!
> Am I barking up the wrong tree with this structure? If not, any help or
> references to online FAQs or tutorials would be greatly appreciated. (I d
id
> look at some on-line references, but it did not help!)
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Just include a join of the tblPhone to it's related table:
SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum)
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID
WHERE A.Building = 3
AND R.Status = "Moved"
AND P.Type = "Work"
I used a LEFT JOIN on the tblPhone in case a resident did not have a
Work phone number.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQh+Xr4echKqOuFEgEQJT3QCfRSFLOB36+5+e
0gJlQbDM46GtRGcAn05A
CBHjNQ63P/0GQ76Yy82VTWT/
=B/s0
--END PGP SIGNATURE--|||"Don" <someone@.somewhere.net> wrote in message
news:OdOgs73GFHA.3440@.TK2MSFTNGP10.phx.gbl...
> In order to expand my skills in SQL for a database I am working, I
developed
> a conceptual query with which I am having some difficulty. The problem
can
> be represented as three tables: tblApartments, tblResidents, tblPhone.
> tblResidents links to tblApartments through a field called AptNum.
Current,
> past, and future residents are stored in tblResidents and catagorized by a
> field called Status. Phone numbers (e.g. home, emergency, work, etc) are
> linked to the appropriate resident via a field called ResID. tblApartment
> contains details about the apartment like building number and floor.
> Now let's say I want to get a list of names and work phone numbers for
> people that lived in building #3. The query I came up with to get the
names
> looks like:
> SELECT tblApartments.Building, tblApartments.AptNum,
> tblResidents.Name, tblResidents.AptNum,
tblResidents.Status
> FROM tblApartments INNER JOIN tblResidents ON
> tblApartments.AptNum = tblResidents.AptNum
> WHERE tblApartments.Building = 3 AND
> tblResidents.Status = "Moved"
> What I can't get a handle on is how to add work phone numbers. I am
> assuming a subquery is the correct construct and would be of the form
> SELECT tblResidents.ResID, tblPhone.ResID, tblPhone.Number, tblPhone.Type
> FROM tblResidents INNER JOIN tblPhone ON
> tblResidents.ResID = tblPhone.ResID
> WHERE tblPhone.Type = "Work"
> However, I don't have a clue how to integrate this into the overall query!
> Am I barking up the wrong tree with this structure? If not, any help or
> references to online FAQs or tutorials would be greatly appreciated. (I
did
> look at some on-line references, but it did not help!)
> Any help will be greatly appreciated!!
> Thanks!
> Don
>
I don't think a subquery is required for this, another INNER JOIN should do
it:
SELECT tblApartments.Building, tblApartments.AptNum, tblResidents.Name,
tblResidents.AptNum, tblResidents.Status, tblPhone.Number, tblPhone.Type
FROM tblApartments
INNER JOIN tblResidents ON tblApartments.AptNum = tblResidents.AptNum
INNER JOIN tblPhone ON tblResidents.ResID = tblPhone.ResID
WHERE tblApartments.Building = 3 AND tblResidents.Status = "Moved" AND
tblPhone.Type = "Work"
It's a matter of taste, but I use joins in preference to subqueries where
possible because I think they are easier to read. Also, I've read that the
Query Optimiser often handles joins better than subqueries.
Regards,
Simon|||On Fri, 25 Feb 2005 21:24:59 GMT, MGFoster wrote:
(snip)
>Just include a join of the tblPhone to it's related table:
>SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
>FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum)
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID
>WHERE A.Building = 3
> AND R.Status = "Moved"
> AND P.Type = "Work"
>I used a LEFT JOIN on the tblPhone in case a resident did not have a
>Work phone number.
Hi MGFoster,
In order for that to work, the P.Type = 'Work' shoould be in the ON
clause, not in the WHERE clause. (And you shouldn't use double quotes to
delimit string constants!)
SELECT A.Building, A.AptNum,
R.Name, R.AptNum, R.Status,
P.Number, P.Type
FROM Apartments AS A
INNER JOIN Residents AS R
ON R.AptNum = A.AptNum
LEFT JOIN Phones AS P
ON P.ResID = R.ResID
AND P.Type = 'Work'
WHERE A.Building = 3
AND R.Status = 'Moved'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"MGFoster" <me@.privacy.com> wrote in message
news:LIMTd.5610$MY6.682@.newsread1.news.pas.earthlink.net...
> SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum)
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID
> WHERE A.Building = 3
> AND R.Status = "Moved"
> AND P.Type = "Work"
> I used a LEFT JOIN on the tblPhone in case a resident did not have a
> Work phone number.
I believe that if you want to see all residents even if they have no work
phone that you need to add the P.type condition to the Join clause as:
SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum)
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID and P.Type = "Work"
WHERE A.Building = 3
AND R.Status = "Moved"
Good Luck,
Jim|||James Goodwin wrote:
> "MGFoster" <me@.privacy.com> wrote in message
> news:LIMTd.5610$MY6.682@.newsread1.news.pas.earthlink.net...
>
>
> I believe that if you want to see all residents even if they have no work
> phone that you need to add the P.type condition to the Join clause as:
> SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum)
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID and P.Type = "Work"
> WHERE A.Building = 3
> AND R.Status = "Moved"
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Hugo & Jim,
Double quotes: Yeah, I know. I move between Access (double quotes OK)
& SQL a lot & probably just got discombobulated.
P.Type = 'Work' in ON clause: I've seen the equivalency evaluation of
"hard coded" data in the JOIN's ON clause before, but I've always put,
what could be a parameter, in the WHERE clause. Is there any increased
efficiency in putting it in the ON clause rather than the WHERE clause?
If there is an efficiency increase, wouldn't that indicate that all
WHERE clause evaluations could be put into the join's ON clause?
I'm not advocating this just curious. Also, I'm too lazy to create some
test tables & data to look at the query's execution plan. ;-)
Thanks,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQh/ s1IechKqOuFEgEQJK3QCg+2KBLrUaTtXxbQ98L95
US9slC6IAn3rb
5PXNZ5Plgzrt65L864qdd7rX
=xIwu
--END PGP SIGNATURE--|||"MGFoster" <me@.privacy.com> wrote in message
news:j1STd.5822$MY6.1200@.newsread1.news.pas.earthlink.net...
> James Goodwin wrote:
work
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Hugo & Jim,
> Double quotes: Yeah, I know. I move between Access (double quotes OK)
> & SQL a lot & probably just got discombobulated.
> P.Type = 'Work' in ON clause: I've seen the equivalency evaluation of
> "hard coded" data in the JOIN's ON clause before, but I've always put,
> what could be a parameter, in the WHERE clause. Is there any increased
> efficiency in putting it in the ON clause rather than the WHERE clause?
> If there is an efficiency increase, wouldn't that indicate that all
> WHERE clause evaluations could be put into the join's ON clause?
> I'm not advocating this just curious. Also, I'm too lazy to create some
> test tables & data to look at the query's execution plan. ;-)
> Thanks,
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/AwUBQh/ s1IechKqOuFEgEQJK3QCg+2KBLrUaTtXxbQ98L95
US9slC6IAn3rb
> 5PXNZ5Plgzrt65L864qdd7rX
> =xIwu
> --END PGP SIGNATURE--
Hello -
In this case, LEFT OUTER JOIN tblPhone ON (R.ResID = P.ResID and P.Type =
'Work') is necessary to ensure that people who don't have work phones are
returned. If you put the P.Type = 'Work' condition in the overall WHERE
clause, then people who don't have work phones will not be returned, because
their P.Type value is null.
Alternatively, you could use the simpler JOIN condition make the condition
in the WHERE clause (P.Type = 'Work' OR P.Type IS NULL).
Regards,
Simon|||> "MGFoster" <me@.privacy.com> wrote in message
< snip >
< snip >
Simon Shearn wrote:
> Hello -
> In this case, LEFT OUTER JOIN tblPhone ON (R.ResID = P.ResID and P.Type =
> 'Work') is necessary to ensure that people who don't have work phones are
> returned. If you put the P.Type = 'Work' condition in the overall WHERE
> clause, then people who don't have work phones will not be returned, becau
se
> their P.Type value is null.
> Alternatively, you could use the simpler JOIN condition make the condition
> in the WHERE clause (P.Type = 'Work' OR P.Type IS NULL).
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Simon, Thanks for the info. It made me want to see how this works. So
I went ahead & created some test tables/data:
use tempdb
go
set nocount on
create table tblApartments (
building tinyint,
aptnum tinyint primary key
)
insert into tblApartments values (1,1)
insert into tblApartments values (1,2)
insert into tblApartments values (1,3)
insert into tblApartments values (2,4)
insert into tblApartments values (2,5)
insert into tblApartments values (3,6)
insert into tblApartments values (3,7)
insert into tblApartments values (3,8)
create table tblResidents(
ResID tinyint primary key,
[name] varchar(20),
aptnum tinyint ,
status varchar(10),
constraint fk_res foreign key (aptnum) references tblApartments
)
insert into tblResidents values (1,'dalton',1,'moved')
insert into tblResidents values (2,'liloo',2,'current')
insert into tblResidents values (3,'lucy',3,'moved')
insert into tblResidents values (4,'harry',4,'moved')
insert into tblResidents values (5,'sondine',5,'current')
insert into tblResidents values (6,'jean-baptiste',6,'moved')
insert into tblResidents values (7,'betty',7,'moved')
insert into tblResidents values (8,'cornelius',8,'current')
create table tblPhone (
ResID tinyint ,
Type varchar(5),
[Number] varchar(10),
constraint fk_phone foreign key (resid) references tblResidents
)
insert into tblPhone values (1,'work','055-1234')
insert into tblPhone values(2,'home','155-1234')
--insert into tblPhone values(3,'work','255-1234')--
insert into tblPhone values(3,'home','355-1234')
insert into tblPhone values(5,'work','455-1234')
insert into tblPhone values(6,'work','555-1234')
insert into tblPhone values(7,'home','655-1234')
--insert into tblPhone values(7,'work','755-1234')-- uncomment to get
betty's PH#
SELECT A.Building, A.AptNum, R.[Name], P.[Number] as Phone, R.Status
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum ) -- and r.status = 'moved')
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID AND P.Type = 'Work'
WHERE A.Building = 3
AND R.Status = 'Moved'
-- AND (P.Type = 'Work') -- OR P.Type IS NULL)
drop table tblPhone
drop table tblResidents
drop table tblApartments
set nocount off
Result set:
Building AptNum Name Phone Status
-- -- -- -- --
3 6 jean-baptiste 555-1234 moved
3 7 betty NULL moved
Which is correct, 'cuz betty & jean-baptiste are the only residents of
building 3 apts who have moved.
============
If you change the FROM & WHERE clause to this:
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum )
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID --AND P.Type = 'Work'
WHERE A.Building = 3
AND R.Status = 'Moved'
AND (P.Type = 'Work' OR P.Type IS NULL)
The result set is:
Building AptNum Name Phone Status
-- -- -- -- --
3 6 jean-baptiste 555-1234 moved
which means the "OR P.Type IS NULL" criteria doesn't pull betty's record
as you suggested it would.
This solves some problems I've had w/ LEFT JOINS not working as I had
anticipated. Thanks for the info.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQiEODIechKqOuFEgEQLr0ACgylrysm6ilcay
4mfQ1n/B5GcLw3oAoJ+9
DDimxetXGCEb6UEz2vEmaA4m
=oICZ
--END PGP SIGNATURE--|||"MGFoster" <me@.privacy.com> wrote in message
news:K68Ud.6547$873.1771@.newsread3.news.pas.earthlink.net...
> < snip >
> < snip >
> Simon Shearn wrote:
=
are
because
condition
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Simon, Thanks for the info. It made me want to see how this works. So
> I went ahead & created some test tables/data:
> use tempdb
> go
> set nocount on
> create table tblApartments (
> building tinyint,
> aptnum tinyint primary key
> )
> insert into tblApartments values (1,1)
> insert into tblApartments values (1,2)
> insert into tblApartments values (1,3)
> insert into tblApartments values (2,4)
> insert into tblApartments values (2,5)
> insert into tblApartments values (3,6)
> insert into tblApartments values (3,7)
> insert into tblApartments values (3,8)
> create table tblResidents(
> ResID tinyint primary key,
> [name] varchar(20),
> aptnum tinyint ,
> status varchar(10),
> constraint fk_res foreign key (aptnum) references tblApartments
> )
> insert into tblResidents values (1,'dalton',1,'moved')
> insert into tblResidents values (2,'liloo',2,'current')
> insert into tblResidents values (3,'lucy',3,'moved')
> insert into tblResidents values (4,'harry',4,'moved')
> insert into tblResidents values (5,'sondine',5,'current')
> insert into tblResidents values (6,'jean-baptiste',6,'moved')
> insert into tblResidents values (7,'betty',7,'moved')
> insert into tblResidents values (8,'cornelius',8,'current')
> create table tblPhone (
> ResID tinyint ,
> Type varchar(5),
> [Number] varchar(10),
> constraint fk_phone foreign key (resid) references tblResidents
> )
> insert into tblPhone values (1,'work','055-1234')
> insert into tblPhone values(2,'home','155-1234')
> --insert into tblPhone values(3,'work','255-1234')--
> insert into tblPhone values(3,'home','355-1234')
> insert into tblPhone values(5,'work','455-1234')
> insert into tblPhone values(6,'work','555-1234')
> insert into tblPhone values(7,'home','655-1234')
> --insert into tblPhone values(7,'work','755-1234')-- uncomment to get
> betty's PH#
> SELECT A.Building, A.AptNum, R.[Name], P.[Number] as Phone, R.Status
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum ) -- and r.status = 'moved')
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID AND P.Type = 'Work'
> WHERE A.Building = 3
> AND R.Status = 'Moved'
> -- AND (P.Type = 'Work') -- OR P.Type IS NULL)
> drop table tblPhone
> drop table tblResidents
> drop table tblApartments
> set nocount off
> Result set:
> Building AptNum Name Phone Status
> -- -- -- -- --
> 3 6 jean-baptiste 555-1234 moved
> 3 7 betty NULL moved
> Which is correct, 'cuz betty & jean-baptiste are the only residents of
> building 3 apts who have moved.
> ============
> If you change the FROM & WHERE clause to this:
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum )
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID --AND P.Type = 'Work'
> WHERE A.Building = 3
> AND R.Status = 'Moved'
> AND (P.Type = 'Work' OR P.Type IS NULL)
> The result set is:
> Building AptNum Name Phone Status
> -- -- -- -- --
> 3 6 jean-baptiste 555-1234 moved
> which means the "OR P.Type IS NULL" criteria doesn't pull betty's record
> as you suggested it would.
> This solves some problems I've had w/ LEFT JOINS not working as I had
> anticipated. Thanks for the info.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQiEODIechKqOuFEgEQLr0ACgylrysm6ilcay
4mfQ1n/B5GcLw3oAoJ+9
> DDimxetXGCEb6UEz2vEmaA4m
> =oICZ
> --END PGP SIGNATURE--
Hello -
Yes, you're right - the alternative form I suggested only works when the
person involved has no phone of any kind, so putting the P.Type = 'Work' in
the join condition, as others suggested, is the correct way of doing it.
Regards,
Simon|||On Sat, 26 Feb 2005 03:28:15 GMT, MGFoster wrote:
(snip)
>P.Type = 'Work' in ON clause: I've seen the equivalency evaluation of
>"hard coded" data in the JOIN's ON clause before, but I've always put,
>what could be a parameter, in the WHERE clause. Is there any increased
>efficiency in putting it in the ON clause rather than the WHERE clause?
Hi MGFoster,
Sorry for the late reply. The flu managed to get me down; I'm now
struggling to remove as much as possible from my 400+ message backlog
before my headache forces me back to bed again. :-)
Anyway, Simon already pointed out that in the case of outer joins, the
choice to put things in the WHERE clause or the ON clause influences the
results.
In the case of INNER joins, there is no performance difference, so
choose what suits you best. My preference (and I know I'm not alone with
this) is to code the "proper" joining criteria (usually following the
defined foreign keys) in the ON and the filter criteria in the WHERE.
But that's just my personal preference.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Propagating results of Alter Table to its views

When I alter a table to add columns or change their properties, either in
Enterprise Manager or with T-SQL statements in Query Analyzer, the
alterations are not automatically noticed by the views that use the table.
The only thing I have been able to figure out to do is to find each
potentially affected view and then:
1. Open it in design mode
2. Check or uncheck a field in the Altered table
3. Uncheck or check the same field to restore the view to its original field
selection
4. Close the view and Save it.
Two problems:
1. That's tedious and time-consuming
2. It is easy to miss an affected view.
Can someone advise me as to a better way? (I am mostly using SQL Server 2000
)
Thanks,
Doug MacLeanDoug MacLean wrote:
> When I alter a table to add columns or change their properties, either in
> Enterprise Manager or with T-SQL statements in Query Analyzer, the
> alterations are not automatically noticed by the views that use the table.
> The only thing I have been able to figure out to do is to find each
> potentially affected view and then:
> 1. Open it in design mode
> 2. Check or uncheck a field in the Altered table
> 3. Uncheck or check the same field to restore the view to its original fie
ld
> selection
> 4. Close the view and Save it.
> Two problems:
> 1. That's tedious and time-consuming
> 2. It is easy to miss an affected view.
> Can someone advise me as to a better way? (I am mostly using SQL Server 20
00)
> --
> Thanks,
> Doug MacLean
Firstly, do not use SELECT * in views. It's generally a bad idea to use
SELECT * anywhere in production code. It's a particularly bad idea in
views because of the way views handle changes to the columns.
So assuming you have named columns in all your views, use
sp_refreshview to ensure the view is up to date with changes to its
columns. If you want to add a column you'll have to edit the view
definition of course, which is good practice and not difficult if you
have adequate source control and change control procedures. Table
changes should never be executed by Enterprise Manager in a production
environment.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Thanks. That's very helpful. It eliminates the need for my 4-step process in
Enterprise Manager.
I wonder if there is a tool to list all of the views that depend on a
specified table, because the other challenge is actually finding all of them
when altering a table.
Best Regards,
--
Doug MacLean
"David Portas" wrote:

> Doug MacLean wrote:
> Firstly, do not use SELECT * in views. It's generally a bad idea to use
> SELECT * anywhere in production code. It's a particularly bad idea in
> views because of the way views handle changes to the columns.
> So assuming you have named columns in all your views, use
> sp_refreshview to ensure the view is up to date with changes to its
> columns. If you want to add a column you'll have to edit the view
> definition of course, which is good practice and not difficult if you
> have adequate source control and change control procedures. Table
> changes should never be executed by Enterprise Manager in a production
> environment.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Greetings,
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
> David,
> Thanks. That's very helpful. It eliminates the need for my 4-step process
> in
> Enterprise Manager.
> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
> Best Regards,
> --
> Doug MacLean
Create the views WITH SCHEMABINDING and you won't be able to change the
table without first dropping the view. This is a good reminder to fix the
view ;-)
Regards,
Neale NOON|||You may want to use this.
select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where table_name = '<table_name>'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
You might find it easier to refresh all views since you cannot rely on
dependency information unless the views were created WITH SCHEMABINDING.
The script below will generate a script to refresh all views in the current
database. You can wrap it in a cursor and execute according to your
preference. BTW, even without 'SELECT *', you can run into issues with
changed datatypes in the referenced tables.
SELECT
'EXEC sp_refreshview ''' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
''''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
AND OBJECTPROPERTY(OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
> David,
> Thanks. That's very helpful. It eliminates the need for my 4-step process
> in
> Enterprise Manager.
> I wonder if there is a tool to list all of the views that depend on a
> specified table, because the other challenge is actually finding all of
> them
> when altering a table.
> Best Regards,
> --
> Doug MacLean
>
> "David Portas" wrote:
>|||Thanks, Neale.
That's an interesting idea to know when I change a table that has one or
more dependent views. So it is a good reminder.
But I KNOW there are views. And what I want is a clean way of finding and
refreshing them. Adding a process of dropping (and then re-adding) them seem
s
tedious.
Thanks Best Regards,
--
Doug MacLean
"Neale NOON" wrote:

> Greetings,
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
> news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
>
> Create the views WITH SCHEMABINDING and you won't be able to change the
> table without first dropping the view. This is a good reminder to fix the
> view ;-)
> --
> Regards,
> Neale NOON
>
>|||Dan,
Ahhh!. That's a great idea. Thanks for both the idea and the sample code.
And it's practical because I only do such updates on the production system a
t
times when no one is using it.
You're right. I've already seen the problem that -- even without adding
columns -- the view needs to be refreshed.
Thanks much and Best regards,
--
Doug MacLean
"Dan Guzman" wrote:

> You might find it easier to refresh all views since you cannot rely on
> dependency information unless the views were created WITH SCHEMABINDING.
> The script below will generate a script to refresh all views in the curren
t
> database. You can wrap it in a cursor and execute according to your
> preference. BTW, even without 'SELECT *', you can run into issues with
> changed datatypes in the referenced tables.
> SELECT
> 'EXEC sp_refreshview ''' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> ''''
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'VIEW'
> AND OBJECTPROPERTY(OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> ), 'IsMSShipped') = 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
> news:C701B020-919E-44B3-A61E-1FCF7AC6AA8D@.microsoft.com...
>
>

Promt user for criteria ?

I know it can be done with a SP but is there a way to prompt a user for
specific criteria like date range (between ? and ?) in a view.
I have a query in a view but I need to prompt the user for a date range, my
front end is access 2002 I can do it as a passthrough but it takes to long to
run and while testing I found that as a view it runs in half the time.
Thanks
Xavier
On Fri, 20 Jan 2006 07:36:05 -0800, Xavier wrote:

>I know it can be done with a SP but is there a way to prompt a user for
>specific criteria like date range (between ? and ?) in a view.
>I have a query in a view but I need to prompt the user for a date range, my
>front end is access 2002 I can do it as a passthrough but it takes to long to
>run and while testing I found that as a view it runs in half the time.
Hi Xavier,
SQL Server can't ever prompt the user. Not in a view, and not in a
stored procedure either.
If you refer to prompting for arguments in the front end, then passing
them as parameters to the back end: that is possible in stored procs,
but not in a view. But you can use variables in a SELECT statement that
queries a view.
However, I am intrigued by your statement that you found a view to be
faster than a stored procedure. While I don't doubt your observations,
I'm pretty sure that there's no such blanket statement about performance
of views vs stored procedures. I suspect that is has something to do
with the specific details of your tables, your stored procedure and your
view.
To investigate this further, you'll have to give more details: how do
your tables look (CREATE TABLE statements), how are the view and the
stored procedure defined (CREATE VIEW / CREATE PROC), what does your
data typically look like (INSERT statements for a small sample of your
data) and how do you call the view and the stored proc?
Chcek out www.aspfaq.com/5006 for some tips on how to assemble this
information.
Hugo Kornelis, SQL Server MVP
|||Hugo
You are correct I mistakenly said faster than a SP but I should has said
faster that a passthrough query from my front end which is access.
I could not run my report from access unless I add the view to my access
front ent as a linked table, is there any other way ? for an access report to
run a sql view and pass a parameter, like a date range to extrack particular
data example ()now
minus 48hrs everytime the user runs it.
Thanks
Xavier
"Hugo Kornelis" wrote:

> On Fri, 20 Jan 2006 07:36:05 -0800, Xavier wrote:
>
> Hi Xavier,
> SQL Server can't ever prompt the user. Not in a view, and not in a
> stored procedure either.
> If you refer to prompting for arguments in the front end, then passing
> them as parameters to the back end: that is possible in stored procs,
> but not in a view. But you can use variables in a SELECT statement that
> queries a view.
> However, I am intrigued by your statement that you found a view to be
> faster than a stored procedure. While I don't doubt your observations,
> I'm pretty sure that there's no such blanket statement about performance
> of views vs stored procedures. I suspect that is has something to do
> with the specific details of your tables, your stored procedure and your
> view.
> To investigate this further, you'll have to give more details: how do
> your tables look (CREATE TABLE statements), how are the view and the
> stored procedure defined (CREATE VIEW / CREATE PROC), what does your
> data typically look like (INSERT statements for a small sample of your
> data) and how do you call the view and the stored proc?
> Chcek out www.aspfaq.com/5006 for some tips on how to assemble this
> information.
> --
> Hugo Kornelis, SQL Server MVP
>
|||On Mon, 23 Jan 2006 06:36:03 -0800, Xavier wrote:

>Hugo
>You are correct I mistakenly said faster than a SP but I should has said
>faster that a passthrough query from my front end which is access.
>I could not run my report from access unless I add the view to my access
>front ent as a linked table, is there any other way ? for an access report to
>run a sql view and pass a parameter, like a date range to extrack particular
>data example ()now
>minus 48hrs everytime the user runs it.
Hi Xavier,
When using Access, you want to make sure that network traffic is kept at
a minimum. Stored procedures and pass-through queries meet this
requirement without any doubt. I'm less sure about queries on linked
tables.
I have read reports claiming that Access will fetch a complete linked
table over the network to execute the query client-side. I have also
read reports claiming that the former reports are rubbish. My personal
experience with Access is too limited to be able to tell which reports
are true and which aren't. If you want to find out, then run a profiler
trace while running Access and check what Access really sends to the
server.
I would personally use a stored procedure, but that's probably because
I'm more at ease on SQL Server - I know how to squeeze every drop of
performance out of the SP code; I'm not nearrly as proficient in
optimizing Jet SQL.
If your tests indicate that an Access query on an Access linked table
(to a SQL Server table or view) is faster than calling a stored
procedure, then by all means go for it.
Hugo Kornelis, SQL Server MVP
|||Hugo
Thank you, based on your experience with SQL can you recommend
and good books on SP and queries within sql.
We are planning on moving from access as a front end reporting tool and
replacing it with Crystal Reports, any thoughts on this you might want to
share?
We have a web based application where our users need to print certain forms
with the data that is on their screen plus a little more not seen at the
moment
that we have determined this information will print on every form allways.
The provider of our web based application which uses a propriatary tool kit
together with FrontPage to compile the forms will only support Access or
Crystal Reports.
Thanks
Xavier
"Hugo Kornelis" wrote:

> On Mon, 23 Jan 2006 06:36:03 -0800, Xavier wrote:
>
> Hi Xavier,
> When using Access, you want to make sure that network traffic is kept at
> a minimum. Stored procedures and pass-through queries meet this
> requirement without any doubt. I'm less sure about queries on linked
> tables.
> I have read reports claiming that Access will fetch a complete linked
> table over the network to execute the query client-side. I have also
> read reports claiming that the former reports are rubbish. My personal
> experience with Access is too limited to be able to tell which reports
> are true and which aren't. If you want to find out, then run a profiler
> trace while running Access and check what Access really sends to the
> server.
> I would personally use a stored procedure, but that's probably because
> I'm more at ease on SQL Server - I know how to squeeze every drop of
> performance out of the SP code; I'm not nearrly as proficient in
> optimizing Jet SQL.
> If your tests indicate that an Access query on an Access linked table
> (to a SQL Server table or view) is faster than calling a stored
> procedure, then by all means go for it.
> --
> Hugo Kornelis, SQL Server MVP
>
|||On Thu, 26 Jan 2006 18:31:01 -0800, Xavier wrote:

>Hugo
>Thank you, based on your experience with SQL can you recommend
>and good books on SP and queries within sql.
Hi Xavier,
I've learnt most from Books Online and by trial and error. But I'll give
you some titles that are often recommended in these groups by very
kowledgeable persons.
Two books specifically targetted towards coding for SQL Server:
* Advanced Transact-SQL for SQL Server 2000 (Ben-Gan/Moreau)
* The Guru's Guide to Transact-SQL (Henderson)
A book about the inner working of SQL Server - a great aid if you start
to think about fine-tuning, since knowing how things work is the best
way to tune:
* Inside SQL Server 2000 (Delaney)
An advanced book, full with tips and tricks. Definitely no easy stuff
here. And it uses ANSI standard SQL - you'll have to change some things
here and there to amke it run on SQL Server:
* SQL for Smarties (Celko)

>We are planning on moving from access as a front end reporting tool and
>replacing it with Crystal Reports, any thoughts on this you might want to
>share?
I've never worked with Crystal Reports.
Hugo Kornelis, SQL Server MVP

Prompts in Model Designer

I would like to set up a prompt / filter as part of the model that carries through to the report builder. I have a query where I would like to force a prompt on the user as part of any report they create. Ideally, I would like to have it default as well.

I noticed that there is a prompt attribute in model designer. When I go to add the filter attribute in model designer, I get the same dialog box I get in the report builder - except it does not have the prompt as an option. Is there a way to accomplish this?

Thanks

There is no way to specify in the report model that filter conditions created for a given attribute should be set to "Prompt" by default.

It's an interesting request, though. If you have a minute, I'd be interested to hear more about why this feature is important to you. You can contact me through my blog.

|||

For the same reason an administrator might set up Prompts on Reports in RS...RB reports are no different. We might want to impose certain filters...either to guide users in report building, or perhaps to limit the amount of resources in querying. the reasons are too numerous to list.

Having the capability on the user front-end but not the development back-end is a deficiency.

sql

Prompts in Model Designer

I would like to set up a prompt / filter as part of the model that carries through to the report builder. I have a query where I would like to force a prompt on the user as part of any report they create. Ideally, I would like to have it default as well.

I noticed that there is a prompt attribute in model designer. When I go to add the filter attribute in model designer, I get the same dialog box I get in the report builder - except it does not have the prompt as an option. Is there a way to accomplish this?

Thanks

There is no way to specify in the report model that filter conditions created for a given attribute should be set to "Prompt" by default.

It's an interesting request, though. If you have a minute, I'd be interested to hear more about why this feature is important to you. You can contact me through my blog.

|||

For the same reason an administrator might set up Prompts on Reports in RS...RB reports are no different. We might want to impose certain filters...either to guide users in report building, or perhaps to limit the amount of resources in querying. the reasons are too numerous to list.

Having the capability on the user front-end but not the development back-end is a deficiency.

Prompt Parameter Query?

Hi there,
How to I convert Northwind Access Query look like this
SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
Employees.HireDate
FROM Employees
WHERE
(((Employees.HireDate) Between [Enter Begining date]
And
[Enter ending date]))
OR
(((([Employees].[HireDate]) Like [Enter Begining date]) Is Null))
OR
(((([Employees].[HireDate]) Like [Enter ending date]) Is Null));
Into SQL Server 2005 Stored Proc. I tried
create proc usp_hdate as
declare @.Hdate datetime
select FirstName, LastName, HireDate
From Employees
Where (HireDate = @.Hdate) or HireDate Is Not Null
but no results
All I want to create prompt parameter for HireDate
When you don't type parameter It will return all records
when you type the date it will return specific record
Thanks an advanced
Oded DrorCREATE PROC usp_hdate
AS
DECLARE @.Hdate DATETIME
SELECT FirstName, LastName, HireDate
FROM Employees
WHERE HireDate = COALESCE(@.Hdate,HireDate)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Oded Dror wrote:
> Hi there,
> How to I convert Northwind Access Query look like this
> SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
> Employees.HireDate
> FROM Employees
> WHERE
> (((Employees.HireDate) Between [Enter Begining date]
> And
> [Enter ending date]))
> OR
> (((([Employees].[HireDate]) Like [Enter Begining date]) Is Null))
> OR
> (((([Employees].[HireDate]) Like [Enter ending date]) Is Null));
>
> Into SQL Server 2005 Stored Proc. I tried
> create proc usp_hdate as
> declare @.Hdate datetime
> select FirstName, LastName, HireDate
> From Employees
> Where (HireDate = @.Hdate) or HireDate Is Not Null
> but no results
> All I want to create prompt parameter for HireDate
> When you don't type parameter It will return all records
> when you type the date it will return specific record
>
> Thanks an advanced
> Oded Dror
>
>
>
>|||Thanks for your help
This will return all value if the parameter is null
but what about when I'm submitting parameter
Basically I want to submit parameter and received one record or
don't submit record and received all records
Thanks,
Ed Dror
"MGFoster" <me@.privacy.com> wrote in message
news:iiVyf.3116$Hd4.2207@.newsread1.news.pas.earthlink.net...
> CREATE PROC usp_hdate
> AS
> DECLARE @.Hdate DATETIME
> SELECT FirstName, LastName, HireDate
> FROM Employees
> WHERE HireDate = COALESCE(@.Hdate,HireDate)
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> Oded Dror wrote:

Prompt for variable values in a SQL script

I am writing a SQL script and it needs some parameters to be entered by the user. Users will run this script from the SQL Query Analyzer. I trying to see if there is a way the script will prompt the user to enter values for the parameters. This is possible in Oracle. Is there any equivalent in SQL Server?

Thanks in advance for your timeNo, you can't prompt for variables in the SQL script.

You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).

That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.

Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?

blindman|||I realize that it is a bad and dangerous practice to let the users access the database from the query analyzer. We are redesigning the system from the scratch but till then, we have to support the existing system. Prevoius DBA let this hole into the system and we have to live with this till we finish our re-design. I was almost positive what I was looking for is not possible but I just wanted to make sure. Thanks a lot for your time blindman.

Originally posted by blindman
No, you can't prompt for variables in the SQL script.

You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).

That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.

Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?

blindman

Prompt for value

What is the sql code to prompt for a value?
Can I do this in query analyzer or do I have to send the value from some other application?the query analyser cannot prompt the user for a value.|||Prompting for values is the responsibility of the application interface. Query Analyzer is a tool, but it is by no means an application interface.

prompt for value

How do I prompt for a value in my SQL query for MS SQL Server 2000. Eventually I would like to enter the value in excel that passes it to a pivot table and then to the sql query.You need to have Excel prompt for the value. SQL Server doesn't permit interactive things within Transact-SQL.

-PatP

Prompt for user input in criteria field of view

In Access, I use [Enter Date] in the Criteria field of the Query. I tried the same thing in SQL Server in the Criteria field of the View and it does not recognize this. Is there a comparable command in SQL to get user input into the Criteria field of a view?

Hi,

you either have to use a procedure with an input parameter or have to put a condition on the query with querying the view with:

Select * from SomeView Where SomeColumn = 'SomeValue'

But there is no GUI on SQL Server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||MSSQL as service prompts you nothing. You have to write client application to be prompted.|||

Hi Jens,

I was able to find out how to do what I needed using the @. sign (i.e. @.Date Required?). In the criteria field of the SQL view, this generates a 'Date Required?' prompt box when running the view.

Thanks anyway!

Ernie

sql

prompt for parameter in a view

I would like to create a view that will prompt the user for a parameter.
Much the same way a user can provide the paramenter for a query in Access.
Seems like it should be simple, but I don't know how to do it.
Any ideas?
Thanks
Tyler
Hi,
SQL Server will not prompt for a user input. Access will allow because it
have both Front end and back end.
Thanks
Hari
SQL Server MVP
"Tyler" <Tyler@.discussions.microsoft.com> wrote in message
news:CA2CA968-4B15-4ED6-AECF-FE3C17C119AB@.microsoft.com...
>I would like to create a view that will prompt the user for a parameter.
> Much the same way a user can provide the paramenter for a query in Access.
> Seems like it should be simple, but I don't know how to do it.
> Any ideas?
> Thanks
> --
> Tyler

Prompt box with Crystal

I am currently working on billing reports. I wish to add a facility that will allow us to use Crystal as a facility to query the SQL database. Ideally a prompt box of some sort would ask for a Customer ID of which will then filter the report content down to that customer. This could be achieved by directly intervieing with the SQL query, but this wouldn't suit with our staff.

I also have some other posts up if you are willing to help.

Thanks in advance.Ok, after doing a little research and reading the manual I think I may have found something that can assist me in this problem.

Parameter's.

I have set up a parameter that asks for a customer id, but it doesn't return the report or refresh the content of the report specific to that customer. Im unsure of how to approach this.

Regards