SQL Serverメモリプロセスを深く分析する

Transact-SQLの格納プロセスは、Java言語の方法と非常に似ています。繰り返し起動できます。記憶プロセスが一回実行されると、ステートメントキャッシュにおいて、このように次回実行されると、キャッシュ中のステートメントがそのまま使用されます。これにより、記憶プロセスの性能を向上させることができる。
Ø保存プロセスの概念
    記憶プロセスProcesdureは、特定の機能を完了するためのSQL文のセットであり、コンパイルされた後にデータベースに格納され、ユーザは格納プロセスの名前を指定し、パラメータを与えて実行する。
    記憶プロセスには、パラメータ、出力パラメータ、単一または複数の結果セット、および戻り値を受け入れる論理制御文とデータ操作文が含まれてもよい。
    ストレージプロセスは、作成時、すなわちデータベースサーバ上でコンパイルされ、データベースに格納されているので、格納プロセスの実行は単一のSQL文ブロックよりも速いです。また、呼び出し時には、プロセス名と必要なパラメータ情報を提供するだけで、ある程度のネットワーク流量、簡単なネットワーク負荷を低減することができる。
    1、保存プロセスのメリット
        A、記憶プロセスは標準コンポーネント式のプログラミングを可能にする。
        ストレージプロセスが作成された後は、ストレージプロセスのSQL文を再作成する必要なく、プログラム内で実行を何度も呼び出すことができます。また、データベースの専門家は随時に保存過程を修正することができますが、アプリケーションのソースコードには影響がなく、プログラムの移植性を大幅に向上させました。
        B、格納プロセスはより速い実行速度を実現することができる。
        いくつかの操作が大量のT-SQL文コードを含んでいる場合、それぞれが複数回実行されるので、記憶プロセスはバッチ処理の実行速度よりずっと速いです。記憶プロセスはプリコンパイルされているので、最初の記憶プロセスを実行するときに、クエリ最適化器は分析し、最適化し、最終的にシステムテーブルに保存される計画を与える。バッチ処理のT-SQL文は運行毎にプリコンパイルと最適化が必要ですので、速度は少し遅くなります。
        C、保存プロセスはネットワーク流量を軽減する。
        同じデータベースオブジェクトに対する動作の場合、この動作に関連するT−SQL文が格納プロセスとして構成されている場合、クライアントで格納プロセスを呼び出した場合、ネットワークで伝達されるのは呼び出し文だけであり、そうでなければ複数のSQL文である。これによりネットワーク流量が軽減され、ネットワーク負荷が低減される。
        D、記憶プロセスは安全機構として十分に利用できる。
        システム管理者は、実行されるある格納プロセスに対して権限制限を行うことができ、いくつかのデータへのアクセス制限を実現することができ、非許可のユーザによるデータへのアクセスを回避し、データの安全を保証する。
Øシステム格納プロセス
    システム格納プロセスは、システムテーブルから情報を検索したり、データベーステーブルを更新することに関連する管理タスクや他のシステム管理タスクを完了することができるようにするためのシステム作成の格納プロセスである。システム格納プロセスは主にマスターデータベースに格納され、「sp」をアンダースコアで開始する格納プロセスです。これらのシステムはマスターデータベースに格納されていますが、他のデータベースでもシステム格納プロセスを起動できます。いくつかのシステムストレージプロセスは、新しいデータベースを作成するときに自動的に現在のデータベースに作成されます。
    一般的なシステム格納プロセスには、次のようなものがあります。

exec sp_databases; --     

exec sp_tables;        --   

exec sp_columns student;--   

exec sp_helpIndex student;--    

exec sp_helpConstraint student;--  

exec sp_stored_procedures;

exec sp_helptext 'sp_stored_procedures';--        、    

exec sp_rename student, stuInfo;--   、  、    

exec sp_renamedb myTempDB, myDB;--       

exec sp_defaultdb 'master', 'myDB';--           

exec sp_helpdb;--     ，       

exec sp_helpdb master;

    システム格納プロセスの例：
--表の名前変更

exec sp_rename 'stu', 'stud';
select * from stud;

--列の名前変更

exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';

--索引の名前変更

exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';

--すべての保存手順を問い合わせる

select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

Øユーザーのカスタム保存プロセス
   1、文法を作成する
create proc procedure pro uname
    [{@パラメータデータタイプ>[=デフォルト値][output]
     {@パラメータデータタイプ>[=デフォルト値][output]
     ....
    ]
as
    SQL_statements
   2、パラメータなしの保存プロセスを作成する
--保存プロセスの作成

if (exists (select * from sys.objects where name = 'proc_get_student'))
  drop proc proc_get_student
go
create proc proc_get_student
as
  select * from student;

--メモリプロセスの呼び出し、実行

exec proc_get_student;

   3、保存手順を変更する
--保存手順の変更

alter proc proc_get_student
as
select * from student;

   4、参考文献保存プロセス
--参考文献保存プロセス

if (object_id('proc_find_stu', 'P') is not null)
  drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
  select * from student where id between @startId and @endId
go

exec proc_find_stu 2, 4;

   5、ワイルドカードパラメータの記憶過程
--ワイルドカードパラメータ記憶プロセス

if (object_id('proc_findStudentByName', 'P') is not null)
  drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
  select * from student where name like @name and name like @nextName;
go

exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';

   6、出力パラメータの記憶過程

if (object_id('proc_getStudentRecord', 'P') is not null)
  drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
  @id int, --      
  @name varchar(20) out, --    
  @age varchar(20) output--      
)
as
  select @name = name, @age = age from student where id = @id and sex = @age;
go

-- 
declare @id int,
    @name varchar(20),
    @temp varchar(20);
set @id = 7; 
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;
 

   7、キャッシュレス記憶プロセス
--WITH RECOMPILEはキャッシュしません。

if (object_id('proc_temp', 'P') is not null)
  drop proc proc_temp
go
create proc proc_temp
with recompile
as
  select * from student;
go

exec proc_temp;

   8、暗号化保存プロセス
--暗号化WITH ENCRYPTION 

if (object_id('proc_temp_encryption', 'P') is not null)
  drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
  select * from student;
go

exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';

   9、ラベルパラメータの記憶プロセスを持つ

if (object_id('proc_cursor', 'P') is not null)
  drop proc proc_cursor
go
create proc proc_cursor
  @cur cursor varying output
as
  set @cur = cursor forward_only static for
  select id, name, age from student;
  open @cur;
go
--  
declare @exec_cur cursor;
declare @id int,
    @name varchar(20),
    @age int;
exec proc_cursor @cur = @exec_cur output;--      
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
  fetch next from @exec_cur into @id, @name, @age;
  print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--    
 

   10、別紙保存プロセス
---格納プロセス、row_number完成改ページ

if (object_id('pro_page', 'P') is not null)
  drop proc proc_cursor
go
create proc pro_page
  @startIndex int,
  @endIndex int
as
  select count(*) from product
;  
  select * from (
    select row_number() over(order by pid) as rowId, * from product 
  ) temp
  where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--      
if (object_id('pro_page', 'P') is not null)
  drop proc pro_stu
go
create procedure pro_stu(
  @pageIndex int,
  @pageSize int
)
as
  declare @startRow int, @endRow int
  set @startRow = (@pageIndex - 1) * @pageSize +1
  set @endRow = @startRow + @pageSize -1
  select * from (
    select *, row_number() over (order by id asc) as number from student 
  ) t
  where t.number between @startRow and @endRow;

exec pro_stu 2, 2;
 

ØRaiserror
Raiserrorは、ユーザが定義したエラー情報を返し、深刻なレベルを指定し、システム変数レコードに発生したエラーを設定します。
   文法は以下の通りです
Raiserror({msg_}id|msg_str 124@local_variable.
  {, severity，state｝
  [,argment[，…n]]
  [with option[，…n]
)
   # msg_id:syssmsシステムテーブルで指定されたユーザ定義エラー情報
   # msg_str:ユーザー定義の情報で、最大の情報長は2047文字です。
   # severity：ユーザは、メッセージに関連する重要なレベルを定義する。msg_を使用するとidに端を発してsp_を使うaddmessageが作成したユーザ定義メッセージの場合、raserrorがsp_を上書きする重篤性を指定します。addmessageで定義された深刻さ。
    任意のユーザは、0−18の直接的な重度レベルを指定することができる。syssadmin固定サーバの役割が一般的またはalter trace権限を持つユーザーのみが19-25の直接的な深刻レベルを指定することができます。19-25間のセキュリティレベルはwith logオプションを使用する必要があります。
   # state：1から127までの直接の任意の整数です。Stateのデフォルト値は1です。
ライザー（'is error'，16，1）
select*from sys.message;
--syssmsで定義されたメッセージを使用する
ライザー（33003、16、1）
ライザー（33006、16、1）
以上述べましたが、本文の内容は全部です。お好きになってください。