the “left” and “right” query on either side of an INTERSECT/EXCEPT clause

 

EXCEPT and INTERCEPT clauses
EXCEPT and INTERCEPT

One thing I didn’t even notice in the last post was that the SELECT query above the EXCEPT/INTERSECT was referred to as the query “on the left,” and the one below the EXCEPT/INTERSECT was referred to as “on the right.”

This actually does make sense if you imagine reading these things left to right. However, it sounded like one of those little differences in meaning that could trip a newbie like me up in training. Thankfully the guys who did this one made sure to point out the terminology and what it meant.

Advertisements

The EXCEPT and INTERSECT clauses

I’d heard tell of the “EXCEPT clause,” but this is my first time seeing it. Isn’t it beautiful? I have wished before for such a function, but I didn’t expect it to look so clean on the page. In Access, I have jumped through a few hoops to achieve a result similar to what this appears to do.

20160615 EXCEPT and INTERSECT clauses

The INTERSECT Clause sounds pretty cool too. I think the equivalent of this definition (at least as far as data returned) in Access would be making inner joins to the fields you want and only pulling those fields down into the bottom.

20160615 Access INTERSECT equivalent

^ Here’s my attempt to represent my understanding of the INTERSECT if represented in Microsoft Access. But here’s the rub. If I were to write a SQL query for the above Access query, I think I would write it as follows:

20160615 Access INTERSECT equivalent back in SQL

This is a very different-looking construct. I went ahead and ran it both ways in SQL and got very different results. For the results of the query using the inner join, I got 72591 rows, and in the results using the INTERSECT query from the Microsoft training, I got a mere 238 rows. Looking closer, I notice that my INTERSECT query doesn’t have immediately obvious repeated values, and my Inner Join query does. I go back and add a “Group By” clause, and then my results match. In these queries using the AdventureWorks tables, both queries ran very quickly. I do not know how the dark underbelly of SQL Server may treat these two queries, so I don’t know if they are equivalent in CPU usage or just in data returned. Here are the two queries with the equivalent results, if anybody else is looking to play:

20160615 SQL Query Comparison
Both of these queries return 238 matching rows if using the AdventureWorks 2014 database.

And just to round out this post, I did go back into Microsoft Access, add the “Group By” and found 238 rows that way as well:

20160615 Access INTERSECT equivalen correctedt

All things being equal, I love the idea of the INTERSECT query.

The BETWEEN Clause

I’m watching Microsoft Virtual Academy’s Database Fundamental’s class – the Introduction to Core Database Concepts section, and there is an example of the BETWEEN clause using the AdventureWorks 2014 database:

SELECT BusinessEntityID, JobTitle, VacationHours
FROM HumanResources.Employee
WHERE VacationHours BETWEEN 75 AND 100;

I ran this query in my database, and retrieved 75 rows.The lowest number of hours in the VacationHours field is 75, and the highest is 99.

I get confused with what the word “BETWEEN” means in queries. In Access, I avoided using “Between” in favor of “> x and <y” in the criteria of a field, so that I knew exactly what I was asking for. Looking at the data returned in this query, I find myself asking again – is 100 included in “BETWEEN 75 AND 100”?

I check to see if the table has any values equal to 100 in the VacationHours field:

SELECT BusinessEntityID, JobTitle, VacationHours
FROM HumanResources.Employee
WHERE VacationHours = 100;

No data was returned, so that first query wasn’t excluding values of 100 necessarily – they just weren’t there to begin with.

Since I know the value 99 is in this field, I alter the query:

SELECT BusinessEntityID, JobTitle, VacationHours
FROM HumanResources.Employee
WHERE VacationHours BETWEEN 75 AND 99;

I again retrieved 75 rows, lowest number of hours in the VacationHours field is 75, and the highest is 99 – the same as the initial query results. I think I can reasonably conclude from this that “BETWEEN” includes that end number.

This seems easy enough for integers, and maybe with dates that are not datetimes(?), but if this were a decimal field, I don’t know what would happen to a value of 100.1 in the first query, or a value of 99.1 in the last. More to investigate.

How T-SQL Thinks? Sorting: Order By, Joins, Group By?

I started out my morning delightfully forgetful yesterday’s research mission.

As I fed my cats, made coffee, drove to work, and began settling into my day, I intermittently listened (and re-listened) to the first third of Episode 29 of SQL Data Partners Podcast with Carlos L Chacon. This episode is called, “You Thinking What I Am Thinking?” and it’s about how SQL Server “thinks.” The interview is with Brent Ozar, and I think it’s a really interesting interview even to a newbie like me whose understanding of SQL Server / T-SQL is so limited.

From the perspective of a person in my position who wants to turn out queries (often based on unique, one-time, complex requests for years’ worth of data from multiple tables) with results in the most finished way possible, it was a bit distressing to hear Mr. Ozar talk about how applying an ORDER BY to a query is like asking SQL Server to go through an the white pages in an old phone book and manually re-order all the names by first name – and how that would require going through and scribbling all the names down as you went to get them in the right order. .

On Sorting and Joins-

Mr. Ozar mentioned that when you join tables together, even if there isn’t an ORDER BY in the query, there is an “implicit order by” created by the join. Uh oh! In our group, we rarely have a need to run a query that doesn’t involve a few joins.

On Sorting and Group By –

I mentioned this podcast episode to my boss this morning as something he might also be interested in – and he wanted to know if GROUP BY was included in this general CPU strain scenario. My answer right now? I don’t know but it makes sense to me if Joins and ORDER BY would. Googling gave me very some rather complex results (for where I am.)

I don’t yet understand indices at all in SQL Server, and it looks like they play a significant role in the way these things work. I am looking forward to getting a better understanding of these things, and will report back.

 

 

Three Terms to Research

My boss came in today and asked if I knew about any of the three following terms as they apply to SQL Server:

  • Query Optimization
  • Performance Tuning
  • Execution Plan

He said they were not worth my investigating if I did not know them, but that the person in charge of our database had brought them up to him.

I don’t have a specific plan of study in mind, but I heard in a podcast (I think it was the “SQL Data Partners Podcast with Carlos L. Chacon”) that SQL has an “S-shaped” learning curve, where you learn a lot quickly in the beginning, and then it gets harder.

Because I want to be as sensitive as I can be regarding server performance from the beginning, I would like to try to learn things with an eye toward not screwing things up for our DBAs. I’ve already slowed down the server enough to have had my boss called regarding one of my queries recently, and I don’t want that to happen again.

Queries inspired by MVA Database Fundamentals course

I’m currently working through Microsoft Virtual Academy’s Database Fundamentals course. I really appreciate how likable these guys are. In fact, I am noticing this as a trend in many of the SQL resources I have been looking into – there are a lot of really relatable, helpful people out their teaching about SQL.  I hope this trend continues!

Anyway, they showed how to make a UNION statement, and in their example they used two select queries pulling results from the same table. I might be wrong, but I think that same query could be done by just making a more complex WHERE clause. However, they said in the video that you could union data from two separate tables if the columns were the same. To test that theory, I first made a duplicate of the

20160612 select into new table and union

The results appeared to come back correct. I was having difficulty yesterday with SQL Server telling me the objectID HumanResources.Employee2 wasn’t valid (I didn’t save the exact error, but it showed up if I hovered over my query), but that has cleared up today. (Because of reboot?) I had planned to continue trying to figure this out, and wondered if it had to do with a lack of primary key or some other strangeness.

Anyway, it was slightly exciting to create my first table using a SELECT / INTO statement, and also to use the UNION clause for the first time. The UNION query is almost identical to the one from the Database Fundamentals class, except for the second “FROM” being the Employee2 table instead of the Employee table.

I think it’s important to know you can create a table using the SELECT/INTO in this way, because when I very first read about this type of query, I understood it to be more like Microsoft Access’ Append query. I think it is like that, except it will create the table if the table doesn’t already exist. That’s pretty cool! That leaves the “CREATE TABLE” query for creating a blank table structure, which is definitely different from Microsoft Access.

Windows 10 turned off my SQL Server Service

Good morning. 🙂

Brief introductions: I’m Natalie, and I’m at the very beginning stages of learning SQL, and I’ve been contemplating starting a blog just to document the stuff I’m trying to soak in.

Long story short: I come from a Microsoft Access background, and my employer has impressed upon me that I must learn T-SQL querying in order to keep my job. I had been living without a computer outside of my iphone for almost a year, but last week I bit the bullet and bought a new Windows 10 laptop.

I installed SQL Server 2016 Express on it. (We are using 2014 at work, but I just couldn’t help myself.) I loaded the 2014 version of AdventureWorks on it, and was following along with Microsoft Virtual Academy’s Database Fundamentals course, re-creating the queries as I watched the material. All was well as of last night.

I fell asleep around midnight after updating the comments in a few of my practice queries, and when I woke up my laptop had gone through an update. I’m new to Windows 10, so I’m sure this isn’t exactly unique insight, but I found it slightly amusing and slightly threatening how Windows slowly let me back into my machine, reassuring me that my files were all still in the same place but that now my computer would work even better. I remember thinking that the underlying message seemed to be, “Don’t freak out!”

All seemed well – the Word document I had open the night before had auto-saved and was recovered. I went to SQL Server, chose my local server, and hit “Connect.” This is what I got back:

20160612 screenshot of error
Failed to connect to server x\SQLEXPRESS. (Microsoft.SqlServer.ConnectionInfo) Additional Information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Location Server/Instance Specified) (Microsoft SQL Server)

So, what the heck, Microsoft? It worked fine last night. How can I not connect to the data on my own hard drive?

Google got me this far: “Was facing the same issue. Realized that Windows 10 had turned off the SQL Server Service. To resolve, open ‘services.msc’ and start service of SQL Server ()””

I searched for “Services” from my start menu and opened them up. Here are the SQL Server ones:

20160612 screenshot of services

It may be ham-fisted of me, but I changed the disabled ones to automatic without delayed start. I don’t know which one was causing the issue. (I did this by right-clicking, going to properties, and choosing this from the drop-down.)

Thankfully this did resolve the issue, and I was able to continue with my studies.

I would love feedback if someone has more info on this – hopefully I haven’t opened up a wormhole. 😀