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

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, 100 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

0 comments, 73 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, 135 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, 231 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

2 comments, 1,101 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

1 comments, 152 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, 352 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,763 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

3 comments, 1,265 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,185 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, 177 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,435 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, 689 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, 152 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, 155 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, 208 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, 193 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,359 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,604 reads

Posted in SQLStudies on 14 September 2017

Older posts