Click here to monitor SSC
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.

Connecting to Azure SQL Database

Recently I created my first Azure SQL Database. But now I need to connect to it.

You can connect using any number of tools. We can actually see several options by going back to our Azure Portal. Select the SQL Databases list, a specific SQL Database, and then… Read more

1 comments, 63 reads

Posted in SQLStudies on 25 May 2016

Master_Views: master_objects, master_tables, etc

There is a highly useful system view called sys.master_files. If you’ve never encountered it before it returns a list of all files from all databases. It’s been somewhat frustrating to me that there is no similar master_objects, master_tables, master_indexes etc. So what does a dev/dba do? Create them! So… Read more

0 comments, 90 reads

Posted in SQLStudies on 23 May 2016

Re-Evaluating Best Practices

I was reading a blog post from my friend Randolph West (b/t) on Best Practices and a thought struck me.

Starting with Randolph’s definition of a best practice (he got it from Wikipedia and it’s more than good enough)

A best practice is a method or…

Read more

3 comments, 139 reads

Posted in SQLStudies on 18 May 2016

RAID your backups

Let’s start with a very brief definition of some RAID levels.

  • RAID 0 : Stripe your data across multiple disks. Writing a portion of the data to each disk. This improves performance but increases the risk of corruption.
  • RAID 1 : Mirror your data across multiple disks. This duplicates…

Read more

4 comments, 861 reads

Posted in SQLStudies on 16 May 2016

Comparing the contents of two tables

I’ve been working on tuning a query for the last week and I’ve had to make several changes to the query’s logic. I’m always a little nervous when making those types of changes so I try to do some careful testing before I even suggest the changes to the developers.… Read more

10 comments, 2,603 reads

Posted in SQLStudies on 12 May 2016

Getting your Cloud on

One of the things I like best about joining in on Adam Machanic’s (b/t) TSQL Tuesday blog party is that it can force you to explore things you haven’t tried before. Well that’s exactly what Wendy Pastrick (b/t) wanted us to do.… Read more

1 comments, 154 reads

Posted in SQLStudies on 10 May 2016

Grant Deny Revoke

It always amazes me how often these three commands get confused. Heck it amazes me how long I confused them.

TL;DR; GRANT and DENY are opposites. GRANT applies a positive permission, DENY a negative permission. DENY will always override GRANT. REVOKE is the opposite of GRANT and DENY. It removes… Read more

2 comments, 120 reads

Posted in SQLStudies on 4 May 2016

Remove files from a filegroup

You have a database with one or more filegroups, and one or more of those filegroups has multiple files. You’ve decided that you don’t actually need some of those additional files and want to get rid of them. Here are some steps you can follow.

Assume there are 5 files… Read more

0 comments, 764 reads

Posted in SQLStudies on 2 May 2016

Certificates, Proxies and Jobs! Oh My!

Oh Noes! I have a job that requires me to run an SSIS package, cmdshell script, etc. And I need them to have specific file level permissions! What do I do! I know I need to run my job/job step as a specific AD/Windows user but I don’t see how… Read more

2 comments, 1,621 reads

Posted in SQLStudies on 27 April 2016

Get your SQL Server MCSA without taking the BI exam.

I’m a big fan of certifications as a training exercise. However not everyone has the same interests. With the new MCSA (well new 6-8 years ago) we were required to take the data exam (70-461), the admin exam (70-462) AND the BI exam (70-463). I know a number… Read more

2 comments, 1,347 reads

Posted in SQLStudies on 25 April 2016

Why my primary test environment is case sensitive.

Me: I think I’m going create a new playground instance.
Myself: Make sure you use a case sensitive (CS) collation when you install it!
I: You mean a collation that forces us to pay attention to the case of the variable names (for example)? So that something like this returns… Read more

0 comments, 144 reads

Posted in SQLStudies on 20 April 2016

How do I rename a column?

Everyone makes mistakes right? And sometimes you create an object and decide later that you messed up and need to rename it.

EXEC sp_rename 'OldName','NewName'

This works for tables, stored procedures, views etc, but there are a few things to be careful about. It doesn’t change the code behind code… Read more

2 comments, 152 reads

Posted in SQLStudies on 18 April 2016

Modulo!

I love this somewhat obscure mathematical operator. All it does is return the remainder of a division statement but even though it’s pretty basic most people I talk to have never heard of it. The format is

dividend % divisor = remainder

DECLARE @remainder INT
DECLARE @dividend INT = 10…

Read more

5 comments, 132 reads

Posted in SQLStudies on 14 April 2016

The risks of Delayed Durability

It’s T-SQL Tuesday again and this month we are hosted by Jens Vestergaard (b/t) who want’s us to talk about our favorite SQL Server feature. Great topic, but a really difficult choice. There are so many great features of SQL Server.
Some of my favorites include: Read more

0 comments, 853 reads

Posted in SQLStudies on 12 April 2016

Name the schema when you create an object (or else)

This is one of those frequently ignored best practices.

When you create an object, specify the schema where it will be created.

I should also point out

When you reference an object (in a query for example), specify it’s schema.

I know I have a tendency to forget this myself.… Read more

1 comments, 148 reads

Posted in SQLStudies on 6 April 2016

Complex/Conditional ORDER BYs

Typically it’s considered best to run your queries without an ORDER BY and let the application do any sorting needed. Sometimes though it’s faster and easier to let SQL handle the sorting for you. And that’s fine. SQL is very good at sorting.

Simple sorts are pretty easy. In the… Read more

1 comments, 181 reads

Posted in SQLStudies on 4 April 2016

Hidden code in AdventureWorks

I’ve playing around with AdventureWorks and managed to found some hidden code! Truly odd stuff. I’m so shocked I couldn’t wait until the time I usually post.

So if you take the x character of the first character column of each table (where x is object_id % len(column) ), merge… Read more

4 comments, 192 reads

Posted in SQLStudies on 1 April 2016

When should I test?

To test or not to test. That is the question. Or is it? Shouldn’t we always test? That was the question posted here. It’s a big question so let’s break it down a bit.

Upgrades

You have a system and are about to upgrade some or all of it.… Read more

1 comments, 154 reads

Posted in SQLStudies on 30 March 2016

Why you shouldn’t use SELECT *

I answered an interesting question on SE today (ever notice how many posts come from stuff I read on forums?) and I was pleased with how the answer turned out. In particular one piece (expanded a bit) seemed like it would make a good blog post.

To start with here… Read more

1 comments, 151 reads

Posted in SQLStudies on 28 March 2016

I’m presenting for the Pass Security Virtual Chapter!

Tomorrow (3/24/2016) at noon CT I’ll be presenting my SQL Server Security Basics session for the Pass Security Virtual Chapter. If you are interested in SQL Server database security and have little to no experience with it then this is the session for you! Heck even if you do… Read more

1 comments, 150 reads

Posted in SQLStudies on 23 March 2016

Older posts