Difference between the querries.

I have two tables
1. AccountMaster(AccountID int, Name varchar(20))
2. Transactions(TransID, AccountID int, Amount float)

I have two queries for achieving the same result set. They are

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A
inner join Transactions T
on T. AccountID=A. AccountID

Here both will make use of inner join then,
Is there any difference in executing the queries ?
If yes, why SQL Server provides both the option ?

Quote:

Originally Posted by getmeidea

I have two tables
1. AccountMaster(AccountID int, Name varchar(20))
2. Transactions(TransID, AccountID int, Amount float)

I have two queries for achieving the same result set. They are

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A
inner join Transactions T
on T. AccountID=A. AccountID

Here both will make use of inner join then,
Is there any difference in executing the queries ?
If yes, why SQL Server provides both the option ?

I am not sure but one uses a cross join, the best thing for you is to look up joins, also it is mainly to do with speed issues. In some cases it may actually be nessecary to use a inner join that a cross join can't do or doesn't allow.|||

Quote:

Originally Posted by getmeidea

I have two tables
1. AccountMaster(AccountID int, Name varchar(20))
2. Transactions(TransID, AccountID int, Amount float)

I have two queries for achieving the same result set. They are

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

Query1:
Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A
inner join Transactions T
on T. AccountID=A. AccountID

Here both will make use of inner join then,
Is there any difference in executing the queries ?
If yes, why SQL Server provides both the option ?

Select A.AccountID,A.Name,T.TransID,T.Amount
from AccountMaster A, Transactions T
where T. AccountID=A. AccountID

this kind of qry may easy for join one or two tables with 1 or 2 fields, but if u go beyond that
you have to write many condition with combination in where clause
for join itself. some more conditions for selecting records after joining...

difference between FOR INSERT and AFTER INSERT triggers

I've been reading the docs and playing around, but I'm still not
getting the difference. For instance,

create table a(i int check(i>0))
create table a_src(i int)
go
create unique index ai on a(i) with IGNORE_DUP_KEY
go
insert into a_src values(1)
insert into a_src values(1)
insert into a_src values(2)
--insert into a_src values(-1)
go
create trigger a4ins on a
for insert
as
select * from inserted
go
create trigger afterins on a
after insert
as
select * from inserted
go
insert into a select * from a_src
go
drop table a
drop table a_src

I'm getting

i
----
1
2

(2 row(s) affected)

Server: Msg 3604, Level 16, State 1, Procedure a4ins, Line 4
Duplicate key was ignored.
i
----
1
2

(2 row(s) affected)

even the inserted quasi tables are identical.
If I uncomment insert into a_src values(-1), I'm getting

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint
'CK__a__i__58FC18A6'. The conflict occurred in database 'ABC_1COMPEE',
table 'a', column 'i'.
The statement has been terminated.

without any output from either trigger.
So,
in which situations will FOR INSERT be useful while AFTER INSERT won't
do?
in which situations will AFTER INSERT be useful while FOR INSERT won't
do?Ford Desperado (ford_desperado@.yahoo.com) writes:
> I've been reading the docs and playing around, but I'm still not
> getting the difference.

That is because there isn't any!

If memory servers, FOR was the original syntax. I suspect that AFTER
has been added to align with ANSI standards. FOR is not very precise,
where as AFTER tells us that the trigger fires after the statement.
That in difference to BEFORE and INSTEAD OF triggers. (Of which SQL
Server has the latter, but not the former.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks Erlandsql

difference

create table #temp(id int,col1 int,col2 int)
go
select id,col1[Field1],col2[Field2] from #temp
select id,col1 as [Field1],col2 as [Field2] from #temp
go
drop table #temp
go
Is there a difference between these 2 select statements in terms of the
column names and results returned to ado.net client.?
thxAFAIK, no.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||There is no difference. The client will see "id, Filed1, Filed2".
Perayu
"skg" <skg@.yahoo.com> wrote in message
news:uECDpaDTGHA.776@.TK2MSFTNGP09.phx.gbl...
>
> create table #temp(id int,col1 int,col2 int)
> go
> select id,col1[Field1],col2[Field2] from #temp
> select id,col1 as [Field1],col2 as [Field2] from #temp
> go
> drop table #temp
> go
> Is there a difference between these 2 select statements in terms of the
> column names and results returned to ado.net client.?
> thx
>|||Thanks all !!. We have an ado.net application which is working when we use
the As and not [] in the select statement.
I will investigate further and update you if i find something
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uMmP7jDTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> There is no difference. The client will see "id, Filed1, Filed2".
> Perayu
> "skg" <skg@.yahoo.com> wrote in message
> news:uECDpaDTGHA.776@.TK2MSFTNGP09.phx.gbl...
>sql

Diff Datatypes used doubt int and bigint ........SQL SERVER 2005......Any useful links ?

Hello Frdz,

I have doubt regarding the datatypes fields used in SQL SERVER 2005.

The value for bigint Int64 is 18

The value of int Int32 is 9/10

Now,if in int i write : 1234567890 (accepted)

This gives error : 9874565656 (not accepted........why is it so ? )

Why is it so ??

I want to know the perfect size of all the datatypes used in SQLSERVER 2005.

There are also smallint,tinyint....

What's the main difference with all of them ??

Can anyone provide me the nice links which can explain me what m i asking in this post...

Please help me...I want to know all the datatypes used differences...

you cannot store 9874565656 in an int as it exceeds the max value for an int which is: 2,147,483,647

http://msdn2.microsoft.com/en-us/library/ms187745(SQL.90).aspx

|||

thanxs...i think it's helpful link..

I want to also know that in SQL SERVER 2005 can we assign or fix manually the values upto the limit...like,

Id1 int - 4

Id2 bigint - 10

like in varchar we can do

Name Varchar(20) if we take varchar(50) as datatype...

Hope u understand what i ask...this questions are not solved in my mind...

Please help me...

Thanxs again....

|||

varchar is a variable length datatype and you are allowed to set its length

you cannot do that with the integer datatypes.

|||

If you really intend to limit the value <= 4 digits ( = 9999) you can create a constraing on the column to make sure the value <= 9999.

|||

You can also create your own datatype that is based on an integer value type that can store all your possible values and has a constraint.

This may help:

http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/07/426930.aspx

More complex datatype needs can be done via a CLR UDT, but I'm not sure how well they perform. Like:

http://www.devx.com/dotnet/Article/22644

|||

thanxs all of...

I think it's better to make a constraint......

Nice answers to clear my doubt...