Using sub queries in the HAVING clause is little different than using a sub query in any other part of the clause. The one thing that may through you is the WHERE clause inside the sub query… it acts as a JOIN criteria, so you might not get the placement right, until you’ve done it…
Category: Microsoft SQL
UPDATE Triggers
Last time we discussed triggers, we covered insert triggers. This time we’re doing UPDATE triggers. All the same rules apply to UPDATE triggers that apply to INSERT triggers. CREATE TRIGGER t_tablename_update ON TableName AFTER|FOR|INSTEAD OF UPDATE AS T-SQL code Just like for the INSERT TRIGGER, we can either run the T-SQL code after the update…
Correlated Subqueries with Comparison Operators
If you haven’t used a sub query yet, I’m surprised. They can be incredibly useful. They can also be a crutch…but like I say time and again, each tool has it’s use. I’d like to go into a little detail on sub queries today by talking about correlated sub queries. Let’s say you wanted to…
Correlated Sub Queries with Aliases
I know I’ve covered sub queries before, but I wanted to make sure I made a specific point. When you create a correlated sub query, you can reference columns from your subquery anywhere in your outer query. Usually you’ll either use it in your SELECT clause or your WHERE clause, but you can use it…
COUNT(*) vs COUNT(columnName)
Let’s look at the following code using your copy of AdventureWorks. USE AdventureWorks GO SELECT COUNT(*) AS countStar , COUNT(ALL EmployeeID) AS CountAllEmployeeID , COUNT(DISTINCT EmployeeID) as CountDistinctEmployeeID FROM HumanResources.JobCandidate You should get three counts countStar CountAllEmployeeID CountDistinctEmployeeID 13 2 2 You need to understand the differences in these counts. COUNT(*) returns the number of…
Correlated Subqueries
The fundamental idea behind sub queries is you execute the sub query once, then take the value(s) from that sub query and substitute them in place of the sub query for the outer query. That’s quite a mouthful. Just think of it like this, the database engine will run the inner query, then replace the…
Error Number
Every error generated in Microsoft SQL has a specific number, this number should uniquely identify the error that has occurred. This can be useful in building logic on what to do when a certain error is raised since creating a logic test for a number is far quicker than generating one for a string. Let’s…
Insert Triggers
An insert trigger is a Data Manipulation Language (DML) trigger that acts when a new record is added to the table. CREATE TRIGGER t_tableName_insert ON tableName AFTER|FOR|INSTEAD OF INSERT AS Some bit of T-SQ OK, let’s walk through this. CREATE TRIGGER begins the command. The next part t_tableName_insert is the trigger’s name. I follow a…
SQL 102 – REVOKE
Like I mentioned previously REVOKE is like an undo function for GRANT and DENY. If you have a developer and you work with him for a while, you may find that he’s ready to be given a little more leeway in the database. Once you’ve decided to make a change to the permissions, you may…
SQL 102 – DENY
Alright, at this point you should know how to GRANT permission to a database object, but do you know how to DENY permission to an object? DENY uses a syntax similar to GRANT. DENY { ALL [ PRIVILEGES ] } | permission [ (column(s) ) ] [ ON securable ] TO principal I want to…