Align image

I have an image in a table cell in my report. Naturally not all images fit 100% in the cell which is why I'd like to align the images in the middle of the cell, horisontally and vertically.

I've been unable to figure out how to do this in Reporting Services for SQL Server 2000. Am I missing something or is this simply not possible? Does anyone know whether it can be done in 2005?

try manipulating the padding values |||I would but we're talking an entire column of images of varying sizes here thus I would have to calculate the correct padding size based on the size of the column and of the image itself. It seems like a little much for such a simple thing.|||I have not seen any built in functionality for this in reporting services so I guess your options are pretty limited. Padding values are a sure way to get this done but you could have the cell fixed sized and have your image fit proportionally|||http://blogs.msdn.com/ChrisHays/

this link tells you what i been saying all along. paddings are the only way to go.

and the working sample is here

Aliasing columns for a DMX subquery

I require the column of a nested table (KOL s) as part of the output of my DMX query, which needs to be written out to a relational table. Hence, I flatten the <select_list> of the SELECT DMX query as below:

SELECT FLATTENED

([Speciality].[SPECIALITY ID]) as [Speciality_Id],

(0) as [Bool_NameInAuthors],

(0) as [Bool_EmailInAbstract],

(0) as [Bool_AffiliationInAbstract],

(SELECT ([KOL ID]) as [Id], ([FIRST NAME]) as [FirstName], ([MIDDLE NAME]) as [MiddleName], ([LAST NAME]) as [LastName], ([AFFILIATION]) as [Affiliation], ([EMAILADDRESS]) as [EmailAddress] FROM [Speciality].[KO Ls]),

(SELECT ([Speciality Term DESCRIPTION]) as [Term] FROM [Speciality].[SPECIALITYTERMS]) AS Spec

From

[Speciality]

PREDICTION JOIN

OPENQUERY([ETL Profiler DB],

'SELECT

[SPECIALITY_ID]

FROM

[dbo].[KOLs]

') AS t

ON

[Speciality].[SPECIALITY ID] = t.[SPECIALITY_ID]

However, this causes the subquery columns (ID, FirstName, ...) to be aliased as Expression.ID, Expression.FirstName...

How do I alias these flattened columns properly?

I tried to alias the subquery to a derived table (as follows), but it just replaces the Expression word by the derived table alias (KOL in this case). So, does not solve my problem.

(SELECT ([KOL ID]) as [Id], ([FIRST NAME]) as [FirstName], ([MIDDLE NAME]) as [MiddleName], ([LAST NAME]) as [LastName], ([AFFILIATION]) as [Affiliation], ([EMAILADDRESS]) as [EmailAddress] FROM [Speciality].[KO Ls]) AS KOL

You can enclose the entire query in another SELECT where you alias the nested table columns -

SELECT [KOL.Id] as KOL_Id, [KOL.FirstName] as KOL_FirstName, ....

FROM

(SELECT FLATTENED ....) AS TT

sql

Aliasing a column name

Hi,
I'm trying to write a SQL event (view or storied proceedure). I have a table that is meant for reporting....the data is arranged verticle. I deal in Fiscal Years ie 2006/2007, 2007/2008, ect. The table in question has generic column lables ie FY1, FY2. I'm writing a report off the table and I want to dynamically turn FY1 into the current FiscalYear, FY2 into current FiscalYear + 1. I tried:

SELECT dbo.tblBudgetConfig.CurrentBudgetYear, dbo.tblBudgetProjectedCurrent.FY4 AS Left ([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3 & "/" & Right([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3
FROM dbo.tblBudgetConfig INNER JOIN
dbo.tblBudgetProjectedCurrent ON dbo.tblBudgetConfig.CurrentBudgetYear = dbo.tblBudgetProjectedCurrent.CurrentBudgetYear

But SQL squawks everytime I try this and tells me that there is something a miss near 'Left'.

Any help will be appreciated.

Nope, you cannot dynamically alias columns. Columns are part of the definition of the query and have to be there at the end of compile phase, not execution. To do this you would need to use dynamic SQL like EXEC ('query string'). You would have to build up the AS in a prior query .

A couple of things to note:

& does not work in SQL. You have to use + for concatenation (and you have to cast everything to the proper datatype_

"value" does not mean a literal, it means a column name. Use single quotes 'value'

Consider using the user interface to manage such things. This wouldn't be likely possible query anyhow because the data could change row by row, so you could then have a variable column name (it may not in your case, but SQL doesn't know that.) Instead of using the column names, add a column named fiscal year and store your value in there. Then let the UI put it in the right places and make it look all pretty for the user.

|||

AFAIK, you can′t compose the Aliases on the fly.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for your help. Its not the answer I was hoping for but what you are saying does make sense. And thanks for the t-sql tips too. I am really new at this so any help is appreciated.

Aliasing a column name

Hi,
I'm trying to write a SQL event (view or storied proceedure). I have a table that is meant for reporting....the data is arranged verticle. I deal in Fiscal Years ie 2006/2007, 2007/2008, ect. The table in question has generic column lables ie FY1, FY2. I'm writing a report off the table and I want to dynamically turn FY1 into the current FiscalYear, FY2 into current FiscalYear + 1. I tried:

SELECT dbo.tblBudgetConfig.CurrentBudgetYear, dbo.tblBudgetProjectedCurrent.FY4 AS Left ([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3 & "/" & Right([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3
FROM dbo.tblBudgetConfig INNER JOIN
dbo.tblBudgetProjectedCurrent ON dbo.tblBudgetConfig.CurrentBudgetYear = dbo.tblBudgetProjectedCurrent.CurrentBudgetYear

But SQL squawks everytime I try this and tells me that there is something a miss near 'Left'.

Any help will be appreciated.

Nope, you cannot dynamically alias columns. Columns are part of the definition of the query and have to be there at the end of compile phase, not execution. To do this you would need to use dynamic SQL like EXEC ('query string'). You would have to build up the AS in a prior query .

A couple of things to note:

& does not work in SQL. You have to use + for concatenation (and you have to cast everything to the proper datatype_

"value" does not mean a literal, it means a column name. Use single quotes 'value'

Consider using the user interface to manage such things. This wouldn't be likely possible query anyhow because the data could change row by row, so you could then have a variable column name (it may not in your case, but SQL doesn't know that.) Instead of using the column names, add a column named fiscal year and store your value in there. Then let the UI put it in the right places and make it look all pretty for the user.

|||

AFAIK, you can′t compose the Aliases on the fly.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for your help. Its not the answer I was hoping for but what you are saying does make sense. And thanks for the t-sql tips too. I am really new at this so any help is appreciated.

Alias on Update query

How can I put an alias on the table in an Update query
Update T64PE as Person
Where Person.ID = 5
(PS This works with Sybase)OK
Found this !

Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||Originally posted by Karolyn
OK
Found this !

Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5

Even more :

Update Person
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||noted !
(thks)sql

alias for variable [memory] table?

i have this sql Q:
a delete statement not allowed to use with "as" (delete from table as t
where..),
so when i need "as" i do it in sub q, like this:
delete from customers where 3<(select count(*) from customer t where
t.date=customer.date)
but when table in memory table, I got err:
declare @.t table([id] int, [date] smalldatetime)
delete from @.t where 3<(select count(*) from @.t t where @.t.date=t.date)
I got err:Must declare the scalar variable "@.t"
have a solution for like situations?Indeed, you need to use an alias. Try something like this:
declare @.t table([id] int, [date] smalldatetime)
delete a from @.t a where 3<(select count(*) from @.t t where
a.date=t.date)
Razvan|||Your attempt at inventing syntax makes no sense in terms of the SQL
language model. An alias is supposed to act as it materializes a new
working table with the data from the original table expression in it.
To be consistent, this syntax says that you have done nothing to the
base table.
The next question is why would you use that proprietary in memory table
in the first place? It looks like you are mimicking a scratch tape in a
1950's file system instead of writing SQL. But you did not post enough
for anyone to give you a relatioanl solution.|||Use an alias, but use it where it belongs:
delete <alias>
from <table> [as] <alias>
where <condition>
ML
http://milambda.blogspot.com/|||Here is another way, without aliases:
DELETE @.t WHERE date IN (
SELECT date FROM @.t
GROUP BY date
HAVING COUNT(*)>3
)
Razvan
PS. I hope your real columns have better names...|||to --CELKO--
I realy interest you, but i not understand at all, please explain your
approach!!
for anyone to give you a relatioanl solution.
my example is very clear, i need to delete from this table rec that
appear more
then 3 times. did you have better way?sql

Algorithmic question

I have a table with a field that contains a currency quote as a float.
In another table I have a rule field as a varchar.
We have an application that uses both to gennerate a price.
I need to do the same thing but in SQL.
An example could be:
Price = 1.05
rule = '*100'
Another example:
price = 1.05
rule = '*0+95'
The rules can also include division and minus.
How do I get the value of the field with the rule applied in SQL
Any idears ?
Best regards
Mikaelwhen you select a query do you use a single rule for all the prices or a
different rule based on condition.
can you post the ddl please|||I've done something similar but on a very large scale for a client, they
have about 10 calculations each calculation has a couple of hundred
different variables with different formula's, the best approach I found was
dynamic SQL and parameter substition...
Point 1 though, you shouldn't use float because its an approximate datatype,
use decimal instead.
declare @.formula_sql nvarchar(1000)
declare @.rule nvarchar(50)
set @.rule = '* 0.95'
set @.formula_sql = 'set @.answer = @.price ' + @.rule
declare @.answer decimal( 28, 3 )
sp_executesql @.forumla_sql,
N'@.price decimal( 28, 3 ), @.answer decimal( 28, 3 )
output',
@.price, @.answer OUTPUT
print @.answer
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:817AE7F0-660E-4174-BAA3-6220184E6F60@.microsoft.com...
>I have a table with a field that contains a currency quote as a float.
> In another table I have a rule field as a varchar.
> We have an application that uses both to gennerate a price.
> I need to do the same thing but in SQL.
> An example could be:
> Price = 1.05
> rule = '*100'
> Another example:
> price = 1.05
> rule = '*0+95'
> The rules can also include division and minus.
> How do I get the value of the field with the rule applied in SQL
> Any idears ?
>
> --
> Best regards
> Mikael|||Mikael
First of all avoid using FLOAT datatype for currency quote you may get
incorrect result particular in calculation
http://www.aspfaq.com/show.asp?id=2477
For the problem , please visit at
http://www.sommarskog.se/dynamic_sql.html
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:817AE7F0-660E-4174-BAA3-6220184E6F60@.microsoft.com...
>I have a table with a field that contains a currency quote as a float.
> In another table I have a rule field as a varchar.
> We have an application that uses both to gennerate a price.
> I need to do the same thing but in SQL.
> An example could be:
> Price = 1.05
> rule = '*100'
> Another example:
> price = 1.05
> rule = '*0+95'
> The rules can also include division and minus.
> How do I get the value of the field with the rule applied in SQL
> Any idears ?
>
> --
> Best regards
> Mikael|||Tony solved my problem, but here is the DDL anyways:
CREATE TABLE #curr
(
[id] int not null identity(1,1) primary key,
[rule] varchar(100) not null
)
CREATE TABLE #Price
(
FK_CurrId int not null primary key,
[price] decimal(28,10) not null-- float
)
insert into #curr ([rule])
VALUES ('*0+102')
INSERT INTO #Price (FK_CurrId,[price])
VALUES (@.@.identity,100)
SELECT *
FROM #curr c
INNER JOiN #Price p ON c.id = p.FK_CurrId
Best regards
Mikael
"Omnibuzz" wrote:

> when you select a query do you use a single rule for all the prices or a
> different rule based on condition.
> can you post the ddl please|||>> I have a table with a field [sic] that contains a currency quote as a float [bad id
ea!]. In another table I have a rule field [sic] as a varchar. <<
Columns and fields are totally different concepts. Get a book on basic
RDBMS before you do any more programming. FLOAT is never used in
currency calculations. The EU publishes some rules that you have to
follow when computing with Euros. The US has the GAAP rules. Your
design will not pass an audit.
You are vague about these rules, but it looks like they are arithmetic
functions of the form (price * A + B) where A and B can be positive or
negative, A can be greater or less than zero, and B can be any number
within a range.
CREATE TABLE Rules
(rule_name CHAR(7) NOT NULL PRIMARY KEY,
multiplier DECIMAL (8,4) DEFAULT 1.0 NOT NULL,
adder DECIMAL (8,4) DEFAULT 0.0 NOT NULL);
SQL is a compiled language and you are trying to use it as if you were
writing BASIC on the fly. The kludge is to use dynamic SQL, to mimic
interpreted BASIC.
I saw a similiar solution which stored formulas in a string for dynamic
SQL. A missing decimal point converted the formula to integer math and
destroyed the data integrity. It is really hard to see that 2 and 2.0
are not the same when you have a table with hundreds of such strings.sql

Algorithm to populate a table with finite value combinations

I need to populate a table which have 10 columns with four values. Each row should be a different combination of these four values and the columns can be null too. In other words how can I get all the different combinations for the 4 values that can be in 10 buckets. The final result column based on these values will be generated manually.

For example , I have for grades (P,F, WP, WF) and I have 8 terms and two exams. 8 terms and two exams can have any of the above four values. Based on these grades and terms and exams I need to generate a table which wil be used to determine the student final status Pass/Fail.

What will be the best way to do this and how is it possible. Is there a T-SQL or C# program for this.

If I need to submit this in another forum please let me know.

Do you just need some code which generates all the possible combinations? There are quite a few: 4 (scores) ^10 (exams) ~ 1M rows, without considering nulls. Do you plan to score them manually after?

You may want to rather develop some sort of scoring system (P =10,F=1, WP..., W...) and require a certain sum or average to pass

|||Yes. I am trying to get some code/algorithm to do this. The manual part will be assigning the Pass/Fail based on the combinations. Thanks.

Alerts passing info to a job

I'm not sure if this is the right place to post this, but here's my question
.
I have a trigger on a table where if a particular field is updated, I need
to create a file containing some info relevant to the row that was updated.
My initial thought was to raiserror in my trigger and then fire a job from
the alert which will run a select statement to a file, but I don't think it'
s
possible to pass information from my alert to my job to specify which row I
need to select against.
Am I going about this the entirely wrong way? Or is this a possibility?You can just start the job from trigger with sp_start_job. sp_start_job
works asynchronously, so your trigger won't have to wait for the job to
finish before it can complete itself.
Jacco Schalkwijk
SQL Server MVP
"MattBell" <MattBell@.discussions.microsoft.com> wrote in message
news:645BC25A-52AB-4E5F-A74C-7F6232D2C97C@.microsoft.com...
> I'm not sure if this is the right place to post this, but here's my
> question.
> I have a trigger on a table where if a particular field is updated, I need
> to create a file containing some info relevant to the row that was
> updated.
> My initial thought was to raiserror in my trigger and then fire a job from
> the alert which will run a select statement to a file, but I don't think
> it's
> possible to pass information from my alert to my job to specify which row
> I
> need to select against.
> Am I going about this the entirely wrong way? Or is this a possibility?|||Why not call a stored procedure from the trigger? You can pass the data
there and create your file when the trigger is fired..
Regards,
Brad Feaker
Ex nihilo, nihil fit
"MattBell" wrote:

> I'm not sure if this is the right place to post this, but here's my questi
on.
> I have a trigger on a table where if a particular field is updated, I need
> to create a file containing some info relevant to the row that was updated
.
> My initial thought was to raiserror in my trigger and then fire a job from
> the alert which will run a select statement to a file, but I don't think i
t's
> possible to pass information from my alert to my job to specify which row
I
> need to select against.
> Am I going about this the entirely wrong way? Or is this a possibility?|||Hey thanks for the info, works great.
One quick question though, is there anyway to squelch the "Job Started"
output from the sp_start_job?
"Jacco Schalkwijk" wrote:

> You can just start the job from trigger with sp_start_job. sp_start_job
> works asynchronously, so your trigger won't have to wait for the job to
> finish before it can complete itself.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "MattBell" <MattBell@.discussions.microsoft.com> wrote in message
> news:645BC25A-52AB-4E5F-A74C-7F6232D2C97C@.microsoft.com...
>
>|||No, unfortunately not. But it is only an informational message, so it won't
be picked up by most clients, for example ADO.
Jacco Schalkwijk
SQL Server MVP
"MattBell" <MattBell@.discussions.microsoft.com> wrote in message
news:047C5095-0253-40B8-BBFA-542603136CBD@.microsoft.com...
> Hey thanks for the info, works great.
> One quick question though, is there anyway to squelch the "Job Started"
> output from the sp_start_job?
>
> "Jacco Schalkwijk" wrote:
>

Alert on data deletion

We have an employee table that contains bank details and are experiencing
problems with account numbers being erased and lost. In order to track down
why this is happening (either due to our application code or SQL
replication) we'd like to be able to prevent certain columns from being
deleted if they already contain some data.

Is it possible to setup a check constraint to prevent our ee_acct_no columns
from being set to NULL or blank strings if it contains an account number
(i.e a 9 digit number)? We have setup the column to allow NULL's as we don't
always know employees bank details until later, so we do need to put them on
our database without bank details initially.

Also, if possible, can someone suggest a stored procedure or trigger i could
create that would fire a user-defined error message that would email an
operator if a bank account number changed?

Many thanks

Dan Williams.On Thu, 10 Mar 2005 14:35:55 +0000 (UTC), Dan Williams wrote:

> We have an employee table that contains bank details and are experiencing
> problems with account numbers being erased and lost. In order to track down
> why this is happening (either due to our application code or SQL
> replication) we'd like to be able to prevent certain columns from being
> deleted if they already contain some data.
> Is it possible to setup a check constraint to prevent our ee_acct_no columns
> from being set to NULL or blank strings if it contains an account number
> (i.e a 9 digit number)? We have setup the column to allow NULL's as we don't
> always know employees bank details until later, so we do need to put them on
> our database without bank details initially.

If ee_acct_no starts out null and later becomes non-null, then a check
constraint can't do the trick. You need a trigger ... which answers the
next question

> Also, if possible, can someone suggest a stored procedure or trigger i could
> create that would fire a user-defined error message that would email an
> operator if a bank account number changed?

CREATE TRIGGER trig_ee_acct
ON ee_acct
FOR UPDATE
AS
IF UPDATE(ee_acct_no)
BEGIN
declare @.msg varchar(400)
RAISERROR ('The ee_acct_no column must never be changed', 16, 1)
ROLLBACK TRANSACTION
END
GO

As Books Online describes:

All ad hoc messages have a standard message ID of 14,000.

Therefore in enterprise manager, you can set an email alert on message ID
14,000, and operators will get an email.

> Many thanks
> Dan Williams.|||Ross Presser (rpresser@.imtek.com) writes:
> As Books Online describes:
> All ad hoc messages have a standard message ID of 14,000.
> Therefore in enterprise manager, you can set an email alert on message ID
> 14,000, and operators will get an email.

Is that SQL 7 Books Online? The number for ad hoc messages is 50000.

Dan could also use sp_addmessage to add a custom message, for instance
75321 for this error, and say:

RAISERROR(75321, 16, 1)

and the set up the alert on this code.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Dan Williams (dtwilliams@.hotmail.com) writes:
> We have an employee table that contains bank details and are
> experiencing problems with account numbers being erased and lost. In
> order to track down why this is happening (either due to our application
> code or SQL replication) we'd like to be able to prevent certain columns
> from being deleted if they already contain some data.
> Is it possible to setup a check constraint to prevent our ee_acct_no
> columns from being set to NULL or blank strings if it contains an
> account number (i.e a 9 digit number)? We have setup the column to allow
> NULL's as we don't always know employees bank details until later, so we
> do need to put them on our database without bank details initially.

That would have to be a trigger. Ross showed you the basics, but I like
to add some more details.

First IF UPDATE() a bit heavy-handed. IF UPDATE() only tells us that
the column mentioned in the SET clause, but not that the value was
actually changed.

So, you would have to compare inserted and deleted with each other
to compare these. However, joining inserted and deleted can have
costly performance effects. My standard routine is to save inserted
and deleted into table variables and then work with these.

Note that it's a good idea to keep IF UPDATE(), so that you don't
perform the check if the UPDATE is for a completely different column
only.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 10 Mar 2005 21:46:25 +0000 (UTC), Erland Sommarskog wrote:

> Ross Presser (rpresser@.imtek.com) writes:
>> As Books Online describes:
>>
>> All ad hoc messages have a standard message ID of 14,000.
>>
>> Therefore in enterprise manager, you can set an email alert on message ID
>> 14,000, and operators will get an email.
> Is that SQL 7 Books Online? The number for ad hoc messages is 50000.
> Dan could also use sp_addmessage to add a custom message, for instance
> 75321 for this error, and say:
> RAISERROR(75321, 16, 1)
> and the set up the alert on this code.

SQL 2000 BOL. And it's self-contradictory!

msg_id

Is a user-defined error message stored in the sysmessages table. Error
numbers for user-defined error messages should be greater than 50,000. Ad
hoc messages raise an error of 50,000.

msg_str

Is an ad hoc message with formatting similar to the PRINTF format style
used in C. The error message can have up to 400 characters. If the message
contains more than 400 characters, only the first 397 will be displayed and
an ellipsis will be added to indicate that the message has been cut. All ad
hoc messages have a standard message ID of 14,000.|||That's great. Thanks a lot.

I've managed to create a trigger that makes use of custom made error
messages that get emailed to me whenever an account number gets
changed. Here is my SQL code:-

CREATE TRIGGER trig_ee_acct ON employee
FOR UPDATE
AS

DECLARE @.eecode varchar(30)
SET @.eecode = (select ee_code FROM inserted)

IF UPDATE(ee_acct_no)
DECLARE @.oldAcctNo varchar(10)
DECLARE @.newAcctNo varchar(10)

SET @.oldAcctNo = (select ee_acct_no from deleted)
SET @.newAcctNo = (select ee_acct_no from inserted)

IF LEN(@.oldAcctNo) > 0 AND @.newAcctNo = ''
BEGIN
RAISERROR ('Bank Account Numbers cannot be deleted.', 16, 1)
ROLLBACK TRANSACTION
END

IF LEN(@.newAcctNo) > 0 AND LEN(@.newAcctNo) < 8
BEGIN
RAISERROR ('Bank Account Numbers must be 8 digits long.', 16,
1)
ROLLBACK TRANSACTION
END

IF LEN(@.newAcctNo) = 8 AND @.newAcctNo <> @.oldAcctNo
BEGIN
RAISERROR (50001, 10, 1, @.eecode, @.oldAcctNo, @.newAcctNo)
END

I believe my logic could probably be made more efficient, but initially
this appears to be working. However, i am experiencing issues with my
custom alert not being triggered consistently when the account numbers
are being changed. Do the insert and delete trigger tables get purged
after the trigger fires?

After updating an employees account number, i do initially receive the
email alert (with details of the old and new numbers), but if i update
it immediately after, it doesn't seem to fire the alert. However, if i
wait a couple of minutes, it appears to work ok.

That's why i'm thinking the inserted and deleted tables still contain
the previously saved information. Is there anyway i can purge the
information?

Thanks again

Dan|||dtwilliams@.hotmail.com (dan_williams@.newcross-nursing.com) writes:
> CREATE TRIGGER trig_ee_acct ON employee
> FOR UPDATE
> AS
> DECLARE @.eecode varchar(30)
> SET @.eecode = (select ee_code FROM inserted)
> IF UPDATE(ee_acct_no)
> DECLARE @.oldAcctNo varchar(10)
> DECLARE @.newAcctNo varchar(10)
> SET @.oldAcctNo = (select ee_acct_no from deleted)
> SET @.newAcctNo = (select ee_acct_no from inserted)

This is not a good trigger. A trigger fires once per statement, and
the deleted/inserted tables can contain many rows, and a good trigger
should handle this. So you will need to rewrite your trigger.

> That's why i'm thinking the inserted and deleted tables still contain
> the previously saved information. Is there anyway i can purge the
> information?

deleted/inserted are so-called virtual tables and are constructed from
the transaction log, and they cannot be accessed outside the scope of
the trigger.

By the way, if you need to make many accesses to the tables in your
trigger, it's a good idea to copy the interesting columns to table
variables and work with these instead. This can give quite some
performance improvements.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9616EB37A93C5Yazorman@.127.0.0.1...
> dtwilliams@.hotmail.com (dan_williams@.newcross-nursing.com) writes:
>> CREATE TRIGGER trig_ee_acct ON employee
>> FOR UPDATE
>> AS
>>
>> DECLARE @.eecode varchar(30)
>> SET @.eecode = (select ee_code FROM inserted)
>>
>> IF UPDATE(ee_acct_no)
>> DECLARE @.oldAcctNo varchar(10)
>> DECLARE @.newAcctNo varchar(10)
>>
>> SET @.oldAcctNo = (select ee_acct_no from deleted)
>> SET @.newAcctNo = (select ee_acct_no from inserted)
> This is not a good trigger. A trigger fires once per statement, and
> the deleted/inserted tables can contain many rows, and a good trigger
> should handle this. So you will need to rewrite your trigger.
>> That's why i'm thinking the inserted and deleted tables still contain
>> the previously saved information. Is there anyway i can purge the
>> information?
> deleted/inserted are so-called virtual tables and are constructed from
> the transaction log, and they cannot be accessed outside the scope of
> the trigger.
> By the way, if you need to make many accesses to the tables in your
> trigger, it's a good idea to copy the interesting columns to table
> variables and work with these instead. This can give quite some
> performance improvements.

OK, thanks for the advice. I've just discovered the wonders of triggers and
have previously been performing validation and integrity checks in my
application code, so i'm very much a beginner in writing SQL code.

Could you provide me with an example trigger that i can use or point me in
the direction of a good web site that i can learn from?

Many thanks

Dan|||Dan Williams (dtwilliams@.hotmail.com) writes:
> OK, thanks for the advice. I've just discovered the wonders of triggers
> and have previously been performing validation and integrity checks in
> my application code, so i'm very much a beginner in writing SQL code.
> Could you provide me with an example trigger that i can use or point me in
> the direction of a good web site that i can learn from?

Writing triggers is not fundamentaly different from writing stored
procedures, although a few things apply:

1) The "inserted" and "deleted" are visible in the trigger only, not
from stored procedures or dynamic SQL called from the trigger.
2) The tables are slow to access, so if the trigger has many references
to them, copying to a table variable is recommendable.
3) You are always in the context of the transaction defined by the statement
that fired the trigger. For this reason, one should engage in long-
running operations, as this can give contention problems.
4) Any error (save RAISERROR) terminates execution, aborts the batch and
rolls back the transaction.
5) Likewise, if the transaction count on exit differs from the trancount
when the trigger started execution, this also causes the entire batch
to be rolled back.

The trigger you posted could be rewritten to something like:

CREATE TRIGGER trig_ee_acct ON employee
FOR UPDATE
AS

DECLARE @.inserted TABLE (...)
DECLARE @.deleted TABLE (...)

INSERT @.inserted (...)
SELECT ... FROM inserted

INSERT @.deleted (...9
SELECT ... FROM deleted

IF UPDATE(ee_acct_no)
BEGIN
IF EXISTS (SELECT *
FROM @.inserted i
JOIN @.deleted d ON i.pk = d.pk
WHERE len(d.oldAcctNo) > 0
AND nullif(i.newAcctNo, '') IS NULL
BEGIN
RAISERROR ('Bank Account Numbers cannot be deleted.', 16, 1)
ROLLBACK TRANSACTION
END

IF EXISTS (SELECT *
FROM @.inserted i
WHERE len(i.oldAcctNo) <> 8)
BEGIN
RAISERROR ('Bank Account Numbers must be 8 digits long.', 16, 1)
ROLLBACK TRANSACTION
END

IF EXISTS (SELECT *
FROM @.inserted i
JOIN @.deleted d ON i.pk = d.pk
WHERE d.oldAcctNo <> i.newAcctNo
BEGIN
RAISERROR (50001, 10, 1, @.eecode, @.oldAcctNo, @.newAcctNo)
ROLLBACK TRANSACTION
END
END

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

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

Alert from addition to table

The SMS provider will update my SQL server when there is incoming SMS
message.
apart from regular manual check on table
how can i know there is incoming new SMS message?
alert by email? any guidance?
Thanks.
TonyHi Tony,
You can have a trigger on the table that sends you an email
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"tony wong" <x34@.netvigator.com> wrote in message
news:%23nRYnw$nFHA.1148@.TK2MSFTNGP12.phx.gbl...
> The SMS provider will update my SQL server when there is incoming SMS
> message.
> apart from regular manual check on table
> how can i know there is incoming new SMS message?
> alert by email? any guidance?
> Thanks.
> Tony
>|||Hi Greg
Could i have some hints on that? i am new on programming.
my primary thought is to write exe to check any new SMS and then send email
via smtp, and run it in Windows Scheduler.
Any better methods? Thanks
Tony
"GregO" <grego@.community.nospam> glsD:edQ1fnAoFHA.1048@.tk2msftngp13.phx.gbl...[co
lor=darkred]
> Hi Tony,
> You can have a trigger on the table that sends you an email
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "tony wong" <x34@.netvigator.com> wrote in message
> news:%23nRYnw$nFHA.1148@.TK2MSFTNGP12.phx.gbl...
>[/color]sql

alert e-mail

Hello:
In SQL Server 2000 database I have a table MyTable which is updated daily
with previous day data. In some reason data could be missing for particular
day. To control the situation I'd like to create alert message to be sent to
me by e-mail.
The verification SQL statement is:
IF (select DATEDIFF ( day , max(datadate) , GetDate() )from MyTable) >1
print 'ALERT!'
How can I get the verification result by e-mail?
Thanks,
GBThere are generally three ways to send email from SQL 2000:
1. Install Outlook on the server and configure SQL Mail. You can then
use xp_sendmail to send emails.
2. Use xp_smtp_sendmail: http://www.sqldev.net/xp/xpsmtp.htm
3. Use a command-line mailer such as "Blat" in combination with
xp_cmdshell
I've used all three, and all three work well.
GB wrote:
> Hello:
> In SQL Server 2000 database I have a table MyTable which is updated daily
> with previous day data. In some reason data could be missing for particula
r
> day. To control the situation I'd like to create alert message to be sent
to
> me by e-mail.
> The verification SQL statement is:
> IF (select DATEDIFF ( day , max(datadate) , GetDate() )from MyTable) >1
> print 'ALERT!'
> How can I get the verification result by e-mail?
> Thanks,
> GB|||Thank you, xp_sendmail is working OK.
GB
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1149797645.683037.46670@.i39g2000cwa.googlegroups.com...
> There are generally three ways to send email from SQL 2000:
> 1. Install Outlook on the server and configure SQL Mail. You can then
> use xp_sendmail to send emails.
> 2. Use xp_smtp_sendmail: http://www.sqldev.net/xp/xpsmtp.htm
> 3. Use a command-line mailer such as "Blat" in combination with
> xp_cmdshell
> I've used all three, and all three work well.
>
> GB wrote:
daily
particular
sent to
>

Alem

Hi freinds

I have a trouble plz help me

how to create table in SQL programming.

Thank you inadvance

Quote:

Originally Posted by alem

Hi freinds

I have a trouble plz help me

how to create table in SQL programming.

Thank you inadvance

Use the Qry Create table table name and fields field datatypesql

Alaising the same table multiple times

To All--

First off, I'm new to SQL Server and apologize if this is a trivial question.

What I'm trying to do is alias the same table with 2 different names so that I may join them on two different fields.

Table1
Emp_Number
Emp_Code_1
Emp_Code_2

Table2 (Contains a list of codes and their related descriptions)
Emp_Code
Long_Desc
Short_Desc

I'm trying to query Table1 for the Emp_Number but I want to get the Short_Desc from Table2 for both Emp_Code1 and Emp_Code2. I'm using Microsoft Access as the front end (using Pass Through Queries) and SQL Server on the back end.

Hope this makes sense.

CSelect t1.Emp_Number, t1.Emp_Code_1, t2a.Short_Desc, t1.Emp_Code_2, t2b.Short_Desc
FROM Table1 t1
INNER JOIN Table2 t2a on t2a.Emp_Code = t1.Emp_Code_1
INNER JOIN Table2 t2b on t2b.Emp_Code = t1.Emp_Code_2


Same table, different alias.

Ajax Toggle Within a Gridview

Ive got a table of items which holds the privacy settings for each user. The items can either be the value 1 = Yes ,or 0 = No. Is it possible to bind these two options to a checkbox? I tryed to simply bind it to the checkboxes "checked" property, But it errored. Does anyone know how to bind an int feild to a checkbox? cos in the long run I want to add ajax toggle items to the checkboxes, but i was also wondering why that errored, but i think its cos i did my binding wrong. thanks si!

I've had this problem in the past. When using a boundfield checkbox, the type conversion fails converting from bool to int, if I remember right. If you change it to a template field, it should work though. I think this has to do with the implementation of checkboxfield.

|||

ill give it a go and get back to you! *fingers crossed* thanks si!

'aging' tables

I'm about to create some mechanism for control both: size of given table and
age of it's entries. I wish it to delete entries if older then X , or last n
entries when table is bigger then Y .
I need opinion if it will be good to use ddl trigger mechanism (SS2005), or
maybe someone would share another solution. It is supposed to be used only
for some tables like log table, not for each table in database, I don't want
to the mechanism decrease performance too much though.On 10.01.2007 10:49, fireball wrote:

Quote:

Originally Posted by

I'm about to create some mechanism for control both: size of given table and
age of it's entries. I wish it to delete entries if older then X , or last n
entries when table is bigger then Y .
I need opinion if it will be good to use ddl trigger mechanism (SS2005), or
maybe someone would share another solution. It is supposed to be used only
for some tables like log table, not for each table in database, I don't want
to the mechanism decrease performance too much though.

In that case a batch like approach is probably better, i.e. once a day
run your cleanup job. If possible you should have a CI with the
timestamp as leading column to make deleting more efficient.

The easiest is deletion by age. Deleting the oldest n records is more
difficult. If you are on SQL 2005 you may be able to create something
with an analytic function (i.e. using "row_number").

Cheers

robert|||Uzytkownik "Robert Klemme" <shortcutter@.googlemail.comnapisal w wiadomosci

Quote:

Originally Posted by

a batch like approach is probably better, i.e. once a day

the database will be run on remote client machine and developers are not
allowed to perform scheduled tasks on it (except upgradind/servicing) - so I
suppose I need to have it contorled either from application level or
database itself..|||fireball wrote:

Quote:

Originally Posted by

Uzytkownik "Robert Klemme" <shortcutter@.googlemail.comnapisal w
wiadomosci

Quote:

Originally Posted by

>a batch like approach is probably better, i.e. once a day

>
the database will be run on remote client machine and developers are
not allowed to perform scheduled tasks on it (except
upgradind/servicing)

I dont think he wants you to run the job manually.
Create a DTS package that deletes the rows, and have it scheduled/run as a
job by the sql-server-agent whenever the client starts sql-server (since you
don't know when the database/client will be turned on, you can't use a fixed
time like "at midnight").

/jim

Aggregation table

Hi all

i need to create aggregation table from 2 tables group by date, any one have any idea how to create it by using SSIS

thanks & regards

Use a merge join or union all to bring your two tables together and then use the aggregate component.

Aggregation Queries with > 80.000.000 rows

Hi,
We have a logging database with a table "Logs" that hold 80.000.000 log rows
(about 1 year of application logs). We need to analyze this data in for
variable time periods, so we cannot simply split the table. The table
contains a column "LogID" (Identity INT and the only member of the clustered
PK) and a column "Time" (datetime there exists an index for this column).
The query
SELECT *
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes 2 seconds and returns about 3000 rows.
The query
SELECT MAX(Time), MIN(Time)
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes more than a minute (I've cancelled the query after a minute).
How can this be? The query analyzer shows me a really simple query plan for
the first query using the PK. For the second query, the query plan shows me
no usage of the PK, but usage of the index for the column "Time" (two time,
one for min and one for max).
Even more strange is this query:
SELECT MAX(Logs1.Time), MIN(Logs1.Time)
FROM Logs Logs1 WITH (nolock)
INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
WHERE Logs1.LogID > 110385284
the query finishes in less than a second, and is using the PK.
Also specifying the PK as an index hint solves the problem, but I don't like
to specify query hints, because this may prevent an updated query processor
to use a better query plan.
Another problem is that we also have less expirienced programmers that need
to deal with such tables, so at the moment I need to review each and every
"slow" query and insert query hints. Will there be a fix that "optimizes" the
query engine of sql server?
CU,
Sven
Matzen
Yes, SQL Server needs to do some extra work when you specify an aggregate
functions.
Look, you may want to use INDEX hint to dictate the query optimizer to use
PK,but generally speaking it's not a good idea because the Optimizer is
smart enough to create much more efficient execution plan.
Also,you may create a clustered index on Time column and see how it does the
work.
"Matzen" <Matzen@.discussions.microsoft.com> wrote in message
news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> Hi,
> We have a logging database with a table "Logs" that hold 80.000.000 log
rows
> (about 1 year of application logs). We need to analyze this data in for
> variable time periods, so we cannot simply split the table. The table
> contains a column "LogID" (Identity INT and the only member of the
clustered
> PK) and a column "Time" (datetime there exists an index for this column).
> The query
> SELECT *
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes 2 seconds and returns about 3000 rows.
> The query
> SELECT MAX(Time), MIN(Time)
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes more than a minute (I've cancelled the query after a minute).
> How can this be? The query analyzer shows me a really simple query plan
for
> the first query using the PK. For the second query, the query plan shows
me
> no usage of the PK, but usage of the index for the column "Time" (two
time,
> one for min and one for max).
> Even more strange is this query:
> SELECT MAX(Logs1.Time), MIN(Logs1.Time)
> FROM Logs Logs1 WITH (nolock)
> INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
> WHERE Logs1.LogID > 110385284
> the query finishes in less than a second, and is using the PK.
> Also specifying the PK as an index hint solves the problem, but I don't
like
> to specify query hints, because this may prevent an updated query
processor
> to use a better query plan.
> Another problem is that we also have less expirienced programmers that
need
> to deal with such tables, so at the moment I need to review each and every
> "slow" query and insert query hints. Will there be a fix that "optimizes"
the
> query engine of sql server?
> CU,
> Sven
|||Hi Uri,
As you can see in my post, the query including the hint performs at least 60
times better than without it (without > 1 minute, with hint less than a
second).
With the clustered index ... well 80 million rows are currently clustered by
ID, resorting them to sort by another column may take a while ... may be next
year.
My "problem" is that I cannot belive that a query optimizer like the one of
sql server does not recognize that the "where" statement reduces the amount
of data to be processed from 80 million to 3000 (table statistics are up to
date), because this analysis is not really compex:
1) the "where" does contain an identity field, that is equal to the
clustered PK
2) the condition in the where stament eleminates > 90% of the data to be
processed
This must be a bug, so I assume this to be removed in the next service pack.
CU,
Sven
"Uri Dimant" wrote:

> Matzen
> Yes, SQL Server needs to do some extra work when you specify an aggregate
> functions.
> Look, you may want to use INDEX hint to dictate the query optimizer to use
> PK,but generally speaking it's not a good idea because the Optimizer is
> smart enough to create much more efficient execution plan.
> Also,you may create a clustered index on Time column and see how it does the
> work.
>
>
>
>
> "Matzen" <Matzen@.discussions.microsoft.com> wrote in message
> news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> rows
> clustered
> for
> me
> time,
> like
> processor
> need
> the
>
>
|||Matzen wrote:
> Hi Uri,
> As you can see in my post, the query including the hint performs at
> least 60 times better than without it (without > 1 minute, with hint
> less than a second).
> With the clustered index ... well 80 million rows are currently
> clustered by ID, resorting them to sort by another column may take a
> while ... may be next year.
> My "problem" is that I cannot belive that a query optimizer like the
> one of sql server does not recognize that the "where" statement
> reduces the amount of data to be processed from 80 million to 3000
> (table statistics are up to date), because this analysis is not
> really compex: 1) the "where" does contain an identity field, that is
> equal to the clustered PK
> 2) the condition in the where stament eleminates > 90% of the data to
> be processed
> This must be a bug, so I assume this to be removed in the next
> service pack.
> CU,
> Sven
>
The problem you are seeing is not really a bug, but a known issue with
the query optimizer. Many times SQL Server decides that based on the
number of rows likely to be returned from a query that using an index
(index seek + bookmark lookup for a non-clustered index or just a
clustered index seek for a clustered index) is actually more work than
scanning the table.
It's my understanding that SQL Server fails over to a table scan /
clustered index scan operation too soon in some cases. Your case just
may be one of those. I would keep the index hint and just keep an eye on
the query.
David Gugick
Imceda Software
www.imceda.com

Aggregation Queries with > 80.000.000 rows

Hi,
We have a logging database with a table "Logs" that hold 80.000.000 log rows
(about 1 year of application logs). We need to analyze this data in for
variable time periods, so we cannot simply split the table. The table
contains a column "LogID" (Identity INT and the only member of the clustered
PK) and a column "Time" (datetime there exists an index for this column).
The query
SELECT *
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes 2 seconds and returns about 3000 rows.
The query
SELECT MAX(Time), MIN(Time)
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes more than a minute (I've cancelled the query after a minute).
How can this be? The query analyzer shows me a really simple query plan for
the first query using the PK. For the second query, the query plan shows me
no usage of the PK, but usage of the index for the column "Time" (two time,
one for min and one for max).
Even more strange is this query:
SELECT MAX(Logs1.Time), MIN(Logs1.Time)
FROM Logs Logs1 WITH (nolock)
INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
WHERE Logs1.LogID > 110385284
the query finishes in less than a second, and is using the PK.
Also specifying the PK as an index hint solves the problem, but I don't like
to specify query hints, because this may prevent an updated query processor
to use a better query plan.
Another problem is that we also have less expirienced programmers that need
to deal with such tables, so at the moment I need to review each and every
"slow" query and insert query hints. Will there be a fix that "optimizes" the
query engine of sql server?
CU,
SvenMatzen
Yes, SQL Server needs to do some extra work when you specify an aggregate
functions.
Look, you may want to use INDEX hint to dictate the query optimizer to use
PK,but generally speaking it's not a good idea because the Optimizer is
smart enough to create much more efficient execution plan.
Also,you may create a clustered index on Time column and see how it does the
work.
"Matzen" <Matzen@.discussions.microsoft.com> wrote in message
news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> Hi,
> We have a logging database with a table "Logs" that hold 80.000.000 log
rows
> (about 1 year of application logs). We need to analyze this data in for
> variable time periods, so we cannot simply split the table. The table
> contains a column "LogID" (Identity INT and the only member of the
clustered
> PK) and a column "Time" (datetime there exists an index for this column).
> The query
> SELECT *
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes 2 seconds and returns about 3000 rows.
> The query
> SELECT MAX(Time), MIN(Time)
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes more than a minute (I've cancelled the query after a minute).
> How can this be? The query analyzer shows me a really simple query plan
for
> the first query using the PK. For the second query, the query plan shows
me
> no usage of the PK, but usage of the index for the column "Time" (two
time,
> one for min and one for max).
> Even more strange is this query:
> SELECT MAX(Logs1.Time), MIN(Logs1.Time)
> FROM Logs Logs1 WITH (nolock)
> INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
> WHERE Logs1.LogID > 110385284
> the query finishes in less than a second, and is using the PK.
> Also specifying the PK as an index hint solves the problem, but I don't
like
> to specify query hints, because this may prevent an updated query
processor
> to use a better query plan.
> Another problem is that we also have less expirienced programmers that
need
> to deal with such tables, so at the moment I need to review each and every
> "slow" query and insert query hints. Will there be a fix that "optimizes"
the
> query engine of sql server?
> CU,
> Sven|||Hi Uri,
As you can see in my post, the query including the hint performs at least 60
times better than without it (without > 1 minute, with hint less than a
second).
With the clustered index ... well 80 million rows are currently clustered by
ID, resorting them to sort by another column may take a while ... may be next
year.
My "problem" is that I cannot belive that a query optimizer like the one of
sql server does not recognize that the "where" statement reduces the amount
of data to be processed from 80 million to 3000 (table statistics are up to
date), because this analysis is not really compex:
1) the "where" does contain an identity field, that is equal to the
clustered PK
2) the condition in the where stament eleminates > 90% of the data to be
processed
This must be a bug, so I assume this to be removed in the next service pack.
CU,
Sven
"Uri Dimant" wrote:
> Matzen
> Yes, SQL Server needs to do some extra work when you specify an aggregate
> functions.
> Look, you may want to use INDEX hint to dictate the query optimizer to use
> PK,but generally speaking it's not a good idea because the Optimizer is
> smart enough to create much more efficient execution plan.
> Also,you may create a clustered index on Time column and see how it does the
> work.
>
>
>
>
> "Matzen" <Matzen@.discussions.microsoft.com> wrote in message
> news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> > Hi,
> >
> > We have a logging database with a table "Logs" that hold 80.000.000 log
> rows
> > (about 1 year of application logs). We need to analyze this data in for
> > variable time periods, so we cannot simply split the table. The table
> > contains a column "LogID" (Identity INT and the only member of the
> clustered
> > PK) and a column "Time" (datetime there exists an index for this column).
> >
> > The query
> > SELECT *
> > FROM Logs WITH (nolock)
> > WHERE LogID > 110385284
> > takes 2 seconds and returns about 3000 rows.
> >
> > The query
> > SELECT MAX(Time), MIN(Time)
> > FROM Logs WITH (nolock)
> > WHERE LogID > 110385284
> > takes more than a minute (I've cancelled the query after a minute).
> >
> > How can this be? The query analyzer shows me a really simple query plan
> for
> > the first query using the PK. For the second query, the query plan shows
> me
> > no usage of the PK, but usage of the index for the column "Time" (two
> time,
> > one for min and one for max).
> > Even more strange is this query:
> > SELECT MAX(Logs1.Time), MIN(Logs1.Time)
> > FROM Logs Logs1 WITH (nolock)
> > INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
> > WHERE Logs1.LogID > 110385284
> > the query finishes in less than a second, and is using the PK.
> >
> > Also specifying the PK as an index hint solves the problem, but I don't
> like
> > to specify query hints, because this may prevent an updated query
> processor
> > to use a better query plan.
> >
> > Another problem is that we also have less expirienced programmers that
> need
> > to deal with such tables, so at the moment I need to review each and every
> > "slow" query and insert query hints. Will there be a fix that "optimizes"
> the
> > query engine of sql server?
> >
> > CU,
> > Sven
>
>|||Matzen wrote:
> Hi Uri,
> As you can see in my post, the query including the hint performs at
> least 60 times better than without it (without > 1 minute, with hint
> less than a second).
> With the clustered index ... well 80 million rows are currently
> clustered by ID, resorting them to sort by another column may take a
> while ... may be next year.
> My "problem" is that I cannot belive that a query optimizer like the
> one of sql server does not recognize that the "where" statement
> reduces the amount of data to be processed from 80 million to 3000
> (table statistics are up to date), because this analysis is not
> really compex: 1) the "where" does contain an identity field, that is
> equal to the clustered PK
> 2) the condition in the where stament eleminates > 90% of the data to
> be processed
> This must be a bug, so I assume this to be removed in the next
> service pack.
> CU,
> Sven
>
The problem you are seeing is not really a bug, but a known issue with
the query optimizer. Many times SQL Server decides that based on the
number of rows likely to be returned from a query that using an index
(index seek + bookmark lookup for a non-clustered index or just a
clustered index seek for a clustered index) is actually more work than
scanning the table.
It's my understanding that SQL Server fails over to a table scan /
clustered index scan operation too soon in some cases. Your case just
may be one of those. I would keep the index hint and just keep an eye on
the query.
David Gugick
Imceda Software
www.imceda.com

Aggregation Queries with > 80.000.000 rows

Hi,
We have a logging database with a table "Logs" that hold 80.000.000 log rows
(about 1 year of application logs). We need to analyze this data in for
variable time periods, so we cannot simply split the table. The table
contains a column "LogID" (Identity INT and the only member of the clustered
PK) and a column "Time" (datetime there exists an index for this column).
The query
SELECT *
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes 2 seconds and returns about 3000 rows.
The query
SELECT MAX(Time), MIN(Time)
FROM Logs WITH (nolock)
WHERE LogID > 110385284
takes more than a minute (I've cancelled the query after a minute).
How can this be? The query analyzer shows me a really simple query plan for
the first query using the PK. For the second query, the query plan shows me
no usage of the PK, but usage of the index for the column "Time" (two time,
one for min and one for max).
Even more strange is this query:
SELECT MAX(Logs1.Time), MIN(Logs1.Time)
FROM Logs Logs1 WITH (nolock)
INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
WHERE Logs1.LogID > 110385284
the query finishes in less than a second, and is using the PK.
Also specifying the PK as an index hint solves the problem, but I don't like
to specify query hints, because this may prevent an updated query processor
to use a better query plan.
Another problem is that we also have less expirienced programmers that need
to deal with such tables, so at the moment I need to review each and every
"slow" query and insert query hints. Will there be a fix that "optimizes" th
e
query engine of sql server?
CU,
SvenMatzen
Yes, SQL Server needs to do some extra work when you specify an aggregate
functions.
Look, you may want to use INDEX hint to dictate the query optimizer to use
PK,but generally speaking it's not a good idea because the Optimizer is
smart enough to create much more efficient execution plan.
Also,you may create a clustered index on Time column and see how it does the
work.
"Matzen" <Matzen@.discussions.microsoft.com> wrote in message
news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> Hi,
> We have a logging database with a table "Logs" that hold 80.000.000 log
rows
> (about 1 year of application logs). We need to analyze this data in for
> variable time periods, so we cannot simply split the table. The table
> contains a column "LogID" (Identity INT and the only member of the
clustered
> PK) and a column "Time" (datetime there exists an index for this column).
> The query
> SELECT *
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes 2 seconds and returns about 3000 rows.
> The query
> SELECT MAX(Time), MIN(Time)
> FROM Logs WITH (nolock)
> WHERE LogID > 110385284
> takes more than a minute (I've cancelled the query after a minute).
> How can this be? The query analyzer shows me a really simple query plan
for
> the first query using the PK. For the second query, the query plan shows
me
> no usage of the PK, but usage of the index for the column "Time" (two
time,
> one for min and one for max).
> Even more strange is this query:
> SELECT MAX(Logs1.Time), MIN(Logs1.Time)
> FROM Logs Logs1 WITH (nolock)
> INNER JOIN Logs Logs2 WITH (nolock) ON Logs1.LogID = Logs2.LogID
> WHERE Logs1.LogID > 110385284
> the query finishes in less than a second, and is using the PK.
> Also specifying the PK as an index hint solves the problem, but I don't
like
> to specify query hints, because this may prevent an updated query
processor
> to use a better query plan.
> Another problem is that we also have less expirienced programmers that
need
> to deal with such tables, so at the moment I need to review each and every
> "slow" query and insert query hints. Will there be a fix that "optimizes"
the
> query engine of sql server?
> CU,
> Sven|||Hi Uri,
As you can see in my post, the query including the hint performs at least 60
times better than without it (without > 1 minute, with hint less than a
second).
With the clustered index ... well 80 million rows are currently clustered by
ID, resorting them to sort by another column may take a while ... may be nex
t
year.
My "problem" is that I cannot belive that a query optimizer like the one of
sql server does not recognize that the "where" statement reduces the amount
of data to be processed from 80 million to 3000 (table statistics are up to
date), because this analysis is not really compex:
1) the "where" does contain an identity field, that is equal to the
clustered PK
2) the condition in the where stament eleminates > 90% of the data to be
processed
This must be a bug, so I assume this to be removed in the next service pack.
CU,
Sven
"Uri Dimant" wrote:

> Matzen
> Yes, SQL Server needs to do some extra work when you specify an aggregate
> functions.
> Look, you may want to use INDEX hint to dictate the query optimizer to use
> PK,but generally speaking it's not a good idea because the Optimizer is
> smart enough to create much more efficient execution plan.
> Also,you may create a clustered index on Time column and see how it does t
he
> work.
>
>
>
>
> "Matzen" <Matzen@.discussions.microsoft.com> wrote in message
> news:ADAE2C5C-C301-4F72-966C-C6AB4EAB9FF7@.microsoft.com...
> rows
> clustered
> for
> me
> time,
> like
> processor
> need
> the
>
>|||Matzen wrote:
> Hi Uri,
> As you can see in my post, the query including the hint performs at
> least 60 times better than without it (without > 1 minute, with hint
> less than a second).
> With the clustered index ... well 80 million rows are currently
> clustered by ID, resorting them to sort by another column may take a
> while ... may be next year.
> My "problem" is that I cannot belive that a query optimizer like the
> one of sql server does not recognize that the "where" statement
> reduces the amount of data to be processed from 80 million to 3000
> (table statistics are up to date), because this analysis is not
> really compex: 1) the "where" does contain an identity field, that is
> equal to the clustered PK
> 2) the condition in the where stament eleminates > 90% of the data to
> be processed
> This must be a bug, so I assume this to be removed in the next
> service pack.
> CU,
> Sven
>
The problem you are seeing is not really a bug, but a known issue with
the query optimizer. Many times SQL Server decides that based on the
number of rows likely to be returned from a query that using an index
(index seek + bookmark lookup for a non-clustered index or just a
clustered index seek for a clustered index) is actually more work than
scanning the table.
It's my understanding that SQL Server fails over to a table scan /
clustered index scan operation too soon in some cases. Your case just
may be one of those. I would keep the index hint and just keep an eye on
the query.
David Gugick
Imceda Software
www.imceda.com