Microsoft SQL Server 2012 管理 (1): 安装配置SQL Server 重点

SQL Server 可以在实例,数据库,列,查询分别指定排序规则

/* 
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.

 

Microsoft SQL Server 2012 管理 (1): 安装配置SQL Server 重点

上一篇:数据库原理吉林大学随笔第9课时


下一篇:数据库引擎开发以及常见数据库内部原理学习(1)