Free eBook: Inside the SQL Server Query Optimizer (SQLServerCentral 4/4/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Free eBook: Inside the SQL Server Query Optimizer]( - [Stairway to Biml Level 4: Using Biml as an SSIS Design Patterns Engine]( - [Controlling Access to Azure Cosmos DB]( - [Getting Things Done With SSMS]( (From the SQLServerCentral Blogs) - [Rotating TDE Certificates without re-encrypting data]( (From the SQLServerCentral Blogs) Featured Script - [Automatically Create indexes as needed]( The Voice of the DBA Is the DBA Title Dying? Not is the DBA joib dying, though we could argue about that as well. Instead, I'm asking if the title of Database Administrator is going away. Are there going to be people that really want to send out resumes and apply to be Database Administrators beyond the next few years? As I look at my resume, I wonder if that's a job or title I want to keep around. Think about that for a few minutes. We're in the age of complex systems with cloud platforms, automated backup software, PowerShell and other scripting, Chef, Puppet, containers, self-service clones, automatic indexing, query store plan fixing, and more monitoring options than you would want to spend time evaluating. We have plenty of tools to assist, or takeover, much of the daily administration of database platforms. Even security can be mostly outsourced to AD, AAD, other directory services. While there is some level of what we might consider administration, is that the core of many people's jobs? Will it still be in five years? I read a few of the posts from T-SQL Tuesday #100, including [Adam Machanic]( and [Brent Ozar](. They both think the DBA is dying, and I tend to agree. I know that inertia slows change in many large organizations, and I'd bet there will be a delay just because of HR. After all, if you want to be a data reliability engineer or data professional, most organizations don't list that job title and would have no idea how to hire you. Even if they loved your resume, they'd end up hiring you as a DBA or developer (developer III maybe) just because they like those nicely labeled buckets for the HR systems. They might not even know how to pay someone in some new role. However, the more I talk with people, the less they seem to be doing administration. They still tune queries, but often they're helping do more database development or even database architecture than administration. They may to more reporting or ETL work, which can easily fall under development as well. Security is still a part of their jobs, but that doesn't seem to change as often as it used to. Many people have moved to database roles and AD integration for authentication. Then security becomes more of a set-it-and-let-someone-else-manage-it. I even see Slack bots or other tools that let people self-service requests, and others quickly (or automatically) approve them. We still do some work, as things like HA/DR still aren't quite a smooth as they could be, but that's changing. Cloud services, and the evolving Azure Stack may mean that more and more of our work will be done by templates and patterns. We may have to create the template, but that feels more like architecture and less like basic administration. I expect at some point we'll just link a grid of machines, on premises or in the cloud, and let people self-service their requests for systems. The "template" will let a service deploy HA databases across the machines as it sees fit, providing an address to connect to, implementing monitoring, and even solving many simple problems without human intervention. I expect that security and auditing features and capabilities will also grow rapidly, becoming more automated, or at least automatic, and request less administration. I used to joke I was a data janitor in many jobs. Just dealing with whatever situations people couldn't, or wouldn't, bother working on with databases. I often cleaned up messes made by developers that weren't sure how to build a database that scaled beyond "their machine". These days I think I'm no less of a data janitor, but I call us data professionals. It's a better catch-all term, and certainly sounds more appropriate. We do our best to ensure the safety, accuracy, and availability of data in whatever way works best in our environment. We'll run your T-SQL, R, python, machine learning, SSIS, SSRS, or whatever code for you, and audit the actions. I think that as we do that, we'll do more development, cleansing, and analysis than actual administration. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 5.2MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [SQL Compare]( The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. [Download your free trial]( [Database DevOps](www.red-gate.com/dlmdemo?utm_source=ssc&utm_medium=pubemailad&utm_content=devops-webinar&utm_campaign=databasedevops&utm_term=20180404-slot2) Database DevOps Demo Webinar Learn how to automate your database deployments alongside your app code in this free demo webinar. [Register now](www.red-gate.com/dlmdemo?utm_source=ssc&utm_medium=pubemailad&utm_content=devops-webinar&utm_campaign=databasedevops&utm_term=20180404-slot2) Featured Contents  [] [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.[More »]( ---------------------------------------------------------------  [] [Stairway to Biml Level 4: Using Biml as an SSIS Design Patterns Engine]( Andy Leonard from [SQLServerCentral.com]() In this level, we make a large leap forward, showing you how to use BIML to script a large number of packages using C#.[More »]( ---------------------------------------------------------------  [] [Controlling Access to Azure Cosmos DB]( Additional Articles from [Database Journal]() Azure Cosmos DB offers features that facilitate data availability and resiliency. However, there is also a different, very important aspect of facilitating access to distributed data sources in the most optimal manner, which focuses on access control mechanisms. Read on to learn the specifics of this aspect, as it applies to Azure Cosmos DB.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Getting Things Done With SSMS]( MarlonRibunal from [SQLServerCentral Blogs]( As a SQL Server pro, I spend most of my productive time in the SQL Server Management Studio (SSMS). I...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Rotating TDE Certificates without re-encrypting data]( Matthew McGiffen from [SQLServerCentral Blogs]( I talked previously about why we have each of the layers in the encryption hierarchy used to support TDE (Transparent...[More »]( Question of the Day Today's Question (by Steve Jones): I read a bunch of data into a data frame and it looks like this: > ncaa.2018 V1 V2 V3 V4 V5 1 20180315 Oklahoma URI 78 53 2 20180315 WrightState Tennessee 47 73 3 20180315 NC-Greensboro Gonzaga 64 68 I want to set the correct names for columns. How can I do this? Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: R Language. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [Expert T-SQL Window Functions in SQL Server]( Expert T-SQL Window Functions in SQL Server takes you from any level of knowledge of windowing functions and turns you into an expert who can use these powerful functions to solve many T-SQL queries. Replace slow cursors and self-joins with queries that are easy to write and fantastically better performing, all through the magic of window functions. [Get your copy from Amazon]( today. Yesterday's Question of the Day Yesterday's Question (by Ron S): I execute the following code. What is returned? ;WITH schemas_cte AS ( SELECT schema_id = 1 ) , tables_cte AS ( SELECT tablename = 'table1' , schemaid = 1 UNION ALL SELECT tablename = 'table2' , schemaid = 2 ) SELECT tablename FROM tables_cte WHERE schemaid IN ( SELECT schemaid FROM schemas_cte ); Answer: table1 and table2 Explanation: Without two part naming, the engine assumes this is a correlated subquery and searches the outer query for a matching column. In this case, it finds one. Ref: This isn't well documented, but an old KB shows the issue: [click here]( In - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [Automatically Create indexes as needed]( Steven Christensen from [SQLServerCentral.com]() This script is designed as a job in sql server agent to automatically optimize indexes in your database. Simply run the job and it will create the necessary indexes to optimize performance. Replace [user name here] with your user name and replace [database name] with your database. Then when you need indexes added to optimize performance you just run this job in the database that you are optimizing. [More »]( 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]( : [SQL Server 2017 - Administration]( [Transaction log backup in Logshipping]( - Hi, Can any one help me with an answer pls. I have my normal housekeeping fullbackup and transaction log backup ,running under... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Removing stored procedures to move to cloud]( - I got an interesting email today about cutting back on the reliance of the applications on databases and plans to... [Trying to assign payment amounts for each consecutive month within a date range]( - I have authorizations that have start and end dates and I need to calulate the monthly payment and assign to... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [SQL Server Agent stops running after changing the default listening port]( - Dear Everyone I changed the default listening port for SQL Server 2016 standard edition on windows 2012 R2 then i... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Db name inside the stored proc]( - All, I am trying to work on a stored proc that would accept DB as a parameter name and then work... [Developing Extensions for SSMS 2016]( - I recently released my extension and wanted to share what I have learned so that others can start creating extensions. SSMS... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Application Name from Profiler]( - Hi Experts, Trace result is giving application name in hexdecimal values like below. How to find the exact application name. These... [Database too big than .bak]( - Good day, I have a DB which is 850 GB in size and when i take a full backup the backup... [Moving Database Objects]( - Hi Experts, We are moving objects of a particular schema to a new database. What all things need to check while... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [I need to divide my total by the QTY, but QTY lives on every row]( - I hope i explain this correctly I am dealing with BoMs . . . If there are 50 rows of parts, for example, on... [Harmony integration for SSIS]( - Hello, Does anyone know if it is possible to use HARMONY web service in SSIS? I have a convoluted C# batch... [Use uniqueidentifier field in place of int]( - Hi All, I'm trying to populate a temp table. Here is the temp table: CREATE TABLE #SESDMVESEnrollData (  ContractNumber nvarchar(50) not null,  ... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Send email alert when SQL Server Agent stops/fails - PowerShell command not working?]( - I'm testing a PowerShell Script to send me an email anytime SQL Server Agent stops/fails to start. I'm using the... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Database corrupt - Msg 8966, Level 16, State 2, Line 1]( - Server : SQL Server 2008R2 Microsoft Windows server 2008 R2 Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected... [SQL Server Agent Job question]( - I'm new to SQL Server Agent. What I want to do is execute a small script to run a stored... [Moving data from rows into single delimited field]( - Does someone have a solution for this. If there is more than one MyId1 it needs to be in a... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server Newbies]( [Inactive database user?]( - Hello all, I want to automatically logout the inactive users from sql server 2005/2008 after certain time limit(ie. after 5minute). Is there... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [ssis error on lookup table not sure why]( - Hi, I get the following errors when running an ssis package, but tnot sure why this is, it is failure on... --------------------------------------------------------------- [SQLServerCentral.com]( : [SQLServerCentral.com Website Issues]( [Need simpler way to see a list of my posts and possible replies to them]( - Please excuse what might be an elementary question/suggestion but I am new to this forum. After I log in, there... --------------------------------------------------------------- [SQL Server 7,2000]( : [General]( [QuickBooks to SQL Server]( - Does anyone have any ideas of ways to communicate between SQL Server and Quickbooks? I have found a couple of... 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](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com