configure IIS in SQLXML support

Hi,
I have to create a B2B application in c#.Net and use SQLXML and use
Annotated schemas to get output as XML.
I have no background in any of these. First of all i have to set up
the environment. So I installed win XP pro ,SQL Server 2000 and tried
to configure IIS in SQLXML but when I give the physical path to the
virtual directory say Northwind, as C:\Inetpub\Wwwroot\Northwind,
it says the physical path is not valid. what is the problem i am
having? What should i do? when I navigate in browse and look for the
folders Inetpub and Wwwroot I see nothing there other than some
picture Icons. Can someone help me please...
thanks,
-shubha
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/XML-configur...ict235374.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=816596
Did you create the Northwind directory under wwwroot?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"vanilla" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_816596_2a8025c990fe19afdddaea35d5531d23@.dbf orumz.com...
> Hi,
> I have to create a B2B application in c#.Net and use SQLXML and use
> Annotated schemas to get output as XML.
> I have no background in any of these. First of all i have to set up
> the environment. So I installed win XP pro ,SQL Server 2000 and tried
> to configure IIS in SQLXML but when I give the physical path to the
> virtual directory say Northwind, as C:\Inetpub\Wwwroot\Northwind,
> it says the physical path is not valid. what is the problem i am
> having? What should i do? when I navigate in browse and look for the
> folders Inetpub and Wwwroot I see nothing there other than some
> picture Icons. Can someone help me please...
> thanks,
> -shubha
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/XML-configur...ict235374.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=816596
sqlsql

Configure "Remote Connections" via Group Policy?

Is it possible to create a group policy that sets the "remote connections"
property that exists in the SQL Server Surface Area Configuration applet?
How? :-)
Thanks,
JoeNo. This is also something you do NOT want to do. If you could create
this, it would mean that every single SQL Server instance, including the
various SQL Server Express instances installed with thousands of different
applications would automatically be able to receive inbound connections from
remote clients. This was a very specific security issue that was addressed
in this version. All you have to do is to look up the Slammer worm and
you'll understand why you only want those instances which need to service
client connections able to do so.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Joe" <jwdaigle@.nospam.nospam> wrote in message
news:Ow0cVf8KGHA.3396@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create a group policy that sets the "remote connections"
> property that exists in the SQL Server Surface Area Configuration applet?
> How? :-)
> Thanks,
> Joe
>|||And if I wanted to ensure that it was never set to allow remote connections?
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:uGopFv$KGHA.3264@.TK2MSFTNGP11.phx.gbl...
> No. This is also something you do NOT want to do. If you could create
> this, it would mean that every single SQL Server instance, including the
> various SQL Server Express instances installed with thousands of different
> applications would automatically be able to receive inbound connections
> from remote clients. This was a very specific security issue that was
> addressed in this version. All you have to do is to look up the Slammer
> worm and you'll understand why you only want those instances which need to
> service client connections able to do so.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Joe" <jwdaigle@.nospam.nospam> wrote in message
> news:Ow0cVf8KGHA.3396@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi Joe,
Welcome to use MSDN Managed Newsgroup Support. And thank Michael's great
help!
As Michael stated, it does not mean that never set to allow remote
connection. You can allow remote connection for those instance you want to.
By default, only Enterprise, Standard and Workgroup editions allow remote
client connection.
If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||So then it is not possible to ensure that "allow remote client connections"
is disabled via group policy? Thats too bad, because it requires me to
manually check systems for compliance.
Ok, thank you both for your help.
Joe
"Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
news:Ap4y3JHLGHA.3052@.TK2MSFTNGXA01.phx.gbl...
> Hi Joe,
> Welcome to use MSDN Managed Newsgroup Support. And thank Michael's great
> help!
> As Michael stated, it does not mean that never set to allow remote
> connection. You can allow remote connection for those instance you want
> to.
> By default, only Enterprise, Standard and Workgroup editions allow remote
> client connection.
> If you have any questions or concerns, don't hesitate to let me know. We
> are here to be of assistance!
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||You are welcome, Joe.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Configuration file for reporting server project

Hi!
Is it somehow possible to create a configuration file for a reporting server
project. Something similar like the web.config or app.config file in .NET
applications?
What I wanna do is to store the link of a picture file. I don't want to
hard-code that in every report.
Are there any other options than storing this link in a config file?
Cheers, RogerOn Jul 17, 8:37 am, "Roger Frei" <s...@.hotmail.com> wrote:
> Hi!
> Is it somehow possible to create a configuration file for a reporting server
> project. Something similar like the web.config or app.config file in .NET
> applications?
> What I wanna do is to store the link of a picture file. I don't want to
> hard-code that in every report.
> Are there any other options than storing this link in a config file?
> Cheers, Roger
What you could try doing is include a link to a picture file in a
report and then check the XML structure of the underlying RDL file in
a text editor (Notepad, Wordpad, etc). Then change the path in the RDL
file via a custom application that creates the RDL file on the fly.
This might be a bit tedious but it may be the only option.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Jul 17, 2:37 pm, "Roger Frei" <s...@.hotmail.com> wrote:
> Hi!
> Is it somehow possible to create a configuration file for a reporting server
> project. Something similar like the web.config or app.config file in .NET
> applications?
> What I wanna do is to store the link of a picture file. I don't want to
> hard-code that in every report.
> Are there any other options than storing this link in a config file?
> Cheers, Roger
Roger,
Add the picture file to your report project - deploy and hide - no ?

Config and Connection String

I want to store my connection string in a configuration file, however when I create the config file the connection string password is not stored. I can manually edit my config file and add the password to my connection string but I am sure that BIDS has a better solution. What is the best practice for storing connection string passwords in my SSIS Configuration files?

That behavior is by design; BIDS will not store the password for you; you have to add it manually. Hopefully you will not need to do it very often

|||

dgrm44 wrote:

I want to store my connection string in a configuration file, however when I create the config file the connection string password is not stored. I can manually edit my config file and add the password to my connection string but I am sure that BIDS has a better solution. What is the best practice for storing connection string passwords in my SSIS Configuration files?

Nope. You've got it figured out. BIDS doesn't store passwords.|||

The other guys are quite right but it may help to explain why.

As you may or may not be aware, a few years ago Microsoft introduced a programme called the 'Trustworthy Computing Initiative'. An effort to try and make their products more secure. This is one of the main reasons it took them so long to get Vista out of the door.

Anyway, you can see the effect of this here. If you are going to store passwords in a text file (which is inherently a security risk) then Microsoft want YOU to be responsible for doing that - they will not do it for you.

-Jamie

|||

The what is the best practice for storing my ssis connection string passwords if not in the config file?

|||

dgrm44 wrote:

The what is the best practice for storing my ssis connection string passwords if not in the config file?

That is the best practice. You have to do it manually as we have said. It's just that BIDS will not store it for you; you have to add it yourself. BIDS will do everything else for you in the config file, just not the password (technically any sensitive information)|||

Phil Brammer wrote:

dgrm44 wrote:

The what is the best practice for storing my ssis connection string passwords if not in the config file?

That is the best practice. You have to do it manually as we have said. It's just that BIDS will not store it for you; you have to add it yourself. BIDS will do everything else for you in the config file, just not the password (technically any sensitive information)

The OP raises a good point though. How can this be best practice if it is a security violation? The answer is that, until SSIS provides encrypted config files, you have to restrict access to the config file. This can be done using familiar operating system folder permissions.

-Jamie

|||

Jamie Thomson wrote:

The OP raises a good point though. How can this be best practice if it is a security violation? The answer is that, until SSIS provides encrypted config files, you have to restrict access to the config file. This can be done using familiar operating system folder permissions.

-Jamie

Well at present, it is the best practice as it's really the only practice that works, correct? |||YES! That was my point. If Microsoft is saying this is security violation then they must have another method in mind? What about the protection level of EncryptSensitiveWithPassword? Will this store the password in the package as an encrypted value? Within the config wizard when you select the properties that you want stored in the config file there is a value called password. What is this property?|||

dgrm44 wrote:

YES! That was my point. If Microsoft is saying this is security violation then they must have another method in mind? What about the protection level of EncryptSensitiveWithPassword? Will this store the password in the package as an encrypted value? Within the config wizard when you select the properties that you want stored in the config file there is a value called password. What is this property?

You enter the package password into that property. Then all sensitive data (passwords!) are encrypted with that password entered, using EncryptSensitiveWithPassword. Then, to open/execute the package, that password will be required to be passed into DTEXEC upon execution in order to decrypt the sensitive information. This is a good alternative.|||

More info here:

Storing Passwords

(http://blogs.conchango.com/jamiethomson/archive/2007/04/26/SSIS_3A00_-Storing-passwords.aspx)

-Jamie

confident of data mining

in associate rule, there's confident and support or probabilty values. but i use sql 2000, no support associate rule so i create a model with Microsoft _decision _tree to predict other products when customer choose product in superMarket

basket_id

table...

{

productid

}

is it have confident or support values. what value of it? how can i change the minimum_support and minimum_probabilty ? it can use in Microsoft _ decision tree.

Hi

In the decision trees model, you could filter the prediction results by probability and obtain pretty much the same effect as in association rules.

To some extent, I think the minimum support from association rules can be modeled with the MINIMUM_LEAF_CASES parameter of the decision trees algorithm.

The difference comes from the "greedy" behavior of DT.

Assuming, in Association Rules, a minimum support of 10 and exactly 11 transaction including milk, beer and chips, a rule could be found like below:

"MILK + BEER => CHIPS"

Decision trees with a MINIMUM_LEAF_CASES of 10 will pick the most significant attribute ( in this case, either one of MILK and BEER, say MILK) and split on that attribute. After the split, there are not necessarily sufficient cases to support a new split on the second attribute (BEER), so the rule may be simpler:

IF (MILK) then CHIPS

Hope this helps

|||thanks, but i want ask default value of support value? what's it? and how i can modeled the MINIMUM_LEAF_CASES in decision trees algorithm ( i use interface not write code)|||I believe the default is 10. You can set parameters using the advanced properties of the mining model in the designer. Alternatively, I wrote (a long time ago) an addin for Analysis Manager that provides a UI for setting parameters in SQL 2000 data mining similar to that in SQL 2005. You can still get this addin here: http://www.sqlserverdatamining.com/DMCommunity/SQLServer2000/Links_LinkRedirector.aspx?id=97|||

thanks. i was try in advanced properties but fields is grey so i cant's modify.

10 is support. how about confident? has it?

i use link to get an addin you said, but it error.

so i haven't try it now.

|||

This is the direct link to the file mentioned by Jamie.http://www.sqlserverdatamining.com/DMCommunity/_Downloads/Downloads_GetFile.aspx?id=92

Note that it still requires creating an account on the sqlserverdatamining.com web site.

For confidence, you should filter the query results rather than looking for an algorithm property.

|||

i had download AddIn at http://www.sqlserverdatamining.com/DMCommunity/SQLServer2000/Links_LinkRedirector.aspx?id=97

and read document that requid you must regist file dll and registry. after, right click on model dataming you want to modify param and choose menu to modify it. But i can't do. how can i registry. i tryed double click on it ( file regist and dll, copy them to root folder) but nothing work, so i still can't modify param. how?

more: confider, you say me filter in query. how can i do that? can you give me an example query

|||

Open a command prompt, cd to the directory where the dll is and type

regsvr32 <dllname>

The .reg file should register itself when you double-click it.

confident of data mining

in associate rule, there's confident and support or probabilty values. but i use sql 2000, no support associate rule so i create a model with Microsoft _decision _tree to predict other products when customer choose product in superMarket

basket_id

table...

{

productid

}

is it have confident or support values. what value of it? how can i change the minimum_support and minimum_probabilty ? it can use in Microsoft _ decision tree.

Hi

In the decision trees model, you could filter the prediction results by probability and obtain pretty much the same effect as in association rules.

To some extent, I think the minimum support from association rules can be modeled with the MINIMUM_LEAF_CASES parameter of the decision trees algorithm.

The difference comes from the "greedy" behavior of DT.

Assuming, in Association Rules, a minimum support of 10 and exactly 11 transaction including milk, beer and chips, a rule could be found like below:

"MILK + BEER => CHIPS"

Decision trees with a MINIMUM_LEAF_CASES of 10 will pick the most significant attribute ( in this case, either one of MILK and BEER, say MILK) and split on that attribute. After the split, there are not necessarily sufficient cases to support a new split on the second attribute (BEER), so the rule may be simpler:

IF (MILK) then CHIPS

Hope this helps

|||thanks, but i want ask default value of support value? what's it? and how i can modeled the MINIMUM_LEAF_CASES in decision trees algorithm ( i use interface not write code)|||I believe the default is 10. You can set parameters using the advanced properties of the mining model in the designer. Alternatively, I wrote (a long time ago) an addin for Analysis Manager that provides a UI for setting parameters in SQL 2000 data mining similar to that in SQL 2005. You can still get this addin here: http://www.sqlserverdatamining.com/DMCommunity/SQLServer2000/Links_LinkRedirector.aspx?id=97|||

thanks. i was try in advanced properties but fields is grey so i cant's modify.

10 is support. how about confident? has it?

i use link to get an addin you said, but it error.

so i haven't try it now.

|||

This is the direct link to the file mentioned by Jamie.http://www.sqlserverdatamining.com/DMCommunity/_Downloads/Downloads_GetFile.aspx?id=92

Note that it still requires creating an account on the sqlserverdatamining.com web site.

For confidence, you should filter the query results rather than looking for an algorithm property.

|||

i had download AddIn at http://www.sqlserverdatamining.com/DMCommunity/SQLServer2000/Links_LinkRedirector.aspx?id=97

and read document that requid you must regist file dll and registry. after, right click on model dataming you want to modify param and choose menu to modify it. But i can't do. how can i registry. i tryed double click on it ( file regist and dll, copy them to root folder) but nothing work, so i still can't modify param. how?

more: confider, you say me filter in query. how can i do that? can you give me an example query

|||

Open a command prompt, cd to the directory where the dll is and type

regsvr32 <dllname>

The .reg file should register itself when you double-click it.

sqlsql

Conditionally CREATE a VIEW in a script

Hi,

I would like to create a view depending on a condition check first. However, I do not seem to the able to put a 'CREATE VIEW' within an IF statement. The following example demonstates what I am trying to achieve (please excuse the triviality of the example):

IF NOT col_length('authors','city') IS NULL

BEGIN
CREATE VIEW TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address + ', ' + city) as fullAddress
FROM authors
END
ELSE
BEGIN
CREATE VIEW TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address) as fullAddress
FROM authors
END

When I try to parse/run this I get the following syntax error:

"Incorrect syntax near the keyword 'VIEW'."

Any help would be much appreciated.

Thanks.

Try the code below.

Chris

Code Snippet

DECLARE @.sqlstring NVARCHAR(4000)

IF NOT col_length('authors', 'city') IS NULL
BEGIN
SET @.sqlstring = '
CREATE VIEW TestView
AS
SELECT (au_fname + '' '' + au_lname) as fullName, (address + '', '' + city) as fullAddress
FROM authors'
EXEC (@.sqlstring)
END
ELSE
BEGIN
SET @.sqlstring = '
CREATE VIEW TestView
AS
SELECT (au_fname + '' '' + au_lname) as fullName, (address) as fullAddress
FROM authors'
EXEC (@.sqlstring)
END

|||

I think this looks misguided. Rather than changing the view that is is created dynamically, I think you need to change the view permanently so that both views can be represented by a singular view that uses CASE construct. Hang on and if I don't get you an example, I imagine someone else will.

Maybe something like this:

create view testView
as

select au_fname + ' ' + au_lname
as fullName,
address
+ case when len(rtrim(city)) = 0
then ''
else ', ' + city
end
as address
from authors

go

select * from testView

/*
fullName address
--
Johnson White 10932 Bigge Rd., Menlo Park
Marjorie Green 309 63rd St. #411, Oakland
Cheryl Carson 589 Darwin Ln., Berkeley
*/

|||

Hi Chris,

I had thought about doing that but the real view is quite large and I was trying to avoid dealing with string manipulation but I suppose its just two single quotes for ant existing single quotes.

Thanks.

Smoc

|||

Hi Kent,

Thanks for the response but that will not work if the column does not exist in the table which is the reason I want to conditionally create 1 of 2 possible views. In the simplistic example, I want to handle the situation when the column 'city' may not be in the authors table.

I realise that i could use the col_length function instead to achieve the result you have proposed. I was just wondering why I could have two 'clean' view definitions in a script contained within an IF statement.

Regards,

Smoc

|||

Just thinking out loud really, but could you programatically add the City column to the authors table if the column doesn't exist? That way, going forward, you'd only have one version of the View to maintain.

Chris

|||

Hi Chris,

We have an application that is using a database that we have no control over and no authority to change. We have discovered some differences between schemas of different clients who have this database. The differences are not critical and we hope to handle it at the view level. Other than that we would do as you suggested.

I'm just supprised that I can do a DROP command but not a Create View command in an IF statement.

Smoc

|||

You can't create view/procedure/function/trigger inside or mid of your batch.

These create scripts should be the first line of the batch.

In IF batch you can put only the Drop view/procedure/function/trigger.

The only possible way is using dynmaic sql.

|||

Thanks for the clarification.

I will probably use the dynamic sql that you have suggested and as was also suggested in an earlier thread.

Thanks.

|||There is a neat trick to achieve just what you want . Check out this example:

-- If column doesn't exists, does not create the view that use it
IF col_length('authors','city') IS NULL set noexec on
go
CREATE VIEW dbo.TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address + ', ' + city) as fullAddress
FROM authors
go
-- Return execute mode to default
set noexec off
go
-- If column exists, does not create the view without it
IF col_length('authors','city') IS not NULL set noexec on
go
CREATE VIEW dbo.TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address) as fullAddress
FROM authors
go
-- Return execute mode to default
set noexec off

You only need to carefully choose your conditions because they have to be "reversed", in a way. Still, it is a proven and reliable approach.

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a store
d
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a stored
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional summaring

Hi all
I'm trying to create a summery table of rain data that holds a record
for every 0.1mm of rain in the following format:

Station Name, Time, Value
A, 2002-12-03 14:44:41.000, 0.1
A, 2002-12-03 14:45:45.000, 0.1
A, 2002-12-03 14:49:45.000, 0.1
A, 2002-12-09 05:30:35.000, 0.1
A, 2002-12-09 05:30:37.000, 0.1
A, 2002-12-09 05:33:05.000, 0.1
B, 2002-12-09 05:32:47.000, 0.1
B, 2002-12-09 05:33:00.000, 0.1
B, 2002-12-09 05:35:00.000, 0.1
B, 2002-12-09 05:37:48.000, 0.1
...
B, 2003-02-09 01:32:47.000, 0.1
B, 2003-02-09 05:32:47.000, 0.1

The problem is that I need to count rain events for each station and
produce the starting and ending time of the event (based on the Time
field) and to summarize the amount of rain that fell during the event.
An "event" for that matter is a case where there is a gap of at least
12 hours with no rain before and after a series of rain measurements
(i.e., a rain storm). The data in the table is written in such a way
that each row hold the time where an amount on 0.1mm of rain was
counted by the rain gauge.

For example, the above date should produce:

Station Name, Start Time, End Time, Total rain
A, 2002-12-03 14:44:41.000, 2002-12-03 14:49:45.000, 0.3
A, 2002-12-09 05:30:35.000, 2002-12-09 05:33:05.000, 0.3
B, 2002-12-09 05:32:47.000, 2002-12-09 05:37:48.000, 0.3
B, 2003-02-09 01:32:47.000, 2003-02-09 05:32:47.000, 0.2

As a newbie to SQL I don't know if it can be done.

Many thanks,
IlikOn 14 Feb 2005 08:00:24 -0800, Ilik wrote:

>Hi all
>I'm trying to create a summery table of rain data that holds a record
>for every 0.1mm of rain in the following format:
>Station Name, Time, Value
>A, 2002-12-03 14:44:41.000, 0.1
>A, 2002-12-03 14:45:45.000, 0.1
>A, 2002-12-03 14:49:45.000, 0.1
>A, 2002-12-09 05:30:35.000, 0.1
>A, 2002-12-09 05:30:37.000, 0.1
>A, 2002-12-09 05:33:05.000, 0.1
>B, 2002-12-09 05:32:47.000, 0.1
>B, 2002-12-09 05:33:00.000, 0.1
>B, 2002-12-09 05:35:00.000, 0.1
>B, 2002-12-09 05:37:48.000, 0.1
>...
>B, 2003-02-09 01:32:47.000, 0.1
>B, 2003-02-09 05:32:47.000, 0.1
>The problem is that I need to count rain events for each station and
>produce the starting and ending time of the event (based on the Time
>field) and to summarize the amount of rain that fell during the event.
>An "event" for that matter is a case where there is a gap of at least
>12 hours with no rain before and after a series of rain measurements
>(i.e., a rain storm). The data in the table is written in such a way
>that each row hold the time where an amount on 0.1mm of rain was
>counted by the rain gauge.
>For example, the above date should produce:
>Station Name, Start Time, End Time, Total rain
>A, 2002-12-03 14:44:41.000, 2002-12-03 14:49:45.000, 0.3
>A, 2002-12-09 05:30:35.000, 2002-12-09 05:33:05.000, 0.3
>B, 2002-12-09 05:32:47.000, 2002-12-09 05:37:48.000, 0.3
>B, 2003-02-09 01:32:47.000, 2003-02-09 05:32:47.000, 0.2
>As a newbie to SQL I don't know if it can be done.

Hi Ilik,

It can be done, but it's quite complicated. You have to use several
tricks, then combine them into one query.

Trick 1: Find the start of a rain period. Each row for which no other row
exists with a time value in the preceding 12 hours marks the start of a
rain period. You can find these using NOT EXISTS or using an OUTER JOIN.
SELECT Station
, Time
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.Station = a.Station
AND b.Time < a.Time
AND b.Time >= DATEADD(hour, -12, a.Time))
or
SELECT a.Station
, a.Time
FROM MyTable AS a
LEFT OUTER JOIN MyTable AS b
ON b.Station = a.Station
AND b.Time < a.Time
AND b.Time >= DATEADD(hour, -12, a.Time)
I'll use the NOT EXISTS version in the remainder of this message; if
performance is important for you, I'd advise you to test both versions.

Trick 2: Find the end of a rain period. This is basically the same
technique as trick 1.

Trick 3: Match up the start and end times of each rain period. For each
start, the matching end is the FIRST of all end's that occur AFTER that
start.
SELECT a.Station
, a.Time AS StartTime
, MIN(b.Time) AS EndTime
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Station = a.Station
AND b.Time > a.Time
WHERE NOT EXISTS (SELECT *
FROM MyTable AS c
WHERE c.Station = a.Station
AND c.Time < a.Time
AND c.Time >= DATEADD(hour, -12, a.Time))
AND NOT EXISTS (SELECT *
FROM MyTable AS d
WHERE d.Station = b.Station
AND d.Time > b.Time
AND d.Time <= DATEADD(hour, +12, b.Time))
GROUP BY a.Station
, a.Time

Trick 4: Find all rows between start and end time of a rain period. This
can be done by using the result of the previous step as a derived table
and joining that to the original table, but in this case, I decided to
extend the result of the previous step with one correlated subquery.
SELECT a.Station
, a.Time AS StartTime
, MIN(b.Time) AS EndTime
,(SELECT SUM(e.Value)
FROM MyTable AS e
WHERE e.Station = a.Station
AND e.Time >= a.Time
AND e.Time <= MIN(b.Time)) AS TotalRain
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Station = a.Station
AND b.Time > a.Time
WHERE NOT EXISTS (SELECT *
FROM MyTable AS c
WHERE c.Station = a.Station
AND c.Time < a.Time
AND c.Time >= DATEADD(hour, -12, a.Time))
AND NOT EXISTS (SELECT *
FROM MyTable AS d
WHERE d.Station = b.Station
AND d.Time > b.Time
AND d.Time <= DATEADD(hour, +12, b.Time))
GROUP BY a.Station
, a.Time

Note: all the queries above are untested. To get tested replies, you need
to post SQL that will recreate your tables and data on my system, as shown
in www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||[posted and mailed, please reply in news]

Ilik (levyi@.walla.co.il) writes:
> The problem is that I need to count rain events for each station and
> produce the starting and ending time of the event (based on the Time
> field) and to summarize the amount of rain that fell during the event.
> An "event" for that matter is a case where there is a gap of at least
> 12 hours with no rain before and after a series of rain measurements
> (i.e., a rain storm). The data in the table is written in such a way
> that each row hold the time where an amount on 0.1mm of rain was
> counted by the rain gauge.

By no means a trivial problem. As Hugo said, CREATE TABLE and INSERT
statements are alwyas a good thing. Then again, the table was easy
to compose in this case, as were the INSERT statement. And your
narrative and sample data, was very good. Thanks!

Here is a multi-step approach. Maybe not the most elegant, but it
gives the correct result. If data volumes are large, performance
could be an issue, though.

A particular note on the datediff exercise. I do datediff per minute,
not per hour. This is because datediff(HOUR, '00:15:00', '12:10:10')
is 12, although it's not fully 12 hours. datediff always counts
cross boundaries. Also, I found that coalesce needed special care.
Originally I had 19000101, and 99991231, but that gave me overflow.

CREATE TABLE data(station char(1) NOT NULL,
time datetime NOT NULL,
value float NOT NULL,
CONSTRAINT pk_data PRIMARY KEY (station, time))
go
INSERT data (station, time, value)
SELECT 'A', '20021203 14:44:41.000', 0.1 UNION
SELECT 'A', '20021203 14:45:45.000', 0.1 UNION
SELECT 'A', '20021203 14:49:45.000', 0.1 UNION
SELECT 'A', '20021209 05:30:35.000', 0.1 UNION
SELECT 'A', '20021209 05:30:37.000', 0.1 UNION
SELECT 'A', '20021209 05:33:05.000', 0.1 UNION
SELECT 'B', '20021209 05:32:47.000', 0.1 UNION
SELECT 'B', '20021209 05:33:00.000', 0.1 UNION
SELECT 'B', '20021209 05:35:00.000', 0.1 UNION
SELECT 'B', '20021209 05:37:48.000', 0.1 UNION
SELECT 'B', '20030209 01:32:47.000', 0.1 UNION
SELECT 'B', '20030209 05:32:47.000', 0.1 UNION
SELECT 'B', '20030209 18:32:47.000', 0.1 UNION
SELECT 'B', '20030212 05:32:47.000', 0.1
go
CREATE TABLE #temp (station char(1) NOT NULL,
time datetime NOT NULL,
n int NOT NULL,
starttime datetime NULL,
endtime datetime NULL,
value float NOT NULL,
PRIMARY KEY (station, n))
go
INSERT #temp (station, time, value, n)
SELECT a.station, a.time, a.value,
(SELECT COUNT(*) + 1 FROM data b
WHERE a.station = b.station AND a.time > b.time)
FROM data a
go
UPDATE b
SET starttime = CASE WHEN datediff(minute,
coalesce(a.time, dateadd(DAY, -1, b.time)),
b.time) > 60*12
THEN b.time
END,
endtime = CASE WHEN datediff(minute,
b.time,
coalesce(c.time, dateadd(DAY, 1, b.time))) > 60*12
THEN b.time
END
FROM #temp b
LEFT JOIN #temp a ON a.station = b.station AND b.n - 1 = a.n
LEFT JOIN #temp c ON b.station = c.station AND b.n + 1 = c.n
go
UPDATE #temp
SET starttime = (SELECT MAX(b.starttime)
FROM #temp b
WHERE b.station = a.station
AND b.starttime < a.time)
FROM #temp a
WHERE a.starttime IS NULL
go
SELECT station, starttime, MAX(endtime), SUM(value)
FROM #temp
GROUP BY station, starttime
go
DROP TABLE #temp
DROP TABLE data

--
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 Tue, 15 Feb 2005 21:28:13 +0000 (UTC), Erland Sommarskog wrote:

>By no means a trivial problem. As Hugo said, CREATE TABLE and INSERT
>statements are alwyas a good thing. Then again, the table was easy
>to compose in this case, as were the INSERT statement.

Hi Erland,

You're absolutely right. I guess this just shows that I'm lazy ;-)

I just stole your work to test my query and found one small error. The
corrected version (adapted to the table and column names you've chosen) is

SELECT a.station
, a.time AS Starttime
, MIN(b.time) AS Endtime
,(SELECT SUM(e.value)
FROM data AS e
WHERE e.station = a.station
AND e.time >= a.time
AND e.time <= MIN(b.time)) AS TotalRain
FROM data AS a
INNER JOIN data AS b
ON b.station = a.station
AND b.time >= a.time-- The change is in this line
WHERE NOT EXISTS (SELECT *
FROM data AS c
WHERE c.station = a.station
AND c.time < a.time
AND c.time >= DATEADD(hour, -12, a.time))
AND NOT EXISTS (SELECT *
FROM data AS d
WHERE d.station = b.station
AND d.time > b.time
AND d.time <= DATEADD(hour, +12, b.time))
GROUP BY a.station
, a.time

> And your
>narrative and sample data, was very good. Thanks!

I can only second that.

>Here is a multi-step approach. Maybe not the most elegant, but it
>gives the correct result. If data volumes are large, performance
>could be an issue, though.

I ran some quick tests for a first impression. Based on the difference
from start to end time for both queries, using the sample data provided,
it was too close to call. Increasing the amount of test data (copying the
insert two more times and changing the station names to C, D, E and F)
resulted in a small advantage for my query, but with so little test data,
that is not really saying anything.

With set statistics io on, my query used

Table 'data'. Scan count 151, logical reads 302, physical reads 0,
read-ahead reads 0.

And your code used

Table '#temp__(snip)'. Scan count 0, logical reads 86, physical reads 0,
read-ahead reads 0.
Table 'data'. Scan count 43, logical reads 86, physical reads 0,
read-ahead reads 0.

Table '#temp__(snip)'. Scan count 85, logical reads 254, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 44, physical reads 0,
read-ahead reads 0.

Table '#temp__(snip)'. Scan count 25, logical reads 98, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 26, physical reads 0,
read-ahead reads 0.

Table '#temp__(snip)'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0.

Adding up these numbers shows that your version appears to do quite some
more work than my version.

I'd be interested to hear from Ilik how the versions compare on his
system, with real data.

>A particular note on the datediff exercise. I do datediff per minute,
>not per hour. This is because datediff(HOUR, '00:15:00', '12:10:10')
>is 12, although it's not fully 12 hours. datediff always counts
>cross boundaries. Also, I found that coalesce needed special care.
>Originally I had 19000101, and 99991231, but that gave me overflow.

Why didn't you use DATEADD(hour, 12, a.time) > b.time, then? Wouldn't that
have solved all these issues?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have to walk two dogs and install a Pergo floor this morning, so I
cannot work out the code in detail.

Can I assume that the measurements are collected by device that is
giving you a collection time? This means the data comes over time, and
it is a few microseconds behind the current time.

Instead of modeling the data collection form, model the final results:

CREATE TABLE Rainfall
(station_name CHAR(5) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
rain_accumulation DECIMAL (5,2) DEFAULT 0.00 NOT NULL,
CHECK (start_time < end_time),
PRIMARY KEY (station_name, start_time));

When a new reading comes in:

1) If it is within 12 hours of the start_time of the current row (i.e.
MAX(end_time) , then add 0.1 to the rain_accumulation and the end_time
CURRENT_TIMESTAMP in an UPDATE.

2) If it is more than 12 hours after the start_time of the current row,
then

2.1) update end_time to CURRENT_TIMESTAMP to close out the current
period.
2.2) insert new row with the new start_time, etc.

Something like this in pseudo-code.

CREATE PROCEDURE PostNewReading(@.my_station_name, @.new_reading_time)
AS
BEGIN
UPDATE Rainfall
SET end_time
= CASE WHEN <<less than 12 hours
FROM most recent>>
THEN @.new_reading_time
ELSE end_time END,
rain_accumulation
= CASE WHEN <<less than 12 hours
FROM most recent>>
THEN rain_accumulation + 0.1
ELSE rain_accumulation END;

INSERT INTO Rainfall
SELECT @.my_station_name, @.reading_time,
CURRENT_TIMESTAMP, 0.1
FROM Rainfall
WHERE <<reading more than 12 hours
FROM most recent>>;
END;

The idea is to put the procedural logic into CASE expressions in an
UPDATE and an INSERT statement. Use ELSE clauses to leave data
unchanged. No proprietary T-SQL control of flow stuff at all. No
tricky self-joins or cursors.|||Hi all
First, my sincere apologies for not replying earlier, but I was busy
over my head in the last couple of days with other projects.
In reply to CELKO, you're assuming that the data is being collected at
real time, but this is not the case. The data goes back a few decades
in some cases (starting at 1965), so I cannot evaluate it on entry.

As for Erland's solution, I run it only for a few minutes because of
his remark that performance can be an issue there. I tried running the
Hugo's query with the "NOT EXISTS" option. It run for two hours before
I stopped it... The second option ("LEFT OUTER JOIN ") I stopped after
15min ....
The table holding the data has over 1.3 million records, that besides
rain gauge data also has other data types (e.g., water conductivity,
water level, water chemistry etc.) in the "Class_Name" column. I didn't
mention this in my original post because I thought I could deal with it
after I'll get your help with the basic query, but now I see it is much
more complex then I thought. I added this to Hugo's query as you can
see below. So the efficiency of the query is an important issue here.
If you have any ideas on how to make it run faster?

Here is the query as I run it with both options:

--START
SELECT a.Location_Name
, a.Time AS StartTime
, MIN(b.Time) AS EndTime
,(SELECT SUM(e.Value)
FROM V_Cell AS e
WHERE e.Location_Name =
a.Location_Name
AND e.Class_Name = 'Rain'
AND e.Time >= a.Time
AND e.Time <= MIN(b.Time)) AS
TotalRain
FROM V_Cell AS a
INNER JOIN V_Cell AS b
ON b.Location_Name = a.Location_Name
AND b.Class_Name = 'Rain'
AND b.Time >= a.Time
-- WHERE NOT EXISTS (SELECT *
-- FROM V_Cell AS c
-- WHERE c.Location_Name =
a.Location_Name
LEFT OUTER JOIN V_Cell AS c
ON c.Location_Name = a.Location_Name
AND c.Class_Name = 'Rain'
AND c.Time < a.Time
AND c.Time >= DATEADD(hour, -12, a.Time)
-- AND NOT EXISTS (SELECT *
-- FROM V_Cell AS d
-- WHERE d.Location_Name =
b.Location_Name
LEFT OUTER JOIN V_Cell AS d
ON d.Location_Name = b.Location_Name
AND d.Class_Name = 'Rain'
AND d.Time > b.Time
AND d.Time <= DATEADD(hour, +12, b.Time)
GROUP BY a.Location_Name
, a.Time

--END

Many thanks,
Ilik|||On 17 Feb 2005 01:06:22 -0800, Ilik wrote:

(snip)
>If you have any ideas on how to make it run faster?

Hi Ilik,

I don't think there's much room for simplification of the query, so I'd
look into indexes. I don't expect my query to return results in a fraction
of a second, but two hours appears to be too much. I'm afraid that you
have no indexes on your table that can be used for this query.

In order for us to advise on this, we need to know the actual table
structure (as CREATE TABLE statement, **INCLUDING** all constraints [esp
primary key and unique!]) and all extra indexes you might have defined.
You might wish to run sp_help V_Cell to get a full report on all indexes
and constraints.

Another thing you could do is to run the index tuning wizard (ITW): load
my query in a QA window, select "Query" / "Index Tuning Wizard" and see
what it advises you. ITW is certainly not always perfect, but it never
hurts to check out what it comes up with.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Ilik (levyi@.walla.co.il) writes:
> As for Erland's solution, I run it only for a few minutes because of
> his remark that performance can be an issue there. I tried running the
> Hugo's query with the "NOT EXISTS" option. It run for two hours before
> I stopped it... The second option ("LEFT OUTER JOIN ") I stopped after
> 15min ....
> The table holding the data has over 1.3 million records, that besides
> rain gauge data also has other data types (e.g., water conductivity,
> water level, water chemistry etc.) in the "Class_Name" column. I didn't
> mention this in my original post because I thought I could deal with it
> after I'll get your help with the basic query, but now I see it is much
> more complex then I thought. I added this to Hugo's query as you can
> see below. So the efficiency of the query is an important issue here.
> If you have any ideas on how to make it run faster?

Your data volumes are certainly in par with what I expected. (No one
would have a table like that with only a few thousands of rows!) But
without test data available, it's difficult to post a solution that
tested for performance.

For my query batch, it could be an idea to let it run for a little
longer, and see what actually takes time.

Certainly there is all reason to review the indexing of the table, and
as Hugo said, posting the complete CREATE TABLE and CREATE INDEX
statement for the table may help.

One solution that cannot be ruled out completely, is to run a cursor
over the lot. That is by no means not going to be blinding fast, but
you can quite soon get a grip of when it will complete. (As you would
be able to see how much have been processed.) But that would really be
a last-ditch attempt if nothing else works.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> The data goes back a few decades in some cases (starting at 1965),
so I cannot evaluate it on entry .. The table holding the data has over
1.3 million records, that besides rain gauge data also has other data
types (e.g., water conductivity, water level, water chemistry etc.) in
the "Class_Name" column. <<

Ouch! I think is a case where I would go with a cursor, since it
sounds like you will do this only once. That gives you one scan thru
the whole table.

Any other method I can think of would use a self-join to get the start
and finish times of each collection period. Even with indexing,
self-joins take time.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1108745004.509101.63100@.c13g2000cwb.googlegro ups.com...
> >> The data goes back a few decades in some cases (starting at 1965),
> so I cannot evaluate it on entry .. The table holding the data has over
> 1.3 million records, that besides rain gauge data also has other data
> types (e.g., water conductivity, water level, water chemistry etc.) in
> the "Class_Name" column. <<
> Ouch! I think is a case where I would go with a cursor, since it
> sounds like you will do this only once. That gives you one scan thru
> the whole table.
> Any other method I can think of would use a self-join to get the start
> and finish times of each collection period. Even with indexing,
> self-joins take time.

<Uninformed musings>
As others have stated, it is difficult to gauge the efficacy of the
suggestions without knowing more about the table structure.

Is it possible for the OP to extract the "Rain" records and create a new
table with appropriate indexing? He stated there are 1.3 million records
TOTAL but how many are "Rain" records and will it reduce the work to an
acceptable level for one of the aforementioned queries to complete?

How about a hybrid approach? What if you cursor-ed through the data by
"Station Name" and use one of the queries with the additional condition of
"Station Name"? I presume a table/list of the station names exist otherwise
you could "SELECT DISTINCT". As Hugo K. has demonstrated, the queries work
well enough for a small case which this would be especially if there is an
index on "station Name".
</Uninformed musings|||Hi all,
Thanks for your suggestions.
After a long consideration with my DB admin, we've decided to use the
curser approach as you suggest.
Currently, our db holds only about 9,000 records of type Rain but this
number is expected to grow
in the future since only a small part of the data has been introduced
into the database.
So using an all-at-once query of the data will be a very slow process.
Since I'm fairly new to SQLServer, I don't know how to run a script on
it directly. But since anyway
I only need to produce a web-report, I'm using MathLab for this (thou
VB or asp could do the job just as easy).

again, many thanks for your help,
Ilik

Conditional stored procedure question

I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)

AS

SELECT uid, name, date, registration_confirmed
FROM tbl_members

WHERE
If @.iShowAll = 0
begin
(uid = @.myID) AND (registration_complete = 0)
end
else
begin
(uid = @.myID)
end

GO

Thanks,
davidyou can use a CASE statement but i do not know the syntax..heres another way of doing it

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)
AS
if @.iShowAll = 0
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID AND registration_complete = 0
else
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID
go

HTH|||Here's an example using a Case

SELECT uid, name, date, registration_confirmed FROM tbl_members
WHERE (uid = @.myID) AND registration_complete = CASE WHEN @.iShowAll = 0 THEN 0 ELSE registration_complete END

Conditional Page breaks

How do i create page breaks that depend on report parameters?Nope, page breaks are hardcoded at design time.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"news.microsoft.com" <rodrigoss@.hotmail.com> wrote in message
news:OqO%236aW5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> How do i create page breaks that depend on report parameters?
>

Conditional Order By Stored Procedure

I need to create a conditional if or case statement in SQL Server 2000
for a stored procedure. Basically if the value passed in is 1,2 or 3 then
it will order by either NEWID(), a text field or a datetime feild.
Not done much dynamic sql so any help would be appreciated.
Fuzzy
The approach I typically take is this:

SELECT
someColumns
FROM
someTable
WHERE
CASE WHEN @.sortValue = 1 THEN NEWID() END,
CASE WHEN @.sortValue = 2 THEN someTextColumn END,
CASE WHEN @.sortValue = 3 THEN someDateTimeColumn END,
defaultSortColumn -- just in case the @.sortValue is not 1, 2, or 3, Iknow the results will be sorted by *something*

|||My full stored procedure is listed below but i assume i have to specify the order
by clause somewhere it keeps returning a incorrect syntax near CASE error message
The sproc
CREATE PROCEDURE [dbo].[sp_call_accomSearch]
(
@.accomType As Int,
@.sgleroom As Int,
@.dbleroom As Int,
@.twinroom As Int,
@.tripleroom As Int,
@.Garage As Int,
@.Phone As Int,
@.Altitude As Int,
@.CarPark As Int,
@.Tv As Int,
@.TownCentre As Int,
@.SwimPool As Int,
@.Radio As Int,
@.NearSlopes As Int,
@.DgsAdmit As Int,
@.Safe As Int,
@.CrossCtry As Int,
@.SuitDisable As Int,
@.Balcony As Int,
@.OnTLake As Int,
@.Solarium As Int,
@.Suite As Int,
@.QutZone As Int,
@.BeautyCb As Int,
@.Minibar As Int,
@.Tennis As Int,
@.WhirlPl As Int,
@.Elevator As Int,
@.Sauna As Int,
@.PriceRgLow As Int,
@.PriceRgHigh As Int,
@.DateFromTotal As DateTime,
@.DateToTotal As DateTime,
@.selfcatering As Int,
@.halfboard As Int,
@.fullboard As Int,
@.roomphone As Int,
@.Suitdisableroom As Int,
@.CountryID As Int,
@.OrderBy As Int
)
AS
SELECT
tblaccommodation.accommodationID,
tblaccommodation.[name],
tblaccommodation.address1,
tblaccommodation.address2,
tblaccommodation.town,
tblaccommodation.postcode,
tblaccommodation.country,
tblaccommodation.email,
tblaccommodation.contact,
tblaccommodation.editorial,
(SELECT [name] FROM tblresort WHERE resortID = resortname) As ResortName,
(SELECT SUM(sgleroom) As sgleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As SgleRoomTotal,

(SELECT SUM(dbleroom) As dbleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As dbleRoomTotal,
(SELECT SUM(twinroom) As twinroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As twinRoomTotal,
(SELECT SUM(tripleroom) As tripleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As tripleRoomTotal
FROM
tblaccommodation
WHERE

tblaccommodation.accomType = case
when @.accomType = 1 then @.accomType
else tblaccommodation.accomType
end AND

(SELECT SUM(sgleroom) As sgleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.Sgleroom AND

(SELECT SUM(dbleroom) As dbleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.dbleroom AND
(SELECT SUM(twinroom) As twinroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.twinroom AND
(SELECT SUM(tripleroom) As tripleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.tripleroom AND

tblaccommodation.Garage = case
when @.Garage <> 0 then @.Garage
else tblaccommodation.Garage
end AND
tblaccommodation.Phone = case
when @.Phone <> 0 then @.Phone
else tblaccommodation.Phone
end AND
tblaccommodation.Altitude = case
when @.Altitude <> 0 then @.Altitude
else tblaccommodation.Altitude
end AND
tblaccommodation.Carpark = case
when @.Carpark <> 0 then @.Carpark
else tblaccommodation.Carpark
end AND

tblaccommodation.TownCentre = case
when @.TownCentre <> 0 then @.TownCentre
else tblaccommodation.TownCentre
end AND
tblaccommodation.SwimPool = case
when @.SwimPool <> 0 then @.SwimPool
else tblaccommodation.SwimPool
end AND
tblaccommodation.NearSlopes = case
when @.NearSlopes <> 0 then @.NearSlopes
else tblaccommodation.NearSlopes
end AND
tblaccommodation.DgsAdmit = case
when @.DgsAdmit <> 0 then @.DgsAdmit
else tblaccommodation.DgsAdmit
end AND
tblaccommodation.Safe = case
when @.Safe <> 0 then @.Safe
else tblaccommodation.Safe
end AND
tblaccommodation.CrossCtry = case
when @.CrossCtry <> 0 then @.CrossCtry
else tblaccommodation.CrossCtry
end AND
tblaccommodation.SuitDisable = case
when @.SuitDisable <> 0 then @.SuitDisable
else tblaccommodation.SuitDisable
end AND
tblaccommodation.OnTLake = case
when @.OnTLake <> 0 then @.OnTLake
else tblaccommodation.OnTLake
end AND
tblaccommodation.Solarium = case
when @.Solarium <> 0 then @.Solarium
else tblaccommodation.Solarium
end AND
tblaccommodation.QutZone = case
when @.QutZone <> 0 then @.QutZone
else tblaccommodation.QutZone
end AND
tblaccommodation.BeautyCb = case
when @.BeautyCb <> 0 then @.BeautyCb
else tblaccommodation.BeautyCb
end AND
tblaccommodation.Tennis = case
when @.Tennis <> 0 then @.Tennis
else tblaccommodation.Tennis
end AND
tblaccommodation.Whirlpl = case
when @.Whirlpl <> 0 then @.Whirlpl
else tblaccommodation.Whirlpl
end AND
tblaccommodation.Elevator = case
when @.Elevator <> 0 then @.Elevator
else tblaccommodation.Elevator
end AND
tblaccommodation.Sauna = case
when @.Sauna <> 0 then @.Sauna
else tblaccommodation.Sauna
end AND

tblaccommodation.PriceRgLow >= @.PriceRgLow AND
tblaccommodation.PriceRgLow <= @.PriceRgHigh AND
tblaccommodation.PriceRgHigh <= @.PriceRgHigh AND

tblaccommodation.FromDT >= @.DateFromTotal AND
tblaccommodation.ToDT <= @.DateToTotal AND
tblaccommodation.selfcatering = case
when @.selfcatering<> 0 then @.selfcatering
else tblaccommodation.selfcatering
end AND
tblaccommodation.halfboard = case
when @.halfboard<> 0 then @.halfboard
else tblaccommodation.halfboard
end AND
tblaccommodation.fullboard = case
when @.fullboard<> 0 then @.fullboard
else tblaccommodation.fullboard
end AND
tblaccommodation.Country = @.CountryID AND
tblaccommodation.displayAcc = '1'
CASE WHEN @.OrderBy = 1 THEN NEWID() END,
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDT END,
GO
|||First, I made a mistake in my example code. I mistakenly used aWHERE clause instead of an ORDER BY clause. Sorry to be confusing:-(
You are missing the ORDER BY, and you had an unneeded comma at the end. Try:

tblaccommodation.displayAcc = '1'
ORDER BY
CASE WHEN @.OrderBy = 1 THEN NEWID() END,
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDTEND

|||LOL ... i am confused most of the time anyway usually why i am on here so much
I am still getting an sql error still when i try to check the syntax of the query in question any
thoughts what is going wrong here not found anything on google groups like it yet. I have
listed the error below.
Fuzzy

Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as
part of the expression identfying a column position. Variables are only allowed when ordering
by an expression referencing a column name
|||Hmmm, it's not liking the NEWID(). I suggest this as aworkaround, but it will force NEWID() to be generated for each row inthe resultset, which *might* be a performance hit if you have a lot ofrows:
ORDER BY
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDTEND,
NEWID()
|||

You Can also do:

SELECT [all of your fields]
FROM (
SELECT [all of your fields], newID() as ID
FROM ...
) X
ORDER BY
CASE WHEN @.orderBy = 1 THEN ID END,
CASE WHEN @.orderBy = 2 THEN name END,
CASE WHEN @.orderBy = 3 THEN FromDT END
While either version will work for you, I think this one might be a little cleaner so if someone else needs to update it, they can see exactly how it is supposed to use the new ID (if orderBy is 1).
Just my 1.50
Nick

|||

nick-w wrote:

You Can also do:

SELECT [all of your fields]
FROM (
SELECT [all of your fields], newID() as ID
FROM ...
) X
ORDER BY
CASE WHEN @.orderBy = 1 THEN ID END,
CASE WHEN @.orderBy = 2 THEN name END,
CASE WHEN @.orderBy = 3 THEN FromDT END
Whileeither version will work for you, I think this one might be a littlecleaner so if someone else needs to update it, they can see exactly howit is supposed to use the new ID (if orderBy is 1).

I had thought about that, but had become concerned that NEWID()would be calculated for every row in every table, not just for theresultset. That sent me on a fruitless hunt to find a referencefor the processing sequence of all of the different portions of aSELECT statement so that I could confirm or deny that concern. When I couldn't find a reference (does anyone have one?) , I decidedon the approach I recommended. But, with that being said, I muchprefer the approach you've recommended as it doesn't make assumptionsand as you said it is cleaner. :-)

Conditional Joins

Hi all,

I have 4 tables with the structure shown below

Main Table :

Create Table TestMain
(TestMainId INT , TestCompanyID INT )

Other Tables :

Create Table TestCompany1
(Id INT , TestCompanyID INT )

Create Table TestCompany2
(Id INT , TestCompanyID INT )

Create Table TestCompany3
(Id INT , TestCompanyID INT )

In this above tables.. I would have a record in the table TestMain and a entry for that specific record would be in any of the tables like TestCompany1,TestCompany2,TestCompany3

Sample Records :

In the table TestMain

1 1000
2 2000
3 3000
4 4000
5 5000
6 6000
7 7000

In the table TestCompany1

1 1000
2 6000

In the table TestCompany2

1 3000
2 4000
3 5000

In the table TestCompany3

1 7000

How do I join those tables and fetch the main record with its subsequent entry from the other tables ?

Thanks in advance,

HHADo you have defined any relationship between the tables..? It's basic requirement for data integrity.

Anyway you can join the two tables this way...

Select Testmain.TestMainId, TestMain.TestCompanyID From TestMain
JOIN TestCompany1 ON TestMain.TestCompanyID = TestCompany1.TestCompanyID

You can join more than two tables using different join types...|||Hi

You would need to use left joins and maybe COALESCE but it is hard to know without more details. The fact that you are doing this hints that your design may not be sound too (although it may be - this looks like a mock up yes?).

HTH|||I think your best bet would be to inner join against each table and UNION or UNION ALL the results - your design does look dubious though, why are you segmenting companies across 3 tables - do they have different attributes per collection or is there another reason?|||Hi all,

There is a main table say COMPANY and they other tables CompanyA , CompanyB , CompanyC.

The main table COMPANY has the general info about the company ( like address , contact info) and there are 3 BIT columns to indicate what all type of company it falls under.If it falls under A & B , then the relevant information
are stored in CompanyA & CompanyB.

Now I need to write a proc which gets few input parameters and searches
for the company details.

1. If no parameters where passed , I need to get all the company from the
COMPANY with relevant information from the CompanyA, CompanyB,CompanyC.

2.If I get a parameter which says I should fetch only companys falling under
CompanyA , I should be able to get them too.

Still , the DB is in production , I cant touch the design.

Thanks for all your help ,

HHA|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71565

Terrible design - I'm sure you know or are at least becoming aware of.

Playing with post three from the link (not efficient - there may be better solutions):

CREATE TABLE #TESTMAIN
(TESTMAINID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY1
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY2
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY3
(ID INT , TESTCOMPANYID INT )

INSERT #TESTMAIN
SELECT 1, 1000 UNION ALL
SELECT 2, 2000 UNION ALL
SELECT 3, 3000 UNION ALL
SELECT 4, 4000 UNION ALL
SELECT 5, 5000 UNION ALL
SELECT 6, 6000 UNION ALL
SELECT 7, 7000

INSERT #TESTCOMPANY1
SELECT 1, 1000 UNION ALL
SELECT 2, 6000

INSERT #TESTCOMPANY2
SELECT 1, 3000 UNION ALL
SELECT 2, 4000 UNION ALL
SELECT 3, 5000

INSERT #TESTCOMPANY3
SELECT 1, 7000

DECLARE @.CompanyOneOnly AS Bit
SET @.CompanyOneOnly = 1

SELECT *
FROM -- Relvent companys
(SELECT X1.TESTMAINID,
X1.TESTCOMPANYID,
ISNULL(X2.ID,0) AS Comp1,
ISNULL(X3.ID,0) AS Comp2,
ISNULL(X4.ID,0) AS Comp3
FROM #TESTMAIN X1
LEFT JOIN #TESTCOMPANY1 X2 ON X1.TESTCOMPANYID = X2.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY2 X3 ON X1.TESTCOMPANYID = X3.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY3 X4 ON X1.TESTCOMPANYID = X4.TESTCOMPANYID) AS DerT
WHERE CAST(Comp1 AS Bit) = @.CompanyOneOnly OR @.CompanyOneOnly = 0

DROP TABLE #TESTMAIN
DROP TABLE #TESTCOMPANY1
DROP TABLE #TESTCOMPANY2
DROP TABLE #TESTCOMPANY3

Conditional Insert in SQL Server 2000?

I need to do a conditional insert, and I am afraid that if I do it in 2 steps it will create bugs...
I have a table called customers and a table called locks
customers has 3 fields CustomerID, FirstName, and LastName
locks has fields LockID, CustomerID, UserID, SessionID, and TimeStamp
I need to check if there are any records in Locks for a CustomerID and if there are none, I need to insert One.
This is going to tell my application that this Customer record is locked so no one else can edit it, until the Lock record is deleted.
I just don't want to make 2 trips, One to check if the Lock exists, then One to insert the Lock, since this could allow 2 locks to be created if the timing is correct.
Any ideas or comments?This code from myPreventing Duplicate Record Insertion on Page Refresh article should do what you need (note the use of BEGIN TRANSACTION, EXISTS, and WITH (UPDLOCK

Listing 4 – Stored Procedure spAddEmployee_UsingExists

CREATEPROCEDURE spAddEmployee_UsingExists
(
      @.FirstNamevarchar(50),
      @.LastNamevarchar(50)
)
AS
DECLARE @.Resultint
BEGINTRANSACTION
IFEXISTS
(
     SELECT
           NULL
     FROM
            EmployeesWITH (UPDLOCK)
     WHERE
            FirstName = @.FirstNameAND
            LastName = @.LastName
)
     BEGIN
           SELECT @.Result = -1
     END
ELSE
     BEGIN
           INSERTINTO
                  Employees
            (
                  FirstName,
                  LastName
            )
           VALUES
            (
                  @.FirstName,
                  @.LastName
            )
           SELECT @.Result =@.@.ERROR
     END
IF @.Result <> 0
     BEGIN
           ROLLBACK
     END
ELSE
     BEGIN
           COMMIT
     END
RETURN @.Result

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.
Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/pro...05/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.
|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn this
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it. If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/pro...05/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where the
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled plan.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead of
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creating
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>
|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote:

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
--
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn this
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it. If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where the
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled plan.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead of
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creating
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
--
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote:
>> Creating an index over a table causes its schema to change, and in turn
>> this causes queries that reference the table to be recompiled. So, the
>> short answer to "will the conditional index creation mess up the SQL
>> engine" is no.
>> SQL Server will first compile the procedure, and then start executing it.
>> If the schema of a table changes between the compilation and execution of
>> a statement referencing it, the statement will be recompiled.
>> Actually the behavior changed significantly between SQL 2000 and 2005. In
>> 2000, the recompilations would affect the entire batch or procedure. A
>> significant improvement has been made in SQL 2005 with statement-level
>> recompiles. As the name suggests, in SQL 2005 only the affected
>> statements are recompiled, rather than the entire batch or procedure.
>> For more information on the subject, we have a very good whitepaper here:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> The consequence of what you are doing is that if you interleave
>> executions of the procedure that do not cause the index creation with
>> others where the index is created, you will incur in a significant number
>> of recompiles, because the schema of the temp table won't match the
>> previous compiled plan. In SQL 2000, this will be exacerbated with the
>> lack of statement level recompiles. This might easily negate the benefits
>> of saving the overhead of creating an index when the table is small.
>> Also, creating an index on a small table is a low overhead operation
>> anyway. I'd consider always creating the index, and seeing if you can
>> make it part of the table definition altogether if applicable.