The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I've been at the Redgate office all week, broadcasting SQL in the City and meeting with product teams. Quite a bit of our focus is on the GDPR law, with enforcement beginning in May. We're building and enhancing tools to help you cope. That means I've been spending a significant amount of time trying to learn and comprehend the potential impact to data professionals, both to help guide customers, but also to ensure our software will help them ensure compliance in their data systems.

Over the last year I've seen lots of doom and gloom, everyone needs to fix their systems and ensure data can be deleted on demand concerns in the media over the GDPR. Ultimately I think there is work to be done for many companies in the EU or those that sell products and services to the EU, but we don't quite know to what extent we need to change existing applications and databases. Lawyers and solicitors will sort some of this out across the next few years, though I certainly think any data breaches in the next year will be dealt with more harshly by regulators if organizations haven't made any effort to secure their systems.

There are also some simple things that I think most of us should just do. When I first saw the addition of data classification in SSMS, it seemed fairly trivial. However, the more I've thought about it, this simple addition is a way of ensuring that I can easily spend a relatively little amount of time to just think about the information in a database. Just tracking this down can be a pain, and if the information isn't recorded in an easy to access format, it's easy to forget what items need our focus. Using Extended Properties is a great idea, as the information is kept with the database, but this means that a better interface than the table properties is needed. There are also a few potential problems doing things this way, but this is a good start to becoming better data stewards.

That's what we need. A few good starts. We need to see this as an opportunity to clean up practices and move forward in a way that shows us to be professional data professionals that take our responsibility for data security, accuracy, and usage seriously. This is a chance to move forward in a way that reduces our risk of losing data, of becoming the next "headline" corporation or government agency that makes a mistake. Instead, we can embrace this as an opportunity to find new ways of managing our data and extracting information while still complying with data privacy rules.

I read a white paper that talk about the challenges of IoT data in a GDPR world. There are issues to be concerned about, but this also means there will be opportunity for those that devise better data handling methods, that learn to clearly disclose their purpose and practices. I suspect most people realize that there is a certain amount of information that companies collect, and that if there is some value or usefulness that company gives back, we can accept their data storage. Where individuals often become concerned is when organizations move their data to other parties without their consent.

To me, this means it becomes more important for an individual company to understand and process their own data. There will be less movement of data between companies, and perhaps less ability to purchase and import data from others. We'll need to help our organization extract more value from the data we are allowed to hold, meaning those of us that are data professionals will become more important. At least, that's what I hope happens.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

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.

Webinars

PASS Marathon GDPR in partnership with Redgate - This special edition of the PASS Marathon series will focus on the General Data Protection Regulation (GDPR). You’ll learn how the legislation impacts SQL Server data professionals around the globe and practical steps you can take to help ensure you’re ready for when GDPR enforcement takes effect on May 25th....(more)

Vendors/3rd Party Products

Masking Dates in a Non-Production Database - All too often there’s a reluctance to use masked data since it’s perceived to be just X’s and N’s and therefore not useable. That’s not the case if you use Redgate’s Data Masker tool....(more)

SQL Provision adds fully integrated data masking - SQL Provision launched in January, offering users blazingly fast database copying, with a light storage footprint, centralized management, and the ability to mask any sensitive data, prior to distribution. This new release takes compliant provisioning one step further, by integrating data masking directly into SQL Clone’s image creation process, rather than running it as a separate step prior to the image creation....(more)

T-SQL

SQL SERVER – Puzzle – How Does Table Qualifier Work in INSERT Statement? - Today’s puzzle is very interesting and I am confident that you will find that very interesting. Today’s puzzle is about Table Qualifier. I have not talked about this subject on my blog for a while, and I feel confident that you will like it very much. Generally, ......(more)

New Built-in Function CONCAT_WS() – SQL Server 2017 - In my previous post on new functions in SQL Server 2017, I discussed about the STRING_AGG function. Here, I am going to discuss about the function CONCAT_WS. This function concatenates a variable number of arguments with a delimiter specified in the ......(more)

Dates and Times in SQL Server: SMALLDATETIME - Last week I spoke about a world wary datatype for storing dates and times in a single column, with a granularity of three milliseconds. But let’s say you don’t need that kind of accuracy and are happy with a granularity to the nearest minute. Maybe you’re ......(more)

Altering table types, part 2 - Altering table types - a compatibility guide In yesterday's post, I altered a table type. Pray I don't alter them further. What else is incompatible with an integer column? It's just a morbid curiosity at this point as I don't recall having ever seen ......(more)

Pop Quiz - REPLACE in SQL Server - It's amazing the things I've run into with SQL Server this week that I never noticed. In today's pop quiz, let's look at REPLACE DECLARE @Repro table( SourceColumn varchar(30));INSERT INTO @Repro( SourceColumn)SELECT D.SourceColumnFROM( ......(more)

Row Goals, Part 2: Semi Joins - This post is part a series of articles about row goals. You can find the first part here: Part 1: Setting and Identifying Row Goals It is relatively well-known that using TOP or a FAST n query hint can set a row goal in an execution plan (see Setting ......(more)

Homebuilt sequential columns - I gave my introductory session on transactions at all three of the South African SQL Saturdays in 2016, as well as at SQL Saturday Oregon in October 2017, and something that came up in most of them was the ‘manual sequence’, the idea of using a column ......(more)

Changing Data Types on Large Tables: The INT to BIGINT Conundrum - During one of our regular SQL Server heath checks, using sp_blitz, one of our largest production tables raised a worrying alert. The ID column of a table holding customer order information was an INT datatype, and it was running out of numbers. The ......(more)

Fifteen Things I Hate About ISNUMERIC - Yello! SELECT ISNUMERIC('$') AS [What] UNION ALL SELECT ISNUMERIC('£') AS [What] UNION ALL SELECT ISNUMERIC(',') AS [What] UNION ALL SELECT ISNUMERIC('.') AS [What] UNION ALL SELECT ISNUMERIC('0e+99') AS [What] UNION ALL SELECT ISNUMERIC('2e2') ......(more)

SET IMPLICIT_TRANSACTIONS ON Is One Hell of a Bad Idea - By default, when you run a query in SQL Server, your delete/update/insert (DUI) is finished as soon as your query finishes. That’s because the default behavior of SQL Server is IMPLICIT_TRANSACTIONS OFF – meaning, SQL Server doesn’t hold your queries ......(more)

New Built-in Function STRING_AGG() – SQL Server 2017 - SQL Server 2017 introduces a set of useful functions like STRING_AGG(), STRING_SPLIT(), TRIM()… and many more. In this tip, I am going to discuss one of the built-in aggregate functions – “STRING_AGG()”. The function concatenates values from rows as ......(more)

The Lighter Side

California now allows driverless cars without a human behind the wheel - On Monday, the California Department of Motor Vehicles approved new rules that would allow self-driving ......(more)

King of the crossovers? The 2018 Volvo XC60 T8 makes a convincing case - Jonathan Gitlin The renaissance at Volvo is in full swing. Flush with funds and a supportive owner, its products over the past few years have proven you don't need to look to Germany to see a luxury car maker at the top of its game. This corner of the ......(more)

Details emerge about Porsche’s new electric car, charging network - The electric vehicle market is finally about to get interesting. It's been some time since Tesla proved that EVs didn't have to involve hair shirts, and at long last some other automakers are ready to enter the fray. Later this week at the Geneva ......(more)

SQL Server Security and Auditing

Who installed SQL Server? - Recently, I was working with the team and got stuck up with SQL Server Installation information. We got an urgent requirement to patch the physical box from Windows team. As the server was not in production, so the server entry was missing. Since Windows ......(more)

SQL Server Security

Login failed in SQL Server due to SSMS Intellisense - Hello all, Recently, I had worked on an interesting Login failed error constantly getting reported in SQL Error log. In this blog, we are covering the steps taken to fix the issue. Below error was reported in Error log: 2018-02-22 09:48:19.76 Logon ......(more)

SQLCLR vs. SQL Server 2012 & 2014 & 2016, Part 7: “CLR strict security” – The Problem Continues … in the Past (Wait, What?!?) - If the new “CLR strict security” Server-level configuration option in SQL Server 2017 hasn’t caused enough confusion and pain, then, as Eagle Man says, “I’ve got something for you”. You can now enable this super-fun setting in SQL Server 2012, 2014, ......(more)

Double hop error when using SQLCMD - tl;dr; SQLCMD v2014 and up has special requirements for Kerberos. One of the problems with linked servers (no rude noises please) is that frequently you will see a double hop error. Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ Let’s start with ......(more)

SQL Server on Linux

Change the name of master database files – SQL Server on Linux - In my recent blog, I discussed about how to move master database to another location. One of the readers requested to blog about renaming the master database files. So, we are going to explore step by step process of renaming the files in this blog. Change ......(more)

SQL Server News

SQL Server 2017 Cumulative Update 4 - On February 20, 2018, Microsoft released SQL Server 2017 Cumulative Update 4, which is Build 14.0.3022.28. I count 55 hotfixes in the public fix list. There is a special T-SQL script in the release notes that you need to run if you are using Query Store ......(more)

Security news and thoughts

More Bad News on the Massive Equifax Data Breach - Equifax said Thursday that an additional 2.4 million Americans were impacted by last year's data breach, however these newly disclosed consumers had significantly less personal information stolen. The company says the additional consumers only had their ......(more)

New CPU Microcode Updates for Intel Processors - Intel has released a new round of CPU microcode updates that address the Spectre variant #2 CPU vulnerability that now include older processor microarchitectures (all the way back to Haswell). There is always a delay between when Intel makes these updates ......(more)

I've Just Added 2,844 New Data Breaches With 80M Records To Have I Been Pwned - Presently sponsored by: DigiCert: IoT security can be filled with overwhelming identity challenges. One simple change can help you address nearly every one of them.tl;dr - a collection of nearly 3k alleged data breaches has appeared with a bunch of data ......(more)

Feds have spent 13 years failing to verify whether passport data is legit - For over a decade, Customs and Border has failed to properly verify e-passports (which contain biometric data) as "it lacked the software to do so," according to a new letter sent by two top senators....(more)

Developer gets prison after admitting backdoor was made for malice - An Arkansas man has been sentenced to serve almost three years in federal prison for developing advanced malware that he knew would be used to steal passwords, surreptitiously turn on webcams, and conduct other unlawful actions ......(more)

Your Apple iCloud data is now stored on Google servers—surprised? - Apple has updated its publicly available iOS security documentation to disclose that personal data associated with the company’s iCloud service is stored in cloud servers ......(more)

Replication

SQL Server Replication Enhancement – Dynamic Reloading of Agent Profile Parameters - With SQL Server 2017 Cumulative Update 3, we introduced an improvement to SQL Server Replication wherein, changes to the Replication agent parameters can be reloaded dynamically, without having to restart the agent. This improvement will be available ......(more)

Configuring Replication with Availability Groups - I fancy myself more of a Jedi, but I appreciate the Empire’s dedication to replication. I’ve used replication and Availability Groups together before. Replication works great with an AG as both a publisher or a subscriber (though, not as a distributor–you ......(more)

R Language

Machine Learning in R with TensorFlow - Modern machine learning platforms like Tensorflow have to date been used mainly by the computer science crowd, for applications like computer vision and language understanding. But as JJ Allaire pointed out in his keynote at the RStudio conference earlier ......(more)

Stripping Out HTML With StringR - This is a quick post today on removing HTML tags using the stringr package in R. My purpose here is in taking some raw data, which can include HTML markup, and preparing it for a vectorizer. I don’t need the resulting output to look pretty; I just want ......(more)

Product Upgrades and Releases

First Responder Kit Release: SQLBits was pretty awesome but now I have to do work again - APRIL FOOLS! IT’S ONLY MARCH FIRST! Tomorrow. Butthead. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1407: @parlevjo2 gave us a check to make sure databases are owned by an existing user. Existence is everything, so ......(more)

Microsoft readies Python, Java support for its bot-building framework - Microsoft may be ready to rev up (again) its conversation as a service strategy, with new additions to its bot-framework toolset. ...(more)

Microsoft could opt to release a free version of Teams after all - Microsoft may be readying a new free version of its Teams group-chat service, according to a new report. ...(more)

PowerShell

Introducing Jojoba 4, the multi-threaded testing framework for PowerShell - I've been hard at work this weekend updating Jojoba to version 4 and which is now available on the PowerShell Gallery. ...(more)

PowerPivot/PowerQuery/PowerBI

Power BI Custom Visuals Class (Module 92 – KPI Ticker MAQ Software) - In this module you will learn how to use the KPI Indicator Custom Visual by MAQ Software. The KPI Ticker displays frequently changing metrics with a with a trend indicator. Module 92 – KPI Ticker (MAQ Software) Downloads Power BI Custom Visual – KPI ......(more)

Power BI Custom Visuals Class (Module 91 – Smart Filter by OKViz) - In this module you will learn how to use the Smart Filter by OKViz. The Smart Filter works similar to a traditional slicer in Power BI but has a better user experience in many cases. Module 91 – Smart File by OKViz Downloads Power BI Custom Visual ......(more)

Performance Tuning SQL Server

55 Point SQL Server DATABASE Health Check Checklist - Some clients want a FULL SQL Health Check, while some only want to go deep on analysis for a SINGLE database. This is a SINGLE database health check. Even though it’s a single db health analysis, we still check for some server/instance level items. That’s ......(more)

Can I Force Multiple Plans for a Query in Query Store? - Nope. At least, not right now. I started thinking about this when I noticed that the sys.sp_query_store_unforce_plan requires you to specify both a @query_id and a @plan_id. If there’s only ever one plan that can be forced for a query, why would I need ......(more)

Adaptive Joins - I was surprised to find out that a lot people hadn’t heard about the new join type, Adaptive join. So, I figured I could do a quick overview. Adaptive Join Behavior Currently the adaptive join only works with columnstore indexes, but according to Microsoft, ......(more)

7 Indexing Tips to Improve SQL Server Performance - SQL Server performance is always one of the most challenging subjects. Indexes are commonly created to gain additional performance from the system, but sometimes they are duplicated, missing, or unused, which can adversely affect database performance ......(more)

MDX/DAX

How to use RANKX in DAX (Part 1 of 3 – Calculated Columns) - When I first started to play with DAX, one of the functions that seemed to confuse me more than it should, was how to add ranking to my data. It didn’t seem to matter what the data was, if I followed the online documentation sometimes it would work, ......(more)

Machine Learning

Business Understanding for Machine Learning – Predictive and Prescriptive Analysis - In the last Post, the explanation about machine learning and what is descriptive analysis has been provided. In this post, I am going to provide some overview of the Predictive and Prescriptive analysis Predictive Analysis Another analysis in machine ......(more)

Hardware News

XPS 13 2018 review: Dell’s improvements propel this laptop forward - Enlarge The XPS 13 laptop needed an overhaul and Dell needed to make a statement. The XPS family has produced some of the best and most-loved consumer ultrabooks, but this particular laptop has been stifled in recent years. Since 2016, it has seen ......(more)

Western Digital Demos SD Card with PCIe x1 Interface, 880 MB/s Read Speed - BARCELONA, ESP — Western Digital demonstrated an experimental SD card featuring a PCIe Gen 3 x1 interface at Mobile World Congress. Meanwhile, the SD Card Association is calling upon the industry to adopt PCIe as a standard interface and to support the ......(more)

HA/DR/Always On/Clustering

SQL SERVER – Patch Rule Failure: Not Clustered or the Cluster Service is Up and Online - One of my clients contacted me for quick assistance. They were trying to install a Service pack for SQL Server 2016 instance, but it was failing with below error message about cluster service. From the error message its clear that machine is a node ......(more)

SQL SERVER – Empty Startup Parameter in SQL Cluster on One Node - While working with many clients, I learned that “there could be many problems leading to same symptoms”. In this blog, we would learn about a reason which can cause empty startup parameter in SQL Cluster. Long ago, I worked with a client and found that ......(more)

ETL/SSIS/ELT

Self-Serve Data Preparation Doesn’t Mean Traditional ETL is Dead - Click to learn more about author Kartik Patel. Extract, Transform and Load (ETL) refers to a process of connecting to data sources, integrating data from various data sources, improving Data Quality, aggregating it and then storing it in staging data ......(more)

DevOps and Continuous Delivery (CI/CD)

Doing DevOPs for your Database? You need to start here… - I’ve been doing that thing called DevOPs for about 17 years – you know – before it had a name… In fact it was when I first joined Jade Software that I realised that Ops and DEV had a common interest in making systems go. In 2002 I started working with ......(more)

The top 7 benefits of DevOps for CIOs - When CEOs consider the benefits of DevOps and whether or not they should introduce it, their viewpoint will be influenced by their ongoing concerns with lowering costs and increasing revenues. As a result, factors like gaining a faster time to market and creating higher quality products will be top of the agenda. It’s a different story for CIOs because their focus is more on processes that can increase the throughput of the IT department, or how skilled IT staff can be recruited and retained....(more)

Database Design, Theory and Development

The Many Levels Of Concurrency - “Concurrency is hard” Construction begins on the Tower of Babel, 610 BCE (colorized) When designing for high concurrency, most people look to the hardware for answers. And while it’s true that it plays an important role, there’s a heck of a lot more ......(more)

Relationships and the Relational Model - Note: This is a rewrite of several older posts (which now link here) to bring them in line with McGoveran's formalization and interpretation of Codd's true RDM [1]. "William Kent confesses (in my words) that he can not distinguish between "relationships" ......(more)

Driving Data Quality - Ensuring optimal Data Quality is a difficult and often problematic task for many organizations. A study of 75 executives conducted over the last couple of years revealed that only 3 percent found that their departments fell within the minimum acceptable ......(more)

Data Visualisation

The Perfect Mix of Data and Story - Click to learn more about author Bree Baich. Remember the conundrum Goldilocks had while trying to find the ideal mattress? Papa Bear’s was too hard, Mama’s was too soft, but when she stretched out on Baby Bear’s bed, the feeling was sublime. Finding ......(more)

Data Science

Information Measurement with SQL Server, Part 3: Inverse Probability and Bayes Factors - In the last segment of this series of amateur self-tutorials, we discussed how to code various ways of quantifying how much we don’t know about the data in SQL Server tables and cubes. The various probabilistic entropies I translated ......(more)

Building Business Understanding - This is part two of a series on launching a data science project. How Is Babby Data Science Project Formed? Behind each data science project, there is (hopefully) someone higher up on the business side who wants it done. This person might have been ......(more)

Data Privacy

Azure Content Spotlight – Microsoft 365 GDPR Protection Strategy - Welcome to another Azure Content Spotlight! These articles are used to highlight items in Azure that could be more visible to the Azure community. Microsoft is currently rolling out some additional features to help organizations assess GDPR compliance. ...(more)

IoT and GDPR: Microshare Announces Data Convergence That Pits the Bold Against the Cautious - According to a recent press release, “The European Union’s forthcoming General Data Protection Regulation (GDPR) will reshape the rules of data management and raise both the cost of complying with laws governing people’s personal data ......(more)

GDPR: Your Hair Is Not On Fire - Along with a lot of other people, I’ve been attempting to call people’s attentions to the new General Data Protection Regulation (GDPR) that was created two years ago and becomes effective in May of this year. The regulation defines processes and practices ......(more)

Are You Ready for GDPR? - The European Union’s General Data Protection Regulation (GDPR) takes effect on May 25, 2018. In contrast to older directives and data protection acts, the GDPR will bring new accountability obligations, increased data protection rights for the EU cit ......(more)

Some on High Court Back U.S. in Email Fight With Microsoft - Several U.S. Supreme Court justices expressed support for the government Tuesday in its fight with Microsoft Corp. over whether a decades-old law lets government investigators get digital information stored on overseas servers. ...(more)

Conferences and Events

Precons in Richmond, Philadelphia and New York - I love when I get the opportunity to present at SQLSaturday events. Even more than that, I love when I get the opportunity to do a precon at a SQLSaturday event. Well, I’ve got three coming up. All three are an all day session entitled “SQL Server Tools ......(more)

Computing in the Cloud (Azure, Google , AWS)

Free Azure credits for students - For students looking to try out cloud computing, but who don't have access to a credit card, there's a new way to get access to Azure. Microsoft now offers a free Azure account to students in 140 countries, with free access to dozens of services — plus ......(more)

How to setup Azure SQL Analytics - I am a big fan of this feature, I have written and spoken about it before ( https://blobeater.blog/2018/01/04/azure-sql-analytics/) but I did not cover HOW to set this up. In the previous post mentioned above all I stated was: Setup a Log Analytics ......(more)

Career Growth

My experience with TechOutbound 2018 – Caribbean - I am a passionate, regular attendee of Techoutbound (formerly SQLCruise) events. I try to do at least one every year. This year’s cruise on east coast was to East Caribbean – with an itinerary that was different from original because of hurricane related ......(more)

The Road to PASS, Week 3: The Abstract’s Technical Details - In this month’s Road to PASS series, I’m challenging you to submit an abstract for the PASS Summit. Week 1 challenged you to write a few pain points you’ve solved this year, and week 2’s homework was to write the session’s recap slide. This week, let’s ......(more)

Backup and Recovery

An overview of SQL Server backup-and-restore process - In a manner of speaking, planning and implementing a SQL Server backup design is an art. Backup, Restoration, Recovery, Business Continuity Plans (BCP), and Disaster Recovery (DR) are different phases of data revolving around the discussions involving ......(more)

Azure SQL Database

Azure SQL Database – Allow Azure Services to Access Server - When you create a “logical” Azure SQL Server (I say logical because we are not really physically creating anything) there is a setting that is ticked ON by default which is called “Allow Azure services to access server”. The question is, what does it ......(more)

Introducing SQL Information Protection for Azure SQL Database and on-premises SQL Server! - We are delighted to announce the public preview of SQL Information Protection (Data Discovery & Classification), introducing advanced capabilities built into Azure SQL Database for discovering, classifying, labeling & protecting the sensitive data in ......(more)

Azure CosmosDB

A Swiss Army Knife Might Not Make the Best Shovel - You might have noticed that I’ve been pretty quiet as of late. We’re working on a super top secret internal project here at my company, and we’ve got the need to ingest a LOT of data around the clock for some analytics work. My preferred DBMS is, of ......(more)

Analysis Services / BI on the MS Stack

Analysis Services Query Analyzer - Last week a new, free tool for analysing the performance of MDX queries on SSAS Multidimensional was released: Analysis Services Query Analyzer. You can get all the details and download it here: https://ssasqueryanalyzer.github.io/ …and here’s a post ......(more)

Filtering Data Loaded Into A Workspace Database In Analysis Services Tabular 2017 And Azure Analysis Services - The first mistake that all new Analysis Services Tabular developers make is this one: they create a new project in SSDT, they connect to their source database, they select the tables they want to work with, they click Import, and they then realise that ......(more)

AI/Machine Learning/Cognitive Services

Microsoft makes more AI programming interfaces available to developers - Microsoft is making available new vision, face recognition and entity search interfaces to developers who want to add more AI smarts to their apps and services. ...(more)

SQL Server Machine Learning Services – Part 6: Merging Data Frames in Python - The series so far: SQL Server Machine Learning Services – Part 1: Python Basics SQL Server Machine Learning Services – Part 2: Python Data Frames SQL Server Machine Learning Services – Part 3: Plotting Data with Python SQL Server Machine Learning Services ......(more)

Azure Machine Learning and the Team Data Science Process – Part 1 - The Team Data Science Process allows you to have a repeatable, controlled progression for analytics projects. You can use it with any Data Science technologies, and Microsoft has a full suite of products you can use for AI programming. Microsoft Azure ......(more)

Administration of SQL Server

The Guide: SQL Server Installation Checklist (settings that increase SQL Server Performance) - How’d you like to see my secret stash of untapped SQL Server tuning items I personally use when setting up new SQL Servers to make them go faster and be more reliable? Well consider yourself very lucky… …because today I’m going to pull back the curtain ......(more)

SQL SERVER – Why SQL Server Service is Stuck in Starting / Change Pending State? - Recently, I wrote a blog about “Change Pending” state of SQL Server service. After looking at that blog one of my readers contacted me via email for the “similar” issue. In this blog, we would learn about another variation of “Change Pending” state. ­­­­ Here ......(more)

sp_helpindex2 - this is my extended version of sp_helpindex sp_helpindex2 lets face it, sp_helpindex is old and has not been updated for all the new features of SQL Server introduced since the beginning. sp_helpindex provides a list of indexes, information on: the type ......(more)

Uniqueifier considerations and error 666 - This post is intended to shed some light around uniqueifiers and table design that rely on its usage. First a quick information about the subject. A uniqueifier (or uniquifier as reported by SQL Server internal tools) has been used in the engine for ......(more)

How to create System-versioned temporal tables? – New database feature of SQL Server 2016 - Creating a System-Versioned Temporal Table – There are three ways to create a system-versioned temporal table with regards to how the history table is specified: 1) Creating a temporal table with an anonymous history table Creating a temporal table with ......(more)

How to Throttle Logins to SQL Server - So, uh, you can use WAITFOR in a logon trigger:CREATE OR ALTER TRIGGER SorryNorm ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'NormTheNewGuy' WAITFOR DELAY '00:00:15'; END; GO You probably don’t want it to be TOO long, lest their app report ......(more)

SQL database engine service startup failure: Troubleshooting Checklist - Hi all, In this article, we are covering the common scenarios of SQL Service startup failures and troubleshooting approach to resolve these issues: 1. For standalone instance of SQL, always use SQL Server configuration manager to start the SQL database ......(more)

Cost Threshold for Parallelism and How to Increase it Properly - Planning to Increase Cost Threshold for Parallelism When administrating a SQL Server instance with multiple CPU cores and heavy workload, it’s common to see SQL Server creating and using execution plans with parallelism. The instance configuration “cost ......(more)

Finding Composite Primary Key Columns - You never know when the idea for a script will make an appearance. I had to work with composite primary keys recently while working on a project for a client. More specifically, I had to create a process that would dynamically (dynamic SQL?! Say it ......(more)

Query Store Fix in SQL Server 2017 - There is a Query Store fix in the latest Cumulative Updates of SQL Server 2017, and if you are keeping current with the latest cumulative updates, there’s a script you need to run if you’ve installed CU2 at any point. First, if you are still on CU2, ......(more)

Summary of my Partitioning Series - Partitioning Basics – Part 1 – Creating Partitions Partitioning Basics – Part 2 – Splitting/Merging Partitions Partitioning Basics – Part 3 – Switching Data Partitioning and filegroup restores Update to TRUNCATE TABLE in SQL 2016 (partition support) ......(more)