We’re reading in some JSON files in Azure Data Factory (ADF), for example for a REST API. We’re storing the data in a relational table (SQL Server, Azure SQL DB…). The data volume is low, so we’re going to use a Copy Data activity in a pipeline, rather than a mapping data flow (or whatever […]
Tag: sql server
Convert Local Datetime to UTC (pre-SQL Server 2016)
Recently I was working with a client who was still on SQL Server 2012. Support issues aside, there are some very useful T-SQL functions/clauses who didn’t exist in 2012, but were introduced in a later version. One of them is AT TIME ZONE, which you can use the convert dates between time zones, as demonstrated […]
T-SQL Tuesday #143 – Short code examples
It’s this time of the month again! T-SQL Tuesday is a monthly blogging party on the second Tuesday. This month’s topic is “What are your go to handy short scripts?” brought to you by John McCormack. What are those little short bits of code that you can’t live without? I’m talking about little snippets that […]
The GREATEST new T-SQL Function
If the title isn’t obvious enough, this blog post is about the new function GREATEST, and it’s sibling LEAST. But wait, how come there’s a new T-SQL function? There isn’t a new release of SQL Server! That’s right, those two new functions are released in Azure SQL DB, Azure SQL Managed Instance and Azure Synapse […]
#TSQL2sday 136 – Your Favorite Data Type
It’s T-SQL Tuesday time again, and this month’s honor to host the blog party goes to Brent Ozar. The topic is data types: Mad about money? Feverish about float? Tell us what you use your favorite data type for, and what people need to know before they get started using it. Data types have so many interesting […]
Publish Database Fails – Script File could not be found
A small blog post for anyone who stumbles across this issue (and for my future self, should I forget the solution). I was working on some tables in SSDT and I tried to publish changes to the database on my local machine. I got a build error because I created a clustered index on the […]
Exporting Environment Variables out of the SSIS Catalog
Sometimes when you’ve created a SSIS catalog, along with folders, environments and environment variables, you want this content on another server. Unfortunately, you can only script out these objects in SSMS at the moment you’re creating them (thus right before you clikc ‘OK’ to confirm). Once the objects are created, you can no longer script […]
Converting a Datetime to UTC
I was in a need of converting some datetime values (of which I know the actual timezone) to UTC dates. A quick Google search showed me that most results on the first page were simply wrong. Most of them used this trick: Seems nifty, but they forgot about daylight savings time. If I run the […]
Optimize for Unknown for Inline Table-Valued Functions
I had a curious performance issue today. An inline table-valued function (iTVF) was performing poorly for some parameter sets, and quite fast for other parameter values. In short, this one is fast: This one is slow: Turns out SQL Server used a plan with a hash join in the fast query, and a nested loop […]
Quickly Editing Data in SSMS
WARNING: do not do this in production. A quick blog post on a SSMS trick: how to quickly edit data. Sometimes you directly want to manipulate data without having to write a SQL statement. For example, in the development environment you want to modify a value in a column to test out a scenario, or […]