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 …

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 …

SQL Server File Locations for Default Instances

Each Instance of SQL Server has its own set of executable files (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 …

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 …

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 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) …

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 Order Schema. Click here to download a PDF of the Sage SOP Invoicing Schema

SQL Server: Relationships

In a previous article, I spoke about Referential Integrity within SQL Server when migrating from Microsoft Access. For the same audience, this article shows how to create a “Database Diagram” similar to the ones that can be created using the “Relationships” panel within Microsoft Access. To create a “Relationship” Diagram within SQL Server Before you …

SQL Server: Referential Integrity

If you are used to enforcing referential integrity via the “Relationships” panel within Microsoft Access, you will quickly find things are done a little differently within SQL Server.  There are two methods available to help maintain referential integrity within SQL Server. Foreign Keys / DRI (Declarative Referential Integrity) Triggers / PRI (Procedural Referential Integrity) DRI …

SQL Server 2000 Network Utility

If you are wondering (or like me you forgot) where to enable / disable the various Network Protocols within SQL Server 2000, type “svrnetcn.exe” from the “Start => Run” menu on the Server that is hosting the SQL Instance. This brings up the “SQL Server Network Utility” For remote connections to the server, “TCP/IP” should …