Introduction to Indexed Views in SQL Server (2020-02-25)

 SQLServerCentral Newsletter for February 25, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Introduction to Indexed Views in SQL Server]( - [Configure SQL Server Transparent Data Encryption with PowerShell]( - [From the SQL Server Central Blogs - Why I Support Code Automation Tools Like ORMs]( - [From the SQL Server Central Blogs - #PowershellBasics: Run a file and pass it a parameter.]( Question of the Day - [Creating a Columnstore Index]( [Redgate University]( The Voice of the DBA  Time for a New Adventure with Redgate Today we have a guest editorial from Kendra Little as Steve is away on his sabbatical. I’ve been a remote worker based in the Pacific Northwest in the United States since 2011. I used to think that working from home was the ideal setup for me: I love spending breaks and lunchtime with my dogs, using chat tools and video conferencing for productivity, and spending precisely 30 seconds on my commute each morning. I joined the Advocate team at Redgate in 2018. Working remotely as part of the team has been great: the rest of the Advocates also work remotely, and we collaborate quite easily with online tools. We often meet up in person at Redgate events, industry conferences, and visits to Redgate offices around the globe. A few months ago, my husband Jeremiah and I decided that it was time for a new adventure. At the time he was finishing up his master’s degree in computer science. We were starting to discuss the possibility that we might relocate for the right job for him—after all, I could work from most places which are near an airport. At the same time, we realized that I might enjoy living and (gasp!) working out of one of Redgate’s offices where I could connect with colleagues in person on a regular basis. Fast-forward to today and quite a lot is in motion: our family is currently working through the visa process in hopes of relocating to Cambridge, UK very soon. Jeremiah successfully interviewed at Redgate and has landed a job working in the Product Development division (of course, depending on our visa being approved). Similarly, I interviewed to be an Advocate based in the UK office. I will get the opportunity to partner with our sales team in helping customers in the UK and across Europe, very likely getting to visit loads of places I’ve never been. But oddly enough, the part of the new adventure which I’m most looking forward to is working in the office. Although working from home has been great for the past nine years—and I will likely still work from home one day of each week-- I am incredibly excited about heading into work at a software company on most weekday mornings. The Cambridge office is a dynamic place, and I’ll get the chance to know loads of people across the company better. I started my career off as a DBA by working at a gutsy, fun software development company where I loved going into the office. This move feels very much like it brings my career full circle in a wonderful way. To all my friends and colleagues in the USA, I promise I won’t be far off! I’ll travel back over for events when it makes sense, and of course, continue to blog and tweet and make videos, I’ll just be online in a slightly different time zone. Here’s to new adventures!  Kendra Little [Join the debate, and respond to today's editorial on the forums]( [Redgate SQL Prompt](   Featured Contents [Introduction to Indexed Views in SQL Server]( Archana from SQLServerCentral This article explains indexed views and provides an example that shows when they should be used to improve performance of a complex query. [Configure SQL Server Transparent Data Encryption with PowerShell]( Additional Articles from MSSQLTips.com In this article Filip Holub looks at how to configure and enable transparent data encryption for a SQL Server database using PowerShell. From the SQL Server Central Blogs - [Why I Support Code Automation Tools Like ORMs]( Grant Fritchey from The Scary DBA I know I’m a weirdo. I’ve always been a weirdo. When I was a DBA (now I only play one on TV), I was a weirdo too. Case in... From the SQL Server Central Blogs - [#PowershellBasics: Run a file and pass it a parameter.]( Kenneth.Fisher from SQLStudies I realized in my last #PowershellBasics post that I was talking about running a file and passing in an optional ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  Creating a Columnstore Index In the AdventureWorks database, I want to create a new nonclustered columnstore index. Which of these is the correct way to do this? -- 1 CREATE NONCLUSTERED INDEX Ncci_SalesOrderDetail on Sales.SalesOrderDetail ( SalesOrderID, ProductID, UnitPrice, UnitPriceDiscount) WITH (COLUMNSTORE = ON) -- 1 CREATE COLUMNSTORE INDEX Ncci_SalesOrderDetail on Sales.SalesOrderDetail ( SalesOrderID, ProductID, UnitPrice, UnitPriceDiscount) WITH (NONCLUSTERED) -- 3 CREATE NONCLUSTERED INDEX Ncci_SalesOrderDetail on Sales.SalesOrderDetail ( SalesOrderID, ProductID, UnitPrice, UnitPriceDiscount) WITH (COLUMNSTORE) -- 4 CREATE NONCLUSTERED COLUMNSTORE INDEX Ncci_SalesOrderDetail on Sales.SalesOrderDetail ( SalesOrderID, ProductID, UnitPrice, UnitPriceDiscount) 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) More PowerShell Variables I have this code: $name = 'Steve' write-host( 'My name is $name') What is returned? Answer: My name is $name Explanation: In this code, with single quotes, the variable is not expanded. Instead, the literal, $name, is returned. [Results in PoSh] Ref: Variable expansion in strings and here-strings - [( [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 [SSMS Object Explorer Icons]( - After so many years of working with SQL Server, I should know the answer to this one, but I don't. I'm connected to QA and Prod servers, and, in Object Explorer, the icons look like this The connection to QA seems to be in a 'known' good state, whereas the connection to Prod has a […] SQL Server 2016 - Administration [TDE for Always on databases]( - Hi All, In terms configuring and usage of TDE encryption is there a difference for Standalone instance databases & databases involved in AG? Because the secondary will be in read-only, just want to know how to configure TDE on AG databases and during failover do we need to take care of any additional steps etc.. […] [Parameter Sniffing]( - Hi All, I know there are various causes for parameter sniffing issues, but one I want to focus on is: not having a covering index. Logically speaking then, having a covering index would be a way to address the issue. This is further evidenced in Tara Kizer's comment on: There’s lots of ways to […] [script out the dbmail settings and profiles]( - How can we take the script of existing db mail settings and profiles ? SQL Server 2016 - Development and T-SQL [Convert Varchar to Int and SUM]( - Hi, need a request...it's possible to convert dbo.RE80_MOVSCHPUNTI.RE80_VALORE this table from nvarchar to INT and make a SUM of it??' Thank's in advance Administration - SQL Server 2014 [Problem with KB4532095 installation]( - Has anyone else had trouble installing this security patch? It starts up and I get to the screen where I have to select my instances and it won't select anything... my instance is running and I can connect to it. But the update patch won't let me select anything on this screen. Any thoughts on […] SQL Server 2019 - Administration [grant unmask to role]( - Hi All, i am using SQL Server Management Studio 2017. i have a database and i have masked a column. There are 500 users who are using the database. Upon request i need to provide unmask data to around 150 users. Would it be possible to create a database role and give the grant to […] [Cannot connect to SQL Server, which is on VirtualBox, through my host computer]( - I have successfully pinged and telnet my virtualbox with my host computer but when I go to connect to the sql server with workbench I get the following error: 'Lost connection to MySQL server at 'waiting for initial communication packet', system error: 60'. Have tried everything it seems like and cannot seem to fix the […] [SQL Replication - Uncooperative Databases]( - I have a database from a vendor which I need to mirror. This database, as a product, has tables which are dropped and recreated, truncated regularly, etc. Many issues which prevent a replication from being set up as I normally have. I very much wanted to have a transactional replication of this database as a […] T-SQL (SS2K8) [Transposing the data and then converting monthly data to daily data]( - I have a Budget Table in following format Account -- Year -- Month1- Month2- Month3- Month4- Month5- Month6- Month7- Month8- Month9- Month10- Month11- Month12 500100 - 2020 - 100 - 200 - 300- 300- 300- 300- 300- 300- 300- 300- 300- 300 I would like to convert the data like below Account Period Amount 500100 […] SQL Azure - Development [FileStream - Azure managed instance]( - Hi, Simple question, I know that FileStream is not supported in Azure DB single database or Elastic pools but is it supported in a managed instance? I've tried to find a definitive answer but I'm getting conflicting info. Says it only applies to SQL Server (Windows Only) and not Azure SQL Database But […] Amazon AWS and other cloud vendors [Amazon RDS linked server issue]( - I have an RDS issue with a linked server (I know its not good, but I've inherited it) I keep loosing my linked server and we get the error "an error occurred during encryption" according to my google assistant and a few hours research every time... this occurs when Amazon switch us onto new hardware […] Integration Services [OLE DB Destination has written 0 rows]( - Hi SSC, I'm having issues with one SSIS Package running on SQL Server 2017. What it does is basically: Truncate Table DFT OLE DB Source -> no Transformations or something -> OLE DB Destination When I run the Source Statement wether it's as a source preview or SQL Statement it works as expected. When I […] Hardware [Search for wicked fast direct attach storage]( - After working and managing many different SAN's ( i am not going to name them but you can guess, these are top 5 ) i am sick and tired of dealing with networking changes and having army of people lined up to add a shelf. I was super happy with FIO direct attached storage, scaling […] Disaster Recovery [Clustering - WSFC vs AGs]( - Hi, More and more I'm having discussions with customers who want to move away from FCI to AGs. The main reasoning is that AGs offer "faster, almost instant" failover times. I will admit I've never delved in to this difference, so I am asking here. What are the under-the-hood pros and cons of each; there […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -