Introduction to Statistics (2019-06-28)

 SQLServerCentral Newsletter for June 28, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Introduction to Statistics]( - [Loading partitions incrementally using SSIS]( - [The 2019 State of SQL Server Monitoring Report]( - [Tips for Better Presentations]( - [How to provision realistic and compliant data to dev – free whitepaper]( - [From the SQL Server Central Blogs - T-SQL Tuesday #115 – Dear 20-Year-Old Self]( - [From the SQL Server Central Blogs - T-SQL Tuesday #115 – Notes to 20 year old Wayne]( Question of the Day - [Removing Ghosts]( The Voice of the DBA  Organisations and Technical Apartheid Today we have a guest editorial from David Poole as Steve is traveling today. I have been thinking hard about what I want from the last decade of my career. I am happiest, challenged and fulfilled when working in teams of people with different technical skills and backgrounds. Despite our differences the atmosphere within such teams has been one of energy, collaboration and curiosity. The first time I experienced such a team spirit predates FoxPro so it certainly predates the great Developer/DBA divide. Grant Fritchey and I have both written articles illustrating the destructiveness of this divide. Grant in his [DevOps without the database: A cautionary tale](, and my [DBAs vs Developers: A Sad Tale of Unnecessary Conflict](. Others have similar tales, but where do such conflicts originate? My first clue was when a colleague were venting over a Dilbertesque situation and he joked "this demonstrates [Conways Law](27s_law) to a tee". This was the first time I had heard of such a law but it is a major piece of the puzzle. An extension to Conway's law came from a colleague whose pearl of wisdom was that "You don't get isolated pockets of crapness. If your database design is crap then the code that uses it will be crap, the architecture will be crap. The infrastructure will be no better"! Each functional team striving for its own idea of excellence collectively achieving less than desirable outcomes. I consider to be the final piece of the puzzle to come from a short biography that has nothing to do with IT. [Born a crime: stories from a South African childhood](dp-kindle-redirect?_encoding=UTF8&btkr=1) by Trevor Noah. In it he describes how apartheid was able to work for so long despite its manifest injustices. Separation of South African tribes was by design. This used their natural antagonisms, separate languages and cultures against them. The antagonisms would be so great as to prevent them from unifying and throwing off the ruling minority. I began to think of developers and DBAs as two separate tribes, also with natural antagonisms, language and culture. As with the apartheid system, enforced separation exacerbates the divisions between us. This leads me to believe that organising IT into silos of functional specialisms is a mistake except in the short term. At the boundary of any functional specialism there is a friction point. This point is where communication fails and conflict begins. Yet businesses like to organise people into crisply defined functions. Functional silos do give the benefit of focused excellence. When teams pull in opposite directions that excellence will be squandered. This is why I am so keen on [agile software development]( and the [twelve principles]( behind it. This is why I embrace [DevOps culture]( and why I am delighted to see Redgate place such emphasis on tools to support disciplines and practices that are the bedrock of the DevOps culture. David.Poole [Join the debate, and respond to today's editorial on the forums](  [Redgate SQL Provision]( SQL Source Control](  Featured Contents [Introduction to Statistics]( jbieber from SQLServerCentral.com This article will give a brief overview of how statistics are generated, stored, and used in SQL Server. [Loading partitions incrementally using SSIS]( shubhankarthatte from SQLServerCentral.com In this article, we discuss how to load large partition tables incrementally. [The 2019 State of SQL Server Monitoring Report]( Additional Articles from Redgate Redgate's second annual State of SQL Server Monitoring survey provides new insights into how the world of SQL Server monitoring is adjusting and adapting to emerging challenges, such as compliance regulations and cloud technologies, while keeping on-top of the usual issues such as deployments, availability, and capacity. [Tips for Better Presentations]( Additional Articles from Brent Ozar Unlimited Blog Brent Ozar gives some advice to those of you who give presentations to user groups and conferences. [How to provision realistic and compliant data to dev – free whitepaper]( Site Owners from Redgate Compare common approaches to provisioning test data to database development teams, and assess how they stack up in terms of delivering realistic test data, managing bottlenecks, and meeting data privacy regulations. From the SQL Server Central Blogs - [T-SQL Tuesday #115 – Dear 20-Year-Old Self]( taboggiano@gmail.com from Database Superhero’s Blog This month’s T-SQL Tuesday blog is brought to us by Mohammd Dorab (b | t) and this month topic is about what you would tell your 20-year-old self now... From the SQL Server Central Blogs - [T-SQL Tuesday #115 – Notes to 20 year old Wayne]( WayneS from A Discussion of SQL Server-Related Topics  There’s a lot that goes on in June. From the 75th anniversary of D-Day, Fathers Day, to the official start of summer (though it feels like it already!)....   Question of the Day Today's question (by Steve Jones - SSC Editor):  Removing Ghosts Which command would I use to immediately remove ghost records from a page that have not been cleaned up by the background process? Think you know the answer? [Click here]( and find out if you are right.  [Redgate Webinars]( SQL Provision](    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Reducing Round Trips I need to code some SQL in my application and make parameterized calls. I want to minimize round trips as well. Which of these choices should I choose? Answer: Use sp_prepexec to send my code for execution Explanation: Of these choices, we wouldn't want a new procedure for code. sp_prepare and sp_exec work fine, but these require two round trips. sp_prepexec combines both functions into one network trip. Ref: sp_prepexec() - [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 [AlwaysOn AG versus FCI Licensing]( - AlwaysOn Availability Groups (AG) and AlwaysOn Failover Cluster Instances (FCI) Checking to see the licensing cost for 2 and 3. Would there be licensing cost for secondary replica or the cost would be for secondary server only? Please advise? FCI is for HA scenarios. An AG synchronous secondary replica, co-located with the primary, is for […] [Powershell for Automating SQL Server Restore.]( - Team, Has anybody written a Windows Powershell Script to Automate the SQL Server Restore process. I am looking for following Input Parameters:- DB Name Backup Path The script should be able to script out the Logins before starting the Restore process. I am looking for to Restore the Databases using it's most Recent Backup copy. […] [Verifying Backup Integrity.]( - Team, If DBCC CHECKDB is successful; do we still need to perform Verify Backup Integrity while taking the DB Backups. As far as i believe; if DBCC CHECKDB is successful then it means that the Database is free from any sort of corruption. In that case; there shouldn't be a need to perform Verify Backup […] [Updates are failing]( - Hi All, We are facing a roadblock in production server, hope experts can give some solution here. we are having a 900GB MS-CRM on premise database with replication in place. In every 6-8 hours interval DB is declining all updates from application, unable to find the exact reason. any updates to any table is giving […] [FO cluster.... Sql browser behaviour]( - Apologies if this topic has been cleared away many times. I can't locate the specifics of this if so. A new Sql 2017 Ent FO cluster default instance (1433) named instance 1 (1434) named instance 2 (1435) named instance 3 (1436) named instance 4 (1437) named instance 5 (1438) I can connect to the default […] SQL Server 2017 - Development [Stored Procedure broken out into views]( - So my end result, I want to use the dataset in Tableau. Right now, I have a stored procedure. DECLARE @name TABLE ( VALUE1 VALUE2 VALUE3 )  SELECT DIFFERENT VALUES AS VALUE1 VALUE2 VALUE3 INTO #A FROM DB1 WHERE ( MYPARAMETERS = -1)  SELECT DIFFERENT VALUES AS VALUE1 VALUE2 VALUE3 INTO #B FROM […] [Indexing for View Containing UNION]( - Basic outline is that I wish to ensure uniqueness of data within a table where some of the data can be considered as "shared". Sample data and code: - The intent is "C" is not an actual owner - that all the records with "C" as their owner are effectively shared for all other owners, […] SQL Server 2016 - Development and T-SQL [Disappearing posts]( - Hi all I've tried to post an issue that got marked a SPAM (for no reason that I could see, although I did try to edit it to correct some typos). The forum will not allow me to post the question again as it's saying "Duplicate post" but I can't see it.  The post […] [date yyyy-dd-mm in SQL 2016?]( - Hello, Our new serer SQL 2016 on Windows 2019 is set to language British English. My login is set to British English. Then why is the count of records showing different when using this date format: >= 2018-08-01 It's giving records total from 8th January instead of 1st August. I am comparing the same query […] [tsql help]( - Hi All, Need some help in preparing a restore cmd dynamically using metadata which is stored inside a table. We need to read the file locations from the metadata table and form the RESTORE commands. -- metadata table CREATE TABLE [dbo].[FilelocationsTBL]( [dbid] [smallint] NULL, [dbname] [nvarchar](128) NULL, [logicalname] [varchar](100) NOT NULL, [filename] [varchar](300) NOT NULL […] Development - SQL Server 2014 [If we pass the DB name paramter should take the backup in the same server]( - Hi Team, Please help me on this below metioned code. Requirement: If we pass the DB name should take the Full or Diff or Tlog backups in deafult location where the data files existed in the same server. First it should check the DB size and drive space is avaialble then it create or disply […] [bcp exporting to txt and wish to keep nulls]( - How to get end result where the word 'NULL' appears if value was NULL in source table? Getting WARNING: "Error = [Microsoft][ODBC Driver 11 for SQL Server]Warning: BCP import with a format file will convert empty strings in delimited columns to NULL."  , yet wherever there were NULLS in source table there are blanks in txt file. […] SQL Server 2008 - General [Duplicate Query Failing to return the correct count]( - Hi All, WITH TempTable ( DuplicateCount, CustomerID, ActivityID, [RelatedReferenceID], [RelatedReferenceType], [Due Date], [TableType], [Team Member], [TableStatus] ) AS ( SELECT ROW_NUMBER() OVER(PARTITION by CustomerID, ActivityID,[RelatedReferenceType], [Due Date] ORDER BY [Due Date]), CustomerID, ActivityID, [RelatedReferenceID], [RelatedReferenceType], [Due Date], [TableType], [Team Member], [tableStatus] AS DuplicateCount FROM Tbl_MyTable ) Select * from TempTable The problem here is that […] Reporting Services [Linked Report Parameters Not updating when master is changed]( - Hi , as above we have a report say with a parameter which two options and a linked report.  If on the main the design is changed so the parameter can accept multiple values , the linked report doesn't automatically allow the same. We have to relink the report.  Is there a way […] Disaster Recovery [Attaching Multiple MDF and having LDF regenerated]( - There are a bunch of posts for attaching a single MDF and having the LDF regenerated using sp_attach_single_file_db. Can anyone see anything wrong with this approach for doing the same thing with multiple MDFs? 1. Detach the database 2. Delete or move the log file to a new location so it will not be found. 3. […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -