Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Archive to the Lake

Microsoft Fabric was announced at Build in May 2023. This is the next evolution of data warehousing from Microsoft, folding in Synapse and a number of other technologies to create a simpler location for storing and analyzing data. We've published some articles on the platform and there's a great presentation from Mr. Paul Andrew on Linked In. It's worth listening to, even in the background. Paul has a nice style and a great voice.

Part of this platform is OneLake. This is a data lake for your org, just one of them, and while it's able to store data in many formats, it's mainly optimized to read tabular data in the delta parquet format. This is essentially a compressed text file that allows for some transactional changes to the original data in parquet format.

I don't do a lot of work with text files, and I've been suspicious of using lots of CSV or other text files in a warehouse environment, which is what a lot of people were advocating a few years ago. Exporting tables into lots of files split on some field, like date, while easy, didn't seem like the best way to move data for reporting.

Fabric, however, is optimized for reading delta files. A few presentations I've seen from people have advocated for exporting your data from SQL Server (or other platforms) into parquet. While I don't know there's a native way to do this (yet), I suspect one is coming. I've seen lots of articles (one, two, three, more) about how to do this now. We also have SQL Server able to read these files with external file formats already, so I'm sure we'll have an easy way to write them soon.

Many of us struggle with large systems, especially with query performance. We'd love to archive off data, though that's often impractical. However, in an amazing, wonderful world, maybe we'll get lots of people doing this, writing about it in the media, and our bosses will start to let us establish an archive in the lake. We could move some data there, especially old, unchanging data. We could delete that from source systems. We could have all our users happy.

I don't know if I see lots of data moving to the lake, but I certainly expect lots of it to be copied. If you haven't thought about archives, data lakes, and text formats, it's an area that seems to have a lot of growth. Perhaps it's of interest to you and you might find a new career.

Or maybe you just hope it gets widely adopted to relieve some pressure on your OLTP server.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents
SQLServerCentral Article

Combine DATE and TIME to DATETIME2 in SQL Server

Jeff Moden from SQLServerCentral

The most common method on the internet for combining DATE and TIME columns in SQL Server is incorrect. This article demonstrates why that and other methods are incorrect and two lesser known high performance methods that produce correct results even for the "edge cases".

External Article

Using Power Query to Combine Data from Multiple Sheets in Excel

Additional Articles from MSSQLTips.com

This article employs Power Query features to combine queries and implement requirements in different listed scenarios.

External Article

7 Essential Factors for a Successful Cloud Migration: A Non-Technical Guide

Additional Articles from Redgate

Are you looking to modernize and migrate your data but have questions about the complex organizational challenges? We recently hosted the webinar: Overcoming Organizational Challenges in Data Modernization and Cloud Migration: A Guide for Senior IT Leaders. This blog post summarizes the conversation into 7 key points.

From the SQL Server Central Blogs - Azure DevOps: Enterprise Power BI report deployment with connections to Shared datasets

Rayis Imayev from Data Adventures

(2023-July-15) Let's say you have a collection of Power BI .pbix files stored in a git-based source control system (GitHub, Azure DevOps, or any other system). Among these files, one...

Blog Post

From the SQL Server Central Blogs - Enhancements I’d Like to See in the Power BI Treemap Visual

Meagan Longoria from Data Savvy

I recently created a treemap in Power BI for a Workout Wednesday challenge. Originally, I had set out to make a different treemap, but I ran into some limitations...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Checking a SchemaBound View

I have this code in SQL Server 2019: 
CREATE TABLE moduletest(
   col1 int, col2 int, col3 int)
GO
CREATE OR ALTER VIEW dbo.GetModuleTest WITH SCHEMABINDING
AS
  SELECT col1, col2
    FROM dbo.moduletest
 GO
I now execute this: 
EXEC sp_refreshsqlmodule 'dbo.GetModuleTest'
GO
EXEC sp_refreshview 'dbo.GetModuleTest'
GO
What is 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)

More Left Joins

I have a number of tables with this data:

CREATE TABLE CustomerLeft (CustomerID INT, CustomerName VARCHAR(20))
GO
CREATE TABLE CustomerContact (CustomerID INT, CustomerEmail VARCHAR(100))
GO
CREATE TABLE EmailDomain (CustomerEmail VARCHAR(100), domain VARCHAR(20))
GO
INSERT dbo.CustomerLeft (CustomerID, CustomerName) VALUES (1, 'Steve'), (2, 'Andy'), (3, 'Brian')
GO
INSERT dbo.CustomerContact
  (CustomerID, CustomerEmail)
VALUES
  (1, 'steve.jones@red-gate.com'), (2, 'awarren@sqlservercentral.com')
GO
INSERT dbo.EmailDomain (CustomerEmail, domain) VALUES ('steve.jones@red-gate.com', 'red-gate.com')
GO

If I run this query, how many rows are returned?

SELECT *
 FROM dbo.CustomerLeft AS cl
 LEFT JOIN dbo.CustomerContact AS cc ON cc.CustomerID = cl.CustomerID
 LEFT JOIN dbo.EmailDomain AS ed ON ed.CustomerEmail = cc.CustomerEmail

Answer: 3

Explanation: The two left joins mean all rows from the first table are returned and then the matches from the other tables. Ref: Joins - https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins?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 2016 - Development and T-SQL
How to identify changed values from a history table? - We have several tables with system_versioning set to on. There is a requirement now to produce a solution (function / stored procedure) to return a list of changed columns of a given table within a range of days. Below is an example of one of the tables - customer_sites and a bit of data: /**** […]
Run Stored Procedure on the first Monday if each month of Tues if Mon Holiday - Hello, I need to setup a Stored Procedure to run on the first Monday of each month that is not a holiday.  If the first Monday of the month is a holiday, than I need the PROC to run on Tuesday.  Via Scheduled Jobs, how is this possible?  Can someone help with a query on […]
SQL Server 2019 - Administration
repeated login failures. - Do repeated login failures into SQL affect performance.  Surely some resources are used with each attempted ack & nack?  Certainly log writes.  I have careless app owners who repeatedly fall foul of my attempts to stop this happening as their apps try access every 10 seconds or so.  They failures fill my logs & I […]
SQL SERVER AUDIT LOG can scheduled job be placed and get file in .csv format - Hi Team, SQL SERVER AUDIT LOG can scheduled job be placed and get file in .csv format we can see the audit logs using view logs rather than is there any way we can set the audit to run in frequency for every one hour and schedule it in a job where we can get […]
Deadlock - implicit transaction - Hi, We are experiencing deadlocks. The process not being the victim is doing an update by stored procedure which has begin tran,  commit tran. However the transactionname in the deadlock graph is implicit_transactions. Could this be potentially causing the deadlock issue and from the application implicit_transaction is on?  
File does not close: VerifyShareWriteAccess.txt - Hi Folks. We have two  SQL Server 2019 Enterprise in an High Availability Group (always on).  The OS is Windows server 2019. For the witness of the failover cluster we use a file share.  The cluster itself runs perfect. But we have some troubles with the file VerifyShareWriteAccess.txt. The cluster service opens the file (read, […]
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. - Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided.  see error below: I'm trying to login into a sql server on another domain (DEV).  I run a command to launch SSMS and then login to a DEV server as follows: %windir%\system32\RUNAS.exe /smartcard /user:ABC@dev.com /netonly "C:\Program Files (x86)\Microsoft […]
Why would user account authentication issues arise in Azure? - Through a series of events too long to go into here, last year I was involved in helping to move a test SQL Server 2019 database from on-premises, to a VM in Azure. However, after it was migrated, they no longer needed me and that was the last I heard of or knew about it, […]
SQL Server 2019 - Development
Using Dynamic SQL to execute Select from Openrowset with JSON - I have some code which will load in and process a JSON file using Select..from Openrowset as follows: Declare @JSON varchar(max) SELECT @JSON = BulkColumn FROM OPENROWSET (BULK 'C:\Users\User1\DR\staging\returns\RET02022203.JSON', SINGLE_CLOB) as j select @JSON i tried to turn this into Dynamic SQL  so that I can pass in the path to the JSON file as […]
xp_cmdshell problem - Hi, i'm trying to run this command from within the SQL but i get this error 'C:\Program' is not recognized as an internal or external command, operable program or batch file. here is my code DECLARE @CONVERT_HTML_TO_PDF VARCHAR(4000) SET @CONVERT_HTML_TO_PDF = '"C:\Program Files\Microsoft SQL Server\MSSQL15.PRI\MSSQL\monitor_binaries\wkhtmltopdf.exe" "c:\new folder\1.html" "c:\new folder\1.pdf"' EXEC xp_cmdshell @CONVERT_HTML_TO_PDF what is wrong […]
Best way to handle incremental duplicate data in SQL Server? - I am processing .csv files daily which has persons data. Every person has a unique ID. My table has FileID and PersonUniqueID columns; it is working fine and saved to parsed table, i.e. with duplicate rows. Now would like to copy the unique data to new tables which is used by other applications. I can […]
CosmosDB
To change the maximum throughput of an Azure Cosmos DB for NoSQL container - Hello,   I have a question, and please advise. When I wish to change the maximum throughput of an Azure Cosmos DB for NoSQL container from 4000 RU/s to 5000 RU/s, would that be sufficient if I use command: az cosmosdb sql container throughput update command --max-throughput ‘5000’ ???   Tanks in advanced  
Reporting Services
Custom Authentication SSRS 2019 - When trying to setup custom authentication on ssrs 2019 getting "ERR_CONNECTION_REFUSED " and this in the logs   ERROR: Error loading configuration file: The configuration file contains an element that is not valid. The #text element is not a configuration file element. library!DefaultDomain!13d0!08/02/2023-09:54:52:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: , Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration […]
SSRS Integration with Power BI - I am trying to integrate SSRS 2022 with Power BI.  One of the steps is to go into Report Server Configuration Manager and go to the Power BI integration tab;  however, I am not seeing a Power BI integration tab.  Is there something special I need to do or install to see that tab?   […]
Analysis Services
Date Difference of current record and its relative record using MDX - Dear All, I have a some rows where it points to another row in same table based on RelativeId column value. I need to find the difference of these two records date values using MDX. for ex.  sample table structure with some values ID - HasRelative - Date - RelativeId 1 - False - 2023-01-01 […]
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -