All Articles - SQL Authority with Pinal Dave

SQL SERVER 2016 – STRING_SPLIT Function Performance Comparison – Notes from the Field #118

March 17, 2016

Notes from the Field, SQL DateTime, SQL Function, SQL Scripts, SQL Server

SQL Server 2016 is around the corner. We already have SQL Server 2016 RC0 released and many of us have started to test out various aspects of the same. For many years users have created temporary tables as well as used cross apply functions to split a large string and make it a table. I think every single time when I was assigned this task, I disliked it as the methods I used never made me happy. Well, the situation is now changed now with STRING_SPLIT function in SQL Server 2016.

In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about easy and amazing functions of SQL Server. Kathi is an amazing instructor, she was the SQL author I have read in my early career. Today she brings a very new topic for database experts. Read the experience of Kathi in her own words.

SQL SERVER – Mirroring Error 1412 – The remote copy of database has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error:)

March 16, 2016

Pinal Dave

SQL Tips and Tricks

SQL Error Messages, SQL Mirroring, SQL Server

With mirroring gone as deprecated feature, I have tried to keep away from exploring into what has been happening in this space. But what can I do when customers still use them? The challenges for consulting is pushing the boundaries of my comfort zone here. This made me revisit some of my learnings again. As part of this exercise, I learnt a minute or two which I personally felt was worth a share as part of this blog post. Here is a story for Mirroring Error 1412.

SQL SERVER – The Need for Data Tier Elasticity in Cloud Computing

March 15, 2016

Pinal Dave

SQL Tips and Tricks

Cloud Computing, SQL Server

Many application workloads are characterized by resource use that varies over time. Usage peaks can vary depending on the time of the day, week, or month, and outside events. Even a big sport game or an online sale can bring usage spikes. With traditional, on-premises technologies, IT departments typically provisioned enough capacity to manage worst-case scenarios. This approach to scalability can leave a significant amount of underutilized resources in the data centers, most of the time—and inefficiency that can impact overall costs. Cloud computing promises answers to such inefficiencies. In the cloud, you can balance costs and performance by deploying new resources when needed and shrinking them during slack periods. This elastic approach matches demand and capacity.

SQL SERVER – Installing SQL Server Management Studio 2016 – Step by Step

March 14, 2016

Pinal Dave

SQL Tips and Tricks

SQL Server, SQL Server 2016, SQL Server Management Studio

New versions bring new learning. To keep myself up-to-date, I have downloaded RC0 (release candidate zero) for SQL Server 2016 and while installing SQL Server I was surprised to see the feature list. Installing these bits were something I was wanting to do as we are getting closer to the release date. But something got me by surprise though, check the screenshot shown below, there is no mention of SQL Server Management Studio 2016:

All Articles indexwithoutclusteredinex-600x400

Interview Question of the Week #062 – How to Find Table Without Clustered Index (Heap)?

March 13, 2016

Pinal Dave

SQL Interview Questions and Answers

Clustered Index, SQL Constraint and Keys, SQL Scripts, SQL Server

Question: How to Find Table Without Clustered Index (heap)?

Answer: In SQL Server, when we create a primary key it automatically creates clustered index on the table, unless we explicitly mentioned not to create one. Due to this reason, the most of the table where there is a primary key, there are good chances it is also a clustered index key.

All Articles file-format-error-01-600x200

SQL SERVER – Installation Error – File Format is Not Valid

March 12, 2016

Pinal Dave

SQL Tips and Tricks

File format, SQL Error Messages, SQL Server

The reason I love consulting is because I get to see many flavors of the problems. This new avatar for me is pushing the limits to how I keep learning technologies. My recent interaction with client has shown one more error and another angle to look at a problem. Though some of these errors are new, it is a great way to understand how SQL Server actually works. In this blog post we will see Installation Error.

Comparison – Understanding Tables Between Oracle and SQL Server

March 11, 2016

Pinal Dave

SQL Tips and Tricks

Oracle, SQL Server

Oracle has a Heap-organized table which SQL Server calls “Heap”
Oracle’s “Clustered” tables are called “Indexed Views” in SQL Server
Both Oracle and SQL Server have “Partition” & “Temporary” tables
Oracle’s “External” tables are called “Linked Servers” in SQL Server.
Oracle’s “Object” table is called “Table Type” in SQL Server.
Oracle’s “Index-organized” tables are referred to as a “Clustered index” in SQL Server.
Oracle does not have an equivalent/term for SQL Server’s In-Memory OLTP table
And Oracle’s “Hybrid columnar” compressed tables are called “Column store clustered index” in SQL Server.

SQL SERVER – The NOLOCK Question – Notes from the Field #117

March 10, 2016

Pinal Dave

SQL Tips and Tricks

Notes from the Field, SQL Server, SQL Server Security, SQL Statistics

[Note from Pinal]: This is a 117th episode of Notes from the Field series. Everytime I go to do performance tuning consultancy, one thing annoys me and makes me angry. The usage of NOLOCK hint. I think without any argument, this is one of the most abused query hint in the SQL Server universe. Lots of people treat NOLOCK as a silver bullet of performance tuning and that is unfortunate. There is a special purpose of how NOLOCK should be used and what is the end goal of it.

In this episode of the Notes from the Field series database expert Stuart Ainsworth explains about NOLOCK and its best practices. Trust me, you do not want to overuse NOLOCK hint as it can just impact your database integrity negatively.

SQL SERVER – ApexSQL Generate – SQL Server Test Data Generation

March 9, 2016

Pinal Dave

SQL

ApexSQL, SQL Server, SQL Utility

ApexSQL Generate is a test data generation tool for SQL Server. In this article, we’ll introduce the tools’ core features, describe the main types of generators, walk you through an example and, finally, cover some advanced features.

SQL SERVER – syspolicy_purge_history job failing step: Erase Phantom System Health Records

March 8, 2016

Pinal Dave

SQL Tips and Tricks

SQL Error Messages, SQL Server, SQL Server Management Studio

Getting back to consulting mode has got my newer clients and bigger challenges. Not only that I am seeing interests for some of the greatest and latest versions, but some clients are sitting on an older version of SQL Server requiring some serious help for trivial issues. One of my clients has asked to consult for a quick error message troubleshooting in the recent past. Since it was an interesting one about syspolicy_purge_history job, I am sharing it via the blog.