Database development

Nemanja Popovic

SQL Server database continuous integration (CI) Best practices and how to implement them – Testing, processing and automation

January 31, 2017 by

Testing

Test databases should be processed with unit tests

In many shops code is unit tested at the point of commit. For databases, I prefer running all unit tests at once and in sequence against a QA database, vs development, as part of a Test step, in my continuous integration workflow pipeline. Yes, issues would be caught later than at check-in, but continuous integration largely solves this with frequent iterations, including at a commit itself. So the difference between on-check in, unit testing and unit testing a build created on-commit, is simply that the unit tests will be run against a fully re-constituted QA database, vs Development. The previous article in this series is SQL Server database continuous integration (CI) Best practices and how to implement them – Source control. Read more »
Nemanja Popovic

SQL Server database continuous integration (CI) Best practices and how to implement them – Source control

January 31, 2017 by

This article provides for a roadmap to continuous integration and delivery best practices, and along the way demonstrates how to apply these with ApexSQL tools and technologies. In some sections this article is aspirational, as no solution yet exists, but demonstrates our plan, direction and roadmap. As the tools that apply these best practices are released this article will be updated accordingly.

Read more »
Daniel Calbimonte

How to generate random SQL Server test data using T-SQL

January 26, 2017 by

Introduction

In this article, we will talk about generating random values for testing purposes.

I once had a customer with software that worked fine in the demo with 30 rows, but after some months, the software had more than a million rows and it became very slow. The problem was not SQL Server, the problem was the application, which was not designed for tables with millions of rows. The customer sued to the software provider and lawyers were needed to create a resolution. If the provider had tested the software with millions of rows, this problem would have never happened.

Read more »
David Alcock

Using sp_server_diagnostics

January 19, 2017 by

Troubleshooting SQL Server is all about gathering the right evidence. Ordinarily we utilise a variety of different methods and analyse their output to look for specific areas where we would focus our diagnostic efforts. We could for example, use the results of various DMVs to look at wait statistic and resource information to help us focus our investigation in a particular area of SQL Server.

Read more »
Marko Radakovic

Understanding SQL Server database static data and how it fits into Database lifecycle management

January 13, 2017 by

What is static data

Static data (aka Code, Lookup, List or Reference data), in the context of a relational database management system, is generally data that represents fixed data, that generally doesn’t change, or if it does, changes infrequently, such as abbreviations for US states for example e.g. ME, MA, NC. This data is typically referenced, extensively, by transactional type data. For example, an customer table would have references to static table for City name, State or province, Country, Payment terms e.g. NET 30 etc.

Read more »
Shawn Melton
C:\Users\wshaw\AppData\Local\Temp\SNAGHTML519cfb8.PNG

Introduction of Visual Studio Code for DBAs

December 29, 2016 by

Introduction

Visual Studio Code (Code), have you heard of this product yet? This is an open-source, cross-platform and extremely light weight code editor from Microsoft. You may see some folks explain this editor as the little brother to Visual Studio Community Edition (VS Community), but it is more compared to editors like Atom, Sublime Text or even Notepad++. It is not something you can use to compile program code, so it is for the less complex coding projects. I utilize Code as my editor of choice now with PowerShell, and even T-SQL at times. In this article, I wanted to walk you through using Code and note some specific extensions I use for PowerShell and 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 »
Nesha Maric

Creating reports based on existing stored procedures with SQL Server Reporting Services

November 10, 2016 by

Basic extraction of the SQL Server database data is usually achieved by querying the databases and creating stored procedure to automate the extraction process. Unfortunately, extracting the information in this way will not yield high-end reports, and only basic table-shaped reports are available when extracting the information via SQL Server Management Studio or similar tools. In order to create high-end reports that will include additional projections of the data, such are graphs, lists, charts… SQL Server offers powerful reporting options within the SQL Server Reporting service.

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 »
Marko Radakovic

Revision history of an object change in a SQL database using Team Foundation Server

May 31, 2016 by

Similarly, as described previously in this article, where the revision history is covered for the Git source control system, we’ll present the workflow of reviewing the history of committed SQL database objects using Team Foundation Server (TFS) source control system. In order to use TFS and have SQL database objects being version controlled, Visual Studio is required, as well as TFS server, either installed on a machine or TFS through Team Services, which is actually TFS “in the cloud”.

Read more »