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

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

0 comments, 66 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, 99 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, 132 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,246 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, 597 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, 121 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

0 comments, 122 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, 172 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, 167 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,298 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,528 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, 136 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, 283 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, 182 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,438 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,603 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,418 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, 232 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,492 reads

Posted in SQLStudies on 16 August 2017

Older posts