Become a T-SQL Hero with SQL Prompt

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.



 
What SSMS misses
The tool that Microsoft provides you with when you install SQL Server is pretty nice. It’s nicely designed (even though I’ve heard other opinions), it’s stable, and it does what it should do: it allows you to administer your servers. But that’s not the only thing that it should do in my opinion. If you take a look at Visual Studio as an example, that studio contains more options that helps developers do their job. And remember, SQL Server Management Studio (SSMS) is actually a Visual Studio instance with a different layout (just check the Ssms.exe.config)…

So why doesn’t SSMS have a schema compare option, like Visual Studio has? Visual Studio is no longer the environment that is used only by developers that work with ASP.NET and C#, but it evolved to much more the last few years. It’s now the tool for working with Data Quality Services (DQS) and SQL Server Integration Services (SSIS). So let’s talk about some other features that SSMS misses in my opinion, and let’s see how SQL Prompt can fill that gap.

 
IntelliSense
SSMS ships with a default intelliSense, but this isn’t an implementation that I would like to see. It misses a few vital features. For example, the fact that SSMS IntelliSense doesn’t take relations between objects into account, is one of the biggest shortcomings. One of the companies that created a tool to fix that is Red Gate. If you install SQL Prompt, you get IntelliSense 2.0, or IntelliSense on steroids if you like.

When you installed SQL Prompt, it gives you suggestions when you write a JOIN clause. This means that it scans column names, and traces primary- and foreign key relationships on the tables you are joining. The join suggestion based on keys can be recognized by the little key symbol in front of it:

 
Object discovery
Whenever you’re working in a database, and you’re writing your queries, there comes a point that you can’t remember a column name or datatype. In SSMS you need to navigate the object explorer to the object (let’s say a table), and generate a create script, or click on the table to get to the column list. SQL Prompt allows you to hover your mouse over an object, and see some extra information:

 
If you click on the popup, you’ll get another popup window with the creation script (by default), or a summary of the object:

 
Scripting options
Whenever you need to script an object, or want to see the contents of for example a Stored Procedure, you need to navigate to the object in your object explorer. With SQL Prompt, you can also use the mouse context menu to script objects. Just right-click an object you referenced in your query, and choose the “Script Object as ALTER” option:

 
This will generate an alter script for the object you selected. This makes it a lot easier to see the contents of a Stored Procedure or View, and change it when needed.

 
Useful functions
The last feature I want to show you is the menu of SQL Prompt. This shows you another set of useful tools and functions. For example, how do you format your T-SQL query? SQL Prompt can do that for you with a few mouse clicks, or if you press the hotkey combination. Another great feature is the “Find Unused Variables and Parameters”. This saves you time when you try to find out which declared variables you don’t use anymore, in a very large query. All of these options can be found in the SQL Prompt menu:

 
If you want, you can also create a style-export for all your colleagues, so your entire department or company formats queries according to the same layout. You can find out more about this in the SQL Prompt menu, under Options -> Format -> Styles. You can export your formatting options as a .sqlpromptstyle file, or import one.

 
Is it worth it?
If you would ask me, my answer would be: yes! Even though it’ll cost you about €285,- (or $390,-), it’s definitely worth it. It saves you a lot of time, and it adds a lot of useful (and needed) features to SSMS.

If you want to try it out, just go to Red-Gate.com, or the product site for SQL Prompt. You can download a trial there that contains all features, for a limited time.

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

- Julie Koesmarno: Clean And Tidy SQL With SQL Prompt
Mickey Stuewe: Becoming a SQL Prompt Power User
Chris Yates: SQL Prompt – The Power Within

T-SQL Tuesday #56 – Assumptions

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 “Assumptions “. If you want to read the opening post, please click the image below to go to the party-starter: Dev Nambi (Blog | @DevNambi).



 
This months topic is about assumptions. A few years back, I worked in a team that consisted of mainly .NET developers. Every time we mentioned “I think so…”, “I assume it works like this…” or “I think we should…”, one of them used the quote: “Assumption is the mother of all f*ck ups!”, which is a quote from the movie Under Siege 2: Dark Territory. But he was right. The moment you assume something, it’s going to blow up in your face in the end.

 
I tested it, and it works
Working for larger organizations should mean they are more prepared to certain things then smaller organizations. But I’ve seen large organization being badly prepared, or just plain unprepared. They assume their processes work, or they will never encounter failure at all.

One of the companies I worked for, took backups every night. Full backups. Of databases there were between 100 GB and 500+ GB… And they never tested a restore… Why? They used the default maintenance functionality with “Verify Backup Integrity” enabled, and they never needed to restore a database before. So they only took backups because management wanted that. They didn’t understand why, because their processes never failed, and would never fail in the future.

But one day, it wasn’t their processes that failed. A LUN went offline during the ETL process, and SQL Server naturally detected that. SQL Server put a database into suspect mode, because of database corruption. But because there were no backups, they needed to move to plan B: process about 2+ years of data (stored in XML files) again.

Eventually I solved it and recovered the database without the need of a restore, but it scared them. They now saw why they needed backups, and why they needed to test the restore on a regular basis. But they forgot about it after a few days, and we never got the time to change the maintenance processes or test any restore. After that, I made the best choice possible in my opinion: I found myself a new challenge.

 
If you don’t know what you’re talking about…
Another example of assumption I have seen a lot over the years, is people explaining stuff to other people, without any proper knowledge about a certain subject. I can recall a conversation between me and an intern. He was a .NET developer, and had some questions about how a T-SQL feature worked. Another junior BI developer started laughing, when the intern asked his question. “What a stupid question, everybody knows the answer to that!” he said. Kind of irritated by that, I asked him to provide the answer to that question. He didn’t want to. I asked him again: “you answer the question, because you laughed about it, and I want to hear the answer from you.”

He started to stutter, and he explained the functionality all wrong. When I explained it the right way, the .NET intern thanked me, and walked away with his new knowledge, ready to bring it into practice. The BI developer wanted to continue the discussion. “You’re all wrong! That feature doesn’t work that way!”. I nicely told him, that I used this feature on a daily basis, and that he was wrong. The discussion went on a little more, but I stopped the discussion by telling him: “I’m doing this for a number of years now, and working with SQL Server is what I do. You just started, and wrote your first query a few months ago. If you find any resources that show me being wrong, I’ll be happy to quit my job. Until then, please don’t explain T-SQL to other people if you don’t know what you’re talking about.”. Until this day, he never got back to me on this discussion.

 
I don’t need to check that!
Another great assumption you’ll see in several companies, are IT people that trust their own automation a little bit too much. The rule in IT is that if you need to do something more than once or a couple of times, you need to automate it. Automation is a good thing, and it can save you time. But who checks if your process doesn’t fail? You don’t want to build a system, that checks another system for you. One of the things I’ve seen is a developer that created an automated process, that checked a log table on a database server, and mailed new errors to the developer. Looking at this, it’s a perfect solution. You don’t have to monitor the log table by yourself, but an automated process does that for you.

At one moment, an application seemed to fail. It threw exceptions, and the end-users weren’t able to do anything with the application. The developer was called, and he told the users to contact the system administrator, because it must have been a server- or hardware problem. The system administrator called the developer after a few minutes, and told him the server and hardware were in perfect condition. The developer insisted his software wasn’t failing, because he didn’t receive any errors by email. But after a quick check, the developer came to the conclusion his automated process failed. The developer lost a lot of credits because of this attitude. As you see, this is another example of an assumption that went wrong.

 
Never stop asking questions
One of the most important things I wanted to show you with this blog post, is that if you don’t know the answer to a question, don’t be afraid to ask someone. The same goes for processes, tools, functionality, or any other question you want to ask. If people mock you for asking questions, they are the ones that are wrong. You’re just trying to learn and grow, so don’t feel bad about yourself!

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

Follow

Get every new post delivered to your Inbox.

Join 50 other followers