Category SQL SERVER

Configure MS SQL SERVER,MY SQL,MS ACCESS to run from Oracle SQL Developer

We can execute MS SQL SERVER,MY SQL,MS ACCESS from Oracle SQL Developer.Steps are given below 1.download Oracle SQL Developer from below link http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html 2.Oracle connection We can connect oracle by Host Name,Port and SID or by TNS details.                     3.MY SQL Server connection a.need to download […]

How to select the first row of each group in Oracle?

I have a table like this: ID | VAL | DATE ———————- 1 | 10 | 25/12/2013 1 | 2000 | 25/12/2012 2 | 5 | 25/12/2012 2 | 3 | 25/12/2013 I want to make a SELECT that will return just the first row for each ID, ordering by Date. Sample output: ID | […]

What is the difference between DELETE and TRUNCATE? Is one faster than the other?

DELETE logs the data for each row affected by the statement in the transaction log and physically removes the row from the file, one row at a time. The recording of each affected row can cause your transaction log grow massively if you are deleting huge numbers of rows. However, when you run your databases in […]

Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance There are three major theoretical differences between temporary tables: create table #T (…) And table variables: declare @T table (…) The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction […]

Count(*) and Count(columnName)

COUNT(*) returns the total number of rows in the table, while COUNT(expression) returns the number of rows where the result of the expression is not NULL. Naturally, COUNT(DISTINCT expression) means that duplicates are only counted once. This means that COUNT( ) can return different results depending on how you write it

SQL JOIN

–drop table t1,t2,t3 create table t1(f1 int)create table t2(f1 int)create table t3(f1 int)–=========================== set nocountondeclare @j intset @j = 1while @j <= 10begin insertinto t1 values(@j)set @j = @j + 1end –=========================== set nocountondeclare @k intset @k = 6while @k <= 15begin insertinto t2 values(@k)set @k = @k + 1end –=========================== insert into t3 values(6)insert […]

SQL Server Tips

    query based on Table:tblemp(eid,mid,sal)   Q:write a query to Select Manager Name and emplyee Name from tblemp table Ans:select e1.[name] ename,e2.[name] mnane from tblemp e1,tblemp e2 where e1.mid=e2.eid   Q:Write a query where employee earns more than their managers. Ans:select e1.[name] ename,e2.[name] mnane from tblemp e1,tblemp e2 where e1.mid=e2.eid and e1.sal>e2.sal     […]