Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Playing with the log – VLF order while growing the log

The log file is one of those parts of SQL Server I find fascinating. For instance it is composed of VLFs (virtual log files). These VLFs are a logical way for SQL to break the physical file down into smaller pieces. These pieces are then marked as “in use” or… Read more

0 comments, 78 reads

Posted in SQLStudies on 17 December 2014

‘Twas 4:45 Friday

‘Twas 4:45 Friday, and all through the office
not a creature was stirring, not even the bosses.
The code was checked in, into Tortoise with care,
in hopes a release date – soon would be there.

The devs were all slumped – in front of computers,
while prospects of bonuses… Read more

0 comments, 85 reads

Posted in SQLStudies on 16 December 2014

Does a clustered index sort the rows within a page

This is just something I was curious about. I ran my tests and got an answer and now I thought I would share.

Before I start I want to point out a few things about my tests. I’m using code to create/modify a PRIMARY KEY. PRIMARY KEYs are not necessarily… Read more

0 comments, 166 reads

Posted in SQLStudies on 15 December 2014

DROP INDEX and CREATE INDEX vs ALTER INDEX REBUILD

I saw someone ask a few weeks ago if it was faster to drop and re-create an index or do an ALTER INDEX REBUILD. I’m not sure, although I suspect they will take about the same amount of time. And what do I do when I’m not sure how… Read more

2 comments, 1,539 reads

Posted in SQLStudies on 11 December 2014

TSQL Tuesday #61: Giving Back, you gain more than you give.

Almost four months ago I posted my goals for the next year in my two year anniversary post. I pretty much decided I was going to continue on the same way I’d been going. Actively posting, writing the odd article, and hopefully catching up with my certifications. Well not only… Read more

0 comments, 93 reads

Posted in SQLStudies on 9 December 2014

INSERT INTO SELECT vs SELECT INTO

INSERT INTO SELECT and SELECT INTO may be very similar commands but they have some important differences. Every now and again I see people getting confused by the similarities and missing the differences. So here is a quick check list.

Similarities
  • They look similar (I think this one throws people…

Read more

2 comments, 550 reads

Posted in SQLStudies on 3 December 2014

Using a date or int column as the clustered index.

The other day I was answering a question about clustered indexes and it lead indirectly to a twitter conversation on whether a date or int column was better as a clustered index. My contention is that a date column (if it is appropriate and will be useful) is a better… Read more

9 comments, 5,354 reads

Posted in SQLStudies on 1 December 2014

Who’s on call

Kenneth Fisher:

I really enjoyed writing this post last year and honestly still enjoy reading it myself. And since tomorrow is a holiday for a lot of people (I’m in America so I get to make that assumption) it seemed like a good time to bring it out again.…

Read more

0 comments, 165 reads

Posted in SQLStudies on 26 November 2014

Putting sp_ at the beginning of your stored procedure.

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

1 comments, 213 reads

Posted in SQLStudies on 24 November 2014

You don’t really need ORDER BY do you?

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

--…

Read more

6 comments, 8,076 reads

Posted in SQLStudies on 19 November 2014

Using T-SQL to populate a Central Management Server

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

2 comments, 5,644 reads

Posted in SQLStudies on 17 November 2014

Adding a column to the primary key.

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

5 comments, 427 reads

Posted in SQLStudies on 13 November 2014

TSQL Tuesday #60: Something New Learned – What permissions does have?

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

4 comments, 766 reads

Posted in SQLStudies on 11 November 2014

How to remove additional (unwanted) log files

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

0 comments, 161 reads

Posted in SQLStudies on 5 November 2014

My DTS Package won’t load with the error “here were errors loading package”

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

0 comments, 127 reads

Posted in SQLStudies on 3 November 2014

Playing with the log – Before the first full backup

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

0 comments, 554 reads

Posted in SQLStudies on 29 October 2014

A script to list the DTS connection information for every package on the instance.

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

0 comments, 140 reads

Posted in SQLStudies on 27 October 2014

Force only certain values to be unique

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

1 comments, 5,553 reads

Posted in SQLStudies on 22 October 2014

Update to “The clustered index columns are in all of the non clustered indexes.”

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

4 comments, 5,431 reads

Posted in SQLStudies on 20 October 2014

UNION vs UNION ALL

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…

Read more

5 comments, 426 reads

Posted in SQLStudies on 16 October 2014

Older posts