{"id":2703,"date":"2018-07-08T23:31:31","date_gmt":"2018-07-08T14:31:31","guid":{"rendered":"http:\/\/1bed.allright.life\/?p=2703"},"modified":"2020-04-18T19:36:42","modified_gmt":"2020-04-18T10:36:42","slug":"post-2703","status":"publish","type":"post","link":"https:\/\/1bed.allright.life\/?p=2703","title":{"rendered":"SQL Server \u306e\u8a08\u7b97\u5217\u306b\u52a9\u3051\u3089\u308c\u305f\u304c\u3001\u5b9f\u884c\u901f\u5ea6\u304c\u6c17\u306b\u306a\u3063\u305f\u306e\u3067\u691c\u8a3c"},"content":{"rendered":"<h2>\u7d4c\u7def<\/h2>\n<p>SharePoint \u306e 1 \u884c\u30c6\u30ad\u30b9\u30c8\u306b\u3042\u308b\u300c\u30b3\u30fc\u30c9\u5217\u300d\u3068\u3044\u3046\u306e\u304c\u3042\u308a\u3001\u30aa\u30f3\u30d7\u30ec\u306e\u30b7\u30b9\u30c6\u30e0\u306b\u30d0\u30c3\u30c1\u3067\u540c\u671f\u3092\u53d6\u3063\u3066\u3044\u308b\u30b7\u30b9\u30c6\u30e0\u304c\u3042\u3063\u305f\u3068\u3057\u307e\u3059\u3002<\/p>\n<p>\u7d0d\u54c1\u65e5\u306b\u8a00\u308f\u308c\u307e\u3057\u305f\u3002<br \/>\n\u300c\u30b3\u30fc\u30c9\u5217\u306e\u4e26\u3073\u9806\u306f\u6570\u5b57\u9806\u306b\u3057\u3066\u304f\u3060\u3055\u3044\u300d<\/p>\n<p>\u4ffa\u300c\u305d\u306e\u30d5\u30a3\u30fc\u30eb\u30c9\u306f 255 \u6587\u5b57\u306e 1 \u884c\u30c6\u30ad\u30b9\u30c8\u306a\u306e\u3067\u3001\u6570\u5024\u578b\u4ee5\u5916\u304c\u5165\u308b\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002\u300d<br \/>\n\u304a\u5ba2\u69d8\u300c\u3093\u30fc \u305d\u308c\u306f\u4e26\u3070\u306a\u304f\u3066\u3044\u3044\u304b\u3089\u3001\u6570\u5b57\u306e\u3060\u3051\u4e26\u3079\u3066\u304f\u3060\u3055\u3044\u300d<\/p>\n<p>(\u00b4\uff1b\u03c9\uff1b\uff40)\uff73\uff6f\u2026<\/p>\n<p>\u4eca\u66f4\u3001\u540c\u671f\u30d0\u30c3\u30c1\u306b\u624b\u3092\u5165\u308c\u308b\u8a33\u306b\u3082\u3044\u304b\u306a\u304b\u3063\u305f\u306e\u3067\u3001SQL Server \u306e\u8a08\u7b97\u5217\u3092\u4f5c\u308a\u3001ORDER BY \u53e5\u3060\u3051\u5909\u66f4\u3059\u308b\u4e8b\u3067\u9003\u3052\u5207\u308c\u307e\u3057\u305f\u3002<\/p>\n<p>\u305d\u306e\u6642\u306b\u6c17\u306b\u306a\u3063\u305f\u306e\u306f\u3001\u6b21\u306e 3 \u30d1\u30bf\u30fc\u30f3\u3067\u5b9f\u884c\u901f\u5ea6\u304c\u3069\u306e\u7a0b\u5ea6\u5909\u308f\u3063\u305f\u306e\u3060\u308d\u3046\uff1f\u3068\u3044\u3046\u3053\u3068\u3002<\/p>\n<ul>\n<li>ORDER BY \u53e5\u3067 CAST \u3059\u308b<\/li>\n<li>\u8a08\u7b97\u5217\u306e\u5024\u3092\u7269\u7406\u7684\u306b\u4fdd\u5b58\u3057\u306a\u3044\u4eee\u60f3\u5217\u3067\u4f5c\u308b<\/li>\n<li>\u8a08\u7b97\u5217\u306e\u5024\u3092\u7269\u7406\u7684\u306b\u4fdd\u5b58\u3059\u308b PRESISTED \u3092\u4ed8\u3051\u308b<\/li>\n<\/ul>\n<h2>\u305d\u3082\u305d\u3082\u8a08\u7b97\u5217\u3068\u306f\uff1f<\/h2>\n<p>Docs \u306b\u306f\u6b21\u306e\u8aac\u660e\u304c\u3042\u308a\u307e\u3057\u305f\u3002<\/p>\n<blockquote>\n<p>\u8a08\u7b97\u5217\u306e\u5f0f\u306f\u3001\u4ed6\u306e\u5217\u306e\u30c7\u30fc\u30bf\u3092\u4f7f\u7528\u3057\u3066\u5024\u3092\u8a08\u7b97\u3057\u3001\u305d\u308c\u3092\u81ea\u8eab\u306e\u5217\u306b\u683c\u7d0d\u3057\u307e\u3059\u3002<\/p>\n<\/blockquote>\n<h2>\u691c\u8a3c\u74b0\u5883<\/h2>\n<p>\u691c\u8a3c\u3067\u4f7f\u3063\u305f\u306e\u306f SQL Server 2017 (64bit)\u3067\u3001\u691c\u8a3c\u7528\u30c6\u30fc\u30d6\u30eb\u306f\u6b21\u306e\u901a\u308a\u3002<\/p>\n<pre><code class=\"language-sql\">CREATE TABLE [dbo].[dummy](\n    [CODE] [nvarchar](50) NOT NULL\n) ON [PRIMARY]\nGO<\/code><\/pre>\n<p>\u691c\u8a3c\u7528\u30c7\u30fc\u30bf\u3068\u3057\u3066\u6b21\u306e\u30c7\u30fc\u30bf\u3092\u4f5c\u6210\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<ul>\n<li>CODE \u5217\u306b\u3001TEST00 \uff5e TEST09 \u3092\u767b\u9332<\/li>\n<li>CODE \u5217\u306b\u3001000010 \uff5e 999999 \u3092\u30e9\u30f3\u30c0\u30e0\u306b\u767b\u9332<\/li>\n<\/ul>\n<p>\u691c\u8a3c\u7528\u8a08\u7b97\u5217\u3092\u3064\u3051\u307e\u3059\u3002<\/p>\n<pre><code class=\"language-sql\">ALTER TABLE [dbo].[dummy] ADD [INTCODE] AS TRY_CAST(CODE AS int);\nALTER TABLE [dbo].[dummy] ADD [INTCODE_PERSISTED] AS TRY_CAST(CODE AS int) PERSISTED;<\/code><\/pre>\n<p>PERSISTED \u3092\u4ed8\u3051\u308b\u3068\u7269\u7406\u7684\u306b\u30c7\u30fc\u30bf\u3092\u4fdd\u5b58\u3057\u307e\u3059\u3002<br \/>\n\u304b\u304b\u3063\u305f\u6642\u9593\u306f\u3001\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u304c\u5165\u3063\u3066\u3044\u308b\u72b6\u614b\u3067 3 \u79d2\u307b\u3069\u3067\u3057\u305f\u3002<\/p>\n<h2>\u691c\u8a3c<\/h2>\n<p>SQL Server Management Studio \u3067\u5b9f\u884c<\/p>\n<h3>\u8a08\u6e2c\u3067\u4f7f\u3063\u305f SQL \u6587<\/h3>\n<pre><code class=\"language-sql\">DECLARE @StartTime datetime\nDECLARE @EndTime datetime\n\n-- \u30ad\u30e3\u30c3\u30b7\u30e5\u306e\u30af\u30ea\u30a2\nDBCC DROPCLEANBUFFERS\nDBCC FREEPROCCACHE \n\nSELECT @StartTime=GETDATE()\n\n-- \u3053\u3053\u306b\u691c\u8a3c\u3059\u308b SQL \u6587\n\nSELECT @EndTime=GETDATE()\nSELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs] <\/code><\/pre>\n<h3>\u691c\u8a3c\u3059\u308b SQL \u6587<\/h3>\n<ul>\n<li>\n<p>ORDER BY \u3059\u308b\u6642\u306b CAST \u3057\u3066\u30bd\u30fc\u30c8<\/p>\n<pre><code class=\"language-sql\">SELECT [CODE] FROM [test].[dbo].[dummy] ORDER BY TRY_CAST(CODE AS int);<\/code><\/pre>\n<\/li>\n<li>\n<p>\u8a08\u7b97\u5217\u306e\u5024\u3092\u7269\u7406\u7684\u306b\u4fdd\u5b58\u3057\u306a\u3044\u4eee\u60f3\u5217\u3067\u30bd\u30fc\u30c8<\/p>\n<pre><code class=\"language-sql\">SELECT [CODE] FROM [test].[dbo].[dummy] ORDER BY [INTCODE];<\/code><\/pre>\n<\/li>\n<li>\n<p>\u8a08\u7b97\u5217\u306e\u5024\u3092\u7269\u7406\u7684\u306b\u4fdd\u5b58\u3059\u308b PRESISTED \u3092\u3064\u3051\u305f\u5217\u3067\u30bd\u30fc\u30c8<\/p>\n<pre><code class=\"language-sql\">SELECT [CODE] FROM [test].[dbo].[dummy] ORDER BY [INTCODE_PERSISTED];<\/code><\/pre>\n<\/li>\n<\/ul>\n<h3>\u691c\u8a3c\u7d50\u679c<\/h3>\n<table>\n<thead>\n<tr>\n<th>\u691c\u8a3c\u5185\u5bb9<\/th>\n<th>\u304b\u304b\u3063\u305f\u6642\u9593(\u30df\u30ea\u79d2)<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>ORDER BY \u3059\u308b\u6642\u306b CAST \u3057\u3066\u30bd\u30fc\u30c8<\/td>\n<td>7606<\/td>\n<\/tr>\n<tr>\n<td>\u8a08\u7b97\u5217\u306e\u5024\u3092\u7269\u7406\u7684\u306b\u4fdd\u5b58\u3057\u306a\u3044\u4eee\u60f3\u5217\u3067\u30bd\u30fc\u30c8<\/td>\n<td>7624<\/td>\n<\/tr>\n<tr>\n<td>\u8a08\u7b97\u5217\u306e\u5024\u3092\u7269\u7406\u7684\u306b\u4fdd\u5b58\u3059\u308b PRESISTED \u3092\u3064\u3051\u305f\u5217\u3067\u30bd\u30fc\u30c8<\/td>\n<td>7300<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u8a08\u7b97\u5217\u306b\u306f\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3082\u5f35\u308c\u308b\u3088\u3046\u306a\u306e\u3067\u3001\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3082\u3064\u3051\u3066\u518d\u5ea6\u691c\u8a3c\u3057\u3066\u307f\u308b<\/p>\n<ul>\n<li>\n<p>\u4f5c\u6210\u3057\u305f\u30a4\u30f3\u30c7\u30c3\u30af\u30b9<\/p>\n<pre><code class=\"language-sql\">CREATE NONCLUSTERED INDEX IX_INTCODE ON [dbo].[dummy]([INTCODE]);\nCREATE NONCLUSTERED INDEX IX_INTCODE_PERSISTED ON [dbo].[dummy]([INTCODE_PERSISTED]);<\/code><\/pre>\n<\/li>\n<li>\n<p>\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3092\u3064\u3051\u3066\u691c\u8a3c<\/p>\n<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th>\u691c\u8a3c\u5185\u5bb9<\/th>\n<th>\u304b\u304b\u3063\u305f\u6642\u9593(\u30df\u30ea\u79d2)<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\u8a08\u7b97\u5217\u306e\u5024\u3092\u7269\u7406\u7684\u306b\u4fdd\u5b58\u3057\u306a\u3044\u4eee\u60f3\u5217\u3067\u30bd\u30fc\u30c8<\/td>\n<td>7424<\/td>\n<\/tr>\n<tr>\n<td>\u8a08\u7b97\u5217\u306e\u5024\u3092\u7269\u7406\u7684\u306b\u4fdd\u5b58\u3059\u308b PRESISTED \u3092\u3064\u3051\u305f\u5217\u3067\u30bd\u30fc\u30c8<\/td>\n<td>7316<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>\u8003\u5bdf<\/h2>\n<p>\u6570\u5b57\u3092\u898b\u3066\u3069\u3061\u3089\u3067\u3082\u826f\u304b\u3063\u305f\u3093\u3060\u306a\u3068\u5b89\u5fc3\u3002<br \/>\n\u4eca\u56de\u306f\u540c\u671f\u30d0\u30c3\u30c1\u3092\u4fee\u6b63\u305b\u305a\u3001\u30d7\u30ed\u30b0\u30e9\u30e0\u5074\u3082\u6700\u5c0f\u9650\u306e\u5909\u66f4\u306b\u6291\u3048\u305f\u304b\u3063\u305f\u306e\u3067\u3001\u8a08\u7b97\u5217\u3092\u4f7f\u3063\u3066\u826f\u304b\u3063\u305f\u3068\u4eca\u3067\u3082\u601d\u3063\u3066\u3044\u308b\u3002<\/p>\n<p>\u5411\u3044\u3066\u3044\u308b\u51e6\u7406\u3068\u3057\u3066\u306f\u3001\u5404\u7a2e\u7a0e\u306e\u8a08\u7b97\u3092\u8a08\u7b97\u5217\u3067\u3084\u308b\u3068\u304b\u306f\u30a2\u30ea\u304b\u3082\u3068\u601d\u3063\u305f\u3002<\/p>\n<h2>\u53c2\u8003<\/h2>\n<ul>\n<li><a href=\"https:\/\/docs.microsoft.com\/ja-jp\/sql\/relational-databases\/tables\/specify-computed-columns-in-a-table?view=sql-server-2017\">\u30c6\u30fc\u30d6\u30eb\u306e\u8a08\u7b97\u5217\u306e\u6307\u5b9a<\/a><\/li>\n<li><a href=\"https:\/\/docs.microsoft.com\/ja-jp\/sql\/t-sql\/functions\/try-cast-transact-sql?view=sql-server-2017\">TRY_CAST (Transact-SQL)<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u7d4c\u7def SharePoint \u306e 1 \u884c\u30c6\u30ad\u30b9\u30c8\u306b\u3042\u308b\u300c\u30b3\u30fc\u30c9\u5217\u300d\u3068\u3044\u3046\u306e\u304c\u3042\u308a\u3001\u30aa\u30f3\u30d7\u30ec\u306e\u30b7\u30b9\u30c6\u30e0\u306b\u30d0\u30c3\u30c1\u3067\u540c\u671f\u3092\u53d6\u3063\u3066\u3044\u308b\u30b7\u30b9\u30c6\u30e0\u304c\u3042\u3063\u305f\u3068\u3057\u307e\u3059\u3002 \u7d0d\u54c1\u65e5\u306b\u8a00\u308f\u308c\u307e\u3057\u305f\u3002 \u300c\u30b3\u30fc\u30c9\u5217\u306e\u4e26\u3073\u9806\u306f\u6570\u5b57\u9806\u306b\u3057\u3066\u304f\u3060\u3055\u3044\u300d  [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49],"tags":[117],"class_list":["post-2703","post","type-post","status-publish","format-standard","hentry","category-database","tag-sqlserver"],"_links":{"self":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts\/2703","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2703"}],"version-history":[{"count":2,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts\/2703\/revisions"}],"predecessor-version":[{"id":3107,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts\/2703\/revisions\/3107"}],"wp:attachment":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2703"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2703"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2703"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}