Database development

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 »
Rajendra Gupta
Execute DMV query sys.dm_db_database_page_allocations to get page details

SQL Server 2019 – New DMF sys.dm_db_page_info

October 22, 2018 by

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.

Read more »
Prashanth Jayaram

Using the SQL Coalesce function in SQL Server

September 20, 2018 by

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.

Read more »
Prashanth Jayaram

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

August 14, 2018 by

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.

Read more »
Prashanth Jayaram

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

August 14, 2018 by

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:

  1. The Database Management Life Cycle
  2. Complexities of the database release management process
  3. Introduction to SQL Server Data Tools and SqlPackage
  4. Using SqlPackage.exe, with examples
Read more »
Bojan Petrovic

How to implement error handling in SQL Server

June 15, 2018 by

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.

Read more »
Gerald Britton

Partial stored procedures in SQL Server

June 8, 2018 by

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.

Read more »
Timothy Smith

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

May 17, 2018 by

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 »

Ben Richardson

Understanding SQL Server’s TRY_PARSE and TRY_CONVERT functions

May 16, 2018 by

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.

Read more »
Nikhilesh Patel

The HashBytes function in T-SQL

May 16, 2018 by

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.

Read more »
Prashanth Jayaram

Getting started building applications using SQL Server DevOps Tools

March 28, 2018 by

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.

Read more »
Sifiso W. Ndlovu

Impact of CLR Strict Security configuration setting in SQL Server 2017

February 13, 2018 by

Every seasoned SQL Server developer will tell you that no matter how hard you try, there are just operations in SQL Server better implemented elsewhere than relying on native Transact-SQL language (T-SQL). Operations such as performing complex calculations, implementing regular expression checks and accessing external web service applications can easily lead to your SQL Server instance incurring significant performance overhead. Thankfully, through its common language runtime (CLR) feature, SQL Server provides developers with a platform to address some of the inconveniences of native T-SQL by supporting an import of assembly files produced from projects written in. Net programming languages (i.e. C#, VB.NET). I have personally found CLR to be very useful when it comes to splitting string characters into multiple delimited lines.

Read more »
Prashanth Jayaram

Top 8 new (or enhanced) SQL Server 2017 DMVs and DMFs for DBAs

January 17, 2018 by

Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.

SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.

Read more »
Brian Lockwood

Using production data for testing in a post GDPR world

January 15, 2018 by

To SQL Server DBAs who are the shepherds of data in organizations, key GDPR questions, in general, center around whether data will need to be treated differently, safeguarded more etc. and specifically, as it relates to allowing production data to be used in testing.

That will be the focus of this article as we’ll work our way through the details of this regulation as well as various authoritative articles on the subject, to address this key question. Then we’ll look to ways and means to potentially ameliorate our findings to provide alternatives and workarounds if possible.

Read more »
Craig Porteous

How to build a SQL Server Virtual Lab with AutomatedLab in Hyper-V

October 30, 2017 by

There are often situations I want to test a piece of software or the latest version of SQL Server for a proof of concept, maybe even the new Power BI Report Server. I could install these locally, where I’ve likely got previous SQL installs or an already burgeoning system. You could be lucky that you have access to Virtual Machines that you can build out for the task. Now add a few hours to setup Windows or even just the applications themselves. Those VMs may be in Azure, or AWS, now add a few £’s or $’s every time you want to work on something. Did you remember to turn them off?

Read more »
Sifiso W. Ndlovu

ETL optimization using SQL TRY Functions

October 24, 2017 by

Introduction

An enterprise data warehouse ETL solution typically includes, amongst other steps, a data transformation step that converts source data from one data type into another. It is during this step that type conversion errors may occur and depending on the type of exception handling techniques implemented in the ETL solution (or lack thereof), frustration may occur for both ETL developers and DBAs when trying to identify and resolve type conversion errors. In this article we take a look at a trio of SQL TRY built-in functions that were introduced in SQL Server 2012, namely, TRY_PARSE, TRY_CAST, and TRY_CONVERT and how they could be utilized to reduce type conversion errors in ETL solutions and thereby saving developers needless troubleshooting exercise.

Read more »