Development

Ed Pollack

Methods to Insert Data into SQL Server

April 10, 2019 by

Summary

There are a variety of ways of managing data to insert into SQL Server. How we generate and insert data into tables can have a profound impact on performance and maintainability! This topic is often overlooked as a beginner’s consideration, but mistakes in how we grow objects can create massive headaches for future developers and administrators.

Read more »
Timothy Smith

Securely Working with Invoke-SqlCmd

March 26, 2019 by

We have a convenient tool for working with PowerShell and SQL Server when using Invoke-SqlCmd. As we saw when running statements, we can run DDL and DML changes with the command without writing our own custom scripts. This carries advantages when we need to quickly develop with PowerShell, but it can come with drawbacks on security if we’re not careful how we use this function. We’ll look at security when using this function by starting with a few examples of what we can do when we have unlimited access along with how we can design to limit our environment to be strict with our use of this tool.

Read more »
Timothy Smith
Our CSV file returns with the Export-CSV function that catches the output from the select

Working with PowerShell’s Invoke-SqlCmd

March 18, 2019 by

PowerShell features many one-line commands for working with SQL Server, one of which is Invoke-SqlCmd. This tool can be useful in many development contexts where we need to quickly execute scripts or test code and it helps to know some of the parameters we’ll often use. In addition, because we may want a custom script using some of the underlying .NET objects, we’ll look at an alternative where we will be able to create a custom PowerShell script that connects to SQL Server in order to run commands. The latter can be useful because one-line scripts have a tendency to change in future versions of PowerShell and working with the library directly can sometimes avoid this challenge.

Read more »
Esat Erkec

SQL Unit Testing with SSDT (SQL Server Data Tools)

February 12, 2019 by

In this article, we will discuss the essentials of the SQL unit testing and then we will explore how to apply SQL unit testing methodology in Azure SQL Database with help of SQL Server Data Tools (SSDT). In particular, this article will focus about unit test theory in terms of SQL Server database development aspect and illustrate SQL unit testing with a detailed example.

Read more »
Timothy Smith

Working With Line Numbers and Errors Using Bulk Insert

January 10, 2019 by

In the first part of reviewing the basics of bulk insert, we looked at importing entire files, specifying delimiters for rows and columns, and bypassing error messages. Sometimes we’ll want to skip first and ending lines, log errors and bad records for review after inserting data, and work with data types directly without first importing using a varchar and converting to the data type later. In this part, we look at these techniques using T-SQL’s native bulk insert.

Ranga Babu

SQL Server Code simplification using synonyms

January 4, 2019 by

Problem

In our environment, we use different DB servers and these servers are linked via Linked servers for cross server transactions. We use these linked servers in our stored procedures. The server names are different in different environments and we had to change the server names manually when we promote the stored procedures to other environments. There is a chance of errors while changing the server names manually. In this article let us see how can we eliminate changing the code manually.

Read more »
Sifiso W. Ndlovu

An MDS Driven Approach to a Turnaround Time Calculation in SQL Server

November 30, 2018 by

One calculation that you are almost guaranteed to have to produce in your career as a T-SQL developer relates to the calculation of a turnaround time. This is often a key KPI for measuring the performance of both individuals and teams, particularly when the business operates within a service-oriented sector i.e. customer support, transportation, healthcare etc. Turnaround time calculation does not only refer to business metrics rather any activity (i.e. ordering a pizza) with a recorded start and an end time can have its own turnaround time calculated. In this article we evaluate different options for calculating a turnaround time including using DATEDIFF function, creating your own user-defined function (UDF) as well as an integration with SQL Server Master Data Services.

Read more »
Samir Behara
Machine generated alternative text: using (var conn — using (var cmd conn . Open(); cmd . cmd cmd cmd cmd . new Sq1Connection(connString)) new Sq1Command()) level, exception conn ; Command Type . Text ; string. Format ( sq1Temp1ate , Connection - . Command Type - . CommandText - . Command Timeout — conn . Connection Timeout; ExecuteNonQuery() ;

SQL Server Code Review Checklist for Developers

April 26, 2017 by

In a software development life cycle, Code Review plays an integral role in improving the product quality. Having a Code Review Checklist is indispensable since it ensures that the best practices are followed and reviews are performed consistently. It is essential for developers to be aware of the coding guidelines while working on their code changes. Catching a bug early in the process is inexpensive and easier to resolve, than compared with a bug caught later in the game. Having all the common mistakes added to the checklist document is a great way to create awareness and ensure good code quality over a period of time.

Read more »
Jefferson Elias

From mathematics to SQL Server, a fast introduction to set theory

April 25, 2017 by

Introduction

In the previous article of this series “An introduction to set-based vs procedural programming approaches in T-SQL”, we’ve seen from a simple example that we could find actual benefit from learning set-based approach when writing T-SQL code.

In this article, we will carry on in this way by having a look at what a set is and what we can do with it in a mathematical point of view and how it’s implemented and provided to us in SQL Server. We will also have a look at more “realistic” examples using Microsoft’s AdventureWorks database.

Read more »