Stairway to Columnstore Indexes Level 13: Improvements in SQL Server 2016 (2020-07-22)

 SQLServerCentral Newsletter for July 22, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Columnstore Indexes Level 13: Improvements in SQL Server 2016]( - [Automate Database Restore for SQL Server]( - [Free eBook: Inside the SQL Server Query Optimizer]( - [From the SQL Server Central Blogs - The Length Limitations of LEN()?]( - [From the SQL Server Central Blogs - The Ironic DBA—My First Year as a DBA [Part 3]]( Question of the Day - [Removing Variables from R Studio]( The Voice of the DBA  Daily Coping Tip Write your worries down and save them for a specific “worry time” I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Hacking the Admin Recently [Twitter had a security breach](, with a silly scam. At least, I'd think it was silly. I saw a tweet from Elon Musk noting that he'd return $2k in bitcoin for every $1k anyone sent to him. He was feeling generous. While that might seem silly, a number of other high profile accounts were breached and seemed to lend some level of veracity to the offer. I saw a few news reports that the hackers made off with over US$100,000, so apparently at least a few people were fooled. Twitter locked down verified accounts for a bit while it What is disturbing here is that apparently the hack took place through Twitter syadmins, with privileged accounts. As of this writing, it isn't clear if this was social engineering or a sysadmin worked with hackers, but there were internal tools allowing Twitter employees to post tweets on behalf of users. I have no idea how this happened, but I'm assuming this is some sort of data change made to their system. If this were an RDBMS with a "tweets" table, this would be adding a row to the table with links to the verified accounts' linkage. Not a hard change in the SQL Server world, and certainly the type of change that most admins could make. The question might be should they be allowed? Many of us have made ad hoc data changes to systems to correct an issue, and some of us do this regularly. This reminds me of some customers whose DBAs aren't allowed to directly connect from SSMS (or other clients) to production and make changes. All changes, including ad hoc data changes, must be submitted to some sort of pipeline, where the change is logged, and perhaps approved by someone else. A different sort of two factor authentication. Should this be a more common pattern of access to production systems? Limiting access by everyone, even admins? I know we need to trust administrators, but what happens when [administrators get fooled by social engineering](? A thorny attack vector that we ought to be considering in our architectures. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Columnstore Indexes]( [Stairway to Columnstore Indexes Level 13: Improvements in SQL Server 2016]( Hugo Kornelis from SQLServerCentral.com This stairway series was started in 2015. As such, the focus was on SQL Server 2012 and SQL Server 2014 only. When SQL Server 2016 was released, with lots of improvements in the columnstore technology, I decided to finish the planned levels with the original focus on SQL Server 2012 and 2014, and add one extra level with a brief overview of the improvements available in SQL Server 2016. [Automate Database Restore for SQL Server]( Additional Articles from MSSQLTips.com In this article we look at how to automate a SQL Server database restore using a PowerShell script. [Free eBook: Inside the SQL Server Query Optimizer]( Press Release from SQLServerCentral.com This free eBook from Redgate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. From the SQL Server Central Blogs - [The Length Limitations of LEN()?]( Pam Mooney from The Non-Cluttered Index I was using LEN() to troubleshoot an issue I was having with a dynamically constructed string truncating while inserting into an NVARCHAR(MAX) column. Since I know that NVARCHAR(MAX) has... From the SQL Server Central Blogs - [The Ironic DBA—My First Year as a DBA [Part 3]]( Kevin3NF from Dallas DBAs T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Kerry Tyler...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Removing Variables from R Studio I am using R Studio and do this: > customerids <- c(1,2,3) > orderqty <- c(10, 20, 30) I added those vectors to a data frame, but in R Studio, I see them as Values in the Global Environment pane: [variables in R Studio] I want to remove just these two variables from my environment to test something. How can I do this? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Loading Azure Synapse Data With COPY The COPY command in Azure Synapse Analytics allows me to load data from which formats? Answer: UTF-8 and UTF-16 CSV files, ORC, and Parquet files only Explanation: COP Y can load data in csv, parquet, and orc formats. Ref: COPY - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Backup server]( - We will have backup server maintenance with the outage between 15mins to 1 hr. So there won't be log backups during that time and business is aware of that. I don't see any issues doing this during business hours instead of after hours. Do you agree or anticipate any other issues.Please advise? [Trying to Make combinations within a bracket in SSMS column]( - This is what the data looks like, I need to break the Column TID prefix as the first letter outside of the brackets needs to be combined with every letter inside the bracket. for example: A[CL] needs to broken down in 2 new rows as AC and AL. Can someone please help me with this? SQL Server 2017 - Development [Separating a string]( - Hi, I have a string that comes into one of my column and I need to create a column for each item. It looks something like this but much longer {"Id":"1bban5d82-23g4c-430ui-b804-4c3awasgec28","RecordType":20,"CreationTime":"2020-07-07T03:39:55"} Thanks Astrid  SQL Server 2016 - Administration [Worker thread on SQL 2012 and SQL 2016]( - I migrated SQL 2012 to SQL 2016 with same 4 processor CPU On the SQL 2012 server I don't see worker thread issue but on new Server SQL 2016 I am getting Worker thread issue. Worker thread is set to default on both the SQL server [MultiSubnet AG Listener]( - I have an AG listener set up on two subnets - two nodes are in the same subnet and a third node is on a different subnet in a separate data center. The issue I'm having is that applications are timing out when connecting to the listener. Adding MultiSubnetFailover=True to the connection strings isn't possible […] SQL Server 2016 - Development and T-SQL [WHILE LOOP Takes a long time]( - This is a strange one and hope someone can help me understand the problem. The code in question has been working without issue for about 2 years last week it started taking an extremely long time to finish. It went from about 3 seconds to upward of 6 minutes overnight. I am going to use […] Development - SQL Server 2014 [sql to find members not contacted in past 18 months]( - I have a table that has ContactNumber (Bigint), MemberNumber(bigInt) and ContactDate(date). The table has millions of records (sample data file attached). I want to get all the records where member has not been contacted for last 18 months. How do I do that using tsql? Your help is highly appreciated.  Thanks.  SQL Server 2019 - Administration [Is SQL developer edition exactly the same as enterprise edition?]( - Hey all, Regarding licensing of SQL server, we are considering the possibility of running SQL developer edition in our non production environments, but SQL enterprise edition in our production environments. From a licensing perspective is looks like we would be allowed to do this, but what about from a testing perspective? Are these two versions […] [Possible to replicate SQL Server data to Terdata?]( - Is it possible to replicate SQL Server data (using SQL Replication) over to Teradata? If so, are there instructions available? thx in advance [Best Way to Run 1000+ SELECT statements?]( - So I basically have a spreadsheet with five columns of values. Each column represents a different field in my query. For each row in the spreadsheet, I need to execute a query (Oracle) that uses the values from that row and store/output the results. Example query for 1 row of the spreadsheet: SELECT * FROM […] SQL Server 2019 - Development [Logging (auditing) in the table vs. having a logging (auditing) table]( - I really like Steve Jones' editorial today, Carefully Giving Normal Users Security Permissions. I've got a question about something Steve brought up, but I didn't want to ask it in response to the editorial, as I thought my question was too far off topic. In my current job, I've seen the practice of logging (auditing) […] SQL Server 2008 - General [Rebuild or Reorganize index]( - I am using SQL server 2008. The DB is normally used from 9 am-9 pm and the maximum db size is 10 GB. Whether rebuild index offline causes any problem, if it is done during midnight (when no one is using db)? or reorganize is enough for index maintenance in this case? SSRS 2012 [Is there an easy way of moving all controls down the report?]( - I'm working on a report which has a label (it's a TextBox) at the top, in the middle. The label says simply Transaction Report. To the left of that label is our address. To the right are 6 TextBoxes, three are captions and the other three are data elements. Under all of this are several […] Integration Services [Extract DB2 data into Raw File]( - Hello: I'm trying to generate a Raw file in SSIS using the Raw Destination task but I'm reading from DB2 using ODBC Source. I need to convert this to UTF-8 because the file is automatically generated in ANSI. I used derived column to convert this columns to (DT_STR) but still got the same issue. The […] [SSIS 4 Minute Job Takes Over 19 *Hours* on New Server]( - I have an SSIS package which runs on SQL Server 2008 R2 in about 4 minutes. It's been running successfully for years. We are now migrating to SQL Server 2019. What Works: In addition to running on the old R2 server, the package runs perfectly on my little laptop in Visual Studio Data Tools with […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -