Difference between SET and SELECT

Hii,
Anybody knows the difference between the SET and SELECT statement while assigning variables

Quote:

Originally Posted by sukeshchand

Hii,
Anybody knows the difference between the SET and SELECT statement while assigning variables

Hi

declare @.i int
set @.i=1
This s used to assign constant values

select @.i=max(column_name)from table_name
for ex.
select @.i=max(emp_id) from table_emp

This abve stmnt assign the max of the column value to the variable @.i|||

Quote:

Originally Posted by davash6

Hi

declare @.i int
set @.i=1
This s used to assign constant values

select @.i=max(column_name)from table_name
for ex.
select @.i=max(emp_id) from table_emp

This abve stmnt assign the max of the column value to the variable @.i

we can also use select statement to assign constants like
Select @.i=1 from tableEmp|||We can assign more than one value by using Select Statement but in SET we cant
Eg:
Select @.i=Mark1, @.j=Mark2 from Marks where SID=12323

but if we use SET statement We must write two different statement to do that
like

SET @.i=Select Mark1 From Marks where SID=12323
SET @.j=Select Mark2 From Marks where SID=12323

so i think the select statement is fast in this case

anybody have any other comments??? then pls post...|||SELECT:
Just selects data from a table, and does not change the values in the actual table.
so you see a view of the data.

select * from table1
see all data is table1

select * from table1
order by col01
see all data in table1 with the data ordered by col01.
so your view is different to the actual table but the table is unchanged.

SET:
this is used to change values in tables etc.

example 1:
update table1
set col01 = '23' where col01 = '21'
So set all instances of col01 to value '23' where it currently = '21'

example 2:
declare @.variable as int
set @.variable = '123'
create a variable called @.variable and store value '123' in it.

difference between a SQL:BatchCompleted and SQL:StmtCompleted eve

When i execute a SQL Statement and trace it through the profiler.. why there
is a differnce in counters for SQL:BatchCompleted and SQL:StmtCompleted for
a single SQL Statement?
For example
the statement
select * from office
SQL:StmtCompleted : CPU->0 , Read -> 10 , Write->0 , Duration->0
SQL:BatchCompleted : CPU->0 , Read -> 24 , Write->0 , Duration->13I may be way off, but there can be more than one statement per batch...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Mahesh" <Mahesh@.discussions.microsoft.com> wrote in message
news:A24FAD9F-138D-42A1-9F9D-97D96F7EBE6A@.microsoft.com...
> When i execute a SQL Statement and trace it through the profiler.. why
> there
> is a differnce in counters for SQL:BatchCompleted and SQL:StmtCompleted
> for
> a single SQL Statement?
> For example
> the statement
> select * from office
> SQL:StmtCompleted : CPU->0 , Read -> 10 , Write->0 , Duration->0
> SQL:BatchCompleted : CPU->0 , Read -> 24 , Write->0 , Duration->13
>|||Mahesh wrote:
> When i execute a SQL Statement and trace it through the profiler..
> why there is a differnce in counters for SQL:BatchCompleted and
> SQL:StmtCompleted for a single SQL Statement?
> For example
> the statement
> select * from office
> SQL:StmtCompleted : CPU->0 , Read -> 10 , Write->0 , Duration->0
> SQL:BatchCompleted : CPU->0 , Read -> 24 , Write->0 , Duration->13
SQL:BatchCompleted is for the entire batch, which could be more than one
statement. SQL:StmtCompleted is for a single statement.
The duration counter in SQL Trace is not super accurate. You can
download a more accurate timer from SQLDev.net (for testing). However,
there is more to do to complete a batch than a single statement, even if
what the end user sees is pretty much the same.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||You expect a batch to be larger because it is a collection of statements
submitted together.
Your SQL:StmtCompleted may only be the call to a stored proc (and if you are
not capturing SP:StmCompleted events you will not capture the granular level
of calls within the proc). The SQL:BatchCompleted is an event for the who
batch.
HTH,
John Scragg
"Mahesh" wrote:

> When i execute a SQL Statement and trace it through the profiler.. why the
re
> is a differnce in counters for SQL:BatchCompleted and SQL:StmtCompleted f
or
> a single SQL Statement?
> For example
> the statement
> select * from office
> SQL:StmtCompleted : CPU->0 , Read -> 10 , Write->0 , Duration->0
> SQL:BatchCompleted : CPU->0 , Read -> 24 , Write->0 , Duration->13
>|||My guess is that batch contains some degree of compilation time, as compilat
ion is made at the batch
level, not the statement level. I'd be surprised if SQL Server could keep tr
ack of compile time per
statement in a batch. Of course, batch can include other things as well, lik
e meta-data access etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mahesh" <Mahesh@.discussions.microsoft.com> wrote in message
news:A24FAD9F-138D-42A1-9F9D-97D96F7EBE6A@.microsoft.com...
> When i execute a SQL Statement and trace it through the profiler.. why the
re
> is a differnce in counters for SQL:BatchCompleted and SQL:StmtCompleted f
or
> a single SQL Statement?
> For example
> the statement
> select * from office
> SQL:StmtCompleted : CPU->0 , Read -> 10 , Write->0 , Duration->0
> SQL:BatchCompleted : CPU->0 , Read -> 24 , Write->0 , Duration->13
>

Differ bet WHERE clause & INNER JOIN?

In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set.

In layman's terms, what is the difference? Any examples? Thanks in advance.

ddaveSo, I saw the question about Inner Join vs Where in Queries. I'm using all the "Joins" in clause where, is there problem to do it or I should be using Inner Join ?|||Linking tables should be done using a JOIN clause. Filtering on individual data elements should be done with WHERE clauses.

For simple queries, the SQL Server Optimizer will convert a statement like

select * from a, b where a.key = b.key

into

select * from a inner join b on a.key = b.key

...and will make optimium use of the indexes on the two tables to create an efficient execution plan. For more complicated queries the optimizer may not be able to make this conversion.

The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.|||Originally posted by blindman
...and will make optimium use of the indexes on the two tables to create an efficient execution plan. For more complicated queries the optimizer may not be able to make this conversion.


Ya think? Not so sure...but youcan test it and check out the plan the optimizer chooses by looking at the query plan

The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.

100%|||Not so sure it will reevalutate the simple query, or not so sure it won't be able to evaluate a complex query?|||I'm not sure that it will give you 2 different plans...have you seen this?|||It won't for simple queries. I've heard that it might not be able to for complex queries. At some point of complexity there must be a statement that it is beyond its capacity to reduce to simple join clauses. I mean, complexity is theoretically infinite, right? I suspect that it may have difficulty reevaluating WHERE clauses containing conditional syntax, but I haven't formally tested it.sql

Diff. performance in Query Analyzer than when using stored procedure

Hi group,

I have a select statement that if run against a 1 million record
database directly in query analyzer takes less than 1 second.
However, if I execute the select statement in a stored procedure
instead, calling the stored proc from query analyzer, then it takes
12-17 seconds.

Here is what I execute in Query Analyzer when bypassing the stored
procedure:

USE Verizon
GO
DECLARE @.phonenumber varchar(15)
SELECT @.phonenumber = '6317898493'
SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance

FROM BadDebt
WHERE (Telephone_Number = @.phonenumber) OR (Telephone_Number_Redef =
@.phonenumber)
order by Service_Connection_Date desc

RETURN
GO

Here is what I execute in Query Analyzer when calling the stored
procedure:

DECLARE @.phonenumber varchar(15)
SELECT @.phonenumber = '6317898493'
EXEC Verizon.dbo.baddebt_phonelookup @.phonenumber

Here is the script that created the stored procedure itself:

CREATE PROCEDURE dbo.baddebt_phonelookup @.phonenumber varchar(15)
AS

SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance

FROM BadDebt
WHERE (Telephone_Number = @.phonenumber) OR (Telephone_Number_Redef =
@.phonenumber)
order by Service_Connection_Date desc

RETURN
GO

Using SQL Profiler, I also have the execution trees for each of these
two different ways of running the same query.

Here is the Execution tree when running the whole query in the
analyzer, bypassing the stored procedure:

------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([Verizon].[dbo].[BadDebt]))
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Concatenation
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),
SEEK:([BadDebt].[Telephone_Number]=[@.phonenumber]) ORDERED FORWARD)
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),
SEEK:([BadDebt].[Telephone_Number_Redef]=[@.phonenumber]) ORDERED
FORWARD)
------------

Finally, here is the execution tree when calling the stored procedure:

------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@.phonenumber] OR
[BadDebt].[Telephone_Number_Redef]=[@.phonenumber]))
|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),
1, 10)))
|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))
------------

Thanks for any help on my path to optimizing this query for our
production environment.

Regards,

Warren Wright
Scorex Development Teamwarren.wright@.us.scorex.com (Warren Wright) wrote in message news:<8497c269.0308051401.2e65bb80@.posting.google.com>...
> Hi group,
> I have a select statement that if run against a 1 million record
> database directly in query analyzer takes less than 1 second.
> However, if I execute the select statement in a stored procedure
> instead, calling the stored proc from query analyzer, then it takes
> 12-17 seconds.

<snip
One possible reason is parameter sniffing - see here:

http://groups.google.com/groups?sel...7&output=gplain

Simon|||sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0308060118.46c12f2e@.posting.google.com>...
> One possible reason is parameter sniffing - see here:
> http://groups.google.com/groups?sel...7&output=gplain
> Simon

Wow. Thats a bit of an eye opener. It makes me wonder how best to
make sure a decent plan is chosen by SQL Server, and the answer seems
to be to make it recompile the stored procedure every time ?

or is there a way to make SQL simply use the index at all times? I'd
hate to spend a lot of time on my dev machine getting the stored
procedure to run correctly on a million record table, only to port it
to my production machine and have it take forever on the 33 million
record database because of some magically crafted execution plan :-)

Thanks,

Warren|||Here is something else that I don't understand. The stored procedure
I listed above compares a phone number that is passed in against a
Telephone_Number column that is 15 digits long, and against a computed
column (Telephone_Number_Redef), that is the left 10 digits of the
Telephone_Number column.

This is because sometimes our client passes in a 10 digit number, and
sometimes a 15 digit version that includes some check digits on the
end (Don't ask).

Anyway, in the execution plan for when the stored proc is executing, I
see the following:

----------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@.phonenumber] OR
[BadDebt].[Telephone_Number_Redef]=[@.phonenumber]))
|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),
1, 10)))
|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))
----------

It appears to be recomputing the Telephone_Number_Redef column values
on the fly, instead of using the values already present. The
Telephone_Number_Redef column is indexed specifically to allow that
second comparison in the WHERE statement to be a SARG, but it seems
this is being ignored.

Is it being ignored because SQL had already decided to do a table
scan, and so though it might as well speed things up by not scanning
both columns? or is SQL doing a table scan because it thinks it needs
to re-compute the values for Telephone_Number_Redef on the fly?

Argh.

Thanks,

Warren Wright
Scorex Development Team
Dallas|||More follow-up on this issue, to help you experts analyze what's going
on here.

I've spent the day trying various things, with no success. I tried
using hints to suggest that the index on Telephone_Number_Redef be
used, which results in an error stating the stored procedure couldn't
be executed due to an unworkable hint.

I've tried declaring a new variable in the stored proc with a value
set equal to the @.phonenumber input, so SQL couldn't optimize based on
the actual value being passed in.

I've tried changing the index for the computed column to be a
clustered index.

I only wish I could simply tell SQL to use the same execution plan it
uses when I run the query from the analyzer!! All problems would be
solved!

No matter what, if I run the query from query analyzer, the response
time is a few milliseconds. If I run the stored procedure, the
response time is at least 17 seconds due to a completely suboptimal
execution plan (where the Telephone_Number_Redef's index isn't used at
all).

Introducing the new version of the stored proc with the OR statement
that checks against the computed column as well bogs down the
production server, and results in timeouts and app errors for our
client.

Frustrated,

Warren|||[posted and mailed, please reply in news]

Warren Wright (warren.wright@.us.scorex.com) writes:
> Here is something else that I don't understand. The stored procedure
> I listed above compares a phone number that is passed in against a
> Telephone_Number column that is 15 digits long, and against a computed
> column (Telephone_Number_Redef), that is the left 10 digits of the
> Telephone_Number column.

Computed column? Which you have an index on? Aha!

While Bart's article on parameter sniffing is good reading it is not
the answer here. Index on computed columns (as well on views) can
only be used if these SET options are ON: ANSI_NULLS, QUOTED_IDENTIFIER,
ANSI_WARNINGS, ARITHABORT, ANSI_PADDING and CONCAT_NULLS_YIELDS_NULL.
And NUMERIC_ROUNDABORT be OFF.

The killer here is usually QUOTED_IDENTIFIER. That option, together
with ANSI_NULLS is saved with the procedure, so that the run-time
setting does not apply, but the setting saved with the procedure.
QUOTED_IDENTIFIER is ON by default with ODBC and OLE DB, as well
with Query Analyzer. But OSQL and Enterprise Manager turns it off.
So you need to make sure that the procedure is created with
QUOTED_IDENTIFIER on.

You can review the current setting with

select objectproperty(object_id('your_sp'), 'IsQuotedIdentOn')

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Diff Result Between SQL 7 & SQL 2000

Hi.

When I execute the following sql statement in SQL 2000 DTS or query analyzer, I received the following error message. I used [ ] because my field names have spaces in between.

The same sql statement ran perfect in SQL 7. What could be wrong? Please advise. Thanks a million.

Update SAPvsSQL set [Sales Organisation] = sales_org,
[Value Out By] = val_diff, [Qty Out By] = qty_diff

Error message:

Server: Msg 16882, Level 11, State 1, Procedure sp_runwebtask, Line ...

SQL Web Assistant: Web task not found. Verify the name of the name for possible errors.

Best regardsYou sure you just didn't execute all of the code in the QA window, and you're executing something you don't see?|||And also, what are you setting the columns to?

Those aren't local variables...is there more to the statement?|||Originally posted by Brett Kaiser
And also, what are you setting the columns to?

Those aren't local variables...is there more to the statement?

Hi,

Thank you for your email reply.

Those in [ ] were actual field names in my database table. I used them for display purposes so instead of showing Sales_organation, I display Sales Organisation, Value Out By instead of val_diff and finally Qty Out By instead of qty_diff (look more meanful to the users).

You are right that I didn't show all the script in my previous message because they worked ok until this line. I am just being curious why the same script work PERFECT in SQL 7 but not in SQL 2000. I have been running this same set of script for more than a year and still running fine as at this morning. Reason why I am trying it on SQL 2000 because we will be moving to SQL 2000 totally by end this month.|||No problems here...your doing something else wrong...

USE Northwind
GO

CREATE TABLE SAPvsSQL (
[Sales Organisation] varchar(10)
, [Value Out By] varchar(10)
, [Qty Out By] varchar(10)
)
GO

DECLARE @.Sales_Org varchar(10), @.val_diff varchar(10), @.qty_diff varchar(10)

SELECT @.Sales_Org = 'A', @.val_Diff = 'B', @.qty_diff = 'C'

INSERT INTO SAPvsSQL ([Sales Organisation], [Value Out By], [Qty Out By])
SELECT 'X', 'Y', 'Z'

SELECT * FROM SAPvsSQL

UPDATE SAPvsSQL
SET [Sales Organisation] = @.sales_org
, [Value Out By] = @.val_diff
, [Qty Out By] = @.qty_diff

SELECT * FROM SAPvsSQL
GO

DROP TABLE SAPvsSQL
GO

Diakrieten worden niet opgeslagen

Hi,
When I use the update statement "UPDATE tablename SET field =3D
'As=FBca' WHERE fieldid =3D 1" will generate in the field "Asuca". How
is that possible?
When I cut and paste the field within Enterprise Manager the field
looks like "As=FBca".
So... whats the problem?
Thanks,
Ron
Diver
Try
UPDATE tablename SET field =N'Asca' WHERE fieldid = 1
"Diver" <rnooit@.hotmail.com> wrote in message
news:1121756360.293341.217730@.g47g2000cwa.googlegr oups.com...
Hi,
When I use the update statement "UPDATE tablename SET field =
'Asca' WHERE fieldid = 1" will generate in the field "Asuca". How
is that possible?
When I cut and paste the field within Enterprise Manager the field
looks like "Asca".
So... whats the problem?
Thanks,
Ron
|||Uri,
Thanks. But is there any sqlserver setting that i can set for the whole
database?
Ron

Diakrieten worden niet opgeslagen

Hi,
When I use the update statement "UPDATE tablename SET field =3D
'As=FBca' WHERE fieldid =3D 1" will generate in the field "Asuca". How
is that possible?
When I cut and paste the field within Enterprise Manager the field
looks like "As=FBca".
So... whats the problem?
Thanks,
RonDiver
Try
UPDATE tablename SET field =N'Asca' WHERE fieldid = 1
"Diver" <rnooit@.hotmail.com> wrote in message
news:1121756360.293341.217730@.g47g2000cwa.googlegroups.com...
Hi,
When I use the update statement "UPDATE tablename SET field =
'Asca' WHERE fieldid = 1" will generate in the field "Asuca". How
is that possible?
When I cut and paste the field within Enterprise Manager the field
looks like "Asca".
So... whats the problem?
Thanks,
Ron|||Uri,
Thanks. But is there any sqlserver setting that i can set for the whole
database?
Ron

Diakrieten worden niet opgeslagen

Hi,
When I use the update statement "UPDATE tablename SET field =3D
'As=FBca' WHERE fieldid =3D 1" will generate in the field "Asuca". How
is that possible?
When I cut and paste the field within Enterprise Manager the field
looks like "As=FBca".
So... whats the problem?
Thanks,
RonDiver
Try
UPDATE tablename SET field =N'Asûca' WHERE fieldid = 1
"Diver" <rnooit@.hotmail.com> wrote in message
news:1121756360.293341.217730@.g47g2000cwa.googlegroups.com...
Hi,
When I use the update statement "UPDATE tablename SET field ='Asûca' WHERE fieldid = 1" will generate in the field "Asuca". How
is that possible?
When I cut and paste the field within Enterprise Manager the field
looks like "Asûca".
So... whats the problem?
Thanks,
Ron|||Uri,
Thanks. But is there any sqlserver setting that i can set for the whole
database?
Ron

Devide by zero error

help please.
I have sql statement that cast two date range into decimal and then devive
by one another.
When I compile this code I get error: Source: Microsoft OLE DB provider for
sql
Error desc: Devide by zero error encountered.
How would I resolve this issue?Please disregard. I found a thread that already had the answer.
search for : Divide by Zero
"ITDUDE27" wrote:

> help please.
> I have sql statement that cast two date range into decimal and then devive
> by one another.
> When I compile this code I get error: Source: Microsoft OLE DB provider fo
r
> sql
> Error desc: Devide by zero error encountered.
> How would I resolve this issue?

determining the where statement

Hello,

Does anyone know how I can write the where statement according to the value?
In other words,
a command name would determine the where statement but the select statement is always the same.

This does not work:

create procedure new_procedure
(@.cid int, @.command VarChar(25))
Select * from a table
If @.command = 'pick1' begin
Where this = @.cid and that = 'Yes'
End
Else If @.command = 'pick2' begin
Where this = @.cid and that = 'No'
End
Else If @.command = 'pick3' begin
Where this = @.cid and that = 'No' and datefield >= DateAdd(d, -5, getdate())
End

something like that there.
Any ideas?
Thanks
Ehow many distinct values for @.command do you see ? is that something fixed like 3-4 options or would you have plenty of them..if you have only 3-4 options like you have above then you can do something like:

create procedure new_procedure
(@.cid int, @.command VarChar(25)) AS
begin
If @.command = 'pick1'
 select * from table Where this = @.cid and that = 'Yes'
Else If @.command = 'pick2'
 select * from table Where this = @.cid and that = 'No'
Else If @.command = 'pick3'
 select * from table Where this = @.cid and that = 'No' and datefield >= DateAdd(d, -5,
 getdate())
end

HTH