Delete Duplicate Records in tables

CREATE TABLE #Duplicate (ID INT,FNAME VARCHAR(10),MNAME
)VARCHAR(10)INSERT INTO #Duplicate VALUES(1, 'AAA','CCC')INSERT INTO #Duplicate VALUES(2, 'BBB','DDD')INSERT INTO #Duplicate VALUES(1, 'AAA','CCC')INSERT INTO #Duplicate VALUES(2, 'BBB','DDD')INSERT INTO #Duplicate VALUES(1, 'AAA','CCC')INSERT INTO #Duplicate VALUES(2, 'BBB','DDD')INSERT
 ---Otion 1
;WITH CTE as(SELECT
* ROW_NUMBER() OVER(PARTITION BY ID, FName, MName ORDER BY (SELECT 1)) AS RowID,FROM #Duplicate)DELETE FROM CTE WHERE RowID > 1SELECT
 ---option 2
SELECT * INTO #TempDuplicate FROM #Duplicate WHERE 1 = 0INSERT #TempDuplicate SELECT DISTINCT * FROM #DuplicateBEGIN
DELETE TRAN #DuplicateINSERT #Duplicate SELECT * FROM #TempDuplicateCOMMIT
DROP TRAN TABLE #TempDuplicateSELECT * FROM #Duplicate * FROM #Duplicate INTO #Duplicate VALUES(3, 'BCB','DGD')

GROUP BY purpose

Group by can't be discussed without appealing to aggregate functions. The main aggregate functions are MIN, MAX, SUM, COUNT, AVG. For visual understanding let's create a test table and fill it in with some test data

CREATE TABLE testgroupby (id INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,city VARCHAR(100) not null,country VARCHAR(100) not null,population INT
 GO
INSERT INTO testgroupby (city, country, population)SELECT 'kiev', 'ukraine', 5000000UNION ALL SELECT 'odessa', 'ukraine', 1000000UNION ALL SELECT 'moscow', 'russia', 10000000UNION ALL SELECT 'rostov', 'russia', 2000000UNION ALL SELECT 'omsk', 'russia', 2000000UNION ALL SELECT 'los-angeles', 'USA', 15000000UNION ALL SELECT 'New York', 'USA', 20000000UNION ALL SELECT 'Miami', 'USA', 7000000GO


So let's use aggregation functions against the testgroupby table.

SELECT COUNT (*), MIN(population), MAX( population), SUM(population)FROM testgroupby
 
And you will see the next results
)

Dynamic columns in table

 USE TEMPDB

GO

 

CREATE TABLE data (

                [Key] VARCHAR(100) ,

                [Value] VARCHAR(100) 

);

 

INSERT INTO data VALUES('X1','1');

INSERT INTO data VALUES('X3','2');

INSERT INTO data VALUES('X5','3');

INSERT INTO data VALUES('Y1','10');

INSERT INTO data VALUES('Y2','20');

INSERT INTO data VALUES('Y3','30');

 

Select * from data;

CREATE TABLE #temp1(dummy BIT);

 

DECLARE @Script1 AS VARCHAR(8000);

DECLARE @Script_prepare1 AS VARCHAR(8000);

 

SET @Script_prepare1 = 'Alter table #temp1 Add [?] varchar(100);'

SET @Script1 = ''

 

SELECT

            @Script1 = @Script1 + Replace(@Script_prepare1, '?', [Value])

FROM

            data

--WHERE

--            [Value] like  'x' 

 

Exec (@Script1)

 

Alter table #temp1 drop column dummy;

 

CREATE TABLE #temp(dummy BIT);

 

DECLARE @Script AS VARCHAR(8000);

DECLARE @Script_prepare AS VARCHAR(8000);

 

SET @Script_prepare = 'Alter table #temp Add [?] varchar(100);'

SET @Script = ''

 

SELECT

            @Script = @Script + Replace(@Script_prepare, '?', [Key])

FROM

            data

--WHERE

--            [Value] like  'x' 

 

Exec (@Script)

 

Alter table #temp drop column dummy;

 

Select * from #temp;

Select * from #temp1; 

--drop table #temp1

--DROP TABLE DATA

To Find Parameter Data Type Size

CREATE PROC [dbo].[PARAMETERS_TYPES_SIZES]

AS

BEGIN

            SELECT PR.NAME,PA.NAME,TY.NAME+

                                    CASE WHEN TY.NAME IN ('int','smallint','bigint','bit','datetime','money','tinyint') 

                                    THEN '' 

                                    ELSE '('+cast(PA.max_length as varchar(100))+')' END as SIZE

            FROM SYS.PROCEDURES PR

                        JOIN  SYS.PARAMETERS PA

            ON PR.OBJECT_ID=PA.OBJECT_ID

                        JOIN SYS.TYPES TY

            ON PA.USER_TYPE_ID=TY.USER_TYPE_ID

END

exec  PARAMETERS_TYPES_SIZES

output:
NAME                 NAME                         SIZE

Sp_Proc               @parameterName        Varchar(100)

Hirarchy Query

with x as(select emp_id,emp_name,mgr_id from employee  where emp_id=7

union all

select e.emp_id,e.emp_name,e.mgr_id from x inner join employee e

on x.mgr_id=e.emp_id )

select * from x

Backup Whole Database & Restore

--creating database
create database nalo
--Drop Database
drop database nalo


backup database nalo to disk='d:\office.bak'


-- veryfying backup
restore filelistonly from disk='d:\office.bak'


restore verifyonly from disk='d:\office.bak'


restore headeronly from disk='d:\office.bak'


-- creating new database from backup


restore database OfficeDB from disk='d:\office.bak' with
move 'nalo' to 'D:\officeDB\OfficeDB.mdf',
move 'nalo_log' to 'D:\officeDB\OfficeDB_log.ldf'


-- checking current selected database files

sp_helpfile


-- altering databse to modify logical names of the DB

alter database OfficeDB MODIFY FILE ( NAME = nalo, NEWNAME = OfficeDB )

Alter database OfficeDB MODIFY FILE (NAME=nalo_log,NEWNAME=OfficeDB_log)

Sql Server syllabus

------------------Day-1----------------------

Step1-About management studio
Step2-Creating tables
          Altering tables
          Adding new columns
          Altering existing columns
          Data type Decrease/Increase
Step3-Insert data into tables
          Simple insert
          Columns by insert
          Bulk insert
          Insert using union
Step4-Identity and its properties .


------------------Day-2--------------------------

Step1-Inserting data from existing tables to newly created table
Step2-inserting data from one table to other table (both should be existing)
Step3-Creating new tables from existing table
          With data
          Without data
Step4-Creating relationships
Step5-creating constraints
Step6-Learn foreign key value errors
Step7-On cascade delete
Step8-Drop
          Truncate
          Delete

 ----------------Day-3-----------------

  Step1-Joins
                   Inner join
                   Outer join
                   Cross join
                   Self join
Step2-Sql syntax
Step3-Sub Queries(inline views)
Step4-Correlated sub queries
Step5-Views
Step6-Difference between views & inline views
Step7-Group by
          Having
          Order by

 --------------------Day-4-------------------------

Step1-Procedures
                            -In parameters
                            -Out parameters


Step2-Functions
                        -Predefine functions
                        -Built in functions
                        -UDF
                               -Table functions
                               -Scalar functions
Step3-CTE

------------------------- Day-5---------------------

Step1-While loop
Step2-If conditions
Step3-Using all those prepare procedures
Step4-String Manipulations
Step5-Dynamic SQL
                              -Exce
                              -Sp_excesql

--------------------------Day-6-------------------------

Step1-Triggers
                       -DML Triggers
                       -DDL Triggers
Step2-Cursors
Step3-Temp tables
Step4-Eliminate Cursors with temp tables




------------------Day-7---------------------------

Step1-Data migration
Step2-Dbcc commands
Step3-Tunning Queries
Step4-Tunning Wizard
Step5-Simple DBA roles
          Backups
          Restores
          Jobs
         

SP & DBCC COMMANDS

Command	Purpose	Sample Usage
sp_helpdb	This gives you information about all databases in the instance or specific information about one database.	sp_helpdb sp_helpdb databasename
fn_virtualfilestats	This command will show you the number of read and writes to a data file.  Use sp_helpdb with the database name to see the logical file numbers for the data files and the database id.	SELECT * FROM :: fn_virtualfilestats(dabaseid, logicalfileid)          SELECT * FROM :: fn_virtualfilestats(1, 1)
fn_get_sql()	Returns the text of the SQL statement for the specified SQL handle.  This is similar to using DBCC INPUTBUFFER, but this command will show you additional information.  This can also be embedded in a process easier then using the DBCC command	DECLARE @Handle binary(20) SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52 SELECT * FROM ::fn_get_sql(@Handle)
sp_lock	This command shows you all of the locks that the system is currently tracking This is similar to information you can see in Enterprise Manager.	sp_lock sp_lock spid sp_lock spid1, spid2
sp_help	This command gives you information about the objects within a database.  The command without an object name will give you a list of all objects within the database.	sp_help sp_help object name
sp_who2	Gives you process information similar to what you see when using Enterprise Manager.	sp_who2 sp_who2 spid
sp_helpindex	Gives you information about the indexes on a table as well as the columns used for the index. MSSQLTips additional info	sp_helpindex object name
sp_spaceused	This command shows you how much space has been allocated for the database (or if specified an object) and how much space is being used.	sp_spaceused sp_spaceused object name
DBCC CACHESTATS	Displays information about the objects currently in the buffer cache.	DBCC CACHESTATS
DBCC CHECKDB	This will check the allocation of all pages in the database as well as check for any integrity issues.	DBCC CHECKDB
DBCC CHECKTABLE	This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.	DBCC CHECKTABLE (‘table Name’)
DBCC DBREINDEX	This command will reindex your table.  If the index name is left out then all indexes are rebuilt.  If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.	DBCC DBREINDEX (tablename, indexname, fillfactor) DBCC DBREINDEX (authors, '', 70) DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
DBCC PROCCACHE	This command will show you information about the procedure cache and how much is being used.  Spotlight will also show you this same information.	DBCC PROCCACHE
DBCC MEMORYSTATUS	Displays how the SQL Server buffer cache is divided up, including buffer activity.	DBCC MEMORYSTATUS
DBCC SHOWCONTIG	This command gives you information about how much space is used for a table and indexes.  Information provided includes number of pages used as well as how fragmented the data is in the database.	DBCC SHOWCONTIG DBCC SHOWCONTIG WITH ALL_INDEXES DBCC SHOWCONTIG tablename
DBCC SHOW_STATISTICS	This will show how statistics are laid out for an index.  You can see how distributed the data is and whether the index is really a good candidate or not.	DBCC SHOW_STATISTICS (tablename, indexname)
DBCC SHRINKFILE	This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to.  Use the sp_helpdb command along with the database name to see the actual file names used.	DBCC SHRINKFILE (filename, size in MB) DBCC SHRINKFILE (DataFile, 1000)
DBCC SQLPERF	This command will show you much of the transaction logs are being used.	DBCC SQLPERF(LOGSPACE)
DBCC TRACEON	This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.	DBCC TRACEON(traceflag)
DBCC TRACEOFF	This command turns off a trace flag.	DBCC TRACEOFF(traceflag)

Find The tables and column based on datatype

Based on datatype to find the all table and column for particular database

SELECT TA.name,C.name,T.name FROM SYS.TYPES T INNER JOIN SYS.COLUMNS CON T.system_type_id = C.system_type_id INNER JOIN SYS.TABLES TA ON TA.OBJECT_ID = C.OBJECT_IDWHERE t.name = 'money'