SQLCode

---

AttachDatabase.sql

--Unable to open the physical file "E:\SQLData\Data\AttachStudentDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
CREATE DATABASE TestAttach
	ON (FILENAME = 'E:\SQLData\Data\AttachStudentDB.mdf'),
	(FILENAME = 'E:\SQLData\Data\AttachStudentDB_log.ldf')
	FOR ATTACH

CREATE DATABASE TestAttach
	ON (FILENAME = 'E:\SQLData\Data\TestAttach.mdf')
	FOR ATTACH

Backup2.sql

--Create new table in database TestDB
USE TestDB
GO
CREATE TABLE TestTable
(
	ID INT IDENTITY PRIMARY KEY,
	TESTNAME VARCHAR(32)
)
GO
INSERT INTO TestTable
VALUES ('FIRST TEST')

--Create full back up for TestDB
BACKUP DATABASE TestDB TO DISK = 'E:\SQLData\Backup\TestDB.bak' WITH INIT

--Create back up differential for TestDB
BACKUP DATABASE TestDB TO DISK = 'E:\SQLData\Backup\TestDB_Diff.bak' WITH DIFFERENTIAL

--Restore database to NewBackUp database
USE master
GO
RESTORE DATABASE NewBackUp FROM DISK = 'E:\SQLData\Backup\TestDB.bak' WITH REPLACE

CreateConstraintForiegnKey.sql

CREATE DATABASE StudentDB
GO
USE StudentDB
GO 
CREATE TABLE StudentDB.dbo.Class (
	ClassID INT PRIMARY KEY NOT NULL,
	ClassName VARCHAR(32)
)
GO
CREATE TABLE StudentDB.dbo.Student(
	StudentID INT PRIMARY KEY NOT NULL,
	Name VARCHAR(64),
	Address VARCHAR(32),
	ClassID INT,
	CONSTRAINT FK_ClassID FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
)
GO
SELECT * FROM StudentDB.dbo.Class
GO
SELECT * FROM StudentDB.dbo.Student
--Luon phai insert bang Foreign key truoc
INSERT INTO Class VALUES(1,'12A1')
INSERT INTO Class VALUES(2,'12A2')
INSERT INTO Class VALUES(3,'12Y')


--Neu thong tin foreign key khong co trong bang reference bang se bi loi
INSERT INTO Student
VALUES(
1,'PHAM DINH KHANH','HOANG MAI',1
)

INSERT INTO Student
VALUES(
2,'LE THANH CONG','CAU GIAY',3)

INSERT INTO Student
VALUES(
3,'DANG NGHIA HUNG','MINH KHAI',2
)

INSERT INTO Student
VALUES(
4,'NGUYEN DUY TUNG','BA DINH',2
)

CreateDateBetweenRange.sql

USE KHANH_DB
DECLARE @dates TABLE (Dt datetime)
DECLARE @dateFrom datetime
DECLARE @dateTo datetime

SET @dateFrom = '2015-01-01'
SET @dateTo = '2017-12-31'

WHILE (@dateFrom < @dateTo)
BEGIN
	INSERT INTO @dates
	SELECT @dateFrom
	SET @dateFrom = DATEADD(DAY,1,@dateFrom)
END

SELECT * FROM @dates

CreateLExcelLink.sql

DECLARE @RC int

DECLARE @server     nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider   nvarchar(128)
DECLARE @datasrc    nvarchar(4000)
DECLARE @location   nvarchar(4000)
DECLARE @provstr    nvarchar(4000)
DECLARE @catalog    nvarchar(128)

-- Set parameter values
SET @server =     'EXCELLINK'
SET @srvproduct = 'Excel'
SET @provider =   'Microsoft.ACE.OLEDB.12.0'
SET @datasrc =    'E:\SQLData\Data\WorkOrderOpenRowSet.xlsx'
SET @provstr =    'Excel 12.0'

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog

ExcecImportToSQLFromTextUsingR.sql

EXEC TestDB.dbo.ImportTextFile 'E:\SQLData\Data\ConvertTextFile.txt','TestDB.dbo.WorkOrderImportFromTextFileUseR','\t','\n',2

ExecRcodeFromSQL.sql

USE master
GO
sp_configure 'external scripts enabled', 1;  
RECONFIGURE WITH OVERRIDE;  

EXECUTE sp_execute_external_script
      @language = N'R'
    , @script = N'
        library(utils);
        mymemory <- memory.limit();
        OutputDataSet <- as.data.frame(mymemory);'
    , @input_data_1 = N' ;'
WITH RESULT SETS (([Col1] int not null));

FunctionConvertDateCrossApply.sql

USE TestDB
GO
CREATE FUNCTION dbo.ConvertDate (
	@StringDate VARCHAR(32)
) 
RETURNS @TableResult TABLE(
	DateConverted VARCHAR(8)
)
AS
BEGIN
DECLARE @B VARCHAR(32), @Date VARCHAR(32), 
	@Month VARCHAR(32), @Year VARCHAR(32),
	@TempDate VARCHAR(32), @TempMonth VARCHAR(32)
	SET @B = LEFT(@StringDate,LEN(@StringDate)-6)
	SET @TempDate = LEFT(@StringDate,CHARINDEX('/',@B,1)-1)
	SET @Date = CASE WHEN LEN(@TempDate) = 2 THEN @TempDate
					 ELSE '0'+@TempDate END
	SET @Year= RIGHT(@B,4)
	SET @TempMonth = SUBSTRING(@B,LEN(@TempDate)+2,LEN(@B)-LEN(@Year)-LEN(@TempDate)-2)
	SET @Month = CASE WHEN LEN(@TempMonth) = 2 THEN @TempMonth
					  ELSE '0'+@TempMonth END
	INSERT INTO @TableResult
	SELECT @Year+@Month+@Date
	RETURN
END

FunctionReturnDateInMonth.sql

CREATE FUNCTION dbo.DateInMonth(@Date VARCHAR(32))
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
	DECLARE @StartDateMonth VARCHAR(32)
	DECLARE @Day INT
	SET @StartDateMonth = LEFT(@Date,6) + '01'
	SET @Day = DATEDIFF(DAY,@StartDateMonth, @Date)
	RETURN(@Day)
END
--SELECT dbo.DateInMonth('20170131')

FunctionReturnTable.sql

USE TestDB
GO
--DROP FUNCTION dbo.AverageOrder
CREATE FUNCTION dbo.AverageOrder(
	@ProductID VARCHAR(32),
	@MinOrderQty INT
) 
RETURNS @OrderTable TABLE (
	ProductID VARCHAR(32),
	AverageOrderQty INT
)
AS 
BEGIN
	WITH A AS (
		SELECT ProductID,AVG(OrderQty) AverageOrderQty FROM TestDB.dbo.WorkOrder
		WHERE ProductID = @ProductID AND OrderQty >= @MinOrderQty
		GROUP BY ProductID
	)
	INSERT INTO @OrderTable
	SELECT * FROM A
	RETURN
END

FunctionReturnTableSegment.sql

USE TestDB
GO
CREATE FUNCTION dbo.Segment(
	@Value INT,	
	@LowValue INT,
	@MediumValue INT,
	@HightValue INT
) 
RETURNS @SegmentTable TABLE(
	Segment VARCHAR(32)
)
AS
BEGIN
	DECLARE @Segment VARCHAR(32)
	SET @Segment = CASE WHEN @Value <= @LowValue THEN 'SILVER'
						WHEN @Value <= @MediumValue THEN 'GOLD'
						WHEN @Value <= @HightValue THEN 'TITAN'
						ELSE 'PLATINUM' END
	INSERT INTO @SegmentTable VALUES (@Segment)
	RETURN
END
--SELECT * FROM TestDB.dbo.Segment(49,10,50,100)
--SELECT A.ProductID,A.OrderQty,B.* FROM TestDB.dbo.WorkOrder A
--	CROSS APPLY
--	TestDB.dbo.Segment(A.OrderQty,10,20,100) B

FunctionReturnVariable.sql

--SELECT * FROM TestDB..WorkOrder
--ALTER TABLE TestDB..WorkOrder
--ADD SegmentOrder VARCHAR(32)


CREATE FUNCTION dbo.SegmentOrder(@OrderQty INT,
	@iSILVER INT,
	@iGOLD INT,
	@iTITAN INT
)
RETURNS VARCHAR(32) 
AS
BEGIN
	DECLARE @SegmentOrder VARCHAR(32)
	SET @SegmentOrder = CASE WHEN @OrderQty < @iSILVER THEN 'SILVER'
							WHEN @OrderQty < @iGOLD THEN 'GOLD'
							WHEN @OrderQty < @iTITAN THEN 'TITAN'
							ELSE 'PLATINUM' END
	RETURN(@SegmentOrder)
END

--SELECT dbo.SegmentOrder(100,10,20,100)
--UPDATE WorkOrder
--SET SegmentOrder = dbo.SegmentOrder(OrderQty,10,20,100)
--SELECT * FROM TestDB..WorkOrder

FunctionReturnWeek.sql

CREATE FUNCTION dbo.ISOweek (@DATE datetime)  
RETURNS int  
WITH EXECUTE AS CALLER  
AS  
BEGIN  
     DECLARE @ISOweek int;  
     SET @ISOweek= DATEPART(wk,@DATE)+1  
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');  
--Special cases: Jan 1-3 may belong to the previous year  
     IF (@ISOweek=0)   
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1   
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;  
--Special case: Dec 29-31 may belong to the next year  
     IF ((DATEPART(mm,@DATE)=12) AND   
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))  
          SET @ISOweek=1;  
     RETURN(@ISOweek);  
END;  
--GO  
--SET DATEFIRST 1;  
--SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';  

ImportBCP.sql

select * from TestDB.dbo.ImportBCP

---bcp.exe TestDB.dbo.ImportBCP in "E:\SQLData\Data\WorkOrder.csv" -T -c -t
---C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn

ImportFromOpenRowSet.sql

USE master
GO
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE; 
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParam', 1

--Điều kiện: phải cài Access database Engine 64 bit
USE TestDB;
GO
TRUNCATE TABLE WorkOrderOpenRowSet
INSERT INTO WorkOrderOpenRowSet
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0; Database=E:\SQLData\Data\WorkOrderOpenRowSet.xlsx', [Sheet1$]);
GO

--SELECT * FROM WorkOrderOpenRowSet

Index.sql

--Tao index trong bang TransactionTest theo NGAY
USE PartTest
CREATE TABLE TransactionTest(
	ID INT IDENTITY,
	NGAY DATETIME,
	SANPHAM_ID INT
) ON PScheme_NGD(NGAY)
GO
CREATE INDEX CI_SANPHAM_ID ON dbo.TransactionTest(SANPHAM_ID)

--Mac dinh INDEX se duoc phan doan theo INDEX cua bang
--Kiem tra xem index duoc phan doan
SELECT i.name AS IndexName, i.type_desc, ps.name PartitionName, ps.data_space_id 
FROM SYS.indexes i
JOIN SYS.partition_schemes ps
	ON ps.data_space_id = i.data_space_id
		WHERE i.name = 'CI_SANPHAM_ID'

	

--code để liệt kê các cột chứa trong index
SELECT ind.name AS Index_Name, col.name AS Column_Name
FROM SYS.indexes ind 
INNER JOIN SYS.index_columns ic 
    ON  ind.OBJECT_ID = ic.OBJECT_ID and ind.index_id = ic.index_id 
INNER JOIN SYS.COLUMNS col 
    ON ic.OBJECT_ID = col.OBJECT_ID and ic.column_id = col.column_id 
WHERE ind.name = 'CI_SANPHAM_ID'	


--Tao unique index 
CREATE UNIQUE INDEX UI_SANPHAM_ID ON TransactionTest(SANPHAM_ID)	
--unique index nay phai la tap con cua index key.
CREATE UNIQUE INDEX UI_SANPHAM_ID ON TransactionTest(SANPHAM_ID) ON [PRIMARY]
--chi dinh index tren primary group

Inline&MultipleStatementFunction.sql

--Ham kieu bang tra ve 1 bang 
--dang 1:in-line: chi co duy nhat mot cau lenh select duoc gui toi Server
USE AdventureWorks
GO
CREATE FUNCTION dbo.fn_ProductInfoByModelId(@p INT) 
RETURNS TABLE AS
RETURN
		SELECT P.ProductID, P.Name, P.ProductNumber
		FROM Production.Product P
		WHERE P.ProductModelID = @p
GO
--goi ham truc tiep
SELECT * FROM dbo.fn_ProductInfoByModelId(5)

--dang 2:multiple-statement nhieu hon 1 lenh duoc gui toi server
CREATE FUNCTION dbo.fnCSVStr2Table(@CSVStr VARCHAR(8000))
RETURNS @Tbl TABLE(ValueColumn VARCHAR(1000))
AS
BEGIN 
	DECLARE @SubStr VARCHAR(100)
	DECLARE @i INT
	SET @i = CHARINDEX(',',@CSVStr, 0)
	WHILE @i > 0
	BEGIN
		SET @SubStr = LTRIM(RTRIM(LEFT(@CSVStr,@i-1)))
		INSERT INTO @Tbl
		SELECT @SubStr
		SET @CSVStr = SUBSTRING(@CSVStr, @i+1,8000)
		SET @i = CHARINDEX(',',@CSVStr,0)
	END
	--khi khong tim thay dau phay tri se co @i = 0, insert phan substring duoc luu trong @CSVStr
	INSERT INTO @Tbl
	SELECT LTRIM(RTRIM(@CSVStr))
	RETURN
END


SELECT * FROM fnCSVStr2Table('HANOI, HAIPHONG, THANH HOA')

--Ham multi-statement se phai khai bao dinh dang cua bang va phan than chuong trinh gom cac ham lenh thao tac du lieu va ket thuc
--la ham return vi ket qua da duoc khai bao trong @Tbl. Trai nguoc voi in-line khong can khai bao bang va return la mot cau lenh select

--Xem phuong an thuc thi
SELECT TH.*,P.Name
FROM Production.TransactionHistory TH
JOIN dbo.fn_ProductInfoByModelId(5) P ON TH.ProductID = P.ProductID

--chuyen ham fn_ProductInfoByModelId ve multi-statement
DROP FUNCTION dbo.fn_ProductInfoByModelId

CREATE FUNCTION dbo.fn_ProductInfoByModelID_MSTV(@p INT) 
RETURNS @t TABLE(ProductID INT, Name NVARCHAR(50), ProductNumber NVARCHAR(25))
AS
BEGIN
	INSERT INTO @t
	SELECT P.ProductID, P.Name, P.ProductNumber
	FROM Production.Product P 
	WHERE P.ProductModelID = @p
 
	RETURN
END


SELECT *
FROM Production.TransactionHistory TH
JOIN dbo.fn_ProductInfoByModelID_MSTV(5) P ON TH.ProductID = P.ProductID

PartitionTable.sql

--Tao database va file group
CREATE DATABASE PartTest
GO
USE PartTest
GO
--Tao filegroup
ALTER DATABASE PartTest ADD FILEGROUP FG1
ALTER DATABASE PartTest ADD FILEGROUP FG2
ALTER DATABASE PartTest ADD FILEGROUP FG3

--Them data vao moi File group
ALTER DATABASE PartTest ADD FILE (NAME = N'FG1', FILENAME = N'E:\SQLData\Data\PartTest\FY2015.ndf') TO FILEGROUP FG1
ALTER DATABASE PartTest ADD FILE (NAME = N'FG2', FILENAME = N'E:\SQLData\Data\PartTest\FY2016.ndf') TO FILEGROUP FG2
ALTER DATABASE PartTest ADD FILE (NAME = N'FG3', FILENAME = N'E:\SQLData\Data\PartTest\FY2017.ndf') TO FILEGROUP FG3


--Tao partition function va partition scheme
USE PartTest
GO
CREATE PARTITION FUNCTION PFunction(DATETIME) AS RANGE RIGHT FOR VALUES ('2016-01-01','2017-01-01')
GO
CREATE PARTITION SCHEME PScheme_NGD AS PARTITION PFunction to (FG1,FG2,FG3)
--Ham PFunction se cat cac doan vao tung file group (o day co 2 doan va 3 khoang)
--khoang 1: < 2016-01-01
--khoang 2: tu 2016-01-01 den 2016-12-31
--khoang 3: > 2017-01-01
--Ham PScheme_NGD dung de dinh cac doan vao tung file group

--Tao bang dung partition scheme
USE PartTest
GO
CREATE TABLE dbo.Dt(
	Dt DATETIME
) ON PScheme_NGD(Dt)
GO
CREATE CLUSTERED INDEX CI_Dt on dbo.Dt(Dt) ON PScheme_NGD(Dt)
--Kiem tra phan doan du lieu
SELECT $PARTITION.PFunction('2017-03-31')
SELECT $PARTITION.PFunction('2016-03-31')
SELECT $PARTITION.PFunction('2015-03-31')

--Lua chon partition 3
SET STATISTICS TIME ON
SELECT * FROM Dt WHERE $PARTITION.PFunction(Dt) = 3
SET STATISTICS TIME OFF


SET STATISTICS TIME ON
SELECT * FROM Dt 
SET STATISTICS TIME OFF


--Khi rebuild index chi can rebuild cho mot doan
ALTER INDEX CI_Dt
ON dbo.Dt
Rebuild PARTITION = 3

Pivotable.sql

SELECT SellStartDate, 
[0], [1], [2], [3], [4]
FROM
(SELECT SellStartDate,DaysToManufacture, StandardCost 
    FROM AdventureWorks.Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

ProcedureImportCSVFile.sql

USE TestDB
GO
ALTER PROCEDURE dbo.ImportCSV(
	@Path VARCHAR(MAX),
	@Table VARCHAR(MAX),
	@FormatFilePath VARCHAR(MAX),
	@FirstRow VARCHAR(MAX)
) AS
BEGIN
	DECLARE @Sql VARCHAR(MAX)
	SET @Sql = '
	BULK INSERT ' + @Table + ' FROM '''+@Path+ '''
		WITH (
			FORMATFILE = '''+@FormatFilePath+''',
			FIRSTROW = '+@FirstRow+')'
	PRINT @Sql
	EXEC sp_sqlexec @Sql
END


--BULK INSERT TestDB.dbo.WorkOrderImportFromCSV 
--FROM 'E:\SQLData\Data\WorkOrder.csv'
--WITH (FORMATFILE = 'E:\SQLData\Data\WorkOrder.Fmt',
--	FIRSTROW = 2)
GO
EXEC TestDB.dbo.ImportCSV 'E:\SQLData\Data\WorkOrder.csv','TestDB.dbo.WorkOrderImportFromCSV','E:\SQLData\Data\WorkOrder.Fmt',2

ProcedureImportTextFile.sql

USE TestDB
GO
ALTER PROCEDURE dbo.ImportTextFile(
	@Path VARCHAR(MAX),
	@Table VARCHAR(MAX),
	@FieldTerminator VARCHAR(MAX),
	@RowTerminator VARCHAR(MAX),
	@FirstRow VARCHAR(MAX)
) AS
BEGIN
	DECLARE @Sql VARCHAR(MAX)
	SET @Sql = '
	BULK INSERT ' + @Table + ' FROM '''+@Path+ '''
		WITH (
			FIELDTERMINATOR = '''+@FieldTerminator+''',
			FIRSTROW = '+@FirstRow+',ROWTERMINATOR = '''+@RowTerminator+'''
			)'

	PRINT @Sql
	EXEC sp_sqlexec @Sql
END
GO


	--BULK INSERT TestDB.dbo.WorkOrderImportFromTextFile FROM 'E:\SQLData\Data\WorkOrder.txt'
	--	WITH (
	--		FIELDTERMINATOR = ',',
	--		FIRSTROW = 2,
	--		ROWTERMINATOR = '\n'
	--		)

	--Lưu ý: ROWTERMINATOR = '\n' khi kết thúc dòng là xuống dòng

-- EXEC TestDB.dbo.ImportTextFile 'E:\SQLData\Data\WorkOrder.txt','TestDB.dbo.WorkOrderImportFromTextFile',',','\n',2

ProcedureSelectWorkOrder.sql

USE TestDB
GO
ALTER PROCEDURE dbo.SelectWorkOrder
(
	@ProductID VARCHAR(MAX),
	@MinOrderQty VARCHAR(MAX)
) AS
BEGIN
DECLARE @sql VARCHAR(MAX) =''
SET @sql = 'SELECT * FROM TestDB.dbo.WorkOrder A WHERE A.OrderQty >= '+@MinOrderQty+'AND LEFT(A.ProductID,2) = '+@ProductID
PRINT @sql
EXEC sp_sqlexec @sql
PRINT 'Select Successfully'
END


--EXEC dbo.SelectWorkOrder 71,10

SetIdentityInsertOn.sql

SET IDENTITY_INSERT TestDB.dbo.WorkOrder ON
INSERT INTO TestDB.dbo.WorkOrder
([WorkOrderID]
    ,[ProductID]
    ,[OrderQty]
    ,[StockedQty]
    ,[ScrappedQty]
    ,[StartDate]
    ,[EndDate]
    ,[DueDate]
    ,[ScrapReasonID]
    ,[ModifiedDate]
    ,[SegmentOrder])

SELECT A.*,B.Segment FROM [AdventureWorks].[Production].[WorkOrder] A
CROSS APPLY  TestDB.dbo.Segment(A.OrderQty,10,20,50) B
   
SET IDENTITY_INSERT TestDB.dbo.WorkOrder ON

SwitchOutDataByPartition.sql

CREATE DATABASE SwitchOut
GO
--Tao cac file group
ALTER DATABASE SwitchOut ADD FILEGROUP FG1
ALTER DATABASE SwitchOut ADD FILEGROUP FG2
ALTER DATABASE SwitchOut ADD FILEGROUP FG3
ALTER DATABASE SwitchOut ADD FILEGROUP FG4
--Them data vao moi file group
ALTER DATABASE SwitchOut ADD FILE (NAME = 'F1', FILENAME = 'E:\SQLData\Data\SwitchOut\SwitchOut_F1.ndf') TO FILEGROUP FG1
ALTER DATABASE SwitchOut ADD FILE (NAME = 'F2', FILENAME = 'E:\SQLData\Data\SwitchOut\SwitchOut_F2.ndf') TO FILEGROUP FG2
ALTER DATABASE SwitchOut ADD FILE (NAME = 'F3', FILENAME = 'E:\SQLData\Data\SwitchOut\SwitchOut_F3.ndf') TO FILEGROUP FG3
ALTER DATABASE SwitchOut ADD FILE (NAME = 'F4', FILENAME = 'E:\SQLData\Data\SwitchOut\SwitchOut_F4.ndf') TO FILEGROUP FG4
GO

USE SwitchOut
GO
--tao Partition Function (Range left se lay max la cut point, min la cut point lien truoc +1)
CREATE PARTITION FUNCTION PF_SwitchOut(INT) AS RANGE RIGHT FOR VALUES (1000,2000,3000)
--tao Partition Scheme
CREATE PARTITION SCHEME PS_SwitchOut AS PARTITION PF_SwitchOut TO (FG1,FG2,FG3,FG4)
--tao bang se partition du lieu theo ID vao cac file group
CREATE TABLE MyPartTable
(
	ID INT IDENTITY PRIMARY KEY,
	MyData VARCHAR(100)
) ON PS_SwitchOut(ID)

--chen them du lieu vao bang
DECLARE @ID TABLE (ID int)
DECLARE @StartId int
DECLARE @EndId int

SET @StartId = 1
SET @EndId = 10000

WHILE(@StartId <= @EndId)
BEGIN
	INSERT INTO @ID
	SELECT @StartId
	SET @StartId = @StartId + 1
END

INSERT INTO MyPartTable(MyData)
SELECT * FROM @ID


--Chuyen du lieu tu 1 bang sang 1 bang khac ngay tuc thi
--Tao bang tam
CREATE TABLE dbo.Tbl_1000 (
	ID INT IDENTITY PRIMARY KEY,
	MyData VARCHAR(100)
) ON FG1 
GO
--Switch out toi Tbl_1000
ALTER TABLE MyPartTable SWITCH PARTITION 1 TO Tbl_1000
--Kiem tra ket qua
SELECT * FROM MyPartTable
SELECT * FROM Tbl_1000
--nhu vay du lieu da duoc cat vao Tbl_1000

TestIndex.sql

--Hieu nang cua create INDEX
--Khong su dung INDEX
DECLARE @TEST_PLAYER TABLE (ID int, MOST_PLAYER_ID int)
DECLARE @Start int
DECLARE @End int
DECLARE @i datetime
DECLARE @e datetime
SET @Start = 1
SET @End = 10000

WHILE (@Start < @End)
	BEGIN
		INSERT INTO @TEST_PLAYER
		SELECT @Start, @Start + 1000
		SET @Start = @Start + 1
	END
SET @i = GETDATE()
SELECT * FROM @TEST_PLAYER
SET @e = GETDATE()

SELECT DATEDIFF(MILLISECOND,@i,@e)
--253

--su dung index
DECLARE @TEST_PLAYER TABLE(ID int INDEX Ix CLUSTERED, MOST_PLAYER_ID int)
DECLARE @Start int
DECLARE @End int
DECLARE @i datetime
DECLARE @e datetime
SET @Start = 1
SET @End = 10000
WHILE (@Start < @End)
BEGIN
	INSERT INTO @TEST_PLAYER
	SELECT @Start, @Start + 1000
	SET @Start = @Start + 1
END
SET @i = GETDATE()
SELECT * FROM @TEST_PLAYER
SET @e = GETDATE()
SELECT DATEDIFF(MILLISECOND,@i,@e)
--253
DROP TABLE ##TEST_PLAYER
CREATE TABLE ##TEST_PLAYER(ID int INDEX Ix NONCLUSTERED, MOST_PLAYER_ID int)
DECLARE @Start int
DECLARE @End int
DECLARE @i datetime
DECLARE @e datetime
SET @Start = 1
SET @End = 100000
WHILE (@Start < @End)
BEGIN
	INSERT INTO ##TEST_PLAYER
	SELECT @Start, @Start + 1000
	SET @Start = @Start + 1
END
SET @i = GETDATE()
SELECT * FROM ##TEST_PLAYER
SET @e = GETDATE()
SELECT DATEDIFF(MILLISECOND,@i,@e)
--1893
DROP TABLE ##TEST_PLAYER
CREATE TABLE ##TEST_PLAYER(ID int,MOST_PLAYER_ID int)
DECLARE @Start int
DECLARE @End int
DECLARE @i datetime
DECLARE @e datetime
SET @Start = 1
SET @End = 100000
WHILE (@Start < @End)
BEGIN
	INSERT INTO ##TEST_PLAYER
	SELECT @Start, @Start + 1000
	SET @Start = @Start + 1
END
SET @i = GETDATE()
SELECT * FROM ##TEST_PLAYER
SET @e = GETDATE()
SELECT DATEDIFF(MILLISECOND,@i,@e)
--1916

TestTable.sql

/*    ==Scripting Parameters==

    Source Server Version : SQL Server 2016 (13.0.1742)
    Source Database Engine Edition : Microsoft SQL Server Enterprise Edition
    Source Database Engine Type : Standalone SQL Server

    Target Server Version : SQL Server 2016
    Target Database Engine Edition : Microsoft SQL Server Enterprise Edition
    Target Database Engine Type : Standalone SQL Server
*/

USE [TestDB]
GO

/****** Object:  Table [dbo].[ConvertTextFile]    Script Date: 9/5/2017 5:38:19 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ConvertTextFile](
	[WorkOrderID] [float] NULL,
	[ProductID] [float] NULL,
	[OrderQty] [float] NULL,
	[StockedQty] [float] NULL,
	[ScrappedQty] [float] NULL,
	[StartDate] [float] NULL,
	[EndDate] [float] NULL,
	[DueDate] [float] NULL,
	[ScrapReasonID] [float] NULL,
	[ModifiedDate] [float] NULL,
	[SegmentOrder] [varchar](255) NULL
) ON [PRIMARY]
GO

backup.sql

--Dieu kien backup
--1.Database de o che do RECOVERY MODE la full
--2.Database da tung duoc full backup
--3.Log file chua tung bi SHRINK
--Tao Database
USE master
GO
IF DB_ID('TestDB') IS NOT NULL DROP DATABASE TestDB
GO 
CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE dbo.Table1(
	ID INT IDENTITY,
	TEN VARCHAR(32)
)
GO
INSERT INTO dbo.Table1(TEN)
SELECT A = 'Nguyen Van A' UNION ALL
SELECT B = 'Nguyen Van B' UNION ALL
SELECT C = 'Nguyen Van C'
GO
--full backup
BACKUP DATABASE TestDB TO DISK = 'E:\SQLData\Backup\TestDB.bak' WITH INIT
--2017-08-02 14:54:50.807
--Them du lieu moi sau khi full back up (can duoc thuc hien dinh ki cuoi tuan)
INSERT INTO dbo.Table1(TEN)
SELECT 'Nguyen Van D' UNION ALL
SELECT 'Nguyen Van E'
--differental backup (chi backup du lieu sau thoi diem full backup) (thuc hien vao 1 ngay trong tuan)
BACKUP DATABASE TestDB TO DISK = 'E:\SQLData\Backup\TestDB_Diff.bak' WITH DIFFERENTIAL
--Mo phong tinh huong database da duoc full backup ngay hom truoc, them moi du lieu ngay hom sau va bi xoa
DELETE FROM dbo.Table1
--Dong Database de khong cho ai co the truy cap du lieu den khi Database duoc khoi phuc xong. Chuyen sang che do 1 nguoi dung
--SINGLE_USER
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--Ghi lai thoi diem xay ra su co
SELECT GETDATE()
--2017-08-02 15:16:05.323
--Khoi phuc lai DB theo thu tu ban backup truoc roi den ban Log backup:
USE master
GO
RESTORE DATABASE TestDB FROM DISK = 'E:\SQLData\Backup\TestDB.bak' WITH NORECOVERY
RESTORE DATABASE TestDB FROM DISK = 'E:\SQLData\Backup\TestDB.trn' WITH STOPAT = '2017-08-02 15:16:05'

USE TestDB
SELECT * FROM dbo.Table1

--Chuyen Database ve che do nhieu nguoi dung
ALTER DATABASE TestDB SET MULTI_USER

---