T-SQL

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 »

Dejan Sarka

Data science, data understanding and preparation – entropy of a discrete variable

May 14, 2018 by

In the conclusion of my last article, Data science, data understanding and preparation – binning a continuous variable, I wrote something about preserving the information when you bin a continuous variable to bins with an equal number of cases. I am explaining this sentence in this article you are currently reading. I will show you how to calculate the information stored in a discrete variable by explaining the measure for the information, namely the entropy.

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 »
Dejan Sarka

Data science, data understanding and preparation – binning a continuous variable

April 23, 2018 by

I started to explain the data preparation part of a data science project with discrete variables. As you should know by now, discrete variables can be categorical or ordinal. For ordinal, you have to define the order either through the values of the variable or inform about the order the R or the Python execution engine. Let me start this article with Python code that shows another way how to define the order of the Education variable from the dbo.vTargetMail view from the AdventureWorksDW2016 demo database.

Read more »
Dejan Sarka

Data science, data understanding and preparation – ordinal variables and dummies

March 29, 2018 by

In my previous article, Introduction to data science, data understanding and preparation, I showed how to make an overview of a distribution of a discrete variable. I analyzed the NumberCarsOwned variable from the dbo.vTargetMail view that you can find in the AdventureWorksDW2016 demo database. The graphs I created in R and Python and the histogram created with T-SQL were all very nice. Now let me try to create a histogram for another variable from that view, for the Education variable. I am starting with R, as you can see from the following code.

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 »
Dejan Sarka

Introduction to data science, data understanding and preparation

March 14, 2018 by

Data science, machine learning, data mining, advanced analytics, or however you want to name it, is a hot topic these days. Many people would like to start some project in this area. However, very soon after the start you realize you have a huge problem: your data. Your data might come from your line of business applications, data warehouses, or even external sources. Typically, it is not prepared for applying advanced analytical algorithms on it straight out of the source. In addition, you have to understand your data thoroughly, otherwise you might feed the algorithms with inappropriate variables. Soon you learn the fact that is well known to seasoned data scientists: you spend around 70-80% of the time dedicated to a data science project on data preparation and understanding.

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 »
Daniel Calbimonte

How to upload multiple images to SQL Server

March 6, 2018 by

Introduction

Sometimes we need to store information including photos in our database. For example, the photo of the product, the photo of the team members. But how can we store images in SQL Server?

We could create an application in .NET or Java, but if we do not have experience in those programming languages, we could use SQL Server tools to do it.

In this new article, we will learn the following tips that will help us to work with images including how to:

  • insert one image into SQL Server
  • store multiple files into a table
  • verify that the images were inserted
Read more »
Timothy Smith

How to archive SQL Server data with scale in mind

February 21, 2018 by

We manage data in a growing environment where our clients query some of our data, and on occasion will query past data. We do not have an environment that scales and we know that we need to archive some of our data in a way that allows clients to access it, but also doesn’t interfere with current data clients are more interested in querying. With the current data in our environment and new data sets will be using in the future, what are some ways we can archive and scale our environment?

Read more »
Ahmad Yaseen

SQL Server table hints – WITH (NOLOCK) best practices

February 14, 2018 by

SQL Server table hints are a special type of explicit command that are used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is accomplished by enforcing a specific locking method, a specific index or query processing operation, such index seek or table scan, to be used by the SQL Server query optimizer to build the query execution plan. The table hints can be added to the FROM clause of the T-SQL query, affecting the table or the view that is referenced in the FROM clause only.

Read more »
Sifiso W. Ndlovu

Impact of CLR Strict Security configuration setting in SQL Server 2017

February 13, 2018 by

Every seasoned SQL Server developer will tell you that no matter how hard you try, there are just operations in SQL Server better implemented elsewhere than relying on native Transact-SQL language (T-SQL). Operations such as performing complex calculations, implementing regular expression checks and accessing external web service applications can easily lead to your SQL Server instance incurring significant performance overhead. Thankfully, through its common language runtime (CLR) feature, SQL Server provides developers with a platform to address some of the inconveniences of native T-SQL by supporting an import of assembly files produced from projects written in. Net programming languages (i.e. C#, VB.NET). I have personally found CLR to be very useful when it comes to splitting string characters into multiple delimited lines.

Read more »
Page 1 of 3123