Every now and again you see articles and posts about putting sp_ at the beginning of a stored procedure. So what does that do and why should we care? The primary effect is that if you put a stored procedure starting with sp_ in master you can call it directly… Read more
You see dozens of blog posts and articles about how the order of a result set is not guaranteed without an ORDER BY clause and here is my contribution to the subject.
You all know I love examples so without further ado here we go:
Using data from AdventureWorks2014
We have been using a CMS (Central Management Server) at my office for the last few years. Even beyond the uses for managing multiple servers (PBM etc) we use it primarily as a shared list of registered servers. Given that our current list is a bit over 150 instances we… Read more
Every now and again you realize that the primary key for a table does not uniquely describe a row. Not really a big deal right? Just add an additional column in order to make it unique and move on. Easy enough if the column already exists and does not allow… Read more
What have I learned recently? Well as it happens I learned something rather interesting recently and was actually trying to figure out how/when to post it. This month’s T-SQL Tuesday host Chris Yates (b/t) wants to know something we have learned recently so that seems like… Read more
Using multiple data files is a common best practice for a number of reasons, but multiple log files? Typically a database has only one log file, and in fact I know of only one good reason to add an additional one. Every now and again though someone makes a mistake… Read more
You are trying to open a DTS package (yes some people still use DTS packages) and receive the following error:
Error opening a DTS package TITLE: Open DTS 2000 Package ------------------------------ here were errors loading package "MyDTSPackageName": [DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection. (Microsoft.SqlServer.DtsObjectExplorerUI)
First let me point out that the “here… Read more
The log file tends to fascinate me. In fact one of my favorite posts is where I looked into the effect of VLF size on shrinking the log. So the other day I was asking about VLFs and got sent to this great video of a recorded session from… Read more
For those of you who have moved completely to SQL 2012 & 2014, Lucky you and don’t judge! For the rest of us who are still dealing with DTS packages I’m going to do a couple of posts on the subject. To start with here’s a script for collecting all… Read more
I thought of something rather interesting the other day while answering a question asking for help with some table structures.
In a similar situation to the questioner let’s say we have a Baseball team. There are teams and players. A player can be some combination of coach, assistant coach… Read more
I certainly hadn’t planned on creating a part two of my post on clustered index columns but in the comments Jeremy Hughes pointed out that my understanding was if not incorrect, incomplete. So I did some additional testing, confirmed a few things with Paul Randal (b/t)… Read more
You might be wondering why I’m going into such a simple subject. Well the way I see it there are four options here.
- You already know the difference, it seems really obvious and you are probably wondering why I’m mentioning it.
- You think you know the difference but it turns…
I have to admit this isn’t something I think about much. I have… Read more
I was watching a great presentation by Brent Ozar(b/t) on why to move to SQL 2014 and in the Q & A someone asked about jumping from SQL 2005 up to 2014. This immediately got me to thinking about the Breaking Changes pages that Microsoft puts… Read more
One of my favorite features of CROSS APPLY is the ability to use it instead of a calculated variable. Well recently I was working on performance tuning a rather annoying query (which I will blog about in more detail later) and one of the steps I took was an… Read more
I had an article published yesterday on http://www.SQLShack.com.
It’s worth reading if I do say so myself. In it I go through several different methods for generating data that might be required by an auditor. I range from adding auditing columns to each… Read more
One of the options you had when installing your instance was setting some default file locations.
- Defaut data file location
- Default log file location
- Default backup file location
If you are new to installing SQL Server (and even if you aren’t) you might very well have skipped past these… Read more
For a SQL Server guy it sometimes amazes me how often I’m in and out of batch files and how truly useful they can be. To that end here is all that I can remember about batch files (basically a text file that ends with .bat). Everyone seemed to like… Read more
I was recently asked where to go in Configuration Manager to change the settings for xp_cmdshell. It was then that I realized that the existence of sp_configure is probably not universal knowledge. I was truly shocked. For some reason I had thought that DBA’s were born with this knowledge. As… Read more
Triggers are very useful tools but can easily cause all sorts of headaches. In fact I would generally advise… Read more