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.

Changing configuration settings with sp_configure

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

0 comments, 131 reads

Posted in SQLStudies on 22 September 2014

Triggers are not toys!

For the love of all that’s SQL, triggers are not toys! I’m not even talking about logon triggers or DDL triggers. I’m talking about plain old ordinary DML triggers.

Triggers are very useful tools but can easily cause all sorts of headaches. In fact I would generally advise… Read more

7 comments, 361 reads

Posted in SQLStudies on 17 September 2014

I SCHEMABOUND my scalar UDF and you won’t believe what happened next.

Sorry for the click bait but I just couldn’t resist, and to be fair I was pretty impressed with this little trick.

The other week I did a post on SCHEMABINDING and Diana sent me this great link on SCHEMABINDING UDFs. In it TomErv discribes SCHEMABINDING scalar UDFs that… Read more

1 comments, 289 reads

Posted in SQLStudies on 15 September 2014

What are trace flags?

Trace flags are one of those things that I’ve heard about more and more over the last five or six years. But only in the past year or so have I started to understand what they are and how to use them. I want to start out by saying that… Read more

0 comments, 4,929 reads

Posted in SQLStudies on 11 September 2014

T-SQL Tuesday #58: Passwords

It’s that time again. The second Tuesday of each month we have a blog party called T-SQL Tuesday. The host picks a subject and we all blog about it. It was originally started by Adam Mechanic (b/t) almost 5 years ago. This month Sebastian Meine ( Read more

2 comments, 5,253 reads

Posted in SQLStudies on 9 September 2014

Pausing an MSSQL Instance

I’m sure most of you have looked at the control options of the SQL services right? Start an instance, stop an instance, pause an instance. Start, stop, pause. Wait just a minute! Is this SQL Server or an mp3? (I just want to point out I started with a record,… Read more

9 comments, 1,179 reads

Posted in SQLStudies on 4 September 2014

Two simple commands that can be a big help in performance tuning.

The first thing that always comes to mind when discussing performance tuning is query plans and rightly so. They are the best information about what a query is doing and so how to improve it. However there are a couple of little commands that can be a big help too.… Read more

4 comments, 1,557 reads

Posted in SQLStudies on 2 September 2014

Finding a job!

I’ve been told over the years that I’m pretty good at finding jobs. I mean in the last 25 years and 15 jobs or so (some contract positions) I’ve never been out of work for more than a month and a half at a time. I’ve always thought the methods… Read more

2 comments, 5,756 reads

Posted in SQLStudies on 27 August 2014

The amazing never shrinking heap

This is a quick demo of a little “trick” with heaps I’ve known about for a couple of years. However until recently I could never duplicate it on purpose. (You can read that as I’ve had a production problem bite me in the …. repeatedly.) At least I couldn’t duplicated… Read more

0 comments, 176 reads

Posted in SQLStudies on 25 August 2014

Two years!

Two years ago today I began my blog with a post about the DEFAULT keyword. I set out with the goal of building a blog I could be truly proud of in three years. One that was well liked and provided value to the community.

Little did I imagine… Read more

6 comments, 374 reads

Posted in SQLStudies on 20 August 2014

Generating a restore script

In order to speed up our backups on a large database our team decided to stripe the backup files. In case you weren’t aware of this particular backup feature it his means that a single backup is written to multiple files which can dramatically speed up your backups and restores.… Read more

3 comments, 6,629 reads

Posted in SQLStudies on 18 August 2014

More than sp_help

If you have worked with SQL Server for very long you have probably run across the extremely useful system function called sp_help. This handy little function will return a list of the objects in the database if you don’t pass in a parameter. If you do pass in a… Read more

1 comments, 217 reads

Posted in SQLStudies on 14 August 2014

T-SQL Tuesday #57: SQL Family

This month’s T-SQL Tuesday topic is SQL Family and is brought to us by Jeffrey Verheul (b/t). It’s an amazing topic, but a difficult one for me to put into words. SQL Family is a lot of things, but at its base it is a group… Read more

1 comments, 102 reads

Posted in SQLStudies on 12 August 2014

SchemaBinding – What & Why

What

When you use the SchemaBinding keyword while creating a view or function you bind the structure of any underlying tables or views. So what does that mean? It means that as long as that schemabound object exists as a schemabound object (ie you don’t remove schemabinding) you are limited… Read more

6 comments, 149 reads

Posted in SQLStudies on 6 August 2014

Deny vs Revoke

Quick quiz. Which of these two commands is the opposite of GRANT?

  1. DENY
  2. REVOKE

 
Well lets start with some definitions

  • GRANT – Grants permissions on a securable to a principal.
  • DENY – Denies a permission to a principal.
  • REVOKE – Removes a previously granted or denied permission.

 
While… Read more

1 comments, 7,238 reads

Posted in SQLStudies on 4 August 2014

The clustered index columns are in all of the non clustered indexes.

Did you know that whatever columns you pick as your clustered index will be included in any non clustered indexes on the same table? But don’t take my word for it. Let’s take a look!

First things first I’m going to use some AdventureWorks2012 tables to make a test table. Read more

9 comments, 8,071 reads

Posted in SQLStudies on 30 July 2014

What is ANSI_NULLS and why will I be glad when it finally goes away?

Recently I wrote about what it means that a value is NULL. Right at the beginning I mentioned ANSI_NULLS and said I would discuss it later. Well it’s later.

In the previous post I discussed how NULL is basically “unknown”. And any time you compare something to “unknown” you… Read more

10 comments, 9,007 reads

Posted in SQLStudies on 28 July 2014

Why am I getting a primary/unique key violation?

This may seem like a question with a simple answer but there is a bit more to it than you might think. In fact I know of 3 possible reasons (and there may be more I don’t know) for seeing a primary key error. Technically they occur for any unique… Read more

3 comments, 161 reads

Posted in SQLStudies on 23 July 2014

Add them to ALL the roles!

I seem to get a lot of permissions questions these days and one of the more frequent ones goes along these lines “I still don’t have the right permissions on database xyq.” So of course the first thing I do is use my handy dandy sp_dbpermissions stored procedure to check… Read more

3 comments, 7,984 reads

Posted in SQLStudies on 21 July 2014

Yes, Virginia, you can have too much white space.

When I was in college and taking programming classes (back in the days when “a T-Rex ate my 5.25 inch floppy” was a reasonable excuse) we were taught that since the compiler didn’t really see white space we should use it to format our code. And that is absolutely correct.… Read more

10 comments, 8,432 reads

Posted in SQLStudies on 16 July 2014

Older posts