Using Try Catch And Exception Handling in Procedures

CREATE PROCEDURE usp_InsertTest            
(                  
 @Name varchar(50),    
 @Add varchar(50),    
 @Sal int ,    
 @ErrMsg varchar(4000) output      
 )                  
AS      
declare @Err varchar(2000)
Begin
 begin try
  begin transaction    
  insert into Tbltest([Name],[Add],Sal) values(@Name,@Add,@Sal)      
  commit transaction  
 end try
 begin catch
  rollback transaction

 end catch  
End

Difference between Truncate and Delete in SQL

Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.

• TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
  
  
• You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .
  
  
• You cann't rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.
  
  
• A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.
  
  
• If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work.
  
  
• TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.
  
  
• Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.
  
  
• TRUNCATE is faster than DELETE.

Difference between Having and Where clause

Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement.
 We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.
Where Clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).
. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.

list of differences between SQL Server 2000 , 2005 and 2008

SQL SERVER 2008:
1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is used.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Can encrypt the entire database introduced in 2008.
--check it(http://technet.microsoft.com/en-us/library/cc278098(SQL.100).aspx)
(http://www.sqlservercentral.com/articles/Administration/implementing_efs/870/)
(http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx)
(http://www.sql-server-performance.com/articles/dev/encryption_2005_1_p1.aspx)
(http://geekswithblogs.net/chrisfalter/archive/2008/05/08/encrypt-documents-with-sql-server.aspx)
13.Can compress tables and indexes.
-http://www.mssqltips.com/tip.asp?tip=1582
14.Date and time are seperately used for date and time datatype,geospatial and timestamp with internal timezone
is used.
15.Varchar(max) and varbinary(max) is used.
16.Table datatype introduced.
17.SSIS avails in this version.
18.Central Management Server(CMS) is Introduced.
-http://msdn.microsoft.com/en-us/library/bb934126.aspx
-http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx
19.Policy based management(PBM) server is Introduced.
-http://www.mssqltips.com/tip.asp?tip=1492
-http://msdn.microsoft.com/en-us/library/bb510667.aspx














SQL SERVER 2005:

1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is introduced.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Cant encrypt
13.Can Compress tables and indexes.(Introduced in 2005 SP2)
14.Datetime is used for both date and time.
15.Varchar(max) and varbinary(max) is used.
16.No table datatype is included.
17.SSIS is started using.
18.CMS is not available.
19.PBM is not available.


 SQL SERVER 2000:
1.Query Analyser and Enterprise manager are separate.
2.No XML datatype is used.
3.We can create maximum of 65,535 databases.
4.Nill
5.Nill
6.Nill
7.Nill
8.Nill
9.Nill
10.Nill
11.Nill
12.Nill
13.cant compress the tables and indexes.
14.Datetime datatype is used for both date and time.
15.No varchar(max) or varbinary(max) is available.
16.No table datatype is included.
17.No SSIS is included.
18.CMS is not available.
19.PBM is not available