|
ma se wrote: I'm not sure how to construct this stored procedure because not all the input values will have a value.
Then they will be null?
SELECT * FROM MyTable
WHERE (someColumn = @param1 OR @param1 IS NULL) AND
(someOtherColumn = @param2 OR @param2 IS NULL) AND... so on
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
As I said in my other answer, that approach can give poor performance because either a) the optimizer gets confused and picks a bad plan or b) the plan it picks for the first set of arguments is poor for the second set of arguments. Marking the procedure to be recompiled every time (WITH RECOMPILE) can help with the second, but you incur the cost of recompiling the plan every time (although I suspect this is largely CPU-bound, barring loading the index/table statistics from wherever SQL Server keeps them).
Any time it has to do a table scan or index scan, it's going to cost you. If the data is not yet in memory, you'll incur a lot of I/O cost reading in all the pages from disk. If it is in memory, you'll get a lot of CPU usage shown in Task Manager and Processor performance counters, but adding more/faster CPUs won't help at all because in fact the processor is just sitting in wait states waiting for the data to come into its caches from main memory. (A faster front-side bus and faster RAM modules would help.)
This assumes that the main operator for each parameter is sargable and can therefore make use of index seeks. If you're using LIKE '%value%' , go ahead and do it this way, SQL Server will have to perform a table scan or clustered index scan anyway, assuming no other fixed filter parameters which are sargable.
|
|
|
|
|
Thanks.
I've just been informed that I need to make use of full text searching, how would I do this now??
Regards
ma se
|
|
|
|
|
If you are using sql2005 you could also look at creating your stored procedure as a clr stored procedure, giving you an assembly and stored procedure. The advantage is that you can wrap the c#'s string capabilities into the stored procedure as well as other coding language capabilities. Also, you are able to recieve nulls and can debug the SProc at run time in vs2005.
Should take a look if this is an option.
My 2C worth.
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
What would I need to search for??
Please check my new post under 1 on Colin's postings.
|
|
|
|
|
Hi All,
Im using Sql server Express 2005, in the client place.
i want to copy the database with all data and put it in my sql server express 2005 in office.
so plz help me how can i do this
how can i copy entire data from sql server express and put it in another sql server express
Thanks for ur valuable help
With Regards
Amjath
|
|
|
|
|
copy mdf and ldf file from client place then attach database in your office
I Love SQL
|
|
|
|
|
thanks for ur quick and valuable reply.
can u plz explain me how to do that.
sorry for disturbing u
With Regards
Amjath
|
|
|
|
|
Just stop MSSQL Service then locate where are files copy them and restart again MSSQL Service
In your office open SQL Server Attach Database[^] by findinf file of MDF extension.
I Love SQL
|
|
|
|
|
Actually detaching the database would be the first step. In ServerManager just right click on the database, click on tasks and then detach. I would first get the location from the properties of the ldf and mdf before detaching. Copy those files and move them to where you want, reattach on the database detached from and on your other database. The only other issue you might have is table ownership. Occasionally the mapping gets a little off and has to be justified.
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
Why did you replied to me?!
I Love SQL
|
|
|
|
|
I am using SQL server 2000.
I have added some records in table.
and then deleted.
Now I would like ID field to start count from 1 and onward.
what command will be used.
God Bless you.
Always do good to others
|
|
|
|
|
|
I agree... but what about the relationships and constraints ...
if I have a primary key of identity column .. then that statement will generate value that already exists in my table.... m I right?
|
|
|
|
|
dbcc checkident(tablename,reseed,1)
I Love SQL
|
|
|
|
|
Do any one know how to display data from the data base by extracting it out using the unique id of the database. I wan to display the whole row of information of that unique id.
calvin
|
|
|
|
|
|
Please don't repost things so quickly. It's not good practice and won't get your question answered any quicker. In fact, as you are finding out, people are more likely just to respond and tell you not to do this.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Do any one know how to display data from the data base by extracting it out using the unique id of the database. I wan to display the whole row of information of that unique id.
calvin
calvin
|
|
|
|
|
If we have an identity field wit data type of BigInt ... but at certain stage it will overflow ... then what will happen and how can prevent for such scenario?
|
|
|
|
|
use decimal data type
this allows max value i.e. 38 digits
Regards
KP
|
|
|
|
|
But at certain stage ... it could overflow too! then? for example.. in case of Amazon or any service like that...
|
|
|
|
|
every s/w has its own limitations.
in SQL Server 2000 decimal is the one which can be used max digits
pls check whether your number is more than 38 digits
or in how many yrs it is going to reach this limit.
or use a char/varchar data type which allowes you even more.
or else take a new table.
Regards
KP
|
|
|
|
|
Hi ....
Iam new to dotnet and Sql server ...
I want to install sql server in my system ..
Ive 180 days trail version ...
its installed but i cant able to login ...
My System name is NARESH ...
It asking about system name , username and passwords ..
I gave "sa" , "sa" .
But it is showling like unable to login with NARESH ...
Plz help me ...
Ive .net framework 2.0 in my system ..
Thanks in advance
Naresh
|
|
|
|
|