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.

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

4 comments, 89 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, 102 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, 112 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, 83 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

5 comments, 125 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, 5,393 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, 6,865 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

9 comments, 8,327 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, 141 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,922 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,386 reads

Posted in SQLStudies on 16 July 2014

What does it mean that a value is NULL?

Let’s start by assuming that ANSI_NULLS are ON. If you aren’t sure what ANSI_NULLS are exactly, don’t worry, I’ll be going over that in some detail in a future post. However Microsoft tells us that ANSI_NULLS will always be ON in the near future. So we are not going to… Read more

10 comments, 9,569 reads

Posted in SQLStudies on 14 July 2014

db_ddladmin and the SSMS table designer

If you want to grant a user the ability to create/alter/delete any table, SP, function etc in a database you have a several options. For example:

  • You can grant all of the CREATE permissions either to the database itself or to all of the schemas.
  • You can add the user…

Read more

0 comments, 142 reads

Posted in SQLStudies on 10 July 2014

T-SQL Tuesday #56: Assumptions

I assume that since it’s the second Tuesday of the month that it’s time for T-SQL Tuesday. And you can be certain that Dev Nambi(b/t) has decided to make Assumptions the topic of the 56th T-SQL Tuesday.

I generally like to start with definitions when discussing… Read more

6 comments, 141 reads

Posted in SQLStudies on 8 July 2014

Argenis and the unicorn hoodie

Early yesterday Kirsten Benzel(b/t) announced something very exciting, at least exciting to me. Argenis Fernandez(b/t) has agreed to wear a unicorn hoodie to the 2014 Pass Summit.

Why is this exciting? A number of months ago Kirsten photo-shopped Argenis into this self… Read more

0 comments, 111 reads

Posted in SQLStudies on 2 July 2014

Make BOL your friend

One of the most powerful tools we have as users of SQL Server is Books Online (BOL).  Whether you work mainly as an admin, a developer or in BI, Microsoft has provided a HUGE amount of information for you to use.  But BOL is by no means your only resource… Read more

0 comments, 220 reads

Posted in SQLStudies on 30 June 2014

Dealing with a long string

Every now and again you have to put a really long string (more than 8000 characters). Dynamic SQL is the most frequent example where I see this but I do see it elsewhere as well and it’s very easy to make a simple mistake. This is caused by the fact… Read more

4 comments, 802 reads

Posted in SQLStudies on 25 June 2014

sp_SrvPermissions & sp_DBPermissions V5.0

These are a couple of stored procedures I wrote to help me with security research. Each of the stored procedures returns three data sets.

  1. A list of principals and some basic properties about them.
  2. Role membership
  3. Object/Database/Server level permissions

 
Each of the datasets has a set of do/undo scripts… Read more

2 comments, 128 reads

Posted in SQLStudies on 23 June 2014

Aliasing a SQL Server: When it works, when it doesn’t and when it may be your problem.

Creating an alias for a SQL Server is fairly easy and there are several ways to do it. Configuration Manager is my personal favorite. Open up configuration manager and select the SQL Native Client xx Configuration. Under that you will find Aliases.

From here you can add, update or delete… Read more

10 comments, 1,352 reads

Posted in SQLStudies on 18 June 2014

I don’t want to grant permission to all the tables in the database at once.

A couple of weeks ago I did a post on granting or denying permissions to all the tables within a database. However sometimes you don’t want to grant permissions to the whole database at once. This is still pretty easy but there are no built-in roles to do it.… Read more

1 comments, 266 reads

Posted in SQLStudies on 16 June 2014

Older posts