DELETE duplicate values in a TABLE

-----------------------DELETE duplicate values in a TABLE------------------------------------------

CREATE TABLE dbo.TestTable(
Code Varchar (1) NULL,
n smallint NULL
) ON [PRIMARY]
GO
--select * from TestTable
INSERT INTO dbo.TestTable
VALUES
  ('A',1),('A',2),('A',2),('B',1),('B',2),('B',3),('C',1),('C',1),('C',2)


 ;WITH x AS
 (
 select code,n,ROW_NUMBER() over(order by Code,n)AS DPC from TestTable
 )
 DELETE
FROM x
WHERE DPC > 1

BACKUP types

---------------------------------BACKUP types----------------------------

--WITH MOVE 

RESTORE DATABASE XX FROM DISK='D:\Back\full.bak' 
 WITH MOVE 'xx' TO 'E:\full.mdf', 
             MOVE 'xx_log' TO 'E:\full.ldf'
-- full backup
backup database xx to disk='D:\BACKUP\xx_full.bak'
-- Restoring full back
restore database xx from disk='D:\BACKUP\xx_full.bak' with  norecovery
--log back
backup log xx to disk='D:\BACKUP\XX_log.bak'
-- restore log back
restore log xx from disk='D:\BACKUP\XX_log.bak'with  norecovery
--differential back ---
backup database xx to disk='D:\BACKUP\XX_diff.bak' with differential
--restoring differential back
restore database xx from disk='D:\BACKUP\XX_diff.bak' with  norecovery
--Shared backup
backup database xx to disk='D:\BACKUP\XX_copy1.bak' ,disk ='D:\BACKUP\XX_copy2.bak' --with differential
--restoring shared backup files
restore database xx from disk='D:\BACKUP\XX_copy1.bak', disk ='D:\BACKUP\XX_copy2.bak' with  norecovery
--mirror backup/ copy of backup
backup database xx to disk='D:\BACKUP\XX_copy3.bak' mirror to disk='D:\BACKUP\XX_copy4.bak' with format--,differential
--restoring mirror backup files (these are full backup files )
restore database xx from disk ='D:\BACKUP\XX_copy4.bak' with replace, norecovery

-- restore data base to get thye orignal state of the database
--backup applied sets( latest full back up+latest differentials from latest full backup+ latest log back ups from latest differential onwords + tile log backup)

--taking database into recovery state
restore database xx with recovery

REFERENCE SITES for SQL

-----------------REFERENCE SITES for SQL-------------------

1.http://www.1keydata.com/sql/sql.html

2.http://www.dotnet-tricks.com/Tutorial/sqlserverlist

3.http://sqlservercodebook.blogspot.in/2008/03/sql-string-functions-tutorial.html

4.http://www.c-sharpcorner.com/uploadfile/raj1979/functions-in-sql-server-2005/

5.http://sqlmag.com/sql-server/sql-server-2008

6.http://skyserver.sdss3.org/dr8/en/tools/search/sql.asp?cmd=SELECT%20top%2010%20objID%2C%20ra%2C%20dec%2C%20g%2C%20clean%0D%0AFROM%20PhotoObj%0D%0AWHERE%20CLEAN%3D1%0D%0A

7.http://www.sqlservercurry.com/search/label/Transact%20SQL%20T-SQL

8.http://sqlexplore.wordpress.com/category/database/

DYNAMIC SQL

-------------DYNAMIC SQL----------------

alter proc emp_ins (@name varchar(100) , @empno int out )

as

 begin

 declare @query nvarchar(500)=( 'select @i_empno=empno from emp where ename =@in_name ')

 exec sp_executesql @query,N'@in_name varchar(100),@i_empno int out'  ,@in_name=@name,@i_empno=@empno out

end
　
　

declare @c int

exec emp_ins 'smith',@c out

print @c

TABLE VALUED PARAMETERS (CREATE TYPE)

---------------table valued parameters-----------------

Create table xx(id int identity primary key, name varchar(10),gender bit)
 CREATE TYPE [dbo].xx_type AS TABLE(
 id int , name varchar(10),gender bit,isModified bit
 )GO　insert into xx select 'c',1
　declare @my_type xx_type
 
insert into @my_type
 select *,0 as isModified from xx
 update @my_type set name='cc',ismodified=1 where id=3
 insert into @my_type select 0,'d',1,0 -- is modified should be zero for newly inserted records
 insert into @my_type select 0,'e',1,0 -- is modified should be zero for newly inserted records
 　
　exec USP_XX_Modify @my_type
 Create PRoc USP_XX_Modify(@my_type xx_type readonly) AS
BEGIN -- updating modified recordsupdate At set name=t.name , gender=t.gender 
from @my_type t inner join xx AT on t.id=at.id 
where isModified=1
-- inserting newly passed recordsInsert into xx
select name,gender from @my_type where id=0
END　select * from xx

concatenate EVERY 3rd CHARACTER using FUNCTION and PROCEDURE

concatenate EVERY 3rd CHARACTER using FUNCTION and PROCEDURE----------------

---------------------------USING PROCEDURE---------------------------------
CREATE PROC name_store(@stringname VARCHAR(300))
AS
BEGIN
DECLARE @char_concat varchar(50)=''
DECLARE @i int=3
WHILE(@i<LEN(@stringname))
BEGIN
select @char_concat=@char_concat+substring(@stringname,@i,1)
SET @i=@i+3
END
PRINT @char_concat
END
EXEC name_store 'JFSOAIRFGASLKEHLRUWAH...UDSFJGNZDSFH'

--------------------USING FUNCTION------------------------------------------------

ALTER FUNCTION get_concat_char(@FULLNAME VARCHAR(50))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @I INT=3
DECLARE @NAME VARCHAR(100)=''
WHILE(@I<LEN(@FULLNAME))
BEGIN
SET @NAME=@NAME+SUBSTRING(@FULLNAME,@I,1)
SET @I=@I+3
END
RETURN @NAME;
END
SELECT  DBO.get_concat_char('JZSDFJJBVSUDFSMAZBXFCVH') 

Print DATABASES and NO OF TABLES in each DATABASE--using CURSOR

------------CREATE PROC for Print DATABASES and NO OF TABLES in each DATABASE---------

ALTER PROC concatination
AS
BEGIN
DECLARE @qry varchar(100)
DECLARE @NAME VARCHAR(MAX)=''
DECLARE c1 CURSOR FOR SELECT name FROM sys.databases
OPEN C1
FETCH NEXT FROM C1 INTO @NAME
WHILE(@@FETCH_STATUS<>-1)
BEGIN
set @qry= 'select '+char(39)+@name+char(39)+',count(*) from '+@name+'.sys.tables'
--EXEC(@qry)
fetch next from c1 into @name
PRINT @qry
END
CLOSE C1
DEALLOCATE C1
END
EXEC concatination