SQL Server Trace Flags - Complete list (SQLServerCentral 2/23/2017)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [SQL Server Trace Flags - Complete list]( - [Performance Surprises and Assumptions : GROUP BY vs. DISTINCT]( - [Adding a T-SQL Job Step to a SQL Agent Job with PowerShell]( (From the SQLServerCentral Blogs) - [External SQL files vs. Stored procedures in SSIS development]( (From the SQLServerCentral Blogs) Featured Script - [Building calendar table using MTVF]( The Voice of the DBA The Cloud is Just a Tool The cloud is a term that's full of hype. We hear from various media outlets all the time: the cloud is the answer, the cloud is cheaper, the cloud is the way of the future, the cloud handles your DR, the cloud managers availability, and more. Microsoft has been pushing the message of "[cloud-first](" (and mobile-first), which has many SQL Server professionals confused, concerned, or even angry. There are also plenty of professionals that dismiss the idea of cloud anything when it comes to data. I've felt similar emotions, and certainly I have been skeptical of the cloud versions of databases. I remember the first cloud service, a key-value store, which seemed woefully inadequate for most purposes. Since they I've seen the Azure SQL Database grow, and many other products get released. Across that time, I've become more and more impressed with what Microsoft has done, and as Visual Studio Team Services has expended, I've come to really embrace and get excited by the cloud. It's still not something I'd always recommend, but I would always start there. Mike Walsh wrote [a great blog post]( on the move to the cloud, which I recommend you read. The end message that I get from Mike's thoughts are that the cloud is a tool, and it can be a tool that really enables you to solve issues without getting caught up in the details of implementing every little part of the system. That's a mantra that I think many of us embrace, even if we don't really realize it. How many of you deal with SQL hardware? How many of you install or configure Windows? For many of you, do you even worry about backups or do you have scripts/tools/products that just start backing up new databases? I used to do all those things, but I haven't even seen a production database server with my own eyes in a decade, despite connecting to many. We all move at different paces. Some of us still deal with SQL Server 2008, 2005, 2000, or even earlier versions. Some of us will need to manage those platforms for years to come, even as we may end up helping build applications on Azure SQL Database and deal with data integrity, quality, and security issues through a remote connection. I'd like to be even more hands off. Enabling TDE in Azure is clicking a button. I wish it were that simple on premise (whether really here or in an IaaS scenario), because it should be. I should be able to click a button, get prompted to confirm, pick a backup location for my cert backup, maybe give the cert a name, and it should just get completed. The cloud really is a set of tools and services that take away some of the details and drudgery. Sometimes that's fantastic, and it enables more rapid, more scalable deployment of resources. Sometimes it's dangerous because the vendors haven't really thought through the process completely. I really think that's where we add value as professionals. We shouldn't be doing too many tasks that can be more easily automated. We should understand what the automation does, and be able to examine it, but we should be spending our time examining problems and evaluating solutions. We should be using tools, of which the cloud is just one, to ensure our organizations become more productive and more efficient over time. 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]( ( 4.5MB) 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 Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more.]( Featured Contents  [] [SQL Server Trace Flags - Complete list]( Konstantin Taranov from [SQLServerCentral.com]( Article in markdown format: 20Server%20Trace%20Flag.md[More »]( ---------------------------------------------------------------  [] [Performance Surprises and Assumptions : GROUP BY vs. DISTINCT]( Additional Articles from [SimpleTalk]( Aaron Bertrand acknowledges that DISTINCT and GROUP BY are usually interchangeable, but shows there are cases where one performs better than the other.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Adding a T-SQL Job Step to a SQL Agent Job with PowerShell]( Rob Sewell from [SQLServerCentral Blogs]( In my last post, I explained how to alter an existing job step across many servers. I also had cause...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [External SQL files vs. Stored procedures in SSIS development]( Rayis Imayev from [SQLServerCentral Blogs]( [2017-Feb-21] I'm creating this particular blog post more for myself to remember and use it as a reference to related discussions...[More »]( Question of the Day Today's Question (by Steve Jones): I have connected to the master database of my Azure SQL Data Warehouse. Currently I have this set for 100 DWUs of performance. I want to set this to 200 DWUs. Can I do this with code? 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: Azure SQL Data Warehouse. 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 [Pro Power BI Desktop]( This book shows how to deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. You can now take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience’s attention. Slice and dice the data with remarkable ease then add metrics and KPIs to project the insights that create your competitive advantage. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I run this command: Backup-SqlDatabase -ServerInstance .\SQL2014 -Database sandbox -Incremental What is the name of my differential backup file? Answer: sandbox.bak Explanation: The default file name is the database name with .bak on it, even for differentials. Ref: Backup-SqlDatabase - [( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [Building calendar table using MTVF]( Darko Martinovic from [SQLServerCentral.com]( SELECT [Calendar].[DetermineEaster](2016,0) ->Corpus Christ SELECT [Calendar].[DetermineEaster](2016,1) ->Easter SELECT dateAdd(day,-46,[Calendar].[DetermineEaster](2016)) --Powdery mildew SELECT dateAdd(day,-7,[Calendar].[DetermineEaster](2016)) --Palm Sunday SELECT * FROM CALENDAR.EASTERS(2017, 2018); TheYear TheEaster TheCorpusChrist ----------- ---------- --------------- 2017 2017-04-16 2017-06-15 2018 2018-04-01 2018-05-31 SELECT * FROM CALENDAR.HOLIDAYS('20170101', '20171231'); TheDate HolidayName ---------- ---------------------------------------------------------------------------------------------------- 2017-01-01 New Year 2017-01-06 Epiphany 2017-04-16 Easter 2017-04-17 Easter Monday 2017-05-01 Labor Day 2017-06-15 Corpus Christi 2017-06-22 Anti-Fascist Struggle Day 2017-06-25 Statehood Day 2017-08-05 Victory and Homeland Thanksgiving Day and the Day of Croatian Defenders 2017-08-15 Assumption of Mary 2017-10-08 Independence Day 2017-11-01 All Saints'Day 2017-12-25 Christmas 2017-12-26 St. Stephen's Day SELECT CalendarDate ,DayOfTheWeek ,IsWeekEnd ,isHoliday ,isWorkedDay ,WorkedDayNo FROM Calendar.Calendar('20170101', '20170131'); CalendarDate DayOfTheWeek IsWeekEnd isHoliday isWorkedDay WorkedDayNo ------------ ------------ --------- --------- ----------- ----------- 2017-01-01 1 1 1 0 1 2017-01-02 2 0 0 1 1 2017-01-03 3 0 0 1 2 2017-01-04 4 0 0 1 3 2017-01-05 5 0 0 1 4 2017-01-06 6 0 1 0 2 2017-01-07 0 1 0 0 3 2017-01-08 1 1 0 0 4 2017-01-09 2 0 0 1 5 2017-01-10 3 0 0 1 6 2017-01-11 4 0 0 1 7 2017-01-12 5 0 0 1 8 2017-01-13 6 0 0 1 9 2017-01-14 0 1 0 0 5 2017-01-15 1 1 0 0 6 2017-01-16 2 0 0 1 10 2017-01-17 3 0 0 1 11 2017-01-18 4 0 0 1 12 2017-01-19 5 0 0 1 13 2017-01-20 6 0 0 1 14 2017-01-21 0 1 0 0 7 2017-01-22 1 1 0 0 8 2017-01-23 2 0 0 1 15 2017-01-24 3 0 0 1 16 2017-01-25 4 0 0 1 17 2017-01-26 5 0 0 1 18 2017-01-27 6 0 0 1 19 2017-01-28 0 1 0 0 9 2017-01-29 1 1 0 0 10 2017-01-30 2 0 0 1 20 2017-01-31 3 0 0 1 21 [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 2016]( : [SQL Server 2016 - Administration]( [MSDB growth in SQL 2016]( - Hi, We have an MSDB which has grown to over 10Gb on a SQL 2016 RTM server. At first we thought... [Restore DB takes a coffee break to open file explorer]( - Hi all! This has been a problem sice my first SQL DB's in 2005. When i want trestore from device, it's really... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [stored proc fails randomly...]( - I have a agent task that calls a SSIS routine and in that routine is a stored proc call.. Sometime... [How do I state my foreign key in my Create Table?]( - Hi Expert, I tried to create a table with foreign key but I am not sure why it is giving me... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Performance improves after reboot]( - I am an ETL Developer who was asked to look at SQL Server performance problem. We have two DBA's but they're... [Backup Database is terminated Abnormally Failed on SQL Server 2014 RTM]( - My Database backup is not working in SQL 2014 RTM version. It works fine on SQL Server 2008 R2. All... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Key Lookup to Index Seek?]( - Hello All, I am trying to educate myself a little more about Index usage in regards to performance tuning. I have... [Compare two sets of data]( - I have two sets of data . Select * from table A where fileid=3196 select * from table A Where fileid=2918 example : There are 534... [Storing Sql Scripts]( - I'm looking to store sql statements into a column and then retrieve and execute. Basically I want to try and stay... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Restore Database from backup and applying all transaction logs backups performed from last full database backup]( - How do you restore a database from backup and applying all transaction logs backups performed from last full database backup? I... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Find Matching People in table based on similar values]( - Hello All, I'm trying to find a method that will run on Data-sets of 100K rows to a million efficiently and... [Convert first column in row without duplicate values, and second column in rows]( - Hi, I have a table with 2 columns : questions, answers. The same questions can be answered by many users so my first... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [UPDATE STATISTICS with FULLSCAN -- implications?]( - I am being asked by our SharePoint people upon suggestion of a Microsoft engineer to run an "UPDATE STATISTICS ... [Seeing invalid column and object everytime I create StoreProcedure or insert a new column]( - Hi experts, I am really new in this IT field and in my project I faced this problem in experiencing frequent... [redesigning table to accommodate new data types]( - i have a table in which there is  column called Marks of type int. This is used to save the... [Need to migrate a databases from DB2 to SQL server]( - Hi, Need a help in Migrating a database of 100 GB from DB2 to Sql server. Client would extract the data from... --------------------------------------------------------------- [SQL Server 2008]( : [T-SQL (SS2K8)]( [Exists on where clause]( - Hi, Sample data to play with Declare @vendor table(IdVenor int,MessageId int); Declare @vendor_customer table(IdVenorCustom int,MessageId int); Declare @Message table(MessageId int, description varchar(4000)); insert into... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [Simple SSIS failed with The value could not be converted because of a potential loss of data.]( - I have a very simple SSIS: 1. Source: 5 columns, all in string(50) 2. Key two columns: Period, Val 3. To do: get... --------------------------------------------------------------- [Database Design]( : [Design Ideas and Questions]( [Many to Many Relationship Design Query]( - Hi, I am looking to design tables which contain tag(s) and their relationship with tasks (parent/child). Requirements: Any Parent task can be assigned... --------------------------------------------------------------- [Career]( : [Presentations and Speaking]( [Potential presentation idea: SQL Server for absolute beginners]( - So, I took today off from work to prepare and travel to SQL Saturday #517, and something occurred to me... 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 ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com