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…
Author: slowder
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…
Intro to XML, Part The Second
Last time I gave you a short introduction into XML. I covered the very beginning of what it is, what you use it for, and I even showed you an example file. This time, I’d like to show you how to work with your XML in a web browser, Then I’d like to show you…
XML, in as Few Posts as Possible
Ok, I know I have a few groups of posts started, so you won’t mind me starting another one, right? Right, so this collection of posts will be all about XML. If you’re not using it already, or completely familiar with what you can do with XML, check out this collection. I’m hoping to cover…
New DATETIME Data Types in 2008
We all know that in previous versions of SQL Server 2008 we have just one data type for storing date and time data, DATETIME. The problem is sometimes we only needed date, or we only needed time. Now you have some new options! TIME DECLARE@myTime TIME SET @myTime = GETDATE() SELECT@myTime as myTime By default,…
Business Intelligence Concepts
The phrase business intelligence is pretty old, 1958. In the early 90’s the term came to refer to the “concepts and methods to improve business decision making by using fact based systems.” So business intelligence is the means for taking all of the data in your organization and pull out the relationships. By finding these…