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 … Continue reading T-SQL: Using the HAVING clause
Category: Microsoft SQL Server
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 … Continue reading T-SQL: Aggregate Functions; Date Functions; and Group By Statement Woes
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 … Continue reading SQL Server File Locations for Default Instances
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 … Continue reading SQL Server: Clustered Indexes
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 … Continue reading SQL Server Browser Service and “Network Discovery”
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) … Continue reading SQL Server: Setting Domain User Permissions
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 … Continue reading SQL Server: Relationships
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 … Continue reading SQL Server: Referential Integrity
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 … Continue reading SQL Server 2000 Network Utility
You must be logged in to post a comment.