ハロの外部記憶インターフェイス

そろそろ覚える努力が必要かも…

MSSQL

OracleのRAW型をSQL ServerのVARBINARY型への移行方法

OracleのテーブルをMSSQLへ移行する ORACLEがRAW型を持つデータがあり、SQL Serverへ移行する方法を探した結果 なんとか解決したので、メモ。 OracleのRAW型に対応するMS SQLServerの型はVARBINARY型 ORACLE CREATE TABLE rawdata ( PASSWORD RAW(64) ); SQL…

トリガー作成メモ

TRIGGER作成 基本文法 CREATE TRIGGER [dbo].[TR_TABLE_NAME] ON [dbo].[TABLE_NAME] AFTER INSERT,DELETE,UPDATE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert …

CSVファイルからのBULK INSERT

CSVファイルからSQL ServerのTableへ高速でデータを投入する 私用場面は色々あるが、とりあえず、スキーマ間でデータを移動する場合を想定します。 復元もとDBからデータを抽出するためのSQLをファイルに記載します。 output.sql SET NOCOUNT ON ; SELECT * …

SQLCMDでSELECT結果をCSV出力する。

SELECT文の結果をSQLCMDを利用し、CSV出力する query.sql クエリを指定したファイル SET NOCOUNT ON; SELECT [Code] ,[Name] FROM [dbo].[Data] ; SET NOCOUNT ON;を削除すると件数が一緒に出力される。 SQLCMD query.sqlを読み取り、output.csvファイルへ出…

Visual StudioのLocalDb管理

LocalDb Visual Studioから、ローカル用にLocalDbの追加が可能になっている。 ※詳細については SQL Server 2016 Express LocalDB | Microsoft Docs Visual Studioから追加する。 サンプルプロジェクトはASP.NET MVC Webアプリケーションのルートから、マウス…

SQL ServerのIdentityカラムの最大値

Identity カラム設定 int型で生成した場合、最大値は「21億(正確には2,147,483,647)」まで採番可能になる。 CREATE TABLE [table1] ( [IDNO] int NOT NULL IDENTITY (1, 1), CONSTRAINT [PK_T_SYSTEM_MON_NEW_HISTORY] PRIMARY KEY CLUSTERED ( [IDNO] ASC…

SQL Serverで外部ファイルをコマンドライン実行する

外部ファイルをコマンドラインから実行 サンプル db_nameに接続し、script.sqlを実行し、処理結果をscript.logファイルに出力する。 sqlcmd -U "sa" -P "password" -S "localhost" -d db_name -i script.sql -o script.log サーバー認証の場合、-E sqlcmd -S…

SQL ServerのManagement Studioでは早いがアプリケーションで実行すると遅くなる

Management Studioでのクエリ実行とPGの中での実行の違い 主に遅い原因は実行プランの違い、問題はなぜ違いが発生するかだが、原因としてManagement Studioでの実行とPGでの実行ではPGで頻繁に実行されるとSQLが再利用される。実行プランも再利用されるため…

インデックス断片化調査

SQL Serverびインデックス断片化の調査 とりあえず30%以上だったら、最高値を検討しよう SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'DbName'), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes …

SQL ServerにOracleにリンクサーバーを設定する。

SQL ServerにORACLEへのリンクサーバ設定 SQl ServerからOracleをリンク経由で処理を行う環境 Oracle Provider for Oracleで登録しても何故か「インスタンス生成失敗」のエラーになった。 SQL Server 64bitバージョンの場合、ODACだけでは「OraOLEDB.Oracle…

行データをカンマ区切り表示

ORACLE, MYSQLの場合 GROUP_CONCATを使用する。 SELECT GROUP_CONCAT(CLM_NAME) FROM TABLENAME; テスト用データ差癖 create table test1 (clm1 int, clm2 text(500)); insert into test1 values (1, 'abc'); insert into test1 values (1, 'def'); insert i…

Docker MS SQL Server 2017 for linux

Docker用SQL Server 2017を起動してみる。 参考:https://docs.microsoft.com/ja-jp/sql/linux/quickstart-install-connect-docker Dockerのメモリを4GB にする必要がある。 最上位のステータス バーに Docker ロゴをクリックします。 選択設定です。 メモリ…

SQL Server 2014への接続をIP Addressでする

SQL Serverへの接続をIP Addressにする方法 インストールしたばかりでは、基本「パソコン名\サーバー名」などでしか接続できません。 これをIPアドレスでできるようにするためにはSQL Server 構成マネージャーで設定が必要です。 SQL Server 構成マネージャ…

サーバリンクのトランザクショントラブル

MSSQLサーバ同士にサーバリンクを作成 リンクサーバーの情報を先に登録 EXEC sp_addlinkedserver @server = 'MY_LINK_DB', --リンクサーバの名前 @srvproduct = '', @provider = 'SQLNCLI', -- プロバイダーの名前 @datasrc = '192.168.1.201'; -- ロケーシ…

DB・ログファイルの圧縮

DBファイルが膨らんで空き領域が増えている場合、圧縮する必要がある まず、DBの情報を収集 exec sp_helpdb 'dbname' name db_size owner dbid created status compatibility_level dbname 3055.44 MB dbname 9 06 21 2012 Status=ONLINE, Updateability=REA…

SQLCMD コマンドラインで実行

SQL ServerのSQLCMD SQL Serverでもコマンドラインで実行出来るユティリティが存在する。 実行方法 C:\work>sqlcmd -S localhost -U sa -P 1234 1> use master 2> select name,file from sysdatabases 3> go S : ログインするSQLサーバーのホスト名 U:ログ…

DBをBackupから復元した時のトラブル

SQL Server間でバックアップを復元舌時にアクセスできなくなる時の対応 現象としては「ユーザー'xxxx'の作成に失敗しました」、 「ユーザー、グループ、またはロール'xxxx'は現在のデータベースに常に存在します」のエラーが表示される原因は他Serverからの…

Table型のパラメータ引渡し

.NetでSQL ServerのStoredProcedureの実行時、パラメータとしてテーブル型を渡す方法 SQLサーバに対象となるTable型を定義する CREATE TYPE dbo.MyTableType AS TABLE ( id int, name varchar(50) ) MyTableTypeと言う名前で型を追加 テスト用データを保存す…

T-SQLで動的SQLを実行し値を取得する方法

MS SQL Serverで動的SQLを作成し実行及び値を取得する。 declare @sSQL nvarchar(max); declare @sParam nvarchar(max); declare @sOutput1 char(8); declare @sOutput2 char(10); set @sSQL = N'select @sDate1 = convert(char(8),getdate(),112)' set @sSQ…

SQL ServerのMinus、Except

OracleのMINUS演算子がSQL Serverには存在しない、代わりにEXCEPTを使って似た様な処理が可能だ。 EXCEPT:左のクエリにあって右のクエリにない個別の値を返す。 結果はMINUSと同じになる。(違うケースあり) insert into TBL1 values ('1'); insert into T…

文字列の計算式でのnullの扱い

文字列の計算で項目がnullの場合、結果がnullになることに注意 ORACLEでは''はnullと同じ扱いになる為、問題ないが、MS SQL Serverでは問題になる MS SQL Serverでの例 SQL1 : select 'a' + null + 'b' SQL2 : select 'a' + '' + 'b' 上記SQL1とSQL2の結果 S…

SELECT文の結果を新規TABLEに投入

SQLサーバーでもSELECTの結果で新しいTABLEを作成し、データを投入する事が可能 ORACLEでの「CREATE TABLE NEW_TBL1 AS SELECT * FROM TBL2」の様な書き方で、下記の様に記入 MS SQL SERVERでの方法 SELECT * INTO NEW_TBL1 FROM TBL2; 結果:TBL2の全項目で…

SQL SERVERでのMERGE文

MS SQL SERVER 2008からサーポートされたMERGEステートメントの使い方 MERGE TBL1 USING TBL2 ON (TBL1.CLM1 = TBL2.CLM1) WHEN MATCHED THEN UPDATE SET TBL1.CLM2 = TBL2.CLM2 WHEN NOT MATCHED THEN INSERT (TBL1.CLM1, TBL1.CLM2) Values (TBL2.CLM1, TB…

SQL ServerでRPAD関数の作成

MS SQLにはRPAD関数は基本提供されない為、関数として作成しておく必要がある CREATE FUNCTION F_RPAD ( @TEXT VARCHAR(200), @MAX_COUNT INT, @PAD_CHAR CHAR(1) ) RETURNS VARCHAR(200) AS BEGIN DECLARE @RTNVAL VARCHAR(200) SELECT @RTNVAL = RIGHT(REP…