SQL ADDA

Create a Table; Create table Test_tbl ( Id int, Name varchar (50) ) Create a clustered index on this table before creating a primary key constraint on it; Create Clustered Index Index_Name on Test_tbl(Name) Now create a primary key as below; Alter table Test_tbl add constraint Primary key (Id) Now check the type  of primary key Using this query as below;   Select object_name (object_id) as tablename, type_desc, Name, index_id, type from sys.indexes where object_name(object_id) = 'Test_tbl' 1. Clustered Index :   Clustered index is created only when both the following conditions satisfy –  The data or file, that you are moving into secondary memory should be in sequential or sorted order. There should be a key value, meaning it can not have repeated values.    Whenever you apply clustered indexing in a table, it will perform sorting in that table only. You can create only one clustered index in a table like primary k...

What is the difference between SQL, PL-SQL and T-SQL   SQL is a query language to operate on sets. It is more or less standardized, and used by almost all relational database management systems: SQL Server, Oracle, MySQL, PostgreSQL, DB2, Informix, etc. PL/SQL is a proprietary procedural language used by Oracle PL/pgSQL is a procedural language used by PostgreSQL T-SQL is a proprietary procedural language used by Microsoft in SQL Server. Procedural languages are designed to extend SQL's abilities while being able to integrate well with SQL. Several features such as local variables and string/data processing are added. These features make the language Turing-complete. 1. SQL  or Structured Query Language was developed by IBM for their product "System R". Later  ANSI  made it as a  Standard  on which all Query Languages are based upon and have extended this to create their own DataBase Query Language suits. The first standard was ...

Q - Delete duplicate data from a table. A - First of all create a table -  create table del_duplicate  (  id int ,  name varchar (15),  class varchar (15)  ) insert records :  insert into del_duplicate values ('1','Abhishek','IT')  insert into del_duplicate values ('1','Abhishek','IT')  insert into del_duplicate values ('2','Amit','Civil')  insert into del_duplicate values ('2','Amit','Civil') Query for delete duplicate data -: Method 1: SELECT DISTINCT id,name,class INTO    tempTable FROM    del_duplicate GO TRUNCATE TABLE del_duplicate DROP TABLE del_duplicate exec sp_rename 'tempTable', 'del_duplicate' Method 2:       SET NOCOUNT ON       SET ROWCOUNT 1       WHILE 1 = 1        BEGIN        DELETE   FROM del_duplicate       WHERE    id IN (SELECT  id   ...

Q- What is DBCC command in SQL sever. A - DBCC  (Database consistency checker ) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises. I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples. These are four types: Maintenance Informational Validation Miscellaneous                Maintenance Commands Performs maintenance tasks on a database, index, or file group. Syntax: DBCC  CLEANTABLE ( ‘DatabaseName’,’TableName.ColumnName’ ,0) 2.  DBREINDEX  – Builds one or more indexes for the table in the specified database. (Will be  removed  in the future version, use  ALTER INDEX  instead) Syntax: USE DatabaseName DBCC  DBREINDEX ( ‘TableName.ColumnName’,’PK_ID’ ,80) 3.  DROPCLEANBUFFERS  – Removes all clean buffers from buffer pool. Synta...

Q- Reverse a string without using reverse function. A-declare @Result varchar (max) declare @string varchar (50) = 'ABHISHEK'  DECLARE @i int         SET @Result=''     SET @i = 1     WHILE @i <= LEN(@string)     begin     SET @Result = SUBSTRING(@string,@i,1)+ @Result     SET @i=@i + 1 end print @Result  

Q- What is the difference between the  WHERE  and  HAVING  clauses? Ans-  When   GROUP BY   is not used, the   WHERE   and   HAVING   clauses are essentially equivalent. However, when  GROUP BY is  used: The  WHERE  clause is used to filter records from a result. The filtering occurs before any groupings are made. The  HAVING  clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).

Q- What is a key difference between Truncate and Delete? Ans-  Truncate is used to delete table content and the action can  not  be rolled back, whereas Delete is used to delete one or more rows in the table and  can  be rolled back and in delete we use where clause.