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 …

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 …

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 …

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 Schema.