Rollback,Commit,Truncate Example:

/* CREATE TABLE */
CREATE TABLE TRUNTEST(ID INT)
/* INSERT TABLE VALUES */
INSERT INTO TRUNTEST  VALUES(5)
/*SELECT TABLE*/
SELECT * FROM TRUNTEST
/* BEFORE SELECT BEGIN TRAN*/
BEGIN TRAN
TRUNCATE TABLE TRUNTEST
SELECT * FROM TRUNTEST
/*TRUNCATE AFTER ROLLBACK TRANSACTION*/
ROLLBACK TRAN
SELECT * FROM TRUNTEST
/*COMMIT TRANSACTION*/
BEGIN TRAN
COMMIT TRAN
SELECT * FROM TRUNTEST

STORED PROCEDURE TIPS

sp_server_info : Gives complete information about the current SQL Server that you are connected to.

sp_databases, sp_helpdb – Lists all the databases, with this two sp_helpdb provides more precise and clear information on the size, status created date etc..,

sp_attach_db, sp_detach_db –These stored procedures are usefull when you are porting a database from one server to another server. This is one of the best methods to change the database without any problems. This is just like copying a file from one place to another place. The Operation is to Detach from the Source and Attach to the Destination. After this you may need to use sp_change_users_login to update the logins and users link.

sp_helpuser – Lists all the users in the system.

sp_who, sp_who2 – Gives the list of logged in users and their complete details about the execution status.

sp_msforeachtable – This is one of the undocumented procedures, which you can used to find the Total Physical Space Occupied by each tables in the database.

Eg:sp_msforeachtable 'sp_spaceused "?"'

sp_password – Used to change password for an SQL Server Login.

sp_tables – Lists all the tables and virtual tables (Views) along with the system tables, with information’s including table owner.

sp_stored_procedures  - Lists all the stored procedures as like the table list with custom stored procedures in name; 1 format. (Please note it doesn’t mean that you can execute all the procedures that are listed, it depends on the permissions that you have)

sp_help tablename – Lists all the information pertaining to the given tablename with details about every column, constraints and the file table is located.

sp_helptext  - Used to view the contents of a view, stored procedure, user defined functions.

sp_changeobjectowner  - Changing Table / Stored Procedure Owner 
If you ever want to change the owner of the object to another user this procedure will be helpful.

sp_change_users_login – This is a very useful procedure when you are porting the database using sp_attach_db and where you need to keep your old logins and users as same.

Composite key in sql

Composite key:

 Composite key has one or more attribute  of field.

Example:

CREATE TABLE SAMPLES(ID NUMERIC(10),NAME NVARCHAR(50),CCODE NVARCHAR(50) PRIMARY KEY(ID,CCODE))

Simple and sample Storedprocedure

Simple and sample Storedprocedure......
-- =============================================
-- Author: Raja
-- Create date: 03-08-2011
-- Description: Sample user Storedprocedure
-- =============================================
CREATE PROCEDURE USP_Sample
-- Add the parameters for the stored procedure here
@sno numeric(10),
@sname nvarchar(50)
AS
BEGIN

    -- Insert statements for procedure here
INSERT INTO SAMP(SNO,SNAME) VALUES (@sno ,@sname )

END


EXEC USP_Sample 1,'RESOURCES'

Example storedprocedure

Hi,
it's sample sp....
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

Tempory delete data after rollback datas in sql

HI,
--CREATETABLE

CREATE TABLE TEP(SNO NUMERIC(10),NAME NVARCHAR(50))

INSERT INTO TEP VALUES(4,'RESOURCES')
--BEGINTRANSACTION
BEGIN TRANSACTION TEP
DELETE FROM TEP
--ROLLBACKTRANSACTION
ROLLBACK TRANSACTION TEP