Carriage Return in Microsoft Excel

Today, a colleague asked me to help him manipulate a spreadsheet to make it easier and faster for him to turn into an HTML table.

He had a spread sheet consisting of 10 or so columns, one of which housed a list of product codes, separated by semi-colons, in a single cell. What he wanted to do was keep the product codes in a single cell, but instead of having them separated by semi-colons, he wanted them each on their own line.

After some initial head scratching, and a little Googling, I came across the ALT command for a carriage return – Bingo! Once I had this, all that was required was a find replace to replace against the column to replace semi-colon with a carriage return (ALT + 010).

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Changing the TOP 1000 option

I often use a quick SELECT TOP 1000 to get a quick understand of what data is stored in an unfamiliar table, or to retrieve a small extract from a view to hand off to someone else in a spread sheet, and that’s… about it.

For those two purposes, do I really need 1000 rows? Certainly not for the first, and the second depends on someone else’s needs – and that’s why I like to lower this to SELECT TOP 100. It’s ever so slightly less intrusive as it’s having to ask for fewer rows, but still gets me enough data for me to get the information I want.

Here’s a very quick run down on how to change your “SELECT TOP” option to the number of your choosing:

  1. Open SSMS.
  2. From the menu bar, select Tools > Options.
  3. Go into the SQL Server Object Explorer node.
  4. From here, you can change your SELECT TOP option, as well as your EDIT TOP option
Top 1000 Option

Screenshot showing the aforementioned dialogue box with its default settings

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Using dm_db_missing_index_details can be dangerous

Using the DMV sys.dm_db_missing_index_details to identify and create “missing” indexes could be doing you more harm than good. If you read Microsoft books online carefully for information on this DMV, you’ll notice that under the remarks it states that data in the DMV is “updated when a query is optimized by the query optimizer”. Ok, big deal, who cares? You should! Why? Because every single query is passed through the query optimizer, and if it believes an index would be useful whilst optimizing the query, a record will be placed in sys.dm_db_missing_index_details.

What this means is that someone could have run a simple one-time query, with no intention of ever running it again, but a record will be placed into the DMV.

With that in mind, you should never, ever, blindly create an index suggested by this DMV. In doing so, you run the risk of solving a problem that isn’t really there. Having the extra, unnecessary, index will consume valuable overhead whilst performing maintenance tasks and slow the insertion of new data.

So, what is this DMV used for? It’s still a great little tool in helping identify useful indexes. If you’re working on a query and believe an index could improve your performance, it may be worth checking in here to see if many other queries could have been helped by the same index.

One of the most useful things you can do with dm_db_missing_index_details, is combine it with dm_db_missing_index_columns, allowing you to apply aggregate functions with ease and see exactly which columns are being utilized heavily and may require an index. I’ve written the below script which does exactly that:

SELECT TableName,
       ColumnName,
       INCLUDE,
       EQUALITY,
       INEQUALITY
 
FROM (
      SELECT D.statement [TableName],
	     C.column_name [ColumnName],
	     C.column_name,
	     C.column_usage
      FROM sys.dm_db_missing_index_details D
	   CROSS APPLY sys.dm_db_missing_index_columns(D.index_handle) C
     ) Cols
 
PIVOT
 
     (
      COUNT(column_name) FOR column_usage IN ([INCLUDE], [EQUALITY], [INEQUALITY])
     ) PVT
 
ORDER BY EQUALITY DESC

Running the above query will give you every single column in the instance you run it against which has, in one way or another, been identified by the query optimizer as requiring an index. The more useful part of the query is the count within the pivot, this is allows you to see how many times the optimizer has deemed an index was required, and for what purpose.

It’s important to remember that data is cleared down from this DMV every time the instance is restarted, so it has next to no use for a short while after a restart. This again plays a part in making this DMV a dangerous indicator of missing indexes.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

SSRS Default Date Parameter Functions

Several months ago, I made a list of date functions I commonly used to set the default value of an SSRS Parameter. I put it on our internal network for my colleagues, and today figured I may as well publish it on my blog too.

Function Description
Today
(00:00:00)
=DateSerial(Year(Now()), Month(Now()), Day(Now()))
1st of Current Month
(00:00:00)
=DateSerial(Year(Now()), Month(Now()), 1)
1st of Last Month
(00:00:00)
=DateSerial(Year(Now()), Month(Now())-1, 1)
1st of Current Year
(00:00:00)
=DateSerial(Year(Now()), 1, 1)
Last of Current Month
(23:59:59)
=DateAdd(“S”, -1, DateSerial(Year(Now()), Month(Now())+1,1))
Last of Last Month
(23:59:59)
=DateAdd(“S”, -1, DateSerial(Year(Now()), Month(Now()),1))

As with all things, make sure you test before implementing to ensure it does exactly as you need.

If you have a favorite you use regularly, add it to the comments and I’ll include it in the post.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

SQL Challenge: Snap To Nearest Factor

In my office, I often have people come over to my desk and say something like “Zach, I’ve got a SQL Challenge for you…”, and I love it. It gives me a chance to just take a break from what I’m doing and engage my brain in something different for a few minutes.

I thought it might be fun to post some of these challenges up on my site, just to see what people come up with – so, here’s the latest:

SQL Challenge: Snap To Nearest Factor

A user will select a whole number between 1 and 100 – You need to be able to “snap” that number to the nearest factor of another number.

Example one – Using a factor of 5 and a number of 19 you should return 20, which is the nearest factor of 5 to 19.

Example two – Using a factor of 9 and a number of 41, you should return 45.

Lets see what you can come up with. I’ll post my attempt in the comments at a later date as to not spoil the fun.

DECLARE @Factor INT = 5
DECLARE @Num INT = 19
Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

SQL In The City London 2012 was fun…

A few months ago I posted about a free SQL Server training event, SQL In The City London 2012, being put on in London by the good people of Redgate. Well, yesterday it took place and I thought I’d write up a quick post on the day.

The venue was great, it was held at One Wimpole Street in London. Very comfy seats, a nice clean building – but if I were to be critical of something, it would be the temperature. For some reason the air-con was on all day in the auditorium, which makes for some cold limbs when you’re just sat there listening to talks! Anyway…

This was the first conference I’d ever been too, and it was great to see people talk who are so clearly at the top of their game.

The talks were great, and I learned plenty of things to take back to the office with me on Monday morning. A brief outline of what I saw is below:

Brad McGehee: Database Maintenance Essentials – This was the first talk of the day, and it was my favourite one. Brad covered a few topics in the hour he had, including importance of proper index and statistic maintenance, some dangerous DMV’s, the importance of DBCC CHECKDB, and some other bits.

Steve Jones: Prepare for When Disaster Strikes – Steve hates it when his phone rings, because nobody ever calls him to see how he’s days going. If Steve’s phone is ringing, something’s broken (sounds familiar, right?). This talk was all about being prepared, and the importance of SLA’s. He showed off Redgate’s SQL Compare, which would be a very nice tool to have if you do quite a bit of publishing from a UAT environment to a production server – like I do! Steve is a funny dude, and he was really great with audience (even if he did insult the English’s sporting ability).

Steve Jones - Preparing for When Disaster Strikes

Steve Jones – Preparing for When Disaster Strikes

Brad McGehee: Proactive Data Growth Management – Another good talk from Brad, and this one really showed off Redgate’s SQL Backup Pro. I want it. Compressing your backups by UP TO 95%!! It also has the ability to encrypt backups, which is only available as an option in SQL Server with the use to Transparent Data Encryption (requiring enterprise edition) and lots of other cool features. Brad also stated some pretty incredible statistics, like the fact that in 2011 roughly 1.8 zettabytes of data was generated!

Brad McGehee - Proactive Data Growth Management

Brad McGehee – Data Growth Management

Steve Jones: AlwaysOn and Contained Databases in SQL Server 2012 – As someone who hasn’t really had an opportunity to play with SQL 2012 yet, I really liked seeing some of the newer features. Contained databases look like a big step forward, but I think I need to play around with it myself a little before saying much more than that.

Grant Fritchey: Forgotten Rings and Other Monitoring Stories – Aimed at showing off Redgate’s SQL Monitor, Grant showed us some of the more awkward to use DMV’s and DBCC commands, explained why they were generally useless as just a single figures – therefore requiring a benchmark. This is where SQL Monitor comes to life. It monitors the server constantly, providing benchmarks as well as historic figures for trend analysis.

It was a great day, and I’d recommend it to any DBA or Database Developer. I learned some genuinely useful things that I can apply in the real world, got to see some of the new 2012 features, a lot of cool industry tools I’ll be convincing my boss to let me buy, and three of the industries greatest DBA’s – Brad McGehee, Steve Jones, and Grant Fritchey – All SQL Server MVPs.

The people of Redgate did an excellent job organizing the event, and had thought of literately everything. I was a little disappointed that there were multiple talks going on at any one time, as I thought I’d miss out on some of the other talks I wanted to see. Luckily, this isn’t an issue at all – Redgate had all talks filmed, and are going to put them up on the web for attendees to view at a later date – brilliant. At the end of the event, they let you choose a free book from a selection, but it didn’t really matter which one you took, as they’re putting them all up on the site for free download as PDF at some point anyway!

Zach_Stagers_Redgate

Me, with all my free stuff!

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Returning vertical data in one horizontal column

Having data in a normalized form is great. It helps eliminate redundant data, and therefore helps save space in the database. In this blog post, I want to discuss how you can query a normalized database to return denormalized data – that is, return data from many rows in a single column. I’ve used this technique a number of times, and below a couple of the ways I’ve put it to use.

  • At my current company we have a custom service which runs on a nightly basis and sends emails for various reasons. Some emails are sent to the user in question, but also copies in their manager(s). If the user has multiple managers, we need to put both of their email addresses into a single “CC” column, separated by a semicolon, as well as the employees email address in the “To” column.
  • Over the last 6 or so months, I’ve been building my company a Holiday & Sick Day management tool in ASP.Net & C# as a means to teach myself a coding language. In the tool, a manager can view sick days on a calendar by hovering over a highlighted day (indicating there was a sickness on that day), and a tool tip will appear with each person who was ill on that day. The tool tip came straight from a stored procedure in one column, making it much easier for me to utilize in C#.

Now that I’ve given you a small taster of what it’s used for, I should probably show you some SQL!

Using the second example given above, imagine we have the below query:

SELECT S.SicknessDate,
	   P.Name   
 
FROM Person P
	 JOIN Sickness S ON P.PersonId = S.PersonId

Which gives us this dataset:

Returning vertical data in one horizontal column

 

 

 

Great, we have data! But, we only want 2 rows, one for ’2012-01-01′ containing Zach Stagers and Some Guy, and another for ’2012-03-01′ for Other Fella.

How do we do that? Through the use of a correlated subquery making use of FOR XML PATH and the [text()] column name. Here’s the new code:

SELECT DISTINCT 
	   S.SicknessDate,
	   (
		SELECT Name + ', ' [text()]
 
		FROM Person P1
			 JOIN Sickness S1 ON P1.PersonId = S1.PersonId
 
		WHERE S1.SicknessDate = S.SicknessDate
 
		FOR XML PATH('')
	   ) [Names]
 
FROM Person P
	 JOIN Sickness S ON P.PersonId = S.PersonId

And the new, easier to utilize, data set:

Returning vertical data in one horizontal column_FIXED

 

 

 
Perfecto! Just what we wanted! The FOR XML PATH subquery kindly does all of the work for us, and the distinct gets rid of duplicate rows caused when multiple people are ill on the same day. In the subquery, we use where the sickness date of the inner query equals the sickness date of the outer query, turn the result into XML (FOR XML PATH(”)), and tell it to just output it as plain text ([text()]).

I hope you find as useful an implementation for this as I have!

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Stored Procedure Definition Search

I just re-discovered this little script in my “Magic SQL” folder, and thought I’d share it.

Below is a script to search the definition of all stored procedures in a database for a specified value – useful in circumstances when you’re trying to figure out if a particular column is used anywhere in procedure code, or something like that.

USE DatabaseName
 
DECLARE @SearchString VARCHAR(500)
SET @SearchString = 'Search'
 
SELECT DISTINCT
       Name
 
FROM sysobjects SO
     JOIN syscomments SC ON SO.id = SC.id
 
WHERE SO.[type] = 'P'
      AND SO.category = 0
      AND SC.[text] LIKE '%' + @SearchString + '%'

To use the script, change the USE statement to point at the database you’d like to search, and change the value of @SearchString to the value you’d like to search for.

The result set will provide the name of any procedures containing the value defined within @SearchString.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Changing Bar Colour Based on SQL Instance Connection

I found something extremely useful whilst perusing the MSDN forums the other day – The fact that you can change the colour of the connection bar in SSMS based on the instance of SQL Server you’re connected to!

It’s extremely easy to implement, but helps differentiate which server you’re looking at when working with multiple servers in multiple tabs. This means you can have one colour for a local server, another for a development testing server, and another for a live environment.

To start using a colour, open up SSMS.

From the Connect to Server dialogue box, click “Options >>” to expand the window.

On the Connection tab, select the instance you’d like to configure.

Go to the Connection Properties tab, and tick “Use custom color” – this while make the “Select” button on the same line available. Click “Select”, and choose a colour you’d like to associate with this instance – note that you can change this as much as you like.

SSMS Custom Colour

SSMS Custom Colour

Now click “Connect” to connect to your chosen instance, and you should notice that the bar towards the bottom of the screen is colour you picked in the last step.

SSMS Bar Custom Colour

SSMS Bar Custom Colour

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

I’ve Joined Twitter – @ZachStagers

So, I’ve finally broken down and joined the Twitter ranks. Why? I thought it might be a good way to receive much more dynamic content by following people who like to talk about MS SQL as much as I do, thus helping me grow as a DBA. That’s the theory, anyway.

I’ve even unleashed my very first tweet on the world, explaining how I’m studying for my SQL Server 70-432 exam.

Please feel free to follow me – @ZachStagers, or if you’ve been using Twitter for awhile for the same reasons I’ve joined, please suggest some DBA’s to follow!

You can find my latest tweets, along with my latest MSDN Forum activity at the bottom of this page.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)