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

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.

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.

XAMPP: Using MySQLDump to Backup MySQL on Windows Server 2003 and Windows 7

I couldn’t find anything specific for Backing up the MySQL Databases within XAMPP. There certainly isn’t a pre-configured routine available. In addition, MySQL Workbench (unlike its predecessor MySQL Administrator) does not provide any automated backup facility.

So, I decided to explore the use of “MySQLDump”, a command line routine that can be inserted into a batch file and executed as a “Scheduled Task”. “MySQLDump” is included as part of XAMPP and is found within “<path to XAMPP>\MySQL\bin\”.

Here is the basic syntax I used (from a command prompt):

<path to XAMPP>\MySQL\bin\MySQLDump –u root –p myrootpassword –result-file=”<path to Backup Folder>\MySQLDump.sql” –all-databases

I then copied this into a batch file (MySQLDump.bat) and used “System Tools => Scheduled Tasks” (Task Scheduler in Windows 7) to run the batch file each evening.

Of course, you must ensure that the “MySQLDump.sql” file itself is stored in a safe location and can be recovered in case of server failure / building loss.

Fitting USB3 into a DELL PowerEdge R510 Server

USB3 is not supported natively within Windows Server 2008 (or Windows 7), and therefore Dell has always seemed reluctant to fit USB3 hardware into its Servers.

To make matters worse, most USB3 cards require some sort of SATA Power from the main board, and Dell does not provide these connectors inside the chassis either. So, fitting a PCI Express USB3 card is a tricky proposition.

Fortunately, StarTech offer several USB3 Cards that do not require any additional power from the board (save that provided by the PCI Express slot itself). However, be aware that if you are intending to connect an external Backup Drive to the card, you will need to ensure that the device itself is powered.

I chose the StarTech PEXUSB3S42 Card. It provides three external USB 3.0 Ports (another port sits inside), and was a breeze to install.

The first image (below) shows my R510 Server prior to fitting the Card, and the second shot shows the new card in place.

For reasons explained above, it was neccesary to run the driver installation disk (provided in the box), and this resulted in a further reboot of the server.

My HP RDX Backup System is now connected to a much faster port and the backup times have reduced accordingly.

All in all, a successful upgrade.

PS, don’t forget to use a USB3 lead when connecting your External Drive, or you will still be running at USB2 speeds.😉

 

 

 

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.

Sage Payroll: No Updates were installed

Occasionally, you may find that Sage Payroll “Software Update” will continue to prompt for an update that has been installed previously.

After hitting “Install Now”, you get a further notification that “No Updates Were Installed”.

The next time you start payroll, it will repeat the whole process again!

Fixing this is easy enough. Just delete the “softwareupdates.dat” from the “C:\ProgramData\Sage\SBD Software Updates” folder on the workstation.

You should now receive the correct notification that your software is up to date.