Functions

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 »
Sifiso W. Ndlovu

ETL optimization using SQL Server 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 Server 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 »
Ahmad Yaseen

SQL Server Data Type Conversion Methods and performance comparison

October 3, 2017 by

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

Read more »
Ben Richardson
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

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.

Read more »
Gerald Britton

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

June 12, 2017 by

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.

Read more »
Ahmad Yaseen

Compression and decompression functions in SQL Server 2016

June 2, 2017 by

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.

Read more »
Gerald Britton

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

May 15, 2017 by

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.

Read more »
Daniel Calbimonte

FAQ about Dates in SQL Server

December 22, 2016 by

Introduction

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

  1. Which function should I use to get the current date in SQL Server?
  2. How can I get the current time in the format hh:mm:ss?
  3. How can I calculate my age in SQL Server with a birth date?
  4. How can I insert the current time by default in a SQL Server table?
  5. How can I check the total time that the employees of my company worked per day?
  6. How can I get the time of a specific region?
  7. How can I get the time of a specified Standard time?
Read more »
Jean-Pierre Voogt

Running with running totals in SQL Server

July 29, 2016 by

Background

Running totals have long been the core of most financial systems, be statements or even balance calculations at a given point in time. Now it’s not the hardest thing to do in SQL Server but it is definitely not the fastest thing in the world either as each record has to be evaluated separately. Prior to SQL Server 2012, you have to manually define the window/subset in which you want to calculate you running total, normally we would define a row number with a window on a specific order or a customer depending on the requirements at hand.

Read more »
Ahmad Yaseen

Querying remote data sources in SQL Server

June 10, 2016 by

A common activity when writing T-SQL queries is connecting to local databases and processing data directly. But there will be situations in which you need to connect to a remote database that is located in a different instance in the same server or in a different physical server, and process its data in parallel with the local data processing.

SQL Server provides us with four useful methods to connect to the remote database servers, even other database server types, and query its data within your T-SQL statement. In this article, we will discuss these four methods and how to use it to query remote SQL Server databases.

Read more »
Eli Leiba

Construct a special multi-statement table function for checking SQL Server’s health

December 24, 2015 by

The Problem

Checking the many SQL Servers health conditions is one of the DBA basic tasks. Monitoring many servers and databases, day after day can be a very tedious job. There are many aspects to look for when checking the server performance figures. Such aspects include among others: CPU, Read and Write Disk counters, Various memory counters and locks performance counters. The DBA needs a tool that checks the most important counters and output a report that states whether the server is considered to be healthy or not. For every aspect, a counter is chosen to represent it to be included in the overall report.

Read more »
Eli Leiba

Creating an automatic View to an In-line table function conversion stored procedure

December 22, 2015 by

Introducing the Problem

We have a series of views in our organization’s application database. The views definitions are all static queries that consist of few or many condition clauses.

When the application first started with the initial data, the views operated normally, but after a while, after a period where data volume increased, the application’s performance time, through the views usage, degraded.

Read more »
Steve Simon

Using the “Row_Number” function and a WHILE loop to create the rolling average report

May 27, 2015 by

Introduction

A few days ago I received an email from a gentleman in the healthcare industry. He had read an article that I had previously published a few years back on a well known SQL Server website. Based on the original article, he requested a few ideas on how to expand the sample code to do a rolling three-month revenue summary, in addition to a rolling three-month revenue average.

In today’s get together, we shall be looking at doing just this!

Read more »
Daniel Calbimonte

The SQL Server system views/tables/functions. Common questions and solutions to real life problems

December 25, 2014 by

Introduction

In this new article, we will talk about the system views/tables/functions and how to solve common questions using them.

The system views are views that contain internal information about a Database.

The master database for example contains information about the SQL Server itself, while the msdb database contain information about the SQL Server agent and each database has its own system views/tables.

In this article we will show how to get the list of tables, views, stored procedures, how to get a list of tables of all the databases, how to find a table in multiple datatabases, how to get the list of users, logins, mapped logins, how to detect a fragmentation in a table and more.

Read more »
Sifiso W. Ndlovu

Background to exception handling in SQL Server

June 26, 2014 by
Since the advent of SQL Server 7.0, the best way of raising T-SQL related errors back to calling applications has been through the usage of the RAISERROR statement. Figure 1 demonstrates a combination of mandatory parameters (i.e. msg_id/msg_str, severity, state) as well as optional parameters (i.e. WITH option) that ought to be provided for the purposes of successfully using the RAISERROR function. Read more »