Timothy Smith

Applying Field Operators and Objects in Azure Cosmos DB

February 15, 2019 by

Since we will sometimes require removing documents in Azure Cosmos DB, we’ll want to be able to specify the documents for removal. In some cases, this will be as simple as specifying a field for removal, such as removing one type of workout in our temporary database we’ve created. In other delete situations, we’ll want to remove if the value of the field isn’t what we expect – such as greater than what we want. This applies to updates as well – we may want to drill into a specific value range for an update. In this tip, we’ll look at using operators with strings, numeric types and dates.

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 »
Ahmad Yaseen

Creating a SQL Server audit using SQL Server Extended Events and Triggers

February 13, 2019 by

This article will provide an overview of manually creating a SQL Server audit using SQL Server Extended events and triggers. It will provide an overview of SQL tracing/profiling as well as extended events. I’ll walk you through how to create a new extended events session and how to use it, in a worked example, to audit failed logins. Finally, we’ll touch on an auditing approach using triggers

Read more »
Esat Erkec

SQL Unit Testing with SSDT (SQL Server Data Tools)

February 12, 2019 by

In this article, we will discuss the essentials of the SQL unit testing and then we will explore how to apply SQL unit testing methodology in Azure SQL Database with help of SQL Server Data Tools (SSDT). In particular, this article will focus about unit test theory in terms of SQL Server database development aspect and illustrate SQL unit testing with a detailed example.

Read more »
Timothy Smith

Updating and Querying Details in Azure Cosmos DB

February 12, 2019 by

In the first part of this series Getting Started with Azure Cosmos DB, we looked at using Azure Cosmos DB for saving an individual’s fitness routine and why this database structure is better for this data than a SQL database while also showing that we still have to organize our structure like a file system organizes files. In this part of our series, we’ll begin looking at the terminology translation between NoSQL and SQL along with running updates for our documents and queries with filters that return some fields in our document, but not other fields.

Timothy Smith

Getting Started with Azure Cosmos DB

February 11, 2019 by

In the past two years, we’ve seen an explosion in growth with document-oriented databases like Azure Cosmos DB. MongoDB – one of the major document databases – went live on the Nasdaq and attracted some attention in the past year as well. While more developers are using the document structure for some appropriate data models, less than 10 years ago, some in the industry were predicting that document databases were unnecessary and wouldn’t last because all data could be flattened to fit the SQL model. I took the opposite approach, being an early adopter of MongoDB along with continuing to use SQL databases as I saw opportunities in both SQL and NoSQL for various data structures. While some data do fit the SQL model and SQL will continue to exist, some data are best for document databases, like Azure Cosmos DB. In this series, we’ll be looking at the why and how of document databases.

James Rhoat

Preparing for SQL Server Database Corruption; initial reaction and analysis

February 6, 2019 by

Corruption is a looming thought through every administrator’s mind, from sysadmins to database administrators there is always a monster hiding in the shadows. SQL Server Database corruption happens all the time around the world and while most of us have been lucky enough to avoid it, we should still be prepared. While there is no way to prevent the corruption from happening, we must work hard to implement practices that minimize damage caused by the corruption. This means good backups and of course running DBCC CHECKDB.

Read more »
Milan Rancic

SQL database provisioning via Database clone using PSDatabaseClone PowerShell module

February 4, 2019 by

With the complex SQL database development and production infrastructure there comes an issue with database provisioning. The issue implies that all development and testing instances should have proper version of a database in the proper environments. So that means that database development teams, client application teams, QA and testing teams need to work on proper database version which is usually the one in production but naturally cannot work on the production database itself. This is why those environments have to have a provisioned database and there comes the problem for database DevOps teams how to perform database provisioning, which is repetitive task, with optimal time invested.

Read more »
Rajendra Gupta

Importing SQL Server FILESTREAM data with SSIS packages

February 1, 2019 by

Initial configuration

We have been exploring the SQL Server FILESTREAM feature in this ongoing series of articles. In this previous article, Managing data with SQL Server FILESTREAM tables, we wrote about inserting FILESTREAM data into a FILESTREAM table and performing DML activities on it. Suppose we have created the FILESTREAM database in our instance and now we want to insert a large number of files into a FILESTREAM container. It is easy to write out the insert queries for a small number of files, but if the numbers of files were in huge quantity, it would be difficult to write out the code and insert data into it. It is difficult to manage such kind of requests regularly in the environment.

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 »
Ben Richardson

Importing and Working with CSV Files in SQL Server

January 28, 2019 by

Introduction

CSV (comma separated values) is one of the most popular formats for datasets used in machine learning and data science. MS Excel can be used for basic manipulation of data in CSV format. We often need to execute complex SQL queries on CSV files, which is not possible with MS Excel. See this article for what is possible with Power BI.

However, before we can execute complex SQL queries on CSV files, we need to convert CSV files to data tables.

Read more »