SET NOCOUNT ON
GO

USE MASTER
GO

/*

-- Cleanup

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
DROP DATABASE Dev
GO
DROP DATABASE Prod
GO
DROP DATABASE CodeCamp
GO
exec xp_cmdshell "cmd /c del c:\sample.cer"
exec xp_cmdshell "cmd /c del c:\sample.pvk"
exec xp_cmdshell "cmd /c del c:\codecamp.bak"
GO

*/

-- Create empty database

CREATE DATABASE CodeCamp
GO

USE CodeCamp
GO

CREATE TABLE Test (ID INT IDENTITY(1,1))

INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES

-- BACKUP DATABASE CodeCamp TO DISK = 'C:\CodeCamp.bak' WITH STOP_ON_ERROR, INIT

BACKUP DATABASE CodeCamp
    TO DISK = 'C:\CodeCamp.bak'
  WITH CHECKSUM, STOP_ON_ERROR, INIT

RESTORE VERIFYONLY
   FROM DISK = 'C:\CodeCamp.bak'

-- Use Visual Studio's hex editor to make a goof

RESTORE VERIFYONLY
   FROM DISK = 'C:\CodeCamp.bak'

-- RESTORE VERIFYONLY verifies:
--
-- That the backup set is complete and all volumes are readable.
-- Page ID (as if it were about to write the data)
-- Checksum (if present on the media)
-- Checking for sufficient space on destination devices

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

-- Demo: Security

USE CodeCamp
GO

-- DROP LOGIN MyLogin

-- Create a login with a weak password

CREATE LOGIN MyLogin WITH PASSWORD = 'password'

-- Create a login with a strong(er) password

CREATE LOGIN MyLogin WITH PASSWORD = 'P@ssw0rd'

-- You can skip the password policy

-- DROP LOGIN MyLogin

CREATE LOGIN MyLogin WITH PASSWORD = 'password', CHECK_POLICY = OFF

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

-- Demo: Encryption

USE CodeCamp
GO

-- DROP CERTIFICATE SampleCert

-- Create certificate

CREATE CERTIFICATE SampleCert
 ENCRYPTION BY PASSWORD = 'P@ssw0rd'
 WITH SUBJECT = 'SampleCertificate' 

-- Load certificate

-- First, create the certificate and private key files (go to the .NET command prompt)
-- MAKECERT.EXE c:\sample.cer -sv c:\sample.pvk
-- Use P@ssw0rd for all passwords

-- DROP CERTIFICATE SampleCert

-- Create Certificate
 
CREATE CERTIFICATE SampleCert
FROM FILE = 'C:\Sample.cer'
WITH PRIVATE KEY (FILE='C:\sample.pvk',ENCRYPTION BY PASSWORD  = N'P@ssw0rd',DECRYPTION BY PASSWORD  = N'P@ssw0rd')
 
-- Verify
 
SELECT * FROM SYS.CERTIFICATES
SELECT Cert_ID('SampleCert')
 
-- Encrypt
 
DECLARE @Encrypted varbinary(4000)
SET @Encrypted = EncryptByCert(Cert_ID('SampleCert'), 'SQL Server 2005 Rocks!')
SELECT @Encrypted AS Encrypted
 
-- Decrypt
 
SELECT CONVERT(VARCHAR,DecryptByCert(Cert_ID('SampleCert'),@Encrypted,N'P@ssw0rd'))
 
-- Done
 
DROP CERTIFICATE SampleCert

-- Encrypt by simple passphrase

DECLARE @Encrypted varbinary(4000)
SET @Encrypted = EncryptByPassPhrase('P@ssw0rd','SQL Server 2005 Rocks!')
SELECT @Encrypted

-- Decrypt by simple passphrase

SELECT CONVERT(varchar(30),DecryptByPassPhrase('P@ssw0rd',@Encrypted))

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

USE MASTER
GO

CREATE DATABASE DEV
GO

CREATE DATABASE PROD
GO

USE PROD
CREATE TABLE Test (ID INT NOT NULL)
GO
ALTER TABLE Test ADD CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (ID)
INSERT Test VALUES (1)
INSERT Test VALUES (2)
INSERT Test VALUES (3)

USE DEV
CREATE TABLE Test (ID INT NOT NULL,GUID uniqueidentifier)
GO
ALTER TABLE Test ADD CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (ID)
INSERT Test VALUES (1,NewID())
INSERT Test VALUES (2,NewID())
INSERT Test VALUES (3,NewID())

PRINT 'Prod Database'
SELECT * FROM Prod.dbo.Test
PRINT 'Dev Database'
SELECT * FROM Dev.dbo.Test

-- Run DBPro Schema Compare and Write the Changes

PRINT 'Prod Database'
SELECT * FROM Prod.dbo.Test
PRINT 'Dev Database'
SELECT * FROM Dev.dbo.Test

-- Run DBPro Data Compare and Write the Changes

PRINT 'Prod Database'
SELECT * FROM Prod.dbo.Test
PRINT 'Dev Database'
SELECT * FROM Dev.dbo.Test

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

-- Demo: MAX specifier

USE CodeCamp
GO

SET NOCOUNT ON

CREATE TABLE BigText (ID int, Data text)

INSERT BigText VALUES (1,'Hello World')
INSERT BigText VALUES (2,REPLICATE('x',10000))
SELECT * FROM BigText

-- Can't pass TEXT to functions :-(

SELECT UPPER(Data) FROM BigText

-- Use MAX instead!

DROP Table BigText
CREATE TABLE BigText (ID int, Data nvarchar(max))

INSERT BigText VALUES (1,'Hello World')
INSERT BigText VALUES (2,REPLICATE('x',10000))
SELECT * FROM BigText
SELECT UPPER(Data) FROM BigText

DROP Table BigText

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

-- Demo: TRY-CATCH

USE AdventureWorks
GO

-- DROP PROCEDURE MyProc

CREATE PROCEDURE MyProc
AS
BEGIN
 BEGIN TRY 
   SELECT * FROM Person.Address WHERE 1 > 2/0 -- Divide by zero!
 END TRY
 BEGIN CATCH
  PRINT 'Error Occurred'
  PRINT ERROR_NUMBER()
  PRINT ERROR_SEVERITY()
  PRINT ERROR_STATE()
  PRINT ERROR_MESSAGE()
 END CATCH
END
GO

EXEC MyProc 

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

-- Demo: OUTPUT IDENTITIES

USE CodeCamp
GO

SET NOCOUNT ON
GO

-- Create table

CREATE TABLE IdentityTest (ID int identity(1,1), Data varchar(20))
GO

-- Insert a row

INSERT IdentityTest (Data) Values ('Red')
SELECT * FROM IdentityTest

-- @@IDENTITY returns last identity value assigned

INSERT IdentityTest (Data) Values ('Blue')
SELECT @@IDENTITY

-- @@IDENTITY doesn't handle inserting multiple rows

INSERT IdentityTest (Data) SELECT Data FROM IdentityTest
SELECT @@Identity

-- Neither does SCOPE_IDENTITY()

INSERT IdentityTest (Data) SELECT Data FROM IdentityTest
SELECT SCOPE_IDENTITY()

-- Using INSERT ... with OUTPUT is the solution!

DECLARE @OutputTable TABLE (ID int, Data varchar(20))
INSERT IdentityTest (Data) 
       OUTPUT Inserted.ID, Inserted.Data INTO @OutputTable
       SELECT Data FROM IdentityTest
SELECT * FROM @OutputTable

DECLARE @OutputTable TABLE (ID int, OldData varchar(20), NewData varchar(20))
UPDATE IdentityTest SET Data = 'Blue'
       OUTPUT Inserted.ID, Deleted.Data, Inserted.Data INTO @OutputTable
        WHERE Data = 'Red' 
SELECT * FROM @OutputTable

-----------------------------------------------------------------------------------------
----------------------------------------------
-- FOR XML Improvements

USE ADVENTUREWORKS

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10
FOR XML raw

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10
FOR XML auto

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10
FOR XML AUTO, ELEMENTS

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10
FOR XML PATH

--

SELECT Title AS '@Title',
       EmailAddress as '@Email',
       FirstName as 'Name/First',
       LastName as  'Name/Last'
FROM   Person.Contact 
WHERE  ContactID < 10 
FOR XML PATH ('Dude'), ROOT ('Dudes')

SELECT Title AS '@Title',
       FirstName as 'Name/A/A/A/A/A/A/A/A/A/A/A/A/A/A/A/A/A/First',
       LastName as  'Name/A/A/A/A/A/A/A/A/A/A/A/A/A/A/A/A/A/Last',
       EmailAddress as 'Email'
FROM   Person.Contact 
WHERE  ContactID < 10 
FOR XML PATH ('Person'), ROOT ('People')

, XMLSCHEMA

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, ELEMENTS

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, ELEMENTS XSINIL

FOR XML AUTO, ELEMENTS XSINIL

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, XMLDATA

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, XMLSCHEMA('Pizza')

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, ELEMENTS, XMLSCHEMA

--

DECLARE @XML XML
SET @XML = 
(SELECT Title, FirstName, LastName, EmailAddress FROM Person.Contact
   WHERE ContactID < 10 FOR XML PATH)
SELECT @XML

-----------------------------------------------------------------------------------------

-- Demo: Unit Testing Spro

USE CodeCamp
GO

CREATE TABLE SomeData (ID int, Data text)

INSERT SomeData VALUES (1,'Richard')
INSERT SomeData VALUES (2,'Steve')
INSERT SomeData VALUES (3,'Jeff')

SELECT * FROM SomeData

-- Time permitting: Create a unit test

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

-- Demo: Unit Testing Spro
