読者です 読者をやめる 読者になる 読者になる

列ストアインデックスのメタ情報を取るクエリのメモ

幾つかメモ。

行グループ

select 
  object_name(object_id) as table_name
  , * 
from 
  sys.column_store_row_groups
where 
  OBJECT_NAME(object_id) = 'table_name'

sys.column_store_row_groups (Transact-SQL)

列セグメント

select
  object_name(p.object_id) as table_name
  , c.name as column_name
  , css.*
from
  sys.column_store_segments css
  left join sys.partitions p on p.hobt_id = css.hobt_id and p.partition_id = css.partition_id
  left join sys.indexes ind on p.index_id = ind.index_id and p.object_id = ind.object_id
  left join sys.index_columns indc on indc.object_id = ind.object_id and indc.index_id = ind.index_id and indc.index_column_id = css.column_id
  left join sys.columns c on c.column_id = indc.column_id and c.object_id = p.object_id
where
  object_name(p.object_id) = 'table_name'

sys.column_store_segments (Transact-SQL)

デルタストア、削除済ビットマップ、削除バッファーとか

select 
  object_name(object_id) as table_name 
  , * 
from sys.internal_partitions
where
  object_name(object_id) = 'table_name'

sys.internal_partitions (TRANSACT-SQL)

三木会で列ストアインデックスのお話しをしました

[2017/03/16(木): 三木会] SQL Server の 列ストアインデックス 入門 | Insight Technology, Inc. で、列ストアインデックス 入門のお話しをしてきました。
前半は列ストアインデックスとはどんなものかと、今までのテーブルと何が違うのかの概要で、後半は更新処理や検索処理の中の動きをデモを中心にお話ししました。

スライドはこちら

この勉強会で話すために、久しぶりに列ストアを調べましたが 2014 から 2016 で結構変わっててへーって感じでした。

調べた内容はちょくちょくブログに書いていこうと思います。

3/16(木) 三木会 で 列ストアインデックスのお話しします

3/16(木) Insight Technology 様が主催している 三木会 で SQL Server の列ストアインデックス のお話しをさせていただきます。
[2017/03/16(木): 三木会] SQL Server の 列ストアインデックス 入門 | Insight Technology, Inc.
内容は 列ストアって何?ってところから順に掘り下げていく予定です。
梅田のグランフロントで開催しますのでお近くの方は是非ご参加を~。

Application Insights JavaScript browserTimings の メモ

networkDuration / sendDuration / receivedDuration / prosessiongDuration とかって何の時間かっての調べた時のメモ。
ApplicationInsights-JS/PageViewPerformance.ts at 4620cfdc47975f2e32e82f63c693f9b88fa50568 · Microsoft/ApplicationInsights-JS · GitHub

ソースのコメント見たかんじ、本家はここなんかな?
Navigation Timing

Japanese_XJIS_140 って _SC 指定出来ないけど、どないなん?

SQL Server 2012 で追加された照合順序で何ができるようになった? at SE の雑記

の検証クエリを拝借しました。

CREATE TABLE dbo.TblTest
(
Col1 nvarchar(50) COLLATE Japanese_XJIS_100_CI_AS NULL,
Col2 nvarchar(50) COLLATE Japanese_XJIS_100_CI_AS_SC NULL,
Col3 nvarchar(50) COLLATE Japanese_XJIS_140_CI_AS NULL
)
GO

INSERT INTO TblTest VALUES(N'森鷗外𠮟る', N'森鷗外𠮟る', N'森鷗外𠮟る')
INSERT INTO TblTest VALUES(N'森鴎外叱る', N'森鴎外叱る', N'森鴎外叱る')

SELECT LEFT(Col1, 4), LEFT(Col2, 4), LEFT(Col3, 4),* FROM TblTest
SELECT * FROM TblTest WHERE Col1 LIKE N'森鷗外_る'
SELECT * FROM TblTest WHERE Col2 LIKE N'森鷗外_る'
SELECT * FROM TblTest WHERE Col3 LIKE N'森鷗外_る'

_SC ありの時と同じ動作のように見えますね。

照合順序に KI、WI はないですよー

照合順序について調べてて、KI、WI について書いてるページがあったので。
正しくは、省略して何も指定しないです。

例:列の照合順序を変える

-- 全部区別する場合
alter table t alter column col char(10) collate JAPANESE_XJIS_140_CS_AS_KS_WS
-- 全部区別しない場合
alter table t alter column col char(10) collate JAPANESE_XJIS_140_CI_AI

SQL Server の照合順序 - マイクロソフト系技術情報 Wiki
このサイトが参考にしているサイトが
照合順序 – 文字の比較と並び順 (その 1) – Microsoft SQL Server Japan Support Team Blog
でした。
Team Blog じゃないですかーということで、コメントで連絡してみました。
そのうち直るはず。

公式はこちらで。
照合順序と Unicode のサポート

vNext と SQL DB(v12) で、Japanese_XJIS_140 が使えるようになってるけど、_SC 指定できない。
どっちの動作になってるのかな?後で試す。

OUTPUT 句で FK 付テーブルに INTO する時のメモ

OUTPUT 句 (Transact-SQL)
今日気付いたのですが、INTO 先のテーブルには FOREIGN KEY 付いてるとダメという制約がありました。

幾つかの回避方法があると思いますが、ここでは テーブル変数 に退避してからという方法のメモです。
※FK を一旦無効にしてから OUTPUT で INTO したあと再度 FK 有効にするってやり方もあります。

対処方法のメモ

create table [ユーザー] (
    [ID] int not null primary key, 
    [名前] nvarchar(10) not null 
)
create table [通知] (
    [ID] int not null primary key, 
    [種類] nvarchar(10) not null, 
    [内容] nvarchar(100)
)
create table [ユーザーへの通常通知] (
    [ユーザーID] int not null references [ユーザー]([ID]), 
    [通知ID] int not null references [通知]([ID]), 
    [日時] datetime2(0) not null, 
    primary key([ユーザーID], [通知ID])
)

insert into [ユーザー] ([ID], [名前])
values (1, N'一郎'), (2, N'二郎'), (3, N'三郎')

insert into [通知] ([ID], [種類], [内容])
values (1, N'定期', N'一郎さん。お元気ですか?')
, (2, N'定期', N'二郎さん。お元気ですか?')
, (3, N'定期', N'三郎さん。お元気ですか?')
, (4, N'アラート', N'三郎さん。不明な端末でログイン試行されました。')
, (5, N'セキュリティ', N'三郎さん。パスワードリセット要求。')
, (6, N'アラート', N'一郎さん。アカウントロックされました。')

insert into [ユーザーへの通常通知] ([ユーザーID], [通知ID], [日時])
values (1, 1, '2017-02-01 12:34:56')
, (2, 2, '2017-02-02 20:02:01')
, (3, 3, '2017-02-03 02:11:48')
, (3, 4, '2017-02-07 19:02:32')
, (3, 5, '2017-02-07 19:09:51')
, (1, 6, '2017-02-08 12:45:20')

select * from [ユーザー]
select * from [通知]
select * from [ユーザーへの通常通知]


ってのがあったときに、アラート通知の紐づけは別テーブルにしよう!と変更したとします。
テーブルを作って、

create table [ユーザーへのアラート通知] (
    [ユーザーID] int not null references [ユーザー]([ID]), 
    [通知ID] int not null references [通知]([ID]), 
    [日時] datetime2(0) not null, 
    primary key([ユーザーID], [通知ID])
)

データを移行します。

delete from [ユーザーへの通常通知]
output deleted.* into [ユーザーへのアラート通知] -- 列名は同じ順序なので省略
where exists (
    select * from [通知]
    where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
      and [通知].[種類] = N'アラート'
)

これがエラーになります。

メッセージ 332、レベル 16、状態 1、行 99
OUTPUT INTO 句の対象のテーブル 'ユーザーへのアラート通知' を、(主キー、外部キー) リレーションシップのどちらにも配置できません。参照制約 'FK__ユーザーへのアラー__ユーザーI__6477ECF3' が見つかりました。

FK があるとダメーと言われました。
というわけで、一旦テーブル変数に INTO してから移行します。

declare @t table (
    [ユーザーID] int not null, 
    [通知ID] int not null, 
    [日時] datetime2(0) not null
)
delete from [ユーザーへの通常通知]
output deleted.* into @t -- 列名は同じ順序なので省略
where exists (
    select * from [通知]
    where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
      and [通知].[種類] = N'アラート'
)
insert into [ユーザーへのアラート通知]
select * from @t

select * from [ユーザーへの通常通知]
select * from [ユーザーへのアラート通知]

出来上がり!

クエリ全文

create table [ユーザー] (
    [ID] int not null primary key, 
    [名前] nvarchar(10) not null 
)
create table [通知] (
    [ID] int not null primary key, 
    [種類] nvarchar(10) not null, 
    [内容] nvarchar(100)
)
create table [ユーザーへの通常通知] (
    [ユーザーID] int not null references [ユーザー]([ID]), 
    [通知ID] int not null references [通知]([ID]), 
    [日時] datetime2(0) not null, 
    primary key([ユーザーID], [通知ID])
)

insert into [ユーザー] ([ID], [名前])
values (1, N'一郎'), (2, N'二郎'), (3, N'三郎')

insert into [通知] ([ID], [種類], [内容])
values (1, N'定期', N'一郎さん。お元気ですか?')
, (2, N'定期', N'二郎さん。お元気ですか?')
, (3, N'定期', N'三郎さん。お元気ですか?')
, (4, N'アラート', N'三郎さん。不明な端末でログイン試行されました。')
, (5, N'セキュリティ', N'三郎さん。パスワードリセット要求。')
, (6, N'アラート', N'一郎さん。アカウントロックされました。')

insert into [ユーザーへの通常通知] ([ユーザーID], [通知ID], [日時])
values (1, 1, '2017-02-01 12:34:56')
, (2, 2, '2017-02-02 20:02:01')
, (3, 3, '2017-02-03 02:11:48')
, (3, 4, '2017-02-07 19:02:32')
, (3, 5, '2017-02-07 19:09:51')
, (1, 6, '2017-02-08 12:45:20')

select * from [ユーザー]
select * from [通知]
select * from [ユーザーへの通常通知]
go

create table [ユーザーへのアラート通知] (
    [ユーザーID] int not null references [ユーザー]([ID]), 
    [通知ID] int not null references [通知]([ID]), 
    [日時] datetime2(0) not null, 
    primary key([ユーザーID], [通知ID])
)
go

-- エラーになる!
delete from [ユーザーへの通常通知]
output deleted.* into [ユーザーへのアラート通知] -- 列名は同じ順序なので省略
where exists (
    select * from [通知]
    where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
      and [通知].[種類] = N'アラート'
)
go

declare @t table (
    [ユーザーID] int not null, 
    [通知ID] int not null, 
    [日時] datetime2(0) not null
)
delete from [ユーザーへの通常通知]
output deleted.* into @t -- 列名は同じ順序なので省略
where exists (
    select * from [通知]
    where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
      and [通知].[種類] = N'アラート'
)
insert into [ユーザーへのアラート通知]
select * from @t

select * from [ユーザーへの通常通知]
select * from [ユーザーへのアラート通知]
go

-- 後片付け
drop table [ユーザーへのアラート通知]
drop table [ユーザーへの通常通知]
drop table [通知]
drop table [ユーザー]