Running with running totals in SQL Server July 29, 2016 by Jean-Pierre Voogt 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 »
Warehousing JSON formatted data in SQL Server 2016 June 22, 2016 by Sifiso W. Ndlovu In this article, I continue to review the exciting features available in SQL Server 2016. One such feature is the long awaited T-SQL support for JSON formatted data. In this article we take a look at how JSON support will impact data warehouse solutions. Read more »
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. Read more »
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. Read more »
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. Read more »
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! Read more »
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. Read more »
Background to exception handling in SQL Server June 26, 2014 by Sifiso W. 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 »
