T-SQL

Timothy Smith

Useful T-SQL techniques for development in SQL Server

July 17, 2018 by

When we’re developing solutions, we can sometimes forget useful commands we can use in T-SQL that make it convenient to remove data, eliminate objects, or carefully remove data. We look at three of these commands with a few examples of where we might consider using them in development, or in rare production cases. While they may offer us speed and convenience in some cases, we also look at some situations where they may not be the best tool to use.

Read more »
Nikhilesh Patel

Spatial data types in SQL Server

July 11, 2018 by

Spatial data type

SQL server furnishes us with the geometry and geography data types for conserving spatial data, which allows us to render graphical data. To be more specific, it is beneficial for creating, analyzing, comparing and retrieving spatial data.

Read more »
Timothy Smith

SQL Server – using lowest unit of measurement in T-SQL

July 2, 2018 by

A client recently discovered a discrepancy on one of our reports that showed an improvement in performance metrics but was inaccurate. Our reports came from a software tool, which showed the average performance throughout the day. It derived this number from periodic checks and the frequency changed, which affected our report. When we showed an improvement in the metrics on a report, the client showed us that the frequency change may have impacted this, not necessarily any improvement in performance. In situations where we’re measuring values and comparing them to other values, how can we prevent a change in measurement from impacting our reports? Read more »

Esat Erkec

How to automatically create KPIs in SQL Server Reporting Services

June 18, 2018 by

Key Performance Indicator (KPI)

A Key Performance Indicator aka KPI is a metric which objectively measures the numeric equivalent of goals or targets of a company, team or an organization. Any individual or a community which has a numerical target can measure success of their goal with key performance indicators.

In all business roles and levels, if you manage a team you most likely have to set at least one key performance indicator and try influence this metric to show a positive result or trend. KPIs should be both logical and achievable. If not, team motivation and morale may decrease as a result of loss of self-confidence. For this reason, we have to be careful and logical when we set key performance indicators. At the same time, we have to check KPIs at regular intervals to determine if they remain valid. We can illustrate a typical KPI life cycle below.

Read more »
Gerald Britton

Partial stored procedures in SQL Server

June 8, 2018 by

Introduction

SQL is an old language — almost 50 years! The first commercially-available version of SQL was released by Oracle way back in 1969. In its specifications and general, “standard” appearance, it resembles the other leading language of the day, COBOL. Language theory and computer languages have evolved considerably since then. Modern concepts such as object-oriented programming and functional programming are not well-represented in SQL. That’s a pity. It can lead to copying code which almost always increases maintenance costs and code fragility.

Read more »
Timothy Smith

Restricting and monitoring SQL Server data access with views and procedures

May 17, 2018 by

We recently faced a leak of information for one of our employees that involved data which caused a conflict within our company, even if it was not personally identifiable information (PII data). When we investigated the issue, we uncovered that we need to organize data access for our teams and review who has access to what information. As of right now, all our users either have access to all tables directly or a subset of all our tables. What are some practices we can use in SQL Server to avoid giving direct table access to our users? Read more »

Gerald Britton

Recursive CTEs and Foreign Key References in SQL Server

May 16, 2018 by

Introduction

Foreign key constraints are a powerful mechanism for preserving referential integrity in a database. They can also represent a challenge when doing bulk table loads, since you need to find a “base” table to start with – that is, a table that has no foreign key constraints defined. Let’s label tables like this as level 0, or ground level if you like. Once that is loaded, you can begin to load other tables that have foreign key references to the base table. We can label those tables level 1, and so on. If you start with table data that already has referentially integrity and load tables by their level numbers — level 0, level 1, level 2 and so on – the load should proceed without problems. Let’s look at a simple example:

Read more »
Ben Richardson

Understanding SQL Server’s TRY_PARSE and TRY_CONVERT functions

May 16, 2018 by

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.

Read more »
Nikhilesh Patel

The HashBytes function in T-SQL

May 16, 2018 by

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.

Read more »
Timothy Smith

Logging SQL Server database errors

May 15, 2018 by

We receive many database alerts with many of the alerts logging some of these same alerts or information to files or tables. What we’ve found over time is that the logging is now costing us quite a bit of resource. Our logging server (where both files and table logging are stored) has had a few outages related to conflicts from messages for other servers. We’ve considered scaling the alerting by environment type, but we’ve also considered that we may be logging too much information about our databases. In addition, since we receive so many alerts each day, it’s impossible for us to resolve them and assist with other issues that arise. What are some techniques that we can use to help us with the issue of too much logging information and too many alerts? Read more »

Ed Pollack

Troubleshooting Database Mail Failures

April 25, 2018 by

Description

Database Mail is a convenient and easy way to send alerts, reports, or data from SQL Server. Failures are not obvious to the us though, and developing a process to monitor these failures alongside other failures will save immense headaches if anything ever goes wrong.

Database Mail: a (very) brief overview

Database Mail is a component of SQL Server that is available in every edition, except for Express. This feature is designed to be as simple as possible to enable, configure, and use.

Database Mail relies on SMTP to send emails via a specified email server to any number of recipients. When configuring, you provide a mail server, credentials (if needed), and then the service is ready to use. We’ll be focusing here on failure reporting and not configuration. If you need help setting up or configuring this feature, check out some of the references at the end of this article.

Read more »
Ben Richardson

Sequence Objects in SQL Server

March 22, 2018 by

Sequence objects are used to sequentially generate numeric values. They were introduced in SQL Server 2012.

Sequence objects are similar to the IDENTITY column in any SQL table. However, unlike the IDENTITY column, they are independent and are not attached to any table. Sequence objects are used both independently and within the DML statements i.e. INSERT, UPDATE and DELETE.

This article will take a detailed look at sequence objects.

Read more »
Ed Pollack

Reporting and alerting on job failure in SQL Server

March 12, 2018 by

SQL Server Agent can be used to run a wide variety of tasks within SQL Server. The built-in monitoring tools, though, are not well-suited for environments with many servers or many databases.

Removing reliance on default notifications and building our own processes can allow for greater flexibility, less alerting noise, and the ability to track failure conditions that are not typically tracked by SQL Server!

Introduction

At the heart of the SQL Server Agent service is the ability to create, schedule, and customize jobs. These jobs can be given schedules that determine at what times of day a task should execute. Jobs can also be given triggers, such as a server restart or alert to respond to. Jobs can also be called via TSQL from anywhere that has the appropriate access and permissions to SQL Server Agent.

Read more »
SQLShack

SQL Server formatting tools

March 8, 2018 by

This collection of content will provide an overview of SQL Server formatting tools, with product page links for each of the tools. If you noticed a tool we missed, please let us know in the comments below.

Read more »
Page 1 of 41234