T-SQL: Using the HAVING clause

Here is a quick differentiation between the “WHERE” clause and the “HAVING” clause within an SQL Query.

Below is a basic query that will generate a list of the Customers based upon the number of orders they have placed.

select count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no order by orders desc

I have used the “WHERE” clause to filter records where the Order Number is greater than zero (you could also filter by order date).

However, if I am only interested in Customers that have placed more than 100 orders, I can use the HAVING clause as shown below.

select count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no having COUNT(order_no) > 100 order by orders desc

The HAVING clause allows me to apply a filter based upon a value created by the GROUP BY clause (eg SUM; COUNT; AVG), rather than values in the original table.

If you only need to list your top 10 customers, use the TOP clause or the LIMIT clause.

select top 10 count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no having COUNT(order_no) > 100 order by orders desc

select count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no having COUNT(order_no) > 100 order by orders desc limit 10

Advertisements

T-SQL: Aggregate Functions; Date Functions; and Group By Statement Woes

When combining Aggregate Functions with Date / Time functions within an SQL Query, you may run into errors relating to the “Group By” statement, such as:

• “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”
• “Invalid Column Name”
• “Each GROUP BY expression must contain at least one column that is not an outer reference”

Here are some examples of how NOT to formulate the Query.

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by

Msg 8120, Level 16, State 1, Line 2 Column ‘dbo.windows_quote.date_received’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Excluding “year_received” from the GROUP BY statement does not work, and neither does adding it.

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by, year_received

Msg 207, Level 16, State 1, Line 8 Invalid column name ‘year_received’.

I also tried to reference the field by index number..

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by, 2

Msg 164, Level 15, State 1, Line 8 Each GROUP BY expression must contain at least one column that is not an outer reference.

Only by including the function itself within the GROUP BY statement could I get the Query to run properly.

UPDATE: The reason for this is because the “SELECT” clause is the very last part of the query to be evaluated. The query engine works through the statement as follows: FROM => WHERE => GROUP BY => SELECT. Therefore, the attribute “year_received” within the SELECT clause doesn’t exist when GROUP BY tries to reference it.

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by, year(date_received)

 

SQL Server File Locations for Default Instances

Each Instance of SQL Server has its own set of Executables (sqlservr.exe) and Data Files. The file locations for the default instance are different depending upon the version installed. Essentially, this is to allow multiple versions of SQL Server to happily co-existing on the same server.

The table below lists the default file locations (as they appear on my systems).

Version	Location of Database Engine	Additional Instances	Service Names
SQL Server 2000  & MSDE(v8)	C:\Program Files\Microsoft SQL Server\MSSQL	MSSQL$MyInstance	MSSQLSERVER; MSSQL$MyInstance
SQL Server 2005 (v9)	C:\Program Files\Microsoft SQL Server\MSSQL.1\	MSSQL.2; MSSQL.3…
SQL Server 2005 Express (v9)	C:\Program Files\Microsoft SQL Server\MSSQL.1\	MSSQL.2; MSSQL.3…
SQL Server 2008 (v10)	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER	MSSQL10.MyInstance	SQL Server (MSSQLSERVER); SQL Server (MyInstance)
SQL Server 2008 Express (v10)	C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS	MSSQL10.MyInstance
SQL Server 2008 R2 (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER	MSSQL10_50.MyInstance	SQL Server (MyInstance)
SQL Server 2008 R2 Reporting Services (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER	MSSQL10_50.MyInstance	SQL Server (MyInstance)
SQL Server 2008 R2 Express (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS	MSSQL10_50.MyInstance
SQL Server 2012 Express (v11)	C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS	MSSQL11.MyInstance	SQL Server (MyInstance)
SQL Server 2014 Express (v12)	C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS	MSSQL12.MyInstance	SQL Server (MyInstance)

 

Common Files
Additionally, multiple instances (within each version) share some common files.

Version	Location of Common Files	Notes
Common Files (2000)	C:\Program Files\Microsoft SQL Server\80\
Common Files (2005)	C:\Program Files\Microsoft SQL Server\90\	The 2005 installation also creates an  “80” folder.
Common Files (2008)	C:\Program Files\Microsoft SQL Server\100\
Common Files (2012)	C:\Program Files\Microsoft SQL Server\110\
Common Files (2014)	C:\Program Files\Microsoft SQL Server\120\

 

The Browser Service
There is only ever one copy of the SQL Browser Service running (and it always lives in the same folder). Normally, it will be automatically upgraded to whichever is the latest installed version of SQL Server.

The Browser Service listens for requests on UDP Port 1434 on behalf of all the installed SQL Server Instances that are installed on the Server (irrespective of version).

Version	Location of Browser Service	Notes
SQL Server 2000 Listener Service		Requires SQL Server 2000 SP3a
SQL Server 2005 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2005.90.5000.0
SQL Server 2008 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2007.100.5500.0
SQL Server 2008 R2 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2009.100.1600.1
SQL Server 2012 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2011.110.2100.60
SQL Server 2014 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2014.120.2000.8

 

64 Bit Windows
Within 64 Bit Windows, some files may be installed within “Program Files (86)” rather than “Program Files”.

SQL Server Compact Edition
These editions have very little in common with the SQL family as discussed in this document. SQL Server Compact Edition does not run as a service, nor is there any concept of database instances. The default installation directory is “C:\Program Files\Microsoft SQL Server Compact Edition\” and a file type of “sdf” is used for each data store.

SQL Server: Clustered Indexes

When creating a new table within SQL Server Management Studio Express (SSMSE), it will not have an index or a primary key by default.

A primary key is not mandatory, but from a performance point of view, it nearly always makes sense to create one. The Primary Key is a UNIQUE “Index” Value in a Database Table, such as the “Customer Code” in a Customer List (or the “Order Number” in an Order Register).  A table can have only one Primary Key.

To create a Primary Key within SSMSE, right click the desired field within the table and click “Set Primary Key”. If there are any issues saving the table design changes, follow the instructions here.

Once a Primary Key has been created, that particular database field (column) cannot contain duplicate values.

You will now notice that a new “Key” has been created for the table, along with a “Clustered Index”.

Clustered Index
A Clustered Index ensures that the data within the table is stored in exactly the same physical order as the index itself. A clustered index does not have to be unique, but it will be if it is associated with a primary key (as above).

By definition, there can only be ONE clustered index per table.

A table without a clustered index is called a “Heap”.

Non-Clustered Index
Since there can be only one clustered index per table, all other indexes must be Non-Clustered. These do not affect the way in which the data is physically stored.

SQL Server Browser Service and “Network Discovery”

Client Applications normally connect to the Default SQL Server Instance on TCP Port 1433. Additional Instances are normally allocated Dynamic TCP Port Numbers by the Server Operating System.

For the benefit of Clients, Microsoft SQL Server 2005 provides the “Browser Service” (UDP Port 1434) to advertise the Names and Port Numbers of each Instance of SQL currently installed.

When a Client requests access to a particular Instance, the Browser Service responds with the appropriate Port Number and the client uses this to establish a connection.

If the Client is a Remote Client, Windows Firewall (on the SQL Server) needs to be configured to allow inbound traffic to the ports used by:
[1] The SQL Browser Service (UDP 1434).
[2] The Default SQL Instance (TCP 1433).
[3] Additional Named Instances (Dynamic TCP Ports).

It is easy to see that in this scenario, it is impossible to pre-configure the Firewall for a Dynamic Port (since we do not know what the port number is going to be). Therefore, additional Instances should be allocated a “Fixed TCP Port Number” using the SQL Server Configuration Manager. In this way, the correct firewall Exceptions can be configured.

Choosing a Fixed Port
To view the current Dynamic Port of a particular Instance, use the “SQL Server Configuration Manager” (see below).

To turn this into a “Fixed Port”, remove the current value from the “TCP Dynamic Ports” field and place it into the “TCP Port” Field. Restart the SQL Service. It will now be a case of Configuring the SQL Server Firewall to pass traffic to the Fixed TCP Port (in this example Port “1266”).

SQL Server: Setting Domain User Permissions

If you are new to SQL Server, you may be wondering how to quickly assign Domain User Permissions so that they can access the data within your database.

If you are not on a Windows Domain Network, this article is not for you.

There are two types of permissions that can be set (Server; Database) and two types of Authentication (Windows; SQL Server).

I’ll keep it simple. Since we are interested in granting Domain Users access to a specific database, we will be using “Windows Authentication” and “Database Permissions”.

If you are familiar with using Groups to set resource permissions (such as file shares) within a Domain environment, you will be fine with this process.

In preparation, the first step will be to create a “Domain Local” Group (Using “Active Directory Users and Computers”) on your Domain Controller (I’ve called mine “DL_Databases”). Include any “Global Groups” that require access to the database as members.

In the main part of this article, we will now proceed to grant SQL Database Read / Update permissions to the “DL_Databases” Group and all of its members.

As with most aspects of SQL Administration, we will be using SSMS (SQL Server Management Studio) to set the permissions we require.

Before you make changes to any live / production database, ensure that the data / schema is fully backed up!

Ok, let’s begin.

Using SSMS (SQL Server Management Studio), connect to the SQL Server Database Instance you wish to work on. Expand the appropriate database container and right-click on “Security”.

Select New => User.

TYPE the name of your “Domain Local” Group (including the Domain Prefix) into both the “User Name” and “Login” fields. You won’t be able to pick the names from a list, you must type them in!

Ensure that “db_datareader” and “db_datawriter” are both checked within “Role Members”. Ignore the “Owned Schemas” Box. Click “OK” and you’re all done.

Any Domain Users / Groups that are members of the “DL_Databases” group will now have Read / Write permissions to your SQL Database.

Sage 200: SOP Schema

This is the first in a series of articles documenting the Sage 200 Database Schema.

Click here to download a PDF of the Sage SOP Schema.