A Fine Slice Of SQL Server
Then, let’s say the requirements are as follows:
1. No values that are either empty or only spaces
2. No leading spaces
3. No trailing spaces
4. Allow NULL if column allows NULLLet’s look at how we could implement all of these independently, as there certainly are cases where you may wish to allow any or all of the situations in a column.
Click through for the scripts, as well as a time comparison to see how much overhead you’re adding.
Comments closedSuppose we want to build a real-time pipeline to flag fraudulent credit card transactions. Ideally, we want to identify and deny a fraudulent transaction as soon as the culprit has swiped his/her credit card. However, we don’t want to delay legitimate transactions as that would annoy customers. This leads to a strict upper bound on the end-to-end processing latency of our pipeline. Given that there are other delays in transit, the pipeline must process each transaction within 10-20 ms.
Let’s try to build this pipeline in Structured Streaming. Assume that we have a user-defined function “isPaymentFlagged” that can identify the fraudulent transactions. To minimize the latency, we’ll use a 0 second processing time trigger indicating that Spark should start each micro batch as fast as it can with no delays.
They also explain how this newer model differs from the prior model of collecting events in microbatches.
Comments closedMahesh Chand demonstrates how Scala can use lazy evaluation to reduce memory requirements:
In this blog, we will talk about lazy evaluation in Scala. How we can add efficiency to our application?
Efficiency is achieved not just by running things faster, but by avoiding things that shouldn’t be done in the first place.
In functional programming, lazy evaluation means efficiency. Laziness lets us separate the description of an expression from the evaluation of that expression. This gives us a powerful ability—we may choose to describe a “larger” expression than we need, and then evaluate only a portion of it. There are many ways to achieve lazy evaluation in Scala i.e using lazy keyword, views, streams etc.
The fastest operation is the one that doesn’t have to run at all.
Comments closedThe documentation for how to specify build steps in YAML is still a work in progress. In summary, the current procedure is to visit the VSTS Tasks repo on GitHub, open the folder for the task your are interested in, and take a look at the
task.json.In our case, the first task we need is MSBuild, to build the database project. Looking inside task.json, we can see that the name of the task we need is
MSBuild, and that there are a huge number of availableinputswe can use to configure the task;solutionto specify the project or solution to build,platform,configuration, and many more. In our case, we’ll just specify the path to our.sqlprojfile and letmsbuildtake care of the rest.
Gavin walks us through the entire process step by step, though as he notes, some of these steps are liable to change as the VSTS team continues implementation.
Comments closedIn ADF V2 the integration runtime is responsible for providing the compute infrastructure that carries out data movement between data stores. A self-hosted integration runtime is an on-premise version of the integration runtime that is able to perform copy activities to and from on-premise data stores.
When we configure a self-hosted integration runtime the data factory service, that sits in Azure, will orchestrate the nodes that make up the integration runtime through the use of Azure Service Bus meaning our nodes that are hosted on-prem are performing all of our data movement and connecting to our on-premises data sources while being triggered by our data factory pipelines that are hosted in the cloud. A self-hosted integration runtime can have multiple nodes associated with it, which not only caters for high availability but also gives an additional performance benefit as ADF will use all of the available nodes to perform processing.
Read on for the scripts and full process.
Comments closedMax Vernon explains why it’s a bad idea to let your SQL Server be Internet-accessible:
First, it might be helpful to understand why someone might want to connect to their SQL Server via the Internet. Consider any of the following likely scenarios:
- There is a client application or website hosted in a different data-center that needs access to the data stored in the SQL Server.
- You need to provide geographical redundancy between several SQL Servers located in different countries that are only connected via the Internet.
- You’re doing development on a hosted SQL Server and don’t understand the implications of having SQL Server exposed.
The typical way you might expose your SQ Server to the Internet when the server is behind a router or firewall is to open an incoming TCP port at the router or firewall directed at the SQL Server on port 1433. If you do this, you will very quickly start seeing incoming connection attempts from computers on the Internet. These attempts will try to login, typically via the “sa” account, continuously trying passwords.
It’s best to avoid this mess altogether, though if you do need to expose your SQL Server to the Internet, Max has tips on how to mitigate some of that risk.
Comments closedKellyn Pot’vin-Gorman explains the DM_DB_LOG_STATS dynamic management function in SQL Server 2017:
This is an addition to SQL Server 2017 and available in Azure Database/Azure Data Warehouse to simplify and synchronize how we query information about log statistics, not just backups. The added benefit of this is that it only requires the VIEW DATABASE STATE privilege to utilize it, which eases the demand on anyone who’s tried to grant rights to view backup information to non-DBA personnel in previous releases. With this DMV, you can monitor, alert and kick off jobs to manage the transaction log backups.
It does require the database_id to be passed to it to provide results, but who keeps that around? Much easier to just do a Cross Apply to sys.databases and make your life simple.
Click through to see what’s included on this useful DMV.
Comments closedDan Blank shows how to use the PIVOT operator in SQL Server:
I was recently approached by my firms Marketing Manager with a request for some information. She wanted to know “Which departments have our top clients never done any work for?”. For some clarity, I work in a law firm with 11 departments. The request seems pretty straightforward at first. Then once I got thinking about how the output of this report would be presented it made me reconsider quite how simple a request this was. She handed me a drawing with her vision for the output. What she wanted was :
Client’s names down the left,
List of Departments across the top,
Ticks and crosses at the intersection to show whether they had or had not done work for them.
You can make a good argument that presentation mechanics like this are meant for a different tool (a presentation layer) but it’s useful to know how to pivot and unpivot data within T-SQL for more reasons than just presentation.
Comments closedDave Mason points out something quite useful about TRIM in SQL Server 2017:
Now I can tidy things up and remove both leading and trailing spaces with a single call to TRIM():
SELECT TRIM(b.foo) FROM dbo.bar b;On the surface, this may not seem like that big of a deal. And I would tend to agree. But, in this example, it saves a few key strokes and makes the code slightly more readable. And it is nice for T-SQL to finally have a function that has been around in other languages for far longer than I’ve been writing code for a living.
But Wait, There’s More!
Click through for that more. This makes TRIM a lot more useful, so go check it out.
Comments closedRobin Moffatt has an example where he enriches streaming CSV data with information stored in MySQL:
This is a continuous query that executes in the background until explicitly terminated by the user. In effect, these are stream processing applications, and all we need to create them is SQL! Here all we’ve done is an enrichment (joining two sets of data), but we could easily add predicates to the data (simply include a
WHEREclause), or even aggregations.
You can see which queries are running with the
SHOW QUERIES;statement. All queries will pause if the KSQL server stops, and restart automagically when the KSQL server starts again.
The
DESCRIBE EXTENDEDcommand can be used to see information about the derived stream such as the one created above. As well as simply the columns involved, we can see information about the underlying topic, and run-time stats such as the number of messages processed and the timestamp of the most recent one.
It’s pretty easy to do; click through to see just how easy.
Comments closed