SQLServerCentral.com Community Update for January 17, 2024

Problems displaying this newsletter? View online.

Featured Contents

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.

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.

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.

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....

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

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 - https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-user-transact-sql?view=sql-server-ver16

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 […]

This email has been sent to [email protected]. To be removed from this list, please click here. If you have any problems leaving the list, please contact the [email protected]. This newsletter was sent to you because you signed up at SQLServerCentral.com.