Current use of extent			GAM bit setting		SGAM bit setting
	Free, not being used				1			0
	Uniform extent, or full mixed extent		0			0
	Mixed extent with free pages			0			1

	select committed_target_kb, committed_kb from sys.dm_os_sys_info;

	SELECT * FROM sys.dm_db_log_space_usage;	- returns space usage information for the transaction log

	-- shrink a log file to a specific target size
	ALTER DATABASE db1 SET RECOVERY SIMPLE;	- the file is first truncated by setting the database recovery model to SIMPLE
	DBCC SHRINKFILE (db1_Log, 1);	- shrink the truncated log file to 1 MB
	ALTER DATABASE db1 SET RECOVERY FULL;	- reset the database recovery model

	ALTER DATABASE db1 MODIFY FILE (name=db1_log, size = 200 MB);	- enlarge a log file
	
	-- add two log files
	ALTER DATABASE db1 ADD LOG FILE 
	( NAME = db1_log2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\db1_log2.ldf',
		SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ),
	( NAME = db1_log3, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db1_log3.ldf',
		SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB );

	-- create a database specifying data and transaction log files (size in MB, by default)
	CREATE DATABASE db1 ON
	(NAME = db1_dat, FILENAME = 'D:\Program Files\SQL Server\Data\db1.mdf', SIZE = 1000, MAXSIZE = 200000, FILEGROWTH = 5)
	LOG ON
	(NAME = db1_log, FILENAME = 'D:\Program Files\SQL Server\DATA\db1.ldf', SIZE = 50MB, MAXSIZE = 2500MB, FILEGROWTH = 5MB);
	
	select * from sys.databases;	- list all databases

	EXEC sp_detach_db 'db1', 'true';	- detach database
	CREATE DATABASE testdb ON (FILENAME = 'C:\mssql\testdb_data.mdf'), (FILENAME = 'C:\mssql\testdb_log.ldf') 
				FOR ATTACH;	- attach database

	CREATE TABLE t1 ( c1 int primary key identity(1,1), c2 varchar(255), INDEX ix_1 NONCLUSTERED (c2));
	
	EXEC sp_rename 'sales.t1', 't2';	- rename table
	
	ALTER TABLE t1 ADD col1 int NOT NULL, col2 varchar(20);	- add columns
	ALTER TABLE t1 DROP COLUMN col1;			- drop column
	EXEC sp_rename 'dbo.t1.col1', 'col2', 'COLUMN';		- rename column
	ALTER TABLE dbo.t1 ALTER COLUMN col1 DECIMAL(5,2);	- modify column

	CREATE TABLE dbo.products	- table with computed column
	(
	product_id int IDENTITY (1,1) NOT NULL,
	quantity smallint,
	unit_price money,
	inventory_value AS quantity * unit_price );
	
	ALTER TABLE dbo.products DROP COLUMN retail_value;	- drop a computed column
	ALTER TABLE dbo.products ADD retail_value AS (quantity * unit_price * 1.5);	- add a computed column

	IF OBJECT_ID('tempdb..#Results') IS NOT NULL	-- check if temp table exists
	     DROP TABLE #Results;

	select 'drop table ' + name from sys.sysobjects where type = 'U' and name like '%_old'; -- create drop statements for tables

	select cmd,* from sys.sysprocesses where blocked > 0;  -- find all blocked statements

	CREATE [CLUSTERED /NONCLUSTERED /UNIQUE] INDEX idx1 on table/view (col1,col2 etc.)
		WITH options
		ON [filegroup];

	CREATE NONCLUSTERED INDEX ix_address_postalcode	ON dbo.address (postalcode)
		INCLUDE (addr_line1, addr_line2, city, state);

	CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
	CREATE FULLTEXT INDEX ON dbo.candidate(resume) KEY INDEX uk_candidate  WITH STOPLIST = SYSTEM;

	-- find the average fragmentation percentage of all indexes in a table 
	SELECT a.index_id, name, avg_fragmentation_in_percent
	FROM sys.dm_db_index_physical_stats (DB_ID(N'HumanResDB'), OBJECT_ID(N'dbo.Employee'), NULL, NULL, NULL) AS a
	JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

	CREATE XML SCHEMA COLLECTION dbo.nounsSchema AS
	'<?xml version="1.0">
	   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
               <xsd:element name="Category" type="xsd:string" />
               <xsd:element name="arabic" type="xsd:string" />
               <xsd:element name="english"  type="xsd:string" />
		</xsd:schema>';

	CREATE TABLE dbo.words (id int PRIMARY KEY,
				nouns xml(dbo.nounsSchema));

	BEGIN
		statements
	END

	IF boolean expression
		BEGIN 
			sql statements
		END
	ELSE
		BEGIN
			sql statements
		END

	GOTO label1
	...
	label1:

	THROW 51000, 'The record does not exist.', 1;
	
	Msg 51000, Level 16, State 1, Line 1
	The record does not exist.

	BEGIN TRY
		statements
	END TRY
	BEGIN CATCH
		statements
	END CATCH

	WHILE boolean expression
		BEGIN 
			sql statements
			[BREAK] 	
			[CONTINUE]
		END

	CLOSE cursor1

	DEALLOCATE cursor_name

	DECLARE cursorname CURSOR
		[LOCAL | GLOBAL]
		[FORWARD_ONLY | SCROLL]
		FOR select_statement
		[FOR UPDATE OF columnname]

	FETCH [NEXT | PRIOR | FIRST | LAST]	FROM cursor1 INTO @variable1, @variable2....@variablen

	ALTER TABLE t1 add primary key (col1, col2, col3);
	ALTER TABLE t1 ADD CONSTRAINT fk_t1_col1 FOREIGN KEY (col1) REFERENCES RefTableName(RefColumnName);	- add a foreign key
	ALTER TABLE t1 ADD col1 (varchar(50));	- add a column
	ALTER TABLE tbl1 WITH NOCHECK ADD CONSTRAINT CK_tbl1_Col1 CHECK (Col1 in (1,2));	- add a check constraint

	ALTER SCHEMA dbo TRANSFER AS0386.spAuthors1

	/*Using Merge to Insert/Update*/	
	MERGE dbo.UnitMeasure AS target
	USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
		ON (target.UnitMeasureCode = source.UnitMeasureCode)
	WHEN MATCHED THEN 
	UPDATE SET Name = source.Name
	WHEN NOT MATCHED THEN	
	    INSERT (UnitMeasureCode, Name)
	    VALUES (source.UnitMeasureCode, source.Name)
	    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;

	/*Using Merge to Update/Delete*/	
	CREATE PROCEDURE dbo.usp_UpdateInventory @OrderDate datetime AS
		MERGE dbo.ProductInventory AS target
			USING (SELECT ProductID, SUM(OrderQty) FROM dbo.SalesOrderDetail AS sod
			JOIN dbo.SalesOrderHeader AS soh
				ON sod.SalesOrderID = soh.SalesOrderID
				AND soh.OrderDate = @OrderDate
				GROUP BY ProductID) AS source (ProductID, OrderQty)
			ON (target.ProductID = source.ProductID)
			WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
				THEN DELETE	
			WHEN MATCHED 
				THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty, 
					target.ModifiedDate = GETDATE()
			OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
				Deleted.Quantity, Deleted.ModifiedDate;

	DECLARE @local_variable data_type;

	KILL sessionid;

	[ WITH common_table_expression] 
	SELECT select_list [ INTO new_table ] 
	[ FROM table_source ] [ WHERE search_condition ] 
	[ GROUP BY group_by_expression [WITH CUBE | ROLLUP] ] 
	[ HAVING search_condition ] 
	[ ORDER BY order_expression [ ASC | DESC ] ] 
	[ FOR XML [RAW | AUTO | PATH | EXPLICIT] ]

	select * from vw_nouns FOR XML RAW;  -- RAW mode uses a generic row tag
	select * from vw_nouns FOR XML RAW, XMLSCHEMA;  -- returns the schema of the xml output along with the result

	SET @local_variable = expression

	with t as
		(select distinct * from [DB1].[dbo].[tbl1])
			select COUNT(*) from t;

	Simple CASE function:
		CASE input expression
			WHEN cond1 THEN result1
			WHEN cond2 THEN result2
			.......
			ELSE result_else
		END

	Searched CASE function:
		CASE
			WHEN boolean expression THEN result1
			.....
			ELSE result_else
		END

	CAST (expression AS data_type)
	CONVERT (data_type [(length)], expression [,style])

	SELECT title, ytd_sales FROM tblBooks WHERE CAST (ytd_sales AS char[20]) LIKE '3%'
	SELECT title, ytd_sales FROM tblBooks WHERE CONVERT (char[20], ytd_sales) LIKE '3%'

	-- count the no of Y's
	SELECT LENGTH(col) - LENGTH(REPLACE(col, 'Y', ''))

	PATINDEX ('%pattern%', string_expression)

	select fn,  patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,fn) as [Position],
	substring(fn,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,fn),1) as [InvalidCharacter],
	ascii(substring(fn,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,fn),1)) as [ASCIICode]
	from  #vvk1
	where patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,fn) > 0;

 	SELECT x = SUBSTRING ('abcdef', 2, 3) 	-- 'bcd'

	DECLARE @nstring nchar(12)
	SET @nstring = N'Åkergatan 24'
	SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring))

	returns the result set:
	------    - 
	197       Å

	SELECT Name, Salary, RANK() OVER(ORDER BY Salary DESC) as Ranking FROM Employees;

	SELECT Name, Dept, Salary, DENSE_RANK() OVER(PARTITION BY Dept ORDER BY Salary DESC) as DenseRanking FROM Employees;

	SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, recovery_model_desc FROM sys.databases WHERE database_id < 5;

	DECLARE @var1 int;	- declare a variable
	
	SET @var1 = 10;		- set variable to a value
	
	SELECT @var1 = MAX(emp_id) FROM hr.employee;	- set variable to the value of an expression

	declare @t1 table (rowid int identity(1,1), col1 char(2), col2 char(2);
	insert into @t1 select id, ovf, xfer from shipments where status = 'U';
	declare @rows int, @rowid int;
		select @rows = count(1) from @t1;
		while (@rows > 0) 
			begin     
				select top 1 @rowid = rowid, @ovfnbr = col1, @xfernbr = col2 from @t1; 
				/* perform row processing */
				delete from @t1 where rowid = @rowid;
				select @rows = count(1) from @t1;
			end

	create proc dbo.usp_kwi_ExtractCustRecords
		@parm1 char(23),
		@parm2 char(23) as    
	set nocount on

	declare @telephoneno varchar(16), @emailaddress varchar(50);
	declare @transsttime datetime, @transendtime datetime;

	set @transsttime = cast(@parm1 as datetime)
	set @transendtime = cast(@parm2 as datetime)

	create table #tempcust
	 (		telephoneno varchar(16),
			emailaddress varchar(50)   )

	   declare cursorcust cursor for
		select telephone_no1, email_address
				 from customer cu
				 where cu.entry_date_time >=  @transsttime  
				 and cu.entry_date_time <= @transendtime 
			
		open cursorcust
		fetch next from cursorcust into @telephoneno,  @emailaddress;
		
		while @@fetch_status = 0
		begin
		insert into #tempcust values 	(@telephoneno,  @emailaddress );	
					fetch next from cursorcust into  @telephoneno, @emailaddress;
		 end
		
	select * from #tempcust
	cleanup:
		close cursorcust;
		deallocate cursorcust;
		drop table #tempcust;
		return;
	go

	exec sp_lock; -- displays all locks
	exec sp_lock 53; - displays locks for a sessionid

	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'WHI', @recipients = 'vkandasamy@paypal.com', @subject = 'OE Gen 4.5: New Makes Alert',
		@body = 'Automated Message: New Makes have been received in the OE Gen 4.5 process';

	EXEC sp_spaceused @updateusage = N'TRUE'; -- for current DB, returns current values
	EXEC sp_spaceused N'dbo.vendor';   -- find space used by a table
	EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"; -- find space used by all tables

	EXEC sp_who;  -- displays statistics for all users/sessions
	EXEC sp_who 'janetl';  -- show info on a single user login
	EXEC sp_who '10';  - show info by sessionid

	select @var1 = [dbo].[fnConvertBintoStr] (@var2,@var3);

	ALTER FUNCTION [dbo].[fnGetCountryForState]
	(@InState char(2))
	RETURNS char(2)
	AS
	BEGIN
	declare @outCtryCde char(2);
		if @InState in ('AB','BC','MB','NB','NL','NT', 'NS','NU','ON','PE','PQ','QC','SK','YT','NF')
			set @outCtryCde = 'CA';
		else
			set @outCtryCde = 'US';
		return @outCtryCde;
	END

	CREATE FUNCTION [dbo].[fnGetLocationInfo]
	(	
		@RegNumber 	char(9),
		@CompanyCde CHAR(3),
		@OrigCde CHAR(2),
		@OrigNbr CHAR(2)
	)
	RETURNS TABLE 
	AS
	RETURN 
	(	SELECT 	rtrim(LCTN_ADDR1) + ' ' + rtrim(LCTN_ADDR2) as LCTN_ADDR, 
			LCTN_CITY_NME,
			lctn_st_cde = case lctn_st_cde
				when 'PQ' then 'QC'
				when 'NF' then 'NL'	
				else lctn_st_cde end,	
			LCTN_ZIP_CODE,
			LCTN_COUNTRY_CDE FROM SH_LOCATION 
			where REG_NBR =  @RegNumber and COMPANY_CDE = @CompanyCde
				AND LCTN_TYP_CDE = @OrigCde and LCTN_TYP_NBR = @OrigNbr);

	SHUTDOWN;	- wait for currently running statements to finish and then stop the DB Engine
	SHUTDOWN WITH NOWAIT;	- stop immediately

	sp_configure;	- display all options
	
	sp_configure 'show advanced options', 1;
	RECONFIGURE;

	sp_configure 'max server memory', 4096;	- set max memory to 4GB
	RECONFIGURE;

	SELECT physical_memory_in_use_kb/1024 'physical_memory_in_use_MB',	- currently allocated memory
	large_page_allocations_kb/1024 'large_page_allocations_MB', 
	locked_page_allocations_kb/1024 'locked_page_allocations_MB',
	virtual_address_space_reserved_kb/1024 'VAS_reserved_MB', 
	virtual_address_space_committed_kb/1024 'VAS_committed_MB', 
	virtual_address_space_available_kb/1024 'VAS_available_MB',
	page_fault_count,
	memory_utilization_percentage, 
	process_physical_memory_low, 
	process_virtual_memory_low
	FROM sys.dm_os_process_memory;

	SELECT CONVERT (varchar, SERVERPROPERTY('collation'));		- server collation
	SELECT name, collation_name FROM sys.databases;			- database collation
	SELECT name, collation_name FROM sys.columns WHERE name = N'colname';	- column collation
	
	SELECT * FROM fn_helpcollations();	- list of supported collations

	CREATE TABLE t1 (
	LatinColumn nvarchar(15) COLLATE latin1_general_cs_as,
	FrenchColumn ncarchar(15) COLLATE french_100_cs_as);

	SELECT @@max_connections;

	SELECT * FROM sys.dm_tran_locks WHERE resource_type = 'DATABASE' AND resource_database_id = DB_ID('dbname');	- current connections
	SELECT * FROM sys.dm_exec_requests WHERE database_id = DB_ID('dbname');		- connections executing tasks

	DBCC SHRINKDATABASE ( dbname|dbid|0  [, target_percent] [, NOTRUNCATE|TRUNCATEONLY] );
			0 - current database is used
			target_percent - percentage of free space to be left in the database file after shrinking
			NOTRUNCATE - applicable only to data files, the free space at the end of file is not released to the OS
			TRUNCATEONLY - releases all free space at the end of the file to the operating system

	DBCC DBREINDEX ("HumanResources.Employee", PK_Employee_BusinessEntityID,80);
		80 is the fillfactor, the percentage of space on each index page for storing data

	DBCC UPDATEUSAGE (dbname) WITH NO_INFOMSGS;

	DBCC CHECKDB('dbname');
	DBCC CHECKDB('dbname') REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD;

		REPAIR_ALLOW_DATA_LOSS - tries to repair all reported errors. These repairs can cause some data loss
		REPAIR_FAST - maintains syntax for backward compatibility only. No repair actions are performed
		REPAIR_REBUILD - performs repairs that have no possibility of data loss

	DBCC USEROPTIONS

	DBCC HELP ('CHECKDB');
	DBCC HELP ('?');	- returns all DBCC statements for which help is available

• Restarting SQL Server. ALTER DATABASE MODIFY FILE can be used to configure the maximum size of the data and log files if necessary.

  	ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = target_size_in_MB);
  	ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = target_size_in_MB);

• dbcc shrinkdatabase
• dbcc shrinkfile

	SELECT TOP 1000 b.name, a.* From sys.dm_exec_procedure_stats a, sys.objects b
	where  a.object_id = b.object_id and DB_NAME(a.database_id) = 'db1';

	select top 100 session_id, start_time, status, command, sql_handle, total_elapsed_time/1000 'elapsed_time_seconds'
		from sys.dm_exec_requests order by total_elapsed_time desc;		- long running queries

	select * from sys.dm_exec_sessions;

	select * from sys.dm_exec_sql_text (sql_handle);

	declare    @spid int,   @stmt_start int,   @stmt_end int,   @sql_handle binary(20);
	set @spid = 59; -- fill this in

	select top 1
	@sql_handle = sql_handle,
	@stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end,
	@stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
	from master.dbo.sysprocesses
	where spid = @spid
	order by ecid;

	SELECT
		SUBSTRING(	text,
			COALESCE(NULLIF(@stmt_start, 0), 1),
			CASE @stmt_end
				WHEN -1 THEN DATALENGTH(text)
				ELSE (@stmt_end - @stmt_start)
			END
				)
	FROM ::fn_get_sql(@sql_handle);

	select * FROM sys.dm_os_process_memory;

	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SNAPSHOT/SERIALIZABLE;

Resource	Description
RID		A row identifier used to lock a single row within a heap
KEY		A row lock within an index used to protect key ranges in serializable transactions
PAGE		An 8KB page in a database, such as data or index pages
EXTENT		A contiguous group of eight pages
HoBT		A heap or B-tree. A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index
TABLE		The entire table, including all data and indexes
FILE		A database file
APPLICATION	An application-specified resource
METADATA	Metadata locks
ALLOCATION_UNIT	An allocation unit
DATABASE	The entire database

	select * from sys.dm_tran_locks;

	-- display current lock requests
	SELECT
	dtl.request_session_id AS SPID,
	DB_NAME(dtl.resource_database_id) AS DBName,
	dtl.resource_type AS LockedResource,
	CASE
	WHEN dtl.resource_type = 'OBJECT' THEN o.object_id
	WHEN dtl.resource_type = 'KEY' THEN i.object_id
	END AS LockedObjId,
	CASE
	WHEN dtl.resource_type = 'OBJECT' THEN o.name
	WHEN dtl.resource_type = 'KEY' THEN i.name
	END AS LockedObjName,
	dtl.request_mode AS LockType,
	dtl.request_status AS LockStatus,
	es.program_name AS ProgramName,
	es.original_login_name AS OrigLoginName,
	st.text AS SQLText
	FROM sys.dm_tran_locks dtl
	left join sys.partitions p ON dtl.resource_associated_entity_id = p.partition_id AND dtl.resource_type = 'KEY'
	left join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
	left join sys.objects o ON dtl.resource_associated_entity_id = o.object_id AND dtl.resource_type = 'OBJECT'
	join sys.dm_exec_sessions es ON dtl.request_session_id = es.session_id
	JOIN sys.dm_exec_connections dc ON dc.session_id = es.session_id
	CROSS APPLY sys.dm_exec_sql_text(dc.most_recent_sql_handle) AS st
	WHERE resource_database_id = db_id()
	ORDER BY dtl.request_session_id;

	SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail
	WHERE UnitPrice < $5.00  
	GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty
	OPTION (MAXDOP 2);

	select * from openquery(linkedserver, 'select * from t1');

	set showplan_all on;	- returns execution information for each statement without executing it
	set statistics profile on;	- displays profile information for the statement after the resultset
	set statistics xml on;	- returns execution information in an XML document

	bcp {table_name | view_name | "query"} {in data_file | out data_file | queryout data_file | format nul}

	--import from the second row
	bulk insert tblmageMap from 'C:\imagemap.txt'
			with (firstrow=2, fieldterminator='\t', rowterminator = '\n');

	DECLARE @SQL nvarchar(1000), @ACES_XML XML;

	SET @SQL =  'SELECT @a = CONVERT(xml, BulkColumn, 2) FROM OPENROWSET( BULK ''C:\temp\test.xml'', SINGLE_BLOB) AS TempXML; '
	EXEC sp_Executesql  @sql , N'@a XML OUTPUT',@ACES_XML OUTPUT

	SELECT  ref.value('(./PartType/@id)[1]','varchar(10)') parttypeID,
		ref.value('(./MfrLabel)[1]','varchar(200)') MfrLabel,
		ref.value('(./Part)[1]','varchar(20)') Part
	 into dbo.Specialty_friction_20120919	 FROM @ACES_XML.nodes('parts/App') as R(ref);

	namespace XMLLoadSQL
	{
		class XMLLoadSQL
		{
			private static Boolean FileValid;
			static String sAppPath = "C:\\SQLServer\\XMLBulkLoad";
			static void Main(string[] args)
			{
				SQLXMLBULKLOADLib.SQLXMLBulkLoad objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad();
				objBL.ConnectionString = "provider=SQLOLEDB;data source=CSCINDAE747976;database=AEDB;integrated security=SSPI";
				objBL.ErrorLogFile = "c:\\sqlserver\\XMLBulkLoad\\error.log";

			   //Validate the data file prior to bulkload
				String sOutput = ValidateFile("Nouns.xml", "", "NounsSchema.xml");
				Console.WriteLine (sOutput);

		if (FileValid == true) 
		{
	   // Check constraints and initiate transaction (if needed)
	   // objBL.CheckConstraints = True
	   // objBL.Transaction=True
	   //Execute XML bulkload using file.
		objBL.Execute(sAppPath + "\\" + "NounsSchema.xml", sAppPath + "\\" + "Nouns.xml");
		objBL= null;
		}
			}
		
	private static String ValidateFile(String strXmlFile,String strUrn, String strXsdFile)
	{
	   // Create a schema cache and add SampleSchema.xml to it.
	   MSXML2.XMLSchemaCache60 xs =  new MSXML2.XMLSchemaCache60();
		 
	   xs.add (strUrn, sAppPath + "\\" + strXsdFile);

	   // Create an XML DOMDocument object.
	   MSXML2.DOMDocument60 xd = new MSXML2.DOMDocument60();

	   // Assign the schema cache to the DOM document, schemas collection.
	   xd.schemas = xs;

	   // Load XML document as DOM document.
	   xd.async = false;
	   xd.load (sAppPath + "\\" + strXmlFile);

	   // Return validation results in message to the user.
	   if (xd.parseError.errorCode != 0)
			{FileValid = false ;
			 return ("Validation failed on " +  strXmlFile + "=====================" +
				 "Reason: " + xd.parseError.reason +  "Source: " + xd.parseError.srcText +
				  "Line: " +  xd.parseError.line) ;             }
		else
			{FileValid = true;
			 return ("Validation succeeded for " +
				 strXmlFile  +  "======================" + "Contents to be bulkloaded" );        }
		}
		}
	}

	XML file:
	<ROOT>
	  <Nouns>
		<Category>places</Category>
		<arabic>tareeq</arabic>
		<english>road</english>
	  </Nouns>
	  <Nouns>
		<Category>places</Category>
		<arabic>burj</arabic>
		<english>tower</english>
	  </Nouns>
	  <Nouns>
		<Category>places</Category>
		<arabic>bahr</arabic>
		<english>sea</english>
	  </Nouns>
	</ROOT>

	XML Schema file:
	<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
				xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
	   <xsd:element name="ROOT" sql:is-constant="1" >
		 <xsd:complexType>
		   <xsd:sequence>
			 <xsd:element name="Nouns" sql:relation="NounsXML" maxOccurs="unbounded">
			   <xsd:complexType>
				 <xsd:sequence>
				   <xsd:element name="Category" type="xsd:string" />
				   <xsd:element name="arabic" type="xsd:string" />
				   <xsd:element name="english"  type="xsd:string" />
				 </xsd:sequence>
			   </xsd:complexType>
			 </xsd:element>
		   </xsd:sequence>
		  </xsd:complexType>
		 </xsd:element>
	</xsd:schema>

	declare @bk_file varchar(100);
	set @bk_file = 'R:\SQLBackup\dbname_' + convert(varchar(50),GetDate(),112) 
		+ '_' + replace(convert(varchar(50),GetDate(),108),':','') + '.bak';
	BACKUP DATABASE [dbname] TO  DISK = @bk_file WITH NOFORMAT, NOINIT,  NAME = N'DBNAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, 
	COMPRESSION,  STATS = 10;

	RESTORE VERIFYONLY FROM DISK='C:\backup\db1.bak';		-- verifies if .bak file is valid
	RESTORE HEADERONLY FROM DISK='C:\backup\db1.bak';		-- get info on .bak file contents
	RESTORE FILELISTONLY FROM DISK='C:\backup\db1.bak';		-- list of db and log files contained in the backup
	RESTORE DATABASE db1 FROM DISK='C:\backup\db1.bak';
	
	RESTORE DATABASE db1 FROM DISK='C:\Backup\db1.bak' WITH MOVE N'db1' TO N'C:\vincentk\db1.mdf', 
			MOVE N'db1_log' TO N'C:\vincentk\db1_log.ldf';	-- move data/log files to a new location

	BACKUP DATABASE db1 TO DISK = 'Z:\Backups\db1.bak' WITH FORMAT, MEDIANAME = 'Z_Backups', NAME = 'Full Backup of db1';

	BACKUP DATABASE db1 TO DISK = 'Z:\Backups\db1.bak' WITH DIFFERENTIAL;

	BACKUP LOG db1 TO DISK = 'Z:\Backups\db1.bak';

	BACKUP DATABASE AEDB TO DISK = 'C:\Vincent\AEDB.bak' FORMAT, INIT				-- Full backup
	BACKUP DATABASE AEDB TO DISK = 'C:\Vincent\AEDB.bak' WITH DIFFERENTIAL, NOFORMAT, NOINIT,		-- Differential backup
		NAME = 'AEDB Diff bkup', COMPRESSION
	BACKUP LOG AEDB TO DISK = N'C:\Vincent\AEDB.bak' WITH NOFORMAT, NOINIT, NAME = N'AEDB Trans Log  Backup',	-- Log backup
		SKIP, NOREWIND, NOUNLOAD, STATS = 10
	BACKUP LOG AEDB TO DISK = N'C:\Vincent\AEDB.bak' WITH  NO_TRUNCATE , NOFORMAT, NOINIT,		-- Tail log backup
		NAME = N'AEDB Trans Log  Tail Backup', SKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 10

	RESTORE DATABASE AEDB FROM DISK = 'C:\Vincent\AEDB.bak'   ---> Restore of full bkup
		WITH FILE = 1, NORECOVERY                           	 (NORECOVERY puts db in Restoring mode)   
	RESTORE DATABASE AEDB FROM DISK = 'C:\Vincent\AEDB.bak'   ---> Restore of last diff bkup
		WITH FILE = 6, NORECOVERY
	RESTORE DATABASE AEDB FROM DISK = 'C:\Vincent\AEDB.bak'   ---> Restore of log bkup (file 7)
		WITH FILE = 7, NORECOVERY
	RESTORE DATABASE AEDB FROM DISK = 'C:\Vincent\AEDB.bak'   ---> Restore of log bkup (file 8)
		WITH FILE = 8, NORECOVERY
	RESTORE DATABASE AEDB FROM DISK = 'C:\Vincent\AEDB.bak'   ---> Restore of Tail log bkup
		WITH FILE = 9, RECOVERY

	EXEC sp_configure 'recovery interval','seconds'

	ALTER DATABASE … SET TARGET_RECOVERY_TIME =target_recovery_time { SECONDS | MINUTES }

	REVOKE CONNECT FROM GUEST;

	GRANT INSERT on object::dbo.table1 to user1;
	REVOKE DELETE on object::dbo.table1 from user2;
	DENY EXECUTE on object::dbo.usp1 from user3;

	SELECT * FROM fn_builtin_permissions(default);	- all permissions
	SELECT * FROM fn_builtin_permissions('schema');	- for a securable
	
	SELECT * FROM fn_my_permissions('table1', 'object');	- permissions granted to the executing principal on an object
	SELECT * FROM sys.database_permissions WHERE major_id = OBJECT_ID('table1');	- permissions for an object

					Nodes within an FCI				Replicas within an availability group
					-------------------				-------------------------------------
	Uses WSFC cluster		Yes						Yes
	Protection level		Instance					Database
	Storage type			Shared						Non-shared
	Storage solutions		Direct attached, SAN, mount points, SMB		Depends on node type
	Readable secondaries		No						Yes
	Applicable failover 		WSFC quorum					WSFC quorum
		policy settings		FCI-specific					Availability group settings
					Availability group settings
	Failed-over resources		Server, instance, and database			Database only