All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world. WebinarsExtending DevOps practices to SQL Server databases with ReadyRoll from Redgate - In this webinar, Microsoft MVP Steve Jones and Redgate’s Arneh Eskandari will show you how Redgate’s Database DevOps solution can work to improve your database development and deployment processes. You'll see a demo of the following Redgate tools - ReadyRoll, DLM Automation and DLM Dashboard - and see how they plug into GIT. There will also be plenty of time to ask Steve and Arneh questions. ...(more) Virtualization and ContainersCreating Azure Virtual Machine Performance Alerts - How to use Azure virtual machine performance threshold alerts to trigger email alerts or start automated-response actions....(more) Vendors/3rd Party ProductsData Governance Implementation Survey - If your organization uses SQL Server, Redgate would love to hear whether you're interested in, or are already implementing, a Data Governance Programme. Complete their Data Governance Implementation Survey to be entered you into a prize draw to win a $100 Amazon gift card....(more) T-SQLMissing Data - In the real world of business or scientific reporting and analysis, data can prove to be awkward. It can be plain wrong or it can be altogether missing. Sure, we have the NULL to signify unknown, but that doesn't play well with regular business reporting. There are a number of ways of dealing with missing information, and methods of estimating data from existing data has a long and respectable history. Joe Celko gets to grips with a data topic that is often treated with some trepidation....(more) Trigram Wildcard String Search in SQL Server - If the searched data is not large, or response time requirements are not critical, using LIKE '%match%' could well be a suitable solution. But, on the odd occasion where the need for a super-fast search beats all other considerations (including storage space), you might consider a custom solution using n-grams. The specific variation explored in this article is a three character trigram....(more) How to Safely Parameterize SQL Table Names - How using sp_executesql to parameterize our dynamic SQL statements protects us from SQL injection attacks....(more) SQL Server: SARGability part 2 - Placing functions around columns in the WHERE clause, can significantly reduce performance of the query, depending on what the function is....(more) Learn how to unit test SQL Server T-SQL code - Ed Elliott offers a free email course on how to use tSQLt including the technical aspects of writing unit tests and the art of writing repeatable, useful unit tests for even the most complicated T-SQL code....(more) How Much Can One Column Change A Query Plan? Part 1 - Erik Darling investigates two queries that differ only by one selected column, but where the two query plans are wildly different....(more) SQL Code Smells - Some time ago, Phil Factor wrote his booklet 'SQL Code Smells', collecting together a whole range of SQL Coding practices that could be considered to indicate the need for a review of the code. It was published as 119 code smells, even though there were 120 of them at the time. Phil Factor has continued to collect them and the current state of the art is reflected in this article. There are now around 150 of these smells and SQL Code Guard is committed to cover as many as possible of them....(more) ToolsProviding the Necessary Tools and Reports for Very Large IT Projects - For the larger development project using Agile/DevOps, there will always be the problem of comprehensive tooling and reporting. An all-encompassing ALM Integrated Development Environment will have some of what you need but is unlikely to meet your special requirements; Neither will a DevOps toolchain. Where do you start? You'll need to specify up-front what those requirements are, as Mohammad Rizvi explains....(more) SQL Server on LinuxSQL Server on Linux is the New SQL Server on Windows Core - SQL Server 2017 runs on Linux, and the similarities between that and Windows Core are eerie. So why will you hear so much more from Microsoft about Linux than you ever did about Windows Core?...(more) R LanguageA guide to parallelism in R - How to parallelize R code on your computer using foreach....(more) SQL Server R Services: Working with Data Frames - Although you can get started with R in SQL Server without understanding data frames, they are a key structure of the R language that are the equivalent of SQL Server table variables. They give you many ways of manipulating and analysing data and passing it between R and SQL Server. For a Database professional, they provide a clear and familiar concept when getting to grips with integrating R into the database....(more) PowerShellDisk block size with PowerShell - There are various recommendations on disk block size for SQL Server, so how do you check the disk block size for a given server?...(more) PowerPivot/PowerQuery/PowerBISetting up a Drillthrough Action in your Power BI Report - The Microsoft Power BI folks have turned on the drillthrough feature in the Power BI service....(more) Power BI Custom Data Connector For Language Detection, Key Phrase Extraction And Sentiment Analysis - Chris Webb has published his first Power BI custom data connector on GitHub....(more) Create a Date Dimension in Power BI in 4 Steps – Step 2: Fiscal Columns - Showing how to add fiscal columns calculated in the date dimension. Many of business reports generates on fiscal year, fiscal quarter, and fiscal period, so having fiscal columns is important part of a date dimension....(more) Power BI & SQL Server Always On Availability Groups - How to connect to your SQL Server AlwaysOn Availability Group (AG) Secondary Read Replica using Power BI Desktop and the On-Premises Data Gateway. ...(more) Performance Tuning SQL ServerToolbox - IO, IO, Why are You So Slow? - Troubleshooting performance in SQL Server, or almost any other system, is often an iterative process of discovering a bottleneck, fixing it, and then discovering the next bottleneck. ...(more) Creating Basic Indexes on the Stack Overflow Public Database - Knowing what indexes to add means knowing your workloads, but we don’t usually know our database workloads until they start. However, you can start by indexing your foreign key relationships between tables. ...(more) NoSQLCloud Database? NoSQL? Nah, just use CSVs and CsvHelper - Architecting a new system that needs a database, but will only store less than 100 MB of data. What are the options? CosmosDB or perhaps Azure Table Storage? Nah...let's just use CSV files and CsvHelper....(more) ETL/SSIS/ELTSSIS ForEach Enumerator File Order - Ed Elliott investigates how the SSIS file enumeraror orders, or more specifically doesn't order, files....(more) Database Design, Theory and DevelopmentDatabase Fundamentals #9: Schemas as Containers - Schemas are a very useful tool for managing the objects in your database. From security through process, through placement, schemas provide you with another tool to control how your database behaves....(more) Data PrivacyGuide available for enhancing privacy and addressing GDPR requirements with the Microsoft SQL platform - The EU Global Data Protection Regulation (GDPR) goes into effect on May 25, 2018 - and we know that many of you are searching for guidelines and recommendations for how to properly handle the data privacy and data protection requirements stipulated by the GDPR. This guide provides technical guidance and best practices for addressing data privacy concerns and enhancing your overall data protection and security strategy with Microsoft SQL technologies....(more) Data Mining/Data AnalysisMicrosoft aims to take the work out of data wrangling with coming 'Pendleton' tool - Microsoft is testing privately a tool for data scientists for data preparation and cleaning that is codenamed 'Pendleton.'...(more) Data Access / ORMsManaging the Password of the Application’s User - How can you ensure that passwords conform to a policy, even if they are used for connecting applications to databases? Absolving them from the policy is a security risk and you don't want to trouble the users with password changes. Dennes Torres shows how to manage application users’ passwords in Entity Framework; so that no one, not even the database and IT administrators, knows the current password....(more) Conferences and Events14 years of Summit… - Looking back at key moments in 14 years of attending PASS Summit....(more) Luck and the PASS Summit - Grant Fritchey explains why an article on whether or not we downplay the role that luck plays in our lives immediately made him think of the PASS Summit....(more) Find out more about database DevOps at an event near you - To demonstrate how large companies have introduced DevOps to the database, Redgate regularly speaks at major events and conferences. In informative and practical presentations, their experts show how the database can transform from being the blocker to the enabler. Find out what events they're speaking at....(more) Discover database DevOps at Microsoft Ignite - With the event fast approaching, Redgate's Mary Robbins shares her six top tips for Microsoft Ignite 2017. Find out how to make the most of your time in sunny Orlando, and read about how you can get involved with the Redgate team. Read the blog post....(more) Career GrowthThe Stack - Thomas LaRock on why he kept his stack of rejection letters from various schools, companies and agencies. Until now. ...(more) How I Manage Email and Tasks, 2017 Edition - Brent Ozar on how he manages his email and task lists....(more) How much can you learn in 3 content packed days? - A lot, if you join us at PASS Summit. It’s action-packed learning at its best, filled with the latest Data Platform know-how. Connect with experts like Grant Fritchey, Kendra Little, and Denny Lee as they share their passion for data....(more) Azure SQL Data Warehouse and Data LakeCommon ISV application patterns using Azure SQL Data Warehouse - Discussing the main characteristics of successful common ISV patterns, some common considerations/workarounds and finally, walking through three common patterns from our production customers....(more) Querying Multi-Structured JSON Files with U-SQL in Azure Data Lake - Melissa Coates explains how to handle cases where your reporting tool needs to work with a standard, predictable data structure, but that structure varies in the source JSON files....(more) Azure SQL Data Warehouse Workload Patterns and Anti-Patterns - Clarifying some of the concepts around RDBMS usage related to OLTP and OLAP workload, Symmetric Multiprocessing (SMP) and Massively Parallel Processing (MPP), workload patterns and anti-patterns, focusing on Azure SQL DW....(more) Analysis Services / BI on the MS StackBiml Query Table Builder - How to combine the awesomeness that is SQL Server's ability to expose a query's metadata with creating Biml Table objects....(more) Storage differences between calculated columns and calculated tables - Compares the differences between calculated columns and calculated tables from a processing and storage point of view....(more) Administration of SQL ServerWho’s Updated My Rows?! Interrogating the Transaction Logs - How to interrogate the transaction log to find out when modifications were made to our table and who made them....(more) Setting the Fill Factor - Please, please, please Admins do not leave your default index fill factor at 0. This means you are telling SQL Server to fill the page 100% full when creating indexes. This also means you are forcing it to a new page when additional inserts are done. ...(more) Six Scary SQL Surprises - The working life of the DBA can be punctuated by surprises, but they aren't generally nice surprises. This is especially true if the DBA is not checking and monitoring the databases for obvious things such as database corruption, and disk space. However, the really scary surprises are less obvious and provide fewer warning signs. Brent Ozar gives six scary surprises that can be avoided by the shrewd DBA....(more) |