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.

The many uses of CROSS APPLY

Over the last few years of studying SQL I’ve noticed 4 different uses for the command CROSS APPLY.

In the first use I ever saw, and certainly the one I see the most commonly, CROSS APPLY is used to run a function for each row of the query. This is… Read more

0 comments, 175 reads

Posted in SQLStudies on 20 May 2013

CREATE SCHEMA can do what?!?!?

So I was studying for the 70-451 and ended up reading the BOL entry for CREATE SCHEMA. I honestly can’t remember why, but what I do remember is thinking “CREATE SCHEMA can do what?!?!?” but with maybe a few more exclamation points.

Now I had always thought that CREATE SCHEMA… Read more

3 comments, 2,261 reads

Posted in SQLStudies on 15 May 2013

Dragging a name from the object explorer to a query window

In the same vein as Steve Jones’ series on Customizing SSMS this is a fairly basic trick, but one that not everyone appears to know.

A fairly standard layout of SSMS looks like this.

If you drill down, to say the table list, you can left-click and drag a table… Read more

3 comments, 2,032 reads

Posted in SQLStudies on 13 May 2013

Free SQL Books

I was looking for a way to put BOL on my Kindle when I found a few good links I thought I would share. FYI If anyone knows how to put BOL on an Android device it would be great!

Here is a link to large list of Microsoft books Read more

4 comments, 282 reads

Posted in SQLStudies on 8 May 2013

GO

Did you know that the GO command isn’t really part of T-SQL? It’s what’s called a batch separator. And in case you were wondering that’s why you can’t put GO inside of stored procedures, functions etc.

So what does the “batch separator do? Well, it separates batches of course. Working… Read more

2 comments, 121 reads

Posted in SQLStudies on 6 May 2013

Comparing an inner join vs a subquery

I was reading through Stackoverflow today and saw an interesting question. SQL inner join vs subquery. The user was comparing 3 queries and wondered why the first took significantly longer than the other 2.

Here are the queries:

Query 1: SELECT * From TabA INNER JOIN TabB on TabA.Id=TabB.Id…

Read more

10 comments, 3,611 reads

Posted in SQLStudies on 3 May 2013

Well I finally passed the 70-451

So I am now an MCITP Database Developer for SQL 2008. This is a big milestone for me. A few years ago I was terrified to take any of the exams. They are expensive, and I just knew there was no way I was going to pass. It would be… Read more

2 comments, 158 reads

Posted in SQLStudies on 1 May 2013

INFORMATION_SCHEMA the un-sung system views

I spend a reasonable amount of time looking through various forums, answering some questions and reading the answers to others. One of the things I’ve noticed is that while I frequently see system views referenced I only very rarely see the INFORMATION_SCHEMA views mentioned. This seems rather strange to me… Read more

0 comments, 152 reads

Posted in SQLStudies on 29 April 2013

How do I tell the stored procedure name from inside the stored procedure?

I’ve occasionally had the problem of trying to put “smart” logging messages into a process. You know the ones, you’re trying to set up logging for a process and you really want to create a somewhat generic piece of code that you can throw into each stored procedure. Something that… Read more

1 comments, 135 reads

Posted in SQLStudies on 24 April 2013

Who writes your documentation?

I’ve been thinking recently about who writes the best documentation. Not including a professional technical writer (although they actually do fit into my conclusion). On first thought I would have thought the SME (subject matter expert) would of course write the best documentation, because they understand the processes best. Upon… Read more

2 comments, 119 reads

Posted in SQLStudies on 22 April 2013

Calculating the length of a string (Len vs DataLength)

This one is basic but still something that can catch you unawares if you aren’t careful. Most DBAs and developers I know use the function LEN to calculate the length of a string. Little wonder since that is exactly what this function is for. However not everyone realizes the small… Read more

0 comments, 32 reads

Posted in SQLStudies on 15 April 2013

How do I use a variable in an IN clause?

I see this question in one form or another a lot. I’ve seen this or something like it probably half a dozen times in the last couple of weeks on the forums I read. To the point where it even showed up in a dream the other night (I know,… Read more

0 comments, 151 reads

Posted in SQLStudies on 8 April 2013

UNPIVOT a table using CROSS APPLY

I generally spend part of each day looking through http://www.stackoverflow.com, http://dba.stackexchange.com or http://www.sqlservercentral.com. I read through questions that have already been answered if they look interesting and answer questions where I can. This is a great way to not only keep up my basic skills but to collect… Read more

4 comments, 2,004 reads

Posted in SQLStudies on 1 April 2013

How do I move a SQL login from one server to another without the password?

This is an uncommon task but one that does turn up every once in awhile. A SQL login has to be moved from a development server to a test server, test to prod etc. Or maybe a lateral move to a new server. And frequently the DBA doesn’t and/or shouldn’t… Read more

6 comments, 2,076 reads

Posted in SQLStudies on 25 March 2013

Using Templates

Templates are one of those really handy tools that most DBAs I know of have either never heard of or just don’t use much. I have to admit I fall into the second category myself. I will break out templates occasionally when I’m working with something I don’t do much.… Read more

0 comments, 195 reads

Posted in SQLStudies on 18 March 2013

The STUFF function

While I was at the 2011 Pass Summit I was given the following piece of code to create a comma delimited list.

 SELECT files.database_id, db.name AS DatabaseName,
		STUFF((SELECT ', ' + names.name
				FROM sys.master_files names
				WHERE names.database_id = files.database_id
				FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
			, 1, 2, '') AS NameList
FROM sys.master_files…

Read more

0 comments, 177 reads

Posted in SQLStudies on 14 March 2013

What is mssqlsystemresource?

If you are lucky you’ve never seen an error along the lines of:

The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'. 

But let’s face it, luck runs out. So you may want to know what on earth this mysterious fifth system database is. As of… Read more

1 comments, 188 reads

Posted in SQLStudies on 4 March 2013

Script to clean up “Windows” logins no longer in AD

I was scanning http://dba.stackexchange.com and ran across the following question:

http://dba.stackexchange.com/questions/31478/sql-server-script-to-delete-accounts-no-longer-in-active-directory

Basically the OP wanted to know how to get rid of “Windows” or AD logins. Mike Fal answered with a very cool script I just had to blog about. Basically it scan’s through the Windows logins in sys.server_principals and… Read more

7 comments, 1,726 reads

Posted in SQLStudies on 1 March 2013

Insert multiple values sets into a table in one command

The INSERT command has two distinct ways to load data into a table.

INSERT INTO Table (field1, field2) VALUES ('string1','string2')

And

INSERT INTO Table (field1, field2)
SELECT field1, field2 FROM Table2

Both of these are very handy and have been around as far back as I can remember. Historically you… Read more

0 comments, 225 reads

Posted in SQLStudies on 28 February 2013

DROP and CREATE vs ALTER

When writing a script to change a piece of code, say a stored procedure or view, there are 3 basic options.  I’ll start at the outset by saying I use all 3 and that they each have their pluses and minuses.  I’m not trying to advocate one or another, just… Read more

10 comments, 2,642 reads

Posted in SQLStudies on 25 February 2013

Older posts