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

Restrict the usage of a SQL Server Authenticated Application Id

SQL Server login ids may not be the most secure thing in the world but they are likely to be around for quite a while and one of the more common uses of them is as an application id. An application uses a SQL Server id to connect to SQL… Read more

1 comments, 185 reads

Posted in SQLStudies on 6 December 2017

SQL Homework – December 2017 – View the log

Last homework of the year. Last month was to create a view. This month it’s time to take a look in the log! No, not the transaction log. The SQL Server Log. Where error events, backup events, startup events etc, etc get stored. If something goes wrong on your… Read more

0 comments, 115 reads

Posted in SQLStudies on 4 December 2017

SQL Puzzle – Word Scrambler

I’ve been writing crosswords for the last few months and to change things up I thought I would do a word scramble. Well, step one was to create a function that would scramble the words for me. Below is my attempt at creating a word scrambler. Your task (should you… Read more

2 comments, 175 reads

Posted in SQLStudies on 29 November 2017

Closing all of the connections to a database

Doing a database restore may not be the most common task a database professional will ever do, but it’s a lot more frequent than you might think. Operational restores are not exactly uncommon for example. Now, when you are doing a recovery like this, what is common is that there… Read more

3 comments, 1,682 reads

Posted in SQLStudies on 27 November 2017

Why shouldn’t I shrink my database data file?

A while back I did a post about why you shouldn’t shrink your data file. This one is going to be similar in some ways, different in others.

TL;DR: It’s pretty pointless and will almost certainly cause performance issues.

Let’s start by asking why you might want to shrink… Read more

2 comments, 184 reads

Posted in SQLStudies on 20 November 2017

Variables in Dynamic SQL

I had someone ask me about this the other day. Specifically getting variable data out of a dynamic SQL statement. I should point out here that I really enjoy dynamic SQL. I find it fairly intuitive and always enjoy the challenge, so I’ve gotten pretty good at it. So I… Read more

0 comments, 791 reads

Posted in SQLStudies on 16 November 2017

It takes a village

Ewald Cress (b/t) is our host this month for Adam Machanic’s (b/t) monthly blog party T-SQL Tuesday. Having just gotten back from the exaustion of PASS Summit he felt that a less technical subject but one more community related was in… Read more

1 comments, 104 reads

Posted in SQLStudies on 14 November 2017

More Collation Confusion. (tempdb)

I’ve talked about Collation Confusion before. We had the dev and test instances at one collation and the production instance was another collation. Wow, was that a headache.

Well, we had a similar issue recently. This time we had a vendor reporting the following error:

Msg 207, Level 16, State… Read more

1 comments, 162 reads

Posted in SQLStudies on 8 November 2017

DBA Definitions: FILLFACTOR

It was recently brought to my attention that not everyone knows everything. This was a shock. Everyone is born knowing the basics though right? Right??

I was sure everyone at least knew what FILLFACTOR is. But based on some rumors I’ve been hearing, it seems not.

What exactly is FILLFACTOR?… Read more

1 comments, 261 reads

Posted in SQLStudies on 6 November 2017

SQL Homework Nov 2017: Create a view

Views are a highly useful tool for abstracting how you see the data stored in tables. At their simplest, they are just a stored query that can be run without needing to know the contents of the query itself. Common uses include:

  • Joining tables together
  • Restricting data that can be…

Read more

3 comments, 1,191 reads

Posted in SQLStudies on 1 November 2017

Haunted SQL: The ghost of Management Studio

Have you ever had a query change performance unexpectedly? Intellisense all of a sudden stops working, or gives inconsistent results? AutoSave should have worked and saved your work after that unexpected (unexplained) crash? Ever had a query that was taking a long time and you can’t figure out why? Then… Read more

2 comments, 190 reads

Posted in SQLStudies on 30 October 2017

Schema only backup or schema only DB copy

Every now and again you have a database where you don’t necessarily need the data. It can easily be re-loaded or re-created. Or maybe you need a copy of the database, but since you don’t need the data, a backup of the 750gb database seems a bit excessive. (I see… Read more

0 comments, 382 reads

Posted in SQLStudies on 25 October 2017

When is it a good idea to consolidate multiple instances?

In this age of cost-saving after cost-saving, one way you may be looking at saving money is by combining multiple instances into one in order to save on licensing/machine costs. Or possibly you have a new database and want to decide if it can reside on an existing instance or… Read more

1 comments, 1,817 reads

Posted in SQLStudies on 23 October 2017

Updateable CTEs

CTEs are cool things. You can essentially create one or more in-line view(s) within your query. One thing that isn’t overly well known is that you can actually update the data within the CTE. No, I don’t mean using using the UPDATE statement with a CTE but actually running the… Read more

4 comments, 1,332 reads

Posted in SQLStudies on 18 October 2017

Permissions required for developing with Temporal Tables

Temporal tables are one of those new (2016+) cool features that recently came across my desk. Basically, a temporal table is a combination of auditing columns (createdate) and a history table.

Quick example using code from BOL.

CREATE TABLE Department   
(    
     DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   ,…

Read more

1 comments, 1,231 reads

Posted in SQLStudies on 16 October 2017

sys.objects VS helper functions (OBJECT_ID, OBJECT_NAME, etc)

I’ve you’ve done much work with the system views (DMVs for example) then you’ve had to translate an object_id into a schemaname and objectname and vise versa. To do that you’ve either used the functions OBJECT_ID, OBJECT_SCHEMA_NAME, and OBJECT_NAME or sys.objects and sys.schemas.

So what’s the difference?… Read more

1 comments, 194 reads

Posted in SQLStudies on 11 October 2017

Small identity columns

We frequently talk about dealing with outgrowing INT identity columns. What we don’t talk about all that often is small tables. Where we don’t even need an INT. An IDENTITY column can be any of the following data types tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0). INT and… Read more

5 comments, 1,497 reads

Posted in SQLStudies on 9 October 2017

The Alpha and the Omega. The BEGIN and the END.

The BEGIN/END block is a fairly standard thing if you’ve ever done any coding, but it never hurts to do a little review. Not to mention that there are always people just starting to learn about a subject.

So what is a BEGIN/END block? They are boundaries that define… Read more

2 comments, 710 reads

Posted in SQLStudies on 4 October 2017

Haunted SQL

I decided that for Halloween this year it would be fun to tell SQL ghost stories. When I tweeted about it I got a fair amount of positive response so I thought I would make it a party and lay down a few rules.

Content

  • Ghost stories, the type you…

Read more

0 comments, 164 reads

Posted in SQLStudies on 3 October 2017

SQL Homework – October 2017 – Sample/Test Database(s)

Last month your homework was to set up your own lab. Now it’s time to put a sample database or two on at least one SQL instance. Microsoft has provided copies of AdventureWorks for years and recently switched over to Wide World Importers.

Download the database backups

Read more

1 comments, 170 reads

Posted in SQLStudies on 2 October 2017

Older posts