SQL Database development

Prashanth Jayaram

An overview of Python vs PowerShell for SQL Server Database Administration

November 2, 2017 by

Today, Microsoft claims that Linux runs like a First-Class citizen on Azure, .NET Core has been open-sourced, and has been ported over to Linux, taking PowerShell along. PowerShell runs really well on Ubuntu, CentOS, RedHat Linux, and even Mac OS X. There are Alpha builds available for a few other platforms as well, all available for exploitation under the MIT License on GitHub. “Manage anything, anywhere” is what Microsoft is offering to its customers. Keeping with that, we now have:

Read more »
Ahmad Yaseen

Commonly used SQL Server Constraints: FOREIGN KEY, CHECK and DEFAULT

October 31, 2017 by

In the previous article Commonly used SQL Server Constraints: NOT NULL, UNIQUE and PRIMARY KEY, we described, in detail, the first three types of the SQL Server constraints; NOT NULL, UNIQUE and PRIMARY KEY. In this article, we will discuss the other three constraints; FOREIGN KEY, CHECK and DEFAULT by describing each one briefly and providing practical examples.

Read more »
Craig Porteous

How to build a SQL Server Virtual Lab with AutomatedLab in Hyper-V

October 30, 2017 by

There are often situations I want to test a piece of software or the latest version of SQL Server for a proof of concept, maybe even the new Power BI Report Server. I could install these locally, where I’ve likely got previous SQL installs or an already burgeoning system. You could be lucky that you have access to Virtual Machines that you can build out for the task. Now add a few hours to setup Windows or even just the applications themselves. Those VMs may be in Azure, or AWS, now add a few £’s or $’s every time you want to work on something. Did you remember to turn them off?

Read more »
Ahmad Yaseen

Commonly used SQL Server Constraints: NOT NULL, UNIQUE and PRIMARY KEY

October 25, 2017 by

SQL Server Constraints are predefined rules and restrictions that are enforced in a single column or multiple columns, regarding the values allowed in the columns, to maintain the integrity, accuracy, and reliability of that column’s data. In other words, if the inserted data meets the constraint rule, it will be inserted successfully. If the inserted data violates the defined constraint, the insert operation will be aborted.

Read more »
Sifiso W. Ndlovu

ETL optimization using SQL Server TRY Functions

October 24, 2017 by

Introduction

An enterprise data warehouse ETL solution typically includes, amongst other steps, a data transformation step that converts source data from one data type into another. It is during this step that type conversion errors may occur and depending on the type of exception handling techniques implemented in the ETL solution (or lack thereof), frustration may occur for both ETL developers and DBAs when trying to identify and resolve type conversion errors. In this article we take a look at a trio of SQL Server built-in functions that were introduced in SQL Server 2012, namely, TRY_PARSE, TRY_CAST, and TRY_CONVERT and how they could be utilized to reduce type conversion errors in ETL solutions and thereby saving developers needless troubleshooting exercise.

Read more »
Prashanth Jayaram

Why would a SQL Server DBA be interested in Python?

October 23, 2017 by

If we follow blogs and publications on the technological advancement with respect to SQL, we notice the increase in the number of references to Python, of late. Often, that makes us think:

  • Why so much emphasis on Python these days?
  • Isn’t knowing PowerShell scripting sufficient for the automation requirements of today?
  • Is it the time DBAs started learning a programming language such as Python in order to handle their day-to-day tasks more efficiently?
  • Why do so many job postings these days include “knowledge of scripting” as a requirement?
  • Is all of this happening because the paradigm is shifting? Can’t the current Microsoft-specific languages such as PowerShell handle the shift?
Read more »
Prashanth Jayaram

The evolution of SQL Server towards Digital Transformation challenges

October 9, 2017 by

In a data-driven world, where every second see a transfer of billions of pieces of data, enterprises are focused on making the access to data fluid and capable of being accessed on demand on a myriad of devices. Leveraging these capabilities to deliver better business results is now the prime focus. Information Technology is no more what it has been. What the web saw during the dot-com boom is what enterprise data is seeing now. Social, mobile, analytics, cloud, Big Data, Internet of Things… They’ve been enabling organizations to scale.

Read more »
Ahmad Yaseen

SQL Server Data Type Conversion Methods and performance comparison

October 3, 2017 by

When you define SQL Server database tables, local variables, expressions or parameters, you should specify what kind of data will be stored in those objects, such as text data, numbers, money or dates. This attribute is called the Data Type. SQL Server provides us with a big library of system data types that define all types of data that can be used with SQL Server, from which we can choose the data type that is suitable for the data we will store in that object. You can also define your own customized user defined data type using T-SQL script. SQL Server data types can be categorized into seven main categories:

Read more »
Samir Behara

Review of SQL Cop unit tests collection

September 29, 2017 by

The importance of Code Quality in Software Development has increased over the period of time. Everyone wants to follow coding standards and write clean code. However, to timely deliver a product to Business we end up in violating design principles, writing fewer unit tests, increasing code complexity and breaching best practices. This increases the overall Technical Debt of the application and has the potential to halt future development work by creating unplanned work.

Read more »
Gerald Britton

How to compare tables in SQL Server

September 26, 2017 by

Introduction

If you’ve been developing in SQL Server for any length of time, you’ve no doubt hit this scenario: You have an existing, working query that produces results your customers or business owners say are correct. Now, you’re asked to change something, or perhaps you find out your existing code will have to work with new source data or maybe there’s a performance problem and you need to tune the query. Whatever the case, you want to be sure that whatever changes have been made (whether in your code or somewhere else), the changes in the output are as expected. In other words, you need to be sure that anything that was supposed to change, did, and that anything else remains the same. So, how can you easily do that in SQL Server?

Read more »
Ahmad Yaseen

T-SQL commands performance comparison – NOT IN vs NOT EXISTS vs LEFT JOIN vs EXCEPT

September 22, 2017 by

The T-SQL commands library, available in Microsoft SQL Server and updated in each version with new commands and enhancements to the existing commands, provides us with different ways to perform the same action. In addition to an ever evolving toolkit of commands, different developers will apply different techniques and approaches to the same problem sets and challenges

Read more »
Jefferson Elias

How to automatically maintain Full-Text indexes and catalogs

September 21, 2017 by

Introduction

In a previous article entitled Hands on Full-Text Search in SQL Server, we had an overview on the Full-Text feature as it’s implemented in SQL Server. We saw how to create Full-Text indexes and that they were stored inside a container called a Full-Text catalog. We’ve also seen that, by design, this kind of index will generate a fragmentation.

Read more »
Prashanth Jayaram

How to Capture Database(s) usage stats using sp_spaceused and PowerShell

September 11, 2017 by

Today’s businesses work with huge volumes of data. Sometimes, understanding the granularity of the data is helpful in data administration. Understanding the granularity poses unique challenges though and the database administrator needs to balance the key business and technical metrics of the environment. One of the key technical metrics is disk space estimation, which is vital to capacity planning and forecasting. The simplest way to get this information is by using the system stored procedure, called, sp_spaceused. The growth metrics can be captured periodically and stored in a central repository, a repository dedicated to helping with capacity planning and forecasting the disk requirements. This is a critical part of the day-to-day activities of a database administrator.

Read more »
Neeraj Prasad Sharma

Nested Loop Joins in SQL Server – Batch Sort and Implicit Sort

September 1, 2017 by

In SQL Server, it`s not always required to fully understand the internal structure, especially for performance and optimization, if the database design is good, because SQL Server is a very powerful Relational Database and, as such, it has many inbuilt optimization processes which assure a response to the users as fast as possible. But it is always beneficial for the SQL Server developers and administrators to understand the internal structure of the SQL Server so that they can understand and fix the problems that slowed the response of the Database.

Read more »
Jefferson Elias

Hands on Full-Text Search in SQL Server

August 25, 2017 by

Introduction

In most cases, we will use clustered and non-clustered indexes to help a query go faster, but these kinds of indexes have their own limitations and cannot be used for fast text lookup. For instance, a LIKE operator will lead SQL Server to scan the whole table in order to pick up values that meet the expression next to this operator. This means it won’t be fast in every case, even if an index is created for considered column.

Read more »
Prashanth Jayaram

Multi-server Script to Find Orphaned Data Files using PowerShell

August 23, 2017 by

Having worked in busy dev-test environments, it isn’t uncommon to come across situations where someone detached a database from an SQL server, but forgot to reattach it, or drop it from the server. The next task we are then required to undertake, upon coming across such a situation, is to clean up the traces. This task can be complicated if the detached databases get deregistered from the SQL Server metadata catalog.

Read more »
Sifiso W. Ndlovu

How to replace ASCII special characters in SQL Server

August 7, 2017 by

One of the important steps in an ETL process involves the transformation of source data. This could involve looking up foreign keys, converting values from one data type into another, or simply conducting data clean-ups by removing trailing and leading spaces. One aspect of transforming source data that could get complicated relates to the removal of ASCII special characters such as new line characters and the horizontal tab. In this article, we take a look at some of the issues you are likely to encounter when cleaning up source data that contains ASCII special characters and we also look at the user-defined function that could be applied to successfully remove such characters.

Read more »
Daniel Calbimonte

How to email SQL query results to your smartphone using the sp_send_dbmail stored procedure

July 24, 2017 by

Introduction

Sometimes there are tasks that require too much time to finish during business hours and we need to leave the office with some still pending like the migration of data, backups or long running queries. In such cases, we would still want to receive an email in our smartphones to make sure that the job was completed successfully.

Read more »
Page 1 of 812345...Last »