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.

Archives: June 2013

Using OUTPUT to back up a change

How will you back out if your change doesn’t work?! Always take a backup! There should always be a back out script to go with any change script! These are things that have been drilled into me time and time again. Sometimes by management, sometimes by bitter experience. If I’m… Read more

1 comments, 885 reads

Posted in SQLStudies on 26 June 2013

Metadata Functions (OBJECT_NAME for example)

What is a metadata function? According to BOL a metadata function is one that returns data about the database or its objects. Why am I mentioning metadata functions? Because they save me a boatload of effort! I love the system views, functions etc. I’ve made extensive use of them during… Read more

0 comments, 272 reads

Posted in SQLStudies on 24 June 2013

Disaster Recovery’s big brother Operational Recovery

I’m going to get slammed for the title by someone but I stand by it. First some definitions.

Disaster Recovery (DR) is recovering a database (or part of one) in the wake of a “disaster”. Examples of a disaster: one of the drives goes bad, a database is corrupted somehow,… Read more

5 comments, 311 reads

Posted in SQLStudies on 20 June 2013

ORDER BY the numbers

Have you ever needed to order by a calculated column? You might have written it something like this:

SELECT LoginID, YEAR(HireDate) AS HireYear, 
	MONTH(HireDate) AS HireMonth
FROM HumanResources.Employee
ORDER BY YEAR(HireDate), MONTH(HireDate),
	LoginID

Did you know you don’t have to put the calculations in the ORDER BY? You don’t even… Read more

10 comments, 2,149 reads

Posted in SQLStudies on 17 June 2013

Best practice recommendations for writing Dynamic SQL

Note this is not “Best Practices when USING Dynamic SQL”. These are just good habits I’ve come up with over the years to make sure that when writing my dynamic SQL it breaks less often. Note I don’t say “doesn’t break ever”, dynamic SQL is tricky stuff. The best you… Read more

10 comments, 3,770 reads

Posted in SQLStudies on 12 June 2013

Using the OVER clause

Over the last few years I’ve learned quite a bit about different techniques in SQL Server. This particular one has been available since SQL 2005 and looks like it could be really handy.

Let’s say I want to calculate the total sales for by month and at the same the… Read more

0 comments, 288 reads

Posted in SQLStudies on 10 June 2013

Easy way to generate a restore script.

Easy way to generate a restore script.

I was asked today if I had a canned restore script handy. I don’t. I’ve never really had a reason to. Generating a restore script, even a complicated one is really very easy if you know how.

For those of you wondering, there… Read more

0 comments, 338 reads

Posted in SQLStudies on 5 June 2013

Pattern searches beyond ‘%string%’.

I was asked today to take a table with a social security column and put all 0s if the column had any letters in it. This is the typical social security column that actually has more rows with ‘XXX-XX-XXXX’ than it does valid SSNs. There are even cases that look… Read more

4 comments, 301 reads

Posted in SQLStudies on 3 June 2013