-->
SQL Clone
SQLServerCentral is supported by Redgate
 
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.

Temp table quiz

Brent Ozar (b/t) posted a pop quiz on twitter earlier today.

Go ahead and give it a shot .. I’ll wait.

So? What do you think? Did you get it right? I did, but I wasn’t 100% certain, nor did I initially think through all of… Read more

4 comments, 147 reads

Posted in SQLStudies on 26 June 2017

Dynamically generate the command line DMA statement for each database

If you are upgrading your instance to 2016 (or 2017 soon) then you probably are going to want to run the DMA (Database Migration Assistant). If you are doing a BUNCH of upgrades at once (say 100 instances or something) then you probably don’t want to use the… Read more

0 comments, 253 reads

Posted in SQLStudies on 21 June 2017

Adding Identity to an existing column

A friend had an interesting problem today. A really big table (multiple millions of rows) and no primary key. He then ran into an issue where he had to have one. The easiest thing is to create a new int column with an identity column right? Unfortunately in this case… Read more

1 comments, 141 reads

Posted in SQLStudies on 19 June 2017

Turning ANSI_PADDING off, and why you shouldn’t

I ran into an interesting error the other day while doing a partition switch.

Partition switch failed because : column ‘xyz’ does not have the same ANSI trimming semantics in tables ‘a’ and ‘b’

It turned out it was because the ANSI_PADDING setting was different between the two tables (well,… Read more

3 comments, 1,609 reads

Posted in SQLStudies on 15 June 2017

Buzzword Bingo

Grant Fritchey (b/t) is our host for T-SQL Tuesday this month and surprise surprise he’d like to talk about DEVOPS. (As he put it, it was either that or execution plans.) Now I’m not highly knowlegable about DevOps myself. I mean I have a general… Read more

0 comments, 456 reads

Posted in SQLStudies on 13 June 2017

UDP vs TCP

If you’ve worked much with named instances you’ve probably had to deal with the question “What port is that instance on?”. You probably are aware that the default instance uses port 1433. You might also be aware that the browser uses port 1434. What’s the browser I hear you… Read more

3 comments, 259 reads

Posted in SQLStudies on 7 June 2017

Backup to NUL

Yes, I spelled that correctly. NUL not NULL. NUL is basically a location you can send a backup to. In fact, one of the awesome bonuses of this location is that your backup will take up exactly 0 bytes of space. You got it, absolutely none! Now before you… Read more

6 comments, 124 reads

Posted in SQLStudies on 5 June 2017

SQL Crossword

I thought I’d do another crossword. Enjoy! If you need any help with it feel free to let me know

Across
4. Traditional form of an index.
6. Encrypt a column without revealing the encryption keys to the Database Engine.
8. Feature used to limit the amount of CPU,… Read more

6 comments, 1,953 reads

Posted in SQLStudies on 31 May 2017

Running multiple queries at the same time.

I read an interesting question today. Someone wanted to be able to run the same stored procedure multiple times, at the same time, but with different parameters each time. One way to do it would be to open multiple query windows, and run them all as close to at… Read more

8 comments, 2,266 reads

Posted in SQLStudies on 24 May 2017

What’s in a partition?

In my last post on partitioning I used the $Partition command in passing. I’ve been thinking it deserves a bit more attention.

So what does it do? Per BOL

Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function in…

Read more

0 comments, 125 reads

Posted in SQLStudies on 22 May 2017

T-SQL FizzBuzz

I was looking around for something to write about this evening and came across one of Russ Thomas’ (b/t) old monthly challenges (Feb 2016).

The challenge is two-fold. A FizzBuzz problem is a common programming interview challenge that asks a coder to print the numbers from…

Read more

10 comments, 2,360 reads

Posted in SQLStudies on 17 May 2017

Why shouldn’t I shrink my database log file?

TL;DR: It’s pretty pointless and can cause performance issues.

Let’s start by asking why you might want to shrink your log.

It’s too big
I find that people who say this frequently don’t have a firm idea of what is too big or even why it might be as big… Read more

1 comments, 164 reads

Posted in SQLStudies on 15 May 2017

Dynamic SQL and the joys of QUOTENAME

I’m a big fan of dynamic SQL. In the past I’ve written a How to, a Best Practices and even a Generic Dynamic SP although that last one was a bit so so in my opinion. Depending on what type of dynamic code you are writing QUOTENAME will be… Read more

5 comments, 205 reads

Posted in SQLStudies on 11 May 2017

Continuous Integration

It’s T-SQL Tuesday again! This month we are being hosted by James Anderson (b/t). Thanks James! He has asked us to talk about Shipping Database Changes. I actually have some experience with an aspect of this at a previous company and I thought this would… Read more

0 comments, 173 reads

Posted in SQLStudies on 9 May 2017

Partitioning on a nullable column


tl;dr; If you are SWITCHing data into a table and the partitioning column is nullable you will need to add AND ColName IS NOT NULL to the constraint of the table that holds the data you are SWITCHing in.


Partitioning is a really handy, if rather complex tool. It can… Read more

3 comments, 812 reads

Posted in SQLStudies on 3 May 2017

Simple Security Report

Quite a while ago I created a couple of little security scripts to help me out with permissions research. Over time they grew into the procedures sp_dbpermissions and sp_srvpermissions. I recently updated them to v 6.0 and one of the main reasons was so I could add an output Read more

2 comments, 1,764 reads

Posted in SQLStudies on 1 May 2017

Why is a Windows authenticated login more secure than a SQL authenticated one?

I had this question come up at work the other day and while I knew it was true I wasn’t as sure about why as I’d like. The person I was working with wanted a real answer, not just Because I say so, so off to the internet I… Read more

8 comments, 2,631 reads

Posted in SQLStudies on 26 April 2017

SSMS Tips: Copy Full Path

When you right click on a tab you’ll see a number of different options. You can set up new horizontal or vertical tab groups, pin tabs, close tabs etc. But in this particular case take a look at these options.

  • Copy Full Path
  • Open Containing Folder

 
They… Read more

0 comments, 137 reads

Posted in SQLStudies on 24 April 2017

When should I add an index?

We all know indexes are good and I’m hoping everyone knows you can have too many indexes. That means we should be careful when adding new indexes right? So when should we add a new index?

Here are my general rules of thumb, although of course, you should always… Read more

7 comments, 2,207 reads

Posted in SQLStudies on 19 April 2017

What is the “cost” in Cost Threshold for Parallelism?

tl;dr; While at one point the cost of a query was an estimated time to run in seconds, today it’s just an estimated number used to give an idea of scale.

Cost Threshold for Parallelism has come up several times recently. Just so far this year I’ve found the following… Read more

2 comments, 217 reads

Posted in SQLStudies on 17 April 2017

Older posts