Problems & Concerns

Would you ever need a dataset, was returned from a SQL routine, contains the money as well as its wording? → Yes, there is definitely a need in the Invoice report sometime.

Google it, and you’d find out a lot of suggestions that showing with all messy SQL code as SQL function, but it works really!? What is the logic behind the scene?

Let’s explore it in this article.

Image for post
Image for post
mssql-num2words

Solution

With raw SQL, it is 100% possible to convert a number to words in a paticular language. All we need is to have the algorithm.

Let’s pick the English one —…

Q1: What is it?

Image for post
Image for post
columnstore-indexes-overview

Beginning with the version of 2016 (13.x) SP1, the “new” feature called Columnstore Indexes has been added to SQL Server all editions (before that, this feature was only available in the editions: Enterprise, Developer) to enhance the analytical querying performance and to optimize the way of storing indexes.

Columnstore index is the new type of the Indexes introducing in SQL Server, so we are now having:

  • Rowstore index: The tranditional indexes which we’re normally using. …

Problems\Concerns

When you don’t know where to get the data from?
When you have to wait for data provided by another team but you couldn’t?
When you’re trying to go typing the mock data manually — it would hences take too much time to do?

A mock data generated tool comes to be a BIG need.

Image for post
Image for post
tsql-to-generate-mock-fake-data

Let’s see how TSQL can do this job!

Solution

In this article, we will see how to build the SQL functions to generate random data for the most common data type such as: number, date, character or series of characters, word or sentence…

Now let’s go…

Prolems\Concerns

There are many many ways to play with FTP download, but when coming to TSQL level, it’s kind of “a tiny wave” in the brain that how to do it natively.

No, no native option existing in TSQL. But, ways to do it by TSQL code, there are many ones:

  • WinSCP commands
  • Batch script (cmd, powershell)
  • Python code?

Let’s exploring them…

Image for post
Image for post
T-sql-and-FTP-files?

P/s: I won’t talk about the option using CLR functions as I don’t like it much.

Solution

In this post, to make it easy, we will use the simplest FTP server — that you can quickly setup with using…

Problems\Concerns

We all know that R language is great in SQL Server Machine Learning service. Problem comes with “I don’t know R, but Python!” — “No worries, that’s supported as well”, MS said.

Yes, that’s right, and Python’s power can be achieved by the extended stored procedure named: sp_execute_external_script

Image for post
Image for post
SQL and Python?

Let’s say “Hello World!” and prepare the “show”!

Solution

Before you start:

Ensure that marchine learning service of Python has been already installed along with SQL Server instance:

Problems\Concerns

As normally in SQL Server, it will allow us to set up the Database Mail to be sending emails very easily. And to make use it, we then execute sp_send_dbmail stored procedure. But, is there anyway around?

“YES, there is!” — As the previous article about How to call API by using TSQL?, we hence can use it to call SendGrid API to send email.

Image for post
Image for post
TSQL loves SendGrid Email API

Go Playing…!

Prepare your “gun” to shoot emails with below wrapper:

https://github.com/datnguye/SQL-Server/blob/master/web-call/API.sql

Sample usage to POST SendGrid Email API:

EXEC dbo.API 
@Method = ‘POST’,
@Url = 'https://api.sendgrid.com/v3/mail/send',
@ContentType = 'application/json',
@Authorization = 'Bearer your-api-key',
@BodyData = 'your-body-data'

Problems/Concerns

Calling an API using T-SQL, is it possible?
The answer is completely YES.

But, why to call API at the database level?
Well, it could be to build a data crawler, to import data which the source is provided as API only, to be any reasons under requirements you would have.

Image for post
Image for post

Let’s go!

In this article, we will assume you’re all got a know of API with all kinds of its methods

Solution

We’re going to use a C/C++ librabry supported in Windows named WinHttp which version of 5.1 (v5.0 is no longer supported), and to use some extended…

Dat Nguyen

You know who I am ;)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store