How Bad are Bad Page Splits? (2019-11-25)

 SQLServerCentral Newsletter for November 25, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Understanding Auctions]( - [Take the 2020 State of Database DevOps Survey – win a new iPad]( - [From the SQL Server Central Blogs - Using volumes in SQL Server 2019 non-root containers]( - [From the SQL Server Central Blogs - The curious case of CHAR(0)]( - [How Bad are Bad Page Splits?]( Question of the Day - [The Default NULL]( [Redgate SQL Source Control]( The Voice of the DBA  Azure Sphere In the recent Ignite keynote, Satya Nadella noted that there would likely be an explosion of data in the next decade, much of which may come from sensors and other connected devices. That's both exciting and worrisome to many of us that work in the field. Lots of challenges and data to work with, but this is also scary because of the poor level of security many vendors have implemented in their connected devices. Microsoft Research put together a team under [Project Sopris](, who shared many of the same fears. They wanted to try and find a way to build small, connected devices, but keep them secure. That's the type of research we need. It's all good to look for a new cool product to sell, but we also need people that provide a framework and ideas on how to do that securely. This started out internally called CodeName 4x4, with the idea that small devices owuld have 4MB of RAM and 4MB of flash. That's what I used to call a desktop, but now it's a tiny embedded board. They produced a whitepaper on [The Seven Properties of Highly Secure Devices](, which led to a prototype that was sent out for field testing and examination by security researchers. [This has become Azure Sphere]( a mix of secure microcontrollers, a secure OS, and a cloud security service. Together, this may be one way that we do allow more Internet of Things (IoT) devices, but in a secure way that will not compromise our networks and systems. There are still challenges here, especially with small devices that may have data quality issues as they are designed to be cheap and easy to replace, but that's another area of research. This is a step forward for security, something that we desperately need as more vendors want to build and sell sensors and services. The one thing that I wish they had done differently was choose another acronym for microcontrollers. They wrote MCU, which has grown to have [a different meaning]( for me across the last decade. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Understanding Auctions]( Additional Articles from SimpleTalk We participate in auctions all the time even when we don’t realize it. In this article, Joe Celko discusses the many different types of auctions and how they work. [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. From the SQL Server Central Blogs - [Using volumes in SQL Server 2019 non-root containers]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold I’ve seen a few people online asking how to use docker named volumes with the new SQL Server 2019 RTM images. Microsoft changed the way SQL runs within a... From the SQL Server Central Blogs - [The curious case of CHAR(0)]( Kenneth.Fisher from SQLStudies Over the years I’ve seen quite a few strange things with SQL Server. This one may not have been the ... Continue reading [How Bad are Bad Page Splits?]( Mike Byrd from SQLServerCentral A look at bad page splits and how they affect your database.   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Default NULL I create a table like this: CREATE TABLE NullTest (SomeKey INT); GO If I now run this: INSERT NullTest VALUES (NULL) Under what conditions does this return an error? 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) The CTE Insert I have to insert some data into a table from other sources in the database. My SELECT is complex and includes a CTE to make this easier to read. What type of structure should I write for a CTE with an INSERT? -- 1 WITH myTally(n) INSERT dbo.LottaNumbers (n, somechar) AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null)) FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) ) INSERT dbo.LottaNumbers (n, somechar) SELECT n, REPLICATE('a',4500) FROM myTally -- 2 INSERT dbo.LottaNumbers (n, somechar) WITH myTally(n) AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null)) FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) ) SELECT n, REPLICATE('a',4500) FROM myTally -- 3 WITH myTally(n) AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null)) FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) ) INSERT dbo.LottaNumbers (n, somechar) SELECT n, REPLICATE('a',4500) FROM myTally Answer: 3 Explanation: The structure is to write the CTE and then include the INSERT line above the SELECT. Ref: INSERT - [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 - Development [T-SQL to find gaps in Date field of table]( - I'm inexperienced in asking these questions in a forum. I'm sure there's a more elegant way to display this code, etc. I hope I can explain what I'm after here. I have a simple table with a record ID and a date column in it. You can run this simple code to create the table […] [Dividing a string column]( - I have a table that has one column that has multiple values. I need to create a new table with each value in a column and I am not sure how. create table #mytable ( ID NVARCHAR(50), MainImage nvarchar(MAX) ) INSERT INTO #mytable (ID, MainImage) VALUES('asdf123', '["0535bd68e0b14c0f6ff78dff12a7da56e81bd5d0", "27d8b9e313e5c50a650168bea38052cd49fc48b2","cbdfd4438d8259baacad4db3f66d7178c0648f38", "b53afe5d5471003d90e09906f08c0b0fc43004b4","53c6a621ec464a8d174e8094e883bddb5db7c795"]'), ('asdf144', '03e9c0f463de2d92db2fe89790cf8c80d616b825'), ('asthd23', '["b82ae6fa21714e0a3885bdd78195b94fbabd5d65","b66347b4c5436bd69aa427bbbe59d566e024389f"]'); select * […] [NOT Operator and NULLs]( - --test data declare @T table(a int null, b int null) insert @T(a, b) select 0, 0 union all select 1, 0 union all select 0, 1 union all select 1, 1 union all select null, null union all select 0, null union all select null, 0 union all select 1, null union all select null, […] SQL Server 2016 - Administration [AOAG did not failover to other node]( - We had a crash on primary node, created memory dump. But AOAG group did not fail over to secondary node. So, when we encounter memory dump on primary, does AOAG will not fail over? are there any scenarios where AOAG fail over will not work? [Trying to deny delete inside a procedure]( - So the scenario is that I have a user with a role of db_datareader and db_executor. They get an error when they try and run a plain delete statement, however I have a stored procedure that has delete statements in it and they are not blocked. I have tried denying delete on the role and […] [power shell script for add datetime to file]( - Hi, i would like to generate csv file with datetime through sql job with below power shell script.But it didn't work. Any suggessions. $dt=get-date -format "_yyyymmmd_HHmss" Invoke-Sqlcmd -Query "SELECT * FROM [xxxxxx].[xxxx].[xxxxx]" -ServerInstance "LocalHost" | Export-Csv -Path D:\folder\data$dt.csv" -NoTypeInformation   Thanks. SQL Server 2016 - Development and T-SQL [Union with temp table]( - insert into dbo.details ( DetailId ,DetailName ) select distinct st.stdid ,st.name from dbo.student st UNION select distinct dt.depid ,dt.name from dbo.department dt UNION select distinct Ot.Ordid ,Ot.OrderName from dbo.Orders Ot i have 3 different select statements where i am doing union and inserting into table; How can i keep all the three select statements in […] [need help with a query to avoid while loop]( - i am struggling with a query to avoid while loop, please guide me if it can be done without a while loop. --create temp table create table #minmaxvalues ( MaxVal sql_variant, MinVal sql_variant, percentval decimal(28,4) ) -- insert values insert into #minmaxvalues select 15765, 0 ,'1.0000' union select 374922, 15765, '1.0000' union select 1178099, 374922, […] SQL 2012 - General [Migrate SQL Failover to Always on]( - Hi,Currently we have a sql failover cluster with an attached iscsi target. This consists of 3 windows server 2012 R2 machines w/SQL server 2012. We are looking to move to an always on group instead. Can this be accomplished with using the same sql servers in the cluster? What is the best path to accomplish […] [How can I do a simple SUM query]( - This is not working. It does not like the Date used, and Something about aggregate and group by clauses... SELECT EmpNo, Date1, SUM(Time) as Expr1 FROM TimeData WHERE (EmpNo = '8939') AND (Date1 = #11/18/2019#) GROUP BY EmpNo SQL Server 2012 - T-SQL [how to lock the record]( - Hello, i have a table called Codes and having below columns Declare table Codes(IdCode int identity(1,1) primary key, EmpCode varchar(10) unique key, AllotedStatus bit default 0) i will get list of EmpCode from the client and i will fill it in this table. my requirement is, users will login to our website and request for […] SQL Server 2008 - General [HTML page execute a procedure]( - I have zero front end coding skills; last time I worked with any code like that was Visual Basic 6. I'd like to have a html page on my local machine where I could organize the execution of procedures. I have to pass a parameter. Does anyone have a sample? Is there some easy web […] [Distance Zip Code Database]( - Hello, I am looking to find away to determine the distance between one Zip Code and another Zip Code in a database. For example, I would like to determine how far Customer A is from Customer B based on each Customer's Zip Code. Can someone help me understand how this can be done? I am […] Integration Services [SSIS 2016 with Catalogs]( - So I am seeing something strange moving from SSIS 2008 to 2016 with Catalog. I must install SSIS on every SQL server and only connect to that server with my SSIS packages, else it using NT Authority /Anonymous Logon to connect to any server that is not local. For example I have a Job that […] [SSIS 2016, cannot bulk load]( - My SSIS package fails on "Execute SQL Talk" which executes a stored procedure trying to load XML file. I get the following error: [Execute SQL Task] Error: Executing the query "exec [dbo].[AccuplacerXMLProcessing] ?" failed with the following error: "Cannot bulk load. The file "J:\Accuplacer\000753-001\16209412.xml" does not exist.". Possible failure reasons: Problems with the query, "ResultSet" […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -