/* 
Module 1 - working with Clollations
*/
-- 1.1 Obtain the Instance Collation from the GUI
--Create a Database without specifying a specific Collation
Create Database UnspecifiedCollationDB;
GO

-- Use the statement bellow(code) to verfiy that the new database inherited the Collation
USE UnspecifiedCollationDB;
GO

Select DB_NAME() as Current_Database, DATABASEPROPERTYEX(‘UnspecifiedCollationDB‘,‘Collation‘) DatabseCollation

-- 1.2 Create a Database with a Collation that overrides the Instance Default Collation
Create Database MultiLingualSpeakDB
    Collate Arabic_CI_AI
--Use The GUI to obtain the collation of the new database.

-- 1.3 Create Text-base Columns Within a Table with Collations that overide the Database
USE MultiLingualSpeakDB
Create Table MixedSpeakTable
    (
    ProductDI int IDENTITY
    ,EnglighProdName nvarchar(30)  COLLATE Latin1_General_CI_AI    NOT NULL
    ,ArabicProdName nvarchar(30)           NOT NULL
    ,GreeekProdName nvarchar(30)   COLLATE Greek_CS_AS_KS          NOT NULL
    ,JapaneseProdName nvarchar(30) COLLATE Japanese_90_CI_AS_KS_WS NOT NULL
);
--Use the GUI o drill down to the new table, then to one the columns and obtain 
--column Collation settting.

-- 1.4 Open a new query window to the tempDB database

USE tempdb
GO
--Retrieve and discuss the collation of the system and tempdb
Select SERVERPROPERTY(‘Collation‘) as SystemCollation,
    DATABASEPROPERTYEX(‘tempdb‘,‘Collation‘) as DatabaseCollation;
GO

-- Create and populate a table with different column collations
Create Table dbo.TestCharacter
(
    id int IDENTITY,
    CIData varchar(10) COLLATE Latin1_General_CI_AS,
    CSData varchar(10) COLLATE Latin1_General_CS_AS
)

INSERT INTO dbo.TestCharacter(CIData,CSData)
VALUES (‘Test Data‘,‘Test Data‘);
GO

-- Execute queries that try to match the same
-- values from eache column with all lower case
SELECT * FROM     dbo.TestCharacter
WHERE CIData=‘test data‘;
-- Now query the case-sensitive column
SELECT * FROM     dbo.TestCharacter
WHERE CSData=‘test data‘; -- No rows retruned
GO


--Execute a query to perform a case-insensitive
--search on the case-sensitive data
SELECT * FROM     dbo.TestCharacter
WHERE CSData=‘test data‘ COLLATE Latin1_General_CI_AS;

-- Try to execute a query that compares the two columns
-- that have different collations. this will fail
-- as the collation conflict cannot be resolved
SELECT * FROM     dbo.TestCharacter
WHERE CIData=CSData;
-- Execute the qery while specifying a collation
SELECT * FROM     dbo.TestCharacter
WHERE CIData=CSData  COLLATE Latin1_General_CI_AS;

/*
Module 1 Create a Database with Advanced Design; Multiple Data and Multiple Filegroups
*/

--Enable xp_CMDSHELL to run operating system commands with T-SQL code.

EXEC master.dbo.sp_configure ‘Show Advanced Options‘,1;
RECONFIGURE;
EXEC master.dbo.sp_configure ‘xp_CmdShell‘,1; 
RECONFIGURE;
-----------------

-- Make "Drive Latters" to simulate existence of may drive letters(LUNs)
-- for the advanced database.
USE master
Go
EXEC XP_CMDSHELL ‘MD c:\Drive_D‘, no_output
EXEC XP_CMDSHELL ‘MD c:\Drive_E‘, no_output
EXEC XP_CMDSHELL ‘MD c:\Drive_F‘, no_output
EXEC XP_CMDSHELL ‘MD c:\Drive_G‘, no_output
EXEC XP_CMDSHELL ‘MD c:\Drive_H‘, no_output
EXEC XP_CMDSHELL ‘MD c:\Drive_I‘, no_output
EXEC XP_CMDSHELL ‘MD c:\Backups‘, no_output
GO

-- 2.1 Create the AdvancedDB
CREATE DATABASE AdvancedDB
/* Scripte assumes the existence of c:\Drive_D etc, 
to SIMULATE multipledisk drives.
*/
ON Primary
-- NOTICE below non-uniform SIZE, MAXSIZE,and FILEGROUP parmerters!
(
    Name=AdvancedDBF1_PrimaryFG
        ,Filename=‘c:\Drive_D\AdvancedDB_F1_PrimaryFG.MDF‘
        ,Size=16MB
        ,MaxSize=30
        ,FileGrowth=10%
)
,FILEGROUP CurrentDataFG
    (
    Name=AdvancedDBF1_CurrentDataFG
        ,Filename=‘c:\Drive_E\AdvancedDB_F1_CDFG.ndf‘
        ,Size=6MB
        ,MaxSize=15
        ,FileGrowth=10%
    )
    ,(
    Name=AdvancedDBF2_CurrentDataFG
        ,Filename=‘c:\Drive_E\AdvancedDB_F2_CDFG.ndf‘
        ,Size=6MB
        ,MaxSize=15
        ,FileGrowth=10%
    )
,FILEGROUP ArchiveDataFG
    (
    Name=AdvancedDBF1_ArchiveDataFG
        ,Filename=‘c:\Drive_F\AdvancedDB_F1_AFG.ndf‘
        ,Size=6MB
        ,MaxSize=15
        ,FileGrowth=10%
    )
    ,(
    Name=AdvancedDBF2_ArchiveDataFG
        ,Filename=‘c:\Drive_G\AdvancedDB_F2_AFG.ndf‘
        ,Size=6MB
        ,MaxSize=15
        ,FileGrowth=10%
    )
LOG ON
    (
    Name=AdvancedDBLogF1
        ,Filename=‘c:\Drive_G\AdvancedDB_LogF1.ldf‘
        ,Size=6MB
        ,MaxSize=15
        ,FileGrowth=10%
    )
;

--Create a Table (space-occupying-object) withut specifying a FileGroup
USE AdvancedDB;
GO

Create TABLE dbo.tb1_Table1
    (
    COl1 nvarchar(20)
    )
Create TABLE dbo.tb1_Table2
    (
    COl1 nvarchar(20)
    )
    ON ArchiveDataFG
-- Use the GUI to show the FileGroups and Files of AdvancedDB
-- Use the GUI to show the two tables one being on the default FG
-- and the other table being on a designated FG.