|
Hi,
In a table with a primary key and a foreign key like this.
I-------I-------I-------I
I pkey I fkey I name I
I-------I-------I-------I
I 1 I 1 I ITEM I
I-------I-------I-------I
I 2 I 1 I ITEM2 I
I-------i-------I-------I
If we were to run a select query against this table that returns just one row. Would be any performance benefit if we have this select ?:
SELECT * FROM The_Table WHERE pkey=2 AND fkey=1
over when we have this select ?:
SELECT * FROM The_Table WHERE pkey=2
|
|
|
|
|
As the primary key is unique (a condition of being a primary key is that it uniquely identifies the row) then I wouldn't see any need for any additional information in the WHERE clause.
Whether it has any benefit or not depends on how you set up the table. If you go with the traditional defaults then I don't see it having any additional benefit. But I doubt it would have any negative effect either.
|
|
|
|
|
If we define an index on the foreign key field , will be any performance gain ?
|
|
|
|
|
devboycpp wrote: If we define an index on the foreign key field , will be any performance gain ?
In this case, I doubt it. The WHERE clause already contains a reference to a column that is guaranteed to be unique (i.e. the Primary Key) and is most likely to be indexed (unless it was removed) so will go directly to that row. Foreign keys are not normally unique.
Also, adding an index can make performance worse for INSERT, UPDATE and DELETE operations.
|
|
|
|
|
select empid,empname,valid bit null
into #temp
from table
I know this is wrong. What I need is to add a new column which is not in table [valid] and assign that column as bit type and value as null.
Can you please help?
Thanks
|
|
|
|
|
A BIT column is not nullable.
Paul Marfleet
|
|
|
|
|
Please help me this problem, thanks.
Thao
|
|
|
|
|
I'm sorry, I'm sure English is not your first language, and I don't want you to think you're under attack for that. But, your question makes no sense. Your english doesn't have to be perfect, we're very forgiving, but if we don't understand, we just can't hope to help.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I was able to run ssis Installation successfully. In summary of last step, is it possible to take out the author and log file information or rewrite them with other information?
|
|
|
|
|
hi all,
i want to write a generic Insert & update procedure.It should work with different tables having diffrerent datatypes.suppose let say table1 may have col1 int,col2 varchar... and let say table2 may have col1 varchar,col2 char..like that..and so on..
generally till now wat iam doing is that iam writting different stored procedure for different tables..
Now i want to write One Insert & update procedure in my project...and for all the tables in the project i want to use only that procedure.
Is it Possible? If yes kindly help me ..on resolving this issue..
Thanks & regards
suman
|
|
|
|
|
You can. But it would most likely end up using dynamic SQL and you might as well just fire that against the database instead.
|
|
|
|
|
hi
thanks for u reply..
can u help me in writing the query for that requirement.If u have any thing about the dynamic sql and like generic procedures ..please help me by giving some sample query.
|
|
|
|
|
What's the difference between @a and @@a type parameters
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
You pass in parameters that have @. @@ relates to internal functions and values inside SQL Server.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
@a is a user defined parameter where as @@<param-name> such as @@Identity is a system defined parameter.
Intelligence is measured by common sense not by how many scholarly books you read.
|
|
|
|
|
hi
@ stands for local variable
@@ Stands for System variables
eg:
declare @name as nvarchar
set @name='hello'
print @name
select @@VERSION
Returns the date, version, and processor type for the current installation of Microsoft® SQL Server
|
|
|
|
|
how can i use the for loop in stored procedure
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
Sonia Gupta wrote: how can i use the for loop in stored procedure
You can't. Use a WHILE loop instead.
|
|
|
|
|
ok thanks
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
how can i use the while loop.example have to to use select statement
for loop=1 to select count(*) from table
next
this is merely an example.i lknow u have have told i can't use the for loop.this is just to make u understand.
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
declare @cnt as int
set @cnt = (select count(*) from table)
while .....
Regards
KP
|
|
|
|
|
You may have to use a cursor.
This depends on what you are doing in the loop.
A cursor will let you process each row of a query individually.
If you google "SQL cursor" you will find lots of examples.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
GuyThiebaut wrote: You may have to use a cursor.
Don't encourage people to use a cursor unless there is absolutely no other choice. Cursors are extremely slow in SQL Server and should be avoided unless absolutely necessary.
|
|
|
|
|
Ooh I feel like I have just been told off
What I have read in manuals is that cursors are generally very greedy in terms of resources.
My experience however has shown that they are perfectly fine to use.
Yes I know it is possible to write a query without the use of a cursor (I'll be damned if I know how to do this though).
I think unless the application is for some huge banking corporation or airline then using a cursor should be fine.
What would you suggest instead of cursors?
You always pass failure on the way to success.
|
|
|
|
|
GuyThiebaut wrote: Yes I know it is possible to write a query without the use of a cursor (I'll be damned if I know how to do this though).
You have to write a query to populate a cursor in the first place. Therefore you must know how to write some queries.
GuyThiebaut wrote: I think unless the application is for some huge banking corporation or airline then using a cursor should be fine.
That's your opinion, and I, along with many qualified DBAs that I know, disagree with it.
GuyThiebaut wrote: What would you suggest instead of cursors?
Write set based queries where possible. Never consider a cursor until all other avenues are exhausted.
SQL Server, along with many other RDBMS systems, works best when dealing with sets of data. It does not work well when dealing with individual rows.
SQL is a declarative language - you declare what you want and then leave it up to the database engine to figure out how to get it. When you use cursors you introduce an element of procedural programming in to it. That means you are forcing it to do it HOW you tell it, which is often not the best way.
There are some situations where CURSORs are needed, e.g. recursive data. But SQL Server 2005 and 2008 are eliminating those cases also with additions to SQL.
At the end of the day there is no single answer as to what you should replace a cursor with because there are many situations where they can be used when they shouldn't.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
