R Sql Tableau United


R Sql Tableau United I’ve been using SQL Server for years, working a lot with Tableau more recently, and playing around with R because it interests me. Tableau works seamlessly with SQL and can connect to R as well, and now SQL 2016 has a built-in R server with lots of potential. So I thought about uniting all three of them: The first obstacle to overcome was the execution of basic R code in SQL Server. The installation is straightforward as part of SQL 2016 (https://msdn.microsoft.com/en-us/library/mt696069.aspx), but there’s an issue […]

Easy Transposing (Cross-Tabulation) of Any Relation Table

Database icons on green background. Vector illustration.

The challenge Sometime we need to transpose a table, that is, to turn unique values from one or more column in the table into multiple columns in the results. This operation might actually de-normalize the data in the relational table. For example, let’s refer to table Production.Product in the sample database AdventureWorks. Each ProductID belongs to a ProductLine, as shown in the sample below: Suppose we want to display the following result sets: A list in a row of all Name values per each ProductLine: A list in a row […]

Rebuilding a HEAP with nonaligned index

blue digital binary data on computer screen. Close-up shallow DOF

This post is designed to help you rebuild a HEAP with nonaligned indexes and without any issues. Starting in SQL Server 2008, you can rebuild an entire HEAP table using the command: ALTER TABLE tbl_Demo REBUILD Moreover, if the HEAP table is partitioned, you may specify a particular partition number to be rebuild as follows: ALTER TABLE tbl_Demo REBUILD PARTITION = n While n is the partition number being rebuild in order (read more about this command here). While performing a daily regular index maintenance operation to rebuild all indexes and […]

How to Optimize Your Optimization Plan

SEO optimized red stamp isolated on white background.

Database optimization plans are great. But the truth is that optimization plans must be optimized as well.The following maintenance plan for optimization was created at a client sites. You may notice that this is incorect. I thought I’d share this and explain how to correct it so you can avoid making the same mistake or correct your own maintenance plans.

Super Boost Data Loads with UNUSABLE

download transfer speed: speedometer and progress bar

Indexes are a great tool for performance tuning and often provide a drastic performance boost to our SQL statement by reducing the amount IO performed. However, indexes also have a performance penalty during DML operations (Insert / Update / Delete), and especially during heavy data loads to the table. This is due to index maintenance overhead, as each change in the table causes a corresponding change in the index. And the more indexes exist for the table, the more acute this overhead becomes. To speed up the data load it […]