Moving Average Sql Server 2012

SQL Server 2012 Analysis Services (SSAS) DMVs Oleh: Scott Murray Baca Komentar (6) Related Tips: Analisis Layanan Administrasi Apa itu SQL Server Analysis Services (SSAS) 2012 DMVs dan bagaimana penggunaannya? Di tip sebelumnya saya di XMLA keduanya Metode Execute and Discover dibahas dalam konteks menjalankan query XMLA. Metode penemuan memaparkan metadata tentang database SSAS namun datanya dikembalikan dalam bentuk XML. Sebagai alternatif metode penemuan, SSAS menyediakan kumpulan pandangan manajemen dinamis (DMVs for short) yang dapat diminta untuk mengembalikan data yang sama dengan metode penemuan. Namun, dengan menggunakan DMV ini, data dikembalikan dalam format tabular yang umumnya lebih mudah dibaca dan digunakan sebagai dasar laporan. Pertanyaannya adalah DMX, namun mereka memiliki tampilan dan nuansa SQL dengan beberapa keberatan. DMV dapat dipecah menjadi dua kategori utama: DBSCHEMAMDSCHEMA DMVs yang mengambil metadata tentang database SSAS, seperti struktur kubus dan struktur dimensi dan DMV Discover yang mengambil data pemantauan seperti koneksi dan kunci saat ini. SSAS MDSCHEMA dan DBSCHEMA DMVs SSAS DMVs bertindak dalam banyak hal seperti SQL DMV reguler dan mengembalikan data dalam format tabel, setidaknya dalam banyak kasus. Informasi yang bisa ditanyakan mencakup segala hal mulai dari koneksi yang saat ini aktif hingga jumlah memori yang digunakan untuk dimensi apa yang merupakan bagian dari kubus. Selanjutnya, Anda bahkan bisa meminta dimensi untuk mendapatkan anggotanya. Meskipun query seperti SQL, Anda tidak dapat menggunakan yang berikut ini: Terakhir, dan mungkin sama pentingnya, untuk menjalankan query melawan database SSAS, diperlukan izin administrator sistem. Cara terbaik untuk menunjukkan apa pandangan yang dapat dilakukan adalah meninjau beberapa contoh yang pada gilirannya akan menyampaikan keterbatasan yang ikut bermain dengan beberapa DMV. Mungkin tempat terbaik untuk memulai adalah dengan kueri untuk mendapatkan daftar DMV yang tersedia untuk kueri. Kueri MDX di bawah ini akan menampilkan daftar tampilan yang dapat dipertanyakan. Perhatikan bahwa tampilan kata digunakan sangat longgar karena DMV ini mirip SQL tapi bukan SQL murni. Untuk menjalankan query ini, buka SSMS dan hubungkan ke database SSAS Anda seperti yang ditunjukkan di bawah ini. Untuk contoh kami, kami akan menggunakan database contoh AdventureWorks 2012 DataWarehouse yang tersedia di CodePlex, msftdbprodsamples. codeplexreleasesview55330. Pastikan untuk memilih database AdventureWorksDW2012 dan memverifikasi jenis kueri diatur ke MDX. Jika MDX tidak dipilih, Anda dapat mengatur jenis query dengan mengklik tombol MDX pada tool bar. Hasil kueri ditampilkan sebagian di bawah layar cetak. Daftar tabel ini kira-kira setara dengan DMV yang tersedia untuk query terhadap database SSAS. Untuk rincian tentang masing-masing rangkaian baris ini, MSDN memiliki lembar referensi DMV yang tersedia di: msdn. microsoften-uslibraryhh230820.aspxbkmkref. Selanjutnya, kita bisa menjalankan query berikut untuk mendapatkan daftar kubus dalam database tertentu. Tentu ada beberapa peringatan dengan hasil query yang ditunjukkan di bawah ini. Pertama, Anda akan melihat bahwa hanya dua kubus yang ditampilkan di penjelajah objektif (sisi kiri dari cetakan di atas), sedangkan hasil kueri menunjukkan tujuh baris. Alasan perbedaan ini adalah bahwa hasilnya meliputi Perspektif selain batu biasa. Dengan demikian, cara sederhana untuk mengembalikan hanya batu dan bukan perspektifnya adalah menyesuaikan kueri kita seperti yang ditunjukkan pada ilustrasi berikutnya. Dalam kasus ini, kriteria di mana klausa sedikit menipu karena mencari nilai kosong untuk BaseCubeName dengan menggunakan operator yang kurang dari itu. Selanjutnya kita bisa mendapatkan daftar dimensi menggunakan DMV MDSCHEMADIMENSIONS. Tentu ada beberapa peringatan yang ada dengan hasil query yang ditunjukkan di bawah ini. Dimensinya dicantumkan beberapa kali misalnya, dimensi Akun terdaftar tujuh kali. Satu dimensi ada untuk masing-masing kelompok ukuran kubus, satu dimensi, yang nama kubusnya dimulai dengan dimensi kubus. Selanjutnya, keterangan dimensi menampilkan nama yang dilihat pengguna akhir. Mengebor ke dimensi, selanjutnya kita bisa menggunakan DMV MDSCHEMAMEASUREGROUPDIMENSIONS, seperti yang ditunjukkan di bawah ini. DMV ini memecah dimensi pada tingkat granularitas kelompok pengukuran. Perhatikan bahwa saya telah memasukkan dua item dalam klausul Order By. Sayangnya, termasuk lebih dari satu item dalam klausul pesanan tidak didukung dan menghasilkan kesalahan berikut. Menggunakan hanya satu urutan dengan bidang dan menambahkan nama kubus ke klausa mana yang menghasilkan hasil yang lebih baik yang diilustrasikan di bawah ini. Sekali lagi, perhatikan bahwa kami memiliki duplikasi seperti yang kami lakukan sebelumnya dengan MDSCHEMADIMENSIONS DMV. Sebelum beralih ke beberapa DMV pemantauan, mari kita tinjau ukuran MDSCHEMAMEASURES DMV. Seperti diilustrasikan di bawah ini, selain nama normal dan informasi visibilitas yang tersedia dari DMV dimensi, DMV MDSCHEMAMEASURES menyampaikan format yang digunakan untuk mengukur dan metode agregasi, yang menceritakan bagaimana menggabungkan ukuran seperti Sum, Average, atau Custom ( Lihat msdn. microsoften-uslibraryms126250.aspx). DMV juga menampilkan rumus yang digunakan dalam pengukuran yang dihitung di bidang Expression yang sangat membantu saat memeriksa beberapa nilai yang dihitung pada satu waktu yang tidak dihitung tidak memiliki data di bidang ekspresi. SSAS Discover DMVs SSAS Discover DMVs mengambil data yang digunakan untuk memantau database SSAS. Beberapa titik data yang dapat diambil mencakup waktu eksekusi query, kunci saat ini, penggunaan CPU dan memori, dan koneksi saat ini. Kita akan mulai dengan DISCOVERCONNECTIONS DMV. DMV ini memberi kita banyak rincian tentang sesi saat ini termasuk Perintah Terakhir yang akan dijalankan, berapa lama perintah dijalankan, berapa banyak memori yang digunakan sesi, dan berapa banyak bacaan dan penulisan yang digunakan. DMO DISCOVERCOMMANDS ini memberi kita informasi serupa di tingkat perintah. Anda juga akan melihat bahwa Anda dapat melakukan Select untuk mengambil semua kolom dari DMV, sama seperti dengan pernyataan pilihan lainnya, saya akan merekomendasikan hanya mengambil kolom yang dibutuhkan. Beberapa Discover DMVs memerlukan penggunaan SYSTEMRESTRICTSCHEMA dan juga memerlukan passing pada parameter tambahan. Intinya, ini berarti bahwa alih-alih menulis secara langsung terhadap DMV, kami akan meminta SYSTEMRESTRICTSCHEMA dan kemudian menentukan parameter DMV dan parameter lainnya yang diperlukan saat menulis kueri. Misalnya, query di bawah ini mengintegrasikan DMV DISCOVERINSTANCES. Dalam kasus ini, hanya satu parameter yang diperlukan, INSTANCENAME. Perhatikan bagaimana menggunakan SYSTEMRESTRICTSCHMEA. DISCOVERINSTANCES DMV mengembalikan data berikut tentang instance. Dengan menggunakan DMD SYSTEM. DBSCHEMATABLES, Anda dapat menjelajahi banyak DMV Discover SSAS lainnya. Kesimpulan Menggunakan SSAS DMV dapat mengembalikan sejumlah besar informasi tentang metadata kubus SSAS dan sebuah kubus yang ada saat ini, dari sudut pandang pemantauan. DMV ini lebih mudah digunakan daripada query terkait XMLA karena data dikembalikan dalam format tabel dan query ditulis, dalam banyak kasus, dengan cara yang mirip SQL. Hasilnya, tergantung pada DMV, dapat menyediakan banyak informasi yang dibutuhkan untuk administrator SSAS untuk profil dan memantau kubus SSAS. Langkah Berikutnya Update Terakhir: 7262013Setiap orang sudah terbiasa dengan ungkapan, quotthis ini akan membunuh dua burung dengan satu stonequot. Jika tidak, fase mengacu pada pendekatan yang membahas dua tujuan dalam satu tindakan. (Sayangnya, ungkapan itu sendiri agak tidak menyenangkan, karena kebanyakan dari kita tidak ingin melempar batu pada hewan yang tidak berdosa) Hari ini saya akan membahas beberapa dasar mengenai dua fitur hebat di SQL Server: indeks Columnstore (hanya tersedia di SQL Server Enterprise) dan SQL Query Store. Microsoft benar-benar menerapkan indeks Columnstore di SQL 2012 Enterprise, meskipun mereka berhasil meningkatkannya dalam dua rilis terakhir dari SQL Server. Microsoft memperkenalkan Query Store di SQL Server 2016. Jadi, apa saja fitur ini dan mengapa mereka penting Nah, saya punya demo yang akan mengenalkan kedua fitur tersebut dan menunjukkan bagaimana mereka dapat membantu kita. Sebelum saya melangkah lebih jauh, saya juga membahas fitur ini (dan fitur SQL 2016 lainnya) di artikel Majalah KODE saya tentang fitur baru SQL 2016. Sebagai pengantar dasar, indeks Columnstore dapat membantu mempercepat kueri yang memindai berdasarkan data dalam jumlah besar, dan Query Query melacak eksekusi query, rencana eksekusi, dan statistik runtime yang biasanya Anda butuhkan untuk mengumpulkan secara manual. Percayalah ketika saya mengatakannya, ini adalah fitur hebat. Untuk demo ini, saya akan menggunakan database demo Data Warehouse Microsoft Contoso. Ngomong ngomong, Contoso DW seperti kuota AdventureWorksquot yang sangat besar, dengan tabel berisi jutaan baris. (Tabel AdventureWorks terbesar berisi sekitar 100.000 baris paling banyak). Anda bisa mendownload database Contoso DW disini: microsoften-usdownloaddetails. aspxid18279. Contoso DW bekerja dengan sangat baik saat Anda ingin menguji kinerja pada query melawan tabel yang lebih besar. Contoso DW berisi tabel data warehouse standar yang disebut FactOnLineSales, dengan 12,6 juta baris. Itu tentu bukan tabel gudang data terbesar di dunia, tapi juga permainan anak-anak. Misalkan saya ingin meringkas jumlah penjualan produk untuk tahun 2009, dan memberi peringkat produk. Saya mungkin menanyakan tabel fakta dan bergabung ke tabel Dimensi Produk dan menggunakan fungsi RANK, seperti: Berikut adalah hasil parsial dari 10 baris teratas, oleh Total Sales. Di laptop saya (i7, 16 GB RAM), permintaan membutuhkan waktu 3-4 detik untuk dijalankan. Itu mungkin tidak tampak seperti akhir dunia, namun beberapa pengguna mungkin mengharapkan hasil hampir instan (seperti yang mungkin Anda lihat dari hasil instan saat menggunakan Excel melawan kubus OLAP). Satu-satunya indeks yang saya miliki saat ini di tabel ini adalah indeks berkerumun pada kunci penjualan. Jika saya melihat rencana eksekusi, SQL Server membuat saran untuk menambahkan indeks penutup ke tabel: Sekarang, hanya karena SQL Server menyarankan sebuah indeks, tidak berarti Anda harus secara membabi buta membuat indeks pada setiap pesan kuota indeks kuota. Namun, dalam hal ini, SQL Server mendeteksi bahwa kita memfilter berdasarkan tahun, dan menggunakan Product Key dan Sales Amount. Jadi, SQL Server menyarankan indeks penutup, dengan DateKey sebagai bidang indeks kunci. Alasan kami menyebutnya indeks quotcoveringquot adalah karena SQL Server akan melakukan kuota sepanjang fieldquot non-key yang kami gunakan dalam query, quotfor the ridequot. Dengan cara itu, SQL Server tidak perlu menggunakan tabel atau indeks berkerumun di semua mesin database hanya dengan menggunakan indeks pengaitan untuk kueri. Meliputi indeks sangat populer di data pergudangan dan pelaporan database skenario tertentu, meskipun harganya terjangkau oleh mesin database. Catatan: Meliputi indeks telah lama ada, jadi saya belum melihat indeks Columnstore dan Query Store. Jadi, saya akan menambahkan indeks penutupnya: Jika saya menjalankan kueri yang sama dengan saya, saya berlari beberapa saat yang lalu (yang mengumpulkan jumlah penjualan untuk setiap produk), kueri kadang tampaknya berjalan sekitar satu detik lebih cepat, dan saya mendapatkan Rencana eksekusi yang berbeda, yang menggunakan Indeks Seek dan bukan Index Scan (dengan menggunakan tombol tanggal pada indeks penutup untuk mengambil penjualan untuk tahun 2009). Jadi, sebelum Indeks Columnstore, ini bisa menjadi salah satu cara untuk mengoptimalkan kueri ini di banyak versi SQL Server yang lebih lama. Ini berjalan sedikit lebih cepat dari yang pertama, dan saya mendapatkan rencana eksekusi dengan Index Seek daripada Index Scan. Namun, ada beberapa masalah: Dua operator eksekusi quotIndex Seekquot dan quotHash Match (Aggregate) mengutip keduanya pada dasarnya mengoperasikan quotrow oleh rowquot. Bayangkan ini di meja dengan ratusan juta baris. Terkait, pikirkan isi tabel fakta: dalam kasus ini, satu nilai kunci tanggal dan atau satu nilai kunci produk dapat diulang di ratusan ribu baris (ingat, tabel fakta juga memiliki kunci untuk geografi, promosi, salesman , Dll.) Jadi, ketika quotIndex Seekquot dan quotHash Matchquot bekerja baris demi baris, mereka melakukannya atas nilai yang mungkin diulang di banyak baris lainnya. Ini biasanya di mana saya akan beralih ke indeks Columnstore SQL Server, yang menawarkan skenario untuk meningkatkan kinerja kueri ini dengan cara yang menakjubkan. Tapi sebelum saya melakukan itu, mari kita kembali ke masa lalu. Mari kembali ke tahun 2010, saat Microsoft memperkenalkan add-in untuk Excel yang dikenal sebagai PowerPivot. Banyak orang mungkin ingat melihat demo PowerPivot for Excel, di mana pengguna bisa membaca jutaan baris dari sumber data luar ke Excel. PowerPivot akan memampatkan data, dan menyediakan mesin untuk membuat Tabel Pivot dan Diagram Pivot yang tampil dengan kecepatan luar biasa terhadap data yang dikompres. PowerPivot menggunakan teknologi in-memory yang disebut Microsoft quotVertiPaqquot. Teknologi in-memory di PowerPivot pada dasarnya akan mengambil kunci duplikat kunci bisnis kunci utama dan menekannya ke satu vektor tunggal. Teknologi in-memory juga akan memilah-milah nilai-nilai ini secara paralel, dalam blok beberapa ratus sekaligus. Intinya adalah Microsoft memanggang sejumlah besar penyempurnaan kinerja ke dalam fitur memori VertiPaq yang bisa kita gunakan, langsung dari kotak pepatah. Mengapa saya mengambil jalan kecil ini menyusuri jalur memori Karena di SQL Server 2012, Microsoft menerapkan salah satu fitur terpenting dalam sejarah mesin database mereka: indeks Columnstore. Indeks benar-benar sebuah indeks hanya dalam nama: ini adalah cara untuk mengambil tabel SQL Server dan membuat kolom kolom terkompresi dalam memori yang memampatkan nilai kunci asing duplikat ke nilai vektor tunggal. Microsoft juga menciptakan kolam penyangga baru untuk membaca nilai vektor terkompresi ini secara paralel, menciptakan potensi peningkatan kinerja yang sangat besar. Jadi, saya akan membuat indeks kolom di atas meja, dan saya akan melihat seberapa jauh lebih baik (dan lebih efisien) kueri berjalan, versus kueri yang berjalan melawan indeks penutup. Jadi, saya akan membuat salinan duplikat FactOnlineSales (saya akan menyebutnya FactOnlineSalesDetailNCCS), dan saya akan membuat indeks kolom di tabel duplikat sehingga saya tidak akan mengganggu tabel asli dan indeks penutupan dengan cara apa pun. Selanjutnya, saya akan membuat indeks kolom di tabel baru: Perhatikan beberapa hal: Saya telah menetapkan beberapa kolom kunci asing, serta Angka Penjualan. Ingatlah bahwa indeks kolom tidak seperti indeks toko-toko tradisional. Tidak ada quotkeyquot. Kami hanya menunjukkan kolom mana yang harus dikompres SQL Server dan ditempatkan di kolom memori dalam memori. Untuk menggunakan analogi PowerPivot untuk Excel saat kita membuat indeks kolom, kita akan memberitahu SQL Server untuk melakukan hal yang sama seperti PowerPivot saat kita mengimpor 20 juta baris ke Excel menggunakan PowerPivot Jadi, saya akan menjalankan kembali query, kali ini menggunakan Tabel factOnlineSalesDetailNCCS yang digandakan yang berisi indeks kolomstore. Permintaan ini berjalan seketika dalam waktu kurang dari satu detik. Dan saya juga bisa mengatakan bahwa meskipun tabel itu memiliki ratusan juta baris, buku itu tetap akan terbeli oleh kuotasi kuantum. Kita bisa melihat rencana eksekusi (dan dalam beberapa saat, kita akan melakukannya), tapi sekarang saatnya untuk meliput fitur Query Store. Bayangkan sejenak, bahwa kami menjalankan kedua pertanyaan semalam: kueri yang menggunakan tabel FactOnlineSales biasa (dengan indeks penutup) dan kemudian kueri yang menggunakan tabel duplikat dengan indeks Columnstore. Saat kita masuk keesokan paginya, kami ingin melihat rencana eksekusi untuk kedua pertanyaan saat mereka berlangsung, begitu juga dengan statistik eksekusi. Dengan kata lain, kami ingin melihat statistik yang sama bahwa kami dapat melihat apakah kami menjalankan kedua kueri secara interaktif di SQL Management Studio, menyerahkan TIME dan IO Statistics, dan melihat rencana eksekusi tepat setelah menjalankan kueri. Nah, begitulah yang diminta oleh Toko Kueri agar kita dapat mengaktifkan (enable) Query Store untuk database, yang akan memicu SQL Server untuk menyimpan eksekusi query dan merencanakan statistik sehingga kita dapat melihatnya nanti. Jadi, saya akan mengaktifkan Query Store di database Contoso dengan perintah berikut (dan saya juga akan menghapus semua caching): Kemudian saya akan menjalankan dua query (dan quotpretendquot yang saya jalankan beberapa jam yang lalu): Sekarang mari kita berpura-pura berlari berjam-jam. Lalu. Menurut apa yang saya katakan, Query Store akan menangkap statistik eksekusi. Jadi bagaimana cara melihatnya? Untungnya, itu cukup mudah. Jika saya memperluas basis data Contoso DW, saya akan melihat folder Query Store. Toko Kueri memiliki fungsionalitas yang luar biasa dan saya akan mencoba meliputnya di entri blog berikutnya. Tapi untuk sekarang, saya ingin melihat statistik eksekusi pada dua query, dan secara khusus memeriksa operator eksekusi untuk indeks kolomstore. Jadi, saya benar-benar klik kanan pada Kuasa Mengonsumsi Sumber Daya Teratas dan menjalankan pilihan itu. Itu memberi saya bagan seperti di bawah ini, di mana saya bisa melihat durasi eksekusi (dalam milidetik) untuk semua pertanyaan yang telah dieksekusi. Dalam contoh ini, Query 1 adalah query terhadap tabel asli dengan indeks penutup, dan Query 2 melawan tabel dengan indeks kolomstore. Angka-angka itu tidak terletak pada indeks kolomstat mengungguli indeks tablecovering asli dengan faktor hampir 7 banding 1. Saya dapat mengubah metrik untuk melihat konsumsi memori. Dalam kasus ini, perhatikan bahwa query 2 (query indeks kolomstore) menggunakan lebih banyak memori. Ini menunjukkan dengan jelas mengapa indeks kolomstart mewakili teknologi kuotasi-memoriquot SQL Server memuat seluruh indeks kolom di memori, dan menggunakan kolam penyangga yang sama sekali berbeda dengan operator eksekusi yang ditingkatkan untuk memproses indeks. OK, jadi kita punya beberapa grafik untuk melihat statistik eksekusi kita bisa melihat rencana eksekusi (dan eksekusi operator) yang terkait dengan setiap eksekusi Ya, kita bisa Jika Anda mengklik pada batang vertikal untuk query yang menggunakan indeks kolomstore, Anda akan melihat eksekusi Rencanakan di bawah ini Hal pertama yang kita lihat adalah bahwa SQL Server melakukan scan indeks kolom, dan itu mewakili hampir 100 dari biaya kueri. Anda mungkin berkata, quotWait sebentar, kueri pertama menggunakan indeks penutup dan melakukan pencarian indeks jadi bagaimana pemindaian indeks kolom bisa lebih cepat? Pertanyaan yang sah, dan untungnya ada sebuah jawaban. Bahkan ketika query pertama melakukan pencarian indeks, ia masih mengeksekusi quotrow oleh rowquot. Jika saya meletakkan mouse di atas operator pemindai indeks kolom, saya melihat tooltip (seperti yang ada di bawah), dengan satu pengaturan penting: Mode Eksekusi adalah BATCH (berlawanan dengan ROW), yaitu apa yang kami lakukan dengan kueri pertama menggunakan Meliputi indeks). Mode BATCH mengatakan bahwa SQL Server sedang memproses vektor terkompresi (untuk nilai kunci asing yang diduplikasi, seperti kunci produk dan tombol tanggal) dalam jumlah hampir 1.000, secara paralel. Jadi SQL Server masih bisa mengolah indeks columnstore jauh lebih efisien. Selain itu, jika saya menempatkan mouse di atas tugas Hash Match (Aggregate), saya juga melihat bahwa SQL Server menggabungkan indeks kolom menggunakan mode Batch (walaupun operator itu sendiri mewakili persentase kecil dari biaya kueri) Akhirnya, Anda Mungkin bertanya, quotOK, jadi SQL Server memampatkan nilai dalam data, memperlakukan nilai sebagai vektor, dan membacanya di blok hampir seribu nilai secara paralel namun kueri saya hanya menginginkan data untuk tahun 2009. Begitu juga pemindaian SQL Server atas Seluruh rangkaian dataquot Sekali lagi, sebuah pertanyaan bagus. Jawabannya adalah, quotNot reallyquot. Untungnya bagi kami, pool buffer index kolom baru berfungsi melakukan fungsi lain yang disebut quotsegment eliminationquot. Pada dasarnya, SQL Server akan memeriksa nilai vektor untuk kolom kunci tanggal di indeks kolomstore, dan menghilangkan segmen yang berada di luar cakupan tahun 2009. Saya akan berhenti di sini. Dalam posting blog berikutnya, saya akan membahas indeks kolom dan Query Store secara lebih rinci. Intinya, apa yang telah kita lihat di sini hari ini adalah bahwa indeks Columnstore dapat secara signifikan mempercepat kueri yang memindai berdasarkan data dalam jumlah besar, dan Toko Kueri akan menangkap eksekusi kueri dan memungkinkan kita memeriksa statistik eksekusi dan kinerja di lain waktu. Pada akhirnya, kami ingin menghasilkan kumpulan hasil yang menunjukkan hal berikut. Perhatikan tiga hal: Kolom pada pokoknya pivot semua Alasan Kembali yang mungkin, setelah menunjukkan jumlah penjualan Hasil set berisi subtotal oleh tanggal akhir minggu (minggu) di semua klien (di mana Klien adalah NULL) Kumpulan hasil berisi jumlah keseluruhan Baris (dimana Client dan Date keduanya NULL) Pertama, sebelum masuk ke akhir SQL kita bisa menggunakan kemampuan pivotmatrix dinamis di SSRS. Kita hanya perlu menggabungkan dua set hasil dengan satu kolom dan kemudian kita dapat memberi umpan hasilnya pada kontrol matriks SSRS, yang akan menyebarkan alasan pengembalian di sumbu kolom laporan. Namun, tidak semua orang menggunakan SSR (walaupun kebanyakan orang seharusnya). Tapi bahkan saat itu, terkadang pengembang perlu mengonsumsi set hasil dalam sesuatu selain alat pelaporan. Jadi untuk contoh ini, mari kita asumsikan kita ingin menghasilkan hasil yang ditetapkan untuk halaman grid web dan mungkin pengembang ingin mengeluarkan kuota baris subtotal (di mana saya memiliki nilai ResultSetNum 2 dan 3) dan menempatkannya di kolom ringkasan. Jadi intinya, kita perlu menghasilkan output di atas langsung dari prosedur yang tersimpan. Dan sebagai twist tambahan minggu depan mungkin ada Return Reason X dan Y dan Z. Jadi kita tidak tahu berapa banyak alasan pengembalian yang ada. Kami ingin query sederhana untuk berpaling pada kemungkinan nilai yang berbeda untuk Return Reason. Di sinilah TIVASI T-SQL memiliki batasan yang kita butuhkan untuk memberikan nilai yang mungkin. Karena kita tidak tahu bahwa sampai run-time, kita perlu menghasilkan string query secara dinamis dengan menggunakan pola SQL dinamis. Pola SQL dinamis melibatkan pembuatan sintaks, sepotong demi sepotong, menyimpannya dalam sebuah string, dan kemudian mengeksekusi string di akhir. Dynamic SQL bisa jadi rumit, karena kita harus menanamkan sintaks di dalam sebuah string. Tapi dalam kasus ini, ini satu-satunya pilihan sejati jika kita ingin menangani sejumlah alasan pengembalian. Saya selalu menemukan bahwa cara terbaik untuk menciptakan solusi SQL yang dinamis adalah dengan mencari tahu apa query yang dihasilkan oleh kuotaalquot pada akhirnya (dalam kasus ini, mengingat alasan Kembali yang kita ketahui) dan kemudian membalik-ulangnya dengan memilah-milahnya Itu bersama satu bagian pada satu waktu. Jadi, inilah SQL yang kita butuhkan jika kita mengetahui Alasan Kembali (A sampai D) bersifat statis dan tidak akan berubah. Querynya adalah sebagai berikut: Menggabungkan data dari SalesData dengan data dari ReturnData, di mana kita quothard-wirequot kata Sales sebagai Tipe Aksi membentuk Tabel Penjualan, dan kemudian menggunakan Return Reason dari Return Data menjadi kolom ActionType yang sama. Itu akan memberi kita kolom ActionType yang bersih untuk diputar. Kami menggabungkan dua pernyataan SELECT ke dalam common table expression (CTE), yang pada dasarnya merupakan subquery tabel turunan yang kemudian kami gunakan dalam pernyataan berikutnya (untuk PIVOT) Pernyataan PIVOT melawan CTE, yang menetapkan jumlah dolar untuk Tipe Aksi Berada di salah satu nilai Action Type yang mungkin. Perhatikan bahwa ini adalah hasil akhir yang ditetapkan. Kami menempatkan ini ke CTE yang berbunyi dari CTE pertama. Alasan untuk ini adalah karena kita ingin melakukan beberapa pengelompokan di akhir. Pernyataan SELECT terakhir, yang terbaca dari PIVOTCTE, dan menggabungkannya dengan kueri berikutnya melawan PIVOTCTE yang sama, namun di mana kami juga menerapkan dua pengelompokan dalam fitur PENGATURAN SETELAH DI SQL 2008: MENGELOMPOKAN pada Tanggal Akhir Minggu (dbo. WeekEndingDate) PENGELOMPOKAN untuk semua baris () Jadi, jika kita tahu dengan pasti bahwa kita tidak akan pernah memiliki kode alasan pengembalian yang lebih banyak, maka itu akan menjadi solusinya. Namun, kita perlu memperhitungkan kode alasan lainnya. Jadi, kita perlu menghasilkan keseluruhan kueri di atas sebagai satu string besar di mana kita membuat kemungkinan alasan pengembalian sebagai satu daftar yang dipisahkan koma. Aku akan menunjukkan seluruh kode T-SQL untuk menghasilkan (dan mengeksekusi) kueri yang diinginkan. Dan kemudian saya akan memecahnya menjadi beberapa bagian dan menjelaskan setiap langkahnya. Jadi pertama, inilah keseluruhan kode untuk menghasilkan secara dinamis apa yang telah saya hadapi di atas. Pada dasarnya ada lima langkah yang perlu kita liput. Langkah 1 . Kita tahu bahwa di suatu tempat dalam campuran, kita perlu menghasilkan sebuah string untuk ini dalam query: SalesAmount, Reason A, Reason B, Reason C, Reason D0160016001600160 Yang dapat kita lakukan adalah membangun sebuah ekspresi tabel umum sementara yang menggabungkan kutipan kabel keras. Kolom Amountquot dengan daftar kode kemungkinan yang unik. Begitu kita memilikinya di CTE, kita bisa menggunakan sedikit trik bagus untuk XML PATH (3939) untuk menghancurkan baris tersebut menjadi satu string, meletakkan koma di depan setiap baris yang dibaca query, dan kemudian menggunakan STUFF untuk mengganti Contoh koma pertama dengan ruang kosong. Ini adalah trik yang bisa Anda temukan di ratusan blog SQL. Jadi bagian pertama ini membangun sebuah string yang disebut ActionString yang bisa kita gunakan lebih bawah. Langkah 2 . Kami juga tahu bahwa kami ingin SUM kolom dugaan yang dihasilkan, bersama dengan kolom penjualan standar. Jadi kita butuh string terpisah untuk itu, yang akan saya sebut SUMSTRING. Saya hanya akan menggunakan ActionString yang asli, lalu REPLACE kurung luar dengan sintaks SUM, ditambah tanda kurung asli. Langkah 3: Sekarang pekerjaan sebenarnya dimulai. Dengan menggunakan kueri asli sebagai model, kami ingin menghasilkan kueri asli (dimulai dengan UNION dari dua tabel), namun mengganti referensi ke kolom berporos dengan string yang kami buat secara dinamis di atas. Selain itu, meski tidak mutlak diperlukan, saya juga menciptakan variabel hanya kombinasi umpan balik jalur kereta yang ingin kami embed ke kueri yang dihasilkan (untuk keterbacaan). Jadi kita akan membangun seluruh query menjadi variabel yang disebut SQLPivotQuery. Langkah 4. Kami terus membangun kueri lagi, menggabungkan sintaks kami dapat kuotot-wirequot dengan ActionSelectString (yang kami hasilkan secara dinamis untuk menyimpan semua nilai alasan pengembalian yang mungkin) Langkah 5. Akhirnya, kami akan menghasilkan bagian terakhir dari Query Pivot, yang terbaca dari ekspresi tabel umum 2 (PIVOTCTE, dari model di atas) dan menghasilkan SELECT akhir untuk dibaca dari PIVOTCTE dan menggabungkannya dengan pembacaan 2 kali terhadap PIVOTCTE ke Menerapkan pengelompokan. Akhirnya, kita bisa quotexecutequot string menggunakan sistem SQL yang tersimpan proc spexecuteSQL Jadi mudah-mudahan Anda dapat melihat bahwa proses untuk mengikuti jenis usaha ini adalah Menentukan apa permintaan akhir, berdasarkan pada kumpulan data dan nilai Anda saat ini (yaitu dibangun Model kueri) Tuliskan kode T-SQL yang diperlukan untuk menghasilkan model kueri tersebut sebagai string. Arguably bagian yang paling penting adalah menentukan kumpulan nilai unik yang menjadi andalan Anda, dan kemudian menghancurkannya menjadi satu string dengan menggunakan fungsi STUFF dan trik FOR XML PATH (3939) Jadi, apa yang ada di pikiran saya hari ini. Setidaknya 13 item Dua Musim panas yang lalu, saya menulis draf BDR yang memusatkan perhatian pada peran pendidikan dan nilai latar belakang seni liberal yang baik tidak hanya untuk industri perangkat lunak tapi juga untuk industri lain. Salah satu tema BDR ini menekankan sudut pandang penting dan tercerahkan dari arsitek perangkat lunak terkenal Allen Holub mengenai seni liberal. Ill (dengan setia) menguraikan pesannya: dia menyoroti kesejajaran antara pemrograman dan mempelajari sejarah, dengan mengingatkan semua orang bahwa sejarah sedang membaca dan menulis (dan memberi, mengidentifikasi pola), dan pengembangan perangkat lunak juga membaca dan menulis (dan lagi, mengidentifikasi pola ). Maka saya menulis sebuah opini yang terfokus pada topik ini dan topik terkait lainnya. Tapi sampai hari ini, saya tidak pernah sempat mempublikasikannya. Sering sekali Id memikirkan untuk merevisinya, dan Id bahkan duduk selama beberapa menit dan membuat beberapa penyesuaian untuknya. Tapi kemudian kehidupan pada umumnya akan menghalangi dan Id tidak pernah menyelesaikannya. Jadi, apa yang berubah Beberapa minggu yang lalu, kolumnis CoDe Magazine dan pemimpin industri Ted Neward menulis sebuah artikel di kolom regulernya, Managed Coder, yang menarik perhatian saya. Judul artikelnya adalah On Liberal Arts. Dan saya sangat menyarankan agar semua orang membacanya. Ted membahas nilai latar belakang seni liberal, dikotomi palsu antara latar belakang seni liberal dan kesuksesan dalam pengembangan perangkat lunak, dan kebutuhan untuk menulis dengan baik. Dia berbicara tentang beberapa pertemuan masa lalunya dengan manajemen personalia HR mengenai latar belakang pendidikannya. Dia juga menekankan perlunya menerima dan menyesuaikan diri dengan perubahan dalam industri kami, sekaligus keunggulan profesional perangkat lunak yang sukses (dapat diandalkan, merencanakan ke depan, dan belajar untuk melewati konflik awal dengan anggota tim lainnya). Jadi bacaannya yang bagus, seperti juga artikel artikel dan blog Tested lainnya. Hal itu juga membuat saya kembali memikirkan pandangan saya tentang topik ini (dan topik lainnya) juga, dan akhirnya memotivasi saya untuk menyelesaikan editorial saya sendiri. Jadi, lebih baik terlambat daripada tidak pernah, inilah Bakers Dozen of Reflections saya saat ini: Saya memiliki sebuah perkataan: Air membeku pada suhu 32 derajat. Jika Anda dalam peran pelatihan, Anda mungkin berpikir bahwa Anda melakukan segala sesuatu di dunia untuk membantu seseorang padahal sebenarnya mereka hanya merasakan suhu 34 derajat dan karena itu hal-hal yang menghambat pemadaman untuk mereka. Terkadang dibutuhkan sedikit usaha atau katalis ideologis lain atau perspektif baru yang berarti bahwa mereka yang memiliki pendidikan terdahulu dapat memanfaatkan sumber yang berbeda. Air membeku pada suhu 32 derajat. Beberapa orang bisa mempertahankan tingkat konsentrasi yang tinggi meski dengan ruangan yang penuh dengan orang yang berisik. Saya tidak salah satu dari mereka kadang-kadang saya memerlukan beberapa privasi untuk memikirkan masalah kritis. Beberapa orang menggambarkan hal ini karena Anda harus belajar untuk menjauh darinya. Dengan kata lain, pencariannya untuk udara yang langka. Seminggu yang lalu aku menghabiskan berjam-jam di ruang sepi dan sepi dengan papan tulis, sampai aku benar-benar mengerti sebuah masalah. Baru pada saat itulah saya bisa berbicara dengan pengembang lain mengenai sebuah solusi. Pesan di sini bukan untuk mengkhotbahkan bagaimana Anda harus membicarakan bisnis Anda untuk memecahkan masalah tetapi lebih kepada setiap orang untuk mengetahui kekuatan dan apa yang berhasil, dan menggunakannya untuk keuntungan Anda sebanyak mungkin. Beberapa ungkapan seperti kuku di papan tulis untukku. Gunakan sebagai momen mengajar adalah satu. (Mengapa seperti kuku di papan tulis Karena jika Anda memiliki peran mentoring, biasanya Anda harus selalu mengikuti mode pengajar, namun dengan halus). Heres lain saya cant benar-benar menjelaskannya dengan kata-kata, tapi saya mengerti. Ini kedengarannya agak dingin, tapi jika seseorang benar-benar tidak dapat menjelaskan sesuatu dengan kata-kata, mungkin mereka tidak mengerti. Tentu, seseorang dapat memiliki perasaan tidak masuk akal tentang bagaimana sesuatu bekerja. Saya bisa menggertak dengan cara saya menjelaskan bagaimana sebuah kamera digital bekerja namun kenyataannya saya tidak benar-benar mengerti semuanya dengan baik. Ada bidang studi yang dikenal sebagai epistemologi (studi tentang pengetahuan). Salah satu dasar dasar untuk memahami apakah itu kamera atau pola desain - adalah kemampuan untuk membangun konteks, untuk mengidentifikasi rangkaian kejadian terkait, atribut dari setiap komponen di sepanjang jalan, dll. Ya, pemahaman terkadang sangat kerja keras. , Tapi menyelam ke topik dan memecahnya sepadan dengan usaha. Even those who eschew certification will acknowledge that the process of studying for certification tests will help to fill gaps in knowledge. A database manager is more likely to hire a database developer who can speak extemporaneously (and effortlessly) about transaction isolation levels and triggers, as opposed to someone who sort of knows about it but struggles to describe their usage. Theres another corollary here. Ted Neward recommends that developers take up public speaking, blogging, etc. I agree 100. The process of public speaking and blogging will practically force you to start thinking about topics and breaking down definitions that you might have otherwise taken for granted. A few years ago I thought I understood the T-SQL MERGE statement pretty well. But only after writing about it, speaking about, fielding questions from others who had perspectives that never occurred to me that my level of understanding increased exponentially. I know a story of a hiring manager who once interviewed an authordeveloper for a contract position. The hiring manager was contemptuous of publications in general, and barked at the applicant, So, if youre going to work here, would you rather be writing books or writing code Yes, Ill grant that in any industry there will be a few pure academics. But what the hiring manager missed was the opportunities for strengthening and sharpening skill sets. While cleaning out an old box of books, I came across a treasure from the 1980s: Programmers at Work. which contains interviews with a very young Bill Gates, Ray Ozzie, and other well-known names. Every interview and every insight is worth the price of the book. In my view, the most interesting interview was with Butler Lampson. who gave some powerful advice. To hell with computer literacy. Its absolutely ridiculous. Study mathematics. Learn to think. Read. Write. These things are of more enduring value. Learn how to prove theorems: A lot of evidence has accumulated over the centuries that suggests this skill is transferable to many other things. Butler speaks the truth . Ill add to that point learn how to play devils advocate against yourself. The more you can reality-check your own processes and work, the better off youll be. The great computer scientistauthor Allen Holub made the connection between software development and the liberal arts specifically, the subject of history. Here was his point: what is history Reading and writing. What is software development Among other things, reading and writing . I used to give my students T-SQL essay questions as practice tests. One student joked that I acted more like a law professor. Well, just like Coach Donny Haskins said in the movie Glory Road, my way is hard. I firmly believe in a strong intellectual foundation for any profession. Just like applications can benefit from frameworks, individuals and their thought processes can benefit from human frameworks as well. Thats the fundamental basis of scholarship. There is a story that back in the 1970s, IBM expanded their recruiting efforts in the major universities by focusing on the best and brightest of liberal arts graduates. Even then they recognized that the best readers and writers might someday become strong programmersystems analysts. (Feel free to use that story to any HR-type who insists that a candidate must have a computer science degree) And speaking of history: if for no other reason, its important to remember the history of product releases if Im doing work at a client site thats still using SQL Server 2008 or even (gasp) SQL Server 2005, I have to remember what features were implemented in the versions over time. Ever have a favorite doctor whom you liked because heshe explained things in plain English, gave you the straight truth, and earned your trust to operate on you Those are mad skills . and are the result of experience and HARD WORK that take years and even decades to cultivate. There are no guarantees of job success focus on the facts, take a few calculated risks when youre sure you can see your way to the finish line, let the chips fall where they may, and never lose sight of being just like that doctor who earned your trust. Even though some days I fall short, I try to treat my client and their data as a doctor would treat patients. Even though a doctor makes more money There are many clichs I detest but heres one I dont hate: There is no such thing as a bad question. As a former instructor, one thing that drew my ire was hearing someone criticize another person for asking a supposedly, stupid question. A question indicates a person acknowledges they have some gap in knowledge theyre looking to fill. Yes, some questions are better worded than others, and some questions require additional framing before they can be answered. But the journey from forming a question to an answer is likely to generate an active mental process in others. There are all GOOD things. Many good and fruitful discussions originate with a stupid question. I work across the board in SSIS, SSAS, SSRS, MDX, PPS, SharePoint, Power BI, DAX all the tools in the Microsoft BI stack. I still write some code from time to time. But guess what I still spend so much time doing writing T-SQL code to profile data as part of the discovery process. All application developers should have good T-SQL chops. Ted Neward writes (correctly) about the need to adapt to technology changes. Ill add to that the need to adapt to clientemployer changes. Companies change business rules. Companies acquire other companies (or become the target of an acquisition). Companies make mistakes in communicating business requirements and specifications. Yes, we can sometimes play a role in helping to manage those changes and sometimes were the fly, not the windshield. These sometimes cause great pain for everyone, especially the I. T. people. This is why the term fact of life exists we have to deal with it. Just like no developer writes bug-free code every time, no I. T. person deals well with change every single time. One of the biggest struggles Ive had in my 28 years in this industry is showing patience and restraint when changes are flying from many different directions. Here is where my prior suggestion about searching for the rarified air can help. If you can manage to assimilate changes into your thought process, and without feeling overwhelmed, odds are youll be a significant asset. In the last 15 months Ive had to deal with a huge amount of professional change. Its been very difficult at times, but Ive resolved that change will be the norm and Ive tried to tweak my own habits as best I can to cope with frequent (and uncertain) change. Its hard, very hard. But as coach Jimmy Duggan said in the movie A League of Their Own: Of course its hard. If it wasnt hard, everyone would do it. The hard, is what makes it great . A powerful message. Theres been talk in the industry over the last few years about conduct at professional conferences (and conduct in the industry as a whole). Many respected writers have written very good editorials on the topic. Heres my input, for what its worth. 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. So there you have it. 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. Importing SQL Server Data Using SSIS - Which Option is Fastest By: Daniel Calbimonte Read Comments (27) Related Tips: More Integration Services Development This article is useful for SSIS developers who do not know which tasks are best to use in an SSIS projects. The main problem is that at the end of development if performance is slow then you will need to rebuild the project and change components. This article shows various ways of importing data and shows which types of components perform best within SSIS. The contest will be between the following components: ODBC Tasks ADO NET Tasks OLEDB Task SQL Server Destination T-SQL Tasks I created different SSIS packages in order to test performance. In this demo I used SSIS 2012 and the database Adventureworks 2012 . In this demo I am going to import the table AdventureWorks2012.Sales. SalesOrderDetail to the test2 database which is on the same instance of SQL Server. SalesOrderDetails is the table with more rows in AdventureWorks2012. In order to create the database test2 and the destination table dbo. OrderDetails, use this T-SQL code: Test 1 - ODBC Tasks The first example will use ODBC Source and ODBC Destination as shown below: When we run the package we notice the average time is 5 minutes 57 seconds to import the rows: Test 2 - ADO NET Tasks As noticed, ODBC is pretty slow. Lets try another approach. We are going to truncate the destination table first: Lets try ADO tasks to import the same data and verify if these components are faster: The average elapsed time in my testing was 11 seconds. This is much better. Test 3 - OLEDB Tasks This time we are going to import the same data using the OLEDB Tasks. Again we will truncate the table in the test2 database first. The average elapsed time is 5 seconds. Note that I am using the fast load option with the Table Lock option in the OLE DB Destination Task: If we do not use the fast load option, the average elapsed time was 2 minutes and 21 seconds: OK. The fast load option really improves performance. I will return to that configuration. What about the OLE DB Source. By default I am using the option Table or view in the OLE DB Source as shown below: Lets use a SQL Command instead as shown below. The average elapsed time is 2.85 seconds . Test 4 - SQL Server Destination Now, lets try to use the SQL Destination as the destination instead of OLE DB Destination: The average elapsed time is 2.5 seconds. At this point it is the best option. Test 5 - Execute T-SQL Task Finally, some people think that the best option is to use the Execute T-SQL Task: I am using a simple insert statement to import data from one source to another: The average elapsed time is 1.8 seconds Finally Ive been told that if the query runs inside a stored procedure it is even faster: Lets create a stored procedure: After creating the stored procedure we are going to call it in the Execute T-SQL Task: The average elapsed time is 2.12 seconds . The stored procedures does not improve performance. Lets review the table with the results: You may think the morale of the story is to use the Execute T-SQL Task instead of other SSIS tasks. In this example we were importing data on the same instance, but this will not always be the case. So the morale of the story is that there are many alternatives when creating a SSIS project and we have to carefully study the alternatives in different scenarios. There are great SSIS tools and we do not always use the best options. With each new version of SSIS new tasks are added and performance may be improved with existing tasks. The main changes in SSIS for SQL 2008 and 2012 are related to performance improvements. Next Steps If you are working in a SSIS project make sure you are using the best tasks and also verify if there are other SSIS tasks that can be used in your project. Also make sure you are following the best practices recommended by the experts: Last Update: 7132012 Great read and analysis, but I have one caveat to add. If you need to move a large amount of data, you need to take care of the transaction log growth. This is not a much of a concern using SSIS. For instance, I needed to move 1.3 billion rows (15 columns) and began using TSQL which quickly filled my logs. However, using OLE DB Source and Destination (Bulk Inserts) with fast load, there was little impact to the log file. Thursday, September 20, 2012 - 9:19:12 AM - vinodhkumar Its very useful. great job. Thanks Monday, August 27, 2012 - 10:54:42 AM - Orlando Colamatteo I agree with some others that the testbed is a bit contrived. If youre looking to move data from one table to another on the same instance then SSIS will rarely be a viable option. Some form of T-SQL will almost certainly outperform an SSIS operation. A more realistic scenario is moving data between two disparate data sources. It is surpising how poorly the ODBC Destination performs, especially in light of what Microsoft has publicly said in that they will be moving away from OLE DB interfaces and standardizing on ODBC in future products: In the ODBC Destination I expected Microsoft to implement the loading of data via the bulk load API as they did with the FastLoad option of the OLE DB Destination. On a separate note regarding loading data into MySQL with SSIS: In the past I did some performance tests with the Cherry City OLE DB driver for MySQL and it is horribly slow as it only inserts one row at a time. This is not to mention the fact that it crashed BIDS regularly when developing with it. Given the lack of a benefit I would stick with the tools built into SSIS and avoid the hassle of installing and configuring a third party driver. If youre using SSIS 2005 I would recommend using a Script Component as a Destination and issuing batch-inserts against a connection made using the MySQL ODBC Driver: msdn. microsoften-uslibraryms135939.aspx If youre using SSIS 2008 I would recommend using an ADO NET Destination with the MySQL ODBC Driver. In my tests it was only able to achieve about 240 rowsminute throughput to MySQL which is quite disappointing: msdn. microsoften-uslibrarybb895291(vsql.105).aspx If youre using SSIS 2012 I would recommend using an ODBC Destination with the MySQL ODBC Driver. In my tests it outperformed the ADO NET Destination over 3 to 1 but still only achieved about 800 rowsminute throughput, which was still quite disappointing: msdn. microsoften-uslibraryhh758691(vsql.110).aspx