-->
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.

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

4 comments, 168 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, 81 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, 1,775 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, 141 reads

Posted in SQLStudies on 17 April 2017

No space queries

You know, sometimes you get a silly idea in your head and you just HAVE to try it out. The idea? Can I write a query without any white space? No spaces, no carriage returns etc. As it happens, yes. Yes I can.

SELECT*FROM[Person].[Person]WHERE[LastName]='Duffy';

You’ll notice I had to use… Read more

2 comments, 130 reads

Posted in SQLStudies on 13 April 2017

The more things change, the more they stay the same

Koen Verbeeck (b/t) is hosting T-SQL Tuesday this month and our topic is The times they are a-changing. In other words How does the cloud impact you and your job?

Well, to discuss how I feel about the cloud I have to go back a… Read more

2 comments, 195 reads

Posted in SQLStudies on 11 April 2017

How do I change the increment of an identity column?

Wellll .. technically there is no native way to do it. I even went through my notes on identity columns. No luck.

So how do we do it then? Well, believe it or not, there is a way. The other day Adam Machanic (b/t) pointed out… Read more

0 comments, 129 reads

Posted in SQLStudies on 5 April 2017

Hiding tables in SSMS Object Explorer

Extended Properties

Sometimes you find out the strangest things about SQL Server. I’ve written about extended properties before but had no idea they actually did anything beyond documentation. Then the other day I was reading a forum entry (no clue why) and saw a really interesting answer to the question.… Read more

0 comments, 128 reads

Posted in SQLStudies on 3 April 2017

Increasing and decreasing the performance level of your Azure SQL Database

One of the big benefits of Azure, and in fact any of the cloud offerings I’ve seen, is the ability to increase and decrease your capacity as needed. This is easy enough with Azure SQL Databases.

  1. In the Overview blade you can see current pricing tier.
  2. Under settings, just below…

Read more

0 comments, 118 reads

Posted in SQLStudies on 29 March 2017

sp_SrvPermissions & sp_DBPermissions V6.0 Finally!

It’s been almost 3 years since I updated these SPs! I can’t believe so much time has gone by! Well, I’m sure all of you have been holding your collective breath but I finally have some new updates. I’m particularly excited about the @DropTempTables and @Output options.

Just as a… Read more

3 comments, 136 reads

Posted in SQLStudies on 27 March 2017

Creating a list of random numbers

I was working on a blog post this weekend that required a list of random numbers. Now, this isn’t exactly an uncommon task, but it’s not as easy as it would seem. The random number function RAND() has a few inherent flaws. And from what I can remember the random… Read more

1 comments, 1,497 reads

Posted in SQLStudies on 22 March 2017

The parts of an object’s name

Any object within a database in SQL Server has what is called a four part name. Which rather implies four pieces right? Simple enough

Server.Database.Schema.ObjectName

And you can find the lists of each of those pieces in

 
The breakdown is nice but how about usage?… Read more

0 comments, 119 reads

Posted in SQLStudies on 20 March 2017

Compressed backup errors and TF 3042

Compressing your backups has very few downsides. It’s usually faster (the additional time for compression is less than the time saved by reduced IO) and of course, the backups are smaller. I have run into a few issues, however.

On one occasion there was 75gb free on a drive, the… Read more

6 comments, 892 reads

Posted in SQLStudies on 16 March 2017

Limits vs Goals

It’s that time again. Kennie Nybo Pontoppidan (b/t) is the host this month and has a fun topic for us.

The daily (database-related) WTF

Database horror stories. Things that make your eyes pop and not in a good way. What a fun topic! But not an… Read more

0 comments, 801 reads

Posted in SQLStudies on 14 March 2017

Review of Azure Fundamentals class on Openedx

My company has recently adopted a Cloud first attitude. Now that’s different from Microsoft’s Cloud first. Microsoft is putting new features in the Azure first, then at regular intervals (the releases) those changes get pushed to on-premises versions of the applications. Our version means, in any new development check… Read more

0 comments, 181 reads

Posted in SQLStudies on 8 March 2017

Using GROUP BY instead of DISTINCT

Recently, Aaron Bertrand (b/t) posted Performance Surprises and Assumptions : GROUP BY vs. DISTINCT. In it he says he prefers GROUP BY over DISTINCT. He discusses the fact that GROUP BY will, in fact, under certain circumstances, produce a faster query plan. I highly recommend… Read more

6 comments, 1,645 reads

Posted in SQLStudies on 6 March 2017

Event storming! It’s raining emails!

One of the most common ways to get an event notification is by email. So what happens when you get 500 emails in a day and only one or two are actionable? Do you read every single email? Spending quite literally hours to find those one or two gems? Or… Read more

3 comments, 127 reads

Posted in SQLStudies on 1 March 2017

Queries with optional parameters

These are those queries where you are pulling for, let’s say, a first name, a last name, a state, and/or a city. Simple enough, until you notice that or. We might only get a first name, or a state and the query still needs to work. These queries are… Read more

10 comments, 2,549 reads

Posted in SQLStudies on 27 February 2017

What’s in my backup file?

Restoring a backup file is pretty easy right?

RESTORE DATABASE [Test] FROM DISK = 'C:\backups\backup.bak';

Ok, but what if more than one database backup is stored in that single backup file? Didn’t know you could do that?
Yep. You can.

BACKUP DATABASE [Test] TO DISK = 'C:\backups\backup.bak';
BACKUP DATABASE [AdventureWorks2014]…

Read more

0 comments, 349 reads

Posted in SQLStudies on 22 February 2017

Penny wise, pound stupid

Growing up my mother used this phrase quite a bit. Penny wise, pound stupid. (In case you didn’t know the pound is the British equivalent of the dollar.) Basically, it means paying attention to the small stuff at the expense of the big stuff. My favorite example of this… Read more

0 comments, 125 reads

Posted in SQLStudies on 20 February 2017

Older posts