server怎么排序?
開始之前,先確認排序規則(或大小寫是否敏感)
1
2
3
4
5
6
7
8
9
10
--查看服務器排序規則(安裝時指定的排序規則)
SELECT SERVERPROPERTY('COLLATION') AS ServerCollation
,DATABASEPROPERTYEX('tempdb','COLLATION') AS TempdbCollation
,DATABASEPROPERTYEX(DB_NAME(),'COLLATION') AS CurrentDBCollation
--查看數據庫排序規則
SELECT name, collation_name FROM sys.databases
--當前數據庫是否大小寫敏感
SELECT CASE WHEN N'A'=N'a' THEN N'不敏感' ELSE N'敏感' END
此環境實例中:
服務器排序規則為 Chinese_PRC_BIN
當前數據庫排序規則為 Chinese_PRC_CI_AS
若當前數據庫創建的所有對象和執行腳本時,如果用到了變量、臨時對象等,大小寫不一致則出現問題。二進制(_BIN)排序規則是區分大小寫的。
安裝實例時指定的排序規則,就是 master 數據庫的排序規則,同時 model 和 msdb 的排序規則也保持一致,而 tempdb 和用戶數據庫的排序規則都是參照 model 數據庫一樣的。
系統數據庫是不能直接更改排序規則的,因此只能重建系統數據庫,且讓系統數據庫的排序規則都一致。
重建實例排序規則和系統數據庫排序規則:
1、備份系統數據庫!必要的,失敗了或者以后要使用當前環境時,還可以回退!
2、記住所有數據庫及文件路徑,保持到 excel 中。如果記得住所有數據庫位置的話就不用了。
1
select DB_NAME(database_id) as name,physical_name from sys.master_files
3、導出服務器配置(sp_configure)到 excel。因為重建系統數據庫后配置會被初始化。
4、導出賬號信息到 txt。數據庫重建,賬戶信息都會丟失,除了備份也要單獨備份賬號。(SQL Server 中登錄賬號與數據庫用戶遷移)
5、導出鏈接服務器生產腳本到 txt ,如果有的話。
6、導出代理作業到 txt 。
7、(如果還有其他配置,如審核、郵件配置、策略等,都保存出來,后續再重建)
8、分離所有用戶數據庫。
1
2
3
select 'ALTER DATABASE ['+name+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'+char(10)+'go'+char(10)
+'EXEC master.dbo.sp_detach_db @dbname = N'''+name+''''+char(10)+'go'+char(10)
from sys.databases where name not in('master','model','msdb','tempdb')
9、停止所有數據庫相關服務。
10、重建數據庫并制定新的排序規則。打開命令行,進入安裝目錄。
1
2
cd D:\Software\en_sql_server_2008_r2_enterprise_x86_x64_ia64_dvd_520517
setup /QUIET /ACTION=REBUILDDATABASE /instancename=mssqlserver /SQLSYSADMINACCOUNTS=服務器賬號 /sapwd=密碼 /sqlcollation=Chinese_PRC_CI_AS
11、啟動 SQL Server 引擎服務,其他暫不啟動。
12、創建登錄賬戶(之前導出的腳本)
13、附加所有用戶數據庫。
14、創建代理作業、鏈接服務器、更配置等!
完成!~
如果重建系統數據庫后,還原 master 數據庫,那么服務器排序規則和 master 數據庫排序規則都還原和以前一樣,所以不能還原,除非恢復到以前的環境。
如果還原 msdb 或 model ,該數據庫排序規則也會還原,所以系統數據庫備份都不要還原,重建系統數據庫之后只能重新配置。
更改用戶數據庫排序規則
1
ALTER DATABASE test COLLATE Chinese_PRC_CI_AS
更改數據庫排序規則時,需要更改下列內容:
>> 將系統表中的任何 char、varchar、text、nchar、nvarchar 或 ntext 列更改為使用新的排序規則。
>> 將存儲過程和用戶定義函數的所有現有 char、varchar、text、nchar、nvarchar 或 ntext 參數和標量返回值更改為使用新的排序規則。
>> 將 char、varchar、text、nchar、nvarchar 或 ntext 系統數據類型和基于這些系統數據類型的所有用戶定義的數據類型更改為使用新的默認排序規則。
對于數據庫內字段定義的排序規則,參考下面的 設置或更改列排序規則。
設置或更改列排序規則:
1
2
3
4
5
6
7
8
--示例
CREATE TABLE dbo.MyTable(
PrimaryKey INT PRIMARY KEY,
CharCol VARCHAR(10) COLLATE French_CI_AS NOT NULL
);
GO
ALTER TABLE dbo.MyTable ALTER COLUMN CharCol VARCHAR(10) COLLATE Latin1_General_CI_AS NOT NULL;
GO
如果下列其中之一當前正在引用一個列,則無法更改該列的排序規則:
>> 計算列
>> 索引
>> 自動生成或由 CREATE STATISTICS 語句生成的分發統計信息
>> CHECK 約束
>> FOREIGN KEY 約束