A Set-based Solution for the Bin Packing Problem (2020-04-23)

 SQLServerCentral Newsletter for April 23, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [A Set-based Solution for the Bin Packing Problem]( - [What Percent Complete Is That Index Build?]( - [From the SQL Server Central Blogs - Redgate Community Circle]( - [From the SQL Server Central Blogs - SQL Prompt Fixes Poor Order By Coding]( - [From the SQL Server Central Blogs - Move All Your SQL Database and Log Files in Bulk]( Question of the Day - [Finding Lowercase Characters]( The Voice of the DBA  Daily Coping Tip Take a small step towards an important goal I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Mongodb Encryption The last decade has seen a number of new database platforms get implemented in a variety of organizations. Most of the newer popular platforms have been NoSQL (Not Only SQL) products, and one of the first that became quite popular was MongoDB. I first ran into a few customers using this platform around 2012/2013. At the time some customers inquired whether we had any products to help with development. We did, somewhat, as we [invested in 3T](. MongoDB has enjoyed a lot of popularity and set some standards for how to store and retrieve data. CosmosDB (and other products) has [API compatability with MongoDB](, precisely because of its popularity. While I'm not sure MongoDB is the replacement for your RDBMS, I'm sure it has places and problem domains where it shines. I've also watched the product improve and grow to meet the requirements and desires of more customers. [One recent addition was field level encryption](. MongoDB has had [storage level encryption](, similar to TDE in SQL Server. It also has had [transport encryption]( (think SSL network protection). This new feature is more like Always Encrypted (AE) in SQL Server. Clients can perform the encryption and decryption, assuming they have the keys. This isn't quite like AE as it appears to need code changes, but it does protect the data from system administrators, which is a concern for some applications and industries. How well does this work? There certainly is a challenge with managing keys, distributing them to clients, revoking them, and more. All of these are the same challenges more encryption technologies have to deal with, including AE. This is a good addition to all data platforms, which ought to enable encryption, but having, or even using, the encryption, isn't a panacea for the problem of protecting data. We need lots of other things to be designed, implemented, and operated securely. I'm not a big fan of MongoDB, but it's a fine platform, and it likely works well for some organizations. I'm glad that it continues to enhance its security capabilities, and I hope that anyone implementing it enables all that they can. [Too many installations in the past haven't had good security](, and that's a problem. If you manage data, secure your system, and be sure you continue to monitor the platform for security issues and keep learning more about how to best protect your data. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [A Set-based Solution for the Bin Packing Problem]( Alessandro Mortola from SQLServerCentral A detailed description of a set-based solution for the Bin Packing Problem, that also involves a recursive CTE and Window functions [What Percent Complete Is That Index Build?]( Additional Articles from SQLServerCentral SQL Server 2019 has introduced several new features that offer improved performance. The optimizer continues to evolve and get smarter. From the SQL Server Central Blogs - [Redgate Community Circle]( Grant Fritchey from The Scary DBA I am very excited to announce that I will be taking my paid precon content “Tools for SQL Server Query Performance Tuning” and presenting it for free. This is... From the SQL Server Central Blogs - [SQL Prompt Fixes Poor Order By Coding]( Steve Jones - SSC Editor from The Voice of the DBA SQL Prompt is a fantastic coding aid, but it does more than format your code and provide intellisense. Over time, the team has enhanced SQL Prompt to also guide... From the SQL Server Central Blogs - [Move All Your SQL Database and Log Files in Bulk]( david.fowler 42596 from SQL Undercover As always, the script from this post can also be found on our GitHub repo, HERE You’ve got a SQL Server with a few hundred databases on it (to...   Question of the Day Today's question (by BTylerWhite):  Finding Lowercase Characters My database is currently using the Latin1_General_100_CI_AS_CS collation. I determined this by executing the following statement: SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation'); I create a table variable and insert some rows using the following code: DECLARE @X TABLE ( FirstName varchar (32) NOT NULL ); INSERT INTO @X (FirstName) VALUES ('Adam'), ('benjamin'), ('charles'), ('David'); I need to write a query referencing this table variable to identify the rows where the first letter of the FirstName column is lowercase. The names "benjamin" and "charles" should be returned. I begin my query by typing: SELECT FirstName FROM @X Which of the following search conditions should I use in my WHERE clause? 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 Shape of a Dataframe I run this code in Python: import numpy as np import pandas as pd sales = pd.read_csv('sales_data.csv', parse_dates=['Date']) This gives me a dataframe in the sales variable. I now run the shape method, with this code: sales.shape What is returned from this method?  Answer: A tuple that has the number of rows and then columns Explanation: The shape method returns a tuple of rows and then columns. The head method returns a sample of rows. Ref: shape - [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 [query CTE]( - Hi, I have a table with 2 fields DocumentID and ArrivedDate. I am able to elaborate only 2 documents in a day. How can I add a field (Final Date) with the correct days (every day with a max of 2 doc)? Thank you [Temp table and table variable]( - DECLARE @command varchar(1000) SELECT @command = 'USE ? SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.alloc_unit_type_desc, indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] […] [SQL Job that takes too long]( - Hi, I am trying to send notifications to myself when a articular job that longer than 8 minutes to execute. For that , I have created JOB 2 ( Report Issue Job ) as shown below as a SQL AGENT JOB to send notification. Database configuration was setup and was also able to send myself […] [Sql Statement to display multiple true statement]( - I have a sql statement that checks for certain special characters in a string and returns that. SELECT CASE WHEN (LEN(DSC1) - CHARINDEX(char(1), DSC1)) <> LEN(DSC1) THEN '[DSC1 - NUL (null)], ' WHEN (LEN(DSC1) - CHARINDEX(char(2), DSC1)) <> LEN(DSC1) THEN '[DSC1 - SOH (start of heading)], ' WHEN (LEN(DSC1) - CHARINDEX(char(3), DSC1)) <> LEN(DSC1) THEN […] SQL Server 2016 - Development and T-SQL [Need help on Concatenate based on Group and Aggregate in SQL 2016]( - ************* Got the solution ******************* Hi all, I hope and pray for everyone to be safe during this Covid-19 tough time. I need your help on "Concatenate based on Group and Aggregate in SQL 2016". I have found a possible solution in SQL 2017 using STRING_AGG( ) but my current sql sever version is SQL […] Development - SQL Server 2014 [Link Server]( - Hello guys, I need of you help. I have 3 SQL server, srv1, srv2 and srv3. srv2 have 2 network card I hope you can help me. Thanks . SQL 2012 - General [Sql server error randomly]( - we have sql jobs running in sql server 2012 environment and it has linked server to sql 2017 server environment.  Randomly receiving the error below.  Rerunning the job complete successfully and this is not reproducible consistently. Error Message: OLE DB provider "SQLNCLI11" for linked server "server ip" returned message "Client unable to establish connection". Msg […] SQL Server 2019 - Administration [Have SA but unable to grant myself IMPERSONATE]( - I have sa permissions on server yet I'm unable to grant myself IMPERSONATE in any server database. --i tried GRANT IMPERSONATE ANY LOGIN TO [domain\myself]; GO --Getting Error: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. What do I need to do so that I can impersonate another […] SQL Server 2019 - Development [Real columns are comma separated instead of dot separated ?]( - Hi, installed a new SQL Server Express 2019 and somehow my real columns are now with comma instead of the usual dot when separating number from decimals (viewed in SSMS). I never had this happen before. I installed the same SQL Express 2 weeks ago and it was fine. Does SQL Server now forces my […] SQL Server 2008 - General [Get both Count and the data through single query to reduce time]( - My task includes: 1) Getting the count(which is nothing but the total number of records present in the specific SQL table.As they are millions of rows, it is taking a lot of time to run the count() query). I need to get the count before I write that data from data flow task(source) to the […] Reporting Services [TableAdapter configuration wizard problem: no mapping exist]( - I'm getting this problem message when executing my SQL query: "Generated SELECT statement. 'No mapping exists from DbType Object to a known OdbcType." My SQL query: SELECT hencdiag.diagcode, hadmlog.hpercode, CONCAT(hperson.patlast, ', ', hperson.patfirst, ' ', hperson.patmiddle) AS name, hcity.ctyname AS district, haddr.ctycode, hadmlog.disdate FROM hadmlog INNER JOIN hperson ON hadmlog.hpercode=hperson.hpercode INNER JOIN haddr ON hadmlog.hpercode=haddr.hpercode […] SSRS 2016 [Expand/Collapse action shows "+" instead of "-"]( - I want the level 0 expand/collapse icon to be "-". Apparently I've done this incorrectly; how do I fix it? Attachments show ToggleItem textbox, Group visibility expression, and Preview. Powershell [create database if not exists]( - Good morning all I am looking to create a sql server database if the database does not exist on my instance I use this script for this Import-Module "SQLPS" -DisableNameChecking Import-Module -Name "${PSScriptRoot}\Module\logModule.psm1" -DisableNameChecking Add-Type -AssemblyName "Microsoft.SqlServer.Smo" $servers= 'DESKTOP-95KKMMH\PRODUCTION','DESKTOP-95KKMMH' $database='produit' foreach($server in $servers) { Set-Location SQLSERVER:\SQL\$server $db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -Argumentlist $server, $database if($db […] Relational Theory [Entity Relationship diagram - Path and Node entities]( - I have two tables; a path table that contains distinct paths and a node table that contains distinct nodes. A node can be present in one or more paths. A path is composed of one or more nodes (the path is composed of the id of the nodes concatenated by a dot). The image below […] COVID-19 Pandemic [Daily Coping 20 Apr 2020]( - Today's tip is to find positive stories in the news and share with others. A few things from me:   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -