A Brief Introduction to PowerShellAI, an Intelligent Command-line (2023-04-24)

SQLServerCentral Newsletter for April 24, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [A Brief Introduction to PowerShellAI, an Intelligent Command-line]( - [Creating a Standalone Executable Python Application]( - [Automatically Tracking and Deploying Static Data in Flyway Enterprise]( - [From the SQL Server Central Blogs - Data Marketplace]( - [From the SQL Server Central Blogs - How Does SELECT * Affect Query Store?]( - [SQL Server Internals: In-Memory OLTP]( Question of the Day - [Summing a BIT column with a numeric as the outcome]( The Voice of the DBA  A Case for Document Storage One of the challenges for both database developers and administrators is doing more, often with less. Many companies continue to grow their database estate, both in width with more platforms, and in depth with more instances of the platforms they have. Some companies will look to shrink their staff, especially when adopting a cloud platform, while others may add more databases, but not increase staffing to match the additional load. In either case, what many have found over the years is that the cost of labor is high. Both for developers that write code against databases, and administrators that manage those platforms. While licensing can seem to be a large number, compared to the cost of labor, it isn't usually a significant number. Often it seems administrators would prefer more of the same database platform. Developers often seem to ask for new types of database platforms, often some type of [NoSQL data store](. I ran across [an article that makes a case for adding in document storage data stores]( to your environment, instead of just choosing am RDBMS. Labor is one of the big reasons for doing this. The other one is that for a given workload, the hardware cost is lower. The article opens talking about the object/relational mapping problems. There is some truth to the time and effort to map an object in an application to a table (or set of tables) in an RDBMS. There is some knowledge required to do this, but I also think it's an important skill for many developers. The same type of object mapping to a serialized JSON document is shown as being easier, and it is. However, if you add or change your object, the application code to handle the document from the data store gets complex. Over time, you will have lots of "new" fields that don't exist in older documents. How do you handle those? It's not hard, but labor is required to write this code. And this code has to be maintained over time. The other argument is that less hardware is needed, made by noting all the data you may need can be co-located with your object. This is what we would call denormalization in an RDBMS and leads to data duplication? Whether that is a problem or not depends on the amount of duplication. Certainly the structure of an application that often works to send or retrieve singleton rows is easier in a document database. However, non trivial queries, which the author postulates are hard to write for developers, are likely hard to run for a document database. The load of querying across lots of rows, or updating them, is much higher in a document database. Depending on how often you update data, this can be an issue, and require more hardware. Which is better? The classic "it depends" applies here. Database modeling is important in both cases. As I've worked with people that move to NoSQL databases, I find they struggle to model in that world as much as many of us struggle to model in the RDBMS world. I also find that a NoSQL database often is going to require some sort of data warehouse or other structure that is built for reporting across documents. I'm not against the various types of NoSQL databases, but I also don't think they are a panacea of any sort that magically makes building and operating an application easier. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [A Brief Introduction to PowerShellAI, an Intelligent Command-line]( Mikey Bronowski from SQLServerCentral Learn how you can interact with ChatGPT from PowerShell. [External Article]( [Creating a Standalone Executable Python Application]( Additional Articles from MSSQLTips.com We may need to share our Python desktop or console applications with business users or deploy these applications to multiple machines. However, there are some challenges in the delivery of Python applications. [External Article]( [Automatically Tracking and Deploying Static Data in Flyway Enterprise]( Additional Articles from Redgate Static data is often required for the basic functioning of a database and any dependent applications. Therefore, it's vital that we can track this static data to understand how, when, and why it changed, and that we include any static data changes in our database deployments. Flyway Enterprise will now do both tasks automatically. [Blog Post]( From the SQL Server Central Blogs - [Data Marketplace]( James Serra from James Serra's Blog I question I have been getting from customers lately is about a data marketplace. What is it, and what products can I use to build it? A data marketplace,... [Blog Post]( From the SQL Server Central Blogs - [How Does SELECT * Affect Query Store?]( Grant Fritchey from The Scary DBA I live for questions and recently, I had someone ask me, does using SELECT * affect Query Store. My immediate gut reaction was, hell no. Of course it doesn’t.... [In-Memory OLTP eBook Download]( [SQL Server Internals: In-Memory OLTP]( Kalen Delaney from SQLServerCentral.com In this book, Kalen Delaney explains how the 2016 In-Memory OLTP engine works, how it stores and manipulates data, and how, even with all data stored in memory and no locking or latching, it can still guarantee the ACID properties of all transactions.   Question of the Day Today's question (by Tonie Huizer):  Summing a BIT column with a numeric as the outcome I created a table like this: CREATE TABLE NewTable (record BIT) GO I have this query to run: SELECT record FROM dbo.NewTable AS nt GO The software using the outcome of the code would always expect a numeric value. What SELECT will meet this requirement? 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) Percentile Memory Grant Feedback Percentile Memory Grant feedback was introduced in SQL Server 2022 as a way to adjust the memory grant feedback not to the last execution, but as a percentile of history. What compatibility levels does this work under? Answer: 140 and above (SQL Server 2017 level) Explanation: While this was introduced in SQL Server 2022, it works under databases with compatibility levels 140 and above on SQL Server 2022 instances. Ref: Memory Grant Feedback - [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 2016 - Administration [System versioned tables permissions]( - I have an issue with these permissions which I don't understand. When I add a user to a database containing some system versioned tables and look at their effective permissions, using SSMS, on tables I get the following: Ordinary tables - No permissions which is what I would expect System versioned history tables - An […] [Index]( - I have Index Maintenance Job I have stand alone secondary server when I run on secondary server it takes only 10 minutes to complete When I run in Production it takes 2 hours to finish I copied the same DB to secondary stand alone server and still it takes 2 hours to complete but server […] SQL Server 2016 - Development and T-SQL [Extracting just the text in parenthesis]( - Stand down. Found the solution Administration - SQL Server 2014 [Database Mail Using TLS 1.2]( - Hi, Has anyone had any experience in changing Database mail to use TLS 1.2....that would be enabling the Checkbox to use SSL in the mail configuration. I have added the registry settings as per a number of sources. To clarify do I need to restart Server, services or just agent. One source said agent would […] SQL Server 2019 - Administration [sql_variant to varchar is not allowed]( - Getting the following error: Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query. ==== Table LogonInfo has the following columns: Spid int UserName nvarchar LogonDate datetime ClientNetAddress varchar Hostname nvarchar I have the following trigger to capture/insert into the table above: CREATE OR ALTER TRIGGER […] SQL Server 2019 - Development [partitioning a table]( - Hi Team , I want to partition a vey huge table . It has approximately 2109955648 rows . I am planning to partition the tables based on day_id as this column is used to filter the data by all the queries . Day_id is nothing but the month in which we had performed the calculation […] [Error during Restore: Msg 15247, Level 16, State 1, Procedure sys.sp_MSrepl_chec]( - My Backup/Restore job is failing - only when the DB is involved in REPL. Looking to see if anyone has a resolution: So this works fine: Create transactional PUB1 (all tables, views, procs) on Server1 DB1 Create SUB1 on Server 2 (populate DB1 on Server2) pulling from PUB1 on Server1 We now have req'mnt to […] SQL Server 2008 - General [Code Correction]( - The underneath code works on higher sql versions(2016 etc) but throws an error on 2008R2. Error:Msg 102, Level 15, State 1, Line 19 Incorrect syntax near '('. Thanks  SET NOCOUNT ON declare @tmp table(name nvarchar(500),Status int) insert into @tmp SELECT name,0 as completed FROM sys.databases WHERE (state_desc = 'ONLINE') DECLARE @dbname nvarchar(500) DECLARE @cmd […] Working with Oracle [Log shipping or Sychornization for Oracle vs SQL server]( - I would like to ask someone working on both Oracle and SQL server. For oracle, we have a script on a standby server that restores the archive log every 5 hrs from a production server ServerA to a report readonly server ServerB I see in the script that runs from synchronizing job which is scheduled […] [Log shipping or Sychornization for Oracle vs SQL server]( - I may click twice, delete this duplicated one Reporting Services [Open Report for editing using Report Builder 15 or below]( - Want to open SSRS report for edit using Report Builder 15 or below using JQuery function. Using this JQuery Function : tree.on('click', '.edit-report', function (event) { var dataPath = $(this).attr('data-Path'); window.location = dataPath; // Open the URL in a new window //window.open(url); event.preventDefault(); }); data-Path="@(string.Format("{0}/ReportBuilder/ReportBuilder_15_0_0_0.application?{1}", Settings.ReportServerUrl, item.Path.Replace(" ", "+")))" It is redirecting to report Manager. […] [Error configuring SSRS. The database engine instance is not valid.]( - Hi, This is a strange one. The error indicates that the database engine instance is not valid, but according to the documentation it should be valid. Does anyone have any ideas why this is occurring? Thanks in advance. Error: The database engine instance you selected is not valid for this edition of report server. Current […] General [SPN Help Needed]( - We have a .NET web application on a web server [MyWebSvr] and the database for the application is on another server [MySQLSvr]. The application is running fine and has been for years but the domain admin who setup the SPN's and delegation left the company years ago and did not document how the SPN's and […] [i need to automate some functions]( - hi all, i have to compare 9 times same scheme of table but each table keeps data for different device. my purpose is to list all the tables and then with cursor to build my queries.the need is to check if the table contains null values in all fields. i run one query to count […] SQL Server 2022 - Administration [Just joined but can't change sa or my new sql login]( - Trying to connect from sql express 2022 to the azure data studio and to a template in microsoft InfoPath. I'm pretty sure i have had it working once with azure but what  i can't get to happen is i can't get the default sa administrator password to update with a new one. It accepts new […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -