Being the top SQL Shack author again required double the effort and the time than I spent last year (2016) to achieve it, competing more professional authors with creative writing styles. Fortunately, I achieved it this year again, two years in a row. Let me tell you how I succeeded and what kept me motivated!
Read more »How to identify and resolve Hot latches in SQL Server
November 7, 2017Description
In SQL Server, internal latch architecture protects memory during SQL operations. It ensures the consistency of memory structures with read and write operation on pages. Rudimentarily, it has two classes, buffer latches, and non-buffer latches which perform lightweight synchronization in the SQL Engine.
Read more »An overview of Python vs PowerShell for SQL Server Database Administration
November 2, 2017Today, 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 »What’s new in SQL Server Management Studio 17.3; Import Flat File wizard and XEvent Profiler
November 1, 2017SQL Server Management Studio is an integrated graphical user interface tool that is used to perform wide range of administration and development tasks on the SQL Server instances, hosted locally on the user machine, remotely on a Windows or Linux server or on SQL Azure or Azure Data warehouse instances hosted in the cloud. You can use SQL Server Management Studio to easily administrate, develop, deploy, configure and monitor the SQL Server instances.
Read more »Commonly used SQL Server Constraints: FOREIGN KEY, CHECK and DEFAULT
October 31, 2017In 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 »How to build a SQL Server Virtual Lab with AutomatedLab in Hyper-V
October 30, 2017There 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 »How to import/export data to SQL Server using the SQL Server Import and Export Wizard
October 27, 2017This article will explain the steps for importing and exporting data using the SQL Server Import and Export Wizard.
Depending on the options that are selected in the SQL Server Import and Export Wizard, some steps may be different than those described in this article.
For the purpose of this example, exporting data from the SQL Server database to an Excel file will be shown.
Read more »SQL Server utilities you probably didn’t know about – SQLdiag, osql and SqlLocalDB
October 26, 2017Introduction
There are several tools included in SQL Server on-premises that you may not know.
In this article we will explain the following tools:
- SQLdiag
- osql
- SqlLocalDB
After reading this article, you will learn how to use them and understand what are they for.
Read more »Constraints in SQL Server: SQL NOT NULL, UNIQUE and SQL PRIMARY KEY
October 25, 2017This article explains the SQL NOT NULL, Unique and SQL Primary Key constraints in SQL Server with examples.
Constraints in SQL Server 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 »ETL optimization using SQL TRY Functions
October 24, 2017Introduction
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 TRY 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 »Why would a SQL Server DBA be interested in Python?
October 23, 2017If 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?
How SQL Server selects a deadlock victim
October 20, 2017In the article on Deadlock Analysis and Prevention, we saw how deadlock occurs. We saw that SQL Server selects one of the processes involved in the deadlock as deadlock victim. In this article, we will look at the criteria on which SQL server selects a deadlock victim. Why one process is selected as deadlock victim and not the other.
Read more »How to import a flat file into a SQL Server database using the Import Flat File wizard
October 19, 2017Import Flat File wizard is a new feature implemented in SQL Server Management Studio (SSMS) v17.3 for importing a .csv or a .txt file into a SQL Server database.
The latest SSMS version can be found on this link
Read more »Working with the SQL Server command line (sqlcmd)
October 18, 2017Introduction
Sqlcmd allows executing queries, T-SQL sentences and SQL Server scripts using the command line.
In the previous article How to work with the command line and Azure to automate tasks, we worked with the sqlcmd in Azure.
In this new chapter, we will show the following examples in a local SQL Server using sqlcmd:
Read more »Monitoring SQL Server database status changes using T-SQL and PowerShell scripts
October 17, 2017Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays.
Read more »Introduction and FAQs about Microsoft Azure technologies
October 13, 2017Most of the DBAs, Data Scientists, BI and Database developers are working on SQL Server on-premises. Is it time to migrate to the Cloud?
In this new article, we will talk about numbers, statistics, Azure technologies like Virtual Machines with SQL Server, Azure SQL, Azure Data Factory, Azure Machine learning and other technologies.
Read more »How to choose and check the right Database Recovery Model in accordance to your backup strategy
October 11, 2017Introduction
Following best practices, we should apply principles like segregation of duties. This implies the segregation of application environments. In particular, we might see for a given application that it incorporates at least two environments: a production environment and a non-production environment that exists for testing, Q/A, training, etc
Read more »Managing A Slowly Changing Dimension in SQL Server Integration Services
October 10, 2017A data warehouse has to be historically correct. This becomes an issue when data like the Product List Price for a previous year needs to be saved historically. Dimensional Modeling methodologies provide a solution for the situation. The Slowly Changing method integrated with components from SQL Server Integration Services solves the issue. This article will look at updating a product dimension table using the Slowly Changing Type 2 Dimension while maintaining the Type 1 columns.
Read more »The evolution of SQL Server towards Digital Transformation challenges
October 9, 2017In 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 »Top 10 questions and answers about SQL Server Indexes
October 5, 2017Introduction
Without a doubt, few technologies in SQL Server cause as much confusion and the spread of misinformation as indexes. This article looks at some of the most asked questions and a few that should be asked but often are not. We’ll be using SQL Server 2016 for the examples and a tool, for SQL Server query execution plan analysis, ApexSQL Plan, to explore the effects of indexes on a typical business problem: A table of customers.
Read more »How to automatically refresh a SQL Server database
October 4, 2017As a DBA, I am often asked to automate tasks. In addition to this, I generally work with a lot of developers that need to test with data refreshing on a regular basis. A lot of the time, I am asked to either stand up a new test environment or overwrite/refresh an existing one. Now, these tasks are not difficult to complete, but why not just automate them into a SQL Agent job and just run it whenever you need it or schedule it and let the job do all the work? If you have read some of my previous articles (What is causing database slowdowns or Simple SQL Server automated user auditing), you will know I am a big proponent of automating repeatable tasks. The best part of this is if you have multiple clients like I do is that you can script out the job, change the variables and use it over and over again. I cannot tell you how many times I say “I have a job for that” when a client asks me to automate a task.
Read more »SQL Server Data Type Conversion Methods and performance comparison
October 3, 2017When 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 SQL Server 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 SQL Server 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 »How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL
October 2, 2017In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more.
Read more »Review of SQL Cop for SQL unit testing
September 29, 2017SQL unit testing is rising in popularity amongst database developers. 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 »Setting up the dark theme in SQL Server Management Studio
September 28, 2017In SQL Server Management Studio 2016, Microsoft introduced a visual setting option to choose between the Blue and Light color theme. In SSMS 2016, SSMS 2017 and the latest SSMS 18, the user can switch between the Blue or Light theme by going to Tools | Options | Environment | General | Color theme:
Read more »