Machine learning K-Means applied to SQL Server data (2024-01-08)

SQLServerCentral Newsletter for January 8, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Machine learning K-Means applied to SQL Server data]( - [Using a SQL Tokenizer]( - [From the SQL Server Central Blogs - SQL Server Blog quarterly review]( - [From the SQL Server Central Blogs - 2024- Of ‘quiet quitting’ and ‘life in the hallway’]( - [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Question of the Day - [Which Default Schema]( The Voice of the DBA  Advice for Business During the last few months, I've seen a few different advice posts that caught me eye. One was Kevin Kelley's blog (and [his book]( on life advice that [I previously wrote about]( I look at the book once a week or two, read 1-2 items, and think about them. I think some of them are geared more for younger people growing into life, but quite a few are still things that I appreciate as learnings or reminders. Recently I ran across another one, [Sam Altman's post]( looking back from a business point of view. I've worked in business for a long time, used to own one, and I tend to enjoy smaller businesses than larger ones. While I enjoyed my time at JD Edwards, I prefer companies with a few hundred people rather than 10,000 or more. This is an interesting list and one that looks at the world more from a startup perspective. Sam Altman was the CEO of OpenAI (maybe still is), and has worked in several small tech companies in his career. Some of these items are things that I've seen or used. Maybe one of the more interesting ones from a business perspective is about incentives. These really do drive and change how people work, and often management gets this wrong by incentivizing one thing, but saying or preaching another. However, the advice I think resonates more with me, as someone who works inside of an organization and with others, are the items that relate to people. One is about cohesive teams, and how they can get things done with both calmness and urgency. I've always wanted to interview with and know who I work with at many companies because having a team I enjoy and work with is powerful. I think [Grant and Ryan]( amazing, and I wish our jobs were a little more closely aligned. Unfortunately, we all tend to work on slightly different things most of the time, and work more solo, but I do appreciate the projects we tackle together. Another is that things that matter are important. You (and I) need a sense of purpose, which is why hard things that matter are easier than easy things that don't. In life and at work. I also think that recruiting is important. I do look for people who I like, appreciate their views, and can work alongside more than those who know all the skills. We can learn from each other and teach each other when people have that potential (in addition to intelligence and drive). That's why I think it's almost always worth hiring good people, even if you don't have a specific use for them. Too often we hire people for a need, and they're way less qualified than others. Maybe my view is summarized well in the last entry: working with great people is one of the best parts of life. We spend so much time at work, so it better be enjoyable. By the way, if you want to find and enjoy great opportunities, learn to be better. Better at your profession, better at learning, better at being part of a team. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Machine learning K-Means applied to SQL Server data]( Daniel Calbimonte from SQLServerCentral This article shows how to use Python to access data in SQL Server and produce a K-Means analysis with a chart. [External Article]( [Using a SQL Tokenizer]( Additional Articles from SimpleTalk Imagine this: you have several directories full of SQL script files, and you need to know where a certain table is used. You’d rather like the context too, so you can check the whole SQL Expression and work out why it is running so slowly. [Blog Post]( From the SQL Server Central Blogs - [SQL Server Blog quarterly review]( Hemantgiri S. Goswami from SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP This SQL Server blog roll talks about the few blog articles I have published in last couple of months. I am certain you would like reading The post SQL... [Blog Post]( From the SQL Server Central Blogs - [2024- Of ‘quiet quitting’ and ‘life in the hallway’]( Diligentdba 46159 from Mala's Data Blog My good friend Gina Menorek shared this article from NY Post on the overwhelming exhaustion among tech workers. The article talks of ‘quiet quitting’ – not quitting our jobs... [SQL Server 2022 Query Performance Tuning]( [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Additional Articles from SQLServerCentral Troubleshoot slow-performing queries and make them run faster. Database administrators and SQL developers are constantly under pressure to provide more speed. This new edition has been redesigned and rewritten from scratch based on the last 15 years of learning, knowledge, and experience accumulated by the author. The book Includes expanded information on using extended events, automatic execution plan correction, and other advanced features now available in SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Which Default Schema I run this code on a SQL Server 2022 instance: CREATE LOGIN JoeDBA WITH PASSWORD = 'DemoP@sswordTh@t1sLong' GO ALTER SERVER ROLE sysadmin ADD MEMBER JoeDBA GO USE sandbox GO CREATE SCHEMA etl GO CREATE TABLE etl.Customer (CustomerID INT, CustomerName VARCHAR(200), LoadDate DATETIME) CREATE TABLE dbo.Customer (CustomerID INT, CustomerName VARCHAR(200), status tinyint) GO INSERT etl.Customer (CustomerID, CustomerName, LoadDate) VALUES (1, 'Acme', GETDATE()), (2, 'Roadrunner, Inc', GETDATE()), (3, 'Coyote Enterprises', GETDATE()) GO INSERT dbo.Customer SELECT c.CustomerID, c.CustomerName, 1 FROM etl.Customer AS c go CREATE USER JoeDBA FOR LOGIN JoeDBA WITH DEFAULT_SCHEMA =[etl] GO This all works fine. I then log into the instance as JoeDBA. I change to the sandbox database and run this: USE sandbox GO SELECT * FROM Customer AS c Which three columns are returned? 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) High Cardinality When we say a column has high cardinality, what do we mean? Answer: Lost of different values, with the counts not mattering Explanation: Cardinality refers to the number of distinct values in a column. High cardinality means lots of unique values. The counts don't matter, as you could have a table with 1.1 million rows and 1.05 million unique values with 1 repeated 50,000 times. Ref: High Cardinality - [SQL_statements)]( [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](. --------------------------------------------------------------- Administration - SQL Server 2014 [FTS Not enabled for DB - but it is working(?)]( - Hello. I have a couple of DBs that say that Full Text Search is not Enabled on the DB - yet it seems to work? FTS catalogs were created by upgrades to SW that use them to run Contains queries via the portal - this seems to work fine. Maybe the data is stale or […] SQL Server 2012 - T-SQL [Parse out filed as date time ???]( - Hi  I have a field with a start and end date (sometimes no end date) . How can I parse this out to have two fields start and end dates? Data looks like ~__2019121407025301GMTOFFSET=-18000~__2019121415164202GMTOFFSET=-18000~ desired out is Start Date 12/14/2019 7:02 AM End Date 12/14/2019 3:16 PM  Thanks  SQL Server 2019 - Administration [SQL 2019 data masking]( - I have setup data masking on several fields and can see that the data is masked using t-sql. I need this masking to follow through to report server. I am not seeing the data masked there. any ideas / suggestions would be greatly appreciated SQL Server 2019 - Development [Query Tuning]( - I have a table that has 5.5 million records. The query used by a report filters records based on a date column in the table, and currently it returns all records in the table. The query gives result in 100 seconds. I need to make it run faster. Query ----- Select column1, column2... column18 From […] [Dyanamic SQL to call sproc with input params and an output param]( - All, I have the following which works perfectly: DECLARE @ProcessName VARCHAR(100) = 'ETL_InitialLoad' ,@TableName SYSNAME = 'dbo.tableA' ,@id NVARCHAR(30) IF NOT EXISTS (SELECT * FROM dbo.ETL_Log WHERE ProcessName = @ProcessName AND TableName = @TableName AND ProcessEndTime IS NULL) BEGIN EXEC [dbo].[ETL_InsertLog] @ProcessName = @ProcessName, @TableName = @TableName, @id = @id OUTPUT; END SELECT @id  […] Reporting Services [How to use pagination for huge reports]( - Hi all, I created a SSRS report that supposed to load about 600k records. In order to minimize loading time, I used pagination, by 50 records each page. However, when I refresh it in a Preview mode it throws "out of memory" exception. I expected that if I use pagination it will only load 1st […] General [Task solution help request]( - Couldy You help me with this task, please? You are working with the library books database. The Books table has the columns id, name, year. The library has new books whose information is stored in another table called "New", however they do not have a year column.  Write a query to select the books from both tables, Books and […] Analysis Services [SSAS tabular account impersonation]( - Hello, I'll try my best to summarize the problem and I hope someone can offer me a hand with this. The Enviroment: 2 Domains (let's call them domain_1 and domain_2) Server1 (it is joined to domain_1) This server has SQL Server and SSAS SSAS has a domain account set up on the service Domains have […] [Comparison of rolap and molap in the tubular or multidimensional]( - I want to write a comparison article on ROLAP and MOLAP, and I have installed SSAS. During the installation process, there was a Tabular section and a Multidimensional section that I needed to choose from. As far as I knew, Tabular is specifically for ROLAP, and Multidimensional is tailored for MOLAP. However, it seems that […] Integration Services [On Error Event handler in For Each Loop Container]( - I have a For Each Loop Container that iterates through a list of servers in my environment. Usually it is used to gather information from each of the servers. Inevitably there are a few errors. But my error handler does not write the name of the Server at which the error occurred. Here is my […] [Visual studio 2019 hangs when open excel data source]( - I had Visual studio 2019 installed on windows 10. I have a SSIS package that has been working when I run it in visual studio. But recently when I open the ssis package, and open the excel data source to edit, it freeze there forever, then visual studio shows busy. I have to hard stop […] Anything that is NOT about SQL! [SSC Fantasy Football 2024]( - Placeholder for 2024. Prized: $100 $75 $50  SQL Server 2022 - Development [Basic query performance]( - Hi, I have this pretty basic query that is starting to perform slowly. It returns over 22m records in about 5 minutes. I know its difficult without table defs but any ideas on what I can try to make this return faster?  SELECT [e].[eligibility_id] FROM [dbo].[eligibility] AS [e] -- Table is updated hourly with […] [Need Help Uninstalling SQL Server 2019 for SQL Server 2022 Installation]( - hello everyone, I'm currently facing an issue with uninstalling SQL Server 2019 Developer from my computer. The goal is to make way for the installation of SQL Server 2022 Developer, and I want to avoid having two instances of SQL Server on my PC. Despite attempting to uninstall SQL Server 2019, it seems to persist […] [Freezing of multiple databases using VDI snapshot backup command.]( - Transact-SQL snapshot backup for SQL Server 2022: Suspend all user databases on the server for a snapshot backup: ALTER SERVER CONFIGURATION SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON; BACKUP SERVER TO DISK = 'd:\temp\db.bkm' WITH METADATA_ONLY, FORMAT; METADATA_ONLY is synonymous with SNAPSHOT. The Virtual Device Interface (VDI) uses SNAPSHOT. VDI Snapshot backup: Backup statement: BACKUP DATABASE {database_name} TO […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -