CHECKING THE GIVEN NUMBER IS PRIME OR NOT

CREATE  PROC [DBO].[USP_GETPRIMNUMBERS](@N INT)
AS
BEGIN
DECLARE @I INT=2
DECLARE @FLAG BIT=0
WHILE(@I<@N)
BEGIN
IF(@N%@I=0)
BEGIN
SET @FLAG=1
BREAK
END
SET @I=@I+1
END
IF(@FLAG=1)
BEGIN
PRINT (' THE GIVEN NUMBER IS NOT PRIME')
END
       ELSE IF (@FLAG=0)
       BEGIN
          PRINT (' THE GIVEN NUMBER IS PRIME')
       END
END

EXEC [USP_GETPRIMNUMBERS] 2

EXEC [USP_GETPRIMNUMBERS] 3

EXEC [USP_GETPRIMNUMBERS] 5

EXEC [USP_GETPRIMNUMBERS] 8

PROCEDURE FOR REVERSE STRING

CREATE  PROC [DBO].[USP_REVERSESTRING](@STRING VARCHAR(20))
AS
BEGIN
DECLARE @I INT =0
DECLARE @S VARCHAR(20) =''
DECLARE @LENGTH INT
SELECT @LENGTH=LEN(@STRING)
WHILE (@I<=@LENGTH)
BEGIN
SELECT @S=@S+SUBSTRING(@STRING,LEN(@STRING)-@I,1)
SET @I=@I+1
END
 PRINT @S
END


EXEC [USP_REVERSESTRING] 'MANJUNATH'

PROCEDURE FOR CHECKING THE GIVEN STRING IS PALINDROME OR NOT

CREATE  PROC [DBO].[USP_STRING_PALINDROME](@STRING VARCHAR(20))
AS
BEGIN
DECLARE @I INT =0
DECLARE @S VARCHAR(20) = '  '
DECLARE @LENGTH INT
SELECT     @LENGTH=LEN(@STRING)
WHILE      (@I<=@LENGTH)
BEGIN
 SELECT @S=@S+SUBSTRING(@STRING,LEN(@STRING)-@I,1)
 SET        @I=@I+1
END
PRINT @S

IF(@S=@STRING)
 BEGIN
  PRINT 'THE GIVEN STRING IS PALINDROME'
  PRINT'THANK YOU'
 END
ELSE
 BEGIN
  PRINT 'THE GIVENSTRING IS NOT PALINDROME'
  PRINT'THANK YOU'
 END
END


EXEC [USP_STRING_PALINDROME]  'MADAM'

EXEC [USP_STRING_PALINDROME]  'MANJUNATH'


EXEC [USP_STRING_PALINDROME]  'MADAM'

SOURCE TABLE SYNC WITH TARGET TABLE BY USING MANUAL AND MERGE METHODS

HERE I WILL TRY TO SYNC THE TARGET TABLE WITH SOURCE BY USING A PROCEDURE

1. FOR THAT I WILL CREATE TWO TEMP TABLES ONE FOR SOURCE AND ANOTHER FOR TAGET ...
CREATE TABLE #T(ID INT ,NAME VARCHAR(50))
CREATE TABLE #S(ID INT ,NAME VARCHAR(50))

2. I WILL WRITE A PROCEDURE TO INSERT,UPDATE,DELETE IN THE TARGET BASED ON THE SOURCE

CREATE PROC USP_SOURCE_TARGET

AS

BEGIN

INSERT INTO #T

SELECT * FROM #S WHERE ID NOT  IN(SELECT ID FROM #T)

UPDATE B SET NAME=A.NAME FROM #S A INNER JOIN #T B ON A.ID=B.ID

WHERE B.NAME<>A.NAME

DELETE FROM #T WHERE ID NOT IN (SELECT ID FROM #S)

END

3. I WILL INSERT,UPDATE,DELETE IN THE SOURCE TABLE MANUALLY AND THEN EXECUTE THE PROC THEN INSERT ,DELETE AND UPDATE WILL AUTOMATICALLY INTO THE TARGET ALSO

INSERT INTO #S VALUES (3,'MANJU1'),(4,'HARI')

UPDATE #S SET NAME='HHH' WHERE ID=2

DELETE FROM #S WHERE ID=2

EXEC USP_SOURCE_TARGET

IN THE SAME WAY I USED MERGE TO DO THE ABOVE THINGS

MERGE #T AS T
USING #S AS S ON S.ID=T.ID
WHEN NOT MATCHED BY TARGET THEN
INSERT(ID,NAME)VALUES(S.ID,S.NAME)
WHEN MATCHED THEN
UPDATE SET NAME=S.NAME
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

SELECT * FROM  #S

SELECT * FROM  #T

ADDING A FILE AND FILEGROUP TO THE EXISTING DATABASE

ADDING A FILE GROUP TO EXISTING DATABASE

ALTER DATABASE TEST_DB ADD FILEGROUP SECONDARY

SP_HELPFILE

ADDING A FILE TO FILEGROUP OF A DATABASE

ALTER DATABASE TEST_DB
  ADD FILE
  (NAME = N'DATA_2001',
  FILENAME = N'D:\BACKUP\TEST_DB.NDF',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
  TO FILEGROUP SECONDARY

CREATEING A TABLE UNDER THE FILEGROUP

CREATE TABLE TBL_SAL(NAME VARCHAR(50),SAL MONEY)ON FOURTH
DROP TABLE TBL_SAL

SELECT * FROM SYS.FILEGROUPS

SELECT NAME FROM SYS.DATABASES WHERE NAME= 'TEST_DB'

PARTITIONED TABLES

FOR TABLE PARTITIONING WE HAVE TO FOLLOW SOME STEPS

1.IN THE FIRST STEP WE HAVE TO  CREATE A NEW DATABASE CALLED 'PART_DB' WITH TWO FILE GROUPS I.E PRIMARY AND SECONDARY

CREATE DATABASE PART_DB
ON PRIMARY
(NAME='PART_DB',
FILENAME=
'D:\BACKUP\PART_DB.MDF',
SIZE=50,
MAXSIZE=1000,
FILEGROWTH=50 ),
FILEGROUP SECONDARY
(NAME = 'PART_DB1',
FILENAME =
'D:\BACKUP\PART_DB1.NDF',
SIZE = 50,
MAXSIZE=1000,
FILEGROWTH=50 );

CHECKING THE FILES AND FILEGROUPS

SELECT * FROM SYS.FILEGROUPS

SP_HELPFILE

2.NOW WE HAVE TO CREATE  A PARTITION FUNCTION

CREATE PARTITION FUNCTION DB_PARTITIONRANGE (INT)
AS RANGE LEFT FOR
VALUES (5);

3.CREATE A PARRRTITION SCHEMA AND ATTACH TO FILE GROUP

CREATE PARTITION SCHEME DB_PARTITIONSCHEME
AS PARTITION DB_PARTITIONRANGE
TO ([PRIMARY], [SECONDARY]);

4.CREATE TABLE WITH PARTITION KEY AND PARTITION SCHEME

CREATE TABLE TBL_PARTITION
(P_YEAR INT NOT NULL,
DATE DATETIME)
ON DB_PARTITIONSCHEME (P_YEAR);

5.CREATE INDEX ON PARTITIONED TABLE(OPTIONAL/RECOMMENDED)

CREATE UNIQUE CLUSTERED INDEX INDX_PARTITION
ON TBL_PARTITION(P_YEAR)
ON DB_PARTITIONSCHEME (P_YEAR)

6.INSERTING VALUES INTO A TABLE

INSERT INTO TBL_PARTITION (P_YEAR, DATE) -- INSERTED IN PARTITION 1
VALUES (3,GETDATE());
INSERT INTO TBL_PARTITION (P_YEAR, DATE) -- INSERTED IN PARTITION 1
VALUES (2,GETDATE());
INSERT INTO TBL_PARTITION (P_YEAR, DATE) -- INSERTED IN PARTITION 2
VALUES (10,GETDATE());

SELECT * FROM TBL_PARTITION

SELECT *
FROM SYS.PARTITIONS
WHERE OBJECT_NAME(OBJECT_ID)='TBL_PARTITION';

BACKUP AND RECOVERY MODELS

BACKUP MODELS
1.FOR THIS BACKUP MODELS FIRST I HAVE CREATED A NEW DATABASE CALLED "SAMPLE". IN THAT DATABASE I CREATED A TABLE CALLED 'TBL_DESIG'  WITH 3 COLUMNS AND ALSO INSERTED ONE ROW
2.NOW MY TABLE HAVE ONE RECORD
I TAKE FULL BACKUP OF MY SAMPLE DATABASE
3.AFTER SOME TIME I INSERTED ANOTHER RECORD INTO TABLE
I TAKE THE DIFFERENTIAL BACKUP
4.IN THE SAME WAY AFTER SOME TIME I INSERTED ANOTHER RECORD INTO TABLE
I TAKE THE LOG BACKUP IN THE FOLLOWING ..

CREATE TABLE TBL_DESIG(NUM INT,NAME VARCHAR(50),COURSE VARCHAR(50))

INSERT INTO TBL_DESIG VALUES(1,'SARAN','DBA')

1.FULL BACKUP

BACKUP DATABASE SAMPLE TO DISK='D:\BACKUP\MYFULL.BAK'

INSERT INTO TBL_DESIG VALUES(2,'MAHI','MGR')

 2.DIFFERENTIAL BACKUP

BACKUP DATABASE  SAMPLE TO DISK='D:\BACKUP\MYDIFF.BAK' WITH DIFFERENTIAL

INSERT INTO TBL_DESIG VALUES(3,'DEVI','DIRECT')

3.LOG OR TRANSACTIONAL LOG BACKUP

BACKUP LOG SAMPLE TO DISK='D:\BACKUP\MYLOG.BAK'


SELECT * FROM TBL_DESIG

RESTORING MODELS
IN THE RESTORE MODEL I DELETED THE ABOVE CREATED DATABASE
AND RESTORE IN THE FOLLOWING ORDER

RESTORE DATABASE SAMPLE FROM DISK='D:\BACKUP\MYFULL.BAK' WITH NORECOVERY

RESTORE  SAMPLE FROM DISK='D:\BACKUP\MYDIFF.BAK' WITH NORECOVERY

RESTORE LOG SAMPLE FROM DISK='D:\BACKUP\MYLOG.BAK' WITH NORECOVERY

RESTORE  DATABASE SAMPLE WITH RECOVERY

REMOVING THE DUPLICATE ROWS

1.FIRST WE CREATE THE TABLE

CREATE TABLE [dbo].[ATTENDANCE](
[EMPLOYEE_ID] [varchar](50) NOT NULL,
[ATTENDANCE_DATE] [date] NOT NULL) ON [PRIMARY]

2.WE CAN INSERT DATA INTO TABLE WITH DUPLICATE DATA

INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A003',CONVERT(DATETIME,'01-01-11',5))

SELECT * FROM ATTENDANCE

EMPLOYEE_ID                            ATTENDANCE_DATE
--------------------------------------------- ---------------
A001                                               2011-01-01
A001                                               2011-01-01
A002                                               2011-01-01
A002                                               2011-01-01
A002                                               2011-01-01
A003                                               2011-01-01

3.NOW WE CAN ADD A COLUMN TO TABLE WITH IDENTITY

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)

4.NOW WE CAN SEE WHAT ARE THE DUPLICATE ROWS

SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

EMPLOYEE_ID                                        ATTENDANCE_DATE AUTOID
-------------------------------------------------- --------------- -----------
A001                                               2011-01-01      2
A002                                               2011-01-01      4
A002                                               2011-01-01      5


5.THE QUERY FOR REMOVING THE DUPLICATES

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

6.NOW WE CAN CHECK THE RESULT

SELECT * FROM dbo.ATTENDANCE

EMPLOYEE_ID                                        ATTENDANCE_DATE AUTOID
-------------------------------------------------- --------------- -----------
A001                                               2011-01-01      1
A002                                               2011-01-01      3
A003                                               2011-01-01      6

create table EmpDup(empid int,name varchar(20))
   
    insert into EmpDup values(1,'Andy')
    insert into EmpDup values (1,'Andy')
    insert into EmpDup values(2,'Bill')
    insert into EmpDup values(2,'Bill')
    insert into EmpDup values (2,'Bill')
    insert into EmpDup values (3,'Chris')

select * from EmpDup

select distinct * into #tmp From EmpDup
    delete from EmpDup
    insert into EmpDup              
    select * from #tmp drop table #tmp

drop table EmpDup

alter table EmpDup add  sno int identity(1,1)

select * from EmpDup where sno not in (select min(sno) from EmpDup group by empid,name)

delete from EmpDup where sno not in (select min(sno) from EmpDup group by empid,name)

alter table EmpDup
    drop  column sno

SPLITTING STRING

CREATE PROC USP_STRING(@STR VARCHAR(500))
AS
BEGIN
          SET @STR=@STR+','
          DECLARE @SP INT=1
          DECLARE @PP INT=1
          SELECT @SP= CHARINDEX( ' , ' ,@STR,@PP )
          CREATE TABLE #TEMP (ID INT IDENTITY,NAME VARCHAR(500))
          WHILE (@SP>0)
             BEGIN
                INSERT INTO #TEMP
                SELECT SUBSTRING (@STR,@PP,@SP-@PP)
                SET @PP=@SP+1
                SELECT @SP = CHARINDEX ( ' , ' ,@STR,@PP )
            END
         SELECT * FROM #TEMP
 END

 EXEC USP_STRING 'MANJU,RAMU,ANJI,HARI,BABU,DEVI'

OUTPUT

NAME  ID
MANJU 1
RAMU   2
ANJI      3
HARI     4
BABU    5
DEVI      6

CURSOR WITH IN THE PROCEDURE

CREATE   PROC USP_CUR_EMP

AS

BEGIN

  CREATE TABLE  #TEMP(EMPNO INT, ENAME VARCHAR(50), SAL INT ,DNAME VARCHAR(50))

  DECLARE @EMPNO INT,@ENAME VARCHAR(50),@SAL INT

  DECLARE @DNAME VARCHAR(50)

  DECLARE @DNO INT

  DECLARE C1 CURSOR FOR SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP

  OPEN C1

    FETCH NEXT FROM C1 INTO @EMPNO,@ENAME,@SAL,@DNO

    WHILE(@@FETCH_STATUS=0)

      BEGIN

          INSERT INTO #TEMP(EMPNO,ENAME,SAL)VALUES( @EMPNO,@ENAME,@SAL)

          SELECT @DNAME=DNAME FROM DEPT WHERE DEPTNO=@DNO

UPDATE  #TEMP SET DNAME=@DNAME WHERE EMPNO=@EMPNO

FETCH NEXT FROM C1 INTO @EMPNO,@ENAME,@SAL,@DNO

      END

  CLOSE C1

  DEALLOCATE C1

  SELECT  *  FROM  #TEMP

END

EXEC USP_CUR_EMP

DROP TABLE  #TEMP

THE OUTPUT LIKE THIS

EMPNO      ENAME         SAL          DNAME

7369	SMITH	800	RESEARCH
7499	ALLEN	1600	SALES
7521	WARD	1250	SALES
7566	JONES	2975	RESEARCH
7654	MARTIN	1250	SALES
7698	BLAKE	1255	SALES
7782	CLARK	2450	ACCOUNTING
7788	SCOTT	3000	RESEARCH
7839	KING	5000	ACCOUNTING
7844	TURNER	1500	SALES