Stairway to Server Management Objects (SMO) Level 5: Moving to the SQLServer Module (2024-01-17)

SQLServerCentral Newsletter for January 17, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Server Management Objects (SMO) Level 5: Moving to the SQLServer Module]( - [Two-Dimensional Interval Packing Challenge]( - [Kickstart the year with our free Livestream! Navigating the database landscape in 2024]( - [From the SQL Server Central Blogs - Counting cars with Azure’s Computer Vision service]( - [From the SQL Server Central Blogs - Finding Foreign Keys in Azure SQL DB]( - [Expert Performance Indexing in Azure SQL and SQL Server 2022]( Question of the Day - [Adding a PK - II]( The Voice of the DBA  Learning NoSQL It's a good idea to learn more about different technologies. I've been amazed throughout my career, and even now as someone working for a software vendor, how many different technologies I run into. I'm also amazed at how little bits of knowledge comes in handy, either to help me understand a problem or to help guide others with a recommendation of how to proceed. Many of us have experience with relational databases and tabular data. We are comfortable with it, even if we might struggle with a [packing problem]( or using a [tally table](. However, do you understand how a NoSQL database might be different? Can you even name a few types of NoSQL databases? I find it valuable to know a bit and experiment a bit, as your devs may ask you if it's suitable, or even how to work with one of these databases. They might even start using one and expect you can take responsibility for managing it, backing it up, securing it, and more. I wonder how many of you know MongoDB? What type of database it is or how it stores data? What about Cassandra? Neo-4J? CosmosDB? I haven't done much work with any of these, but I've learned a bit about them. I even spent six months digging into graph databases and learning how they work, how to model data, and how to query them. Part of this was SQL Server's inclusion of graph structures, but part of this was a customer looking to add graph capabilities to their application. I learned a lot, and even [delivered a few presentations]( on how they work. I'm not an expert, but I can have a rational, reasonable, informed debate about the good and bad things with graph databases. Enough to help provide an opinion on whether they might suit a problem. They solve some complex things well and I think they might be a good platform to add to some applications. I wouldn't get rid of the RDBMS, but supplement it. This year I'm going to dig more into MongoDB and Cassandra. We have more customers asking about these platforms and we're working on Flyway support for both of them. If you have any thoughts on NoSQL, or experiences, or even want to write a bit about them, let me know. I'm always looking to learn more, and I always need good authors. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Server Management Objects (SMO) Level 5: Moving to the SQLServer Module]( laertesqldba from SQLServerCentral In this level you will learn about the SQLServer PowerShell module that ships with modern versions of SSMS. [External Article]( [Two-Dimensional Interval Packing Challenge]( Additional Articles from Redgate Packing intervals is a classic SQL task that involves packing groups of intersecting intervals to their respective continuous intervals. In mathematics, an interval is the subset of all values of a given type, e.g., integer numbers, between some low value and some high value. [External Article]( [Kickstart the year with our free Livestream! Navigating the database landscape in 2024]( Press Release from Redgate The key finding from our annual ‘State Of’ survey is that there’s a need for skill diversification to keep up with the pace of technological advances in IT world. How will this skills gap affect you? Whether you’re just starting out in your career, you’re a seasoned data professional or you’re a senior IT leader wanting to stay ahead of business growth, join our free livestream on January 23rd. Redgaters Steve Jones, Ryan Booz and Beca Parker will introduce key findings from the survey and offer their thoughts on the big changes coming in 2024 and what you can do to thrive in this changing landscape. [Blog Post]( From the SQL Server Central Blogs - [Counting cars with Azure’s Computer Vision service]( Daniel Janik from Confessions of a Microsoft Addict When I was 11 I got my first computer, an 8088 with 4.5 mhz. I didn’t know much about computers at the time but I knew it was cool.... [Blog Post]( From the SQL Server Central Blogs - [Finding Foreign Keys in Azure SQL DB]( hellosqlkitty from SQLKitty There are plenty of times I’m called upon to fix data. To do this, I must know what dependencies are in the database. Foreign keys are a crucial aspect... [Expert Performance Indexing in Azure SQL and SQL Server 2022]( [Expert Performance Indexing in Azure SQL and SQL Server 2022]( Site Owners from SQLServerCentral Take a deep dive into perhaps the single most important facet of query performance―indexes―and how to best use them. Newly updated for SQL Server 2022 and Azure SQL, this fourth edition includes new guidance and features related to columnstore indexes, improved and consolidated content on Query Store, deeper content around Intelligent Query Processing, and other […]   Question of the Day Today's question (by Steve Jones - SSC Editor):  Adding a PK - II I run this code on SQL Server 2019. What happens? CREATE TABLE SalesTracking ( SalesDate DATETIME , SalesPersonID INT , CustomerID INT NOT null , PONumber VARCHAR(80) , paid bit ) GO CREATE CLUSTERED INDEX SalesTrackingCDX ON dbo.SalesTracking (SalesDate) GO ALTER TABLE dbo.SalesTracking ADD CONSTRAINT SalesTrackingPK PRIMARY KEY (CustomerID, SalesPersonID) GO 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) Creating Tables without a Schema - II I run this code: 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 USER JoeDBA FOR LOGIN JoeDBA WITH DEFAULT_SCHEMA =[etl] GO Then I log in as JoeDBA and run this: USE sandbox GO CREATE TABLE ANewTable (newid INT) What table is created? Answer: dbo.ANewTable Explanation: With a sysadmin, the default schema is always dbo. Ref: ALTER USER - [ [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 [How to optimization of a stored proc]( - Hi All, Need some help on optimizing a stored proc. I see some of the update statements written multiple times. Instead of writing multiple updates , can we re-write in one single statement which might avoid reading same table multiple times. This stored proc is doing a lot of logical reads almost like 76TB of […] SQL Server 2017 - Development [Grouping based on chronological periods]( - Hello, I have a table Customers that contains the current value and another table Customers_History contains the history values. The customers have more that 10 fields AS Bellow a sample of data : CREATE TABLE #CUSTOMERS( ID int primary key identity, First_Name varchar(255), Last_Name varchar(255), Nick_name varchar(255), Email varchar(255), Date_Bithday date, Gender varchar(1), Is_Married int, […] [Null value is eliminated by an aggregate or other SET operation]( - Should I see this warning when I have ISNULL check at SUM function and changing NULLs to 0?  Thank you SQL Server 2012 - T-SQL [parse JSON formatted text into M3U Playlist]( - sorry.. solved.. [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 [ReportViewerControl.exportReport opening in new Window/Tab]( - Hello, I am using the ReportViewer.aspx page to display a SSRS Report. When exporting the report to any format(CSV,PDF,Excel, etc.) it uses the ReportViewerControl.exportReport function which has the following code: window.open(this.ExportUrlBase+encodeURIComponent(n),"_blank") What I would like to do is stop a new window/tab from being created, so instead of "_blank", "_self" could be used. Any idea […] Reporting Services [copy or duplicate a folder in SSRS web portal]( - Is there a way to copy a folder in SSRS 2019 server web portal to another folder, I want to make a copy of backup for temporary keep. For example I have a folder called myReports which has 20 reports in it, I want to duplicate the folder and all the reports in it to […] 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 […] 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 […] SQL Server 2022 - Administration [Scheduling AWS RDS instance resizing]( - I know that it is possible to use the AWS Scheduler to stop and start RDS instances. For my purposes we just want to resize it e.g. 16 to 4 processors, so it can still work in low usage periods but will not cost so much. Has anyone ever done anything like this? We do […] SQL Server 2022 - Development [Aggregate date series]( - I have a an EmployeeTable with data for each day. I need to aggregate that into a table with From and Tom date.  This is a simplified view of the source data: select * from (values (1, 1, 20240101), (1, 1, 20240102), (1, 2, 20240103), (1, 2, 20240104), (1, 1, 20240105)) Employee(EmployeeId, EmploymentTypeId, DateId) […] [How to query selected multiple rows into multiple colums]( - How to display datas using query data from multiple specific rows in one table sql into multiple columns? For example, insert red data into column1, insert blue data into column2, insert green data into column3, etc. I've used these code : SELECT um_value FROM wp_um_metadata WHERE um_key='first_name' But the result I only got one column. […] [Column Name Is Different in Power BI Than SQL View]( - Hi all, Using Snowflake. When I Describe my View the first columns shows as Nielsen Market Name (KUSA Adjusted) VARCHAR (10000) I connected to the View from Power BI Desktop (PBID) However, in PBID, the name of the column is showing as Nielsen Market Name (USA Adjusted) - the "K" has been dropped from the […] [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 […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -