Introduction
In this article, we will show how to use the SQL intersect logical operator using different examples.
Read more »
T-SQL
SQL View – A complete introduction and walk-through
July 1, 2019In relational databases, data is structured using various database objects like tables, stored procedure, views, clusters etc. This article aims to walk you through ‘SQL VIEW’ – one of the widely-used database objects in SQL Server.
Read more »
Monitor batch statements of the Get Data feature in Power BI using SQL Server extended events
July 1, 2019One shared characteristic among popular data visualization tools such as Power BI is the ease at which data can be extracted from a variety of disparate data sources, usually at a click of a button, as shown in Figure 1. Such convenience, though, tends to come at a cost as you often have little control over how background scripts used to extract data are generated. Yet, this should be of utmost concern for data architects and BI developers alike as rarely do you find auto-generated scripts that are efficient and optimal. In this article, join me as I put on my DBA hat and trace, monitor and review SQL batch statements that are auto-generated by the Get Data feature in Power BI using SQL Server Extended Events.
Read more »
Overview of SQL Server Rounding Functions – SQL Round, Ceiling and Floor
June 26, 2019Developers deal with numerous data types on a day- to-day basis. We need to change the data type or format as per the user requirement. We use ‘SQL Server rounding function’ like SQL Round, Ceiling and Floor to round the values to the nearest numbers. We perform an arithmetic calculation on data as well. It is a challenging task to change the value of a number to an approximate number. We do not want to display decimal numbers in the application front end.
Read more »
Overview of the SQL Count Distinct Function
June 26, 2019This article explores SQL Count Distinct operator for eliminating the duplicate rows in the result set.
Read more »
EXEC SQL overview and examples
June 18, 2019In this article, we will review on EXEC SQL statement in SQL Server and explore a few examples.
Read more »
SQL Server MERGE Statement overview and examples
June 14, 2019In this article, we will review SQL Server MERGE statement, alternatives to MERGE statement, different clauses and examples of MERGE statement in SQL Server.
Read more »
Bulk-Model Migration in SQL Server Master Data Services
May 30, 2019Just 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 »
SQL examples for beginners: SQL SELECT statement usage
May 27, 2019SQLSELECT statements are used to retrieve data from the database and also, they populate the result of the query into the result-sets. The SQL examples of this article discourse and explain the fundamental usage of the SELECT statement in the queries.
Methods to Insert Data into SQL Server
April 10, 2019Summary
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 »
SQL convert date
April 1, 2019Introduction
A common task for newbies is to learn how to do a SQL convert date and work to convert them date to other data types or covert other data types to Date.
Read more »
Conventional SQL Unit Testing with tSQLt in Simple Words
March 28, 2019This article is about basics of conventional SQL unit testing concepts and its implementation through tSQLt, a highly acclaimed SQL unit testing framework because of being written in T-SQL and its built-in design support for SQL SQL unit testing needs ranging from simple to complex scenarios.
CTEs in SQL Server; Using Common Table Expressions To Solve Rebasing an Identifier Column
January 31, 2019Since 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 »
SQL Convert Function
January 29, 2019In 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 »
CTE SQL Deletes; Considerations when Deleting Data with Common Table Expressions in SQL Server
January 29, 2019In this article, the latest in our series on Common table expressions, we’ll review CTE SQL Deletes including analyzing the operation before the delete, actually removing the data as well as organizing and ordering the deletes with CTEs.
Read more »
Inserts and Updates with CTEs in SQL Server (Common Table Expressions)
January 28, 2019In 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 »
CTEs in SQL Server; Querying Common Table Expressions
January 25, 2019Common 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 »
Implicit conversion in SQL Server
January 22, 2019This article will provide an overview of SQL Server implicit conversion including data type precedence and conversion tables, implicit conversion examples and means to detect occurrences of implicit conversion
Read more »
T-SQL’s Native Bulk Insert Basics
January 8, 2019From 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 »
CRUD operations in SQL Server
December 10, 2018CRUD operations are foundation operations every database developer and administrator needs to understand. Let’s take a look at how they work with this guide.
Read more »
What is SQL in SQL Server?
December 5, 2018
What is SQL, in the context of SQL Server, is a common question asked by beginners. This article is meant to answer that question, while providing some history, context and an overview of the fundamentals of the language
Read more »
The STRING_SPLIT function in SQL Server
December 3, 2018This article will cover the STRING_SPLIT function in SQL Server including an overview and detailed usage examples.
An MDS Driven Approach to a Turnaround Time Calculation in SQL Server
November 30, 2018One 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 »
Overview of the SQL REPLACE function
November 27, 2018In this article, I’ll show you how to find and replace data within strings. I will demonstrate how to use the function SQL REPLACE, where you look for a substring within a string, and then replace it.
SQL index overview and strategy
November 27, 2018A 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 »