Embedded Queries?

I'm looking into a problem a friend is having, and I'll say right off
the bat that I work with with php and MySQL, and not MS SQL.

What he is attempting to do (in MS SQL) is take two database fields
from a table (string fields), multiply them together, and put them into
a third field. This third column in the table has not yet been created
the time of running the query.

If it needs to be multiple queries, that is fine. My first thought is
to use a simple ALTER query to add the column to the table, then to
call a UPDATE function which uses a select statement inside of it. I'm
not sure if something like this can even be done.

// ---- Suggested query

UPDATE chrisslu SET 'discquantity' = '(SELECT
chrisslu.quantity*chrisslu.nr_of_disc
FROM chrisslu
WHERE (str(period,6)>=? AND str(period,6)<=?))' WHERE
(str(period,6)>=?Andstr(period,6)<=?)

// ---- End Suggested query

It starts with an UPDATE, but replaces the value to be set with a
SELECT statement. I honestly don't even think this query is
syntactically correct, I'm just trying to get the general concept down
:).

So, question the first: Is this type of query possible? The reason
I'm doing this is because I was told MS SQL has no way of storing
temporary variables... otherwise I would just call a SELECT statement,
store the variable, and UPDATE the new field from the variable after
the ALTER statement.

Second question: If it is possible, am I on the right track, or does
it need to be entered in completely different than what I have?

Third: Regarding the 'type'. Do I need to do any kind of typecasting
or conversion of the fields? Both chrisslu.quantity and
chrisslu.nr_of_disc are string fields (that is what I was told, they
may be varchar of some kind). In order to use them in a math
statement, do they have to be floats, or doubles, or something similar?

I appreciate any response, I know this was a long winded question.

ChrisIf the new column is always to be the product of two other columns, why not
use a computed column:

alter table MyTable
add
MyCol as (Col1 * Col2)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Dranai" <dranai@.gmail.com> wrote in message
news:1140277653.986097.250450@.g43g2000cwa.googlegr oups.com...
I'm looking into a problem a friend is having, and I'll say right off
the bat that I work with with php and MySQL, and not MS SQL.

What he is attempting to do (in MS SQL) is take two database fields
from a table (string fields), multiply them together, and put them into
a third field. This third column in the table has not yet been created
the time of running the query.

If it needs to be multiple queries, that is fine. My first thought is
to use a simple ALTER query to add the column to the table, then to
call a UPDATE function which uses a select statement inside of it. I'm
not sure if something like this can even be done.

// ---- Suggested query

UPDATE chrisslu SET 'discquantity' = '(SELECT
chrisslu.quantity*chrisslu.nr_of_disc
FROM chrisslu
WHERE (str(period,6)>=? AND str(period,6)<=?))' WHERE
(str(period,6)>=?Andstr(period,6)<=?)

// ---- End Suggested query

It starts with an UPDATE, but replaces the value to be set with a
SELECT statement. I honestly don't even think this query is
syntactically correct, I'm just trying to get the general concept down
:).

So, question the first: Is this type of query possible? The reason
I'm doing this is because I was told MS SQL has no way of storing
temporary variables... otherwise I would just call a SELECT statement,
store the variable, and UPDATE the new field from the variable after
the ALTER statement.

Second question: If it is possible, am I on the right track, or does
it need to be entered in completely different than what I have?

Third: Regarding the 'type'. Do I need to do any kind of typecasting
or conversion of the fields? Both chrisslu.quantity and
chrisslu.nr_of_disc are string fields (that is what I was told, they
may be varchar of some kind). In order to use them in a math
statement, do they have to be floats, or doubles, or something similar?

I appreciate any response, I know this was a long winded question.

Chris|||That sounds like an excellent idea to look into. Do you know if SQL
will do the math on character fields and insert the data into a float
field?

Occam's law, right? Great suggestion, I'll look into it, thanks.|||You'd just have to cast things (and hope that there were no bugs in the
original data when you added the computed column). Going forward, the two
input columns would have to be numeric:

cast (Col1 as int) * cast (Col2 as int)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Dranai" <dranai@.gmail.com> wrote in message
news:1140283416.620277.318370@.f14g2000cwb.googlegr oups.com...
That sounds like an excellent idea to look into. Do you know if SQL
will do the math on character fields and insert the data into a float
field?

Occam's law, right? Great suggestion, I'll look into it, thanks.|||Great, that is what I was looking for. I am very unfamiliar with the
SQL language, so I had no idea how to typecast. I did some web
browsing for how to do it, and honestly I got too many different
answers, so I wasn't sure which one to use. I was attempting to use
CONVERT(int, col1) first.

Thank you again for the help.

Chris|||Convert works, too. Cast is ANSI, while convert is T-SQL only.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Dranai" <dranai@.gmail.com> wrote in message
news:1140289709.094990.39720@.o13g2000cwo.googlegro ups.com...
Great, that is what I was looking for. I am very unfamiliar with the
SQL language, so I had no idea how to typecast. I did some web
browsing for how to do it, and honestly I got too many different
answers, so I wasn't sure which one to use. I was attempting to use
CONVERT(int, col1) first.

Thank you again for the help.

Chris|||Hey Chris,

Does your friend NEED to store the results of the mathematical
operation in the table, or will the value of this column always be
dependant on the other two columns? If the latter, then simply do the
calculation in the query returning data. No need to waste storage
space (or worry about validation) if the information stored is enough
to calculate your values.

Of course, Tom's suggestion about computed columns doesn't have
physical storage issues, but you have to be aware of the nature of a
computed column when doing an INSERT or UPDATE statement against the
table.

Stu|||Hmm...

I believe the answer to the question is "both". My friends needs to
store the results in the table as a new column, so that later queries
can access the data.

Your last paragraph has given me pause. Could using a computed column
from the query cause problems later on?

The query I was able to come up with is:

// -- Query --
add discquantity as ((cast(chrisslu.quantity as int)) *
cast(chrisslu.nr_of_disc as int))
// -- End Query --

And is returning "function name is missing )". Any thoughts on this?
I didn't believe that the query was related to any function call,
unless each query is considered a function call in MS SQL.

I definately believe we're getting close to a solution here.

Chris|||>From what I can see, you need to include the ALTER TABLE; eg:

ALTER TABLE chrisslu
ADD discquantity AS ((CAST(chrisslu.quantity AS int)) *
CAST(chrisslu.nr_of_disc as int))

As far as whehter or not to use a computed column, it shouldn't be a
problem if:

1. The value of the column is always dependant on the relationship with
the other columns;
2. You always use explicit column names when inserting or updating
data, and;
3. You don't plan on porting this database to another RDBMS engine.

Stu|||OK, great, none of those should be problems. Thank you for the
clarification.

Chris

Efficiency of a ''SELECT TOP'' style GROUP BY query: FREETEXT vs. FREETEXTTABLE

Hi,

Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':

SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options

WHERE FREETEXT(*, 'foo')

GROUP BY Username

ORDER BY [Count] DESC

SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options

JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct

ON OptionID = ct.[KEY]

GROUP BY Username

ORDER BY [Count] DESC

They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.

But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.

If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).

So... I am worrying about this unnecessarily? Should I just use the simpler first version?

Any thoughts appreciated.

Thanks

They are almost identical. Implicitly when you do freetext(*,'foo') the system will convert it to a join based on the key. That means the first query will be implicitly converted to:

Code Snippet

select top 10 username, count(*) [count]

from options o join freetexttable(options,*,'foo') ct on o.optionid=ct.key

group by username

order by [count] desc

which is obviously not as efficient as the second query. However, the result between the two will also differ. For the first query, you're doing the join, group by, and then select the top N. The second you limit the first 500 from FTS query before doing the join, group by, and then select top N. So, you've been lucky thus far because the top 500 returned by the FTS contain the top desired 10 for your second query.

|||

Hi,

Thanks very much - that is exactly the info I was looking for.

I chose '500' as the top_n_by_rank parameter based on a calculation that, having considered all the other factors in the project I am working on, I am likely to get at least 10 results in the second query in the vast majority of cases.

Thanks for your help.

Effect of a SELECT Stored Procedure on tables or database

Hi all,

I have a few stored procedures which all perfom SELECT queries on a table in the database. Do these kind of stored procedures affect any other processes or procedures working on that table. I am talking about locks, blocks etc.

For example, the database has a table which gets updated periodically by some process which I don't know. Now I wrote some stored procedures just to do the SQL SELECT with some conditions in WHERE clause. Is there any possibility that my stored procedure failed and the because of this, the process that runs on the table was not executed?

No, SELECT statements don't cause any kind of locking.|||

And if I don't talk about locking, can I be rest assured that there won't be anything else that can have an effect?

|||

Selects are pretty unobtrusive. I can't think of anything you'd need to worry about.

|||

gt1329a:

No, SELECT statements don't cause any kind of locking.

If your isolation level is read committed, SELECTs do put a shared resource lock but it doesnt block any UPDATEs. Locking is different from Blocking. If reading to-the-minute committed data is not important you can explicitly use NOLOCK.