|
This is all well and good, but could you consider buying some advert space here at CodeProject? You seem to spend an awful lot of time telling us what your product does (and I assume you're not giving it away for free). If it's that good, pay for some space rather than posting replies to questions that (in effect) say don't use this, buy our product and use it instead.
|
|
|
|
|
We do look at our traffic analytics fairly often and would advertise here if the projected ROI figures changed. At the moment we're happy letting codeproject folks download the free personal edition.
|
|
|
|
|
Hi,
I have a stored procedure that is basically as follows:
SELECT ColumnA, ColumnB, ColumnC
FROM TableA
WHERE
(@ValueA IS NULL OR ColumnA = @ValueA) AND
(@ValueB IS NULL OR ColumnB = @ValueB) AND
(@ValueC IS NULL OR ColumnC = @ValueC)
So each of the input values can either have a value or is null. If it has a value it has to be included in the filter, otherwise there will not be a filter on that column.
Let's say that I have 3 indexes: 1) ColumnA, 2) ColumnA and ColumnB, 3) ColumnA and ColumnC
Now on the first run of this sproc, if let's say columnA and ColumnB have values, it will use index 2. On the next runs it will continue using index 2, even if now ColumnA and ColumnC have values (thus actually it should have used index 3).
I know that one can tell SQL what index to use using WITH(INDEX XXXX) but can the use of an index be conditioned, or do i have to write 3 different stored procedures?
|
|
|
|
|
Probably best to use dynamic sql
but read this first http://www.sommarskog.se/dynamic_sql.html
|
|
|
|
|
The truth is I don't want to go there. I rather write an extra sproc.
|
|
|
|
|
What I have been taught and understand about SQL Server is this:
Let it decide on what indexes to use on the basis that 'SQL Server knows best'.
Sometimes SQL Server will decide to do a table scan; as in some cases this is faster than what appears to be the correct index.
There will be others out there who know a lot more than me about this.
So basically set your indexes up and trust SQL to pick the most appropriate use of indexes (after all the database engine is designed precisely for this reason).
I was taught not to use the
gnjunge wrote: WITH(INDEX XXXX)
for this reason.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
You are partially right that SQL chooses the right index.
But since this is a "conditional" where clause, the first time the sproc runs, SQL chooses the index that is right for that condition. But when the condition changes SQL stays with that same index. (which slows down the sproc)
When I cause the sproc to recompile, and use as first run the second condition it chooses the second index as its index, and stays with that index, even when the condition changes. (thus running slow when the first condition happens).
|
|
|
|
|
You can recompile the stored procedure on each run Clickety.
You always pass failure on the way to success.
|
|
|
|
|
Good solution , cause it says that in SQL 2005 I can do statement-level recompilation which fits my needs.
Thanks.
|
|
|
|
|
I wouldnt get into the habit of doing that though. On a complex statement it will take a fair bit of time to analyze the query and build the plan.
If you use sp_executesql then SQL Server will cache the plan associated with that statement.
|
|
|
|
|
Thanks for your comment.
I will test both solutions , and check which one is faster.
|
|
|
|
|
Just a follow up:
I used the sp_executesql method, and it works excellent.
|
|
|
|
|
You're welcome. 
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
hi,
i have a script to create a database but i have to give the filename and path for creating it.
CREATE DATABASE [ATCommon] ON PRIMARY <br />
( NAME = N'ATCommon', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ATCommon.mdf' , SIZE = 40768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )<br />
LOG ON <br />
( NAME = N'ATCommon_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ATCommon_log.ldf' , SIZE = 5184KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)<br />
COLLATE SQL_Latin1_General_CP1_CI_AS<br />
END
When i run it for another instance i got a problem because the path isn't correct.
is there a way to skip the file path when creating or retrieving the path for an sql instance ?
thc
|
|
|
|
|
ok, my fault,
i can skip the filename and path in the create statement
i just have to figure out how to change the settings for the database, but that won't be a problem.
greetz
|
|
|
|
|
how do i clear this Error?
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
i'm using sql2005...in online exam quiz..
plz help me..
|
|
|
|
|
What are the details of your connection string?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
mycon.ConnectionString = "Server=(local);DataBase=im;Integrated Security=True"
mycom = New SqlCommand("INSERT INTO quiz1(Result,Email) values('" + strResult + "','" + TextBox1.Text + "')", mycon)
mycon.Open()
mycom.ExecuteNonQuery()
mycon.Close()
i'm using Vb script and Asp.net... db is Sql 2005..
this program is running on localhost but didnt run external...
what can i do?
|
|
|
|
|
mananth wrote: mycon.ConnectionString = "Server=(local);DataBase=im;Integrated Security=True"
You are using Integrated Security to connect to SQL Server. Does the account that your ASP.NET web app is running under have the required permissions to access the SQL database?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
Can any body help me in writing a query to retrieve the count of non null columns in a particular row.
For eg: I have 30 columns in a table and I enter values to only 5 columns.
when I run the query I should get the output as 5 columns have non null values. If it is not possible then is it possible to read values of different columns of a single row into a single variable.
Please help me out.
ABC
|
|
|
|
|
AFAIK, the only way of doing this would be to loop through the columns and count the number of fields with a NULL value.
You may want to consider whether your database design is optimal for the type of work you are doing.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Its Not pretty but...
select case when cola is null then 1 else 0 end+<br />
case when colb is null then 1 else 0 end+<br />
...................<br />
from table
modified on Friday, January 11, 2008 3:53:29 AM
|
|
|
|
|
Is SQL Injection is possible even after replacing all single quote i.e ' from the user input with two single quote i.e '' ? .If so can you give me any example.
|
|
|
|
|
Using parameterized queries is better practice anyway.
|
|
|
|
|
there there is no way to inject after replacing ' with '' 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
