Solve Sudoku with TSQL - Part 1 - a single select (SQLServerCentral 2/20/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Solve Sudoku with TSQL - Part 1 - a single select]( - [How to Move a TDE Encryption Key to Another SQL Server Instance]( - [Sign up for more free training from Redgate]( - [Power BI Custom Visuals Class (Module 89 – Filter by List)]( (From the SQLServerCentral Blogs) - [gdpr - panic part 4]( (From the SQLServerCentral Blogs) Featured Script - [A framework to backup databases on Availability Groups]( The Voice of the DBA Virtual Data Yesterday I republished an editorial from 2014 for the holiday. The topic was [production subsets](of data, which has been something that many data professionals have struggled with for years. Many of us have built scripts to delete, change, obfuscate, or alter production restores as a way of providing useful, but manageable development database sets. Or maybe it's just some of us. I'm sure more than a few of us have given up on this task and just restored production databases in entirety to test and development systems. I changed over my career to become a fan of additively building a known dataset rather than deleting extra data. I advocate adding rows from production (properly masked/obfuscated) and maintaining this set over time as requirements change. However, this isn't without it's own administrative headaches. I think it's easier, but this does require commitment from everyone to keep going over time. It's certainly better than each developer adding their own 10 rows of data to a table for testing. A year ago, Redgate released [SQL Clone](, designed to solve some of these issues. Once an image is created, new databases for test and development and be provisioned in seconds. I found this to be an amazing product that really changes how I develop against databases, though it does require me to stop getting caught up trying to undo changes or manage a single database. Instead, I need to ensure I am saving code to version control and then build the habit to drop and rebuild a baseline database. As we've worked on SQL Clone, I've found that there are lots of companies that offer similar ways of virtualizing your data, giving you access to large, production scale systems in seconds. Data masking, obfuscation, and more are features, with some vendors requiring specific hardware. Others, like Red Gate, have software add-ons ([Data Masker](). All of these products cost money, which can be an issue for many organizations, but I'm glad that this technology is growing and advancing. With GDPR and other draft legislation, many of us need to take better care of our data and build more secure architectures. Containers are another interesting way to virtualiza data, though they don't solve the scale issues. If you can work with a smaller data set, and maintain that, then containers might provide a fantastic way for you to learn to build, teardown, and rebuild databases in seconds. The world of databases hasn't changed a lot in some ways across my career, but in others, I'm amazed. Data virtualization is one of these areas, and if you haven't trialed the technology, maybe you should give it a whirl this year. 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]( ( 3.6MB) 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 Provision]( NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. [Download your free trial]( [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]( Featured Contents  [] [Solve Sudoku with TSQL - Part 1 - a single select]( bevan ward from [SQLServerCentral.com]() A Sudoku solution can be resolved using a single select statement. The first article shows this method and next how this can be optimised to resolve complex puzzles in seconds.[More »]( ---------------------------------------------------------------  [] [How to Move a TDE Encryption Key to Another SQL Server Instance]( Additional Articles from [Database Journal]() If you have a database backup of a Transparent Data Encryption (TDE) enabled database, the database backup will contain encrypted data. Because the database backup contains encrypted data you can’t just restore it to any instance. You can only restore the database backup to an instance that contains the same certificate used to originally encrypt the database.[More »]( ---------------------------------------------------------------  [] [Sign up for more free training from Redgate]( Redgate has committed to hosting a free virtual event in every quarter of 2018, and will be kicking this off on February 28 with a livestream themed around data privacy and protection. The agenda has now been released, so you can see who will be presenting, what they will be presenting, and how you can tune in to watch.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Power BI Custom Visuals Class (Module 89 – Filter by List)]( Devin Knight from [SQLServerCentral Blogs]( In this module you will learn how to use the Filter by List Custom Visual. The Filter by List allows...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [gdpr - panic part 4]( Ed Elliott from [SQLServerCentral Blogs]( In the first part of this series, we looked at where to find out more information about GDPR in the...[More »]( Question of the Day Today's Question (by Steve Jones): I want to set some session key-value pairs using sp_set_session_context. Since this might be used in a number of places in my application, I'm concerned that developers might set too many pairs. What is the maximum amount of data I can store with this feature? 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: SESSION_CONTEXT(). 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): If I run this code, what is returned? SELECT @@OPTIONS Answer: an integer Explanation: @@OPTIONS returns a binary value that represents a bitmap of your SET options. You can convert this to binary to see a representation fo the options.. Ref: @@OPTIONS - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [A framework to backup databases on Availability Groups]( Gaby Abed from [SQLServerCentral.com]() This script generates a health status of each db in an AG (and list of DBs not in AG) and uses an IF/THEN block to check if the synchronization_state_desc is healthy. Feel free to insert in the part marked INSERT BACKUP CODE HERE your preferred backup scripts, or trusted third party ones such as Ola Hallengren's. Please note, this is very rough, and does not check for other criteria such as the DB being online, and what kind of backup (Full, Diff, Tlog) is required, but hopefully this will be usefuI. Ideally, this script and modifications can be wrapped in a proc that is deployed to all servers, both standalone and those in HA's, as a job. In theory, if you want, you can backup the transaction logs on the secondary, and full on primary. This would require a bit of a tweak with the IF/THEN block. [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]( [Backups - AlwaysON Availabilty Groups]( - Hello, I'm studying Windows Server Failover Clustering for a SQL Server Availability Groups with 2 instances and another node as a... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Delete rows based on a second table]( - I have two Tables (In Ms Sql Server) Table Name          Column Names MasterCities        Countryy   City VisitedCities          Country    City I want to d [Convert a query to a View]( - First and foremost I am not a db programmer, Dispatcher by trade. So if I do not belong here do... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Transaction Log Growth]( - Hi, I am currently using Ola Hallengren's backup solution for my backups on a SQL Server 2016 Standard instance. I have... [Showplan]( - Started a new role, have limited (read) rights to the server, I have asked for showplan rights but have been... [word for create a dev server]( - I have a question about a correct term or word I should use; When we have a production server, and later... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [SELECT TOP X vs Offset Fetch? Want to limit query results but not force a sort and scan?]( - I am making some views to use for AdHoc reporting and wanted to put a 100K row limit on them. However,... [Need experts Advice]( - HI guys,  I need expects advice to do below things with great performance.   A Table has 10 million ID's records. My... [Performance improvement following a disable indexes, shrink, enable indexes - but why?]( - I'm stumped and need the assistance of the wider community to understand what is going on. The database in question has... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Adding multiple triggers to same table - Recommendations/Best Practices]( - Hi Folks, I have a requirement to add additional triggers(INSERT/UPDATE) for a table which already have INSERT/UPDATE/DELETE triggers. This is for... [Data Files (mdf)]( - Hi Is it possible to  combine two or three  database mdf files to one file. Thanks --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [finding the right index]( - We use a program for EDI in our company. One of the screens shows us a list of sent documents. ... [Need to get consecutive duplicate rows]( - I need a query that can get me (the ID of) consecutive duplicate rows, where I am grouping on some... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 High Availability]( [Regarding Mirroring setup - TCP/IP network issue]( - Hi Team, I tried setting up DB mirroring between PRD & DR side and I am getting the below error. I checked firewall,... [SQL server 2008 R2 frequent transaction log full and my database is a mirrored one.]( - I have 1 pair of identical server with SQL server 2008 R2 is installed.I have 2 databases in it which... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 Performance Tuning]( [Very erratic performance in last few days.]( - I apologize for such a general question, and there is not enough info for a detailed answer, but ANY general... --------------------------------------------------------------- [Reporting Services]( : [Reporting Services]( [Line chart from table containing static values]( - Hello everyone! I have a SSRS 2016 report, which contains a dataset that produces a basic table as follows: Department    2014    2015    2016    2017 ========   ====  ====  ====  ==== Department A       ... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [Need idea on exporting column data to separate file -ssis]( - Hi all,   I need a help to generate XML file from a table output and Create main folder and subfolder... --------------------------------------------------------------- [Data Warehousing]( : [Strategies and Ideas]( [Data Warehouse Design - Issue with 1:M Dimension]( - Hi, I am building a Data Warehouse that will store report data from a 3rd party system. The system report data... --------------------------------------------------------------- [Data Warehousing]( : [Data Transformation Services (DTS)]( [Stripping out double quotes (") in bulk insert]( - We have a client that's still on SQL Server 6.5 (yes, I know, I struggled to remember that far back too!),... 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