Using FOR XML to create arrays, JSON and JavaScript

Every new project brings its own challenges and pitfalls. New projects also give you a chance to be creative in finding solutions, and try new things. This keeps it interesting, and allows you to learn new things. And sometimes, the things you avoided for all these years, turn out to be your best option.

 
A missing feature in SQL Server
For a project, we needed to generate JSON or JavaScript from T-SQL. Even though the whole world seems to use JSON at this point, there is no feature to generate JSON from T-SQL or SQL Server. There are more than enough people who would like such a feature (just look at the Connect item made for this). Maybe they’ll put it in the vNext of SQL Server, who knows. But at this moment, we need to generate the JSON ourselves.

 
Generating the data array
First of all, we need to generate an array or list. The problem with this, is the number of rows. For example, if you only have 2 rows per product, you can join the table onto itself, and concatenate the columns like that. But what if you have more rows per product, or you don’t know how many rows per product are returned?

One of the ways to resolve this, is by using the FOR XML clause. You can use this in a CROSS APPLY, to create an array/list of data, grouped by another column.

So let’s create an example, by using airlines. When you fly to another state, country or continent, you’ll book a flight with an airline. Those airlines have a name, a departure airport code, and of course a ticket price. Let’s put this data in a table:

CREATE TABLE #TMP_AirlineTickets
  (ID INT IDENTITY(1,1),
   Airline VARCHAR(10),
   Departure_Airport_Code VARCHAR(5),
   Price DECIMAL(10,4))


INSERT INTO #TMP_AirlineTickets
  (Airline, Departure_Airport_Code, Price)
VALUES
  ('BA', 'RTM', 10.00),
  ('KLM', 'AMS', 125.00),
  ('BA', 'LHR', 15.00),
  ('KLM', 'BCN', 50.00),
  ('KLM', 'BHX', 75.00)

 
When we query this data, we could easily use a sub-select to query. But because I want my query to be readability, and good performing, I used the FOR XML clause:

SELECT
  AT.Airline,
  STUFF((SELECT
            CONCAT(',', SUB.Departure_Airport_Code)
         FROM #TMP_AirlineTickets SUB
         WHERE SUB.Airline = AT.Airline
         ORDER BY SUB.Departure_Airport_Code ASC
         FOR XML PATH('')
        ), 1, 1, '') AS Array_Airport_Codes
FROM #TMP_AirlineTickets AT
WHERE 1 = 1
GROUP BY AT.Airline

 
What happens in this query? The FOR XML clause creates an XML for the airline that is selected in the outer query. So a query like this is executed:

SELECT
  SUB.Departure_Airport_Code
FROM #TMP_AirlineTickets SUB
WHERE SUB.Airline = 'KLM'
ORDER BY SUB.Departure_Airport_Code ASC
FOR XML PATH('')

 
The result from that query is an XML column:

 
By concatenating a character to that XML column, the XML nodes are removed. This gives us the possibility to use this as an array:

SELECT
  CONCAT(',', SUB.Departure_Airport_Code)
FROM #TMP_AirlineTickets SUB
WHERE SUB.Airline = 'KLM'
ORDER BY SUB.Departure_Airport_Code ASC
FOR XML PATH('')

 
The result of this statement looks like this:

 
The next step is to use this in a query that creates an XML column for all airlines in the table:

SELECT
  AT.Airline,
  (SELECT
            CONCAT(',', SUB.Departure_Airport_Code)
         FROM #TMP_AirlineTickets SUB
         WHERE SUB.Airline = AT.Airline
         ORDER BY SUB.Departure_Airport_Code ASC
         FOR XML PATH('')
        ) AS Array_Airport_Codes
FROM #TMP_AirlineTickets AT
WHERE 1 = 1
GROUP BY AT.Airline

 
The result is an array of airport departure codes:

 
As you can see, this creates the array/list for all airlines in the table. With STUFF I replace the first character, because I don’t want my result to start with a comma. This can be done with a RIGHT clause, that USES LEN – 1 to strip of the first character on the left, but this looks better in my opinion:

SELECT
  AT.Airline,
  STUFF((SELECT
            CONCAT(',', SUB.Departure_Airport_Code)
         FROM #TMP_AirlineTickets SUB
         WHERE SUB.Airline = AT.Airline
         ORDER BY SUB.Departure_Airport_Code ASC
         FOR XML PATH('')
        ), 1, 1, '') AS Array_Airport_Codes
FROM #TMP_AirlineTickets AT
WHERE 1 = 1
GROUP BY AT.Airline

 
The result looks the same, except the first comma is gone:

 
One of the best things when creating demos like this for a blog post, is that you can make it as unrealistic as you want. What I mean by that, is I’m making it easy to generate XML, in order to keep the examples clear and understandable. But what if you want to use this in the real world? You’re probably seeing datasets that aren’t as easy to use as the demo above. The strings you want to concatenate are filled with special characters, etc. Luckily there’s a solution for that. Rob Farley (Blog | @rob_farley) wrote a really nice blog post about that.

 
So now we have our array/list ready, but how do we convert this to JSON or JavaScript? The main difference (sorry if I insult my developer friends with this statement), is that in JavaScript everything needs to be enclosed in double quotes (“). This isn’t needed if you create JSON. In the following example, I’ll create a JSON object. But because just airport codes isn’t what we need, I’ll adjust the query so it’ll add the prices to the object as well. This is done by an additional CONCAT in the XML select, which concatenates the airport code and the price into 1 string:

SELECT
  AT.Airline,
  CONCAT('{"ap":[',
         STUFF(
               (SELECT
                    CONCAT(',{"', SUB.Departure_Airport_Code, '":', SUB.Price, '}')
                FROM #TMP_AirlineTickets SUB
                WHERE SUB.Airline = AT.Airline
                ORDER BY SUB.Departure_Airport_Code ASC
                FOR XML PATH('')
                ), 1, 1, '')
         , ']}') AS JSON
FROM #TMP_AirlineTickets AT
WHERE 1 = 1
GROUP BY AT.Airline

 
The GROUP BY can be replaced by a DISTINCT. But either one of those is needed to make sure you’re only returning unique records:

 

 
By parsing the second result in the query in an online tool, you can see that the result is valid JSON:

 

 
By adding some double quotes in the script above, you can easily generate JavaScript from T-SQL. But if you just want to use the FOR XML to generate arrays, this will also work perfectly.

 
Why would you want to use this?
Generating JSON or JavaScript might come in handy for webdevelopers, that can get data from a database, and use that directly on their website. By using the FOR XML clause to generate JSON, you make sure your queries are flexible enough to generate valid JSON, even if your dataset grows. Generating arrays like this can also come in handy for developers that need data from their databases. Instead of creating an array in code, they can now use the array that is returned from the database.

So even though I tried to work my way around using FOR XML, it turned out to be the best option thinkable!

Change data through CTE’s

Even though I’ve written a number of blog posts about CTE (Common Table Expression), there’s always more functionality to discover. Last week I read an article about updating values in a table through a CTE, and I decided to try it out and see how far I could take this.

 
How to use a CTE
A question I get a lot is “When should you use a CTE?”. Personally I use a CTE to create a temporary resultset, that I can use as a source for another query. For example, when I need to transform a datetime column into a separate year, month and day columns, that I need as separate columns in my following query, I use a CTE to create a resultset I can use in my second query. Inserting the transformed data into a a temporary table is a little bit overkill, so a CTE is the easiest option. But there are a lot of different ways to use a CTE.

 
Update
Updating your source data through a CTE sounds like a long shot. Normally I prefer to update my source tables directly, but updating my source data through a CTE might make your queries more readable in some cases. So how does this work? Let’s look at an example, using the AdventureWorks2012 database. Using a CTE, we’re only selecting one specific employee: Angela. Because she worked some extra time, we’re updating here vacation hours, and increase them with 2 extra hours:

WITH EmployeeCTE AS
(
	SELECT
		NationalIDNumber,
		LoginID,
		JobTitle,
		VacationHours
	FROM HumanResources.Employee
	WHERE NationalIDNumber = 563680513
)


UPDATE EmployeeCTE
SET VacationHours += 2

 
If we select the record in the source table after the update, we see that we updated the record. The number of vacation hours is updated from 92 to 94:

 
Insert data
Besides updating, inserting data is also possible. Inserting a record is as easy as updating a record:

WITH CurrencyCTE AS
(
	SELECT
		CurrencyCode,
		Name,
		ModifiedDate
	FROM Sales.Currency
	WHERE CurrencyCode = 'TST'
)


INSERT INTO CurrencyCTE
	(CurrencyCode, Name, ModifiedDate)
VALUES
	('TST', 'Test Currency', '2014-06-01')

 
If we now search for the source table for the currency code we just created, you’ll see that it’s inserted through the CTE:

 
This can shorten your insert statements a lot. If you have a table with 10 columns for example, and you only want to insert 4 values in the table, you can select these columns in a CTE, and just insert those columns. On the other hand, this can make your queries unreadable, because you hide your source table in your query. So if you use this in combination with source control, it can be hard to track down where you update or insert a record in any given table.

 
Delete data
Deleting data from a CTE is just as easy. As an example, let’s delete the currency we created:

WITH CurrencyCTE AS
(
	SELECT
		CurrencyCode,
		Name,
		ModifiedDate
	FROM Sales.Currency
	WHERE CurrencyCode = 'TST'
)


DELETE
FROM CurrencyCTE
WHERE CurrencyCode = 'TST'

 
The limits
Both inserting and updating data through a CTE can be a powerful help in making your queries shorter and easier to read. But it can also backfire on you. Updating your source tables through a CTE can also make your queries hard to read, or even impossible to decipher, when you look at it a few weeks later. But what are the technical limits?

Updating a CTE that contains a join is not a problem. Except when you want to update a column in multiple tables at once:

 
The same goes for an insert. If you want to insert data in 2 or more tables at once, that’s impossible through a CTE.

Another limit you can encounter, is updating columns that aren’t actual source data. For example, if you try to update a column that is transformed with a LEFT() function. If you select the LEFT(CurrencyCode, 2) in the CTE, and you try to update that, you’ll see this error:

 
Because the field is a derived column (a computed/transformed version of the source column), you can’t update it. So if you try to update a transformed column, you should use an update statement on your source table.

 
Should you use it?
Even though this can be a cool feature to use, you must be careful with this. If you use CTE’s to alter data, you should be aware of the fact that your queries can become unreadable or hard to decipher. And even though you might find it readable, your colleagues might have other ideas about that. Also, as mentioned earlier, it might make it harder for you to find all inserts/updates on a specific table in your source control system.

 
If you want to read more about this #SQLCOOP topic, don’t forget to check out these blog posts:

- Julie Koesmarno: Context Aware And Customised Drillthrough
Mickey Stuewe: Data Models, SQL Server, SQLite, and PowerShell
Chris Yates: Alerts – Who Knew?

T-SQL Tuesday #54 – An Interview Invitation

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “An Interview Invitation “. If you want to read the opening post, please click the image below to go to the party-starter: Boris Hristov (Blog | @BorisHristov).



 
This months topic is about job interviews. Looking back, I can say I had a few interview over the last few years. Not only to get myself at a company, but also to hire new employees for the companies I worked for. Both types of conversations can be very interesting, or get on your nerves very quickly. I’ve seen both…

 
Hiring people
When you work for a company, and you hire new people, you want to make sure you’re talking to the right type of people. Whether or not I’m hiring you, isn’t just bases on technical skills. The way I see it is that technical skills is mostly build from experience and insight, and knowledge can be build up by reading a book, watching a video, or talking to and learning from other people with a similar skill set. The thing I’m personally looking at in the interview is personality, passion for what a person does, and what he or she wants to achieve in the next few years. That will tell you something about the person. I’m not saying the technical skills aren’t that important, but it’s not the only thing I look for. One of the last companies I worked for looks for people they want to drink a beer with on a Friday night. So again, the personality is in some cases as important as technical skills.

 
Interviewing people
Talking to other database professionals can be either very interesting, or very frustrating. One of the interviews I had in the past few years, was to hire a new colleague for a project I was working on. His resume looked good, he had a few years experience on equal projects, and I thought he would make a good addition to the team. When I started the interview, he seemed to be a little nervous. I tried to make him feel a little more comfortable, by asking him about his experience, past projects, and personal life. When he calmed down, we slowly moved the conversation to his technical skills.

Talking about the project, I started to notice he wasn’t giving me the answer I would’ve expected from someone with his experience. Okay, maybe he misunderstood the project. We moved on to common issues we all ran into when working on projects. Every question was answered by “it depends…”, and “I’ve read that…”. Okay, that’s no good. Theoretical knowledge isn’t what I expected. And not long after that, I ended the interview. If you’re too scared to even make a theoretical decision, and defend that point of view, I don’t want you to work on my environment. That shows me that if you make a mistake, and that can happened to even the most experienced employee, you won’t have the guts to tell me you did something wrong. And I want you to tell me that, so we can solve it together, and learn from it.

Another interview I had with a database professional, turned out to be a dull conversation. The person came in to apply for a senior function, and he definitely had the experience for it. His personality was good, and would fit the team perfectly. But every answer we asked him, he answered with a lot of needless words, that ended up in a best-practice answer. The reason I didn’t want to hire him, was his lack of creativity. If you’re spilling answers you read on the internet, or in books, it shows me you’re probably not creative enough to solve the problems you’re encountering. If you’re in a stressful situation, and you need to read a book to think of a solution to the problem, you’re probably not the DBA I’m looking for.

 
Being interviewed
One of the most interesting interviews I had in the last few years, was one of my last job interviews. The first interview went well, where we talked about the company, the team, etc. The IT manager and team leader wanted to talk to me a second time, and that was the most interesting interview I had until now.

The interview started out nice. I talked to the team leader again, and another team member joined the interview. We had a good conversation about technical stuff, the team, projects, etc. When we were approaching the end of the interview, the most interesting part started. I knew the company worked with a consultant company from England, and that the lead consultant visited the office every few weeks. Big surprise, he was there that day. So the moment he walked in, I started to struggle. I needed to switch from my native language Dutch to English, and started an interview with an MCM and MVP. This made me even more nervous, and because of that I stared to doubt every answer I gave. But in the end, I did a reasonable job apparently, because the company hired me. Now I see him every month, and we have some good conversations, even though I’m still afraid to ask him a question sometimes, worrying I’m just asking a dumb question.

 
Maybe the most annoying person is the perfect candidate…
Thinking about hiring a new colleague, you might end up with a pretty big dilemma. The person you want to hire because of his personality, might not be the best choice. The one with the best technical skills might be a better fit, but you don’t like him at all. So when looking for a new colleague or team member, you might end up wondering what’s best for the company, and set your own feelings aside. But when you do, it’s always a gamble with new people. At first, the new colleague had perfect technical skills and was very nice, but in the end his skills aren’t that good or his personality is slightly different than you thought. Hiring people is still a combination of asking the right questions and a gut feeling.

SQL Search: The indispensable tool just got better

Since 1999, Red Gate Software has produced ingeniously simple and effective tools for over 500,000 technology professionals worldwide. From their HQ in Cambridge UK, they create a number of great tools for MS SQL Server, .NET, and Oracle. The philosophy of Red Gate is to design highly usable, reliable tools that solve the problems of DBAs and developers.

Every year Red Gate selects a number of active and influential community members (such as popular blog writers and community site owners) as well as SQL and .NET MVPs who are experts in their respective fields, to be part of the Friends of Red Gate (FORG) program. I’m proud to announce that I’m part of the 2014 FORG selection. This post is a part of a series of post, in which I try to explain and show you why the tools of Red Gate are so loved by the community.



 
It gets better
As you might have read in my earlier blog post, I love using Red Gate’s SQL Search. It saves me a lot of time querying system objects, or looking through source files stored on disk, when looking for a specific query or object. Even though it had a few shortcomings, the tool was really good. And now they’ve solved some of those shortcomings in a new release: SQL Search 2.0. In this short post, I want to quickly show you the changes that make it even more useful for me to use SQL Search.

 
Search on specific databases
In the previous version you could only search on 1 specific database, or on all databases:

In the new version, it’s more flexible. You can search on all databases or a selected few databases:

 
This means you’ll be more flexible when searching for specific queries and objects. For example, if you have 2 databases that use an object from a third database, you don’t need to search the whole instance, or execute the search twice on different databases. This can save you a lot of time and effort.

 
Search on specific objects
Searching for specific objects was the same as the database search box. In the old version, you could only search for all object types, or on one specific type:

 
In the new release, you can search on any combination of objects you can think of, or search on all objects:

 
Search results extended
In the new search results, you’ll also see that tables and functions are included. Before you couldn’t see the table definition in the search results, only the table name. Now, the results include the columns of the table. As an example, I’ve searched for tables containing “Employee” on the AdventureWorks2012 example database:

 
Wildcards
You can also use wildcards and boolean expressions in the search in this new version. It does seem to work on some occasions, but I’ve also seen some weird results when testing this new functionality. But as an example, let’s try out a boolean expression:

 
And looking at the results, it shows only objects that contain “Employee”, and not “Employees”:

 
Performance
Because I work with SQL Search a lot, I’ve also noticed that the performance and stability improved a lot. In the old version, I sometimes notices that the searches took a long time, especially searches with a lot of results on instances with a lot of objects and databases. This performance looks better now. But the fact that you can search on specific objects and databases also helps.

 
You don’t use it yet? Try it out!
Looking at my daily job, I’m almost certain that I can’t live without SQL Search anymore. It makes it easier to quickly find a reference to an object, or views en stored procedures that depend on specific objects. So if you don’t use it now, just try it out. It’s completely free for download at Red Gate.

 
If you want to read more about this topic, don’t forget to check out these blog posts:

- Julie Koesmarno: SQL Server 2012 SSIS Project Versions
Mickey Stuewe: New and Improved SQL Search By Red Gate
Chris Yates: RedGate SQL Search Has What????

Write readable and high-performance queries with Window Functions

In SQL Server 2008, we gained a new and powerful feature in our bag of T-SQL tricks: Window Functions. The actual Window Function is the OVER clause, that allows you to determine partitions or “groups” in your query, before applying another function. In practice, this means you can create groups in your dataset, that can be molded further by applying functions to that groups.

In the past, I’ve written a number of posts about Window Functions in SQL Server:

Row_Number: Unique ID in select statement
Calculating Running Totals
Removing duplicates in a dataset

But there’s more!

 
Finding missing numbers in sequence
How many times did you look at a table, and you noticed that one of the values in a sequence is missing? Or a date range in a table, where someone deleted a few records. So all of a sudden, your year doesn’t have 365 days (or 366 in a leap year, or 365.242199 days which is the years average), but 315 days. There go your management reports that are calculated on a per day average. So how do you find the missing dates, without having to write a cursor or create a full blown date table like your BI colleagues do? You guessed right: a query with Window Function!

Let’s start with declaring a table, and insert a sequence with missing values:

DECLARE @Sequence TABLE
  (Number INT)


INSERT INTO @Sequence
  (Number)
VALUES
  (1),
  (10),
  (7),
  (4),
  (2),
  (8),
  (5)

 
So how can we get the “nearest numbers” from that table with just a single select statement?

SELECT
  LAG(Number, 1, 0) OVER (ORDER BY Number) AS LAG_Value,
  Number,
  LEAD(Number, 1, 0) OVER (ORDER BY Number) AS LEAD_Value
FROM @Sequence AS S
ORDER BY Number ASC

 
The LAG and LEAD are standard t-sql functions from sql server 2012 on. These functions give you the opportunity to access the previous or next row, without the need for a so called “self-join”. So what you see is the number, the record preceding that value (LAG) and the following value. So in this case, number 2 is preceded by Number 1, and followed by Number 4.

 
The lemonade stand
Now let’s look at another example. How about you? When you grew up, you wanted to save money for a new mobile phone right? In my case it was either a disc man, a Walkman, or a stereo set. But let’s stick with the modern equivalent of the Walkman for now: the MP3 player. So to earn money for the MP3 player, our fictitious friend who is called Joe, decides to start a lemonade stand. He needs to save up at least $150 to buy a new MP3 player. So every glass of lemonade he sells is accounted for, and at the end of the day he sums up all his
earnings, and puts it into a table:

DECLARE @Profit TABLE
  (DayNumber INT,
   Sales DECIMAL(10,2))


INSERT INTO @Profit
  (DayNumber, Sales)
VALUES
  (1,  6.90),
  (2,  4.17),
  (3,  2.69),
  (4,  7.26),
  (5,  2.93),
  (6,  8.98),
  (7,  7.25),
  (8,  5.88),
  (9,  1.51),
  (10, 7.97),
  (11, 3.44),
  (12, 3.76),
  (13, 9.96),
  (14, 0.92),
  (15, 8.28),
  (16, 6.05),
  (17, 9.40),
  (18, 4.03),
  (19, 9.14),
  (20, 7.25),
  (21, 0.06),
  (22, 9.12),
  (23, 7.39),
  (24, 6.57),
  (25, 4.54),
  (26, 0.09),
  (27, 4.42),
  (28, 9.53),
  (29, 5.09),
  (30, 0.89)

 
So as you can see, he earns quite a lot of money this way! But because he’s eager to buy his new MP3 player, he wants to see his day totals, and the amount he needs to buy his new toy. But because Joe is a really smart guy, he doesn’t want to do this with a lot of self-joins, and he wants his results fast. So looking at performance, what is the easiest way to query this data? How about this:

DECLARE @Goal DECIMAL(10,2) = 150.00


SELECT
  DayNumber,
  Sales,
  @Goal - SUM(Sales) OVER(ORDER BY DayNumber
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MoneyNeeded
FROM @Profit

 
He declares a “goal” variable, that is set to the amount he needs for his new gadget. So for every row, we calculate the sum of that day, plus all the sales he made from the first day (UNBOUNDED PRECEDING) and today (CURRENT ROW). After day 28 he has earned enough to buy his MP3 player. But now he wants to know what his average sales were. So he calculates the average of his sales, based on every sale he’s made so far:

SELECT
  DayNumber,
  Sales,
  AVG(Sales) OVER(ORDER BY DayNumber
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MoneyNeeded
FROM @Profit

 
So where does it stop?
So now that we’ve seen the SUM and AVERAGE option, what do we have left? How far can we take this? Thinking about it, how about a daily checkup if we hit a lowest or highest Sales amount? We can do this with the MIN and MAX option on the same query:

SELECT
  DayNumber,
  Sales,
  MIN(Sales) OVER(ORDER BY DayNumber
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LowestSale
FROM @Profit


SELECT
  DayNumber,
  Sales,
  MAX(Sales) OVER(ORDER BY DayNumber
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS HighestSale
FROM @Profit

 
Now Joe can save his daily sales in the evening, and check if he had a very good, or a very bad day.

 
Not only for the average Joe…
So how can the business profit from all this? In some cases, Window Functions make it easier to output a certain resultset. In some cases it even gives you a whole new way to output data with a well performing (single) query, that was impossible until now. So if you’re running SQL Server 2008 or higher, start using (or at least start exploring) Window Functions right away!

 

To generate the random floats, I’ve used the generator of FYIcenter.com

If you want to read more about this topic, don’t forget to check out these blog posts:

- Julie Koesmarno: ABC Classification With SQL Server Window Function
Mickey Stuewe: A Date At The End of The Month
Chris Yates: Windows functions who knew

It’s the little things that make a difference

I still can get enthusiastic when I discover tiny new features of SQL Server Management Studio (SSMS). It’s the tool that I use every day, but still I discover new and cool things to use, that I never noticed before.

 
Filter Object Explorer
If you look at the Object Explorer, you’ll see a little button that can come in quite handy: the filter button:

 
This allows you to filter the results in Object Explorer. If you click on it, you will see the filter window pop up:

 
Just as a test, I filtered my object on name contains “Test”. This will filter only the database and object type you selected. In my case, I selected the Table-node, and it will filter only these objects:

 
There are a few drawbacks on this. One of them is that you can’t remove the filter without opening the filter pop-up again. Another one is you only filter results once. If you want to adjust your filter, you need to remove it completely, and reapply your new filter.

 
Scripting Magic
One of the things I use on a regular basis is the “Generate and Publish Scripts” wizard. But did you know this wizard had some hidden gems? One of the options you have, it to script objects to individual files. That’s an option that is hidden in plain sight:

 
But another gem is hidden behind the Advanced button:

 
This allows you to generate insert scripts for you tables, without the use of a 3rd party tool.

 
Splitter Bar
One of the hidden gems I wanted to show you, isn’t one I found out myself. This one I discovered via a blog post from Kendra Little (Blog | @Kendra_Little). She blogged about the Splitter Bar in SSMS, which is quite handy sometimes! Go check out her other blog posts as well, for example about Scripting changes from the GUI.

 
In the picture above you see the same stored procedure (in this case from the AdventureWorks 2012 database), split in 2 by the splitter bar. This makes it for example easier to look at the declare statements in the top of the script, and the query your working on in the bottom of the script.

 
Tab Groups
The last one is one I use on a regular basis. At the past few employers, I’ve worked with 2 monitors. This makes it easy to compare files or result sets. But what if you don’t have that luxury? There’s an option in SSMS to create a new tab group. Just right-click a query tab, and choose the option you like:

 
Let’s say you want to compare the resultsets of 2 queries, you can use the Horizontal Tab Group option:

 
To return to your normal view, just right-click on the tab again, and click “Move to Previous Tab Group”.

 
Save time
One of the things Mickey Stuewe (Blog | @SQLMickey) pointed out, is that you can rearrange the columns in the result window of SSMS. Just drag and drop columns the way you like. It could save you a lot of time rerunning the query to change the order of your columns. The order of the columns is reset the next time you run the query.

 
Never stop learning!
There are plenty more hidden gems in SSMS, waiting to be found by you. So never stop learning, and always try to take it a step further than needed. You’ll be surprised to see what awaits you…

How SQL Search saves you time

Since 1999, Red Gate Software has produced ingeniously simple and effective tools for over 500,000 technology professionals worldwide. From their HQ in Cambridge UK, they create a number of great tools for MS SQL Server, .NET, and Oracle. The philosophy of Red Gate is to design highly usable, reliable tools that solve the problems of DBAs and developers.

Every year Red Gate selects a number of active and influential community members (such as popular blog writers and community site owners) as well as SQL and .NET MVPs who are experts in their respective fields, to be part of the Friends of Red Gate (FORG) program. I’m proud to announce that I’m part of the 2014 FORG selection. This post is a part of a series of post, in which I try to explain and show you why the tools of Red Gate are so loved by the community.



 
Red Gate? No thank you!
One of the biggest prejudices of the tools from Red Gate is: you have to sell one of your kidneys, in order to afford one of their tools. I agree with you, some of the tools Red Gate sells are pretty expensive. Especially when you need to buy them yourself. But what if I tell you they pay for themselves in the long run? You don’t believe me? Okay, let’s start of with a free tool to convince you.

 
How SQL Search can save your bacon
As a DBA and SQL Server developer, one of your biggest challenges is to memorize your whole environment, and learn every line of T-SQL there is to find from the top of your head. I’m sorry? Oh, you don’t do that? Well, that doesn’t really come as a surprise. And if you thought: “Hey! I’m doing that too!”, stop wasting your time! No DBA, wherever you will look in the world, will EVER remember all of the T-SQL script, stored procedures, views, etc, that can be found in his or her environment. But where do they get their information from? What would you say if I told you you could search though all objects in a specific database, or on a specific instance?

So how do you do that? You’re planning on making your own script to search through the system view sys.columns for column names, and sys.procedures for Stored Procedure text? There is an easy way out, you know!

 
Installing and testing
In order to create a test case that you can repeat on your own SQL Server, I’m using the AdventureWorks2012 database. That’s a free example database that you can download from CodePlex. I’ve chosen to download the backup file, that I restored on my local SQL Server.

The installation of SQL Search can be found on the Red Gate SQL site. When you installed SQL Search, a button will be added to the SQL Server Management Studio (SSMS):

 
So, what do you actually get? If you click the button in SSMS, you’ll see a new window, that’ll act as a result pane. The top of the new screen looks like this:

 
This screen contains all possible options you will need to find objects either on your server, or in a specific database. You see a textbox on the left, where you fill in your search term. The next few options can be used as a filter. You can search on an exact match (checkbox), search on a specific object only (dropdown list), and on a specific database (dropdown list). The last dropdown list is the instance you want to search on. This list is populated with the open instance connections from your object explorer.

 
Test case
So how does this work in practice? One of your colleagues comes up to you, and asks you what objects in your database are related to department data. You could search for documentation in order to answer that question, or you could let SQL Search give you the answer. So, let’s search the AdventureWorks2012 for objects that are related to, or contain department data. The result will look like this:

 
As you will see there are 18 objects that are related to your search term (the count of object is visible at the bottom of the search results on the right). Some objects are shown multiple times, because there are multiple matches on that object. For example the “vEmployeeDepartment” view. The name of the view contains our search term, one of the columns is called department, and the text of the view (create script) contains your search string.

But how does this work with real life situations? How many times do you get the question from your colleagues how many objects are related to a specific table or column? As a DBA you probably get this question more than you would like. Your developers want to rebuild an application, or add a new feature to it, but they’re not sure if the database change they’ll make will break another applications or processes.

It’s also possible for you to use the tool to your own advantage. For example, when you want to know which object can update a specific employee record. You just search for both terms, and you’ll see that there are 3 stored procedures that update employee information:

 
Please hold on, while I search for the object…
…is a sentence you never have to use again when working with SQL Search. Whenever you found the object you were looking for, you can just double click on it, or use the button between the result pane, and the script pane:

This will look up the object in the object explorer. So you never have to look for an object after you found it. Just let SQL Search do all the hard work.

 
Red Gate? They’re AWESOME!!!
Hopefully you changed your mind about Red Gate tools, after reading this article. This is one of the tools I personally use on a daily basis. Even though there is documentation within your company, you still need to find it. It’s printed and laying around somewhere, or on an intranet or SharePoint. You know where you can find it, except when you REALLY need it!

SQL Search is also a tool you (in my opinion) really need when you’re new to a company. You’ll see a lot of different databases, with different purposes, and maybe even different DBA’s responsible for that specific database. Using SQL Search gives you a great advantage when you need to have a chat with the DBA. You’ll step into their office with a little more knowledge of the database, without reading endless documents, cryptic ERD’s and folders full of unnecessary documentation.

 
Feedback
When you start using the tool, don’t forget to thank the people from Red Gate. They LOVE to hear your feedback, either in a tweet, the Red Gate Forums, or by contacting support. You could also send me a mail or tweet, or leave a comment at the bottom of this post. I would love to answer your questions (as far as I can), or pass them on to Red Gate.

If you want to read more about SQL Search, don’t forget to check out these blog posts:

- Julie Koesmarno: SQL Tools Review: SQL Search
Mickey Stuewe: On a SQL Quest using SQL Search by Red Gate
Chris Yates: Headache + Pain Red Gates SQL Search

Follow

Get every new post delivered to your Inbox.

Join 50 other followers