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

What is the Object Explorer Details tab good for?

For a long time there I would see the Object Explorer Details tab show up when I opened SSMS (SQL Server Management Studio) and I would immediately close it. I couldn’t think of a single use for this view that seemed to be just another way to see the list… Read more

0 comments, 104 reads

Posted in SQLStudies on 17 October 2018

Highlighting sp_spaceused

I’ve been using sp_spaceused a lot recently so I thought I would bring it up again. Basically it’s a quick way to bring up the size of things. Specifically:

  • Tables
  • Indexed views
  • Service Broker queues
  • Databases

 
It’s the first and last that I use the most often. It’s… Read more

0 comments, 114 reads

Posted in SQLStudies on 15 October 2018

Which is faster? IN (list) or IN (SELECT FROM Temp)

If you’ve done much with IN (list) then you’ve realized that it basically translates out to

col=val1 OR col=val2 OR ....

You’ve probably also realized that you can throw all the values into a temp table and do this

SELECT *
FROM tablename
WHERE col IN (SELECT col FROM #temp

Read more

8 comments, 2,100 reads

Posted in SQLStudies on 11 October 2018

Letting the business bully you: T-SQL Tuesday #107

Before I start anything I want go give a disclaimer. I absolutely believe that the business should be involved in every stage of a project. When it comes down to it they are who we do the work for. That said, Introduction!

This is T-SQL Tuesday, the long-running blog… Read more

1 comments, 135 reads

Posted in SQLStudies on 9 October 2018

Generate a random number of children for each parent

I was asked an interesting question the other day.

Is it possible to get a different random number of rows back from each application of a cross apply?

The purpose is to create some random demo/testing information. This is one of those cases where an example may be needed for… Read more

4 comments, 1,735 reads

Posted in SQLStudies on 3 October 2018

SQL Homework – October 2018 – Time for an upgrade

When you created your home lab last September you probably installed a version of SQL 2016. Well, SQL 2017 was released almost immediately afterward and so has been out a year now. Not to mention that SQL 2019 has been announced. Don’t you think it’s time to do an… Read more

0 comments, 142 reads

Posted in SQLStudies on 1 October 2018

Data Types Word Search

Just for fun here is a word search with all of the data types available in SQL Server. Well, two exceptions. No datetime2 because there are no numbers in the word search, and sql_variant I had to remove the underscore. Last but not least, if it helps, there are 33… Read more

4 comments, 1,675 reads

Posted in SQLStudies on 26 September 2018

Escaping strings

No, I’m not talking about that nightmare everyone has where a bunch of strings are chasing you across a loom.

Not everyone has that nightmare? Just me? Really? Um .. well .. You know, let’s just forget I said anything.

Escaping strings is an important concept when working with computers.… Read more

0 comments, 745 reads

Posted in SQLStudies on 24 September 2018

You can’t DELETE TOP (x) with an ORDER BY

Did you know you can’t do this?

DELETE TOP (10)
FROM SalesOrderDetail
ORDER BY SalesOrderID DESC;

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword ‘ORDER’.

I didn’t. Until I tried it anyway. Turns out, it says so right in the limitations section of BOL.… Read more

8 comments, 2,974 reads

Posted in SQLStudies on 19 September 2018

How do I grant permissions to view users and their permissions?

tl;dr; VIEW DEFINITION

Every now and again you’ll have a user that needs to be able to see what permissions other users have. Not change them, just look at them. In the cases I’ve seen it’s usually a manager or something similar reviewing the database permissions. Or maybe someone doing… Read more

1 comments, 100 reads

Posted in SQLStudies on 17 September 2018

Using Table Valued Parameters with sp_executesql

Recently I did a presentation on dynamic SQL. In the presentation I pointed out the similarity of using sp_executesql to creating a stored procedure to do the same task. After the session I was asked: If that’s the case, can I pass a TVP (table valued parameter) into sp_executesql? Read more

3 comments, 2,151 reads

Posted in SQLStudies on 13 September 2018

What happens in a trigger stays in the transaction: T-SQL Tuesday #106

Anything that happens in a trigger happens within the same transaction of the command that called it.

Part of me feels like this should be pretty obvious but it’s also one of those things that’s so obvious that it becomes easy to miss.

Let me explain:

If you run an… Read more

0 comments, 125 reads

Posted in SQLStudies on 11 September 2018

I/O vs Logical I/O

tl;dr; While the difference is very important 90% of the time you won’t care and should just add the two numbers together.

I/O and Logical I/O are two pieces of information you will see all over the place.

Read more

0 comments, 2,339 reads

Posted in SQLStudies on 5 September 2018

SQL Homework – September 2018 – Backup and Restore continued.

In the very first SQL Homework post you were asked to take a backup. In fact it asked you to both take a backup and restore it. Because, I’ll be honest with you, if you can’t restore a backup then you might as well not have taken it. That said,… Read more

0 comments, 673 reads

Posted in SQLStudies on 3 September 2018

SQL Puzzle: Advent of Code

A few years back someone started the Advent of code. If you aren’t familiar with it, it’s an advent calendar of coding puzzles. Each day of December before Christmas there are two puzzles (with very Christmasy descriptions). You can do them with any language you choose, or even multiple… Read more

0 comments, 343 reads

Posted in SQLStudies on 29 August 2018

Copying formatted text out of a grid column.

I was reading a post today by Bert Wagner (b/t).

Displaying Long Variable Values in SQL Server

By the way, if you haven’t seen any of Bert’s vlog posts before they are well done and highly entertaining. Anyway, Bert was having difficulties displaying particularly long text… Read more

3 comments, 700 reads

Posted in SQLStudies on 27 August 2018

#SQLCareer 3/4 August 6th 2018

Last month Steve Jones (b/t) suggested that it might be helpful if those of us that work in the IT/database field shared four random days out of our lives. This is one of them.

The first two are:

 
Before work
Not… Read more

0 comments, 1,536 reads

Posted in SQLStudies on 22 August 2018

Nada to Speaker: Demystifying and Diminishing Dynamic SQL Difficulties

Last Thursday I spoke at NTSSUG (North Texas SQL Server User Group). This is my local group (only 1.5 hours away from me) and my second time speaking for them. I don’t speak frequently so I’m always more than a bit nervous, but the crowd was great, laughed in the… Read more

3 comments, 161 reads

Posted in SQLStudies on 20 August 2018

What are my permissions?

Permissions are a common concern. One of the most frequent requests I get is I need X, Y and Z permissions. And all too often the conversation goes like this:

Dev: I need write permission to this database.
Me: Ok.
Me: Checks their current permissions.
Me: You already have that… Read more

1 comments, 3,001 reads

Posted in SQLStudies on 16 August 2018

Complicated linked servers and a bunch of head-desks: T-SQL Tuesday #104

Wayne Sheffield (b/t) is our host this month for the long running blog party (104 months now and running!) started by Adam Machanic (b/t). In this case Wayne is asking us to talk about a time when we hit a brick wall Read more

0 comments, 251 reads

Posted in SQLStudies on 14 August 2018

Older posts