SQL Server Development Using Free Tools (2022-02-11)

SQLServerCentral Newsletter for February 11, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQL Server Development Using Free Tools]( - [Reporting on the Progress of a Flyway Database Development Project]( - [Vertipaq optimization and its impact on columnstore compression]( - [From the SQL Server Central Blogs - SQL Homework – February 2022 – “Soft” skills.]( - [From the SQL Server Central Blogs - Data Pages – the Foundation of SQL Server]( Question of the Day - [Managed Instance In-Memory OLTP]( The Voice of the DBA  Daily Coping Tip Thank someone for cheering you up in the last month 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. Do You Still Use Char? I ran across an article, titled [When to use CHAR, VARCHAR, or VARCHAR(MAX)](, which struck me as something I never do. I mean, I do use varchar (and nvarchar), but I can't remember the last time I actually created a char column. The article is worth a read, and it sets the stage for you to think about your database design process and the choices you make. It's Friday, and if you take a few minutes and think about the last few times you've added columns to a table have you used CHAR as a data type? Or do you default to varchar of any size as a general rule? I tend to do a lot of demo work, and I help customers with different situations. In many cases, we are storing text data, often not strongly typed data. As a result, I find most customers using varchar (or nvarchar), and I've built the habit of using the variable structures in proof-of-concepts and demos. I find it especially handy when someone asks me to enter some data they use and then show how it would be handled. Earlier in my career, I'd often tag a zip code as a 5 character field, or a state as a 2 character abbreviation. However, these days a postal code can be a 5+4, or even 10 with the plus. Many companies work overseas, and may want to account for longer postal codes. States (or regions), can often be 3 characters, but sometimes more. Often we just leave 10 characters for abbreviations or longer lengths is the data might be spelled out. For many business applications, it seems that there might be a definition for what the data should be, but since exceptions can abound, often using a variable-length data type just prevents issues in the future. Add that to the fact that often we are dealing with cheap storage, and it doesn't seem worth the time to try and get the exact size correct. Even when knowing an invoice uses 10 characters, are you sure that you won't exceed the ten-character width? What if you acquire a company that uses 12 character invoice numbers? Easier to set this to a variable 20 and move on. If you work in data warehousing, then you might know what your data sources contain and be more likely to choose fixed types, but is the space savings worth the change that source systems will change? I don't know. I tend to plan for sources to change and allow a little padding in my schema. You might feel differently, but are the space savings worth the potential hassles in the future? I'd be curious what you think today. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [SQL Server Development Using Free Tools]( Wingenious from SQLServerCentral You can make SQL Server database development faster and easier with a FREE toolkit! [External Article]( [Reporting on the Progress of a Flyway Database Development Project]( Additional Articles from Redgate This article demos a novel way to report on the progress of your Flyway development project. It provides both SQL and PowerShell versions of code that extracts information for each database version from the Flyway schema history table and then plots it in a Gantt chart. [External Article]( [Vertipaq optimization and its impact on columnstore compression]( Additional Articles from SimpleTalk Vertipaq optimization is a critical component of the columnstore compression process. Edward Pollack explains how it works and what you need to know to manage it. [Blog Post]( From the SQL Server Central Blogs - [SQL Homework – February 2022 – “Soft” skills.]( Kenneth.Fisher from SQLStudies Well, it’s a new year and a new start, even if I am a bit behind the start of the ... Continue reading [Blog Post]( From the SQL Server Central Blogs - [Data Pages – the Foundation of SQL Server]( Klaus Aschenbrenner from Klaus Aschenbrenner Last week we laid out the foundation for how SQL Server executes queries. I have also already talked here a little bit about pages that are buffers of 8kb....   Question of the Day Today's question (by Steve Jones - SSC Editor):  Managed Instance In-Memory OLTP Which service tier(s) of Managed Instance allow In-Memory OLTP objects? 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) Removing Deleted Pages I delete a lot of rows from a large table. This appears to happen quickly, but I find the space is not able to be re-used right away. What background process in SQL Server actually removes the records? Answer: The Ghost cleanup process Explanation: The Ghost Cleanup process will remove records from pages, allowing the space to be re-used. Ref: Ghost Cleanup - [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 [Indexes]( - I can see only when the indexes got created. However, I am looking is there a way to find who created the indexes? SQL Server 2016 - Administration [Recovering data from a forked database?]( - I'm the default IT person for my wife's rural medical office (because I could identify the computer in a pile of electronics). We're running Windows Server 2016 Standard with SQL Server 2016 on a Supermicro X11SRA motherboard. Our server crashed two days ago (motherboard died, I think) and due to my poor backup policy, I've […] [AG / Availability Group - Cost Threshold For Parallelism]( - I have a simple AG with a ready only secondary SQLSERVER 2016 Enterprise. My question is rather simple, but I haven't been able to find anything on this Should or does it matter for the Cost Threshold for Parallelism setting be the same on both the primary and secondary? Does this matter? Best practice? Thank […] SQL Server 2016 - Development and T-SQL [how to identify why my proc remove from the cache]( - Hi, I've sql server 2016 with SP2 and i have a problem that every few hours my proc get out of the cache. I've tried to use the option(keepfixed plan) inside the proc and it's still get out of cache. my question is - how i can find exactly why the proc get out from […] [Convert JSON Schema / Format to Schema / Format that can be read from SQL Server]( - First, I want to apologise for the shear volume of JSON code posted here. I would like to convert the following JSON Schema into a format (preferably CSV) that can be queried with SQL Server. I often use an SQL application called dbForge to do the conversion, but its failing. { "name": "FACT_WORK_RECORD", "description": "FACT_WORK_RECORD", […] SQL 2012 - General [MS SQL Server 2012 question regarding SSIS]( - OK so I have a customer with a hosted website with eCommerce options. Essentially if you a good CC and the shipping address matches the address on the card, they will take your order. This order information is stored in a MySQL database that they log into via a website, where they proceed to physically […] SQL Server 2019 - Administration [Weird issue when connecting to instance from DMZ computer]( - Hi, Our infrastructure topology is pretty simple. We have a DMZ that needs access to some instances on the LAN. This is what we have and are observing: A mixture of standalone instances and AG instances Connecting to any instance type from SSMS on the DMZ device is always successful - however the icon is […] [SQL Server Policy Support Mail Profile]( - We've been having trouble getting email notifications to work properly on our new 2019 servers. I could send emails from T-SQL job steps, the Database Mail under Management had all our profiles and accounts set up properly, and under the SQL Server Agent Properties -> Alert System, enable mail profile was checked and all the […] SQL Server 2019 - Development [Use of '$$xxx']( - I have seen a number of queries that contain statements like "set @SomeBitVariable = iif('$$SomeName'='1',1,0);" , is there some significance I am missing to the '$$' that I am missing? [Enable 'Microsoft OLE DB provider for ODBC Drivers' or MSDASQL trace in SSMS]( - Hello, I have a Windows 2019 machine with SQL Server 2019. I have created a linked server to Db2 database using 'Microsoft OLE DB provider for ODBC Drivers'. I am using our own ODBC driver. I am running an update query from SSMS as shown below. UPDATE OPENQUERY(TEST2019,'select prodnum, prodname,prodprice from Q.PRODUCTS where prodnum = […] General Cloud Computing Questions [Advantage to using Cloud SQL over csv in a bucket for small-scale analysis]( - Hi, I'm fairly new to cloud computing and devops in general, so please bear with me. I have a situation where I have a .csv file that I will be wanting to add to over time, going up to a few tens of thousands of rows, but likely not much bigger. One of the columns […] Reporting Services [Limit choices in Multi Select Parmameter]( - Hello, We have a client that would like to limit the user to only selecting 10 items in a multi-select parameter that could have 100s of items to chose from. I did see this article It mentions using javascript for a pop up but I have never done anything of the sort. Has anyone […] Analysis Services [Finding or Building Relationships AMong Tables]( - I would like to link a relationship with my Fact table: USE [PPECB] GO /****** Object: Table [dbo].[PI_ServiceRequest] Script Date: 2022/02/03 15:15:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[PI_ServiceRequest]( [id] [int] NOT NULL, [activitypoint] [int] NULL, [startdate] [datetime] NULL, [enddate] [datetime] NULL, [fbocode] [nvarchar](255) NOT NULL, [ordertype] [int] NULL, [ordercategory] […] Design Ideas and Questions [Create Index]( - hello , i want creat indexs in my base on prod , can i do it when users are connected ans the base is used please ? thank you General [What am I doing to get this message?]( - I've noticed for months now that every time I post a response to a discussion, on the first attempt I always get a message back to the effect of 'Did you really want to do that?'.  Then if I try again, it goes through. This happens not just randomly but every time. I'm on Windows […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -