Database development

Overview of the SQL REPLACE function

November 27, 2018 by Prashanth Jayaram

In 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.

Overview of SQL COUNT and COUNT_BIG in SQL Server

November 23, 2018 by Prashanth Jayaram

In this article, we’ll walk-though two other important SQL aggregate function, SQL COUNT and COUNT_BIG. In the previous article of this series, we covered how to retrieve data, join tables, work with dates and times, use window functions, filter data, and much more.

Overview of the SQL Order by clause

November 23, 2018 by Prashanth Jayaram

In this article, we’ll walk-through the concept of the SQL Order by clause and understand how the SQL engine works with the ordering result in a query.

SQL CAST and SQL CONVERT function overview

November 16, 2018 by Prashanth Jayaram

This article is an effort to discuss SQL Cast and SQL Convert functions as a follow-up to previous articles, in which we’ve discussed several SQL tips such as SQL Date, SQL Coalesce, SQL Union, SQL Join, SQL Like, SQL String etc.

Columnstore Index Enhancements – data compression, estimates and savings

November 14, 2018 by Rajendra Gupta

Data compression is required to reduce database storage size as well as improving performance for the existing data. SQL Server 2008 introduced Data compression as an enterprise version feature. Further to this, SQL Server 2016 SP1 and above supports data compression using the standard edition as well.

Overview of the SQL ROW_NUMBER function

November 13, 2018 by Prashanth Jayaram

In this article, we’re going to discuss the SQL ROW_NUMBER function. This is a continuation of the SQL essential series. In this guide, I’ll explain what a window function is all about, and you’ll see sample examples to understand the concepts behind the SQL ROW_NUMBER function.

The new SQL Server 2019 function Approx_Count_Distinct

November 9, 2018 by Rajendra Gupta

SQL Server 2019 has a rich set of enhancements and new features. In particular, there are many new feature improvements in the database engine for better performance and query tuning.

Columnstore Index Enhancements – Index stats update in clone databases

November 9, 2018 by Rajendra Gupta

SQL Server was launched in 1993 on WinNT and it completed its 25-year anniversary recently. SQL Server has come a long way since its first release. At the same time, Microsoft announced a preview version of SQL Server 2019. SQL Server 2019 provides the ability to extend its support to big data, Apache Spark, Hadoop distributed file system (HDFS) and provides enhancements to database performance, security, new features, and enhancements to SQL Server on Linux.

Enhanced PolyBase SQL 2019 – External tables SQL Server, Catalog view and PushDown

November 6, 2018 by Rajendra Gupta

This article is part 4 of the series for SQL Server 2019 Enhanced PolyBase. Let quickly recap the previous articles.

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

October 31, 2018 by Prashanth Jayaram

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

Enhanced PolyBase SQL 2019 – External tables using t-SQL

October 31, 2018 by Rajendra Gupta

In this article on PolyBase, we will explore more use case scenarios for external tables using T-SQL.

Enhanced PolyBase SQL 2019 – Installation and basic overview

October 24, 2018 by Rajendra Gupta

SQL Server 2019 is recently launched in the ignite 2018 event by Microsoft. We can get an overview of SQL 2019 preview version and learn how to install it on Windows environment by following up the article SQL Server 2019 overview and installation.

SQL Server 2019 – New DMF sys.dm_db_page_info

October 22, 2018 by Rajendra Gupta

Microsoft released preview of SQL Server 2019 recently in Ignite 2018. With every release of SQL Server is enriched with new dynamic management view and functions along with enhancements to existing features.

In this article, we will view the newly introduced dynamic management function (DMF) sys.dm_db_page_info and explore the different scenarios around it.

SQL Server 2019 overview and installation

October 15, 2018 by Rajendra Gupta

On September 24^th, 2018, Microsoft launched SQL Server 2019 preview version (SQL Server vNext 2.0) in the ignite 2018 event. As you know, SQL Server 2017 is still being adopted by the organizations, we are now ready with this preview version.

Using the SQL Coalesce function in SQL Server

September 20, 2018 by Prashanth Jayaram

This article explores the string manipulation using SQL Coalesce function in SQL Server.

String manipulation is a process to generate another form of existing data in a way the business uses or displayed as results in the reports. Previous SQL string function articles, I have written, including SQL string functions for Data Munging and SQL Substring function overview discussed data preparation and data management tasks using built-in SQL Server string functions.

Querying data using the SQL Coalesce function

September 18, 2018 by Bojan Petrovic

We 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).

How to do a quick estimated compare of data in two large SQL Server databases to see if they are equal

August 29, 2018 by Prashanth Jayaram

Bringing impactful analysis into a data always comes with challenges. In many cases, we rely on automated tools and techniques to overcome many of these challenges.

All about MSSQL-Scripter, the SQL Server cross-platform scripting Tool

August 14, 2018 by Prashanth Jayaram

One of the major challenges we face today, in the software development lifecycle, is with respect to development and deployment. As applications are deployed by moving various pieces of the SQL code between several versions, configuration, different editions, and sometimes even different platforms, deployment becomes daunting. In my opinion, scripting helps a lot and can really “grease the wheels” with addressing many scenarios that involve such complexity.

The importance of database level scripting is high, but it is getting easier by the day. Scripting is a process of generating a text file that contains the data structures and data from the database objects.

So, do we have a SQL native tool which serves the various purposes and usage? In some cases, the answer’s a yes, and in others, it’s a no.

Continuous Database Delivery (CD) using SQL Server Tools SqlPackage.exe

August 14, 2018 by Prashanth Jayaram

A little curiosity on my part led me to research a little further on the available pieces of SQL Server Tools. A cross-platform tool for continuous integration and deployment, called SqlPackage, is part of SQL Server Data Tools. I picked it up because continuous integration, delivery and deployment are now must-have in the modern-day application development paradigm.

We’d talk about the following in this article:

The Database Management Life Cycle
Complexities of the database release management process
Introduction to SQL Server Data Tools and SqlPackage
Using SqlPackage.exe, with examples

How to implement error handling in SQL Server

June 15, 2018 by Bojan Petrovic

Error 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.

Partial stored procedures in SQL Server

June 8, 2018 by Gerald Britton

Introduction

SQL is an old language — almost 50 years! The first commercially-available version of SQL was released by Oracle way back in 1969. In its specifications and general, “standard” appearance, it resembles the other leading language of the day, COBOL. Language theory and computer languages have evolved considerably since then. Modern concepts such as object-oriented programming and functional programming are not well-represented in SQL. That’s a pity. It can lead to copying code which almost always increases maintenance costs and code fragility.

Restricting and monitoring SQL Server data access with SQL views and stored procedures

May 17, 2018 by Timothy Smith

This article explains data security for accessing sensitive data and restricts access in application using SQL Views and stored procedures.

We recently faced a leak of information for one of our employees that involved data which caused a conflict within our company, even if it was not personally identifiable information (PII data). When we investigated the issue, we uncovered that we need to organize data access for our teams and review who has access to what information. As of right now, all our users either have access to all tables directly or a subset of all our tables. What are some practices we can use in SQL Server to avoid giving direct table access to our users? Read more »

Understanding SQL Server’s TRY_PARSE and TRY_CONVERT functions

May 16, 2018 by Ben Richardson

Data 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.

The HashBytes function in T-SQL

May 16, 2018 by Nikhilesh Patel

One 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.

Getting started building applications using SQL Server DevOps Tools

March 28, 2018 by Prashanth Jayaram

This article is part of the SQL Server Tools series, aimed at giving you an idea of the available tools and techniques to build applications using SQL Server tools. The first article of the series covers the rudimentary DevOps concepts and discusses on a high level, the overview of SQL Server tools that can be piped for DevOps operations.