SQL Server

★型

整数： bigint int smallint tinyint bit

小数： decimal float

金額： money smallmoney

日付・時間： datetime smalldatetime date time datetime2 datetimeoffset

文字列： varchar nvarchar char nchar

text ntext ⇒ varchar(max) nvarchar(max)

二進数： varbinary binary

image ⇒ varbinary(max)

特殊： sql_variant timestamp uniqueidentifier xml

大 > 小

bigint > int > smallint > tinyint > bit varchar(20) > varchar(10) datetime > smalldatetime

可変 > 固定

float > decimal varchar > char nvarchar > nchar varbinary > binary

char 長さが固定例：電話番号、郵便番号効率よい LTRIMとRTRIMを忘れず

varchar 長さが可変例：名前、地名

char/varchar 英数字非Unicode 範囲：1～8000 byte単位で保存

nchar/nvarchar 多国言語 Unicode 範囲：1～4000 char単位で保存

char(10) 内容：abc 消費：10 byte 最大10 byte

varchar(10) 内容：abc 消費：3 byte 最大10 byte

nvarchar(10) 内容：abcあ消費：4x2=8 byte 最大10 char(20 byte)

★ロック

SELECT * FROM table WITH(<lock>)

UPDATE table WITH(<lock>) SET col = 'xxx'

NOLOCK ロックが影響なし

HOLDLOCK

XLOCK 排他

UPDLOCK 更新

※UPDATE, DELETE, INSERTではNOLOCKが使えない

★多量のデータをINSERTする場合

データファイル（固定長）

20120101320.35260XXX

20120102179.81350YYY

データファイル（区切り）

20120101,320.35,260,XXX

20120102,179.81,350,YYY

フォーマットファイル

<?xml version="1.0"?>

<BCPFORMAT

xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<FIELD ID="1" xsi:type="CharFixed" LENGTH="8"/> 固定長

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=","/> 区切り

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

書き方１

BULK INSERT table

FROM 'C:\data.txt'

WITH(

FORMATFILE='C:\format.xml',

FIELDTERMINATOR=',',

ROWTERMINATOR='\r\n');

書き方２

INSERT INTO table

SELECT *

FROM OPENROWSET

(

BULK 'C:\data.txt',

FORMATFILE='C:\format.xml',

FIRSTROW='3' --3行目から

) AS tbl;

★システムテーブル

すべてのDB

SELECT name FROM SysDatabASes ORDER BY name

指定したテーブルのカラム名

SELECT name FROM SysColumns WHERE id = Object_Id(<table>)

DBにあるすべてのテーブル

SELECT name FROM SysObjects WHERE xtype = 'U' ORDER BY name

xtypeの種類

C = CHECK 制約

D = DEFAULT 制約

F = FOREIGN KEY 制約

L = ログ

FN = スカラ関数

IF = インラインテーブル関数

P = ストアドプロシージャ

PK = PRIMARY KEY 制約 (タイプ K)

RF = レプリケーションフィルタストアドプロシージャ

S = システムテーブル

TF = テーブル関数

TR = トリガ

U = ユーザーテーブル

UQ = UNIQUE 制約 (タイプ K)

V = ビュー

X = 拡張ストアドプロシージャ

★システム関数・変数

SELECT app_name();

SELECT current_timestamp; --現在時間

SELECT current_user;

SELECT host_id();

SELECT host_name();

SELECT db_name();

SELECT @@rowcount; --影響行数

SELECT @@error; --T-SQLのエラー番号

SELECT @@procid;

SELECT @@langId; --言語ID

SELECT @@language; --言語名称

SELECT @@max_connections; --最大接続数

SELECT @@connections; --接続数

SELECT @@servername; --ローカル

SELECT @@servicename;

SELECT @@version;

★主キーの作成方法

方法１

CREATE TABLE table(

id INT IDENTITY(1,2) NOT NULL PRIMARY KEY,

number VARCHAR(20) UNIQUE NOT NULL,

name VARCHAR(20) NOT NULL,

password VARCHAR(20) DEFAULT(123),

description VARCHAR(40) NULL

)

INSERT INTO table(number,name,description) VALUES('001','Andy','あああ')

INSERT INTO table(number,name,description) VALUES('002','Tom','いいい')

方法２

CREATE TABLE table(

id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,

number VARCHAR(20) UNIQUE NOT NULL,

name VARCHAR(20) NOT NULL,

password VARCHAR(20) DEFAULT(123),

description VARCHAR(40) NULL

)

INSERT INTO table(id,number,name,description) VALUES(NEWID(),'001','Andy','あああ')

INSERT INTO table(id,number,name,description) VALUES(NEWID(),'002','Tom','いいい')

★複数行を一行にする

SELECT

table.Category

SELECT

CAST(T.Value AS VARCHAR) + ','

FROM table AS T

WHERE T.Category = table.Category

FOR XML PATH('')

) AS Values

FROM table

GROUP BY Category

★挿入・更新を同時に対応

MERGE INTO table1

USING table2 --或いは USING (SELECT '001' id, 'Andy' name) table2

ON table1.id = table2.id

WHEN MATCHED THEN

UPDATE SET table1.name = table2.name

WHEN NOT MATCHED THEN

INSERT (table1.id, table1.name) VALUES ( table2.id, table2.name );