Functions

Querying remote data sources in SQL Server

June 10, 2016 by Ahmad Yaseen

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.

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

December 24, 2015 by Eli Leiba

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.

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

December 22, 2015 by Eli Leiba

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.

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

May 27, 2015 by Steve Simon

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!

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

December 25, 2014 by Daniel Calbimonte

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.

Background to exception handling in SQL Server

June 26, 2014 by Sifiso Ndlovu

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 »