-
How to use roll back query in SQL server 2008
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...
-
Difference between a "where" clause and a "having" clause?
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 codeCode- span style="color: #ff0000;">'CA', 'LA'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.
Using HavingCodeCode
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 ...
General Functions in SQL
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
- 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...
I have a table in sql server with two fields named 'start_time' and 'stop_time'. both fields have datatype set as 'datetime'. now I want to subtract the 'start_time' from 'stop_time'? I have tried datediff() fuction,but no use. I want the correct outpput of date as well as time . Need help as early as possible.
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
- Jan 15th, 2009
Select starttime,stoptime,datediff(day,starttime,stoptime) as newtime from date.try this...........
What are the advantages of sql server 2005 over sql server 2000/ sql server 7.0 ?
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.
What is a Co-related query in databases?
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
- 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.
How to delete records from two tables. There is relationship between two tables.
It's working in MySQL but not in SQL-Server.
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
- 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
How to find out the no of connections in SQL server?
Rename Database
How can we rename a Database with query?
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
What is the effect of 'delete' on primary key and foreign key
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
- 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...
What is the Disadvantage of StoredProcedure?
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.
Clustered & Non Clustered Indexes
What is difference between clustered & non clustered Indexes?
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
- 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 ...
How do I lock a column
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
- 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.
DB Mirroring
What are the steps to configure DB mirroring?
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.
Insert Multiple Rows
How to insert multiple rows in a single insert query?
Store Images in SQL Server 2005
How to Store Images in SQL Server 2005?
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.
Data Reader or Dataset
If you are doing website project, which one is used dataset or data reader? Why?
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
- 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...
Transaction Isolation Level
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
- 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
- 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 ...
Explain real time situation where you would use clustered and Non-clustered Indexes?
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...
SQL Server Transactions
Why Transactions are needed in SQL Server?
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
- 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...
Read Best Answer
Ans