→SQL Server 2005

→T-SQL関連メモ

→MSDEメモ

→ADO.NET関連

#contents

*Tips [#gcd7697c]

-[[Microsoft SQL Server Community & Samples@CodePlex>http://codeplex.com/SqlServerSamples]]
--サンプルデータベースや各種ツール、サービスなどのDownloadが可能

-[[SQL Server - ジョブの作成とスケジュール設定方法>https://sql55.com/sql-server/how-to-create-scheduled-jobs.php]] 2021.8

-[[日付計算のベストプラクティス>http://codezine.jp/a/article/aid/1686.aspx]]
-[[Various Methods of Connecting to SQL database using .NET>http://www.codeproject.com/useritems/SQLConnectionMethods.asp]]
-[[Questions to ask a SQL Server database developer applicant:http://articles.techrepublic.com.com/5100-9592_11-6126230.html]] 2006.10.18
-[[Basic Cryptographic Hashing of a File and storage to SQL Server:http://www.codeproject.com/useritems/Crypto_FileHash_SQLStore.asp]]
-[[SQL Server Users Group:http://www.sqlpassj.org/]]
-[[SQLServer会議室:http://www7.big.or.jp/~pinball/discus/sqls/]]
-[[ SQL Server 2000で大量の結果セットを効率的にページングする方法:http://codezine.jp/a/article.aspx?aid=391]]
-[[VS.NET ExpressとSQL Server Expressのチュートリアル:http://www.microsoft.com/japan/msdn/coding4fun/inthebox/dataenabled/default.aspx]]
-[[Dr.KのSQL Serverチューニング研修@IT:http://www.atmarkit.co.jp/fdb/index/index-db.html#drk]]
-[[SQL Server 2000チューニング全工程@IT:http://www.atmarkit.co.jp/fdb/rensai/sqlstune01/sqlstune01_1.html]]
-[[Detecting and Resolving Stalled and Stuck I/O Issues in SQL Server 2000 SP 4:http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_04222005.asp]]
-[[SQL Server 2000@MSDN:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp]]
-[[SQL Serverのパフォーマンス向上:http://www.microsoft.com/japan/msdn/enterprise/pag/scalenetchapt14.asp]]

-https://sql55.com/
--このサイトでは、SQL Server や T-SQL を初めて学ぶ人のために、SQL Server に関する基本的なことから、便利な T-SQL &クエリーの紹介や、チューニングの仕方などについて解説しています。

*ツール [#q1c87f1b]
-[[DBdoc automated CHM documentation for a SQL Server 2000>http://www.codeproject.com/KB/vb/DBdoc.aspx]]
-[[Schema Comparison Utility:http://www.codeproject.com/useritems/DatabaseCompare.asp]]
-[[Retrieve SQL Server Password:http://www.codeproject.com/useritems/SQLServerPasswords.asp]]
-[[SQL Server 2000 Collation Changer:http://www.codeproject.com/vb/net/ChangeCollation.asp]]
-[[SQL Database Documentor:http://www.codeproject.com/useritems/dbadoc.asp]]
-[[SQL Server 2K Job Monitor:http://www.codeproject.com/useritems/SQLMon.asp]]


*トランザクションの分離レベル [#l91209d3]
-[[トランザクションの隔離とは>http://www.interdb.jp/techinfo/postgresql/p-2-07.html]]
-[[同時実行@宇宙仮面のC#プログラミング>http://uchukamen.com/SQL2005/Lock/Lock.htm]]
-[[トランザクション分離レベル 読み取り一貫性のサポートとロックエスカレーション>http://www.microsoft.com/japan/sql/facts/compare/03.mspx]]
**ANSI標準の4つの分離レベル [#d822f138]
-READ UNCOMMITED
--もっとも安全度低い
--他プロセスがトランザクション中にUPDATEしたデータをCOMMITする前にSELECT(ダーティリード)可能

-READ COMMITED
--デフォルトの分離レベル。引数なしでBeginTrans()するとこれになる(SQL Server 2005)
--ダーティリード不可
--自分のトランザクション中に、自分がSELECTしただけのレコードは他プロセスがUPDATE-COMMITできる(=反復不可能読み取りが可)
--つまり、自分のトランザクション中のSELECT結果の一貫性が保証されない
--READ_COMMITED_SNAPSHOTオプションの状態によって動作が異なる
---OFFのとき、トランザクションにひっかかったクエリは待たされる
---ONのとき、トランザクションにひっかかったクエリは、トランザクション開始前の状態を取得し、ロック待ちをしない

-REPEATABLE READ
--ダーティリード不可
--自分のトランザクション中に、自分がSELECTしただけのレコードも他プロセスがUPDATE-COMMITできない(=反復不可能読み取りが不可)
--自分のトランザクション中に、自分がSELECTした条件にマッチするレコードをINSERT可能(=ファントム発生可)
--つまり、自分のトランザクション中のSELECT結果は、その時点で存在していたレコードについては同じであることが保証されるが、存在していなかったレコードが追加されている可能性はある

-SERIALIZABLE
--ダーティリード、反復不可能読み取り、ファントム発生すべてが不可
--トランザクションのSELECT結果は外部からの影響を一切受けないことが保証される

*環境設定の確認 [#j691c3fd]
-http://support.microsoft.com/kb/319942/

*メモリの使い方について [#u2a939b6]
-最大メモリはManagementStudio でサーバのプロパティの「メモリ」のページから設定可能

-メモリ設定の詳細については、SQL Server Books Online のトピック「メモリ設定オプションを使用したサーバー パフォーマンスの最適化」を参照してください。 

-[[構成オプションを使ってメモリ使用量を調整する方法>http://support.microsoft.com/kb/321363/ja]]
--デフォルトで、SQL Server ではオペレーティング システムから報告される物理メモリの負荷に合わせて、動的にバッファ プール (キャッシュ) のサイズが拡張または圧縮されます。ページングの防止に必要な量のメモリ (4 MB から 10 MB の間) が確保されている限り、SQL Server のバッファ プールは拡張し続けます。SQL Server と同じコンピュータの他のプロセスでメモリの割り当てが行われると、SQL Server のバッファマネージャは必要に応じてメモリを解放します。SQL Server は、1 秒間に MB 単位でメモリの解放または取得を行うことができ、メモリ割り当ての変化にすばやく対応できます。
--min server memory および max server memory 構成オプションを使用して、SQL Server データベース エンジンで使用されるメモリ (バッファ プール) の容量の上限値と下限値を設定できます
--max server memory オプションで制限できるのは、SQL Server のバッファ プール サイズのみです。拡張ストアドプロシージャ、COM オブジェクト、共有されていない DLL、EXE、MAPI などのための、SQL Server によって確保されないメモリ領域は、max server memory オプションでは制限されません。このメモリ領域の割り当てによって SQL Server のプライベート バイトが max server memory オプションで設定した値を超えることがあります。


*DBリンク [#u2a2c9ca]
-リンクデータベースを作成して他のインスタンスのテーブルを参照する方法

-(例)
--リンク名(任意の名前):LinkSQLSrvr
--データプロバイダ:SQLOLEDB(これはSQL Native Client For OleDB)
--サーバー名:KNLDB001\SQLDEV
--認証:SQL Server認証
---ローカルDBのログインユーザー:iyada
---リモートDBのログインユーザー:kaeritai
---リモートDBのログインパスワード:madakaerenai

-ローカル側DBに接続して以下のSQLを実行
 sp_addlinkedserver N'LinkSQLSrvr', ' ', N'SQLOLEDB', N'KNLDB001\SQLDEV'
 GO
 sp_addlinkedsrvlogin N'LinkSQLSrvr', false, N'iyada', N'kaeritai', N'madakaerenai'
 GO
これで直接SQLを実行することが可能になる。
 select * from LinkSQLSrvr.db2.dbo.tenpo


*同時接続数 [#i17eb1bf]
-[[SQL Serverへの同時接続について>http://forums.microsoft.com/MSDN-JA/ShowPost.aspx?PostID=751883&SiteID=7]]
--http://www.microsoft.com/japan/technet/prodtechnol/sql/2000/books/c01ppcsq.mspx
--SQL Server 2000 のすべてのエディションでは、ユーザー接続が自動的に、かつ動的に設定されます。同時ユーザー接続数について特定の制限事項があった以前のバージョンとは、この点が異なります。そのため、ユーザー接続の管理について以前ほど気にする必要はありません。ただし、ユーザー接続数が増加するに従って、サーバーリソースの使用量も増加することだけは注意してください。サーバーで、多くのユーザー接続のワークロードを分散させる必要が発生し、それによって各ユーザー接続、ひいてはサーバー全体のスループットが低下します。


*画像をDBに格納するサンプル [#gd5bd65f]
-サムネイルのサンプルも兼ねる
-http://www.codeproject.com/useritems/EasyThumbs.asp

*Correlated Subquery [#jf65b547]
-いわゆる相関クエリ。サブクエリを使うときにメインのクエリの検索結果の1レコードに対してなんらかの関係を持つようなサブクエリを指定するような場合を言う


* Clusterd indexと Non-clustered indexの違い [#edcd9275]
-Clustered indexes store the data at the leaf level of the index.
--This means that whichever field(s) in your table are included in the clustered index, they will be stored in an orderly fashion in the table. Because of this sorting, you can only have one clustered index per table. 
--Non-clustered indexes contain a row identifier at the leaf level of the index. This row identifier is a pointer to a location of the data on the disk. This allows you to have more than one non-clustered index per table.
-要は、テーブルのレコード数が多いときは非クラスタインデックスを使えということか

*ユーザ定義関数(UDF) [#j1304a0d]
-http://codezine.jp/a/article.aspx?aid=583
-SQL Serverには、あまり目立たないながら検討に値するビューとストアドプロシージャの代替手段があります。その代替手段とは、テーブル値ユーザー定義関数(UDF)です。テーブル値UDFは、ビューとストアドプロシージャの重要な機能をすべて備えているだけでなく、ビューとストアドプロシージャにはない別の機能も備えています。

*Full-Text Indexing files with SQL Server [#b7acd6bd]
-http://www.codeproject.com/useritems/sqlfulltextindexing.asp

*osqlを使ってバッチでバックアップする [#daeef553]
-バッチファイルの例
 osql -S <dbname> -U sa -P <passwd>  -i test.sql
-test.sqlの中身の例
 backup database <dbname> to disk='hoge.bak' with init;
 go
-※osqlの-Qオプションで直接指定してもよい

*BCPによるインポート/エクスポート [#s96d24d7]
-これはバックアップ/リストアとは別の機能なので注意
-コマンドラインからBCPコマンドによる操作が可能
-3つの形式をサポートする
--ネイティブ形式
--テキスト形式(Shift-JIS タブ区切り、行端\n)
--テキスト形式(Unicode)
-レコード単位でのデータ投入が可能
-インポートの場合
 BCP [DB.][OWNER.]TABLE in <ファイルへのパス> <option>
-エクスポートの場合
 BCP [DB.][OWNER.]TABLE out <ファイルへのパス> <option>

-基本構文
 bcp "テーブル名" in または out "テキストファイル名" オプション
--in がDBへのインポート、outがファイルへの吐き出し
--オプション:
 -U SQL Server認証を使ってログインするログインアカウント名の指定
 -P -Uオプションで指定したログインアカウントのパスワード
 -T Windows認証を使ってログインする
 -c 文字データモードで転送
 -n ネイティブデータモード(SQLServerのみが解釈できる形式)で転送
 -t 列区切りを示す記号を指定(デフォルトはタブ:¥t) ¥は半角です
 -r 行区切りを示す記号を指定(デフォルトはタブ:¥n) ¥は半角です
--なのでSQLServer認証すると仮定してCSVファイルをインポートする場合
 bcp "sampleTable" in "test.csv" -c -S DBサーバ -U ログイン -P パスワード -t","
--参考資料:http://msdn2.microsoft.com/ja-jp/library/ms162802.aspx

--フォーマットファイルを使用すると、SQLLoaderのイメージで利用できます。
---たとえば、日付項目(yyyy.mm.dd hh:mm:ss)で出力されているCSVファイルを日付項目(CHAR(8))でインポートしたい場合フォーマットファイル内でコンバート処理してやればいいです。エクスポートの際もフォーマットファイルを通して出力すると加工済みでファイルが出来ます。
--参考資料:http://msdn2.microsoft.com/ja-jp/library/ms189636.aspx

-エラーの例
--このようなエラーになる場合があるが
 SQLState = S1000, NativeError = 0
 Error = [Microsoft][ODBC SQL Server Driver]一括コピー ホストのデータファイルが開けません。
--インポートしようとしているファイル名が1文字間違っていた

*BULK INSERT [#s5c22d44]
-インポートみたいなことができるらしい
-[[MSDNの説明:http://www.microsoft.com/japan/msdn/library/default.asp?url=/japan/msdn/library/ja/tsqlref/ts_ba-bz_4fec.asp]]


*SQL-DMO [#ic108b7c]
-SQL Distributed Management Objects の略。COMオブジェクトとして提供されているらしい
-SQL ServerのBooks Onlineによると
 Within SQL-DMO, collections represent a group of Microsoft&#174; SQL Server&#8482; components. 
 The meaning of the collection, the components referenced from the objects contained,
 is visible in the collection's name.
 For example, the Operators collection contains Operator objects that 
 reference SQL Server Agent operators.
とのこと
-DMOを使ってサーバのDB名やディレクトリを取得するサンプル(c#) 
 using System;
 using System.Diagnostics;
 using SQLDMO;
 using System.Collections;
 
 public class SqlDmo
 {
      static public String mServerName = "サーバ名適当に";
      static public String mUserName = "ユーザ名";
      static public String mPassword = "パスワード";
 
      static public void Test() 
      {
            try 
            {
                  Debug.WriteLine("----DB名列挙----");
 
                  SQLDMO.SQLServer sv = new SQLDMO.SQLServerClass();
                  sv.Connect(mServerName, mUserName, mPassword);
 
                  int c = sv.Databases.Count;
                  for(int i = 1; i <= c ; i++ ) 
                  {
                        _Database db = sv.Databases.Item(i,"SA");
                        Debug.WriteLine("DB"+ i + ":" + db.Name );
                  }
 
 
                  Debug.WriteLine("----Drive列挙----");
 
                  QueryResults qr = sv.EnumAvailableMedia(
                          SQLDMO_MEDIA_TYPE.SQLDMOMedia_FixedDisk);
                  for( int i = 1; i <= qr.Rows; i++ ) 
                  {
                        String s = qr.GetColumnString(i,1);
                        Debug.WriteLine("DRV" + i + ":" + s);
                  }
 
 
                  Debug.WriteLine("----フォルダ名列挙----");
                  QueryResults qr2 = sv.EnumDirectories("c:\\");
                  for( int i = 1; i <= qr2.Rows; i++ ) 
                  {
                        String s = qr2.GetColumnString(i,1);
                        Debug.WriteLine("DIR" + i + ":" + s);
                  }
                  
                  sv.DisConnect();
            
                  Debug.WriteLine("----end----");
            }
            catch(Exception ex) 
            {
                  Debug.WriteLine(ex.Message);
            }
      }
 }
-DMO用DLL(Microsoft SQLDMO Object Library)への参照設定はしておく必要がある。
--C:\Program Files\Microsoft SQL Server\80\Tools\binn\SQLDMO.dllがインストールされている必要あり
-[[SQL-DMOを単独で配布する方法:http://support.microsoft.com/?scid=kb%3Ben-us%3B326613&x=9&y=9]]
--SQL-DMOに依存するプログラムを作った場合は配布&インストールの必要がある。

*SMO [#i02b994b]
-SMOとはSQL Server用の管理用フレームワーク(.NET用)。実装はSQL Server内のシステムストアドプロシージャであり、SMOはその.NETフレームワーク用ラッパと考えられる。
-SQL Serverとは別途インストールが必要な模様(クライアント側に?)
-SMO=SQL DMOの機能を後継したものという説明もあるのでSQL Server 2005でないと使えないと思われる。
-[[SMO@MSDN:http://msdn2.microsoft.com/ja-jp/library/ms162169.aspx]]
-[[SMO - Manage your SQL Server:http://www.codeproject.com/useritems/SMODemo.asp]] 2006.4.12
--A quick start tutorial on how to manage your Microsoft SQL Server 2000/2005 with C# and the Microsoft's SQL Server Management Objects (SMO) 

*Enterprise Manager [#o38132f7]
-復元のときに元のDBと違うDBに復元したいときは「データベースの復元」ダイアログの「全体」タブの「データベースのバックアップを表示」のドロップダウンリストで復元元のDB(バックアップを取ったDB)の名前を指定する。そうしないとRESTOREがエラーになるので注意。


*使用ポート [#l58eb5cb]
-[[ファイアウォール経由でのSQL Serverとの通信に必要なTCP/IPポート:http://support.microsoft.com/default.aspx?scid=kb;JA;287932]]
--デフォルトではTCP:1433とUDP:1434を使う

*varcharとnvarcharの違い [#qda60501]
-varcharはShift-JISで保存し、バイト数で数える
-nvarcharはUnicodeで保存し、文字数で数える

*COLLATEとは [#f6833cae]
-照合順序のこと。照合順序とは、文字列比較とかソートをするときの細かい動きの違い。例えば、大文字小文字を無視するかどうか、アクセントの有無を区別するかどうか、とか。
--CIで大文字小文字を無視。CS(Case Sensitive?)で大文字小文字を区別。
--AIでアクセント無視。AS でアクセント区別。
--WSで1バイトと2バイトの文字を区別(デフォでは区別しない)。
--KSでカタカナとひらがなを区別する(デフォでは「ア」と「あ」を同一視する
-例)COLLATE Japanese_CI_AS であればその列で比較をする際に日本語のコードページ(932)を使って大文字小文字を無視し、アクセントは区別して大小を決める。
-照合順序にどんなのがあるか知りたければ、SQL Serverのヘルプで「SQL 照合順序名」を検索せよ


*ローカル一時テーブルとは [#s9027ace]
-テーブル名の先頭に#をつけてCREATEすれば、そのセッションで固有のテーブルが保障され、セッション終了時に破棄される。これをローカル一時テーブルと呼ぶ

*データファイルの種類 [#n5d9fd5e]
-三種類に分かれる

-プライマリ データ ファイル 
--データベースの開始点
--1 つのデータベースに 1 つプライマリ ファイルが必要
--プライマリ データ ファイルに推奨されるファイル名拡張子は .mdf

-セカンダリ データ ファイル 
--セカンダリ データ ファイルは、プライマリ データ ファイル以外のすべてのデータ ファイル
--セカンダリ データ ファイルがない場合と、複数のセカンダリ データ ファイルがある場合がある
--セカンダリ データ ファイルに推奨されるファイル名拡張子は .ndf

-ログ ファイル 
--ログ ファイルは、データベースの復旧に使用するすべてのログ情報を保持
--1 つのデータベースには最低 1 つのログ ファイルが必要で、複数あってもかまいません
--ログ ファイルに推奨されるファイル名拡張子は .ldf

-[[参考ページ:物理配置とファイルグループ:http://blogs.wankuma.com/naka/archive/2005/08/07/17900.aspx]]

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