Languages and coding

Timothy Smith
Our CSV file returns with the Export-CSV function that catches the output from the select

Working with PowerShell’s Invoke-SqlCmd

March 18, 2019 by

PowerShell features many one-line commands for working with SQL Server, one of which is Invoke-SqlCmd. This tool can be useful in many development contexts where we need to quickly execute scripts or test code and it helps to know some of the parameters we’ll often use. In addition, because we may want a custom script using some of the underlying .NET objects, we’ll look at an alternative where we will be able to create a custom PowerShell script that connects to SQL Server in order to run commands. The latter can be useful because one-line scripts have a tendency to change in future versions of PowerShell and working with the library directly can sometimes avoid this challenge.

Read more »
Rajendra Gupta
Execcute and monitor status of SQL Server Python SSIS package

Using Python SQL scripts for Importing Data from Compressed files

March 4, 2019 by

Using Python SQL scripts is a powerful technical combination to help developers and database administrators to do data analytics activities. Python provides many useful modules to perform data computation and processing of data efficiently. We can run Python scripts starting from SQL Server 2017. We can create the ETL solutions to extract data from various sources and insert into SQL Server.

Read more »
Rajendra Gupta

Export SQL Server FILESTREAM Objects with PowerShell and SSIS

February 14, 2019 by

In this series of articles on SQL Server FILESTREAM (see TOC at bottom), we explored various ways to store unstructured data in the file system with the metadata in SQL Server tables. If we have a large number of objects in the file system, it is advisable to use the fast disk for storage purpose. It is faster and provides better IO in comparison with the traditional file system.

Rajendra Gupta

Power BI Desktop and Python; like Peanut Butter and Chocolate

February 13, 2019 by

Power BI Desktop helps to visualize complex data with the help of inbuilt and custom visualizations. It allows integrating data from the various data sources and preparing visualization from it.

Microsoft recently integrated the Python programming language in the Power BI. Python is a powerful object-oriented language and is used by the data analyst and scientist to analyze complex datasets. We can now use the Python as a preview feature in the Power BI August 2018 release onwards. We can now use Python scripts to generate custom visualization. The combination of these technologies is akin to 1 + 1 = 3 or like the famous ad for Reese’s Peanut butter cups, when chocolate was introduced to Peanut Butter 😉

Read more »
Ben Richardson

Python in SQL Server: The Basics

February 13, 2019 by

With the introduction of SQL Machine Learning Services, it is now possible to run Python Scripts from any SQL Server client such as SQL Server Management Studio. In addition to directly running the Python Scripts on SQL Server Clients, you can write Python Code on native Python editors and run it remotely on SQL Server using Python clients for SQL Server.

In this article, we will see how to execute some of the basic Python functionalities within SQL Server Management Studio. The article provides an introduction to running basic Python scripts in SQL Server Management Studio.

Read more »
Timothy Smith
Image 4

CTEs in SQL Server; Using Common Table Expressions To Solve Rebasing an Identifier Column

January 31, 2019 by

Since we know that the SQL CTE (common table expression) offers us a tool to group and order data in SQL Server, we will see an applied example of using common table expressions to solve the business challenge of re-basing identifier columns. We can think of the business problem like the following: we have a table of foods that we sell with a unique identifier integer associated with the food. As we sell new foods, we insert the food in our list. After a few years, we observe that many of our queries involve the foods grouped alphabetically. However, our food list is just a list of foods that we add to as needed without any grouping. Rather than re-group or re-order through queries using a SQL CTE or subquery, we want to permanently update the identifier.

Read more »
Esat Erkec

SQL Convert Function

January 29, 2019 by

In this article, we will discuss and learn basics and all details about SQL Server data type converting operations and also we will review the SQL CONVERT and TRY_CONVERT built-in functions with various samples. At first, we will explain and clarify syntax of the SQL CONVERT function and then we will learn how can we make data converting process numerical and date/time data types to character data.

Read more »
Timothy Smith

Inserts and Updates with CTEs in SQL Server (Common Table Expressions)

January 28, 2019 by

In CTEs in SQL Server; Querying Common Table Expressions the first article of this series, we looked at creating common table expressions for select statements to help us organize data. This can be useful in aggregates, partition-based selections from within data, or for calculations where ordering data within groups can help us. We also saw that we weren’t required to explicitly create a table an insert data, but we did have to ensure that we had names for each of the columns along with the names being unique. Now, we’ll use our select statements for inserts and updates.

Read more »
Timothy Smith

CTEs in SQL Server; Querying Common Table Expressions

January 25, 2019 by

Common table expressions (CTEs) in SQL Server provide us with a tool that allows us to design and organize queries in ways that may allow faster development, troubleshooting, and improve performance. In the first part of this series, we’ll look at querying against these with a practice data set. From examples of wrapped query checks to organization of data to multiple structured queries, we’ll see how many options we have with this tool and where it may be useful when we query data.

Read more »
Timothy Smith

T-SQL’s Native Bulk Insert Basics

January 8, 2019 by

From troubleshooting many data flow applications designed by others, I’ve seen a common pattern of over complexity with many designs. Putting aside possible risks by introducing too much complexity, troubleshooting these designs often involves opening many different applications – from a notepad file, to SSIS, to SQL Server Management Studio, to a script tool, etc. It may sound like many of these are doing a hundred steps, yet many times, they’re simply importing data from a file, or calling five stored procedures and then a file task of moving a file. This complexity is often unnecessary, as is opening many different tools when we can use a few tools and solve issues faster.

Read more »
Sifiso W. Ndlovu

An MDS Driven Approach to a Turnaround Time Calculation in SQL Server

November 30, 2018 by

One calculation that you are almost guaranteed to have to produce in your career as a T-SQL developer relates to the calculation of a turnaround time. This is often a key KPI for measuring the performance of both individuals and teams, particularly when the business operates within a service-oriented sector i.e. customer support, transportation, healthcare etc. Turnaround time calculation does not only refer to business metrics rather any activity (i.e. ordering a pizza) with a recorded start and an end time can have its own turnaround time calculated. In this article we evaluate different options for calculating a turnaround time including using DATEDIFF function, creating your own user-defined function (UDF) as well as an integration with SQL Server Master Data Services.

Read more »
Bojan Petrovic

SQL index overview and strategy

November 27, 2018 by

A SQL index is used to retrieve data from a database very fast. Indexing a table or view is, without a doubt, one of the best ways to improve the performance of queries and applications.

A SQL index is a quick lookup table for finding records users need to search frequently. An index is small, fast, and optimized for quick lookups. It is very useful for connecting the relational tables and searching large tables.

Read more »