T-SQL

Sifiso W. Ndlovu
Successful Execution of SSIS components

Bulk-Model Migration in SQL Server Master Data Services

May 30, 2019 by

Just over a year ago, I published How to migrate SQL Server 2017 Master Data Services Models into another server article, that essentially demonstrated the migration of a SQL Server Master Data Services (MDS) model from one MDS instance to another. Like many of my previously published articles, I have since used the aforementioned article as a technical reference (i.e. syntax lookup for using MDSModelDeploy.exe utility) during the implementation of MDS-related projects for my clients.

Read more »
Ed Pollack

Methods to Insert Data into SQL Server

April 10, 2019 by

Summary

There are a variety of ways of managing data to insert into SQL Server. How we generate and insert data into tables can have a profound impact on performance and maintainability! This topic is often overlooked as a beginner’s consideration, but mistakes in how we grow objects can create massive headaches for future developers and administrators.

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 »
Bojan Petrovic

SQL Join introduction and overview

November 15, 2018 by

A SQL Join clause is put within a Select statement and at the end, it’s given a join condition, which tells the database how to fetch your data. The column specified within the join condition must be preceded by a table name if the column name is the same in both tables. When a column is preceded with a table name, it’s known as a qualified column.

Read more »
Prashanth Jayaram

SQL date format Overview; DateDiff SQL function, DateAdd SQL function and more

October 31, 2018 by

SQL date format functions like the DateDiff SQL function and DateAdd SQL Function are oft used by DBAs but many of us never took the time to fully understand these extremely useful features. For professionals just getting started with SQL Server, these functions are some of the first to become familiar with. So hopefully this article will have a little something for everyone across the skill spectrum

Read more »