give IT a try

プログラミング、リモートワーク、田舎暮らし、音楽、etc.

SQL

僕たちは本当のSQLite3を何も知らない(柔軟なデータ型と外部キー制約の罠について)

「えっ、SQLite3ってこんな仕様なの!?」と最近ビックリしたことを紹介します。 たとえばこんな2つのテーブルがあったとします。 CREATE TABLE blogs ( id int primary key, title varchar(32) ); CREATE TABLE comments ( id int primary key, content var…

オンラインツールを使ってSQLを写経!?Software Design 2019年4月号にてSQLの入門記事を監修しました

お知らせ Software Design 2019年4月号(2019年3月18日発売)にて「プログラマのための速習SQL」という特集記事を監修しました。 ちなみに、この記事の執筆者はソニックガーデンの同僚である西見公宏(@mah_lab)です。記事の最後にはいちおう僕の名前も載せ…

SQLの問題を作ってQiitaに載せました

お知らせ 最初はこっちに書いてたんですが、Qiitaで公開した方が面白いかなと思って、こんな問題を投稿してみました。 【SQL腕試し問題!】締め切り日が近いイベントの一覧の取得するSQLを書いて下さいSQLが好きな方や、もっとSQLの腕を磨きたい方はチャレン…

OracleやSQL Serverでテーブルやストアドの一覧を取得する

たまに仕事で使う時があるので、自分用の備忘録として書いておきます。 なお、これらのSQLを発行する際は特別な権限が必要な場合があります。 SQL Serverでテーブルやストアドの一覧を取得する 目的のデータベースにて以下のSQLを実行します。 typeが略称に…

SQL Serverに.NETで接続しようとすると1.2秒でタイムアウトする場合がある

SQL

ちょっと前に発生していたトラブルの情報共有です。 検証用にリリースした社内アプリケーションが頻繁にタイムアウトエラーを起こすというトラブルが先日発生しました。 .NETから社内のSQL Serverに接続していたのですが、デフォルトのタイムアウト秒数は15…

Splitっぽい処理をSQLで実現する

普通のプログラミング言語だと簡単に実装できるのに、SQLだとなんか作りにくいぞ?というこんな問題がありました。 特定の文字(今回はドット".")で、一つの文字列を3つのカラムに分割する ただし、文字列に含まれるドットの数は決まっていない。(0個〜無制限…

割り算の結果に要注意・・・!!

不思議な現象? さて、いきなり問題です。 「6÷5」はいくつでしょうか?? ・・・ほとんどの人は「1.2」と答えるのではないでしょうか? では以下のSQLの実行結果はいくつになるでしょうか? select 6 / 5 ・・・実は「1」になります。 プログラミング言語に…

SQLとの付き合い方

SQL

はじめに 先日、会社のメンバーから「SQLのJOIN文を視覚的に理解する」というサイトを紹介されました。 SQLのJOIN文を視覚的に理解する | IDEA*IDEA おいらも見てみたのですが、「自分がSQLを書いたり読んだりする時はこんなベン図を思い浮かべることはまず…

SqlServerで指定可能な日付の範囲について

SQL

SqlServerの日付型は"1753/1/1"から"9999/12/31"までしかサポートしていないようです。 よって、"1752/12/31"のような日付は論理的には正しくても、SqlServerに渡すとエラーが発生します。 UIで入力値検証(フォーマットチェック)をパスすれば安全、とは限ら…

ビジネスロジックをストアドに埋め込むのは避けるべき

SQL

ストアドの中のビジネスロジックは読みにくいです。 いかんせん、ベースの言語がSQLなので言語設計の時点ですでに構造化プログラミングをしようとすることに無理があります。 構造化プログラミング(手続き型ロジック)の書きやすさ、読みやすさ、デバッグのし…

複雑なレポーティングクエリをUNIONを使ってリファクタリングする

SQL

現在かなり複雑なレポーティングクエリを構築しています。 一貫性のない形式で保存されているデータをSQLできれいに整形して出力するレポートなのですが、データ形式のパターンが10種類ぐらいあるんです。 最初にパターン1向けのクエリを作って、それからパ…

プロとしてのSQLチューニング入門

SQL

Amazonにレビューを追加しました。 プロとしてのSQLチューニング入門作者: 福田武志出版社/メーカー: ソフトバンク クリエイティブ発売日: 2007/03/28メディア: 単行本購入: 4人 クリック: 108回この商品を含むブログ (18件) を見る 数億から100億ぐらいある…

SQL Antipatterns

アメリカのオライリーでこんな本が発売されるようです。 SQL Antipatterns: Avoiding the Pitfalls of Database Programming (Pragmatic Programmers)作者: Bill Karwin出版社/メーカー: Pragmatic Bookshelf発売日: 2010/07/05メディア: ペーパーバック購入…

44のアンチパターンに学ぶDBシステム

先日購入した本のうち一冊を読み終わりました。 44のアンチパターンに学ぶDBシステム (DB Magazine SELECTION)作者: 小田圭二出版社/メーカー: 翔泳社発売日: 2009/11/28メディア: 単行本(ソフトカバー)購入: 14人 クリック: 123回この商品を含むブログ (2…

データベース内のストアドのソースコードを取得するSQL

SQL Server用のSQLです。 SELECT SPECIFIC_NAME AS NAME, ROUTINE_DEFINITION AS SRC FROM INFORMATION_SCHEMA.ROUTINES ORDER BY SPECIFIC_NAME

ストアドの実行権限を調べるストアド

こんな方法でチェックできるようなのでメモメモ... EXEC sp_helprotect '(ストアド名)', @permissionarea='o' 実行結果 Owner Object Grantee Grantor ProtectType Action Column dbo (ストアド名) (権限が付与されているロール) dbo Grant Execute . dbo (…

DISTINCTされた結果の件数を求めるSQL

DISTINCTされた結果の件数、つまり種類の数を求めるSQLは以下のようになる。 ※サンプルコードはSQL Serverの場合 create table #temp( col1 int, col2 varchar(2) ) insert into #temp values (1, 'a') insert into #temp values (1, 'a') insert into #temp…

SQL Server 2005とXMLの連携

SQL

データベースにXML型で保存されているデータを通常のテーブルのように行と列で取得する方法 http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx => 「Example: Extract Properties from XML Variable」を参照 XMLにネームスペースがついている…

累積値を求めるSQL

SQL Server用のロジックとして書くとこんな感じ。 クエリの構造自体は他のRDBMSでも使えるはず。 SQL create table #a (key_col int, val int) insert into #a values (1, 10) insert into #a values (2, 10) insert into #a values (3, 30) insert into #a …

T-SQLで文字列をURLエンコードするロジック

This is test. !"#$%&''()-=^~\|@`[{;+:*]},/?\_ ↑こんなのが %54%68%69%73%20%69%73%20%74%65%73%74%2e%20%21%22%23%24%25%26%27%28%29%2d%3d%5e%7e%5c%7c%40%60%5b%7b%3b%2b%3a%2a%5d%7d%2c%3c%2e%3e%2f%3f%5c%5f ↑こんな感じに変換されます。 ただし制限…

SSISからExcelを読み込む方法

技術メモです。 SSIS(SQL Server Integration Services)でExcelを読み込む場合、クライアントではうまく動作してもサーバーにデプロイすると、エラーが出て動作しない場合があります。 そんなときは以下のような問題があるかも。 64bit版SQL Serverではうま…