Intervals Part 1 - Definitions and Terms (SQLServerCentral 3/12/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Intervals Part 1 - Definitions and Terms]( - [Non-Standard Delimiters for Columns and Rows Using SQL Server Bulk Insert]( - [Redgate’s support for Azure SQL Database Managed Instances]( - [Data Masker for SQL Server–Syncing Values Across Rows]( (From the SQLServerCentral Blogs) - [Using SWITCH On A Single Partition]( (From the SQLServerCentral Blogs) Featured Script - [SQL Battleship]( The Voice of the DBA Our Greatest Strength, Our Greatest Weakness Today we have a guest editorial as Steve is on holiday. The role of the DBA is to ensure that the portion of the shared data asset residing within the databases under his or her control is appropriately available. I use the term “appropriately available” to encapsulate performance, security, resilience, availability, recoverability and stability. The characteristics of a person suited to the role tend towards being cautious, methodical and disciplined. The role does not reward the Gung Ho. When presented with a new idea for the way in which databases should be managed, DBAs will be quick to spot weaknesses in the idea and threats that the idea poses to the task of ensuring appropriate availability. Reading through the 2018 report from Redgate on [the state of Database DevOps](, I begin to question whether we look beyond those weaknesses and threats posed by a new idea and explore adequately the strengths of the idea and the opportunities that it presents? Why do only half of us have our databases under source control? Perhaps we should take a conscious decision to explore the strengths and opportunities of an idea first before following our natural inclination to examine weaknesses and threats. Such an approach is advocated as part of the Disney Brainstorming approach with its three distinct phases [The Disney brainstorming technique] Phase Description The Dreamer This phase is all about capturing ideas without constraint. No criticism or doubts are expressed at this stage The Realist This phase is not to shoot ideas down in flames. This is about exploring how the ideas collected during the “Dreamer” phase could be implemented. What would be necessary in order for them to work? The output from this phase are ideas that have had some thought put into them and have a fairly well formed structure. The Critic This is where the ideas face their toughest challenge and are subject to the cynical glare of criticism. Ideas that survive this phase are worthy of being prioritised for execution. The Disney technique works well because the “Dreamer” stage will generate a lot of ideas, some of which will be out-of-the-ordinary and ones that would not normally be expressed. Who is to say that, out of 15 ideas, idea number 14 wouldn’t be a winning idea that would revolutionise your business? If you were to try and mix the three phases together what would happen is that you would get maybe half a dozen timid ideas. We would not reach double figures let alone express Idea 14. There is an experiment you can do with your colleagues to demonstrate the power of the Disney technique. - Assemble your colleagues around a white board. - Each colleague must take turns to draw an item representing a certain subject - Each drawing must be different from the ones before but for the same subject - Keep going until the person whose turn it is cannot think of an object or until 5 minutes have elapsed. My team were given the subject of an apple. The first few iterations around the team produce ideas were simple evolutions on the original subject. - An apple - An apple with a bite taken out - An apple with two bites taken out - ...etc. At some point, probably through desperation, a team member would draw an oblong on the screen and sheepishly suggest “Apple iPad”. The adjudicator would simply nod and say keep going and from this point on it would be as if a dam had burst. The team felt they had been given permission to produce off the wall ideas. Someone drew a grandmother (Granny Smith apples), someone else drew an insect (The Beatles, Apple Corp). The variations came faster and faster. I saw this scenario played out with several teams and although the subjects were different the results were remarkably similar. At roughly the same point the sheepish tangent would appear followed by a deluge of increasingly off-the-wall ideas. If we had to evaluate the drawings as they came in we would not have produced anywhere near the volume or variety. Looking at Robert Sheldon’s series of “[The SQL Server features that time forgot](” how many of those features suffered a premature demise? Do we retreat behind our moat, pull up the drawbridge and start heating the oil rather than engage, explore and develop the ideas? Do we perform a sWoT analysis or a [SWOT](analysis? Are we, as we would like to think, guardians of stability or, sadly, the champions of stagnancy? David Poole from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [SQL in the City]( Free data protection and privacy livestream Many of you will be aware that the new GDPR legislation comes into effect in May and, in light of this, Redgate recently hosted a livestream that included sessions to help you become best equipped to deal with the challenges GDPR brings to compliant database management. [Watch the recording]( [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]( Featured Contents  [] [Intervals Part 1 - Definitions and Terms]( J. Drew Allen from [SQLServerCentral.com]() Learn the basic terms and definitions for intervals in SQL Server.[More »]( ---------------------------------------------------------------  [] [Non-Standard Delimiters for Columns and Rows Using SQL Server Bulk Insert]( Additional Articles from [MSSQLTips.com]() In this tip, Tim Smith looks at how to use bulk insert to load data into SQL Server when the column and row terminators are not what you typically see.[More »]( ---------------------------------------------------------------  [] [Redgate’s support for Azure SQL Database Managed Instances]( Last week Microsoft released the public preview of Azure SQL Database Managed Instances – an exciting new option for running SQL Server workloads in the cloud. This blog post explains what they are, and how Redgate's SQL Toolbelt supports them.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Data Masker for SQL Server–Syncing Values Across Rows]( Steve Jones from [SQLServerCentral Blogs]( I’ve been playing with Data Masker for SQL Server v6 and it’s an interesting product. I like the way it...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Using SWITCH On A Single Partition]( SQLRUs from [SQLServerCentral Blogs]( Several months ago, I was looking at a question posted on ask.sqlservercentral.com. I discovered an answer to a question regarding...[More »]( Question of the Day Today's Question (by Steve Jones): I want to capture data from sp_server_diagnostics for my Failover Clustered Instance (FCI). What code starts the logging process? 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: Failover Clustered Instance (FCI). 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 [Securing SQL Server: DBAs Defending the Database]( tag=redgatsof-20 linkCode=as2 camp=1789 creative=9325 creativeASIN=1484222644) Protect your data from attack by using SQL Server technologies to implement a defense-in-depth strategy, performing threat analysis, and encrypting sensitive data as a last line of defense against compromise. The multi-layered approach in this book helps ensure that a single breach doesn't lead to loss or compromise of your data that is confidential and important to the business. [Get your copy from Amazon today](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): If I connect to SQL Server 2016 and I have never run SET CONTEXT_INFO, what is returned from this code? SELECT CONTEXT_INFO() Answer: NULL Explanation: If I never SET CONTEXT_INFO, then CONTEXT_INFO returns NULL in SQL Server 2016. In Azure SQL Database, this returns a unique session specific GUID. Ref: CONTEXT_INFO() - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [SQL Battleship]( Sean Smith from [SQLServerCentral.com]() A while back I found myself with some free time and looking for a new SQL challenge; the only caveat was that I wanted it to be something outside of the normal scope of coding in which I would typically engage. I pondered what would fall into this category and came to the conclusion that a game completely playable from within SQL Server Management Studio would be a great fit. After some careful consideration I decided that the classic game of Battleship would be an ideal candidate. The game is well known, fun, and even somewhat lends itself to the natural design and output of SQL queries. So if you've ever wondered what happens when a SQL developer gets bored and is looking for something “exciting” to do, wonder no more… Overview: Battleship is a two player game where the objective is to guess the location of an opponent’s fleet of naval ships hidden throughout an ocean grid. Each player takes turns selecting grid coordinates to fire upon, attempting to determine occupied squares and subsequently sink all of the ships within them. Setup: Each player begins with a fleet of ships which must be placed within a grid and kept hidden from their opponent. They can be positioned either horizontally or vertically (but not diagonally) within the grid spaces. Ships can be situated alongside each other and touch, though they cannot occupy the same physical space and they cannot have any portion extending outside of the grid boundary. Game Play: Once each player’s ships have been deployed, the game proceeds in a series of rounds. At the start of each round, players select a grid coordinate to attack in an attempt to locate and sink their opponent’s fleet. The outcome of the shot will be conveyed as either “Hit”, “Miss”, or “Sunk”. The game is over when one player successfully sinks all of their opponent’s ships while still retaining some portion of their own fleet. Rules: There are several variations to the game, but I decided to go with the set of rules for which I am most familiar: - The ocean grid for each player is 10 x 10 in size - Each player gets two grids: one to place their ships on and track their opponent’s shots, and one to track their own shots against their opponent - Each player is allowed one shot per turn, regardless of the shot's outcome - Opponents are informed whether or not their shot resulted in a hit, miss, or sinking (and in the case of a hit or sinking, the name of the ship which was impacted) - Each player starts with five ships, varying in type and the number of squares needed to occupy a position on the grid - Aircraft Carrier: 5 squares - Battleship: 4 squares - Destroyer: 3 squares - Submarine: 3 squares - Patrol Boat: 2 squares Auto Ship Deployment: Ships are automatically deployed for both the Human and Computer player. This makes the initial setup process far simpler when using a SQL interface (should you not like the deployment generated at the start of the game, simply re-run the code using the "new game" parameter to produce a different set of ship placements). The auto deployment will determine which ships to place where and whether or not to place them horizontally or vertically. As well, since some players' strategy includes not having ships touch borders and / or not having ships touch each other, each deployment will randomly decide whether or not to use either (or both) of these strategies for the Human and / or Computer player. Computer AI: The computer AI has been developed to be competitive and present the human player with a decent challenge. Originally it used a basic spacing approach when hunting for ships combined with honing and general position elimination logic when a ship was found. This yielded an average of 49 shots to locate and sink an entire fleet. I decided to implement an additional process which took into consideration which areas of the grid were heavily fired upon versus those which still contained a large amount of open space. This brought the computer player’s average down to 44 shots. At this point I became curious as to what a typical average should be, and after scouring the Net I hit upon a great [article]( by [Nick Berry]( from his [DataGenetics]( website (thanks Nick!). Using this, I modified my code to use an algorithm which creates a pseudo-probability density matrix. This brought the average shots down to 42, making the game quite challenging at times. Starting The Game / Selecting Coordinates: The only line of code you will need to deal with and modify is located at line 97 (the "SET @Pick_Human" portion). When starting a new game the variable must be set to NEW. From that point on you will need to populate it with your coordinate picks (for example: E05, J10, B07). Coordinate picks must be three characters in length (E5 is considered invalid, while E05 is considered correct). Output: At the start of a new game you will be presented with the following: - Two 10 x 10 grids (upper and lower) - The upper grid will be used to record your shots against the computer (it will start off blank and as the game proceeds it will become populated with hit and miss indicators, depending on the outcome of your shot) - The lower grid will display your ship placements and record hit and miss indicators from shots placed by your opponent - Status Summary - The status summary will show general game information applicable to each player (more on this below) Upper grid at the start of a new game: Lower grid at the start of a new game: Status summary at the start of a new game: As the game progresses, each of the grids will become peppered with hit and miss indicators (represented by an X or dot, respectively). As well, the most recent shot fired for both the human and computer player will be shown as either a hit (H) or a miss (M): Upper grid during mid-gameplay: Lower grid during mid-gameplay: The status summary will also update with each round of shots. Below we see that the computer player’s last shot was on F09 which resulted in a hit on the Battleship, the computer has sunk the human’s Aircraft Carrier and Destroyer, 3 of 4 possible hits on the Battleship have been achieved, the Patrol Boat and Submarine have yet to be located, a total of 21 shots have been fired resulting in 11 hits (or a 52.38% hit rate), and a total of 2 of the fleet's 5 ships have been completely sunk: When the game ends you will be shown the computer player’s remaining ship locations should you lose. Upper grid, revealing the computer player's remaining ship location(s): Lower grid, showing the final status of the human player's ships: The status summary confirms that the computer player has successfully sunk all of the human player’s ships: A game over indicator will also be displayed, confirming the winner and the number of shots taken to defeat their opponent: Final Thoughts: I have to admit that I had a lot of fun working on this. It presented some unique challenges and was a change from my normal SQL routine. If you wish, please feel free to post your score (for first win and first loss) in the comments section of this article. I am curious to see how people fare against the computer AI. Any friendly feedback is always welcome. Enjoy! [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]( [TDE Trusted CA Vs. Self-Signed Certificate]( - Hello, We have a customer who wants us to TDE on their databases. I need to be able to explain at... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Database Restore Failing]( - I am hoping you can help me out here. Specifications SQL Server 2014 SP2 CU8 Windows Server 2012 3 instances on one server... [Trouble Enabling SQL Audits]( - Trouble Enabling SQL Audits  I am running SQ --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Query not return any records although it give me result but in another database]( - Problem How to trace this query to know the reason of why it return null records . query below work in database... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Help with Dynamic SQL in Store Procedure]( - Hello SQL Experts,I am trying to create dynamic SQL in a store Procedure for a search form with many search... --------------------------------------------------------------- [Cloud Computing]( : [SQL Azure - Administration]( [SQL Azure instance doubt]( - Hi All, Can we change the port no of Azure Sql Instance? Today, someone was asking me. I thought Azure instances... --------------------------------------------------------------- [Programming]( : [Powershell]( [PS script works in ISE but fails in SQL JOB step with a syntax error]( - I need to copy a subset of rows from a table on a (sql2016) server, to a table on a... --------------------------------------------------------------- [Database Design]( : [Disaster Recovery]( [AlwaysOn: nice but our servers are virtualized]( - I am currently exploring some high availability solutions so I dived into the world of High availability options for VMWare... 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