suji

 Profile Answers by suji Questions by suji

• Nov 6th, 2011

Here is how you can use rollback in any of the SQL Server versions. works in both SQL Server 2000 and later versions. "sql Begin tran insert into table values or updates rollb...

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: Ankush Sharma

• Sep 14th, 2011

---

Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:

1. The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.

for Example: if for an "Select" statement we use the "where" clause then the the result based on the "where" condition results and then we can use "group by" clause to arrange in some order, Now if we want to impose the condition on that group then we use "having" clause.

The main reason for using WHERE clause is to select rows that are to be included in the query. For example, assume table Test.Suppose I want the names, account numbers, and balance due of all customers from California and Los Angles. Since STATE is one of the fields in the record format, I can use WHERE to select those customers.

Using the code

Code
SELECT cusnum, lstnam, init
FROM Test
WHERE state IN ('CA', 'LA')
 
CUSNUM LSTNAM INIT BALDUE
====== ============ ==== ========
938472 John G K 37.00
938485 Mark J A 3987.50
593029 Lily E D 25.00

Suppose I want the total amount due from customers by state. In that case, I would need to use the GROUP BY clause to build an aggregate query.

Code
SELECT state,SUM(baldue)
FROM Test
GROUP BY state
ORDER BY state
 
State Sum(Baldue)
===== ===========
CA 250.00
CO 58.75
GA 3987.50
MN 510.00
NY 589.50
TX 62.00
VT 439.00
WY .00

Using Having

Code
SELECT state,SUM(baldue)
FROM Test
GROUP BY state
HAVING SUM(baldue) > 250
 
 
State Sum(Baldue)
===== ===========
GA 3987.50
MN 510.00
NY 589.50
VT 439.00

Ankush Sharma

• Sep 14th, 2011

Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between ...

Ankush Sharma

• Sep 14th, 2011

Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between ...

Improve Answer

What are the general functions available in SQL?

aghiad

• Sep 11th, 2011

The General function are use in SQL 1-NVL (exp1,exp2) 2-NVL2 (exp1,exp2,exp3) 3-NULLIF (exp1,exp2) 4-COALESCE (exp1,exp2,exp3,......,expn) For example: "sql SELECT last_name...

ElavarasiGophannan

 Profile Answers by ElavarasiGophannan Questions by ElavarasiGophannan

• Feb 11th, 2009

Aggregate Functions: AVG() - Returns the average value  COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest...

Answer Question  Select Best Answer

satheyaraaj

 Profile Answers by satheyaraaj Questions by satheyaraaj

• Jul 9th, 2011

Select DATEDIFF(MI,FromTime,ToTime) From ShiftMaster  -- between no of minutesselect DATEDIFF(HH,FromTime,ToTime) From ShiftMaster -- between no of Hours

swathi660

 Profile Answers by swathi660 Questions by swathi660

• Jan 15th, 2009

Select starttime,stoptime,datediff(day,starttime,stoptime) as newtime from date.try this...........

Answer Question  Select Best Answer

gvs

 Profile Answers by gvs Questions by gvs

• Jan 3rd, 2008

SQL Server 2005 has reduced application downtime, increased scalability and performance, and tight yet flexible security controls. SQL Server 2005 makes it simpler and easier to deploy, manage, and op...

chandrasekhar

• May 17th, 2007

Sql server 2005 is exclusively designed for dot net developers. what ever the function or procedure that we write in  dot net environment those function or procedure that  we direcly exucute in sql server environment. this is the new feature in sql 2005.

Answer Question  Select Best Answer

sakthi.jaganathan

 Profile Answers by sakthi.jaganathan Questions by sakthi.jaganathan

• Aug 25th, 2008

CO-RELATED Query: It used to find the exits or doesnot exits value. It has two select statement, the outer select statement will execute first and then the inner select statement execute.There is no need of relation between the two select statement.

srinu.tenali

 Profile Answers by srinu.tenali Questions by srinu.tenali

• Dec 14th, 2007

the sub query is executed more than one time i.e,the inner query depends on outer query output is called co-related query.

Answer Question  Select Best Answer

It's working in MySQL but not in SQL-Server.

Santhoshkandula

 Profile Answers by Santhoshkandula Questions by Santhoshkandula

• May 18th, 2011

  For deleting the records from the relational tables there are 4 options available. These options can be used when you create the tables then you have to use these options and create tables and ...

caveatashish

 Profile Answers by caveatashish Questions by caveatashish

• Dec 14th, 2007

Use a procedure which will delete record from child table first and then it will delete the same record from parent tableSame thing can be achieved using cascade delete and cascade update, this feature was not there in sql server 7.0

Answer Question  Select Best Answer

ahmeds08

 Profile Answers by ahmeds08 Questions by ahmeds08

• May 30th, 2011

Sp_who2

Phani Kiran Gullapalli

 Profile Answers by Phani Kiran Gullapalli Questions by Phani Kiran Gullapalli

• May 21st, 2011

Use SP_WHO2

Answer Question  Select Best Answer

How can we rename a Database with query?

Priya Lakshmi

 Profile Answers by Priya Lakshmi Questions by Priya Lakshmi

• May 25th, 2011

Sp_rename 'old_db_name','new_db_name';

dinu_26

 Profile Answers by dinu_26 Questions by dinu_26

• Nov 11th, 2008

Syntax of renaming the database:sp_renamedb old_database name new_database nameEg:? name of the database is tree. Now if you need to change the database as three. So the query for changing the database as follows:sp_renamedb tree three

Answer Question  Select Best Answer

Santhoshkandula

 Profile Answers by Santhoshkandula Questions by Santhoshkandula

• May 18th, 2011

When you have to delete the records on primary key and foreigh key then there is problem will hapen as primary key violation at that time you have to provide delete rules when you create the tablesthose are1. on delete no action2. on delete cascade3. on delete set null4. on delete set default

caveatashish

 Profile Answers by caveatashish Questions by caveatashish

• Dec 14th, 2007

For primary key if that record is being refered by some other (Child Table)  sql server won't let you to delete that record.While creating relationship if you had mention "Dele...

Answer Question  Select Best Answer

anjan_maity

 Profile Answers by anjan_maity Questions by anjan_maity

• Aug 7th, 2008

When a procedure is created a execution plan is created, proc is executed according to that execution plan. When we modify the proc it also follow this execution plan.When index is rebuild, new index ...

Guest

• Jan 10th, 2007

It is possible that the statistics have changed since a stored procedure was compiled and thus may be executing a less the optimal executionplan, thus it may run slower.

Answer Question  Select Best Answer

What is difference between clustered & non clustered Indexes?

itmasterw

 Profile Answers by itmasterw Questions by itmasterw

• Dec 28th, 2010

In general you put a clustered index on the key columns that you will be querying on and you can put a non clustered index on other key columns in the select statement. But you need to really look at ...

psingla

 Profile Answers by psingla Questions by psingla

• Nov 8th, 2010

The index in the sql server are in the form of btree having root and leaf nodes.The root node contains the column key value contained in the index and the leaf node contains the table data or pointer ...

Answer Question  Select Best Answer

How do I lock a column, once records are entered into the table they can not be edited. Also how can I lock the field where only certain values are accepted?

binita007

 Profile Answers by binita007 Questions by binita007

• Dec 21st, 2010

For this purpose, Views are there in SQL Server.You can Create a view by leaving the column you need to restrictUser can edit all the fields in the view except the one you restrict .Binita.

Kiran Patil

 Profile Answers by Kiran Patil Questions by Kiran Patil

• Mar 26th, 2008

May be you can use Constraints.

Answer Question  Select Best Answer

What are the steps to configure DB mirroring?

sthakk3

 Profile Answers by sthakk3 Questions by sthakk3

• Dec 9th, 2010

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.Database mirroring is a primarily software solution for increasing database availability.

Answer Question  Select Best Answer

How to insert multiple rows in a single insert query?

psingla

 Profile Answers by psingla Questions by psingla

• Nov 8th, 2010

Commas are getting deleted here, so put commas between columns and values.

psingla

 Profile Answers by psingla Questions by psingla

• Nov 8th, 2010

1)insert into table (col1,col2) values (val1,val2),(val3,val4)2)insert into table (col1,col2)select val1,val2union allselect val3,val4

Answer Question  Select Best Answer

How to Store Images in SQL Server 2005?

vigneshmca

 Profile Answers by vigneshmca Questions by vigneshmca

• Sep 9th, 2010

You can store images by BLOB (Binary Large object). Conversion should take place in front-end?or SQL supports image as datatype. Try it.

Answer Question  Select Best Answer

If you are doing website project, which one is used dataset or data reader? Why?

kirangiet

 Profile Answers by kirangiet Questions by kirangiet

• Aug 17th, 2010

There is no hard and fast rule like if it is a Web Site project we have to use Data Reader/ Dataset. Its depends on requirement. We can use both of them. We need to understand the pros and cons of bot...

sreyas kvm

 Profile Answers by sreyas kvm Questions by sreyas kvm

• Feb 3rd, 2008

Data reader is used to fetch data from a databaseaccording to users condition.Data reader is used with the select statement.For Eg.to selecting a value from a database in our project, in c#.net w...

Answer Question  Select Best Answer

What is a transaction isolation level and What is the advantage of it? What are different options that we set for a transaction isolation level?

Transaction isolation levels are used when we go for transactions in stored procedures used in Transact SQL.

kirangiet

 Profile Answers by kirangiet Questions by kirangiet

• Aug 17th, 2010

Isolation levels in SQL Server 2008Read UncommittedRead Committed Snapshot*Read CommittedRepeatable ReadSnapshot*Serializable* The two new Isolation levels are based on Optimistic Concurrency Model.Note: Optimistic concurrency uses row versioning instead of row locking.

myelton

 Profile Answers by myelton Questions by myelton

• Jul 8th, 2008

You need to add Snapshot to this answer.  From BOL:Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start ...

Answer Question  Select Best Answer

kirangiet

 Profile Answers by kirangiet Questions by kirangiet

• Aug 17th, 2010

Cluster Index should be created on unique columns. By default it will be created on Primary Key. We can change it to any other column. Note: We can create Clustered Key on Non-Unique column also....

RAGHUKANTH

• Aug 21st, 2007

Yes, coming to your answer clustered index is used and applied only and primary keysFor example if a table student is created consider here in this table student id is the primary key so clustered ind...

Answer Question  Select Best Answer

Why Transactions are needed in SQL Server?

kirangiet

 Profile Answers by kirangiet Questions by kirangiet

• Aug 17th, 2010

SQL Transactions ensure to follow ACID Properties.A-Atomicity: Using Transactions, either none or all the statements inside the transaction will execute successfully.C-Consistency: Using Transact...

sanjaykp

 Profile Answers by sanjaykp Questions by sanjaykp

• Mar 12th, 2010

To guarantee the authenticity and integrity of any change to data in a database(s), be it an insert, update or delete. Transactions are a fundamental tool used by all industry standard Relation Databa...

Answer Question  Select Best Answer