Functions

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

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.

Impact of CLR Strict Security configuration setting in SQL Server 2017

February 13, 2018 by Sifiso Ndlovu

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.

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

January 17, 2018 by Prashanth Jayaram

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.

How to implement array-like functionality in SQL Server

January 16, 2018 by Daniel Calbimonte

Introduction

I was training some Oracle DBAs in T-SQL and they asked me how to create arrays in SQL Server.

I told them that there were no arrays in SQL Server like the ones that we have in Oracle (varray). They were disappointed and asked me how was this problem handled.

Some developers asked me the same thing. Where are the arrays in SQL Server?

ETL optimization using SQL TRY Functions

October 24, 2017 by Sifiso Ndlovu

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.

SQL Server Data Type Conversion Methods and performance comparison

October 3, 2017 by Ahmad Yaseen

When you define SQL Server database tables, local variables, expressions or parameters, you should specify what kind of data will be stored in those objects, such as text data, numbers, money or dates. This attribute is called the SQL Server Data Type. SQL Server provides us with a big library of system data types that define all types of data that can be used with SQL Server, from which we can choose the SQL Server data type that is suitable for the data we will store in that object. You can also define your own customized user defined data type using T-SQL script. SQL Server data types can be categorized into seven main categories:

$C:\Users\ben.ACUITYPLC\Desktop\screenshot2.png$

How to use SQL Server built-in functions and create user-defined scalar functions

July 7, 2017 by Ben Richardson

A function is a set of SQL statements that perform a specific task. Functions foster code reusability. If you have to repeatedly write large SQL scripts to perform the same task, you can create a function that performs that task. Next time instead of rewriting the SQL, you can simply call that function. A function accepts inputs in the form of parameters and returns a value. SQL Server comes with a set of built-in functions that perform a variety of tasks.

How to track SQL Server database space usage with built-in functions and DMVs

June 12, 2017 by Gerald Britton

Introduction

In the previous article in this series on dynamic management views in SQL Server, we used DMV’s to discover some basic information about databases on an instance you are connected to, along with some ideas about getting basic file system and security info. I promised to dive into security next but first I’d like to explore a topic that is currently quite critical for the company I work for today: tracking space usage.

How to use Window functions in SQL Server

June 9, 2017 by Ben Richardson

All database users know about regular aggregate functions which operate on an entire table and are used with a GROUP BY clause. But very few people use Window functions in SQL. These operate on a set of rows and return a single aggregated value for each row.

Compression and decompression functions in SQL Server 2016

June 2, 2017 by Ahmad Yaseen

The concept of data compression is not a new on for SQL Server Database Administrators , as it is was introduced the first time in SQL Server 2008. In that SQL Server version, you were able to configure the compression at the row and page levels on the table, index, indexed view or the partition. The row and page level compression is not the best choice in all cases, as it does not work well on the Binary Large Objects (BLOB) datatypes, such as the videos, images and text documents.

Discovering database specific information using built-in functions and dynamic management views (DMVs)

May 15, 2017 by Gerald Britton

Introduction

In the last two articles on dynamic management views in SQL Server, Discovering SQL server instance information using system views and Discovering more SQL Server information using the built-in dynamic management views (DMVs), we used DMVs to discover a fair bit of information about the SQL Server instance we’re connected to. In this article, we’ll begin diving in to database specifics. There is a lot of territory to cover! We’ll also use several of the built-in functions that come with SQL Server.

New date objects in SQL Server 2016 – DATEDIFF_BIG and AT TIME ZONE

March 10, 2017 by Ahmad Yaseen

SQL Server 2016 obtains the values of the date and time using the GetSystemTimeAsFileTime() Windows API, with precision fixed at 100 nanoseconds. The accuracy of these date and time values depends on the hardware specs and the version of the Windows of the server on which the SQL Server instance installed.

Functions and stored procedures comparisons in SQL Server

February 20, 2017 by Daniel Calbimonte

Introduction

In a previous article, Functions vs stored procedures in SQL Server, we compared Functions vs stored procedures across various attributes. In this article, we will continue the discussion. We will talk also about Table-valued functions and compare performance with stored procedures with table valued functions and scalar functions.

Functions vs stored procedures in SQL Server

February 14, 2017 by Daniel Calbimonte

Introduction

Usually DBAs prefer stored procedures in SQL instead of functions in SQL Server. Is this a good practice?

FAQ about Dates in SQL Server

December 22, 2016 by Daniel Calbimonte

Introduction

In this article, I compiled a list of FAQs and Answers about dates.

Which function should I use to get the current date in SQL Server?
How can I get the current time in the format hh:mm:ss?
How can I calculate my age in SQL Server with a birth date?
How can I insert the current time by default in a SQL Server table?
How can I check the total time that the employees of my company worked per day?
How can I get the time of a specific region?
How can I get the time of a specified Standard time?

New features and enhancements in SQL Server 2016 SP1

December 20, 2016 by Ahmad Yaseen

SQL Server 2016 SP1 is released as announced by Microsoft. It comes with a bunch of new features and enhancements as a result of customer and community feedback. In this article, I will introduce you to these new features and enhancements.

Reporting in SQL Server – Manipulating the data via the grouping functions (provided by the Tablix control)

October 18, 2016 by Steve Simon

Introduction

In many of our past “get togethers” we have discussed pivoting raw data in order to best represent corporate data within our business reporting. As we may remember, in many instances we pivoted the data within the report query.