Microsoft SQL Server 2012 管理 (1): 安裝配置SQL Server 重點

標籤：des   style   blog   color   os   io   ar   for   檔案   

SQL Server 可以在執行個體，資料庫，列，查詢分別指定定序

/* Module 1 - working with Clollations*/-- 1.1 Obtain the Instance Collation from the GUI--Create a Database without specifying a specific CollationCreate Database UnspecifiedCollationDB;GO-- Use the statement bellow(code) to verfiy that the new database inherited the CollationUSE UnspecifiedCollationDB;GOSelect DB_NAME() as Current_Database, DATABASEPROPERTYEX(‘UnspecifiedCollationDB‘,‘Collation‘) DatabseCollation-- 1.2 Create a Database with a Collation that overrides the Instance Default CollationCreate 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 DatabaseUSE MultiLingualSpeakDBCreate 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 databaseUSE tempdbGO--Retrieve and discuss the collation of the system and tempdbSelect SERVERPROPERTY(‘Collation‘) as SystemCollation,    DATABASEPROPERTYEX(‘tempdb‘,‘Collation‘) as DatabaseCollation;GO-- Create and populate a table with different column collationsCreate 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 caseSELECT * FROM     dbo.TestCharacterWHERE CIData=‘test data‘;-- Now query the case-sensitive columnSELECT * FROM     dbo.TestCharacterWHERE CSData=‘test data‘; -- No rows retrunedGO

--Execute a query to perform a case-insensitive--search on the case-sensitive dataSELECT * FROM     dbo.TestCharacterWHERE 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 resolvedSELECT * FROM     dbo.TestCharacterWHERE CIData=CSData;
-- Execute the qery while specifying a collationSELECT * FROM     dbo.TestCharacterWHERE 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 masterGoEXEC XP_CMDSHELL ‘MD c:\Drive_D‘, no_outputEXEC XP_CMDSHELL ‘MD c:\Drive_E‘, no_outputEXEC XP_CMDSHELL ‘MD c:\Drive_F‘, no_outputEXEC XP_CMDSHELL ‘MD c:\Drive_G‘, no_outputEXEC XP_CMDSHELL ‘MD c:\Drive_H‘, no_outputEXEC XP_CMDSHELL ‘MD c:\Drive_I‘, no_outputEXEC XP_CMDSHELL ‘MD c:\Backups‘, no_outputGO-- 2.1 Create the AdvancedDBCREATE 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 FileGroupUSE AdvancedDB;GOCreate 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.

 

Microsoft SQL Server 2012 管理 (1): 安裝配置SQL Server 重點