Microsoft SQL Server 2012 Management (1): Install configuration SQL Server focus

SQL Server can specify collations in instances, databases, columns, and queries, respectively

/*Module 1-working with Clollations*/--1.1 Obtain the Instance Collation from the GUI--Create a Database without specifying a specific CollationCreate Databaseunspecifiedcollationdb;GO--Use the statement bellow (code) to Verfiy, the new database inherited the Collation Useunspecifiedcollationdb;GOSelect db_name() asCurrent_database, Databasepropertyex ('unspecifiedcollationdb','Collation') Databsecollation--1.2 Create A Database with a Collation that overrides the Instance Default CollationCreate Databasemultilingualspeakdb 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 UseMULTILINGUALSPEAKDBCreate Tablemixedspeaktable (Productdiint IDENTITY, Englighprodnamenvarchar( -) COLLATE Latin1_general_ci_ai not NULL, Arabicprodnamenvarchar( -) not NULL, Greeekprodnamenvarchar( -) COLLATE Greek_cs_as_ks not NULL, Japaneseprodnamenvarchar( -) 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 UsetempdbGO--Retrieve and discuss the collation of the system and tempdbSelectSERVERPROPERTY ('Collation') assystemcollation, Databasepropertyex ('tempdb','Collation') asdatabasecollation;GO--Create and populate a table with different column collationsCreate Tabledbo. Testcharacter (IDint IDENTITY, Cidatavarchar(Ten) COLLATE latin1_general_ci_as, Csdatavarchar(Ten) COLLATE latin1_general_cs_as)INSERT  intodbo. Testcharacter (Cidata,csdata)VALUES('Test Data','Test Data');GO--Execute queries that try to match the same--values from Eache column with all lower caseSELECT *  fromdbo. TestcharacterWHERECidata='test Data';--Now query the case-sensitive columnSELECT *  fromdbo. TestcharacterWHERECsdata='test Data';--No rows retrunedGO

--Execute a query to perform a case-insensitive--search on the case-sensitive dataSELECT *  fromdbo. TestcharacterWHERECsdata='test Data'COLLATE latin1_general_ci_as;--Try to execute a query that compares the columns--That has different collations. this would fail--As the collation conflict cannot be resolvedSELECT *  fromdbo. TestcharacterWHERECidata=Csdata;
--Execute the Qery while specifying a collationSELECT *  fromdbo. TestcharacterWHERECidata=Csdata COLLATE latin1_general_ci_as;

Reasonably allocating filegroups to improve database performance

/*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.EXECMaster.dbo.sp_configure'Show Advanced Options',1;RECONFIGURE;EXECMaster.dbo.sp_configure'xp_cmdshell',1; RECONFIGURE;-------------------Make ' drive Latters ' to simulate existence's May drive letters (LUNS)--For the Advanced database. UseMasterGoEXECxp_cmdshell'MD C:\Drive_D', No_outputEXECxp_cmdshell'MD C:\Drive_E', No_outputEXECxp_cmdshell'MD C:\Drive_F', No_outputEXECxp_cmdshell'MD C:\Drive_G', No_outputEXECxp_cmdshell'MD C:\Drive_H', No_outputEXECxp_cmdshell'MD C:\Drive_I', No_outputEXECxp_cmdshell'MD c:\Backups', No_outputGO--2.1 Create The ADVANCEDDBCREATE DATABASEADVANCEDDB/*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= -, FileGrowth=Ten%), FILEGROUP CURRENTDATAFG (Name=ADVANCEDDBF1_CURRENTDATAFG, Filename='C:\DRIVE_E\ADVANCEDDB_F1_CDFG.NDF', Size=6MB, MaxSize= the, FileGrowth=Ten%), (Name=ADVANCEDDBF2_CURRENTDATAFG, Filename='C:\DRIVE_E\ADVANCEDDB_F2_CDFG.NDF', Size=6MB, MaxSize= the, FileGrowth=Ten%), FILEGROUP ARCHIVEDATAFG (Name=ADVANCEDDBF1_ARCHIVEDATAFG, Filename='C:\DRIVE_F\ADVANCEDDB_F1_AFG.NDF', Size=6MB, MaxSize= the, FileGrowth=Ten%), (Name=ADVANCEDDBF2_ARCHIVEDATAFG, Filename='C:\DRIVE_G\ADVANCEDDB_F2_AFG.NDF', Size=6MB, MaxSize= the, FileGrowth=Ten%    )LOG  on(Name=AdvancedDBLogF1, Filename='C:\Drive_G\AdvancedDB_LogF1.ldf', Size=6MB, MaxSize= the, FileGrowth=Ten%    );--Create a Table (space-occupying-object) Withut specifying a FileGroup UseAdvanceddb;GOCreate TABLEDbo.tb1_table1 (COl1nvarchar( -)    )Create TABLEDbo.tb1_table2 (COl1nvarchar( -)    )     onARCHIVEDATAFG--Use the GUI to show the filegroups and Files of Advanceddb--Use the GUI to show the one of the tables one being on the default FG--and the other table being on a designated FG.

Microsoft SQL Server 2012 Management (1): Install configuration SQL Server focus