Ms - sql الحركة من المتوسط

معظم الناس على دراية هذه العبارة، كوتيسيس سوف تقتل طائرتين مع ستونكوت واحد. إذا لم تكن كذلك، فإن المرحلة تشير إلى نهج يعالج هدفين في إجراء واحد. (للأسف، فإن التعبير نفسه غير سارة إلى حد ما، كما أن معظمنا don39t تريد رمي الحجارة على الحيوانات البريئة) اليوم I39m الذهاب لتغطية بعض الأساسيات على اثنين من الميزات الرائعة في سكل سيرفر: مؤشر كولومنستور (متوفر فقط في سكل سيرفر المؤسسة) و مخزن الاستعلام سكل. نفذت ميكروسوفت فعلا مؤشر كولومنستور في سكل 2012 المؤسسة، على الرغم من أنها 39ve تعزيزه في آخر إصدارين من سكل سيرفر. عرضت ميكروسوفت مخزن الاستعلام في سكل سيرفر 2016. لذلك، ما هي هذه الميزات والسبب في أنها مهمة حسنا، لدي تجريبي من شأنها أن أعرض كل من الميزات وتبين كيف يمكن أن تساعدنا. قبل أن أذهب أبعد من ذلك، وأنا أيضا تغطية هذا (وغيرها من سكل 2016 الميزات) في بلدي مجلة مجلة كود على الميزات الجديدة سكل 2016. كمقدمة أساسية، يمكن أن يساعد مؤشر كولومنستور تسريع الاستعلامات التي سكاناجريغات على كميات كبيرة من البيانات، و يخزن "مخزن طلبات البحث" عمليات تنفيذ الاستعلام وخطط التنفيذ وإحصاءات وقت التشغيل التي تحتاج عادة إلى جمعها يدويا. ثق بي عندما أقول، هذه هي الميزات الرائعة. لهذا العرض، I39ll باستخدام قاعدة بيانات تجريبي مستودع بيانات ميكروسوفت كونتوسو التجزئة. يتحدث فضفاضة، كونتوسو دو مثل حصة كبيرة حقا أدفنتورورسكوت، مع الجداول التي تحتوي على الملايين من الصفوف. (أكبر جدول أدفنتوروركس يحتوي على ما يقرب من 100،000 الصفوف على الأكثر). يمكنك تنزيل قاعدة بيانات كونتوسو دو هنا: ميكروسوفت-usdownloaddetails. aspxid18279. يعمل كونتوسو دو بشكل جيد جدا عندما تريد اختبار الأداء على الاستعلامات ضد الجداول الكبيرة. كونتوسو دو يحتوي على مستودع البيانات القياسية جدول الحقائق يسمى فاكتونلينزاليس، مع 12.6 مليون الصفوف. that39s بالتأكيد ليست أكبر مستودع مستودع البيانات في العالم، ولكن it39s لا child39s اللعب سواء. لنفترض أنني أريد تلخيص كمية مبيعات المنتجات لعام 2009، وترتيب المنتجات. قد استعلم جدول الحقائق والانضمام إلى جدول "أبعاد المنتج" واستخدام الدالة رانك، مثل ذلك: Here39s مجموعة نتيجة جزئية من أعلى 10 صفوف، من إجمالي المبيعات. على جهاز الكمبيوتر المحمول (i7، 16 غيغابايت من ذاكرة الوصول العشوائي)، الاستعلام يأخذ أي مكان من 3-4 ثوان لتشغيل. قد لا يبدو ذلك وكأنه نهاية العالم، ولكن بعض المستخدمين قد يتوقعون نتائج قريبة من لحظة (الطريقة التي قد ترى نتائج شبه فورية عند استخدام إكسيل مقابل مكعب أولاب). الفهرس الوحيد الموجود حاليا على هذا الجدول هو فهرس متفاوت المسافات لمفتاح المبيعات. إذا نظرنا إلى خطة التنفيذ، سكل سيرفر يجعل اقتراح لإضافة فهرس تغطية إلى الجدول: الآن، فقط لأن سكل سيرفر يقترح فهرس، يعني tn39 يجب عليك إنشاء الفهارس عمياء على كل رسالة اقتباس إندكسكوت. ومع ذلك، في هذه الحالة، يكتشف سكل سيرفر أننا تصفية استنادا إلى السنة، واستخدام مفتاح المنتج ومبلغ المبيعات. لذلك، يقترح سكل سيرفر مؤشر تغطية، مع داتيكي كحقل مفتاح الفهرس. السبب الذي نسميه هذا مؤشر كوتوفرينجكوت لأن سكل سيرفر سوف كوتبرينغ على طول الحقول غير المفتاحكوت استخدمنا في الاستعلام، كوتور ريديكوت. وبهذه الطريقة، سكل سيرفر لا تحتاج إلى استخدام الجدول أو الفهرس المتفاوت المسافات في كافة محركات قاعدة البيانات يمكن ببساطة استخدام مؤشر تغطية الاستعلام. تغطية المؤشرات تحظى بشعبية في بعض البيانات تخزين البيانات والإبلاغ سيناريوهات، على الرغم من أنها لا تأتي بتكلفة محرك قاعدة البيانات الحفاظ عليها. ملاحظة: تغطي الفهارس حول لفترة طويلة، لذلك أنا haven39t حتى الآن غطت مؤشر كولومنستور ومخزن الاستعلام. لذلك، سأقوم بإضافة مؤشر التغطية: إذا قمت بإعادة تنفيذ نفس الاستعلام ركضت قبل لحظة (تلك التي جمعت كمية المبيعات لكل منتج)، الاستعلام في بعض الأحيان يبدو لتشغيل حوالي ثانية أسرع، وأحصل على خطة تنفيذ مختلفة، واحدة التي تستخدم مؤشر تسعى بدلا من فهرس المسح الضوئي (باستخدام مفتاح التاريخ على مؤشر تغطية لاسترداد المبيعات لعام 2009). لذلك، قبل مؤشر كولومنستور، يمكن أن يكون هذا طريقة واحدة لتحسين هذا الاستعلام في الإصدارات القديمة من سكل سيرفر. تشغيله أسرع قليلا من أول واحد، وأنا الحصول على خطة التنفيذ مع مؤشر تسعى بدلا من مسح الفهرس. ومع ذلك، هناك بعض القضايا: واثنين من مشغلي التنفيذ كوتيندكس سيكوت و كوتهاش المباراة (مجموع) كوت على حد سواء تعمل أساسا عن طريق روكوت. تخيل ذلك في جدول يحتوي على مئات الملايين من الصفوف. ذات الصلة، والتفكير في محتويات جدول الحقائق: في هذه الحالة، قيمة مفتاح تاريخ واحد أندور قيمة مفتاح منتج واحد قد تتكرر عبر مئات الآلاف من الصفوف (تذكر، يحتوي الجدول حقيقة أيضا مفاتيح للجغرافيا والترويج، بائع ، وما إلى ذلك) لذلك، عندما كوتندكس سيكوت و كوتهاش ماتشكوت صف العمل حسب الصف، وأنها تفعل ذلك على القيم التي قد تتكرر عبر العديد من الصفوف الأخرى. هذا هو عادة حيث I39d سيجو إلى سكل سيرفر كولومنستور الفهرس الذي يقدم سيناريو لتحسين أداء هذا الاستعلام بطرق مذهلة. ولكن قبل أن أفعل ذلك، Let39s العودة في الوقت المناسب. Let39s العودة إلى عام 2010، عندما قدمت مايكروسوفت وظيفة إضافية ل إكسيل المعروفة باسم بويربيفوت. كثير من الناس ربما يتذكرون مشاهدة العروض التجريبية من بويربيفوت ل إكسيل، حيث يمكن للمستخدم قراءة الملايين من الصفوف من مصدر بيانات خارجي إلى إكسيل. سوف بويربيفوت ضغط البيانات، وتوفير محرك لإنشاء الجداول المحورية ومخططات البيفوت التي أجريت بسرعة مذهلة ضد البيانات المضغوطة. يستخدم بويربيفوت تقنية في الذاكرة التي وصفتها ميكروسوفت كوفرتيباكوت. هذه التكنولوجيا في الذاكرة في بويربيفوت سوف تتخذ أساسا مكررة القيم الرئيسية كييفورين الأعمال وضغط عليها وصولا إلى ناقلات واحدة. كما أن التكنولوجيا الموجودة في الذاكرة سوف تقوم بتقسيم هذه القيم بالتوازي، في كتل عدة مئات في المرة الواحدة. وخلاصة القول هو أن مايكروسوفت خبز كمية كبيرة من تحسينات الأداء في ميزة فيرتيباق في الذاكرة بالنسبة لنا لاستخدام، والحق من مربع المثل. لماذا أنا أخذ هذا نزهة صغيرة أسفل حارة الذاكرة لأنه في سكل سيرفر 2012، نفذت ميكروسوفت واحدة من أهم الميزات في تاريخ محرك قاعدة البيانات الخاصة بهم: مؤشر كولومنستور. الفهرس هو في الحقيقة فهرس بالاسم فقط: هو طريقة لاتخاذ جدول سكل سيرفر وإنشاء مخزن عمود مضغوط في الذاكرة يقوم بضغط قيم المفتاح الخارجي المكرر وصولا إلى قيم متجه واحد. أنشأت مايكروسوفت أيضا تجمع الاحتياطي الجديد لقراءة هذه القيم متجه مضغوط بالتوازي، وخلق إمكانات لتحقيق مكاسب أداء ضخمة. لذلك، I39m الذهاب إلى إنشاء فهرس العمودية على الطاولة، و I39ll نرى كم أفضل (وأكثر كفاءة) تشغيل الاستعلام، مقابل الاستعلام الذي يتم تشغيله ضد مؤشر التغطية. لذلك، I39ll إنشاء نسخة مكررة من فاكتونلينزاليس (I39ll نسميها فاكتونلينزاليسديتايلنكس)، و I39ll إنشاء فهرس العمودية على الجدول مكررة بهذه الطريقة أنا won39t تتداخل مع الجدول الأصلي ومؤشر تغطي بأي شكل من الأشكال. بعد ذلك، I39ll إنشاء فهرس العمودية على الجدول الجديد: لاحظ عدة أشياء: I39ve المحدد عدة أعمدة المفتاح الأجنبي، فضلا عن مبلغ المبيعات. تذكر أن فهرس الأعمدة ليس مثل فهرس مخزن الصف التقليدي. ليس هناك كوتيكوت. نحن ببساطة تشير إلى الأعمدة التي يجب ضغط سكل سيرفر ووضعها في مخزن عمود في الذاكرة. لاستخدام تشبيه بويربيفوت ل إكسيل عندما نقوم بإنشاء فهرس عمود، we39re نقول سكل سيرفر أن تفعل أساسا نفس الشيء الذي فعلت بويربيفوت عندما استوردنا 20 مليون صف في إكسيل باستخدام بويربيفوت لذلك، I39ll إعادة تشغيل الاستعلام، وهذه المرة باستخدام جدول فاكتونلينزاليسديتايلنكس مكرر الذي يحتوي على فهرس مخزن العمود. يتم تشغيل هذا الاستعلام على الفور في أقل من ثانية. ويمكنني أيضا أن أقول أنه حتى لو كان الجدول مئات الملايين من الصفوف، فإنه لا يزال يعمل في كوتابات المثل من إيلاشكوت. يمكننا أن ننظر في خطة التنفيذ (وفي لحظات قليلة، ونحن سوف)، ولكن الآن الوقت 39s لتغطية ميزة مخزن الاستعلام. تخيل للحظة، أننا أجرينا كل من الاستعلامات بين عشية وضحاها: الاستعلام الذي استخدم الجدول فاكتونلينزاليس العادية (مع مؤشر التغطية) ثم الاستعلام الذي استخدم الجدول المكررة مع فهرس العمود. عند تسجيل الدخول في صباح اليوم التالي، نحن نرغب في رؤية خطة التنفيذ لكل من الاستعلامات عند حدوثها، فضلا عن إحصاءات التنفيذ. وبعبارة أخرى، we39d ترغب في رؤية نفس الإحصاءات التي we39d تكون قادرة على معرفة ما إذا كنا ركض كل من الاستفسارات بشكل تفاعلي في ستوديو إدارة سكل، تحولت في تايم وإحصاءات إو، وعرضت خطة التنفيذ مباشرة بعد تنفيذ الاستعلام. حسنا، أن 39s ما مخزن الاستعلام يسمح لنا القيام به يمكننا تشغيل (تمكين) مخزن الاستعلام لقاعدة بيانات، والذي سيؤدي سكل سيرفر لتخزين تنفيذ الاستعلام والتخطيط الإحصاءات حتى نتمكن من عرضها في وقت لاحق. لذلك، I39m الذهاب إلى تمكين مخزن الاستعلام على قاعدة بيانات كونتوسو مع الأمر التالي (و I39ll أيضا مسح أي التخزين المؤقت): ثم I39ll تشغيل اثنين من الاستعلامات (و كوتريبندكوت التي ركضتها قبل ساعات): الآن Let39s تدعي أنها ركضت ساعات منذ. وفقا لما قلته، فإن مخزن الاستعلام التقاط إحصاءات التنفيذ. فكيف أرى لهم لحسن الحظ، أن 39s من السهل جدا. إذا قمت بتوسيع قاعدة بيانات كونتوسو دو، I39ll راجع مجلد "استعلام مخزن". مخزن الاستعلام لديه وظائف هائلة و I39ll محاولة لتغطية الكثير من ذلك في المشاركات بلوق لاحقة. ولكن في الوقت الحالي، أريد عرض إحصاءات التنفيذ على الاستعلامات اثنين، وعلى وجه التحديد فحص مشغلي التنفيذ لمؤشر المخازن. لذلك I39ll انقر بزر الماوس الأيمن فوق أعلى استعلامات الموارد المستهلك وتشغيل هذا الخيار. هذا يعطيني الرسم البياني مثل واحد أدناه، حيث أستطيع أن أرى مدة تنفيذ التنفيذ (بالمللي ثانية) لجميع الاستعلامات التي تم تنفيذها. في هذه الحالة، كان الاستعلام 1 الاستعلام ضد الجدول الأصلي مع فهرس التغطية، وكان الاستعلام 2 ضد الجدول مع فهرس الأعمدة. أرقام don39t تكمن مؤشر مخزن تجاوزت أداء مؤشر تابلكوفرينغ الأصلي بعامل ما يقرب من 7 إلى 1. يمكنني تغيير المقياس للنظر في استهلاك الذاكرة بدلا من ذلك. في هذه الحالة، لاحظ أن الاستعلام 2 (استعلام الفهرس عمود مخزن) استخدام ذاكرة أكثر بكثير. هذا يوضح بوضوح لماذا مؤشر العمودية يمثل التكنولوجيا كوتين-ميموريكوت تحميل سكل سيرفر مؤشر مخزن العمود بأكمله في الذاكرة ويستخدم تجمع المخزن المؤقت مختلفة تماما مع مشغلي التنفيذ المحسنة لمعالجة الفهرس. موافق، لذلك لدينا بعض الرسوم البيانية لعرض إحصائيات التنفيذ يمكن أن نرى خطة التنفيذ (ومشغلي التنفيذ) المرتبطة بكل تنفيذ نعم، يمكننا إذا قمت بالنقر فوق شريط العمودي للاستعلام الذي استخدم فهرس العمود، you39ll انظر التنفيذ خطة أدناه. أول شيء نراه هو أن سكل سيرفر إجراء فحص فهرس العمود، والتي تمثل ما يقرب من 100 من تكلفة الاستعلام. قد تكون تقول، كوتايت دقيقة واحدة، الاستعلام الأول يستخدم مؤشر تغطية وأداء فهرس تسعى فكيف يمكن أن يكون مسح فهرس العمودية يكون أسرع من أن 39s سؤالا مشروعا، ولحسن الحظ هناك 39s الجواب. حتى عندما يقوم الاستعلام الأول بإجراء فهرس، فإنه لا يزال تنفيذ كوترو بواسطة روكوت. إذا وضعت الماوس فوق عامل المسح الضوئي عامل مسح الفهرس، أرى تلميح (مثل واحد أدناه)، مع إعداد واحد مهم: وضع التنفيذ هو باتش (على عكس رو، وهو ما كان لدينا مع الاستعلام الأول باستخدام تغطي المؤشر). هذا الوضع باتش يخبرنا أن سكل سيرفر معالجة ناقلات مضغوط (لأي القيم مفتاح أجنبي التي يتم تكرارها، مثل مفتاح المنتج ومفتاح التاريخ) في دفعات ما يقرب من 1000، بالتوازي. لذلك سكل سيرفر لا يزال قادرا على معالجة مؤشر عمود أكثر من ذلك بكثير بكفاءة. بالإضافة إلى ذلك، إذا وضعت الماوس فوق مهمة هاش ماتش (التجميع)، أرى أيضا أن سكل سيرفر يتم تجميع فهرس المخازن العمودية باستخدام وضع دفعة (على الرغم من أن المشغل نفسه يمثل مثل هذه النسبة الصغيرة من تكلفة الاستعلام) وأخيرا، أنت قد يسأل، كوتوك، لذلك سكل سيرفر ضغط القيم في البيانات، يعامل القيم كمتجهات، وقراءتها في كتل ما يقرب من ألف القيم بالتوازي ولكن الاستعلام الخاص بي يريد فقط البيانات لعام 2009. لذلك هو سكل سيرفر المسح الضوئي فوق مجموعة كاملة من داتاكوت مرة أخرى، سؤال جيد. الجواب هو، كوتنوت ريليكوت. لحسن الحظ بالنسبة لنا، تجمع مخزن المخزن المؤقت مخزن عمود جديد وظيفة أخرى تسمى كوتسيجمنت إكستراكتيونكوت. في الأساس، سيقوم سكل سيرفر بفحص قيم المتجهات للعمود مفتاح التاريخ في فهرس العمود، وإزالة الشرائح التي تقع خارج نطاق عام 2009. I39ll توقف هنا. في المشاركات بلوق لاحق I39ll تغطية كل من مخزن عمود ومخزن الاستعلام بمزيد من التفصيل. أساسا، ما رأينا هنا اليوم هو أن مؤشر كولومنستور يمكن أن تسرع إلى حد كبير الاستعلامات التي سكاناجريغات على كميات كبيرة من البيانات، ومخزن الاستعلام التقاط عمليات تنفيذ الاستعلام والسماح لنا لفحص التنفيذ وإحصاءات الأداء في وقت لاحق. في النهاية، we39d ترغب في إنتاج مجموعة النتائج التي تبين ما يلي. لاحظ ثلاثة أشياء: الأعمدة أساسا محورية كل من أسباب العودة المحتملة، بعد عرض مبلغ المبيعات مجموعة النتائج تحتوي على المجاميع الفرعية بحلول نهاية الأسبوع (الأحد) التاريخ عبر جميع العملاء (حيث العميل هو نول) مجموعة النتائج يحتوي على المجموع الكلي صف (حيث العميل والتاريخ على حد سواء نول) أولا، قبل أن ندخل في نهاية سكل يمكننا استخدام القدرة بيفوتماتريكس ديناميكية في سرس. سنحتاج ببساطة إلى الجمع بين مجموعتي النتائج من عمود واحد ثم يمكننا تغذية النتائج إلى مراقبة مصفوفة سرس، والتي سوف تنتشر أسباب العودة عبر محور الأعمدة من التقرير. ومع ذلك، لا يستخدم الجميع سرس (على الرغم من أن معظم الناس ينبغي). ولكن حتى في هذه الحالة، يحتاج المطورون أحيانا إلى استهلاك مجموعات النتائج في شيء آخر غير أداة الإبلاغ. لذلك لهذا المثال، Let39s نفترض أننا نريد أن ننتج مجموعة النتائج لصفحة شبكة ويب وربما المطور يريد كوتستريب استبعاد الصفوف المجموع الفرعي (حيث لدي قيمة ريسولتسستنوم 2 و 3) ووضعها في شبكة ملخص. حتى الخط السفلي، ونحن بحاجة لتوليد الناتج أعلاه مباشرة من إجراء المخزنة. و كما أضاف تويست الأسبوع المقبل يمكن أن يكون سبب العودة X و Y و Z. لذلك نحن don39t معرفة عدد أسباب العودة يمكن أن يكون هناك. نحن نريد بسيط الاستعلام إلى محورية القيم المحتملة المحتملة لسبب الإرجاع. هنا حيث T - سكل بيفوت لديه قيود نحن بحاجة إلى توفيره القيم الممكنة. منذ أننا فاز 39t نعرف أنه حتى وقت التشغيل، ونحن بحاجة إلى إنشاء سلسلة الاستعلام ديناميكيا باستخدام نمط سكل الحيوي. يتضمن نمط سكل الديناميكي توليد بناء الجملة، قطعة قطعة، تخزينها في سلسلة، ثم تنفيذ السلسلة في النهاية. ديناميك سكل يمكن أن تكون صعبة، كما لدينا لتضمين بناء الجملة داخل سلسلة. ولكن في هذه الحالة، لدينا الخيار الحقيقي الوحيد إذا أردنا التعامل مع عدد متغير من أسباب العودة. I39ve وجدت دائما أن أفضل طريقة لإنشاء حل سكل الديناميكي هو من خلال معرفة ما كوتيدالكوت ولدت الاستعلام سيكون في النهاية (في هذه الحالة، نظرا لأسباب عودة نعرفها عن)، ثم عكس الهندسة ذلك عن طريق قطع معا جزء واحد في وقت واحد. وهكذا، هنا هو سكل نحن بحاجة إذا كنا نعرف أن أسباب العودة (A إلى D) كانت ثابتة ولن تتغير. يقوم الاستعلام بما يلي: يجمع البيانات من ساليسداتا مع البيانات من ريتورداتا حيث نحن كوثارد-ويريكوت كلمة المبيعات كنوع إجراء شكل جدول المبيعات ثم قم باستخدام سبب الإرجاع من بيانات الإرجاع في نفس العمود أكتيونتيب. وهذا سيعطينا العمود أكتيونتيب نظيفة التي محور. نحن الجمع بين عبارات سيليكت اثنين إلى تعبير جدول مشترك (كت)، وهو أساسا الاستعلام الفرعي الجدول المشتقة التي نستخدمها لاحقا في العبارة التالية (إلى بيفوت) عبارة بيفوت ضد كت، التي تجمع الدولارات لنوع الإجراء في أحد قيم نوع الإجراء الممكنة. لاحظ أن هذا isn39t مجموعة النتيجة النهائية. نحن نضع هذا في كت التي تقرأ من كت الأولى. والسبب في ذلك هو أننا نريد أن نفعل مجموعات متعددة في النهاية. عبارة سيليكت النهائية التي تقرأ من بيفوتكت، ويجمعها مع استعلام لاحق ضد بيفوتكت نفسه، ولكن حيث نقوم أيضا بتنفيذ مجموعتين في ميزة مجموعات التجميع في سكل 2008: تجميع حسب تاريخ نهاية الأسبوع (dbo. WeekEndingDate) تجميع لجميع الصفوف () حتى إذا كنا نعرف على وجه اليقين أن we39d لم يكن لديك أكثر رموز العودة السبب، ثم هذا سيكون الحل. ومع ذلك، نحن بحاجة إلى حساب رموز السبب الأخرى. لذلك نحن بحاجة إلى إنشاء هذا الاستعلام بأكمله أعلاه سلسلة واحدة كبيرة حيث أننا بناء أسباب العودة المحتملة كقائمة واحدة مفصولة بفواصل. I39m الذهاب لإظهار كامل رمز سكل T لتوليد (وتنفيذ) الاستعلام المطلوب. ثم I39ll كسر بها إلى أجزاء وشرح كل خطوة. أولا، هنا 39s رمز كامل لتوليد حيوي ما حصلت I39ve أعلاه. وهناك في الأساس خمس خطوات نحتاج إلى تغطيتها. الخطوة 1 . ونحن نعلم أن في مكان ما في هذا المزيج، ونحن بحاجة إلى إنشاء سلسلة لهذا في الاستعلام: ساليسامونت، السبب A، السبب B، السبب C، السبب D0160016001600160 ما يمكننا القيام به هو بنيت التعبير الجدول المشترك المؤقت الذي يجمع بين الثابت كوتساليس السلكية عمود كولومكوت مع قائمة فريدة من رموز السبب المحتملة. مرة واحدة لدينا ذلك في كت، يمكننا استخدام خدعة صغيرة لطيفة ل شمل باث (3939) لانهيار تلك الصفوف في سلسلة واحدة، وضع فاصلة أمام كل صف أن يقرأ الاستعلام، ومن ثم استخدام ستوف لاستبدال المرحلة الأولى من فاصلة مع مساحة فارغة. هذا هو خدعة التي يمكنك أن تجد في مئات من بلوق سكل. لذلك هذا الجزء الأول يبني سلسلة تسمى أكتيونسترينغ التي يمكننا استخدامها أكثر إلى أسفل. الخطوة 2 . ونحن نعلم أيضا أن we39ll تريد أن سوم أعمدة سبب جيندربيفوتد، جنبا إلى جنب مع العمود المبيعات القياسية. لذلك we39ll بحاجة إلى سلسلة منفصلة لذلك، الذي I39ll استدعاء سومسترينغ. I39ll ببساطة استخدام أكتيونسترينغ الأصلي، ثم ريبلاس الأقواس الخارجية مع بناء جملة سوم، بالإضافة إلى الأقواس الأصلية. الخطوة 3: الآن يبدأ العمل الحقيقي. باستخدام هذا الاستعلام الأصلي كنموذج، نريد إنشاء الاستعلام الأصلي (بدءا من ونيون من الجدولين)، ولكن استبدال أي مراجع للأعمدة المحورية مع السلاسل التي تم إنشاؤها ديناميكيا أعلاه. أيضا، في حين لم يكن مطلوبا تماما، I39ve أيضا إنشاء متغير ببساطة أي تركيبات تغذية عود النقل التي نريد تضمينها في الاستعلام الذي تم إنشاؤه (لقراءة). لذلك we39ll بناء الاستعلام بأكمله إلى متغير يسمى سكلبيفوتكيري. الخطوة 4. ونحن نواصل بناء الاستعلام مرة أخرى، تسلسل بناء الجملة يمكننا كوثارد-ويريكوت مع أكتيونسيلكتسترينغ (التي ولدت ديناميكيا لعقد كل قيم سبب عودة المحتملة) الخطوة 5. وأخيرا، we39ll توليد الجزء الأخير من الاستعلام المحوري، الذي يقرأ من 2 التعبير الجدول المشترك (بيفوتكت، من النموذج أعلاه) ويولد سيليكت النهائي للقراءة من بيفوتكت ودمجها مع 2 الثانية قراءة ضد بيفوتكت إلى تنفيذ مجموعات التجميع. وأخيرا، يمكننا كوتكسكوتكوت السلسلة باستخدام نظام سكل المخزنة بروك سبيكسكوتسول لذلك نأمل أن ترى أن عملية التالية لهذا النوع من الجهد هو تحديد ما الاستعلام النهائي سيكون، استنادا إلى مجموعة الحالية من البيانات والقيم (أي بنيت نموذج الاستعلام) كتابة التعليمات البرمجية T-سكل اللازمة لإنشاء نموذج الاستعلام هذا كسلسلة. يمكن القول إن الجزء الأكثر أهمية هو تحديد مجموعة فريدة من القيم التي يمكنك بيفوت لك 39، ومن ثم طيها في سلسلة واحدة باستخدام وظيفة ستوف و ل شمل باث (3939) خدعة حتى ماذا يكون في ذهني اليوم حسنا، على الأقل 13 البنود اثنين قبل صيف، كتبت مشروع بر الذي ركز (جزئيا) على دور التعليم وقيمة خلفية الفنون الليبرالية جيدة ليس فقط لصناعة البرمجيات ولكن حتى بالنسبة للصناعات الأخرى كذلك. واحدة من مواضيع هذا بدر خاص أكد وجهة نظر محورية ومستنيرة من مهندس البرمجيات الشهير ألين هولوب فيما يتعلق الفنون الحرة. يعبر إل (بصدق) عن رسالته: سلط الضوء على أوجه الشبه بين البرمجة ودراسة التاريخ، من خلال تذكير الجميع بأن التاريخ هو القراءة والكتابة (وإضافة إل، وتحديد الأنماط)، وتطوير البرمجيات هو أيضا القراءة والكتابة (ومرة أخرى، تحديد الأنماط ). وهكذا كتبت مقالة رأي ركزت على هذا الموضوع وغيره من الموضوعات ذات الصلة. ولكن حتى اليوم، لم أحصل أبدا على إما نشره. كل ما في كثير من الأحيان إد التفكير في تنقيحه، و إد حتى الجلوس لبضع دقائق وإجراء بعض التعديلات عليه. ولكن بعد ذلك الحياة بشكل عام سوف تحصل في الطريق و إد أبدا الانتهاء منه. لذلك ما تغير قبل بضعة أسابيع، كتب الكاتب كود مجلة زميل وزعيم الصناعة تيد نيوارد قطعة في عموده العادي، المدرب المبرمج، التي لفتت انتباهي. عنوان المقال هو على الفنون الليبرالية. وأنا أوصي أن الجميع قراءتها. تيد يناقش قيمة خلفية الفنون الحرة، والانقسام كاذبة بين خلفية الفنون الليبرالية والنجاح في تطوير البرمجيات، والحاجة إلى وريتيكومونيكات جيدا. ويتحدث عن بعض لقاءاته السابقة مع إدارة شؤون العاملين في الموارد البشرية فيما يتعلق بخلفيته التعليمية. كما يشدد على الحاجة إلى قبول والتكيف مع التغيرات في صناعتنا، فضلا عن السمات المميزة لنجاح البرمجيات المهنية (موثوق بها، والتخطيط للمستقبل، والتعلم للحصول على الصراع الأولي الماضي مع أعضاء الفريق الآخرين). لذلك لها قراءة كبيرة، وكذلك تيدس مقالات كود الأخرى ومدونات بلوق. كما أعادتني إلى التفكير في آرائي حول هذا الموضوع (والمواضيع الأخرى)، ودفعني أخيرا إلى إنهاء افتتاحيةي الخاصة. لذلك، في وقت متأخر أفضل من أبدا، وهنا بلدي الحالي الخبازين عشرات من تأملات: لدي قول مأثور: يتجمد الماء في 32 درجة. إذا كنت في دور التدريب، قد تعتقد أنك تفعل كل شيء في العالم لمساعدة شخص ما في الواقع، انهم يشعرون فقط درجة حرارة 34 درجة، وبالتالي الأمور أرينت ترسيخ بالنسبة لهم. في بعض الأحيان يستغرق سوى القليل من الجهد أو محفز إيديتشيميكال آخر أو منظور جديد مما يعني أن أولئك الذين لديهم تعليم سابق يمكن أن تعتمد على مصادر مختلفة. يتجمد الماء عند 32 درجة. بعض الناس يمكن الحفاظ على مستويات عالية من التركيز حتى مع غرفة مليئة الناس صاخبة. أنا لا أحد منهم أحيانا أحتاج إلى بعض الخصوصية للتفكير من خلال قضية حرجة. بعض الناس يصفون هذا كما كنت تعلم فلدي على المشي بعيدا عن ذلك. وبطريقة أخرى، بحثا عن الهواء نادر. هذا الأسبوع الماضي قضيت ساعات في نصف مضاءة، غرفة هادئة مع السبورة، حتى فهمت تماما مشكلة. وعندئذ فقط يمكنني التحدث مع المطورين الآخرين حول الحل. الرسالة هنا هو عدم الوعظ كيف يجب أن تذهب حول عملك من حل المشاكل ولكن بدلا للجميع لمعرفة نقاط القوة وما يعمل، واستخدامها لصالحك قدر الإمكان. بعض العبارات مثل الأظافر على السبورة بالنسبة لي. استخدامه بمثابة لحظة التدريس هو واحد. (لماذا هو مثل الأظافر على السبورة لأنه إذا كنت في دور التوجيه، يجب أن تكون عادة في تدريس الوضع لحظة على أي حال، ولكن بمهارة). هيريس آخر لا أستطيع أن أشرح حقا في الكلمات، ولكن أنا أفهم ذلك. هذا قد يبدو قليلا البرد، ولكن إذا كان الشخص حقا لا يمكن تفسير شيء في الكلمات، وربما لا يفهمون. بالتأكيد، يمكن للشخص أن يكون شعور غامض كيف يعمل شيء يمكنني خداع طريقي من خلال وصف كيف تعمل الكاميرا الرقمية ولكن الحقيقة هي أنني لا أفهم حقا كل ذلك بشكل جيد. هناك مجال دراسة يعرف باسم نظرية المعرفة (دراسة المعرفة). واحدة من الأسس الأساسية لفهم ما إذا كانت الكاميرا أو نمط التصميم - هو القدرة على إنشاء السياق، لتحديد سلسلة من الأحداث ذات الصلة، وخصائص أي مكونات على طول الطريق، وما إلى ذلك نعم، والفهم هو في بعض الأحيان العمل الشاق جدا ، ولكن الغوص في موضوع وكسرها بعيدا يستحق هذا الجهد. وحتى أولئك الذين يتجنبون الشهادة سيعترفون بأن عملية دراسة اختبارات الاعتماد ستساعد على سد الثغرات في المعرفة. مدير إدارة قاعدة البيانات هو أكثر عرضة لتوظيف مطور قاعدة البيانات الذين يمكن أن يتكلمون بشكل مؤقت (وبدون عناء) حول مستويات العزلة المعاملات والمشغلات، على عكس شخص من نوع يعرف عن ذلك ولكن يكافح لوصف استخدامها. ثيريس نتيجة طبيعية أخرى هنا. ويوصي تيد نيوارد بأن يقوم المطورون بالتحدث أمام الجمهور والتدوين وما إلى ذلك. أوافق 100. إن عملية التحدث أمام الجمهور والمدونات ستجبرك عمليا على البدء في التفكير في المواضيع وكسر التعاريف التي ربما تكون قد اتخذت بطريقة أخرى أمرا مفروغا منه. قبل بضع سنوات ظننت أنني فهمت بيان T-سكل ميرج بشكل جيد. ولكن فقط بعد الكتابة عن ذلك، والتحدث عن، وإدخال الأسئلة من الآخرين الذين لديهم وجهات النظر التي لم يحدث لي أن مستوى تفهم بلدي زيادة أضعافا مضاعفة. أعرف قصة مدير التوظيف الذي أجرى مقابلة مع أوثورديفيلوبر لموقف العقد. وكان مدير التوظيف في ازدراء من المنشورات بشكل عام، ونباح في مقدم الطلب، لذلك، إذا كنت ذاهب للعمل هنا، وكنت بدلا من ذلك كتابة الكتب أو كتابة التعليمات البرمجية نعم، إل منح أنه في أي صناعة سيكون هناك عدد قليل من الأكاديميين نقية. ولكن ما غاب عنه مدير التوظيف كان فرص تعزيز وشحذ مجموعات المهارات. أثناء تنظيف مربع قديم من الكتب، جئت عبر كنز من 1980s: المبرمجين في العمل. الذي يحتوي على مقابلات مع شاب صغير جدا بيل غيتس، راي أوزي، وغيرها من الأسماء المعروفة. كل مقابلة وكل البصيرة يستحق ثمن الكتاب. في رأيي، كانت المقابلة الأكثر إثارة للاهتمام مع بتلر لامبسون. الذي قدم بعض النصائح القوية. إلى الجحيم مع محو الأمية الكمبيوتر. لها مثير للسخرية على الاطلاق. دراسة الرياضيات. تعلم التفكير. اقرأ. كتابة. هذه الأشياء هي ذات قيمة أكثر ثباتا. تعلم كيفية إثبات النظريات: وهناك الكثير من الأدلة التي تراكمت على مر القرون التي تشير إلى أن هذه المهارة قابلة للتحويل إلى أشياء أخرى كثيرة. بتلر يتحدث الحقيقة. سوء إضافة إلى هذه النقطة تعلم كيفية لعب الشياطين دفاع ضد نفسك. وكلما يمكنك واقع - تحقق العمليات الخاصة بك والعمل، وأفضل حالا يكون عليك. جعل عالم الكمبيوتر الكبير ألين هولوب العلاقة بين تطوير البرمجيات والفنون الليبرالية على وجه التحديد، موضوع التاريخ. هنا كان له نقطة: ما هو التاريخ القراءة والكتابة. ما هو تطوير البرمجيات من بين أمور أخرى، القراءة والكتابة. اعتدت أن أعطي طلابي أسئلة مقالة T - سكل كما اختبارات الممارسة. طالب أحد الطلاب بأنني تصرفت أكثر مثل أستاذ القانون. حسنا، تماما مثل المدرب دوني هاسكينز قال في فيلم غلوري رود، طريقي صعب. إنني أؤمن إيمانا راسخا بأساس فكري قوي لأي مهنة. تماما مثل التطبيقات يمكن أن تستفيد من الأطر، يمكن للأفراد وعمليات التفكير الاستفادة من الأطر البشرية أيضا. هذا الأساس الأساسي للمنح الدراسية. هناك قصة أن يعود في 1970s، وسعت عب جهودهم التجنيد في الجامعات الكبرى من خلال التركيز على أفضل وألمع خريجي الفنون الحرة. حتى بعد ذلك أدركوا أن أفضل القراء والكتاب قد تصبح يوما ما المحللين بروغرامرزيستمز قوية. (لا تتردد في استخدام تلك القصة إلى أي نوع من الموارد البشرية الذين يصرون على أن المرشح يجب أن يكون على درجة علوم الكمبيوتر) والتحدث عن التاريخ: إذا لأي سبب آخر، من المهم أن نتذكر تاريخ إصدارات المنتجات إذا كان إم العمل في موقع العميل التي لا تزال تستخدم سكل سيرفر 2008 أو حتى (غاسب) سكل سيرفر 2005، يجب أن نتذكر ما تم تنفيذ الميزات في الإصدارات مع مرور الوقت. من أي وقت مضى الطبيب المفضل الذي كنت تحب لأن هيش أوضح الأشياء في سهل الإنجليزية، أعطاك الحقيقة على التوالي، وحصل ثقتكم للعمل على لكم هذه هي مهارات جنون. وهي نتيجة الخبرة والعمل الجاد الذي يستغرق سنوات وحتى عقود لزراعة. لا توجد ضمانات للنجاح في العمل التركيز على الحقائق، واتخاذ بعض المخاطر المحسوبة عندما كنت متأكدا من أنك يمكن أن نرى طريقك إلى خط النهاية، والسماح للرقائق تقع حيث قد، وأبدا يغيب عن بالنا من أن مجرد مثل هذا الطبيب الذي حصل ثقتك. على الرغم من أن بعض الأيام أقصر، أحاول علاج موكلي وبياناتهم كطبيب علاج المرضى. على الرغم من أن الطبيب يجعل المزيد من المال هناك العديد من كليتشس أنا أكره ولكن هيرس واحد أنا لا أكره: ليس هناك شيء مثل سؤال سيء. كمدرب سابق، شيء واحد الذي لفت غضبتي كان سماع شخص ينتقد شخص آخر لطرح سؤال مفترض، غبي. ويشير السؤال إلى أن الشخص يقر بأن لديها بعض الثغرات في المعرفة التي يبحثون عن تعبئتها. نعم، بعض الأسئلة هي أفضل صياغة من غيرها، وبعض الأسئلة تتطلب تأطير إضافية قبل أن يمكن الإجابة عليها. ولكن الرحلة من تشكيل سؤال إلى إجابة من المرجح أن تولد عملية عقلية نشطة في الآخرين. هناك كل الأشياء الجيدة. العديد من المناقشات الجيدة والمثمرة تنشأ مع سؤال غبي. أنا أعمل في جميع المجالات في سسيس، ساس، سرس، مدس، بس، شاريبوانت، باور بي، داكس جميع الأدوات في ميكروسوفت بي كومة. ما زلت أكتب بعض التعليمات البرمجية من وقت لآخر. ولكن تخمين ما كنت لا تزال تنفق الكثير من الوقت في كتابة التعليمات البرمجية سكل T إلى البيانات الشخصية كجزء من عملية الاكتشاف. يجب أن يكون جميع مطوري التطبيقات جيدة تي-سكل القطع. يكتب تيد نيوارد (بشكل صحيح) عن الحاجة للتكيف مع التغيرات التكنولوجية. إل إضافة إلى ذلك الحاجة إلى التكيف مع التغييرات عامل العميل. وتغير الشركات قواعد العمل. تستحوذ الشركات على شركات أخرى (أو تصبح هدفا للاستحواذ). الشركات أخطاء في التواصل متطلبات الأعمال والمواصفات. نعم، يمكننا أحيانا أن تلعب دورا في المساعدة على إدارة تلك التغييرات، وأحيانا كانت الطاير، وليس الزجاج الأمامي. هذه في بعض الأحيان تسبب ألم كبير للجميع، وخاصة I. T. اشخاص. هذا هو السبب في وجود حقيقة الحياة يجب علينا التعامل معها. تماما مثل أي مطور يكتب التعليمات البرمجية خالية من الأخطاء في كل مرة، لا I. T. شخص يتعامل بشكل جيد مع تغيير كل مرة واحدة. واحدة من أكبر الصراعات إيف كان في بلدي 28 عاما في هذه الصناعة يظهر الصبر وضبط النفس عندما التغييرات تطير من العديد من الاتجاهات المختلفة. هنا هو المكان الذي يمكن أن تقدم فيه اقتراحاتي السابقة حول البحث عن الهواء المروع. إذا كنت تستطيع إدارة لاستيعاب التغييرات في عملية التفكير الخاص بك، ودون الشعور طغت، والاحتمالات هي أن تكون رصيدا كبيرا. في الأشهر ال 15 الماضية كان إيف للتعامل مع كمية كبيرة من التغيير المهني. كان من الصعب جدا في بعض الأحيان، ولكن إيف حلت أن التغيير سيكون هو القاعدة و إيف حاولت قرص بلدي عادات أفضل ما أستطيع للتعامل مع التغيير المتكرر (وغير مؤكد). من الصعب، من الصعب جدا. ولكن كما قال مدرب جيمي دوغان في فيلم "رابطة من تلقاء نفسها: بالطبع من الصعب. إذا لم يكن من الصعب، الجميع سوف تفعل ذلك. من الصعب، هو ما يجعلها كبيرة. رسالة قوية. وكان الحديث في هذه الصناعة على مدى السنوات القليلة الماضية حول السلوك في المؤتمرات المهنية (والسلوك في هذه الصناعة ككل). وقد كتب العديد من الكتاب المحترمين افتتاحية جيدة جدا حول هذا الموضوع. هيريس مدخلي، لما قيمتها. Its a message to those individuals who have chosen to behave badly: Dude, it shouldnt be that hard to behave like an adult. A few years ago, CoDe Magazine Chief Editor Rod Paddock made some great points in an editorial about Codes of Conduct at conferences. Its definitely unfortunate to have to remind people of what they should expect out of themselves. But the problems go deeper. A few years ago I sat on a five-person panel (3 women, 2 men) at a community event on Women in Technology. The other male stated that men succeed in this industry because the Y chromosome gives men an advantage in areas of performance. The individual who made these remarks is a highly respected technology expert, and not some bozo making dongle remarks at a conference or sponsoring a programming contest where first prize is a date with a bikini model. Our world is becoming increasingly polarized (just watch the news for five minutes), sadly with emotion often winning over reason. Even in our industry, recently I heard someone in a position of responsibility bash software tool XYZ based on a ridiculous premise and then give false praise to a competing tool. So many opinions, so many arguments, but heres the key: before taking a stand, do your homework and get the facts . Sometimes both sides are partly rightor wrong. Theres only one way to determine: get the facts. As Robert Heinlein wrote, Facts are your single clue get the facts Of course, once you get the facts, the next step is to express them in a meaningful and even compelling way. Theres nothing wrong with using some emotion in an intellectual debate but it IS wrong to replace an intellectual debate with emotion and false agenda. A while back I faced resistance to SQL Server Analysis Services from someone who claimed the tool couldnt do feature XYZ. The specifics of XYZ dont matter here. I spent about two hours that evening working up a demo to cogently demonstrate the original claim was false. In that example, it worked. I cant swear it will always work, but to me thats the only way. Im old enough to remember life at a teen in the 1970s. Back then, when a person lost hisher job, (often) it was because the person just wasnt cutting the mustard. Fast-forward to today: a sad fact of life is that even talented people are now losing their jobs because of the changing economic conditions. Theres never a full-proof method for immunity, but now more than ever its critical to provide a high level of what I call the Three Vs (value, versatility, and velocity) for your employerclients. I might not always like working weekends or very late at night to do the proverbial work of two people but then I remember there are folks out there who would give anything to be working at 1 AM at night to feed their families and pay their bills. Always be yourselfyour BEST self. Some people need inspiration from time to time. Heres mine: the great sports movie, Glory Road. If youve never watched it, and even if youre not a sports fan I can almost guarantee youll be moved like never before. And Ill close with this. If you need some major motivation, Ill refer to a story from 2006. Jason McElwain, a high school student with autism, came off the bench to score twenty points in a high school basketball game in Rochester New York. Heres a great YouTube video. His mother said it all . This is the first moment Jason has ever succeeded and is proud of himself. I look at autism as the Berlin Wall. He cracked it. To anyone who wanted to attend my session at todays SQL Saturday event in DC I apologize that the session had to be cancelled. I hate to make excuses, but a combination of getting back late from Detroit (client trip), a car thats dead (blown head gasket), and some sudden health issues with my wife have made it impossible for me to attend. Back in August, I did the same session (ColumnStore Index) for PASS as a webinar. You can go to this link to access the video (itll be streamed, as all PASS videos are streamed) The link does require that you fill out your name and email address, but thats it. And then you can watch the video. Feel free to contact me if you have questions, at kgoffkevinsgoff November 15, 2013 Getting started with Windows Azure and creating SQL Databases in the cloud can be a bit daunting, especially if youve never tried out any of Microsofts cloud offerings. Fortunately, Ive created a webcast to help people get started. This is an absolute beginners guide to creating SQL Databases under Windows Azure. It assumes zero prior knowledge of Azure. You can go to the BDBI Webcasts of this website and check out my webcast (dated 11102013). Or you can just download the webcast videos right here: here is part 1 and here is part 2. You can also download the slide deck here. November 03, 2013 Topic this week: SQL Server Snapshot Isolation Levels, added in SQL Server 2005. To this day, there are still many SQL developers, many good SQL developers who either arent aware of this feature, or havent had time to look at it. Hopefully this information will help. Companion webcast will be uploaded in the next day look for it in the BDBI Webcasts section of this blog. October 26, 2013 Im going to start a weekly post of T-SQL tips, covering many different versions of SQL Server over the years Heres a challenge many developers face. Ill whittle it down to a very simple example, but one where the pattern applies to many situations. Suppose you have a stored procedure that receives a single vendor ID and updates the freight for all orders with that vendor id. create procedure dbo. UpdateVendorOrders update Purchasing. PurchaseOrderHeader set Freight Freight 1 where VendorID VendorID Now, suppose we need to run this for a set of vendor IDs. Today we might run it for three vendors, tomorrow for five vendors, the next day for 100 vendors. We want to pass in the vendor IDs. If youve worked with SQL Server, you can probably guess where Im going with this. The big question is how do we pass a variable number of Vendor IDs Or, stated more generally, how do we pass an array, or a table of keys, to a procedure Something along the lines of exec dbo. UpdateVendorOrders SomeListOfVendors Over the years, developers have come up with different methods: Going all the way back to SQL Server 2000, developers might create a comma-separated list of vendor keys, and pass the CSV list as a varchar to the procedure. The procedure would shred the CSV varchar variable into a table variable and then join the PurchaseOrderHeader table to that table variable (to update the Freight for just those vendors in the table). I wrote about this in CoDe Magazine back in early 2005 (code-magazinearticleprint. aspxquickid0503071ampprintmodetrue. Tip 3) In SQL Server 2005, you could actually create an XML string of the vendor IDs, pass the XML string to the procedure, and then use XQUERY to shred the XML as a table variable. I also wrote about this in CoDe Magazine back in 2007 (code-magazinearticleprint. aspxquickid0703041ampprintmodetrue. Tip 12)Also, some developers will populate a temp table ahead of time, and then reference the temp table inside the procedure. All of these certainly work, and developers have had to use these techniques before because for years there was NO WAY to directly pass a table to a SQL Server stored procedure. Until SQL Server 2008 when Microsoft implemented the table type. This FINALLY allowed developers to pass an actual table of rows to a stored procedure. Now, it does require a few steps. We cant just pass any old table to a procedure. It has to be a pre-defined type (a template). So lets suppose we always want to pass a set of integer keys to different procedures. One day it might be a list of vendor keys. Next day it might be a list of customer keys. So we can create a generic table type of keys, one that can be instantiated for customer keys, vendor keys, etc. CREATE TYPE IntKeysTT AS TABLE ( IntKey int NOT NULL ) So Ive created a Table Typecalled IntKeysTT . Its defined to have one column an IntKey. Nowsuppose I want to load it with Vendors who have a Credit Rating of 1..and then take that list of Vendor keys and pass it to a procedure: DECLARE VendorList IntKeysTT INSERT INTO VendorList SELECT BusinessEntityID from Purchasing. Vendor WHERE CreditRating 1 So, I now have a table type variable not just any table variable, but a table type variable (that I populated the same way I would populate a normal table variable). Its in server memory (unless it needs to spill to tempDB) and is therefore private to the connectionprocess. OK, can I pass it to the stored procedure now Well, not yet we need to modify the procedure to receive a table type. Heres the code: create procedure dbo. UpdateVendorOrdersFromTT IntKeysTT IntKeysTT READONLY update Purchasing. PurchaseOrderHeader set Freight Freight 1 FROM Purchasing. PurchaseOrderHeader JOIN IntKeysTT TempVendorList ON PurchaseOrderHeader. VendorID Te mpVendorList. IntKey Notice how the procedure receives the IntKeysTT table type as a Table Type (again, not just a regular table, but a table type). It also receives it as a READONLY parameter. You CANNOT modify the contents of this table type inside the procedure. Usually you wont want to you simply want to read from it. Well, now you can reference the table type as a parameter and then utilize it in the JOIN statement, as you would any other table variable. لذلك هناك لديك. A bit of work to set up the table type, but in my view, definitely worth it. Additionally, if you pass values from , youre in luck. You can pass an ADO data table (with the same tablename property as the name of the Table Type) to the procedure. For developers who have had to pass CSV lists, XML strings, etc. to a procedure in the past, this is a huge benefit. Finally I want to talk about another approach people have used over the years. SQL Server Cursors. At the risk of sounding dogmatic, I strongly advise against Cursors, unless there is just no other way. Cursors are expensive operations in the server, For instance, someone might use a cursor approach and implement the solution this way: DECLARE VendorID int DECLARE dbcursor CURSOR FASTFORWARD FOR SELECT BusinessEntityID from Purchasing. Vendor where CreditRating 1 FETCH NEXT FROM dbcursor INTO VendorID WHILE FETCHSTATUS 0 EXEC dbo. UpdateVendorOrders VendorID FETCH NEXT FROM dbcursor INTO VendorID The best thing Ill say about this is that it works. And yes, getting something to work is a milestone. But getting something to work and getting something to work acceptably are two different things. Even if this process only takes 5-10 seconds to run, in those 5-10 seconds the cursor utilizes SQL Server resources quite heavily. Thats not a good idea in a large production environment. Additionally, the more the of rows in the cursor to fetch and the more the number of executions of the procedure, the slower it will be. When I ran both processes (the cursor approach and then the table type approach) against a small sampling of vendors (5 vendors), the processing times where 260 ms and 60 ms, respectively. So the table type approach was roughly 4 times faster. But then when I ran the 2 scenarios against a much larger of vendors (84 vendors), the different was staggering 6701 ms versus 207 ms, respectively. So the table type approach was roughly 32 times faster. Again, the CURSOR approach is definitely the least attractive approach. Even in SQL Server 2005, it would have been better to create a CSV list or an XML string (providing the number of keys could be stored in a scalar variable). But now that there is a Table Type feature in SQL Server 2008, you can achieve the objective with a feature thats more closely modeled to the way developers are thinking specifically, how do we pass a table to a procedure Now we have an answer Hope you find this feature help. Feel free to post a comment. SQL Server disk performance metrics Part 2 other important disk performance measures In the previous part of the SQL Server performance metrics series, we presented the most important and useful disk performance metrics. Now, well show other important disk performance measures Current Disk Queue Length Indicates the number of disk requests that are currently waiting as well as requests currently being serviced. Subject to wide variations unless the workload has achieved a steady state and you have collected a sufficient number of samples to establish a pattern. 1 The metric shows how many IO operations are waiting to be written to or read from the hard drive and how many are currently processed. If the hard drive is not available, these operations are queued and will be processed when disk becomes available. The whole disk subsystem has a single queue The Current Disk Queue Length metric in Windows Performance Monitor is available for both physical and logical disk. In some earlier versions of Performance Monitor, this counter was named Disk Queue Length The Current Disk Queue Length value should be less than 2 per disk spindle. Note that this is not per logical, but per physical disk. If larger, this indicates a potential disk bottleneck, so further investigation and monitoring other disk metrics is recommended. Start with monitoring Disk Time (explained below). Frequent peaks should also be investigated Disk array systems such as RAID or SAN have a large number of disks and controllers, which makes queues on such systems shorter. Because the metric doesnt indicate queuing per disk, but for the whole array, some DBAs consider that monitoring Current Disk Queue Length on disk arrays is not needed Another scenario where Current Disk Queue Length can be misleading is when data is stored in the disk cache. It will be reported as being queued for writing and thus the Current Disk Queue Length value will be higher than actual Average Disk Queue Length The Average Disk Queue Length metric shows the information similar to Current Disk Queue Length . only the value is not current but average over a specific time period. The threshold is the same as for the previous metric up to 2 per disk. For disk systems, the recommended value is less than 2 per individual disk drive in an array. For example, in a 6 disk array the Current Disk Queue Length value of 12 means that the queue is 2 per disk There are two more metrics similar to Average Disk Queue Length 8211 Average Disk Read Queue Length and Average Disk Write Queue Length . As their names indicate they show the average queue length for operations waiting for disk to be read or written Disk Time This counter indicates a disk problem, but must be observed in conjunction with the Current Disk Queue Length counter to be truly informative. Recall also that the disk could be a bottleneck prior to the Disk Time reaching 100 2 The Disk Time metric indicates how busy the disk is servicing read and write requests, but as stated above, its not a clear indication of a problem, as its values can be normal while theres a serious disk performance issue. Its value is the Average Disk Queue Length value represented in percents (i. e. multiplied by 100). If Average Disk Queue Length is 1, Disk Time is 100 What can be confusing is that Disk Time values can be over 100, which isnt logical. This happens if the Average Disk Queue Length value is greater than 1. If Average Disk Queue Length is 3, Disk Time is 300, which doesnt mean that processes are using 3 times more disk time than available, nor that there is a bottleneck If you have a hard disk array, the total disk time for all disks is shown, without the indication of how many disks are available and what disk is having the highest Disk Time . For example, Disk Time equal to 500 might indicate good performance (in case you have 6 disks), or extremely bad (in case you have only 1 disk). You cannot tell without knowing the machine hardware As this counter can be misleading, some DBAs dont use it as there are other more straightforward and indicative metrics that show disk performance If the value is higher than 90 per disk, additional investigation is needed. First, check the Current Disk Queue Length value. If its higher than the threshold (2 per physical disk), monitor if the high values occur frequently. If the machine is not used only for SQL Server, other resource-intensive applications might cause disk bottlenecks, so SQL Server performance will be suffering. If this is the case, consider moving these applications to another machine and using a dedicated machine for SQL Server only If this is not the case, or cannot be done, consider moving some of the files to another disk or machine archive databases, database and transaction log backups, using a faster disk, or adding additional disks to an array Disk Read Time and the Disk Write Time The Disk Read Time and Disk Write Time metrics are similar to Disk Time . just showing the operations read from or written to disk, respectively. They are actually the Average Disk Read Queue Length and Average Disk Write Queue Length values presented in percents. The values these metrics show can be equally misleading as Disk Time On a three disk array system, if one disk reads 50 of the time ( Disk Read Time 50), the other one reads 85 of the time, and the third one is idle, Disk Read Time is 135 and Average Disk Read Queue Length 1.35. At a first glance, Disk Read Time equal to 135 looks like a problem, but its not. It doesnt mean that disks are busy 135 of the time. To get a real value, you should divide the value with the number of disks and youll get 1363 45, which indicates normal performance Idle Time The disk is idle when its not processing read and write requests This measures the percentage of time the disk was idle during the sample interval. If this counter falls below 20 percent, the disk system is saturated. You may consider replacing the current disk system with a faster disk system. 3 If the value is lower than 20, disk is not able to service all read and write requests in a timely fashion. Before opting for disk replacement, check whether its possible to remove some applications to another machine Free Space Besides Windows Performance Monitor, this metric is available in Windows Explorer in the computer and disk Properties tabs. While Performance Monitor shows the percentage of available free disk space, Windows Explorer shows the amount in GB This measures the percentage of free space on the selected logical disk drive. Take note if this falls below 15 percent, as you risk running out of free space for the OS to store critical files. One obvious solution here is to add more disk space. 3 If the value shows sudden peaks without obvious reasons, further investigation is required Unlike most of the memory and processor SQL Server performance metrics, disk metrics can be quite deceptive. They might not clearly indicate a performance problem their values might be OK, when actually there is a serious disk issue, while their strangely high values might show normal performance, as they show values for an array of disks. Then it comes to array metrics, hardware configuration knowledge is necessary to read them correctly. Despite these disk metrics downsides, they are necessary for SQL Server performance troubleshootingPostgreSQL vs. MS SQL Server 0. Whats this all about I work as a data analyst in a global professional services firm (one you have certainly heard of). I have been doing this for about a decade. I have spent that decade dealing with data, database software, database hardware, database users, database programmers and data analysis methods, so I know a fair bit about these things. I frequently come into contact with people who know very little about these things ndash although some of them dont realise it . Over the years I have discussed the issue of PostgreSQL vs. MS SQL Server many, many times. A well-known principle in IT says: if youre going to do it more than once, automate it . This document is my way of automating that conversation. Unless otherwise stated I am referring to PostgreSQL 9.3 and MS SQL Server 2014, even though my experience with MS SQL Server is with versions 2008 R2 and 2012 ndash for the sake of fairness and relevance I want to compare the latest version of PostgreSQL to the latest version of MS SQL Server. Where I have made claims about MS SQL Server I have done my best to check that they apply to version 2014 by consulting Microsofts own documentation ndash although, for reasons I will get to. I have also had to rely largely on Google, Stack Overflow and the users of the internet. I know its not scientifically rigorous to do a comparison like this when I dont have equal experience with both databases, but this is not an academic exercise ndash its a real-world comparison. I have done my honest best to get my facts about MS SQL Server right ndash we all know it is impossible to bullshit the whole internet. If I find out that Ive got something wrong, Ill fix it. I am comparing the two databases from the point of view of a data analyst. Maybe MS SQL Server kicks PostgreSQLs arse as an OLTP backend (although I doubt it), but thats not what Im writing about here, because Im not an OLTP developerDBAsysadmin. Finally, there is an email address at top right. Do please use it if you wish I will do my best to respond. DISCLAIMER: all the subjective opinions in here are strictly my own. 1. Why PostgreSQL is way, way better than MS SQL Server Oops, spoiler alert. This section is a comparison of the two databases in terms of features relevant to data analytics. 1.1. CSV support CSV is the de facto standard way of moving structured (i. e. tabular) data around. All RDBMSes can dump data into proprietary formats that nothing else can read, which is fine for backups, replication and the like, but no use at all for migrating data from system X to system Y. A data analytics platform has to be able to look at data from a wide variety of systems and produce outputs that can be read by a wide variety of systems. In practice, this means that it needs to be able to ingest and excrete CSV quickly, reliably, repeatably and painlessly. Lets not understate this: a data analytics platform which cannot handle CSV robustly is a broken, useless liability. PostgreSQLs CSV support is top notch. The COPY TO and COPY FROM commands support the spec outlined in RFC4180 (which is the closest thing there is to an official CSV standard) as well as a multitude of common and not-so-common variants and dialects. These commands are fast and robust. When an error occurs, they give helpful error messages. Importantly, they will not silently corrupt, misunderstand or alter data. If PostgreSQL says your import worked, then it worked properly. The slightest whiff of a problem and it abandons the import and throws a helpful error message. (This may sound fussy or inconvenient, but it is actually an example of a well-established design principle. It makes sense: would you rather find out your import went wrong now, or a month from now when your client complains that your results are off) MS SQL Server can neither import nor export CSV. Most people dont believe me when I tell them this. Then, at some point, they see for themselves. Usually they observe something like: MS SQL Server silently truncating a text field MS SQL Servers text encoding handling going wrong MS SQL Server throwing an error message because it doesnt understand quoting or escaping (contrary to popular belief, quoting and escaping are not exotic extensions to CSV. They are fundamental concepts in literally every human-readable data serialisation specification. Dont trust anyone who doesnt know what these things are) MS SQL Server exporting broken, useless CSV Microsofts horrendous documentation. How did they manage to overcomplicate something as simple as CSV This is especially baffling because CSV parsers are trivially easy to write (I wrote one in C and plumbed it into PHP a year or two ago, because I wasnt happy with its native CSV-handling functions. The whole thing took perhaps 100 lines of code and three hours ndash two of which were spent getting to grips with SWIG. which was new to me at the time). If you dont believe me, download this correctly-formatted, standards-compliant UTF-8 CSV file and use MS SQL Server to calculate the average string length (i. e. number of characters) of the last column in this file (it has 50 columns). Go on, try it. (The answer youre looking for is exactly 183.895.) Naturally, determining this is trivially easy in PostgreSQL ndash in fact, the most time-consuming bit is creating a table with 50 columns to hold the data. Poor understanding of CSV seems to be endemic at Microsoft that file will break Access and Excel too. Sad but true: some database programmers I know recently spent a lot of time and effort writing Python code which sanitises CSV in order to allow MS SQL Server to import it. They werent able to avoid changing the actual data in this process, though. This is as crazy as spending a fortune on Photoshop and then having to write some custom code to get it to open a JPEG, only to find that the image has been altered slightly. 1.2. Ergonomics Every data analytics platform worth mentioning is Turing complete, which means, give or take, that any one of them can do anything that any other one can do. There is no such thing as you can do X in software A but you cant do X in software B. You can do anything in anything ndash all that varies is how hard it is. Good tools make the things you need to do easy poor tools make them hard. Thats what it always boils down to. (This is all conceptually true, if not literally true - for example, no RDBMS I know of can render 3D graphics. But any one of them can emulate any calculation a GPU can perform.) PostgreSQL is clearly written by people who actually care about getting stuff done . MS SQL Server feels like it was written by people who never have to actually use MS SQL Server to achieve anything. Here are a few examples to back this up: PostgreSQL supports DROP TABLE IF EXISTS. which is the smart and obvious way of saying if this table doesnt exist, do nothing, but if it does, get rid of it. Something like this: Heres how you have to do it in MS SQL Server: Yes, its only one extra line of code, but notice the mysterious second parameter to the OBJECTID function. You need to replace that with NV to drop a view. Its NP for a stored procedure. I havent learned all the different letters for all the different types of database objects (why should I have to) Notice also that the table name is repeated unnecessarily. If your concentration slips for a moment, its dead easy to do this: See whats happened there This is a reliable source of annoying, time-wasting errors. PostgreSQL supports DROP SCHEMA CASCADE. which drops a schema and all the database objects inside it. This is very, very important for a robust analytics delivery methodology, where tear-down-and-rebuild is the underlying principle of repeatable, auditable, collaborative analytics work. There is no such facility in MS SQL Server. You have to drop all the objects in the schema manually, and in the right order . because if you try to drop an object on which another object depends, MS SQL Server simply throws an error. This gives an idea of how cumbersome this process can be. PostgreSQL supports CREATE TABLE AS. A wee example: This means you can highlight everything but the first line and execute it, which is a useful and common task when developing SQL code. In MS SQL Server, table creation goes like this instead: So, to execute the plain SELECT statement, you have to comment out or remove the INTO bit. Yes, commenting out two lines is easy thats not the point. The point is that in PostgreSQL you can perform this simple task without modifying the code and in MS SQL Server you cant, and that introduces another potential source of bugs and annoyances. In PostgreSQL, you can execute as many SQL statements as you like in one batch as long as youve ended each statement with a semicolon, you can execute whatever combination of statements you like. For executing automated batch processes or repeatable data builds or output tasks, this is critically important functionality. In MS SQL Server, a CREATE PROCEDURE statement cannot appear halfway through a batch of SQL statements. Theres no good reason for this, its just an arbitrary limitation. It means that extra manual steps are often required to execute a large batch of SQL. Manual steps increase risk and reduce efficiency. PostgreSQL supports the RETURNING clause, allowing UPDATE. INSERT and DELETE statements to return values from affected rows. This is elegant and useful. MS SQL Server has the OUTPUT clause, which requires a separate table variable definition to function. This is clunky and inconvenient and forces a programmer to create and maintain unnecessary boilerplate code. PostgreSQL supports string quoting, like so: This is extremely useful for generating dynamic SQL because (a) it allows the user to avoid tedious and unreliable manual quoting and escaping when literal strings are nested and (b) since text editors and IDEs tend not to recogniise as a string delimiter, syntax highlighting remains functional even in dynamic SQL code. PostgreSQL lets you use procedural languages simply by submitting code to the database engine you write procedural code in Python or Perl or R or JavaScript or any of the other supported languages (see below) right next to your SQL, in the same script. This is convenient, quick, maintainable, easy to review, easy to reuse and so on. In MS SQL Server, you can either use the lumpy, slow, awkward T-SQL procedural language, or you can use a language to make an assembly and load it into the database. This means your code is in two separate places and you have to go through a sequence of GUI-based manual steps to alter it. It makes packaging up all your stuff into one place harder and more error-prone. And there are plenty more examples out there. Each of these things, in isolation, may seem like a relatively minor niggle however, the overall effect is that getting real work done in MS SQL Server is significantly harder and more error-prone than in PostgreSQL, and data analysts spend valuable time and energy on workarounds and manual processes instead of focusing on the actual problem. Update: it was pointed out to me that one really useful feature MS SQL Server has which PostgreSQL lacks is the ability to declare variables in SQL scripts. Like this: PostgreSQL cant do this. I wish it could, because there are an awful lot of uses for such a feature. 1.3. You can run PostgreSQL in Linux, BSD etc. (and, of course, Windows) Anyone who follows developments in IT knows that cross-platform is a thing now. Cross-platform support is arguably the killer feature of Java, which is actually a somewhat lumpy, ugly programming language, but nonetheless enormously successful, influential and widespread. Microsoft no longer has the monopoly it once enjoyed on the desktop, thanks to the rise of Linux and Apple. IT infrastructures are increasingly heterogeneous thanks to the flexibility of cloud services and easy access to high-performance virtualisation technology. Cross-platform software is about giving the user control over their infrastructure. (At work I currently manage several PostgreSQL databases, some in Windows and some in Ubuntu Linux. I and my colleagues freely move code and database dumps between them. We use Python and PHP because they also work in both operating systems. It all just works.) Microsofts policy is and always has been vendor lock-in. They dont open-source their code they dont provide cross-platform versions of their software they even invented a whole ecosystem. NET, designed to draw a hard line between Microsoft users and non-Microsoft users. This is good for them, because it safeguards their revenue. It is bad for you, the user, because it restricts your choices and creates unnecessary work for you. ( Update: a couple of days after I published this, Microsoft made me look like a prat by announcing that it was open-sourcing . Thats a great step, but lets not crack open the Bollinger just yet .) Now, this is not a Linux vs. Windows document, although Im sure Ill end up writing one of those at some point. Suffice it to say that, for real IT work, Linux (and the UNIX-like family: Solaris, BSD etc.) leaves Windows in the dust. UNIX-like operating systems dominate the server market, cloud services, supercomputing (in this field its a near-monopoly) and technical computing, and with good reason ndash these systems are designed by techies for techies. As a result they trade user-friendliness for enormous power and flexibility. A proper UNIX-like OS is not just a nice command line ndash it is an ecosystem of programs, utilities, functionality and support that makes getting real work done efficient and enjoyable. A competent Linux hacker can achieve in a single throwaway line of Bash script a task which would be arduous and time-consuming in Windows. (Example: the other day I was looking through a friends film collection and he said he thought the total number of files in the file system was high, considering how many films he had, and he wondered if maybe he had accidentally copied a large folder structure into one of his film folders. I did a recursive count of files-per-folder for him like this: The whole thing took about a minute to write and a second to run. It confirmed that some of his folders had a problem and told him which ones they were. How would you do this in Windows) For data analytics, an RDBMS doesnt exist in a vacuum it is part of a tool stack. Therefore its environment matters. MS SQL Server is restricted to Windows, and Windows is simply a poor analytics environment. 1.4. Procedural language features This is a biggie. Pure declarative SQL is good at what it was designed for ndash relational data manipulation and querying. You quickly reach its limits if you try to use it for more involved analytical processes, such as complex interest calculations, time series analysis and general algorithm design. SQL database providers know this, so almost all SQL databases implement some kind of procedural language. This allows a database user to write imperative-style code for more complex or fiddly tasks. PostgreSQLs procedural language support is exceptional . Its impossible to do justice to it in a short space, but heres a sample of the goods. Any of these procedural languages can be used for writing stored procedures and functions or simply dumped into a block of code to be executed inline. PLPGSQL: this is PostgreSQLs native procedural language. Its like Oracles PLSQL, but more modern and feature-complete. PLV8: the V8 JavaScript engine from Google Chrome is available in PostgreSQL. This engine is stable, feature-packed and absurdly fast ndash often approaching the execution speed of compiled, optimised C. Combine that with PostgreSQLs native support for the JSON data type (see below) and you have ultimate power and flexibility in a single package. Even better, PLV8 supports global (i. e. cross-function call) state, allowing the user to selectively cache data in RAM for fast random access. Suppose you need to use 100,000 rows of data from table A on each of 1,000,000 rows of data from table B. In traditional SQL, you either need to join these tables (resulting in a 100bn row intermediate table, which will kill any but the most immense server) or do something akin to a scalar subquery (or, worse, cursor-based nested loops), resulting in crippling IO load if the query planner doesnt read your intentions properly. In PLV8 you simply cache table A in memory and run a function on each of the rows of table B ndash in effect giving you RAM-quality access (negligible latency and random access penalty no non-volatile IO load) to the 100k-row table. I did this on a real piece of work recently ndash my PostgreSQLPLV8 code was about 80 times faster than the MS T-SQL solution and the code was much smaller and more maintainable. Because it took about 23 seconds instead of half an hour to run, I was able to run 20 run-test-modify cycles in an hour, resulting in feature-complete, properly tested, bug-free code. Look here for more detail on this. (All those run-test-modify cycles were only possible because of DROP SCHEMA CASCADE and freedom to execute CREATE FUNCTION statements in the middle of a statement batch, as explained above. See how nicely it all fits together) PLPython: you can use full Python in PostgreSQL. Python2 or Python 3, take your pick, and yes, you get the enormous ecosystem of libraries for which Python is justifiably famous. Fancy running a SVM from scikit-learn or some arbitrary-precision arithmetic provided by gmpy2 in the middle of a SQL query No problem PLPerl: Perl has been falling out of fashion for some time, but its versatility earned it a reputation as the Swiss army knife of programming languages. In PostgreSQL you have full Perl as a procedural language. PLR: R is the de facto standard statistical programming environment in academia and data science, and with good reason - it is free, robust, fully-featured and backed by an enormous library of high-quality plugins and add-ons. PostgreSQL lets you use R as a procedural language. Java, Lua, sh, Tcl, Ruby and PHP are also supported as procedural languages in PostgreSQL. C: doesnt quite belong in this list because you have to compile it separately, but its worth a mention. In PostgreSQL it is trivially easy to create functions which execute compiled, optimised C (or C or assembler) in the database backend. This is a power user feature which provides unrivalled speed and fine control of memory management and resource usage for tasks where performance is critical. I have used this to implement a complex, stateful payment processing algorithm operating on a million rows of data per second ndash and that was on a desktop PC. MS SQL Servers inbuilt procedural language (part of their T-SQL extension to SQL) is clunky, slow and feature-poor. It is also prone to subtle errors and bugs, as Microsofts own documentation sometimes acknowledges. I have never met a database user who likes the T-SQL procedural language. What about the fact that you can make assemblies in languages and then use them in MS SQL Server This doesnt count as procedural language support because you cant submit this code to the database engine directly. Manageability and ergonomics are critically important. Inserting some Python code inline in your database query is easy and convenient firing up Visual Studio, managing projects and throwing DLL files around (all in GUI-based processes which cannot be properly scripted, version-controlled, automated or reviewed) is awkward, error-prone and non-scalable. In any case, this mechanism is limited to languages. 1.5. Native regular expression support Regular expressons (regexen or regexes) are as fundamental to analytics work as arithmetic ndash they are the first choice (and often only choice) for a huge variety of text processing tasks. A data analytics tool without regex support is like a bicycle without a saddle ndash you can still use it, but its painful. PostgreSQL has smashing out-of-the-box support for regex. Some examples: Get all lines starting with a repeated digit followed by a vowel: Get the first isolated hex string occurring in a field: Break a string on whitespace and return each fragment in a separate row: Case-insensitively find all words in a string with at least 10 letters: MS SQL Server has LIKE. SUBSTRING. PATINDEX and so on, which are not comparable to proper regex support (if you doubt this, try implementing the above examples using them). There are third-party regex libraries for MS SQL Server theyre just not as good as PostgreSQLs support, and the need to obtain and install them separately adds admin overhead. Note also that PostgreSQLs extensive procedural language support also gets you several other regex engines and their various features - e. g. Pythons regex library provides the added power of positive and negative lookbehind assertions. This is in keeping with the general theme of PostgreSQL giving you all the tools you need to actually get things done. 1.6. Custom aggregate functions This is a feature that, technically, is offered by both PostgreSQL and MS SQL Server. The implementations differ hugely, though. In PostgreSQL, custom aggregates are convenient and simple to use, resulting in fast problem-solving and maintainable code: Elegant, eh A custom aggregate is specified in terms of an internal state and a way to modify that state when we push new values into the aggregate function. In this case we start each customer off with zero balance and no interest accrued, and on each day we accrue interest appropriately and account for payments and withdrawals. We compound the interest on the 1st of every month. Notice that the aggregate accepts an ORDER BY clause (since, unlike SUM. MAX and MIN. this aggregate is order-dependent) and PostgreSQL provides operators for extracting values from JSON objects. So, in 28 lines of code weve created the framework for monthly compounding interest on bank accounts and used it to calculate final balances. If features are to be added to the methodology (e. g. interest rate modifications depending on debitcredit balance, detection of exceptional circumstances), its all right there in the transition function and is written in an appropriate language for implementing complex logic. (Tragic side-note: I have seen large organisations spend tens of thousands of pounds over weeks of work trying to achieve the same thing using poorer tools.) MS SQL Server, on the other hand, makes it absurdly difficult . Incidentally, the examples in the second link are for implementing a simple string concatenation aggregate. Note the huge amount of code and gymnastics required to implement this simple function (which PostgreSQL provides out of the box, incidentally. Probably because its useful). MS SQL Server also does not allow an order to be specified in the aggregate, which renders this function useless for my kind of work ndash with MS SQL Server, the order of string concatenation is random, so the results of a query using this function are non-deterministic (they might change from run to run) and the code will not pass a quality review. The lack of ordering support also breaks code such as the interest calculation example above. As far as I can tell, you just cant do this using an MS SQL Server custom aggregate. (It is actually possible to make MS SQL Server do a deterministic string concatenation aggregation in pure SQL but you have to abuse the RECURSIVE query functionality to do it. Although an interesting academic exercise, this results in slow, unreadable, unmaintainable code and is not a real-world solution). 1.7. Unicode support Long gone are the days when ASCII was universal, character and byte were fungible terms and foreign (from an Anglocentric standpoint) text was an exotic exception. Proper international language support is no longer optional. The solution to all this is Unicode. There are a lot of misconceptions about Unicode out there. Its not a character set, its not a code page, its not a file format and its nothing whatsoever to do with encryption. An exploration of how Unicode works is fascinating but beyond the scope of this document ndash I heartily recommend Googling it and working through a few examples. The key points about Unicode that are relevant to database functionality are: Unicode-encoded text (for our purposes this means either UTF-8 or UTF-16) is a variable-width encoding. In UTF-8 a character can take one, two, three or four bytes to represent. In UTF-16 its either two or four. This means that operations like taking substrings and measuring string lengths need to be Unicode-aware to work properly. Not all sequences of bytes are valid Unicode. Manipulating valid Unicode without knowing its Unicode is likely to produce something that is not valid Unicode. UTF-8 and UTF-16 are not compatible. If you take one file of each type and concatenate them, you (probably) end up with a file which is neither valid UTF-8 nor valid UTF-16. For text which mostly fits into ASCII, UTF-8 is about twice as space-efficient as UTF-16. PostgreSQL supports UTF-8. Its CHAR. VARCHAR and TEXT types are, by default, UTF-8, meaning they will only accept UTF-8 data and all the transformations applied to them, from string concatenation and searching to regular expressions, are UTF-8-aware. It all just works. MS SQL Server 2008 does not support UTF-16 it supports UCS-2, a deprecated subset of UTF-16. What this means is that most of the time, it will look like its working fine, and occasionally, it will silently corrupt your data. Since it interprets text as a string of wide (i. e. 2-byte) characters, it will happily cut a 4-byte UTF-16 character in half. At best, this results in corrupted data. At worst, something else in your toolchain will break badly and youll have a disaster on your hands. Apologists for MS are quick to point out that this is unlikely because it would require the data to contain something outside Unicodes basic multilingual plane. This is completely missing the point. A databases sole purpose is storing, retreiving and manipulating data. A database which can be broken by putting the wrong data in it is as useless as a router that breaks if you download the wrong file. MS SQL Server versions since 2012 have supported UTF-16 properly, if you ensure you select a UTF-16-compliant collation for your database. It is baffling that this is (a) optional and (b) implemented as late as 2012. Better late than never, I suppose. 1.8. Data types that work properly A common misconception is that all databases have the same types ndash INT. CHAR. DATE and so on. هذا ليس صحيحا. PostgreSQLs type system is really useful and intuitive, free of annoyances which introduce bugs or slow work down and, as usual, apparently designed with productivity in mind. MS SQL Servers type system, by comparison, feels like beta software. It cant touch the feature set of PostgreSQLs type system and it is beset with traps waiting to ensnare the unwary user. Lets take a look: CHAR, VARCHAR and family PostgreSQL: the docs actively encourage you to simply use the TEXT type. This is a high-performance, UTF-8 validated text storage type which stores strings up to 1GB in size. It supports all the text operations PostgreSQL is capable of: simple concatenation and substringing regex searching, matching and splitting full-text search casting character transformation and so on. If you have text data, stick it in a TEXT field and carry on. Moreover, since anything in a TEXT field (or, for that matter, CHAR or VARCHAR fields) must be UTF-8, there is no issue with encoding incompatibility. Since UTF-8 is the de facto universal text encoding, converting text to it is easy and reliable. Since UTF-8 is a superset of ASCII, this conversion is often trivially easy or altogether unnecessary. It all just works. MS SQL Server: its a pretty sad story. The TEXT and NTEXT types exist and stretch to 2GB. Bafflingly, though, they dont support casting. Also, dont use them, says MS ndash they will be removed in a future version of MS SQL Server. You should use CHAR. VARCHAR and their N - prefixed versions instead. Unfortunately, VARCHAR(MAX) has poor performance characteristics and VARCHAR(8000) (the next biggest size, for some reason) tops out at 8,000 bytes. (Its 4,000 characters for NVARCHAR .) Remember how PostgreSQLs insistence on a single text encoding per database makes everything work smoothly Not so in MS-land: As with earlier versions of SQL Server, data loss during code page translations is not reported. link In other words, MS SQL Server might corrupt your data, and you wont know about it until something else goes wrong. This is, quite simply, a deal-breaker. A data analytics platform which might silently change, corrupt or lose your data is an enormous liability. Consider the absurdity of forking out for a server using expensive ECC RAM as a defence against data corruption caused by cosmic rays, and then running software on it which might corrupt your data anyway. Date and time types PostgreSQL: you get DATE. TIME. TIMESTAMP and TIMESTAMP WITH TIME ZONE. all of which do exactly what you would expect. They also have fantastic range and precision, supporting microsecond resolution from the 5th millennium BC to almost 300 millennia in the future. They accept input in a wide variety of formats and the last one has full support for time zones. They can be converted to and from Unix time, which is very important for interoperability with other systems. They can take the special values infinity and - infinity. This is not a metaphysico-theologico-philosophical statement, but a hugely useful semantic construction. For example, set a users password expiry date to infinity to denote that they do not have to change their password. The standard way of doing this is to use NULL or some date far in the future, but these are clumsy hacks ndash they both involve putting inaccurate information in the database and writing application logic to compensate. What happens when a developer sees NULL or 3499-12-31. If youre lucky, he knows the secret handshakes and isnt confused by it. If not, he assumes either that the date is unknown or that it really does refer to the 4th millennium, and you have a problem. The cumulative effect of hacks, workarounds and kludges like this is unreliable systems, unhappy programmers and increased business risk. Helpful semantics like infinity and - infinity allow you to say what you mean and write consistent, readable application logic. They also support the INTERVAL type, which is so useful it has its own section right after this one. Casting and conversion of date and time types is easy and intuitive - you can cast any type to TEXT. and the tochar and totimestamp functions give you ultimate flexibility, allowing conversion in both directions using format strings. For example: and, going in the other direction, As usual, it just works. As a data analyst, I care very much about a databases date-handling ability, because dates and times tend to occur in a multitude of different formats and they are usually critical to the analysis itself. MS SQL Server: dates can only have positive 4-digit years, so they are restricted to 0001 AD to 9999 AD. They do not support infinity and - infinity. They do not support interval types, so date arithmetic is tedious and clunky. You can convert them to and from UNIX time, but its a hack involving adding seconds to the UNIX epoch, 1970-01-01T00:00:00Z, which you therefore have to know and be willing to hardcode into your application. Date conversion deserves a special mention, because even by MS SQL Servers shoddy standards its bloody awful. The CONVERT function takes the place of PostgreSQLs tochar and totimestamp. but it works like this: Thats right ndash youre simply expected to know that 126 is the code for converting strings in that format to a datetime. MSDN provides a table of these magic numbers. I didnt give the same example as for PostgreSQL because I couldnt find a magic number corresponding to the right format for Saturday 03 Feb 2001. If someone gave you data with such dates in it, I guess youd have to do some string manipulation (pity the string manipulation facilities in MS SQL Server are almost non-existent. ) PostgreSQL: the INTERVAL type represents a period of time, such as 30 microseconds or 50 years. It can also be negative, which may seem counterintuitive until you remember that the word ago exists. PostgreSQL also knows about ago, in fact, and will accept strings like 1 day ago as interval values (this will be internally represented as an interval of -1 days). Interval values let you do intuitive date arithmetic and store time durations as first-class data values. They work exactly as you expect and can be freely casted and converted to and from anything which makes sense. MS SQL Server: no support for interval types. PostgreSQL: arrays are supported as a first-class data type, meaning fields in tables, variables in PLPGSQL, parameters to functions and so on can be arrays. Arrays can contain any data type you like, including other arrays. This is very, very useful . Here are some of the things you can do with arrays: Store the results of function calls with arbitrarily-many return values, such as regex matches Represent a string as integer word IDs, for use in fast text matching algorithms Aggregation of multiple data values across groups, for efficient cross-tabulation Perform row operations using multiple data values without the expense of a join Accurately and semantically represent array data from other applications in your tool stack Feed array data to other applications in your tool stack I cant think of any programming languages which dont support arrays, other than crazy ones like Brainfuck and Malbolge. Arrays are so useful that they are ubiquitous. Any system, especially a data analytics platform, which doesnt support them is crippled. MS SQL Server: no support for arrays. PostgreSQL: full support for JSON, including a large set of utility functions for transforming between JSON types and tables (in both directions), retreiving values from JSON data and constructing JSON data. Parsing and stringification are handled by simple casts, which as a rule in PostgreSQL are intelligent and robust. The PLV8 procedural language works as seamlessly as you would expect with JSON ndash in fact, a JSON-type internal state in a custom aggregate (see this example) whose transition function is written in PLV8 provides a declarativeimperative best-of-both-worlds so powerful and convenient it feels like cheating. JSON (and its variants, such as JSONB) is of course the de facto standard data transfer format on the web and in several other data platforms, such as MongoDB and ElasticSearch, and in fact any system with a RESTful interface. Aspiring Analytics-as-a-Service providers take note. MS SQL Server: no support for JSON. PostgreSQL: HSTORE is a PostgreSQL extension which implements a fast key-value store as a data type. Like arrays, this is very useful because virtually every high-level programming language has such a concept (and virtually every programming language has such a concept because it is very useful). JavaScript has objects, PHP has associative arrays, Python has dicts, C has std::map and std::unorderedmap. Go has maps. وما إلى ذلك وهلم جرا. In fact, the notion of a key-value store is so important and useful that there exists a whole class of NoSQL databases which use it as their main storage paradigm. Theyre called, uh, key-value stores . There are also some fun unexpected uses of such a data type. A colleague recently asked me if there was a good way to deduplicate a text array. Heres what I came up with: i. e. put the array into both the keys and values of an HSTORE, forcing a dedupe to take place (since key values are unique) then retrieve the keys from the HSTORE. Theres that PostgreSQL versatility again. MS SQL Server: No support for key-value storage. Range types PostgreSQL: range types represent, well, ranges. Every database programmer has seen fields called startdate and enddate. and most of them have had to implement logic to detect overlaps. Some have even found, the hard way, that joins to ranges using BETWEEN can go horribly wrong, for a number of reasons. PostgreSQLs approach is to treat time ranges as first-class data types. Not only can you put a range of time (or INT s or NUMERIC s or whatever) into a single data value, you can use a host of built-in operators to manipulate and query ranges safely and quickly. You can even apply specially-developed indices to them to massively accelerate queries that use these operators. In short, PostgreSQL treats ranges with the importance they deserve and gives you the tools to work with them effectively. Im trying not to make this document a mere list of links to the PostgreSQL docs, but just this once, I suggest you go and see for yourself . (Oh, and if the pre-defined types dont meet your needs, you can define your own ones. You dont have to touch the source code, the database exposes methods to allow you to do this.) MS SQL Server: no support for range types. NUMERIC and DECIMAL PostgreSQL: NUMERIC (and DECIMAL - theyre symonyms) is near-as-dammit arbitrary precision: it supports 131,072 digits before the decimal point and 16,383 digits after the decimal point. If youre running a bank, doing technical computation, landing spaceships on comets or simply doing something where you cannot tolerate rounding errors, youre covered. MS SQL Server: NUMERIC (and DECIMAL - theyre symonyms) supports a maximum of 38 decimal places of precision in total. PostgreSQL: XML is supported as a data type and the database offers a variety of functions for working with XML. Xpath querying is supported. MS SQL Server: finally, some good news MS SQL Server has an XML data type too, and offers plenty of support for working with it. (Shame XML is going out of style. ) 1.9. Scriptability PostgreSQL can be driven entirely from the command line, and since it works in operating systems with proper command lines (i. e. everything except Windows), this is highly effective and secure. You can SSH to a server and configure PostgreSQL from your mobile phone, if you have to (I have done so more than once). You can automate deployment, performance-tuning, security, admin and analytics tasks with scripts. Scripts are very important because unlike GUI processes, they can be copied, version-controlled, documented, automated, reviewed, batched and diffed. For serious work, text editors and command lines are king. MS SQL Server is driven through a GUI. I dont know to what extent it can be automated with Powershell I do know that if you Google for help and advice on getting things done in MS SQL Server, you get a lot of people saying right-click on your database, then click on Tasks. . GUIs do not work well across low-bandwidth or high-latency connections text-based shells do. As I write I am preparing to do some sysadmin on a server 3,500 miles away, on a VPN via a shaky WiFi hotspot, and thanking my lucky stars its an UbuntuPostgreSQL box. (Who on Earth wants a GUI on a server anyway) 1.10. Good external language bindings PostgreSQL is very, very easy to connect to and use from programming environments, because libpq, its external API, is very well-designed and very well-documented. This means that writing utilities which plug into PostgreSQL is very easy and convenient, which makes the database more versatile and a better fit in an analytics stack. On many occasions I have knocked up a quick program in C or C which connects to PostgreSQL, pulls some data out and does some heavy calculations on it, e. g. using multithreading or special CPU instructions - stuff the database itself is not suitable for. I have also written C programs which use setuid to allow normal users to perform certain administrative tasks in PostgreSQL. It is very handy to be able to do this quickly and neatly. MS SQL Servers external language bindings vary. Sometimes you have to install extra drivers. Sometimes you have to create classes to store the data you are querying, which means knowing at compile time what that data looks like. Most importantly, the documentation is a confusing, tangled mess, which makes getting this done unnecessarily time-consuming and painful. 1.11. Documentation Data analytics is all about being a jack of all trades. We use a very wide variety of programming languages and tools. (Off the top of my head, the programmingscripting languages I currently work with are PHP, JavaScript, Python, R, C, C, Go, three dialects of SQL, PLPGSQL and Bash.) It is hopelessly unrealistic to expect to learn everything you will need to know up front. Getting stuff done frequently depends on reading documentation. A well-documented tool is more useful and allows analysts to be more productive and produce higher-quality work. PostgreSQLs documentation is excellent. Everything is covered comprehensively but the documents are not merely reference manuals ndash they are full of examples, hints, useful advice and guidance. If you are an advanced programmer and really want to get stuck in, you can also simply read PostgreSQLs source code, all of which is openly and freely available. The docs also have a sense of humour: The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from -1 century to 1 century. If you disagree with this, please write your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican. MS SQL Servers documentation is all on MSDN, which is an unfriendly, sprawling mess. Because Microsoft is a large corporation and its clients tend to be conservative and humourless, the documentation is business appropriate ndash i. e. officious, boring and dry. Not only does it lack amusing references to the historical role of Catholicism in the development of date arithmetic, it is impenetrably stuffy and hidden behind layers of unnecessary categorisation and ostentatiously capitalised official terms. Try this: go to the product documentation page for MS SQL Server 2012 and try to get from there to something useful. Or try reading this gem (not cherry-picked, I promise): A report part definition is an XML fragment of a report definition file. You create report parts by creating a report definition, and then selecting report items in the report to publish separately as report parts. Has the word report started to lose its meaning yet (And, of course, MS SQL Server is closed source, so you cant look at the source code. Yes, I know source code is not the same as documentation, but it is occasionally surprisingly useful to be able to simply grep the source for a relevant term and cast an eye over the code and the comments of the developers. Its easy to think of our tools as magical black boxes and to forget that even something as huge and complex as an RDBMS engine is, after all, just a list of instructions written by humans in a human-readable language.) 1.12. Logging thats actually useful MS SQL Servers logs are spread across several places - error logs, Windows event log, profiler logs, agent logs and setup log. To access these you need varying levels of permissions and you have to use various tools, some of which are GUI-only. Maybe things like Splunk can help to automate the gathering and parsing of these logs. I havent tried, nor do I know anyone else who has. Google searches on the topic produce surprisingly little information, surprisingly little of which is of any use. PostgreSQLs logs, by default, are all in one place. By changing a couple of settings in a text file, you can get it to log to CSV (and since were talking about PostgreSQL, its proper CSV, not broken CSV). You can easily set the logging level anywhere from dont bother logging anything to full profiling and debugging output. The documentation even contains DDL for a table into which the CSV-format logs can be conveniently imported. You can also log to stderr or the system log or to the Windows event log (provided youre running PostgreSQL in Windows, of course). The logs themselves are human-readable and machine-readable and contain data likely to be of great value to a sysadmin. Who logged in and out, at what times, and from where Which queries are being run and by whom How long are they taking How many queries are submitted in each batch Because the data is well-formatted CSV, it is trivially easy to visualise or analyse it in R or PostgreSQL itself or Pythons matplotlib or whatever you like. Overlay this with the wealth of information that Linux utilities like top, iotop and iostat provide and you have easy, reliable access to all the server telemetry you could possibly need. 1.13. Support How is PostgreSQL going to win this one Everyone knows that expensive flagship enterprise products by big commercial vendors have incredible support, whereas free software doesnt have any Of course, this is nonsense. Commercial products have support from people who support it because they are paid to. They do the minimum amount necessary to satisfy the terms of the SLA. As I type this, some IT professionals I know are waiting for a major hardware vendor to help them with a performance issue in a 40,000 server. Theyve been discussing it with the vendor for weeks theyve spent time and effort running extensive tests and benchmarks at the vendors request and so far the vendors reaction has been a mixture of incompetence, fecklessness and apathy. The 40,000 server is sitting there performing very, very slowly, and its users are working 70-hour weeks to try to stay on schedule. Over the years I have seen many, many problems with expensive commercial software ndash everything from bugs to performance issues to incompatibility to insufficient documentation. Sometimes these problems cause a late night or a lost weekend for the user sometimes they cause missed deadlines and angry clients sometimes it goes as far as legal and reputational risk. Every single time, the same thing happens: the problem is fixed by the end users, using a combination of blood, sweat, tears, Google and late nights. I have never seen the vendor swoop to the rescue and make everything OK. So what is the support for PostgreSQL like On the two occasions I have asked the PostgreSQL mailing list for help, I have received replies from Tom Lane within 24 hours. Take a moment to click on the link and read the wiki - the guy is not just a lead developer of PostgreSQL, hes a well-known computer programmer. Needless to say, his advice is as good as advice gets. On one of the occasions, where I asked a question about the best way to implement cross-function call persistent memory allocation, Lane replied with the features of PostgreSQL I should study and suggested solutions to my problem ndash and for good measure he threw in a list of very good reasons why my tentative solution (a C static variable) was rubbish. You cant buy that kind of support, but you can get it from a community of enthusiastic open source developers. Oh, did I mention that the total cost of the database software and the helpful advice and recommendations from the acclaimed programmer was 0.00 Note that by support I mean help getting it to work properly. Some people (usually people who dont actually use the product) think of support contracts more in terms of legal coverage ndash theyre not really interested in whether help is forthcoming or not, but they like that theres someone to shout at and, more importantly, blame. I discuss this too, here . (And if youre really determined to pay someone to help you out, you can of course go to any of the organisations which provide professional support for PostgreSQL. Unlike commercial software vendors, whose support functions are secondary to their main business of selling products, these organisations live or die by the quality of the support they provide, so it is very good.) 1.14. Flexible, scriptable database dumps Ive already talked about scriptability, but database dumps are very important, so they get their own bit here. PostgreSQLs dump utility is extremely flexible, command-line driven (making it easily automatable and scriptable) and well-documented (like the rest of PostgreSQL). This makes database migration, replication and backups ndash three important and scary tasks ndash controllable, reliable and configurable. Moreover, backups can be in a space-effecient compressed format or in plain SQL, complete with data, making them both human-readable and executable. A backup can be of a single table or of a whole database cluster. The user gets to do exactly as he pleases. With a little work and careful selection of options, it is even possible to make a DDL-only plain SQL PostgreSQL backup executable in a different RDBMS. MS SQL Servers backups are in a proprietary, undocumented, opaque binary format. 1.15. Reliability Neither PostgreSQL nor MS SQL Server are crash-happy, but MS SQL Server does have a bizarre failure mode which I have witnessed more than once: its transaction logs become enormous and prevent the database from working. In theory the logs can be truncated or deleted but the documentation is full of dire warnings against such action. PostgreSQL simply sits there working and getting things done. I have never seen a PostgreSQL database crash in normal use. PostgreSQL is relatively bug-free compared to MS SQL Server. I once found a bug in PostgreSQL 8.4 ndash it was performing a string distance calculation algorithm wrongly. This was a problem for me because I needed to use the algorithm in some fuzzy deduplication code I was writing for work. I looked up the algorithm on Wikipedia, gained a rough idea of how it works, found the implementation in the PostgreSQL source code, wrote a fix and emailed it to one of the PostgreSQL developers. In the next release of PostgreSQL, version 9.0, the bug was fixed. Meanwhile, I applied my fix to my own installation of PostgreSQL 8.4, re-compiled it and kept working. This will be a familiar story to many of the users of PostgreSQL, and indeed any large piece of open source software. The community benefits from high-quality free software, and individuals with the appropriate skills do what they can to contribute. Everyone wins. With a closed-source product, you cant fix it yourself ndash you just raise a bug report, cross your fingers and wait. If MS SQL Server were open source, section 1.1 above would not exist, because I (and probably thousands of other frustrated users) would have damn well written a proper CSV parser and plumbed it in years ago. 1.16. Ease of installing and updating Does this matter Well, yes. Infrastructure flexibility is more important than ever and that trend will only continue. Gone are the days of the big fat server install which sits untouched for years on end. These days its all about fast, reliable, flexible provisioning and keeping up with cutting-edge features. Also, as the saying goes, time is money. I have installed MS SQL Server several times. I have installed PostgreSQL more times than I can remember - probably at least 50 times. Installing MS SQL Server is very slow. It involves immense downloads (who still uses physical install media) and lengthy, important-sounding processes with stately progress bars. It might fail if you dont have the right version of or the right Windows service pack installed. Its the kind of thing your sysadmin needs to find a solid block of time for. Installing PostgreSQL the canonical way ndash from a Linux repo ndash is as easy as typing a single command, like this: How long does it take I just tested this by spinning up a cheap VM in the cloud and installing PostgreSQL using the above command. It took 16 seconds . Thats the total time for the download and the install. As for updates, any software backed by a Linux repo is trivially easily patched and updated by pulling updates from the repo. Because repos are clever and PostgreSQL is not obscenely bloated, downloads are small and fast and application of updates is efficient. I dont know how easy MS SQL Server is to update. I do know that a lot of production MS SQL Server boxes in certain organisations are still on version 2008 R2 though. 1.17. The contrib modules As if the enormous feature set of PostgreSQL is not enough, it comes with a set of extensions called contrib modules. There are libraries of functions, types and utilities for doing certain useful things which dont quite fall into the core feature set of the server. There are libraries for fuzzy string matching, fast integer array handling, external database connectivity, cryptography, UUID generation, tree data types and loads, loads more. A few of the modules dont even do anything except provide templates to allow developers and advanced users to develop their own extensions and custom functionality. Of course, these extensions are trivially easy to install. For example, to install the fuzzystrmatch extension you do this: 1.18. Its free PostgreSQL is free as in freedom and free as in beer. Both types of free are extremely important. The first kind, free as in freedom, means PostgreSQL is open-source and very permissively licensed. In practical terms, this means that you can do whatever you want with it, including distributing software which includes it or is based on it. You can modify it in whatever way you see fit, and then you can distribute the modifications to whomever you like. You can install it as many times as you like, on whatever you like, and then use it for any purpose you like. The second kind, free as in beer, is important for two main reasons. The first is that if, like me, you work for a large organisation, spending that organisations money involves red tape. Red tape means delays and delays sap everyones energy and enthusiasm and suppress innovation. The second reason is that because PostgreSQL is free, many developers, experimenters, hackers, students, innovators, scientists and so on (the brainy-but-poor crowd, essentially) use it, and it develops a wonderful community. This results in great support (as I mentioned above ) and contributions from the intellectual elite. It results in a better product, more innovation, more solutions to problems and more time and energy spent on the things that really matter. 2. The counterarguments For reasons which have always eluded me, people often like to ignore all the arguments and evidence above and try to dismiss the case for PostgreSQL using misconceptions, myths, red herrings and outright nonsense. Stuff like this: 2.1. But a big-name vendor provides a safety net No it doesnt. This misconception is a variant of the old adage no-one ever got fired for buying IBM. Hilariously, if you type that into Google, the first hit is the Wikipedia article on fear, uncertainty and doubt - and even more hilariously, the first entry in the examples section is Microsoft. I promise I did not touch the Wikipedia article, I simply found it like that. In client-serving data analytics, you just have to get it right. If you destroy your reputation by buggering up an important job, your software vendor will not build you a new reputation. If you get sued, then maybe you can recover costs from your vendor - but only if they did something wrong. Microsoft isnt doing anything technically wrong with MS SQL Server, theyre simply releasing a terrible product and being up front about how terrible it is. The documentation admits its terrible. It works exactly as designed the problem is that the design is terrible. You cant sue Microsoft just because you didnt do your due diligence when you picked a database. Even if you somehow do successfully blame the vendor, you still have a messed up job and an angry client, who wont want to hear about MS SQL Servers unfortunate treatment of UTF-16 text as UCS-2, resulting in truncation of a surrogate pair during a substring operation and subsequent failure to identify an incriminating keyword. At best they will continue to demand results (and probably a discount) at worst, they will write you off as incompetent ndash and who could blame them, when you trusted their job to a RDBMS whose docs unapologetically acknowledge that it might silently corrupt your data Since the best way to minimise risk is to get the job done right, the best tool to use is the one which is most likely to let you accomplish that. In this case, thats PostgreSQL. 2.2. But what happens if the author of PostgreSQL dies Same thing that happens if the author of MS SQL Server dies ndash nothing. Also, needless to say, the author of PostgreSQL is as meaningless as the author of MS SQL Server. Theres no such thing. A senior individual with an IT infrastructure oversight role actually asked me this question once (about Hadoop, not PostgreSQL). There just seems to be a misconception that all open-source software is written by a loner who lives in his mums basement. This is obviously not true. Large open source projects like PostgreSQL and Hadoop are written by teams of highly skilled developers who are often commercially sponsored. At its heart, the development model of PostgreSQL is just like the development model of MS SQL Server: a large team of programmers is paid by an organisation to write code. There is no single point of failure. There is at least one key difference, though: PostgreSQLs source code is openly available and is therefore reviewed, tweaked, contributed to, improved and understood by a huge community of skilled programmers. Thats one of the reasons why its so much better. Crucially, because open-source software tends to be written by people who care deeply about its quality (often because they have a direct personal stake in ensuring that the software works as well as possible), it is often of the very highest standard (PostgreSQL, Linux, MySQL, XBMC, Hadoop, Android, VLC, Neo4JS, Redis, 7Zip, FreeBSD, golang, PHP, Python, R, Nginx, Apache, node. js, Chrome, Firefox. ). On the other hand, commercial software is often designed by committee, written in cube farms and developed without proper guidance or inspiration (Microsoft BOB, RealPlayer, Internet Explorer 6, iOS Maps, Lotus Notes, Windows ME, Windows Vista, QuickTime, SharePoint. ) 2.3. But open-source software isnt securereliabletrustworthyenterprise-readyetc Theres no kind way to say this: anyone who says such a thing is very ignorant, and you should ignore them ndash or, if youre feeling generous, educate them. Well, I guess Im feeling generous: Security: the idea that closed-source is more secure is an old misconception, for many good reasons which I will briefly summarise (but do read the links ndash theyre excellent): secrecy isnt the same as security an open review process is more likely to find weaknesses than a closed one properly reviewed open source software is difficult or impossible to build a back door into. If you prefer anecdotal evidence to logical arguments, consider that Microsoft Internet Explorer 6, once a flagship closed-source commercial product, is widely regarded as the least secure software ever produced, and that Rijndael, the algorithm behind AES, which governments the world over use to protect top secret information, is an open standard. In any case, relational databases are not security software. In the IT world, security is a bit like support our troops in the USA or think of the children in the UK ndash a trump card which overrules all other considerations, including common sense and evidence. Dont fall for it. Reliability: Windows was at one point renowned for its instability, although these days things are much better. (Supposedly, Windows 9x would spontaneously crash when its internal uptime counter, counting in milliseconds, exceeded the upper bound of an unsigned 32-bit integer, i. e. after 2 32 milliseconds or about 49.7 days. I have always wanted to try this.) Linux dominates the server space, where reliability is key, and Linux boxes routinely achieve uptimes measured in years. Internet Explorer has always (and still does) failed to comply with web standards, causing websites to break or function improperly the leaders in the field are the open-source browsers Chrome and Firefox. Lotus Notes is a flaky, crash-happy, evil mess Thunderbird just works. And I have more than once seen MS SQL Server paralyse itself by letting transaction log files blow up, something PostgreSQL does not do. Trustworthiness: unless youve been living under a rock for the past couple of years, you know who Edward Snowden is. Thanks to him, we know exactly what you cannot trust: governments and the large organisations they get their hooks into. Since Snowden went public, it is clear that NSA back doors exist in a vast array of products, both hardware and software, that individuals and organisations depend on to keep their data secure. The only defence against this is open code review. The only software that can be subjected to open code review is open source software. If you use proprietary closed-source software, you have no way of knowing what it is really doing under the hood. And thanks to Mr. Snowden, we now know that there is an excellent chance it is giving your secrets away. At the time of writing, 485 of the top 500 supercomputers in the world run on Linux. As of July 2014, Nginx and Apache, two open-source web servers, power over 70 of the million busiest sites on the net. The computers on the International Space Station (the most expensive single man-made object in existence) were moved from Windows to Linux in 2013 in an attempt to improve stability and reliability. The back-end database of Skype (ironically now owned by Microsoft) is PostgreSQL. GCHQ recently reported that Ubuntu Linux is the most secure commonly-available desktop operating system. The Large Hadron Collider is the worlds largest scientific experiment. Its supporting IT infrastructure, the Worldwide LHC Computing Grid, is the worlds largest computing grid. It handles 30 PB of data per year and spans 36 countries and over 170 computing centres. It runs primarily on Linux. Hadoop, the current darling of many large consultancies looking to earn Big Data credentials, is open-source. Red Hat Enterprise Linux CEntOS (Community Enterprise OS) SUSE Linux Enterprise Server Oracle Linux IBM Enterprise Linux Server etc. The idea that open-source software is not for the enterprise is pure bullshit. If you work in tech for an organisation which disregards open source, enjoy it while it lasts. They wont be around for long. 2.4. But MS SQL Server can use multiple CPU cores for a single query This is an advantage for MS SQL Server whenever youre running a query which is CPU-bound and not IO-bound. In real-life data analytics this happens approximately once every three blue moons. On those very rare, very specific occasions when CPU power is truly the bottleneck, you almost certainly should be using something other than an RDBMS. RDBMSes are not for number crunching. This advantage goes away when a server has to do many things at once (as is almost always the case). PostgreSQL uses multiprocessing ndash different connections run in different processes, and hence on different CPU cores. The scheduler of the OS takes care of this. Also, I suspect this query parallelism is what necessitates the merge method which MS SQL Server custom aggregate assemblies are required to implement bits of aggregation done in different threads have to be combined with each other, MapReduce-style. I further suspect that this mechanism is what prevents MS SQL Server aggregates from accepting ORDER BY clauses. So, congratulations ndash you can use more than one CPU core, but you cant do a basic string roll-up. 2.5. But I have MS SQL Server skills, not PostgreSQL skills Youd rather stick with a clumsy, awkward, unreliable system than spend the trivial amount of effort it takes to learn a slightly different dialect of a straightforward querying language Well, just hope you never end up in a job interview with me. 2.6. But a billion Microsoft users cant all be wrong This is a real-life quotation as well, from a senior data analyst I used to work with. I replied well there are 1.5 billion Muslims and 1.2 billion Catholics. They cant all be right. Ergo, a billion people most certainly can be wrong. (In this particular case, 2.7 billion people are wrong.) 2.7. But if it were really that good then it wouldnt be free People actually say this too. I feel sorry for these people, because they are unable to conceive of anyone doing anything for any reason other than monetary gain. Presumably they are also unaware of the existence of charities or volunteers or unpaid bloggers or any of the other things people do purely out of a desire to contribute or to create something or simply to take on a challenge. This argument also depends on an assumption that open source development has no benefit for the developer, which is nonsense. The reason large enterprises open-source their code and then pay their teams to continue working on it is because doing so benefits them. If you open up your code and others use it, then you have just gained a completely free source of bug fixes, feature contributions, code review, product testing and publicity. If your product is good enough, it is used by enough people that it starts having an influence on standards, which means broader industry acceptance. You then have a favoured position in the market as a provider of support and deployment services for the software. Open-sourcing your code is often the most sensible course of action even if you are completely self-interested. As a case in point, here I am spending my free time writing a web page about how fabulous PostgreSQL is and then paying my own money to host it. Perhaps Teradata or Oracle are just as amazing, but theyre not getting their own pages because I cant afford them, so I dont use them. 2.8. But youre biased No, I have a preference. The whole point of this document is to demonstrate, using evidence, that this preference is justified. If you read this and assume that just because I massively prefer PostgreSQL I must be biased, that means you are biased, because you have refused to seriously consider the possibility that it really is better. If you think theres actual evidence that I really am biased, let me know. 2.9. But PostgreSQL is a stupid name This one is arguably true its pretty awkward. It is commonly mispronounced, very commonly misspelt and almost always incorrectly capitalised. Its a good job that stupidness of name is not something serious human beings take into account when theyre choosing industrial software products. That being said, MS SQL Server is literally the most boring possible name for a SQL Server provided by MS. It has anywhere from six to eight syllables, depending on whether or not you abbreviate Microsoft and whether you say it sequel or ess queue ell, which is far too many syllables for a product name. Microsoft has a thing for very long names though ndash possibly its greatest achievement ever is Microsoft WinFX Software Development Kit for Microsoft Pre-Release Windows Operating System Code-Named Longhorn, Beta 1 Web Setup I count 38 syllables. رائع. 2.10. But SSMS is better than PGAdmin Its slicker, sure. Its prettier. It has code completion, although I always turn that off because it constantly screws things up, and for every time it helps me out with a field or table name, theres at least one occasion when it does something mental, like auto-correcting a common SQL keyword like table to a Microsoft monstrosity like TABULATIONNONTRIVIALDISCOMBOBULATEDMACHIAVELLIANGANGLYONID or something. For actually executing SQL and looking at the results in a GUI, PGAdmin is fine. Its just not spectacular. SSMS is obviously Windows-only. PGAdmin is cross-platform. This is actually quite convenient. You can run PGAdmin in Windows, where you have all your familiar stuff ndash Office, Outlook etc. ndash whilst keeping the back end RDBMS in Linux. This gets you the best of both worlds (even an open source advocate like me admits that if youre a heavy MS Office user, there is no serious alternative). Several guys I work with do this. One point in SSMSs favour is that if you run several row-returning statements in a batch, it will give you all the results. PGAdmin returns only the last result set. This can be a drag when doing data analytics, where you often want to simultaneously query several data sets and compare the results. Theres another thing though: psql. This is PostgreSQLs command-line SQL interface. Its really, really good. It has loads of useful catalog-querying features. It displays tabular data intelligently. It has tab completion which, unlike SSMSs code completion, is actually useful, because it is context sensitive. So, for example, if you type DROP SCHEMA t and hit tab, it will suggest schema names starting with t (or, if there is only one, auto-fill it for you). It lets you jump around in the file system and use ultra-powerful text editors like vim inline. It automatically keeps a list of executed commands. It provides convenient, useful data import and export functionality, including the COPY TO PROGRAM feature which makes smashing use of pipes and command-line utilities to provide another level of flexibility and control of data. It makes intelligent use of screen space. It is fast and convenient. You can use it over an SSH connection, even a slow one. Its only serious disadvantage is that it is unsuitable for people who want to be data analysts, but are scared of command lines and typing on a keyboard. 2.11. But MS SQL Server can import straight from Excel Yes. So what Excel can output to CSV (in a rare moment of sanity, Microsoft made Excels CSV export code work properly) and PostgreSQL can import CSV. Admittedly, its an extra step. Is the ability to import straight from Excel a particularly important feature in an analytics platform anyway 2.12. But PostgreSQL is slower than MS SQL Server A more accurate rephrasing would be MS SQL Server is slightly more forgiving if you dont know what youre doing. For certain operations, PostgreSQL is definitely slower than MS SQL Server ndash the easiest example is probably COUNT(). which is (I think) always instant in MS SQL Server and in PostgreSQL requires a full table scan (this is due to the different concurrency models they use). PostgreSQL is slow out-of-the box because its default configuration uses only a tiny amount of system resources ndash but any system being used for serious work has been tuned properly, so raw out-of-the-box performance is not a worthwhile thing to argue about. I once saw PostgreSQL criticised as slow because it was taking a long time to do some big, complex regex operations on a large table. But everyone knows that regex operations can be very computationally expensive, and in any case, what was PostgreSQL being compared to Certainly not the MS SQL Server boxes, which couldnt do regexes. PostgreSQLs extensive support for very clever indexes, such as range type indexes and trigram indexes, makes it orders of magnitude faster than MS SQL Server for a certain class of operations. But only if you know how to use those features properly. The immense flexibility you get from the great procedural language support and the clever data types allows PostgreSQL-based solutions to outperform MS SQL Server-based solutions by orders of magnitude. See my earlier example . In any case, the argument about speed is never only about computer time it is about developer time too. Thats why high-level languages like PHP and Python are very popular, despite the fact that C kicks the shit out of them when it comes to execution speed. They are slower to run but much faster to use for development. Would you prefer to spend an hour writing maintainable, elegant SQL followed by an hour of runtime, or spend three days writing buggy, desperate workarounds followed by 45 minutes of runtime 2.13. But you never mentioned such-and-such feature of MS SQL Server As I said in the banner and the intro. I am comparing these databases from the point of view of a data analyst, because Im a data analyst and I use them for data analysis. I know about SSRS, SSAS, in-memory column stores and so on, but I havent mentioned them because I dont use them (or equivalent features). Yes, this means this is not a comprehensive comparison of the two databases, and I never said it would be. It also means that if you care mostly about OLTP or data warehousing, you might not find this document very helpful. 2.14. But Microsoft has open-sourced Yeah, mere hours after I wrote all about how theyre a vendor lock-in monster and are anti-open source. Doh. However, lets look at this in context. Remember the almighty ruckus when the Office Open XML standard was being created Microsoft played every dirty trick in the book to ensure that MS Office wouldnt lose its dominance. Successfully, too ndash the closest alternative, LibreOffice, is still not a viable option, largely because of incompatibility with document formats. The OOXML standard that was finally pushed through is immense, bloated, ambiguous, inconsistent and riddled with errors. That debacle also started with an apparent gesture toward open standards on Microsofts part. If that seems harsh or paranoid, lets remember that this is an organisation that has been in legal trouble with both the USA and the EU for monopolistic and anticompetitive behaviour and abuse of market power, in the latter case being fined almost half a billion Euros. Then theres the involvement in SCOs potentially Linux-killing lawsuit against IBM. When Steve Ballmer was CEO he described Linux as a cancer (although Ballmer also said Theres no chance that the iPhone is going to get any significant market share. No chance, so maybe he just likes to talk nonsense). Microsoft has a long-established policy of preferring conquest to cooperation. So, if they play nice for the next few years and their magnanimous gesture ushers in a new era of interoperability, productivity and harmony, I (and millions of developers who want to get on with creating great things instead of bickering over platforms and standards) will be over the moon. For now, thinking that MS has suddenly become all warm and fuzzy would just be naive. 2.15. But youre insultingI dont like your toneyou come across as angryyou sound like a fanboythis is unprofessionalthis is a rant This page is unprofessional by definition ndash Im not being paid to write it. That also means I get to use whatever tone I like, and I dont have to hide the way I feel about things. I hope you appreciate the technical content even if you dont like the way I write if my tone makes this document unreadable for you, then I guess Ive lost a reader and youve lost a web page. Cest la vie.