ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

Archive for the ‘ストアドプロシージャ’ Category

学校の先生のための SQL Server 2016 による成績処理 (1) – SQL Server 2016 Express のダウンロード

leave a comment »

今日における高等学校では生徒の成績処理や学籍管理において、多少ともコンピュータの処理がかかわっているだろう。授業担当者は自分が担当する講座の生徒の成績を、学級担任はクラスの生徒の出席状況やホームルーム活動の記録などをエクセルのような表計算ソフトで処理しているだろう。そして学校全体の成績管理をする教務部は、これらのデータを集めて成績会議の資料を作り、生徒の指導要録を管理し、また進路指導部は調査書の処理をしているはずだ。

これらのデータ処理をエクセルのような表計算ソフトを使って学校全体で行っているケースは多いと思われる。しかし表計算ソフトウエアはもともと個人ベースのデータ処理ツールとして開発されたもので、マルチユーザーでの利用には限界がある。学校の規模にもよるが、高等学校には数十名の教職員が勤務しており、定期考査が終わり評価をつけ、成績会議までの日程はあまり長くない。そして何よりも成績処理には正確さが求められる。成績処理には会計処理における「出納違算金」のようなものはなく、1点でも誤りがあってはいけないのだ。

また学校制度が多様化し、学習指導要領に決められていない「学校設定科目」などを多く開講する「総合学科」や夜間定時制を昼間の時間帯にも拡張した「多部制」、学年の区切りがない「単位制」など新しいタイプの学校ができ、また従来からも自宅学習が基本となる「通信制」がある。これらの学校では、そもそも紙ベースでの成績処理、学籍管理には無理があるため、成績処理システムを外注していることが多い。

筆者は10年間、生徒定員1,120名の大規模な多部制、単位制高校に勤務し、成績処理と学籍管理を行ってきた。この学校は午前、午後、夜間の3つの部を持つ多部制、単位制高校で、毎年280名の入学生を受け入れ、100講座の授業を編成し、約4,500の受講登録をして単位の管理を必要とした。出席データは週当たり8,000件、1年間で30万件となった。これらの処理を、私が赴任するまでは業者に発注したデータベースシステムを使っていたが、さまざまな理由で学校事情をあわないものであったため、筆者はMicrosoft SQL Serverを使って学校独自のシステムを構築して運用した。この経験から学校では、その学校に応じたシステムを現場の教員が作る、いわゆる「内製」が望ましいと確信している。SQL Serverは簡単だ。ぜひ多くの教員がSQL Serverを知り、自ら望む最高のデータベースを構築し運用できることを願っている。

現在マイクロソフトのサイトから、SQL Server 2016がダウンロードできる。
Microsoft SQL Server のページ
https://www.microsoft.com/en-us/sql-server/sql-server-2016

SQLServer2016_for_teachers_001_mid_640

SQL Server 2016にはいくつかのバリエーションがあるが、その中に無料で使えるExpress版がある。実運用では正式版を購入することになるが、試験的な利用ではこのExpress版を使うことができ、現時点でSQL Server 2016 SP1 Expressバージョンが提供されている。

上のページでスクロールし、Editions をクリックするとダウンロードページへのリンクがわかる。
Microsoft SQL Server ダウンロードのページ
https://www.microsoft.com/en-us/sql-server/sql-server-2016-editions

SQLServer2016_for_teachers_002_mid_640

ダウンロードのリンクをクリックすると Microsoft SQL Server 2016 Service Pack 1 Express のダウンロードページが開く。いくつかの言語の版を選択できるようになるので言語のドロップダウンリストボックスをクリックし、英語、フランス語、ドイツ語、イタリア語につづく Japanese を選択してダウンロードする。
Microsoft SQL Server 2016 Service Pack 1 Express ダウンロードのページ
https://www.microsoft.com/en-us/download/details.aspx?id=54284

SQLServer2016_for_teachers_003_mid_640

ダウンロードではディスクのどこかにフォルダを作り、そこに保存する。保存できれば次のようなファイルが生成する。ファイル名は SQLServer2016-SSEI-Expr.exe だ。

SQLServer2016_for_teachers_004_mid

重ねて述べるが、SQL Server は難しくない。また Microsoft SQL Server のように多くのユーザーの支持を得て、業界標準となり、長くバージョンアップを繰り返して使われてきた製品は、そのたびに使いやすく機能が豊富になる。身に着けたことは技術資産となり積み上げられる。これを機会にぜひ使ってほしい。

Written by Yoshio Matsumoto

2017年4月3日 at 10:21 AM

Microsoft Visual Studio 2015 と SQL Server データベースファイルで開発する「目からうろこ」の C# プログラミングによる成績処理システム(2) – Visual Studio 2015 による新しいプロジェクトの開始と SQL Server データベースファイルとデータソースの作成

leave a comment »

1.Visual Studio 2015 による新しいプロジェクトの開始

VisualStudio_成績処理_002_mid_640

Visual Studio 2015 を起動する。

VisualStudio_成績処理_003_N_mid_640

起動したら画面の左「開始」から「新しいプロジェクト」をクリックして新しいプロジェクトを開始する。

VisualStudio_成績処理_004_mid_640

プロジェクトで使用する言語やテンプレートを選ぶ。ここでは言語を「C#」、テンプレートを「Windows フォームアプリケーション」とする。必要があれば「名前」でプロジェクトファイルの名前をわかりやすいものに変え、保存する「場所」を変更するなどする。

VisualStudio_成績処理_006_mid_640

2.データベース SQL Server データベースファイルとデータソースの作成

テンプレートが構成され、Windows フォームアプリケーションの雛形が表示される。Windows フォームを作る前に、まずデータベースを作成しよう。そのために Visual Studio の左端に並んでいるタブから「データソース」をクリックする。

VisualStudio_成績処理_007_mid_640

データソースとは、プログラムがデータベースに接続する経路のようなものだ。プロジェクトにはまだデータベースがないのでデータソースもない。「プロジェクトには現在関連データソースがありません。」といったメッセージが示されている。そこで「新しいデータソースの追加」をクリックする。

VisualStudio_成績処理_008_mid_640

Microsoft Visual Studio 2015 は SQL Server 以外の様々なデータベースに接続できるウィザードを持っているので、いくつか選択肢が示される。このあたりの選択肢は用語と概念が理解できていないと何を選んでよいか迷うが、ここで使いたいデータベースは SQL Server データベースファイルとして次のように決定していく。まず「データソースの種類の選択」では「データベース」を選択する。

VisualStudio_成績処理_010_mid_640

「データベースモデルの選択」では選択肢はひとつしかなく「データセット」を選択する。

VisualStudio_成績処理_012_mid_640

次の「データ接続の選択」では、もし以前に同じ Visual Studio でデータベースアプリケーションを開発したことがあるなら、「アプリケーションがデータベースへの接続に使用するデータ接続」のところに以前設定したデータ接続が選択されているかもしれない。ここでは初めてデータ接続を作成するので、選択する項目がない。いずれにせよ新しい SQL Server データベースファイルを作るので、「新しい接続」のボタンをクリックする。

VisualStudio_成績処理_014_mid_640

次の「データソースの選択」では、接続したいデータベースを選ぶことができる。ネットワーク上に SQL Server があれば「Microsoft SQL Server」を選択することになる。また Microsoft Access データベースファイルを選択することもできることがわかる。ここでは新しくアプリケーション内に「Microsoft SQL Server データベースファイル」を作成して利用したいので、「Microsoft SQL Server データベースファイル」を選択する。そうすると自動的に「データプロバイダ」も設定される。

VisualStudio_成績処理_017_mid_640

次の「接続の追加」画面では、データベースファイル名を記述することになるが、まだファイルを生成していないので「参照」ボタンをクリックする。「参照」ボタンをクリックすることで、新規の Microsoft SQL Server データベースファイルを生成することができる。

VisualStudio_成績処理_019_mid_640

このように「SQL Server データベースファイルの選択」ウィンドウが表示されるので、データベースファイルを保存したいフォルダを選び、ファイル名を決める。ファイルがなくても「開く」のボタンをクリックすればよい。

VisualStudio_成績処理_021_mid_640

「接続の追加」画面に戻り、データベースファイル名が書かれている。だがこの時点ではまだデータベースファイルは生成していない。

VisualStudio_成績処理_023_mid_640

「OK」をクリックして先にすすむと、このようにデータベースファイルを作成しますか、というメッセージが表示される。「はい」をクリックするとデータベースファイルが生成する。

VisualStudio_成績処理_025_mid_640

少し前の「データ接続の選択」画面が表示され、データ接続が設定されたことがわかる。このとき、下段の「+」をクリックして「アプリケーションに保存する接続文字列」を展開すると、アプリケーション上でどのような構成がされているかがわかる。この設定情報は知らなくてもアプリケーションを開発することができるが、操作を誤ってトラブルになったときや、この先アプリケーションの工夫をするときに役に立つので一度見ておくといい。

VisualStudio_成績処理_027_mid_640

「アプリケーションに保存する接続文字列」を展開すると、Data Source としてデータベースファイル名や接続時のセキュリティ、タイムアウトなどが記述されている。

VisualStudio_成績処理_028_mid_640

次の画面では「選択された接続は、現在のプロジェクトにはないローカルデータファイルを使用します。プロジェクトにファイルをコピーして接続を変更しますか?」と問われる。また「データファイルをプロジェクトにコピーする場合、アプリケーション実行時に、プロジェクトの出力ディレクトリにコピーされます。この動作の制御に関する情報については、F1を押してください。」といった記述もある。

なにやらものものしい説明になっており「はい」とするのか「いいえ」とするのか戸惑う。この挙動については、初めての人には理解できないだろう。これは説明が悪いのではなく、プロジェクトの開発と完成品の展開についての概念があってはじめて理解できる記述だ。

「現在のプロジェクトにはないローカルデータファイルを使用します」というのは、Visual Studio のプロジェクトが保存されるフォルダーではないところに SQL Server データベースファイルを作ったことを指摘されている。そして「プロジェクトにファイルをコピーして接続を変更しますか?」というのは、先に指定した SQL Server データベースファイルのフォルダやファイル名のものを使わず、それを Visual Studio のプロジェクトフォルダにコピーして開発を続けますか、と聞いている。もしここで「はい」をクリックすると、先に指定した SQL Server データベースファイルのフォルダやファイル名のものは使われず、Visual Studio のプロジェクトフォルダにコピーされたものを使って開発がすすめられることになる。

もしプログラムを開発したあと、どこか別のパソコンに展開し、そこで最初から運用を始めるならそれでいい。しかし今からやりたい開発は、開発しながらデータも用意していき、運用もそのまま開発に引き続いて行う予定である。そのため、ここでは先に指定した SQL Server データベースファイルのフォルダやファイル名のものをそのまま使いたいので「いいえ」をクリックする。

VisualStudio_成績処理_030_mid_640

ここでデータソース作成ウィザードは最終段階で、接続文字列をアプリケーション構成ファイルに保存する。

VisualStudio_成績処理_032_mid_640

データソース構成ウィザードは、データベースオブジェクトの選択に戻る。ここでデータソースを構成したのに「データセット内に指定するデータベースオブジェクト」が何もないが、データベースオブジェクトとはテーブルやビューなどの要素のことであり、ここではデータベースファイルを作っただけで中身を構成していないので何も表示されていない。「データセット名」は自分が指定した SQL Server データベースファイルの名前を含んで自動的に決められる。このまま「完了」をクリックしてウィザードを終了する。

VisualStudio_成績処理_034_mid_640

Visual Studio の画面に戻った。すると左の「データソース」タブに「mySQLSVDBFDataSet」という名前のデータセットができていることがわかる。これで SQL Server データベースファイルとデータソースの作成が完了した。

Microsoft Visual Studio 2015 と SQL Server データベースファイルで開発する「目からうろこ」の C# プログラミングによる成績処理システム(1) – 成績処理システムを開発する背景とシステムの概要

with 2 comments

1.成績処理システムを開発する背景

私は30年の教員経験と、そのうちで学校のコンピュータシステムを15年間担当してきた経験から、学校の成績処理システムは教員自身が作り運用することが最善だという持論を持っている。なぜならコンピュータの管理やシステム開発の技術は教員が学んで身につけることができるが、学校の成績処理の方法は学校業務を経験しなければわからないからである。学校が成績処理システムを業者に発注して開発させることがあるが、コンピュータシステムを知らない教員と学校業務を知らないシステムエンジニアが十分な仕様書を作らずに開発に入ると間違いなく使えないシステムができあがる。さらに 15年前には、システム開発を教員がすることに無理があったが、今は優秀な開発ツールがあり、わずかな技術習得と短い時間でデータベースアプリケーションを作ることができる時代になっている。

コンピュータシステムに限界はない。人間が想像し得るシステムはどんなものでも作ることができる。しかしそれは、いつでもどこでも誰にでもあっという間にできるものではない。複雑なシステムを作るにはそれなりの時間と手間、熟考された設計が必要だ。作り始めてから不具合がわかることや、経験を積んでからより良い手法に気づくことがある。学校の教務規定が変わることもあるし、成績処理のやり方が変わることもある。教員自らがシステムを作る「内製」は、どんな変化にも対応できる利点がある。これらのことから、成績処理システムを構築する場合には、最初から多くの機能を作りこむのではなく、時間をかけずに必要最小限の機能から作ることがよい。

私は昨年の春、12年勤務した大規模で複雑な多部制単位制高校から、規模の小さな夜間定時制の工業高校に異動になり、教務部長を務めている。以前の勤務校では Microsoft SQL Server と Microsoft Office InfoPath や Microsoft Office Access などを組み合わせたいわゆる「 OBA 開発」によるクライアントサーバー型のシステムを作って運用したが、多くの全日制普通高校とは多くの面で違いがあった。現任校では成績処理のコンピュータ化がなされていないため、これを機会に簡単な成績処理システムを作ることにし、その作成過程を紹介することにする。現任校は学年制であり今から作成過程を紹介するシステムは多くの学校の参考になるだろう。

2.Microsoft Visual Studio 2015

Visual Studio は 1997 年に発表され改良を重ねてきた開発効率のたいへん良い開発ツールで、基本的な使い方を学ぶだけで簡単なアプリケーションを作ることができる。各出版社から「はじめて作る Visual Studio ・・・」といったタイトルの書籍がたくさん出版され、初心者でもそれらを読みながら簡単なゲームのようなプログラムを作ることができる。だが 10 年ほど前、Visual Studio 2005 の頃にはデータベースを利用するプログラミングをしようと思うと途端に敷居が高くなり、開発は難しかった。しかし Visual Studio は 2010 、2012 、2013 、2015 とバージョンアップを続ける中で、着実にデータベースアプリケーションの作成を改善してきており、2015 では SQL Server に接続するデータベースアプリケーションの開発は驚くほど簡単になり、データベースアプリケーション開発ツールとしての基本的機能はほぼ完成をみたといえる。

3.SQL Server データベースファイルとは

SQL Server データベースファイルは Microsoft SQL Server のデータベースの実体で、拡張子が .mdf のデータベースファイルと .ldf のログファイルで構成される。このデータベースはファイルであり、SQL Server のような DBMS に管理されないデータベースファイルだが、SQL Server と同じテーブルやビューを持ち、トリガや関数などを SQL Server と同じ技術で開発できる。ストアドプロシージャも利用できる。SQL Server データベースファイルは必要があれば既存の SQL Server にアタッチすることで管理下におくことができ、デタッチで切り離すことができる。この SQL Server データベースファイルは Microsoft Visual Studio 2015 による C# アプリケーション開発において、シームレスに管理でき、アプリケーションに組み込むことができるため、アプリケーションの組み込みデータベースとして利用しやすい。

4.システムの概要

これから作るシステムは、学校全体で使うマルチユーザーのクライアントサーバー型ではなく、あくまでも教務部長としての私が成績会議の資料を作るために効率化することを当面の目的とする。

(1)生徒名簿やクラスごとの講座情報と成績入力できるデータをあらかじめ用意しておく。
(2)期末考査の後に各授業担当者から出された成績伝票から教務部長が出欠と評価を入力する。
(3)成績会議に応じたデータを抽出して成績会議資料が作成できる。

これらの処理がコンピュータでできれば、期末考査の後に成績伝票が集まると、次の日にでも成績会議を開くことができるようになる。また成績データを集約すれば、帳票さえデザインすれば通知表やクラスごとの生成期一覧表も作成できるようになるだろう。さらにクライアントサーバー型にすれば、そもそも成績伝票を作成する時点で各先生方に成績入力をシステム上で行ってもらうことにできる。そういった展開を視野に入れながら、当面はスタンドアロンのパソコン上で動く Windows Forms アプリケーションを開発する。

Microsoft Azure SQL Database を使う – その1 – 概要

leave a comment »

Microsoft Azure SQL Database はクラウド上に展開する SQL Server だ。Microsoft Azure を利用すると、クラウド上にデータベースを構築することができ、サーバー管理の必要なくデータベースを利用できる。Microsoft Azure SQL Database にデータベースを構築し、Windows Form でデータベースアプリケーションを作って利用する概要は次のとおりである。

まず Microsoft Azure にユーザー登録し、SQL Database を作成する。作成した SQL Server データベースの管理は、Microsoft Azure アカウントの管理ポータルで行う。

SQL_Azure_001_mid_640

<Fig.1 : Microsoft Azure アカウントの管理ポータル>

データベースを作成したら、管理ポータルから SQL Database のページへ移動し、デザインのボタンでテーブルを作成することができる。テーブル作成は SQL Server Management Studio のテーブル作成画面とほぼ同じなので、SQL Server のデータベース開発やAccessを使った経験があれば簡単に使うことができるだろう。

SQL_Azure_002_mid_640

<Fig.2 : Microsoft Azure アカウントの管理ポータルからテーブルを作成する>

Visual Studio による開発では、Microsoft Azure アカウントの管理ポータルから、SQL データベース用のスタートプロジェクトをダウンロードする、のリンクをクリックすると、Visual Studio のプロジェクトがダウンロードできる。このプロジェクトには、ここで作成した Microsoft Azure SQL Database がサーバーオブジェクトとして登録されたプロジェクトになっている。

このプロジェクトを Visual Studio Community 2013 で開くと、オンプレミスの SQL Server でデータベースアプリケーションを開発するのとほとんと同じ手順で Windows Form データベースアプリケーションを開発することができる。

SQL_Azure_003_mid_640

<Fig.3 : Microsoft Visual Studio Community 2013 による Windows Form データベースアプリケーション開発>

Microsoft Visual Studio Community 2013 では全くコードを書くことなく、数ステップのウィザードで1対1でテーブルと関連付けるデータベースアプリケーションを作ることができる。ビルドして実行すると Microsoft Aure SQL Dabase のデータを編集することができる。

SQL_Azure_004

<Fig.4 : Microsoft Visual Studio Community 2013 で作った Windows Form データベースアプリケーションを実行する>

データを追加しデータベースに保存する。Microsoft Azure SQL Database にデータがストアされたことは、Microsoft Azure アカウントの管理ポータルでクエリを発行して確かめることができる。たとえば次の SELECT 文を発行する。

——————————————————————–
select ID,sname,sbirthday from students
——————————————————————–

SQL_Azure_005_mid_640

<Fig.5 : Microsoft Azure アカウントの管理ポータルでクエリを発行する>

以上が Microsoft Aure SQL Database にデータベースを作成し、Windows Form データベースアプリケーションで利用する手順だ。Microsoft Azure SQL Database はデータベースをクラウド上にもたせることでサーバーマシンの管理コストをなくすことができ、インターネットに接続できるあらゆる場所から利用することができる。そして Microsoft Azure SQL Database を利用する WIndows Form データベースアプリケーションの作成は、Microsoft Visual Studio Community 2013 を利用するとオンプレミスの SQL Server を利用する場合とほとんど変わらない開発ができ、単にテーブルと1対1のフォームなら全くコードを書くことなく短い時間で開発できる。

神戸三宮の駅前で定期的に開催する神戸SQL Server Users Group(KSSUG)のSQL Serverセミナーは第二回「初級1」と「初級2」を終了した。

leave a comment »

神戸三宮の駅から歩いて数分の貸会議室を借りて、実習形式のSQL Serverセミナーを開催している。昨日は第二回目のセミナーで、「初級1」と「初級2」を連続で実施した。

神戸SQL Serverセミナー「初級1」2013年12月14日(土)http://kobesqlserverusersgroup.wordpress.com/2013/11/26/sql-server%e3%82%bb%e3%83%9f%e3%83%8a%e3%83%bc%e5%88%9d%e7%b4%9a%ef%bc%91-2013%e5%b9%b412%e6%9c%8814%e6%97%a5%ef%bc%88%e5%9c%9f%ef%bc%89-900%ef%bd%9e1030-%e4%b8%89%e5%ae%ae/

神戸SQL Serverセミナー「初級2」2013年12月14日(土)http://kobesqlserverusersgroup.wordpress.com/2013/11/28/sql-server%e3%82%bb%e3%83%9f%e3%83%8a%e3%83%bc%e5%88%9d%e7%b4%9a2-2013%e5%b9%b412%e6%9c%8814%e6%97%a5%ef%bc%88%e5%9c%9f%ef%bc%89-1030%ef%bd%9e1150-%e4%b8%89%e5%ae%ae/

「初級1」はSQL Serverをやってみたいが触ったことのない人を想定して、データベースの作成、テーブル作成から始める実習、「初級2」はユーザー定義関数とストアドプロシージャ作成の基本実習、を行った。1回のセミナーは1時間20分で、ノートパソコンを使った実習形式だ。

今後は高度なSQL文、実務的なストアドプロシージャ、カーソルの使い方、直積やPIVOPビュー、そしてユーザー管理とActiveDirectoryの連携、またVisual Studioを使ったクライアントアプリケーションの開発、など少しずつ内容を深めていきたいと思っている。

ほぼ月1回、できれば月2回程度開催し、少しでも多くの人にSQL Serverに興味と関心を持ってもらいたい。私自身、自学自習の一般ユーザーにすぎないが、この6年間、実務でSQL Serverによるデータベースシステムの開発と運用、管理をやってきた経験をふまえ、ユーザーに知識と技能を伝えられたらと思っている。

神戸SQL Server Users GroupのSQL Serverセミナー(初級1)を実施した

leave a comment »

校務の情報化にはデータベースの知識と技術が必要なことから、SQL Serverを勉強するコミュニティを作った。とりわけ今年度から指導要録のデータベース化が一歩前進することから、各学校の担当者はデータベース化に取り組まざるを得ないはずだ。

神戸SQL Server Users Group
http://kobesqlserverusersgroup.wordpress.com/

昨日、2013年11月9日(土)には神戸SQL Server Users Groupと特定非営利活動法人 情報技術相互支援協会の共催で、SQL Serverセミナー(初級1)を開催した。

このSQL Serverセミナーでは、SQL Serverの初心者がSQL Serverの基礎を学び、データベースを使ったシステムを作り運用することができるようにすることを目標としている。1回のトレーニングを90分とし、初級講座を1、2と分けて企画した。初級1ではSQL Management Studioの使い方、テーブル作成、データベースダイアグラム、ビューとリレーションシップ、データの型、簡単なSQL文、を内容とした。初級2ではストアドプロシージャ、トリガ、ユーザー定義関数、データベースダイアグラム、SQLの応用、を計画している。

初級セミナーは1と2でおおよそ基本を網羅するように考えたが、やはり90分では深まりに欠ける。あらかじめ用意した初級1のプリントをひととおり網羅したが、やりたいと思っていた直積やPIVOTまで説明できなかったし、SQL文もごく基本的なものに留まった。次回は初級1.5的なセミナーとし、フォローアップを考えている。

今後もSQL Serverセミナーは神戸三宮で定期的に開催する予定だ。日程は決まり次第、神戸SQL Server Users Groupのblog http://kobesqlserverusersgroup.wordpress.com/ に公開するので、興味がある人はチェックしてほしい。

SQL Server : フィールドにIDENTITYを設定して自動的に連番が設定されるようにしたテーブルでIDENTITY_INSERTをONにして明示的に数値を指定したデータをINSERTする

with one comment

データを管理するとき、自動的に連番が設定されるようにしたい場合がある。これは例えばMicrosoft Accessでいう「オートナンバー型」のフィールドに相当する。Microsoft Accessの「オートナンバー型」は、データを生成するときに何も値を入れず、自動的に連番が設定されるフィールドだ。テーブルを構成するとき、このように自動的に連番が割り当てられるフィールドを作っておくと、その連番値自体に特段の意味はなくても、データを管理する場面で有効になる。システムの運用をはじめると、さまざまな想定外のことがおこる。間違ったデータを大量に作ってしまい特定の範囲のデータを全部まとめて修正しなければならない、重複したデータを作ってしまい後から作ったデータを削除しなければならない、ある一定期間に行われた処理に誤りがあり一括してデータを修正しなければならない、といったようなこことだ。これらの運用時に、データが生成された順番がわかっていると処理しなければならないデータの範囲を絞り込む重要な手掛かりになる。

Microsoft Accessには次のように「オートナンバー型」というデータ型がある。

SQL_identity_002_mid_640

<Fig.1 : Miceosoft Access 2013でフィールドのデータ型をオートナンバー型にする>

しかしSQL ServerにはAccessのようなオートナンバー型というデータ型はない。SQL ServerでAccessのオートナンバー型のように、数値が連番で自動的に設定されるようにするには、データ型を「bigint」、「decimal」、「int」、「numeric」、「smallint」、「tinyint」のような整数の性質を持っているものとし、列のプロパティで「IDENTITYの指定」の「(IDである)」を「はい」にし、「IDの増分」と「IDENTITYシード」を設定する。「IDENTITYシード」は最初に割り当てられる数値を意味し、「IDの増分」はデータが増えるたびに加えられる値を意味する。次の例は「IDENTITYシード」を10、「IDの増分」を3として作ったテーブルにデータをいくつか追加した例だ。

SQL_identity_004_mid_640

<fig.2 : IDフィールドのIDENTITYを指定し、IDENTITYシードを10、IDの増分を3と設定する>

SQL_identity_006_mid_640

<fig.3 : IDフィールドにIDENTITYを指定したテーブルにデータを3件追加した>

なおIDENTITYの設定は「float」、「real」のような実数型では指定することができない。また「money」、「smallmoney」でも指定することができない。

IDENTITYを指定したフィールドには明示的に値を指定することはできず、自動的に値が割り当てられるだけになる。例えば次のようなINSERT文は実行できない。

——————————————————————

insert into Table_1(ID,[values]) values(20,’testinsert’)

——————————————————————

SQL_identity_007_mid_640

<fig.4 : IDENTITYを指定したフィールドに明示的に値を指定したINSERT文を実行しエラーとなった>

IDENTITYを指定したフィールドは、たとえば次のように明示的に値を指定せずにINSERT文を実行することになるだろう。

——————————————————————

insert into Table_1([values]) values(‘testinsert’)

——————————————————————

SQL_identity_008_mid_640

<fig.5 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した>

SQL_identity_009_mid_640

<fig.6 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した結果>

INSERTされたデータのIDは、フィールドに指定した「IDの増分」だけ増えた値が自動的に割り当てられている。もちろんこれは期待どおりの結果だが、データの保守管理上はこのままでは困る場合が出てくる。たとえばあるテーブルのデータを別のテーブルに一時的に退避し、後に戻したいといった場合だ。データを退避するため「table_1」と同じフィールド構造をもつ「Table_2」を作っておくが、「Table_2」の「ID」フィールドのIDENTITYは設定しない。そして次のクエリのように、「Table_1」のデータを「Table_2」にコピーし、「Table_1」のデータを消去するが、そのあと、「Table_2」のデータを「Table_1」の戻すときにはエラーとなる。

——————————————————————

insert into Table_2(ID,[values]) select ID,[values] from Table_1
delete Table_1
insert into Table_1(ID,[values]) select ID,[values] from Table_2 –(エラーになる)

——————————————————————

SQL_identity_010_mid_640

<fig.7 : IDENTITYが設定されているフィールドに明示的に値を指定してINSERTしようとしてエラーになった>

このエラーを回避する最もわかりやすい方法は、「Table_1」の「ID」列のIDENTITY指定を一時的に解除することだ。しかし一時的ではあれテーブルのデザインを変更すると、そのあいだに他のユーザーが正しい方法でデータを挿入しようとしたときにエラーになるだろう。自分だけが使うデータベースであるとか、システムが小規模で他に与える影響がないと考えられる場合はいいかもしれないが、望ましいことではない。

このようにIDENTITYが設定されたフィールドに明示的に値を与えたいとき、IDENTITY_INSERTをONにする方法がある。先のエラーになったINSERT文の前後にIDENTITY_INSERT文を使おう。例えば次のクエリはエラーにならず成功する。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) select ID,[values] from Table_2
set identity_insert Table_1 off

——————————————————————

SQL_identity_011_mid_640

<fig.8 : IDENTITY_INSERTの設定によりINSERTが成功した>

set identity_insert Table_1 onによってIDENTITYを設定したフィールドにも明示的に値を与えることができるようになり、set identity_insert Table_1 offでデフォルトの状態に戻る。

たとえば次のようにIDに「20」という値を明示的に挿入するクエリも実行することができる。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) values(20,’ID20testdata’)
set identity_insert Table_1 off

——————————————————————

SQL_identity_012_mid_640

<fig.9 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした>

SQL_identity_013_mid_640

<fig.10 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした結果>

このように、テーブルにIDENTITYを設定したIDフィールドを持たせることはよくやることだが、IDENTITYを指定したフィールドには通常は明示的な値を与えることはないし、それはできないのだが、identity_insertをon、offすることによって明示的に値を与えることができ、これはデータの管理の手法として知っておくとよい。