*SQLServer_Tips [#re1e5e4c]
#contents
**Resourceデータベース [#cfeb01a2]
-Resourceデータベースはmasterデータベースと同じ場所に配置しなければならない
-Resourceデータベースのバージョン確認
 SELECT SERVERPROPERTY('ResourceVersion')
-Resourceデータベースの最終更新日時確認
 SELECT SERVERPROPERTY('ResourceLastUpdateDateTime')
**システムオブジェクト [#d253d26a]
***システムオブジェクトのSQL定義を確認する(3種類) [#rb49bd24]
 SELECT OBJECT_DEFINITION(OBJECT_ID('システムオブジェクト'))
 
 EXEC sp_helptext 'システムオブジェクト', NULL
 
 SELECT m.definition
 FROM sys.systems_sql_modules m
 INNER JOIN sys.sysobjects s ON m.object_id = s.id
 WHERE s.name = 'オブジェクト名'    <- sys.all_objectsの場合は、'all_objects'でよい
**データベースの状態確認 [#t914989a]
 SELECT DATABASEPROPERTYEX('データベース名', 'Status')
**ユーザデータベースの移動 [#wc37aa35]
-データベースを計画的に再配置する場合
 ALTER DATABASE データベース名 SET OFFLINE
 ファイルの移動
 ALTER DATABASE データベース名 MODIFY FILE (NAME=データベース論理名, FILENAME='データベースファイルの物理パス(ファイル名含む)')
 ALTER DATABASE データベース名 SET ONLINE
-サーバのメンテナンス時に再配置する場合
 ALTER DATABASE データベース名 MODIFY FILE (NAME=データベース論理名, FILENAME='データベースファイルの物理パス(ファイル名含む)')
 サーバ(インスタンス)の停止
 ファイルの移動
 サーバ(インスタンス)の起動
-障害時の再配置
 サーバ(インスタンス)の停止
 masterデータベースのみ復旧でのサーバ(インスタンス)起動  NET START MSSQLSERVER /f /t3608
 ALTER DATABASE データベース名 MODIFY FILE (NAME=データベース論理名, FILENAME='データベースファイルの物理パス(ファイル名含む)')
 サーバ(インスタンス)の停止
 ファイルの移動
 サーバ(インスタンス)の起動
**フルテキストカタログの移動 [#l6540143]
-ALTER DATABASEでの移動
 ALTER DATABASE データベース名 SET OFFLINE
 ファイルの移動
 ALTER DATABASE データベース名 MODIFY FILE (NAME=カタログ論理名, FILENAME='カタログファイルの物理パス(ファイル名除く)')
 ALTER DATABASE データベース名 SET ONLINE
-アタッチでの移動
 sp_detach_db データベース名
 ファイルの移動
 CREATE DATABASE データベース名 ON (FILENAME='.mdfのパス'), (FILENAME='.ldfのパス'), (FILENAME='カタログのパス') FOR ATTACH
**データベースをシングルユーザモードにする [#ob771ce9]
+AUTO_UPDATE_STATISTICSオプションの統計の更新設定が同期か非同期かを確認する
 SELECT is_auto_update_stats_async_on FROM sys.databases
+結果が1(同期)なら統計の更新設定を非同期に設定する
 ALTER DATABASE データベース名 SET AUTO_UPDATE_STATISTICS_ASYNC OFF
+アクティブな非同期の統計ジョブがあるかどうかを確認する
 SELECT * FROM sys.dm_exec_background_job_queue
+統計ジョブがある場合はKILLする
 KILL STATS JOB
+シングルユーザモードに設定する。(接続していたユーザのトランザクションは指定秒数でロールバックさせる)
 ALTER DATABASE データベース名 SET SINGLE_USER WITH ROLLBACK AFTER 秒数
+シングルモードで実施する作業を行う
+統計の更新設定を同期から非同期へ変更していた場合は元に戻す
 ALTER DATABASE データベース名 SET AUTO_UPDATE_STATISTICS_ASYNC ON
**データベースをマルチユーザモード(通常モード)にする [#r41a6c58]
 ALTER DATABASE SET MULTI_USER
**データベース名を変更する [#gc6ee03e]
 ALTER DATABASE データベース名 MODIFY NAME = 新しいデータベース名
--データベース名を変更する場合は、そのデータベースをシングルユーザモードに設定しておく必要がある
**DAC(専用管理者接続)が使用中かどうかを確認する [#l26ae43b]
 SELECT COUNT(t1.name) FROM sys.tcp_endpoints AS t1
 INNER JOIN sys.dm_exec_sessions AS t2 ON t1.endpoint_id = t2.endpoint_id
 WHERE t1.name = 'Dedicated Admin Connections'
--結果が1ならDAC使用中、0なら使用していない
**データベーススナップショットを作成する [#z7171848]
 CREATE DATABASE スナップショット名
 ON (NAME='データベースファイル論理名', FILENAME='データベースのファイルパス\スナップショットファイル名')
 AS SNAPSHOT OF データベース名
--例
 CREATE DATABASE AdventureWorks_snapshot
 ON (
       NAME='AdventureWorks_Data'
     , FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks_snapshot.ss'
 )
 AS SNAPSHOT OF AdventureWorks
**データベーススナップショットを削除する [#sc5de94a]
 DROP DATABASE スナップショット名
**計算列 [#yf3288b0]
-同じテーブルの他の列の計算結果を列として格納できる
 CREATE TABLE TestTable1(
       [price] [money]
     , [tax] [money]
     , [total] AS ([price] + [price] * [tax] / 100)
 )
 
 INSERT INTO TestTable1 VALUES(100, 5)
 SELECT * FROM TestTable1
 price                 tax                   total
 --------------------- --------------------- ---------------------
 100.00                5.00                  105.00
--計算結果を列に格納して利用できるので利用方法によっては利便性が向上する。
---条件はあるが、インデックスやキーとして利用できる。
---計算列で作成できるのは、UNIQUEとPRIMARY KEYの制約のみ。
---CHECK, FOREIGN KEY, NOT NULLの制約のある列で計算列を利用する場合は、PERSISTED宣言をする必要がある。
 , [total] AS ([price] + [price] * [tax] / 100) NOT NULL              <- ×
 , [total] AS ([price] + [price] * [tax] / 100) PERSISTED NOT NULL    <- ○
--SELECTする度に計算して値を求めるので、計算が複雑になれば速度は落ちる。
**データベースの互換性レベル [#bee56740]
***互換性レベルを確認する [#q2e1c7d7]
 EXEC sp_dbcmptlevel 'データベース名'
***互換性レベルを変更する [#j471857c]
 EXEC sp_dbcmptlevel 'データベース名', 互換性レベル

**テーブルの圧縮 [#ea425754]
***現在圧縮を実行しているかどうかの確認 [#g430355b]
 SELECT data_compression, dataq_compression_desc FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('テーブル名')
-data_compressionの意味
|data_compression|data_compression_desc|意味|h
|0|NONE|非圧縮|
|1|ROW|行圧縮済み|
|2|PAGE|ページ圧縮済み|
***圧縮後のサイズ見積もり [#d60b0061]
 EXEC sp_estimate_data_compression_savings 'スキーマ名', 'テーブル名', NULL, NULL, '圧縮種別'
-例
 USE AdventureWorks
 EXEC sp_estimate_data_compression_savings 'Person', 'Address', NULL, NULL, 'ROW'    <- 行圧縮
|列名|意味|h
|size_with_current_compression_settings[KB]|現在のサイズ|
|size_with_requested_compression_setting[KB]|圧縮後のサイズ|
***圧縮 [#k80e6633]
 ALTER TABLE スキーマ名.テーブル名 REBUILD WITH (DATA_COMPRESSION = 圧縮種別)
-例
 USE AdventureWorks
 ALTER TABLE Persion.Address REBUILD WITH (DATA_COMPRESSION = ROW)
***圧縮解除 [#c8b69b6f]
 ALTER TABLE スキーマ名.テーブル名 REBUILD WITH (DATA_COMPRESSION = NONE)
**DBCC -> DML [#gafb47eb]
***DBCC SQLPERF('logspace') [#p398d4c3]
 SELECT RTRIM(pc1.instance_name) AS [Database Name]
     , pc1.cntr_value / 1024.0 AS [Log Size (MB)]
     , CAST(pc2.cntr_value * 100.0 / pc1.cntr_value AS decimal(5,2)) AS [Log Space Used (%)]
 FROM sys.dm_os_performance_counters as pc1
 INNER JOIN sys.dm_os_performance_counters as pc2 ON pc1.instance_name = pc2.instance_name
 WHERE pc1.object_name LIKE '%Databases%'
     AND pc2.object_name LIKE '%Databases%'
     AND pc1.counter_name = 'Log File(s) Size (KB)'
     AND pc2.counter_name = 'Log File(s) Used Size (KB)'
     AND pc1.instance_name NOT IN ('_Total', 'mssqlsystemrecource')
     AND pc1.cntr_value > 0
**日時 [#k91c5f7d]
***現在日時の取得 [#w80cddbb]
 SELECT GETDATE()
 
 SELECT CURRENT_TIMESTAMP

トップ   編集 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS