In this article, we’ll walk-through the SQL update statement to modify one or more existing rows in the table.
Read more »
Languages and coding
Overview of the SQL LIKE Operator
October 2, 2018In this article, we are going to learn how to use the SQL LIKE operator, in SQL Server, using regular expressions to find and/or manipulate text. We will start by learning the symbols and basic syntax of using wildcard regular expressions. We will use character sets and repetition expressions to create flexible matching patterns, and along the way, we’ll examine different ways to use the LIKE operator. And then, finally, in the latter part of the section, we will explore some of the most common and most useful regular expression examples.
Read more »
SQL Union overview, usage and examples
September 25, 2018This article will provide a deep dive into the SQL Union operator, describing its many uses along with examples and explore some common questions like the differences between Union vs Union All.
Read more »
Querying data using the SQL Coalesce function
September 18, 2018We all know that a Null value is a field with no value. The statements that we are running daily will have to deal with Null values, especially when it comes to strings concatenation (adding strings together).
Read more »
SQL Join types overview and tutorial
September 17, 2018This article will provide an overview of the SQL Join and cover all of the SQL join types including inner, self, cross and outer. For inner joins we’ll be discussing Equi and Theta joins.
Read more »
Substring function overview
September 14, 2018The requirement of data refactoring is very common and vital in data mining operations. In the previous article SQL string functions for Data Munging (Wrangling), you’ll learn the tips for getting started with SQL string functions, including the substring function for data munging with SQL Server. As we all agree that the data stored in one form sometimes require a transformation, we’ll take a look at some common functions or tasks for changing the case of a string, converting a value into a different type, trimming a value, and replacing a particular string in a field and so on.
Read more »
SQL string functions for Data Munging (Wrangling)
September 13, 2018In this article, you’ll learn the tips for getting started using SQL string functions for data munging with SQL Server. In many cases, Machine learning outcomes are only as good as the data they’re built on – but the work of preparing data for analytics (that is, data wrangling) can eat up as much as 80% of your project efforts.
Read more »
Querying data using the SQL Case statement
September 12, 2018The Case statement in SQL is mostly used in a case with equality expressions. The SQL Case statement is usually inside of a Select list to alter the output. What it does is evaluates a list of conditions and returns one of the multiple possible result expressions.
Read more »
Top 50 PowerShell bloggers of 2018
August 31, 2018We made a collection of the most popular PowerShell bloggers, including a link to each individual blog. The ranking is based on Alexa global score.
If your blog is on this list, you can display the ‘Top blogger’ badge on your blog. Please see the bottom of the page for the instructions on how to display the badge on your website
Read more »
How to set up a DDL and DML SQL Server database transactional replication solution
August 22, 2018In the article How to setup SQL Server database replication for reporting Server we discussed all about setting up a simple SQL Server transactional replication environment. In this article, we’ll simulate a more robust system that replicates faster and includes both DDL and DML statements. We’ll walk-through the steps to setup a dynamic transactional replication.
Read more »
Overview of the SQLCMD utility in SQL Server
August 13, 2018This article is aimed at helping you understand the sqlcmd utility. Of course, this is a tool most of us have used it at several occasions given that the tool has been around for a decade now. However, to be a solid starting point, this article contains some examples that help you understand the tool from different viewpoints.
This article discusses the following:
- How to enable SQLCMD
- A few important points to remember
- SQLCMD with scripting variables in SSMS
- Passing variables (or argument) as T-SQL and or a SQL Script file
- SQLCMD with scripting variables and Windows scripting in SSMS
Useful T-SQL techniques for development in SQL Server
July 17, 2018When we’re developing solutions, we can sometimes forget useful commands we can use in T-SQL that make it convenient to remove data, eliminate objects, or carefully remove data. We look at three of these commands with a few examples of where we might consider using them in development, or in rare production cases. While they may offer us speed and convenience in some cases, we also look at some situations where they may not be the best tool to use.
Read more »
SQL Server – using lowest unit of measurement in T-SQL
July 2, 2018A client recently discovered a discrepancy on one of our reports that showed an improvement in performance metrics but was inaccurate. Our reports came from a software tool, which showed the average performance throughout the day. It derived this number from periodic checks and the frequency changed, which affected our report. When we showed an improvement in the metrics on a report, the client showed us that the frequency change may have impacted this, not necessarily any improvement in performance. In situations where we’re measuring values and comparing them to other values, how can we prevent a change in measurement from impacting our reports? Read more »
How to implement error handling in SQL Server
June 15, 2018Error handling overview
Error handling in SQL Server gives us control over the Transact-SQL code. For example, when things go wrong, we get a chance to do something about it and possibly make it right again. SQL Server error handling can be as simple as just logging that something happened, or it could be us trying to fix an error. It can even be translating the error in SQL language because we all know how technical SQL Server error messages could get making no sense and hard to understand. Luckily, we have a chance to translate those messages into something more meaningful to pass on to the users, developers, etc.
Read more »
The Difference between CROSS APPLY and OUTER APPLY in SQL Server
June 6, 2018SQL Server supports table valued functions, what are functions that return data in the form of tables.
JOIN operations in SQL Server are used to join two or more tables. However, JOIN operations cannot be used to join a table with the output of a table valued function.
APPLY operators are used for this purpose.
Read more »
Backup Linux SQL Server databases using PowerShell and Windows task scheduler
May 22, 2018This article is an in-depth guide on how PowerShell can be used to maintain and manage SQL backup on Linux and Windows machines.
Here’s an outline of what this article is all about:
- Introduction
- Technical specifications
- How to load SQL Server modules on Windows machine
- Security – Credential Management
- The objectives of Backup and Restore
- Library Linkage
- How SQL Server 2017 backup feature is used on Linux
- And more …
Understanding SQL Server’s TRY_PARSE and TRY_CONVERT functions
May 16, 2018Data conversion is one of the most fundamental tasks of any programming language. Data received from different sources is often not in the right format. For example, if you receive an XML file where age is in the string format and you want to calculate an average age for the people in the file you will need to convert age into an integer.
To make the conversion process simple, the TRY_PARSE and TRY_CONVERT functions were introduced in SQL Server 2012. Before TRY_PARSE and TRY_CONVERT, SQL Server only had the PARSE and CONVERT functions.
Read more »
The HashBytes function in T-SQL
May 16, 2018One of the paramount ways to guard data within a database is to utilize database encryption. However, no one encryption solution is perfect for all databases. Which encryption solution you select totally depends on the requirements of your application. Note that more powerful encryption for larger amounts of data requires a healthy amount of CPU. So, be prepared in the event that that introduction of encryption increases the system load.
Read more »
Reading file data with PowerShell
May 10, 2018We have some custom files that we receive from different providers and for these situations we are unable to use standard ETL programs without any customization. Since we’re expanding our ability to read these custom files with .NET, we’re looking for efficient ways to read files with PowerShell that we can use in SQL Server Job Agents, Windows Task Schedulers, or with our custom program, which can execute PowerShell scripts. We have many tools for parsing data and wanted to know efficient ways of reading the data for parsing, along with getting specific lines of data from files by number, or by the first or last line of the file. For reading files efficiently, what are some functions or libraries we can use?
Read more »
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
May 7, 2018Data is the key to your organization’s future, but if it’s outdated, irrelevant, or hidden then it’s no good. Maintenance and administration of databases takes a lot of work. As database administrators, we often tend to automate most of these repetitive tasks. A database refresh is one of the most common tasks performed by most of the administrators as part of their daily routine.
Today, database refreshes are quite frequent because of Continuous Integration (CI) and Continuous Deployment (CD). In most of the cases, testing requires a separate but current production dataset to ensure the validity of the desired result.
Read more »
The Halloween Problem in SQL Server and suggested solutions
May 4, 2018Description
As per Wikipedia, the Halloween problem was first discovered by Don Chamberlin, Pat Selinger, and Morton Astrahan, on Halloween day, 1976.
Read more »
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
May 3, 2018Database administrators are often requested to refresh a database, mostly to create a live copy of the production database to the test or development environment. This is done so that the development or the test environment closely resembles the production environment; this way, we prevent many undesirable issues when running the application in the production environment.
Many developers, at times, are required to write and debug code on the production copy of the database, so it makes more sense to refresh the database on regular basis.
Read more »
Learning PowerShell and SQL Server – Introduction
April 23, 2018Introduction
This article is the first step among many that I hope will help give you a foundation of knowledge to get started utilizing PowerShell. The focus in these steps will be specific to using PowerShell with SQL Server, but I have to cover some of the basic things. Which once you grasp the basics of PowerShell and using it, in a general sense, you set yourself up for easily learning how to use it with other products.
In this article I’m going to touch on the following items:
- History Lesson (short reference for a timeline on releases)
- Windows PowerShell vs PowerShell Core
- SQL Server and PowerShell (as it is today)
- Terminology (some terms that help to understand)
- PowerShell Editors
Sequence Objects in SQL Server
March 22, 2018Sequence objects are used to sequentially generate numeric values. They were introduced in SQL Server 2012.
Sequence objects are similar to the IDENTITY column in any SQL table. However, unlike the IDENTITY column, they are independent and are not attached to any table. Sequence objects are used both independently and within the DML statements i.e. INSERT, UPDATE and DELETE.
This article will take a detailed look at sequence objects.
Read more »
SQL formatter tools
March 8, 2018This collection of content will provide an overview of SQL formatter tools, with product page links for each of the tools. If you noticed a SQL formatter tool we missed, please let us know in the comments below.
Read more »