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.
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.
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.
^ 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:
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:
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:
All things being equal, I love the idea of the INTERSECT query.
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:
SELECTBusinessEntityID, JobTitle, VacationHours FROMHumanResources.Employee WHEREVacationHoursBETWEEN 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:
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:
SELECTBusinessEntityID, JobTitle, VacationHours FROMHumanResources.Employee WHEREVacationHoursBETWEEN 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.
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 ofSQL 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 withBrent 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.
My boss came in today and asked if I knew about any of the three following terms as they apply to SQL Server:
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.
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
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.
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:
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:
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. 😀