{"id":476326,"date":"2023-08-09T07:28:31","date_gmt":"2023-08-09T07:28:31","guid":{"rendered":""},"modified":"2023-09-05T11:12:28","modified_gmt":"2023-09-05T11:12:28","slug":"columnstore-indexes-in-sql","status":"publish","type":"wiki","link":"https:\/\/oneproxy.pro\/jp\/wiki\/columnstore-indexes-in-sql\/","title":{"rendered":"SQL \u306e\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9"},"content":{"rendered":"<h2>\u5c0e\u5165<\/h2>\n<p>SQL \u306e\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306f\u3001\u7279\u5b9a\u306e\u30b7\u30ca\u30ea\u30aa\u3067\u30af\u30a8\u30ea \u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u3068\u30c7\u30fc\u30bf\u5727\u7e2e\u3092\u5927\u5e45\u306b\u5411\u4e0a\u3067\u304d\u308b\u7279\u6b8a\u306a\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u6a5f\u80fd\u3067\u3059\u3002\u30c7\u30fc\u30bf \u30a6\u30a7\u30a2\u30cf\u30a6\u30b9\u3084\u5206\u6790\u30ef\u30fc\u30af\u30ed\u30fc\u30c9\u3067\u5927\u91cf\u306e\u30c7\u30fc\u30bf\u3092\u51e6\u7406\u3059\u308b\u969b\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u3068\u30b9\u30c8\u30ec\u30fc\u30b8\u306e\u8ab2\u984c\u306b\u5bfe\u51e6\u3059\u308b\u305f\u3081\u306b\u8a2d\u8a08\u3055\u308c\u307e\u3057\u305f\u3002\u3053\u306e\u8a18\u4e8b\u3067\u306f\u3001SQL \u306e\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u6b74\u53f2\u3001\u5185\u90e8\u69cb\u9020\u3001\u4e3b\u306a\u6a5f\u80fd\u3001\u7a2e\u985e\u3001\u4f7f\u7528\u6cd5\u3001\u5c06\u6765\u306e\u5c55\u671b\u306b\u3064\u3044\u3066\u8a73\u3057\u304f\u8aac\u660e\u3057\u307e\u3059\u3002<\/p>\n<h2>\u6b74\u53f2\u3068\u8d77\u6e90<\/h2>\n<p>SQL \u306e\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306f\u3001SQL Server 2012 \u306e\u30ea\u30ea\u30fc\u30b9\u3067 Microsoft \u306b\u3088\u3063\u3066\u521d\u3081\u3066\u5c0e\u5165\u3055\u308c\u307e\u3057\u305f\u3002\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u57fa\u76e4\u3068\u306a\u308b\u5217\u30b9\u30c8\u30ec\u30fc\u30b8\u306e\u6982\u5ff5\u306f\u30011970 \u5e74\u4ee3\u306b\u9061\u308a\u307e\u3059\u3002\u3057\u304b\u3057\u3001\u30d3\u30c3\u30b0 \u30c7\u30fc\u30bf\u306e\u53f0\u982d\u3068\u3001\u3088\u308a\u512a\u308c\u305f\u30c7\u30fc\u30bf\u5727\u7e2e\u3068\u30af\u30a8\u30ea \u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u306e\u5fc5\u8981\u6027\u306b\u3088\u308a\u30012000 \u5e74\u4ee3\u534a\u3070\u306b\u4eba\u6c17\u304c\u9ad8\u307e\u308a\u307e\u3057\u305f\u3002Microsoft \u306b\u3088\u308b\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u5b9f\u88c5\u306f\u3001\u3053\u306e\u5206\u91ce\u3067\u5927\u304d\u306a\u9032\u6b69\u3092\u793a\u3057\u3001\u591a\u304f\u306e\u6700\u65b0\u306e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u7ba1\u7406\u30b7\u30b9\u30c6\u30e0\u306e\u6a19\u6e96\u6a5f\u80fd\u3068\u306a\u308a\u307e\u3057\u305f\u3002<\/p>\n<h2>SQL \u306e\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306b\u95a2\u3059\u308b\u8a73\u7d30\u60c5\u5831<\/h2>\n<p>\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306f\u3001\u5f93\u6765\u306e\u884c\u30d9\u30fc\u30b9\u306e\u30b9\u30c8\u30ec\u30fc\u30b8\u3067\u306f\u306a\u304f\u3001\u5217\u3054\u3068\u306b\u30c7\u30fc\u30bf\u3092\u6574\u7406\u3057\u3066\u4fdd\u5b58\u3059\u308b\u30c6\u30af\u30ce\u30ed\u30b8\u3067\u3059\u3002\u884c\u30d9\u30fc\u30b9\u306e\u30b9\u30c8\u30ec\u30fc\u30b8\u3067\u306f\u3001\u30c6\u30fc\u30d6\u30eb\u5185\u306e\u30c7\u30fc\u30bf\u306f\u884c\u3054\u3068\u306b\u4fdd\u5b58\u304a\u3088\u3073\u53d6\u5f97\u3055\u308c\u307e\u3059\u3002\u4e00\u65b9\u3001\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3067\u306f\u3001\u5404\u5217\u5185\u306e\u30c7\u30fc\u30bf\u304c\u307e\u3068\u3081\u3066\u4fdd\u5b58\u304a\u3088\u3073\u51e6\u7406\u3055\u308c\u308b\u305f\u3081\u3001\u5727\u7e2e\u304c\u5411\u4e0a\u3057\u3001\u5206\u6790\u30af\u30a8\u30ea\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u304c\u5411\u4e0a\u3057\u307e\u3059\u3002<\/p>\n<p>\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306f\u3001\u5927\u91cf\u306e\u30c7\u30fc\u30bf\u3068\u96c6\u8a08\u3092\u542b\u3080\u30af\u30a8\u30ea\u3092\u5b9f\u884c\u3059\u308b\u8aad\u307f\u53d6\u308a\u96c6\u4e2d\u578b\u306e\u30ef\u30fc\u30af\u30ed\u30fc\u30c9\u306b\u9069\u3057\u3066\u3044\u307e\u3059\u3002\u5927\u898f\u6a21\u306a\u30c7\u30fc\u30bf \u30bb\u30c3\u30c8\u306e\u30b9\u30ad\u30e3\u30f3\u3068\u51e6\u7406\u3092\u5fc5\u8981\u3068\u3059\u308b\u30ec\u30dd\u30fc\u30c8\u3001\u30c7\u30fc\u30bf \u30a6\u30a7\u30a2\u30cf\u30a6\u30b9\u3001\u5206\u6790\u30af\u30a8\u30ea\u3092\u5927\u5e45\u306b\u9ad8\u901f\u5316\u3067\u304d\u307e\u3059\u3002<\/p>\n<h2>\u5185\u90e8\u69cb\u9020\u3068\u6a5f\u80fd<\/h2>\n<p>\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u5185\u90e8\u69cb\u9020\u306f\u3001\u5217\u30bb\u30b0\u30e1\u30f3\u30c8\u3068\u30c7\u30a3\u30af\u30b7\u30e7\u30ca\u30ea\u306b\u57fa\u3065\u3044\u3066\u3044\u307e\u3059\u3002\u5217\u30bb\u30b0\u30e1\u30f3\u30c8\u306f\u3001\u5404\u5217\u306e\u5727\u7e2e\u3055\u308c\u305f\u30c7\u30fc\u30bf\u5358\u4f4d\u3067\u3059\u3002\u3053\u308c\u306f\u3001\u30c7\u30fc\u30bf\u306e\u53d6\u5f97\u3092\u5bb9\u6613\u306b\u3059\u308b\u305f\u3081\u306b\u3001\u6700\u5c0f\u5024\u3068\u6700\u5927\u5024\u3092\u542b\u3080\u4e00\u9023\u306e\u30e1\u30bf\u30c7\u30fc\u30bf\u3068\u3068\u3082\u306b\u3001\u5024\u306e\u30bb\u30c3\u30c8\u3067\u69cb\u6210\u3055\u308c\u307e\u3059\u3002<\/p>\n<p>\u8f9e\u66f8\u306f\u3001\u5217\u5185\u306e\u7e70\u308a\u8fd4\u3057\u5024\u3092\u5727\u7e2e\u3059\u308b\u305f\u3081\u306b\u4f7f\u7528\u3055\u308c\u307e\u3059\u3002\u8f9e\u66f8\u306f\u3001\u5b9f\u969b\u306e\u5024\u3092\u8907\u6570\u56de\u4fdd\u5b58\u3059\u308b\u4ee3\u308f\u308a\u306b\u3001\u4e00\u610f\u306e\u5024\u3068\u305d\u308c\u306b\u5bfe\u5fdc\u3059\u308b ID \u3092\u4fdd\u5b58\u3059\u308b\u305f\u3081\u3001\u30b9\u30c8\u30ec\u30fc\u30b8\u8981\u4ef6\u304c\u8efd\u6e1b\u3055\u308c\u3001\u30af\u30a8\u30ea \u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u304c\u5411\u4e0a\u3057\u307e\u3059\u3002<\/p>\n<p>\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306f\u3001\u30d0\u30c3\u30c1\u51e6\u7406\u3068\u547c\u3070\u308c\u308b\u624b\u6cd5\u3092\u5229\u7528\u3057\u3066\u3001\u5927\u898f\u6a21\u306a\u30c7\u30fc\u30bf \u30bb\u30c3\u30c8\u3092\u52b9\u7387\u7684\u306b\u30b9\u30ad\u30e3\u30f3\u304a\u3088\u3073\u51e6\u7406\u3057\u307e\u3059\u3002\u4e00\u5ea6\u306b\u8907\u6570\u306e\u884c\u306b\u5bfe\u3057\u3066\u64cd\u4f5c\u3092\u5b9f\u884c\u3059\u308b\u305f\u3081\u3001\u5206\u6790\u30af\u30a8\u30ea\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u304c\u5411\u4e0a\u3057\u307e\u3059\u3002<\/p>\n<h2>SQL \u306e\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u4e3b\u306a\u6a5f\u80fd<\/h2>\n<ul>\n<li>\n<p><strong>\u30c7\u30fc\u30bf\u5727\u7e2e<\/strong>: \u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306f\u3001\u5217\u30b9\u30c8\u30ec\u30fc\u30b8\u5f62\u5f0f\u3068\u8f9e\u66f8\u30d9\u30fc\u30b9\u306e\u5727\u7e2e\u6280\u8853\u306b\u3088\u308a\u3001\u30c7\u30fc\u30bf \u30b9\u30c8\u30ec\u30fc\u30b8\u8981\u4ef6\u3092\u5927\u5e45\u306b\u524a\u6e1b\u3057\u307e\u3059\u3002<\/p>\n<\/li>\n<li>\n<p><strong>\u30d0\u30c3\u30c1\u30e2\u30fc\u30c9\u51e6\u7406<\/strong>: \u30c7\u30fc\u30bf\u3092\u884c\u3054\u3068\u3067\u306f\u306a\u304f\u30d0\u30c3\u30c1\u3067\u51e6\u7406\u3067\u304d\u308b\u305f\u3081\u3001\u5927\u898f\u6a21\u306a\u30c7\u30fc\u30bf \u30bb\u30c3\u30c8\u306b\u5bfe\u3059\u308b\u30af\u30a8\u30ea\u306e\u5b9f\u884c\u304c\u9ad8\u901f\u5316\u3055\u308c\u307e\u3059\u3002<\/p>\n<\/li>\n<li>\n<p><strong>\u8ff0\u8a9e\u30d7\u30c3\u30b7\u30e5\u30c0\u30a6\u30f3<\/strong>: \u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306f\u8ff0\u8a9e\u30d7\u30c3\u30b7\u30e5\u30c0\u30a6\u30f3\u3092\u30b5\u30dd\u30fc\u30c8\u3057\u307e\u3059\u3002\u3064\u307e\u308a\u3001\u30af\u30a8\u30ea \u30aa\u30d7\u30c6\u30a3\u30de\u30a4\u30b6\u30fc\u306f\u3001\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3059\u308b\u524d\u306b\u30b9\u30c8\u30ec\u30fc\u30b8 \u30ec\u30d9\u30eb\u3067\u30c7\u30fc\u30bf\u3092\u30d5\u30a3\u30eb\u30bf\u30fc\u51e6\u7406\u3067\u304d\u308b\u305f\u3081\u3001\u30af\u30a8\u30ea\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u304c\u3055\u3089\u306b\u5411\u4e0a\u3057\u307e\u3059\u3002<\/p>\n<\/li>\n<li>\n<p><strong>\u30d9\u30af\u30c8\u30eb\u5316\u3055\u308c\u305f\u5b9f\u884c<\/strong>: \u30c7\u30fc\u30bf\u306e\u30d9\u30af\u30c8\u30eb\u5168\u4f53\u306b\u5bfe\u3059\u308b\u64cd\u4f5c\u304c\u540c\u6642\u306b\u5b9f\u884c\u3055\u308c\u308b\u305f\u3081\u3001\u30af\u30a8\u30ea\u306e\u5b9f\u884c\u901f\u5ea6\u304c\u5411\u4e0a\u3057\u307e\u3059\u3002<\/p>\n<\/li>\n<\/ul>\n<h2>SQL \u306e\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u7a2e\u985e<\/h2>\n<p>SQL \u306b\u306f 2 \u7a2e\u985e\u306e\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n<ol>\n<li>\n<p><strong>\u30af\u30e9\u30b9\u30bf\u30fc\u5316\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9 (CCI)<\/strong>:<\/p>\n<ul>\n<li>\u5404\u30c6\u30fc\u30d6\u30eb\u306b\u306f CCI \u3092 1 \u3064\u3060\u3051\u542b\u3081\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<\/li>\n<li>\u30c6\u30fc\u30d6\u30eb\u5168\u4f53\u304c\u5727\u7e2e\u3055\u308c\u305f\u5217\u5f62\u5f0f\u306b\u5909\u63db\u3055\u308c\u307e\u3059\u3002<\/li>\n<li>\u5927\u898f\u6a21\u306a\u30c7\u30fc\u30bf \u30a6\u30a7\u30a2\u30cf\u30a6\u30b9\u3084\u5206\u6790\u30ef\u30fc\u30af\u30ed\u30fc\u30c9\u306b\u6700\u9069\u3067\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n<li>\n<p><strong>\u975e\u30af\u30e9\u30b9\u30bf\u30fc\u5316\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9 (NCCI)<\/strong>:<\/p>\n<ul>\n<li>1 \u3064\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u8907\u6570\u306e NCCI \u3092\u4f5c\u6210\u3067\u304d\u307e\u3059\u3002<\/li>\n<li>\u9078\u629e\u3055\u308c\u305f\u5217\u306e\u307f\u304c\u5727\u7e2e\u3055\u308c\u305f\u5217\u5f62\u5f0f\u306b\u5909\u63db\u3055\u308c\u3001\u6b8b\u308a\u306f\u884c\u30d9\u30fc\u30b9\u306e\u5f62\u5f0f\u306e\u307e\u307e\u306b\u306a\u308a\u307e\u3059\u3002<\/li>\n<li>\u7279\u5b9a\u306e\u5217\u304c\u4ed6\u306e\u5217\u3088\u308a\u3082\u983b\u7e41\u306b\u30af\u30a8\u30ea\u3055\u308c\u308b\u30b7\u30ca\u30ea\u30aa\u306b\u9069\u3057\u3066\u3044\u307e\u3059\u3002<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<p>\u4ee5\u4e0b\u306f\u3001CCI \u3068 NCCI \u306e\u9055\u3044\u3092\u307e\u3068\u3081\u305f\u8868\u3067\u3059\u3002<\/p>\n<table>\n<thead>\n<tr>\n<th>\u7279\u5fb4<\/th>\n<th>\u30af\u30e9\u30b9\u30bf\u30fc\u5316\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9 (CCI)<\/th>\n<th>\u975e\u30af\u30e9\u30b9\u30bf\u30fc\u5316\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9 (NCCI)<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\u30c6\u30fc\u30d6\u30eb\u5909\u63db<\/td>\n<td>\u30c6\u30fc\u30d6\u30eb\u5168\u4f53\u304c\u5217\u5f62\u5f0f\u306b\u5909\u63db\u3055\u308c\u307e\u3059<\/td>\n<td>\u9078\u629e\u3057\u305f\u5217\u306e\u307f\u304c\u5909\u63db\u3055\u308c\u307e\u3059<\/td>\n<\/tr>\n<tr>\n<td>\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u6570<\/td>\n<td>\u30c6\u30fc\u30d6\u30eb\u3054\u3068\u306b 1 \u3064\u306e CCI \u306e\u307f\u304c\u8a31\u53ef\u3055\u308c\u307e\u3059<\/td>\n<td>\u30c6\u30fc\u30d6\u30eb\u4e0a\u306b\u8907\u6570\u306eNCCI\u3092\u4f5c\u6210\u3067\u304d\u307e\u3059<\/td>\n<\/tr>\n<tr>\n<td>\u30af\u30a8\u30ea\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9<\/td>\n<td>\u5b8c\u5168\u306a\u5217\u6307\u5411\u30b9\u30c8\u30ec\u30fc\u30b8\u306e\u305f\u3081\u3001\u4e00\u822c\u7684\u306b\u9ad8\u901f\u3067\u3059<\/td>\n<td>\u30af\u30a8\u30ea\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u306f\u5217\u306e\u9078\u629e\u306b\u4f9d\u5b58\u3059\u308b<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>\u4f7f\u7528\u6cd5\u3001\u8ab2\u984c\u3001\u89e3\u6c7a\u7b56<\/h2>\n<p>\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306f\u3001\u5927\u898f\u6a21\u306a\u30c7\u30fc\u30bf\u51e6\u7406\u3092\u4f34\u3046\u5206\u6790\u30af\u30a8\u30ea\u306b\u975e\u5e38\u306b\u5f79\u7acb\u3061\u307e\u3059\u3002\u305f\u3060\u3057\u3001\u5c0f\u898f\u6a21\u306a\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u3084\u66f4\u65b0\u304c\u983b\u7e41\u306b\u767a\u751f\u3059\u308b OLTP (\u30aa\u30f3\u30e9\u30a4\u30f3 \u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u51e6\u7406) \u30ef\u30fc\u30af\u30ed\u30fc\u30c9\u306b\u306f\u9069\u3055\u306a\u3044\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002\u3053\u306e\u3088\u3046\u306a\u30b7\u30ca\u30ea\u30aa\u3067\u306f\u3001\u5f93\u6765\u306e\u884c\u30d9\u30fc\u30b9\u306e\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u65b9\u304c\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u304c\u512a\u308c\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u8ab2\u984c\u306f\u6b21\u306e\u3068\u304a\u308a\u3067\u3059\u3002<\/p>\n<ul>\n<li>\n<p><strong>\u633f\u5165\u3068\u66f4\u65b0\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9<\/strong>: \u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3067\u306f\u3001\u6700\u9069\u306a\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u3092\u5f97\u308b\u305f\u3081\u306b\u30c7\u30fc\u30bf\u3092\u4e00\u62ec\u30ed\u30fc\u30c9\u3059\u308b\u5fc5\u8981\u304c\u3042\u308b\u305f\u3081\u3001\u884c\u30d9\u30fc\u30b9\u306e\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3068\u6bd4\u8f03\u3057\u3066\u3001\u633f\u5165\u304a\u3088\u3073\u66f4\u65b0\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u304c\u4f4e\u4e0b\u3059\u308b\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n<\/li>\n<li>\n<p><strong>\u30c7\u30eb\u30bf\u30b9\u30c8\u30a2<\/strong>: \u66f4\u65b0\u3092\u52b9\u7387\u7684\u306b\u51e6\u7406\u3059\u308b\u305f\u3081\u306b\u3001SQL Server \u306f\u30b3\u30df\u30c3\u30c8\u3055\u308c\u3066\u3044\u306a\u3044\u30c7\u30fc\u30bf\u7528\u306e Delta Store \u3092\u4fdd\u6301\u3057\u3001\u5b9a\u671f\u7684\u306b\u30e1\u30a4\u30f3\u306e\u5217\u30b9\u30c8\u30a2\u306b\u30de\u30fc\u30b8\u3057\u307e\u3059\u3002\u3053\u306e\u30d7\u30ed\u30bb\u30b9\u306f\u3001\u30de\u30fc\u30b8\u4e2d\u306e\u30af\u30a8\u30ea \u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u306b\u5f71\u97ff\u3092\u4e0e\u3048\u308b\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n<\/li>\n<\/ul>\n<p>\u3053\u308c\u3089\u306e\u8ab2\u984c\u306b\u5bfe\u3059\u308b\u89e3\u6c7a\u7b56\u306b\u306f\u6b21\u306e\u3088\u3046\u306a\u3082\u306e\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n<ul>\n<li>\n<p><strong>\u30d0\u30c3\u30c1\u66f4\u65b0<\/strong>: \u66f4\u65b0\u3092\u3088\u308a\u5927\u304d\u306a\u30d0\u30c3\u30c1\u3067\u5b9f\u884c\u3059\u308b\u3068\u3001\u30c7\u30eb\u30bf \u30b9\u30c8\u30a2\u306e\u30de\u30fc\u30b8\u306e\u983b\u5ea6\u304c\u6e1b\u308a\u3001\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u304c\u5411\u4e0a\u3057\u307e\u3059\u3002<\/p>\n<\/li>\n<li>\n<p><strong>\u30c7\u30fc\u30bf\u306e\u30bb\u30b0\u30e1\u30f3\u30c8\u5316<\/strong>: \u30c7\u30fc\u30bf\u3092\u5c0f\u3055\u306a\u5358\u4f4d\u306b\u5206\u5272\u3059\u308b\u3068\u3001\u633f\u5165\u304a\u3088\u3073\u66f4\u65b0\u64cd\u4f5c\u304c\u9ad8\u901f\u5316\u3055\u308c\u307e\u3059\u3002<\/p>\n<\/li>\n<\/ul>\n<h2>\u7279\u5fb4\u3068\u6bd4\u8f03<\/h2>\n<p>\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3092\u540c\u69d8\u306e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u6a5f\u80fd\u3068\u6bd4\u8f03\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<table>\n<thead>\n<tr>\n<th>\u7279\u5fb4<\/th>\n<th>\u5217\u30b9\u30c8\u30a2\u30a4\u30f3\u30c7\u30c3\u30af\u30b9<\/th>\n<th>\u884c\u30b9\u30c8\u30a2\u30a4\u30f3\u30c7\u30c3\u30af\u30b9<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\u4fdd\u5b58\u5f62\u5f0f<\/td>\n<td>\u5217\u578b\u30b9\u30c8\u30ec\u30fc\u30b8<\/td>\n<td>\u884c\u30d9\u30fc\u30b9\u306e\u30b9\u30c8\u30ec\u30fc\u30b8<\/td>\n<\/tr>\n<tr>\n<td>\u5727\u7e2e<\/td>\n<td>\u9ad8\u3044\u5727\u7e2e\u6bd4<\/td>\n<td>\u5727\u7e2e\u6bd4\u304c\u4f4e\u3044<\/td>\n<\/tr>\n<tr>\n<td>\u30af\u30a8\u30ea\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9<\/td>\n<td>\u5206\u6790\u30af\u30a8\u30ea\u306e\u9ad8\u901f\u5316<\/td>\n<td>OLTP\u30af\u30a8\u30ea\u306e\u9ad8\u901f\u5316<\/td>\n<\/tr>\n<tr>\n<td>\u633f\u5165\u3068\u66f4\u65b0\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9<\/td>\n<td>\u500b\u5225\u306e\u66f4\u65b0\u306f\u9045\u304f\u306a\u308a\u307e\u3059<\/td>\n<td>\u500b\u5225\u306e\u66f4\u65b0\u304c\u9ad8\u901f\u5316<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>\u5c55\u671b\u3068\u5c06\u6765\u306e\u30c6\u30af\u30ce\u30ed\u30b8\u30fc<\/h2>\n<p>\u30c7\u30fc\u30bf\u304c\u6307\u6570\u95a2\u6570\u7684\u306b\u5897\u52a0\u3057\u7d9a\u3051\u308b\u4e2d\u3001\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306f\u6700\u65b0\u306e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u91cd\u8981\u306a\u30b3\u30f3\u30dd\u30fc\u30cd\u30f3\u30c8\u3067\u3042\u308a\u7d9a\u3051\u308b\u3067\u3057\u3087\u3046\u3002\u4eca\u5f8c\u306e\u9032\u6b69\u306f\u3001\u66f4\u65b0\u306b\u95a2\u9023\u3059\u308b\u8ab2\u984c\u306b\u5bfe\u51e6\u3057\u3001\u3055\u3089\u306b\u52b9\u7387\u7684\u306a\u5727\u7e2e\u30a2\u30eb\u30b4\u30ea\u30ba\u30e0\u3092\u63d0\u4f9b\u3059\u308b\u3053\u3068\u306b\u91cd\u70b9\u304c\u7f6e\u304b\u308c\u308b\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n<h2>SQL \u306e\u30d7\u30ed\u30ad\u30b7 \u30b5\u30fc\u30d0\u30fc\u3068\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9<\/h2>\n<p>OneProxy \u304c\u63d0\u4f9b\u3059\u308b\u30d7\u30ed\u30ad\u30b7 \u30b5\u30fc\u30d0\u30fc\u306f\u3001\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3092\u4f7f\u7528\u3057\u3066 SQL Server \u5c55\u958b\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u3092\u5411\u4e0a\u3067\u304d\u307e\u3059\u3002SQL \u30af\u30a8\u30ea\u3092\u30d7\u30ed\u30ad\u30b7 \u30b5\u30fc\u30d0\u30fc\u7d4c\u7531\u3067\u30eb\u30fc\u30c6\u30a3\u30f3\u30b0\u3059\u308b\u3053\u3068\u3067\u3001\u7d44\u7e54\u306f\u51e6\u7406\u30aa\u30fc\u30d0\u30fc\u30d8\u30c3\u30c9\u306e\u4e00\u90e8\u3092\u30aa\u30d5\u30ed\u30fc\u30c9\u3057\u3001\u30ea\u30e2\u30fc\u30c8 \u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u306e\u5fdc\u7b54\u6642\u9593\u3092\u6539\u5584\u3067\u304d\u307e\u3059\u3002\u3055\u3089\u306b\u3001OneProxy \u306e\u8ca0\u8377\u5206\u6563\u6a5f\u80fd\u306b\u3088\u308a\u3001\u30af\u30a8\u30ea\u3092\u5747\u7b49\u306b\u5206\u6563\u3057\u3066\u3001\u30ea\u30bd\u30fc\u30b9\u306e\u4f7f\u7528\u3092\u6700\u9069\u5316\u3067\u304d\u307e\u3059\u3002<\/p>\n<h2>\u95a2\u9023\u30ea\u30f3\u30af<\/h2>\n<p>SQL \u306e\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u8a73\u7d30\u306b\u3064\u3044\u3066\u306f\u3001\u6b21\u306e\u30ea\u30bd\u30fc\u30b9\u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n<ul>\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/indexes\/columnstore-indexes-overview?view=sql-server-ver15\" target=\"_new\" rel=\"noopener nofollow\">\u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306b\u95a2\u3059\u308b Microsoft Docs<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlservercentral.com\/steps\/columnstore-indexes-introduction\" target=\"_new\" rel=\"noopener nofollow\">SQL Server Central: \u5217\u30b9\u30c8\u30a2 \u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u6982\u8981<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/hub\/product-learning\/sql-prompt\/development-and-testing-benefits-of-data-compression-in-sql-server\" target=\"_new\" rel=\"noopener nofollow\">SQL Server \u3067\u306e\u30c7\u30fc\u30bf\u5727\u7e2e<\/a><\/li>\n<\/ul>","protected":false},"featured_media":467910,"menu_order":0,"template":"","meta":{"_acf_changed":false,"content-type":"","inline_featured_image":false,"footnotes":""},"class_list":["post-476326","wiki","type-wiki","status-publish","has-post-thumbnail","hentry"],"acf":{"faq_title":"Frequently Asked Questions about <mark>Columnstore Indexes in SQL: An Overview<\/mark>","faq_items":[{"question":"What are Columnstore indexes in SQL, and how do they work?","answer":"<p>Columnstore indexes in SQL are a database feature that organizes and stores data in a columnar format rather than the traditional row-based storage. This arrangement allows for improved data compression and faster query performance for analytical workloads. Data within each column is stored and processed together, leveraging batch processing techniques. The indexes consist of column segments and dictionaries, which facilitate efficient data retrieval and compression.<\/p>"},{"question":"What is the history behind the origin of Columnstore indexes in SQL?","answer":"<p>Columnstore indexes in SQL were first introduced by Microsoft with the release of SQL Server 2012. The concept of columnar storage has been around since the 1970s, but it gained popularity in the mid-2000s with the rise of big data and the need for better data compression and query performance. Microsoft's implementation marked a significant advancement in this area, making it a standard feature in modern database management systems.<\/p>"},{"question":"How do Clustered Columnstore Indexes (CCI) differ from Non-Clustered Columnstore Indexes (NCCI)?","answer":"<p>Clustered Columnstore Indexes (CCI) convert the entire table into a columnar format, allowing only one CCI per table. On the other hand, Non-Clustered Columnstore Indexes (NCCI) allow multiple indexes on a single table and only convert selected columns into a columnar format. CCI tends to have faster query performance due to complete columnar storage, while NCCI's performance depends on the selection of columns.<\/p>"},{"question":"What are the key features of Columnstore indexes in SQL?","answer":"<p>Some key features of Columnstore indexes include:<\/p><ul><li>High data compression ratios, leading to reduced storage requirements.<\/li><li>Batch mode processing for faster execution of large analytical queries.<\/li><li>Predicate pushdown, allowing for filtering data at the storage level before retrieval.<\/li><li>Vectorized execution for improved query execution speeds.<\/li><\/ul>"},{"question":"What are the main challenges faced with Columnstore indexes?","answer":"<p>While Columnstore indexes offer significant benefits for analytical queries, they can present challenges, such as slower insert and update performance. This is due to the need for bulk loading data for optimal performance. Additionally, the maintenance of a Delta Store for uncommitted data can impact query performance during merges.<\/p>"},{"question":"How can organizations overcome the challenges of Columnstore indexes?","answer":"<p>To improve insert and update performance, organizations can opt for batch updates, performing updates in larger batches. Segmenting data into smaller units can also aid in faster insert and update operations.<\/p>"},{"question":"How can OneProxy's proxy servers enhance SQL Server deployments with Columnstore indexes?","answer":"<p>OneProxy's proxy servers can optimize SQL Server deployments using Columnstore indexes by offloading some processing overhead and potentially improving response times for remote clients. Additionally, OneProxy's load balancing capabilities help distribute queries evenly, optimizing resource usage and enhancing overall performance.<\/p>"},{"question":"What is the future outlook for Columnstore indexes in SQL?","answer":"<p>Columnstore indexes are expected to remain a crucial component of modern databases as data continues to grow exponentially. Future advancements may focus on addressing challenges related to updates and providing even more efficient compression algorithms.<\/p>"},{"question":"Where can I find more information about Columnstore indexes in SQL?","answer":"<p>For more in-depth insights on Columnstore indexes in SQL, you can refer to the following resources:<\/p><ul><li>Microsoft Docs on Columnstore Indexes: <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/indexes\/columnstore-indexes-overview?view=sql-server-ver15\" target=\"_new\">Link<\/a><\/li><li>SQL Server Central: Introduction to Columnstore Indexes: <a href=\"https:\/\/www.sqlservercentral.com\/steps\/columnstore-indexes-introduction\" target=\"_new\">Link<\/a><\/li><li>Data Compression in SQL Server: <a href=\"https:\/\/www.red-gate.com\/hub\/product-learning\/sql-prompt\/development-and-testing-benefits-of-data-compression-in-sql-server\" target=\"_new\">Link<\/a><\/li><\/ul>"}]},"_links":{"self":[{"href":"https:\/\/oneproxy.pro\/jp\/wp-json\/wp\/v2\/wiki\/476326","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oneproxy.pro\/jp\/wp-json\/wp\/v2\/wiki"}],"about":[{"href":"https:\/\/oneproxy.pro\/jp\/wp-json\/wp\/v2\/types\/wiki"}],"version-history":[{"count":0,"href":"https:\/\/oneproxy.pro\/jp\/wp-json\/wp\/v2\/wiki\/476326\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/oneproxy.pro\/jp\/wp-json\/wp\/v2\/media\/467910"}],"wp:attachment":[{"href":"https:\/\/oneproxy.pro\/jp\/wp-json\/wp\/v2\/media?parent=476326"}],"curies":[{"name":"\u3046\u30fc\u3093","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}