SQLServer DateTime型のフィールドの値の日付を取得

SQLServer DateTime型のフィールドの値の日付を取得するとき。SQLServerではDATE_FORMAT()などが使えないので以下のようにするととれる

select CONVERT(NVARCHAR, TBL_AAA.FIELD_DATETIME, 111) from TBL_AAAA

3つ目の引数で、取得する形式を指定。'111'を指定した場合、'yyyy/mm/dd' の形式で取得できる。

オートナンバーのフィールドの値を取得

オートナンバーになっているフィールドの値をINSERT直後に取得したい場合

insert into TBL_AAAA(field_a, field_b, field_c) values ( 123, 564, 'sss' ) select SCOPE_IDENTITY() as new_id

とすれば、new_id　として値を取得できる。

外部結合で参照列が複数あるSQL

テーブルAには

ID　｜　NAME
001　｜　太郎
002　｜　次郎
003　｜　三郎

がはいっていて、

テーブルBには、テーブルAのIDを参照して

ID　｜　GROUP_NAME | FRIEND＿ID
001　｜　さくら | 003
002　｜　ぽぷら | 002
003　｜　いちご | 001

のようにはいっている。

テーブルBからセレクトするとき、テーブルAを結合して、IDとFRIEND_IDの二つの列に対応するNAMEと取得したい場合いかのように書く

select
テーブルB.ID, テーブルB.GROUP_NAME,テーブルB.FRIEND_ID,T1.NAME as HoninName, T2.NAME as FriendName
from テーブルB
left join
テーブルA as T1
on T1.NAME = テーブルB.ID
left join
テーブルA　as T2
on T2.Name = テーブルB.FRIEND_ID

副問合せを使った削除

本来であれば、

DELETE from TABLE_WRK_GRP WHERE WORKER_ID = '103066' and GROUP_CODE　= 2365

として、TABLE_WRK_GRPからデータを削除したいのに、すぐに入手できるデータがWORKER_IDと別テーブル、TABLE_GRPで GROUP_CODE　と関連付けられている　NAME しか分からない場合、
いちいち、TABLE_GRP　から　NAME でセレクトしGROUP_CODE　を取得してから上記のSQLを書くと手間になる。

そのときは副問合せをつかって、そのselect作業もおなじSQL文で実行できる。

DELETE from TTABLEBL_WRK_GRP WHERE WORKER_ID = '103066' and GROUP_CODE in ( select GROUP_CODE from TABLE_GRP where NAME = 'abcdef' )

データ型 text と varchar は equal to 演算子では互換性がありません。

SQL Server のテーブルで、text型を使って列を定義したとき以下ののSQL文がエラーになる

select * from TABLE_A where field_a = 'aaa'

エラーの内容は以下のとおり

データ型 text と varchar は equal to 演算子では互換性がありません。

対策：

1.DBのデータ型を text から varchar()、nvarchar() に変更する。

2.SQL文で以下のように、データの型変換を記述する。（DBのデータ型を変更できないときはこれしかない）

select * from TABLE_A where CONVERT(nvarchar(20),field_a)= 'aaa'

もしくは、

select * from TABLE_A where CAST(field_a as nvarchar(20)) = 'aaa'

Singleton とは

Singleton パターンを用いると、プログラム内でそのクラスのインスタンスが1つしか生成されないことを保証することができる。ポイントはいかの3点。

・同じ型のインスタンスが private なクラス変数として定義されている。
・コンストラクタの識別子が private になっている。
・同じ型のインスタンスを返す getInstance() がクラス関数（名前は任意）として定義されている。

たとえば、ある一式の設定を管理するクラス ClassSetting にsingletonパターンを定義してみると、

' Singletonクラス
Public Class ClassSetting

    Private Shared setting As ClassSetting = New ClassSetting ()

    ' コンストラクタ(外部からのアクセスが不可)
    Private Sub New()
       ...
    End Sub

    ' 唯一のインスタンスを取得
    Public Shared Function GetInstance() As ClassSetting
       Return setting
    End Function

End Class

コンストラクタの識別子が private となっているのがポイント。

コンストラクタの識別子が private になっていることで、ClassSetting のインスタンスを外部から生成することはできない。

だから、ClassSetting クラスのインスタンスが必要なときは、以下のように、getInstance()メソッドを使って利用することになる。

dim setting as ClassSetting = ClassSetting.GetInstance()

getInstance()メソッドは最初に呼び出されたときにだけインスタンスを生成し、2回目以降に呼び出されたときは最初に生成したインスタンスを返すように作られている。

そのため、プログラム中にSingletonクラスのインスタンスが1つしか存在しないことが保証される。

SQL Server の予約語

FirebirdからSQL ServerへのDB移行の際、Firebirdにあった列名「USE」がそのまま移行できなかった。

理由は「USE」がSQL Server の予約語であったため列名として使えないから。そのため、別の列名を指定する必要があった。

SQL Server の列名などに使うことができない予約語の一覧が、以下のサイトにある。

http://technet.microsoft.com/ja-jp/library/ms173340%28SQL.90%29.aspx

今後、DB設計などの際に注意。

SQL Server で外部キーの設定がうまくできない

SQL Server で外部キーの設定をしようとして、次のようにSQLをたたくとなぜかエラーに。。

alter table AAAA add constraint F_KEY foreign key (aaaa) references BBBB(bbbb)

エラーメッセージは次のような感じ。

"ALTER TABLE ステートメントは FOREIGN KEY 制約 "F_KEY" と競合しています。競合が発生したのは、データベース XXXX、テーブル BBBBです。"

http://faq.tabitama.co.jp/qa5445297.html?order=DESC&by=datetime

で、DBに制約違反のデータがあるとうまくいかない、との指摘あり。

それで、テーブルAAAA内で、テーブルBBBBと外部キー制約を設定したかったフィールドを一回NULLにして、 再びクエリを実行して解決。