Stairway to Columnstore Indexes Level 1: A First Look At Columnstore Indexes (2019-12-18)

 SQLServerCentral Newsletter for December 18, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Columnstore Indexes Level 1: A First Look At Columnstore Indexes]( - [A Beginner’s Guide to SQL String Functions]( - [Take the 2020 State of Database DevOps Survey – win a new iPad]( - [Free eBook: SQL Server Backup and Restore]( - [From the SQL Server Central Blogs - Distributing Jupyter Notebooks]( - [From the SQL Server Central Blogs - Resetting an Azure SQL Database Admin Password]( Question of the Day - [Renaming Columns]( The Voice of the DBA  Data Gravity I've seen a few people use the term data gravity in the last few months. I wasn't quite sure what this meant to me, and it seemed that people used it in a few ways, but someone sent me a link to [this short piece on data gravity in the cloud](. This piece is really in relation to Salesforce and their goal to build [database.com]( and gather more of their customers' data into a virtualized view where you can access all (or most of your data) from your Salesforce applications. This isn't a new idea, as the goal of many companies has been to have a unified view of their data. I've often seen this in relation to customers as the single view of a customer. There are [numerous explanations of this]( as many organizations want to find a way to build this for their own use. I actually think this is a trend, and the changes in SQL Server 2019 with Big Data Clusters, as a more basic platform integration of this concept. We want to query data where it lives, not move it (really, copy it) to another system for analysis. While there are lots of articles written about data gravity based on that initial blog, it seems that they all relate to the idea of applications and services needing more bandwidth and lower latency to grow. That might be true, but I think we've seen another type of gravity in RDBMS systems. As the amount of data grows, certainly more people want access, and there is a need for higher availability and greater performance. Many of us see this in the systems we build and manage, whether we are developers or administrators. What I think of as more of an attractive force, however, is the tendency of a system to stagnate a bit and slow down other work. We find it more difficult to grow and change large systems. Our developers become more hamstrung, and we start to struggle to adapt to new requirements. Certainly we struggle to adapt at the speed with which our organizations would prefer we move. There also becomes this gravity that limits the thinking that your staff may have about how to work with the system. These are the challenges that I see driving many people towards [compliant database DevOps]( solutions. I talk with quite a few customers that want to go faster and more easily adapt to changing requirements in their database systems. However, the gravity and inertia of working a certain way often limits their willingness to adapt to new tools and techniques. Usually there is this cultural gravity more so than technical gravity, which might be the stronger forces. Especially among managers. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate SQL Monitor](   Featured Contents [Stairway to Columnstore Indexes]( [Stairway to Columnstore Indexes Level 1: A First Look At Columnstore Indexes]( Hugo Kornelis from SQLServerCentral.com This level introduces of you to the fundamentals of columnstore indexes, introdused in SQL Server 2012 to manage the indexing of very large tables. [A Beginner’s Guide to SQL String Functions]( Additional Articles from Database Journal Get an introduction to many of the many ways you can manipulate and interact with strings using SQL. Read on for more! [Take the 2020 State of Database DevOps Survey – win a new iPad]( Additional Articles from Redgate Redgate’s 2020 State of Database DevOps Survey is open now. Share your views on Database DevOps today for a chance to win a new 65GB iPad Air. Plus, for every entry, Redgate will donate $1 to UNICEF. Take the survey now. [Free eBook: SQL Server Backup and Restore]( Press Release from Redgate In this free eBook Shawn McGehee offers advice on query tuning, cutting stored procedures, and system process design and implementation for high availability. Discover how to perform backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Redgate's SQL Backup tool. From the SQL Server Central Blogs - [Distributing Jupyter Notebooks]( Grant Fritchey from The Scary DBA If you’re working with the Microsoft Data Platform, you should be, at the least, exploring Azure Data Studio as a new tool in your toolbox. One of the big... From the SQL Server Central Blogs - [Resetting an Azure SQL Database Admin Password]( Steve Jones - SSC Editor from The Voice of the DBA This is likely something that most people know, but it was a bit confusing for me. This post is really here to remind me and help cement this in...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Renaming Columns I have imported the numpy and pandas modules as np and pd, respectively. I have this data frame in my REPL. df BillMonth BillingAmount 0 May 300 1 June 450 2 July 600 3 August 900 I want to rename BillingAmount to InvoiceAmount. 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) Getting my options I know that the @@OPTIONS variable contains a bitmap. If I want to get the value of ANSI_WARNINGS, I know this setting is stored in the 4th (from the right) bit. I know that ANSI_WARNINGS are on, meaning the bit is set. When I run this code, what is returned? DECLARE @a INT SET @a = @@OPTIONS SELECT @a & 8  Answer: 8 Explanation: When I run this, an 8 is returned. If this were the only value set, the binary number would be 1000. When I take the entire @@options value and AND this with 8, I get the either 0000, if the option is not set, or 1000, if the option is set. Ref: - @@OPTIONS - - Bitwise AND - [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 [Always on in standard and enterprise edition]( - Hello everyone! I would glad to know, what is the difference in Always on between Standard and Enterprise edition? And also there is something that change in 2012 and above? Thanks! [Migrating DB encrypted by master key]( - I'm migrating DBs from a 2008 R2 instance onto a 2017 instance. I have one DB that is encrypted by the master key, how do I migrate this successfully across to a new instance? [Log Shipping - What has changed]( - We currently have a situation with a vendor where they will only give us access to our data via 5 minute log ship files that we import into our read only db. this is fine for most queries, but we need a way to get details about what has changed in each period in the […] SQL Server 2017 - Development [Insert in batches using select coalesce option]( - Hi, I am trying to insert multiple tables data into one table using Insert in batches and with select coalesce. I am getting Ambiguos column (id) error. Can some one please help me to resolve the issue. Thank You. ERROR MESSAGE: Msg 209, Level 16, State 1, Line 26 Ambiguous column name 'id'. Line:26 - […] [Fastest Way to Calculate Total Number of records inserted per day]( - Hi, I need to show a number of records per day in the dashboard of the web application if the user reload the page the count will automatically be updated. Initially, we don't have any problem as the records grow to the millions of records the query execution takes slow. The following is the query […] [Fastest way to query Millions of records with Pagination and Total Record Count]( - HI, We had a table that contains a huge volume of data inserted per day it contains millions of rows. Currently, we had 2 million records in staging but once we hit the production the data will be easily more than 10 - 20 million rows Previously we had a pagination query that took more […] SQL Server 2016 - Administration [changing db owner with always on replication (to fix DB properties window)]( - SQL Server 2016 standard edition, with primary databases and one other server set up as secondary failover with always on availability group. There were some issues over the weekend with an application that accesses the databases on this server, unbeknownst to me, they decided just to reboot the database server, which seemed to clear things […] [What\'s the best way to keep a record of all records inserted via an SP call?]( - Hey guys, so I may be overthinking this but basically, I have a bunch of stored procedures which I want to begin keeping historical track of. I want to know which records were updated by which SP & when, and I'm just looking for the best way to do this. Is there any built in […] [Impact value about missing indexes]( - is there relation between Impact from sys.dm_db_missing_index_group_stats and Impact from cache plan? Thanks for all.  [I can't Uninstall SQL Server 2016]( - Hi, I have SQL Server 2016 installed in my machine but I can't uninstall it, it doesn't appear on my Control Panel, I looked again using CMD wmic, product get name, it also doesn't appear. But I can see it, SQL Server on the services and SQL Server Configuration Management. I also tried using CMD […] SQL Server 2016 - Development and T-SQL [BCP utility]( - Hi All I am trying to export some data from a SQL server database to a CSV file. As I need to do this for around hundreds of records, I am using a cursor to loop through and creating dynamic file names to write individual files. This seems to be working ok. Some of the […] Administration - SQL Server 2014 [Trouble connecting in single user mode]( - I have an SQL server which I need to get admin access to. Logged on as administrator to the server I attempt to start in single user (-m) mode. However in this case, instead of dropping into single user (as on other servers in this project), I get a screen full of continuous login failures […] Development - SQL Server 2014 [How to get information about an AG group using powershell]( - Hi Experts, Does anyone has a powershell script which displays below information: for a given AG listener name or AG group name, I need to get Listnener name, AG name, replicas , replica role desc , Can we get this information using pure powershell which reads registry values or do we need to execute a […] SQL 2012 - General [SQL Monitoring]( - Hi All, I have SQL 2012 instance on a vm server which I am testing with Red Gate Monitoring Tool installed on my machine and my machine machine is set a base monitor for testing purpose. Problem I am facing is that it only monitor while I am logged on to the machine. If I […] Strategies and Ideas [Tracking History in Fact Table]( - Hi, I was hoping somebody would be able to offer advice in relation to tracking history in a fact table, in particular whether #2 below would be a viable option? I would have thought that #1 would be bad for performance given the volume of records created through history so maybe not best practice?. If […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -