Vijay

• May 10th, 2012

SELECT ID FROM TBLSAMPLE GROUP BY ID OR SELECT DISTINCT (ID) FROM TBLSAMPLE

Code
 

Mally

• Feb 13th, 2012

Good Code...however the code will delete all the duplicates... you may want to add:

delete top(n) clause in your delete statement to exactly delete the required number of records.

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: Parished.D Chennai India

• Apr 24th, 2007

---

create table ee (eno int, ename varchar(200), sal int)

insert into ee values(1, 'a', 2000)
insert into ee values(2, 'b', 6000)
insert into ee values(3, 'c', 8000)

select ENO, ENAME, min(sal) AS SAL from ee group by eno,ename having min(sal) > 5000

Aman Gupta

• Jul 15th, 2024

SELECT Emp_Name, MIN(Salary) FROM Employee
GROUP BY Emp_Name
HAVING MIN(SALARY) > 5000

Arshad

• Jul 30th, 2023

SELECT ENO, SUM(SALARY)
FROM EE
GROUP BY ENO
HAVING SUM(SALARY) > 5000

Improve Answer

What is its purpose?How to use it?

Read Best Answer

Editorial / Best Answer

suji  

 Profile Answers by suji Questions by suji

• Member Since Sep-2005 | Nov 5th, 2011

---

Here are multiple ways to get the table information. The DESCRIBE command does not exist in MS SQL SERVER. This is an Oracle command used to describe the structure of objects within a given database. To achieve the same task in MSSQL Server, there are a series of stored procedures with the prefix SP_ that can be used. To view the structure of a table within the current database, use the command

Code
sp_help 'TABLE_NAME';

If you would like to see more details, Create your custom procedure

Code
span style="color: #ff0000;">'order'

Here is another alternative way to get the same information

Code
span style="color: #ff0000;">'TableName''Employee'

Here is the usage :

Code
span style="color: #ff0000;">'Department''sa''TableName'

Contributors for the editorial answer : Kewlshiva, srilakshmi.b, raaghav,kevaburg

Rooshi

• Oct 17th, 2014

Sp_help will give all the details about the table columns, indexes, partitions etc.

Saket Kale

• Oct 24th, 2012

Sp_help dbo.customers;

worked for me just fine.

Thanks to the OP, this runs perfectly on SQL 2008 R2, do not know about other versions

Improve Answer

asifeqbal

 Profile Answers by asifeqbal Questions by asifeqbal

• Mar 18th, 2015

Select * Into From Where 1=2

David Sarkar

 Profile Answers by David Sarkar Questions by David Sarkar

• Dec 12th, 2014

Select * Into New_table FROM Old_Table WHERE 1=0;

Answer Question  Select Best Answer

How will you fetch records having underscore ( _ ) in the middle of of strings, from columns having attributes as "amit_6", "t_mit06", "am_06", "m_t", "j_x_t" ?

Rohit_1987

 Profile Answers by Rohit_1987 Questions by Rohit_1987

• May 25th, 2010

Select * from tableName where colName LIKE '%'+'_'+'%'

DeepakMohapatra

 Profile Answers by DeepakMohapatra Questions by DeepakMohapatra

• Apr 15th, 2010

SELECT * FROM TableName WHERE ColumnName LIKE '%[_]%'

Answer Question  Select Best Answer

sushilmishra

 Profile Answers by sushilmishra Questions by sushilmishra

• Jun 8th, 2009

* Stored procedure contains all DML stmt to perform operation With permanent table where user define function does not contain insert, update, delete stmt to modify the permanent table.* Stored proc...

praveen34.tit

 Profile Answers by praveen34.tit Questions by praveen34.tit

• Apr 2nd, 2009

The major difference between procedure and function is that 1. Procedure can be compiled only once and vice versa. 2. Execution of procedure is faster then function 3. Function can be used in select Q...

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

santanukd  

 Profile Answers by santanukd Questions by santanukd

• Member Since May-2008 | May 14th, 2009

---

Just replace "[your-schema-name-here]" in the following query with your schema name. Hope it comes handy to some of you out there.

Select OBJECT_TYPE,OBJECT_NAME
FROM
(
Select 'TABLE' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWS
Union
Select 'VIEW' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWS
Union
Select 'INDEX[Type:Name:Table]' as OBJECT_TYPE, concat(CONSTRAINT_TYPE,' : ',CONSTRAINT_NAME,' : ',TABLE_NAME) as OBJECT_NAME,TABLE_SCHEMA AS OBJECT_SCHEMA from information_schema.TABLE_CONSTRAINTS
Union
Select 'Procedure/Functions' as OBJECT_TYPE, ROUTINE_NAME as OBJECT_NAME, ROUTINE_SCHEMA as OBJECT_SCHEMA from information_schema.ROUTINES
) R

Where R.OBJECT_SCHEMA=[your-schema-name-here]

Keer

• Apr 25th, 2012

Select * from sys.tables where type = U

saikiran

• Jul 22nd, 2011

Select * from sys.tables

Improve 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
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.

Code
 

Using Having

Code
 

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

Sudha

• Apr 2nd, 2018

NULL is not a data type. The key word NULL is used to mention the unknown or missing values of data.
For ex: while registering sign up form there are few fields which are not mandatory and if user didnt fill those non mandatory fields then those values we can save it as null in the data table.

Dhileep

• Oct 24th, 2017

We cant able to mention particular data type for null.

Answer Question  Select Best Answer

psingla

 Profile Answers by psingla Questions by psingla

• Mar 8th, 2010

Truncate:Deletes all the record from the table.keeps no entry in log file.so rollback is not possiblefast in operation than delete.Its a DDL statementReset the identity column value to 1 (specific to ...

vasumathi.sridharan

 Profile Answers by vasumathi.sridharan Questions by vasumathi.sridharan

• May 26th, 2009

Delete is a DML StatementTruncate is a DDL StatementDelete can be rolledbackTruncate cannot be rolledback The memory occupied by the table will be released in Truncate.That is Not the case with DeleteTruncate cannot use a where clauseDelete can use a where clauseTruncate is faster than Delete.

Answer Question  Select Best Answer

sreyas kvm

 Profile Answers by sreyas kvm Questions by sreyas kvm

• Feb 3rd, 2008

Yes we can generate....CREATE TRIGGER Tgr2Tab ON dbo.employee FOR INSERT AS INSERT INTO dbo.department(SELECT departmentno FROM INSERTED )

chauhan_anilit

 Profile Answers by chauhan_anilit Questions by chauhan_anilit

• Jan 10th, 2008

Usha G, your code is right but it only for first (SampleMainTable)   table and execute whn you insert a record in the SampleMainTable  tabe so its only for SampleMainTable  table

Answer Question  Select Best Answer

venkat.rbs

 Profile Answers by venkat.rbs Questions by venkat.rbs

• Feb 12th, 2008

We can define up to 1024 columns. in one table....And 253 forignkeys we can assign for 1 table...

sreyas kvm

 Profile Answers by sreyas kvm Questions by sreyas kvm

• Feb 3rd, 2008

Columns:1000Foreidn key:32

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: Hanif

• Apr 12th, 2006

---

we have to use the group by with having command to get the duplicate values. this query shall show the result of only the users have duplicate values in the employee table.

Syntex:
Select columnName From Table_name
Group By columnName
Having count (*) > 1

Example:
SELECT  UserID FROM employee
GROUP BY userid
HAVING count( * ) > 1

nandu

• Apr 26th, 2012

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Ramakant Sahoo

• Dec 24th, 2011

Code
span style="color: #ff0000;">"REPETED EMAIL"

Improve Answer

I was asked in an interview..."Suppose u have a field with UNIQUE KEY. can u leave two or more cells NULL in SQL Server 2005...If Yes then Why...if NOT then Why.....

slimtech

 Profile Answers by slimtech Questions by slimtech

• Feb 27th, 2009

In case of SQL Server : No and so is for DB2. But in case of Oracle : Yes.. Oracle supports multiple null values in the unique key column whereas SQL Server allows only one null value in the unique key column..

nirajkvinit

 Profile Answers by nirajkvinit Questions by nirajkvinit

• Dec 17th, 2008

Yes! We can have multiple values in a unique key based column. Because Null mean unspecified/undefined value. Which means, no two null values are the same.

Answer Question  Select Best Answer

shabanabegum

 Profile Answers by shabanabegum Questions by shabanabegum

• Dec 9th, 2009

Exec sp_rename 'oldtablename.oldcolmnname','newcolumnname'for example Exec sp_rename 'employee.DateofBirth','DOB'Thanks

vadukiya_piyush

 Profile Answers by vadukiya_piyush Questions by vadukiya_piyush

• May 21st, 2009

Exec sp_rename 'tablename.columnname','newcolumnname'

Answer Question  Select Best Answer

1 How is the error handling in stored proc of T-SQL2 What is clustered index and non-clustered index? How many clustered indexes and non-clustered indexes can be created in one table? 3-what is disconnected mode?

Pradeep Kumar Sharma

• Dec 25th, 2011

Begin Try

--
--
End Try
Begin Catch

Declare @errMsg varchar(1000),@errSev int
select @errMsg=error_message(),@errSev=error_severity()
RaisError(@errMsg,@errSev,1)

End Catch

kirangiet

 Profile Answers by kirangiet Questions by kirangiet

• Aug 17th, 2010

1) CLR is integrated in SQL Server 2005. Hence we can use below code for Exception Handling. BEGIN TRY SELECT * FROM Employees; END TRY BEGIN CATCH SELECT ERROR_NUMBER() ...

Answer Question  Select Best Answer

Can i update views in SQL if yes in what scenarios?
if your view is joining multiple tables

Ayushi

• May 7th, 2015

We can update simple views(having data from one table) and complex views(having data from multiple tables) but columns should be taken from one base table for update.

asifeqbal

 Profile Answers by asifeqbal Questions by asifeqbal

• Mar 18th, 2015

Yes, we can update view when view is created by single table (simple view). If we want to Update view that are based on multiple tables using Joins, Update does not work. So we used "Instead of Triggers" for updating Complex view.

Answer Question  Select Best Answer

cybersavvy

 Profile Answers by cybersavvy Questions by cybersavvy

• Feb 5th, 2009

Varchar2 in Oracle is equal to varchar in SQL Server. There is no varchar2 data type in SQL Server

smithanair

 Profile Answers by smithanair Questions by smithanair

• Sep 17th, 2008

VARCHAR is used to store alphanumeric values without padding unused memory locations. But VARCHAR2 is used to store alphanumeric values with padding unused memory locations. so using VARCHAR2 we are saving memory locations.

Answer Question  Select Best Answer

badri

• Dec 9th, 2015

Can I write a trigger within Stored Procedure?

psingla

 Profile Answers by psingla Questions by psingla

• Mar 19th, 2010

No.Its automatically fired on DML statements.these can not be triggered manually.

Answer Question  Select Best Answer

Why do we use alias for the table name? What is its importance?

psingla

 Profile Answers by psingla Questions by psingla

• Mar 3rd, 2010

Alias is a alternate name given to a table for multiple purposes.The scope of the Alias is within the queryAlias is?used to give short name to the table.Used when same table is used n number of times in a query. Alias is used in such queries to give unique name to each table reference.?

goksn

 Profile Answers by goksn Questions by goksn

• Sep 23rd, 2009

-is a shorthand notation-Will be used along with column name if same colum name present in more than one table (while joining those tables)-Must in self join

Answer Question  Select Best Answer