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

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, 171 reads

Posted in SQLStudies on 2 October 2017

SQL Puzzle – Prime Numbers

My goal here is to have something fun (and hopefully educational/thinky) (and yes, I did just make up the word thinky, live with it) at the end of each month. So this month it’s a puzzle. Calculate the first 10 prime numbers.

Definition of a prime number:

A Prime Number…

Read more

6 comments, 224 reads

Posted in SQLStudies on 27 September 2017

Saving and restoring session options settings

There are a fair number of options settings. ANSI_NULLS, ARITHABORT, QUOTED_IDENTIFIER, etc. Each session has its own set of configurations. They are initially set based on the user settings system configuration, then the various connection programs (SSMS for example) can override that, then the various SET commands can override that.… Read more

4 comments, 202 reads

Posted in SQLStudies on 25 September 2017

Uniquifier is a rather unique word isn’t it?

tl;dr; The uniquifier is used to make a non-unique key in a clustered index unique.

Uniquifier is a rather funny name, and yet it’s very descriptive. If there is a key that must be made unique then SQL server will add a uniquifier to, well, make it unique.

Now, why… Read more

8 comments, 1,381 reads

Posted in SQLStudies on 18 September 2017

Help! My query is too fast!

Said no one ever. Well, maybe. I have had occasions where I needed a brief pause in the middle of a batch. For example, if I’m running a big delete I might create a loop and delete in batches. If I’m having to run this for a while on an… Read more

1 comments, 2,628 reads

Posted in SQLStudies on 14 September 2017

Turn on/off Azure VMs with Powershell

T-SQL Tuesday has rolled up on us yet again. This month it’s Rob Sewell’s (b/t) turn to host and picked a subject Lets get all Posh!.

I’m a little confused, to be honest. Has anyone else heard of PoSH? No? I didn’t think so. Read more

1 comments, 177 reads

Posted in SQLStudies on 12 September 2017

There EXISTS a place where SELECT 1/0 doesn’t return an error.

Kendra Little (b/t) reminded me of this fun little trick (with fairly important ramifications) in her latest quiz on logical joins (Q7)

Using AdventureWorks2014

SELECT *
FROM Sales.SalesOrderHeader as head
WHERE EXISTS (
	SELECT 1/0 as y
	FROM Sales.SalesOrderDetail AS det
	WHERE det.SalesOrderID = head.SalesOrderID);

At… Read more

3 comments, 323 reads

Posted in SQLStudies on 7 September 2017

SQL Homework – September 2017 – Create a home lab

As I wrote my post about installing the latest version of SSDT I realized that my homework posts missed something important at the start. It’s pointless to ask someone to create a table when they don’t have someplace to do it.

There is no need to go as far as… Read more

0 comments, 219 reads

Posted in SQLStudies on 5 September 2017

You might be a DBA if

I’d been trying to think of a fun post to write and an homage to Jeff Foxworth’s You might be a redneck series seemed like a good idea. To that end, I create a hashtag #YouMightBeADBA and wrote my post on twitter :). Of course, it turns out that the… Read more

5 comments, 1,506 reads

Posted in SQLStudies on 30 August 2017

Create an empty table from a SELECT statement but without the IDENTITY.

A while back I did a post about creating an empty table using a SELECT statement. Basically doing something like this:

SELECT TOP 0 * INTO tableNameArchive FROM tableName

will create a new table with the exact same structure as the source table. It can be a really handy… Read more

3 comments, 2,704 reads

Posted in SQLStudies on 28 August 2017

Changing the version of an SSIS package.

Of all of the annoying parts of SSIS, the major version sensitivity has to be the most annoying. Let’s say you create a package in SSDT 2012. You later open that same package with SSDT 16 without thinking about it. SSDT automatically upgrades your to package version 8 (SQL 2014… Read more

2 comments, 1,508 reads

Posted in SQLStudies on 23 August 2017

Deploy an SSIS project to the SSIS catalog.

You’ve installed SSDT, created an SSIS project, created any packages you need and now you want to place that project and its packages onto the SQL instance so you can run them. FYI there are several ways to do this using package or project deployment, I’m just going… Read more

0 comments, 260 reads

Posted in SQLStudies on 21 August 2017

Creating an SSIS project

Now that SSDT (SQL Server Data Tools) has been installed the next step is to create a project. First, open up Visual Studios 2015 or SQL Server Data Tools 2015 (SSDT is really just a set of modules added to Visual Studio).

Menu File -> New -> Project

Here we… Read more

0 comments, 1,543 reads

Posted in SQLStudies on 16 August 2017

Installing the (currently) latest Data Tools

I’m working on a project right now and it’s going to require me not only to refresh my SSIS skills a bit but learn a few things that are only available with the latest tools. So everyone ready for a few SSIS posts? Good. Me too!

Step One and Post… Read more

5 comments, 262 reads

Posted in SQLStudies on 14 August 2017

500 posts

Well, that’s something to be proud of. This is my 500th post on this blog. Not including a handful of articles and posts on other people’s blogs. I have to admit, when I started (almost exactly 5 years ago) I don’t think I expected to have reached this point. In… Read more

6 comments, 244 reads

Posted in SQLStudies on 10 August 2017

Interview Patterns and Anti-Patterns Combined

It’s that time again. T-SQL Tuesday. The monthly blog party started by Adam Machanic (b/t) seven and a half years ago!! We have a host, who picks a topic (on the first Tuesday of the month) and then we all write about that topic over the… Read more

4 comments, 194 reads

Posted in SQLStudies on 8 August 2017

SQL Homework – August 2017 – Create a table

If you didn’t see it last month I’ve started doing monthly SQL Homework. The first month was backups, this month it’s tables.

Here is your homework for August!

  1. Create a table with at least 3 columns.
  2. Add 5 rows to the table.
  3. Add one column to the table.
  4. Update…

Read more

4 comments, 241 reads

Posted in SQLStudies on 2 August 2017

DBA Dice

I’ve always thought it would be fun to have a DBA themed magic 8-ball. Now you may not be aware of this but the inside of a magic 8-ball is actually a 20 sided die. So since a custom magic 8-ball is more than this cheap geek is willing to… Read more

0 comments, 219 reads

Posted in SQLStudies on 31 July 2017

Performance is bad. Did you change anything recently? No. Are you sure?

Ever have this conversation?

Dev: Hey, can you help me? The performance on my application is terrible all of a sudden.
DBA: Sure thing. Has any code changed recently?
Dev: No. Nothing’s changed at all. Everything is just suddenly slower.
DBA: Are you sure?

Want to bet some code has… Read more

3 comments, 2,885 reads

Posted in SQLStudies on 26 July 2017

Newer posts

Older posts