Thursday, March 29, 2012

Enums in Visual Basic 6

It appears that VB6 allows you to declare enumerations in the code anywhere just like a sub or function.  I declared an enum at the bottom of a bas file.  I declared a function somewhere about that in the code and passed it a parameter of the type of the enum.  The code failed that it did not know the type I was using and the intellisense did not see the enum either.

I moved the enum definition to the top of the bas file at the end of the variable declarions.  At that point the intellisense was happy and so was the compiler.

I did not test it far enough to find out if the error I got was just because the compiler got to my using the type first before it got to the possible error of defining the enum in the wrong place.

Detailed Instructions How To Write a Select Statement in SQL Server (Transact SQL(T-SQL))

Basic Select Statement
A Select statement returns a set of records filtered by the criteria provided.  If not criteria clauses are specified, then all records in the table is returned.

Example returning all records from a table called Employees.

Select * From Employees.

The Asterisk
The asterisk specifies that all fields in the table will be returned.

Specifying Fields
To return only specific fields omit the asterisk and list the field names separated by commas.

Example Example returning all records from a table call Employees, but only the FirstName and LastName fields.
Select FirstName, LastName  From Employees

The Where Clause
The Where clause specifies criteria for which records will be returned.  Only records that meet the Where criteria will be returned.

Example of a Select statement with a Where clause that specifies to only return records with the first name of Samuel.
Select *
From Employees
Where FirstName = ‘Samuel’

Note that strings are enclosed in single quotes, not double quotes as in some programming languages.

The Like Operator and Wildcards
The Like operator and wildcards are used to match parts of the data in a field.  One would use Like in the place of an equal sign.  Previously we used the equal sign to return all records with a FirstName field equal to the string ‘Samuel’.  If we wanted to find any records with a FirstName field that start with the string ‘Sam’ we would do the following.
Select *
From Employees
Where FirstName Like ‘Sam%’

The character % matches any string of any length.  It also matches zero length strings.
The _ matches a single character.

The Order By Clause
The Order By clause sorts the records that are returned by a Select statement.  One may specify more than one field to be ordered on separated by commas.  The first field will be the one that is sorted first and the second field will be sorted within the first.  The third will be sorted within the second within the first and so forth for as many fields as one has chosen to order on.

Example of an Order By clause sorting records by LastName and FirstName.
Select *
From Employees
Order By LastName, FirstName

One may use Asc or Desc after the field name, but before the comma to specify a descending or ascending sort on a specific field.  If neither is specified ascending(Asc) is the default.

The Group By Clause
The Group By Clause tells SQL Server how to group the data together when using aggregate functions.  Aggregate functions perform calculations on each value in a field in a database table and returns a single value.  A single value is returned for each grouping.  For example, if the Group By clause says to group on a person’s name that every record that has that person’s name on it will be rolled into one value.  Each person’s name will return one value for the field being aggregated and only one record representing all records with that person’s name.
The following sections will describe some of the aggregate functions that one can use with the Group By clause.

One may specify more than one field to be grouped on separated by commas.  The first field will be the one that is grouped first and the second field will be grouped within the first.  The third will be group within the second within the first and so forth for as many fields as one has chosen to group on.

Note that all fields in the Select to be returned from the query which are not aggregate functions must also be in the Group By clause.

Aggregate Functions
Sum()
Sum is used to add all values together in a field in all records that meet the criteria of a Group By clause.

Example of a Select statement with a Sum aggregate function summing the field HourlyWage and a Group By clause which groups on the LastName field of the Employees table.
Select LastName, Sum(HourlyWage)
From Employees
Group By LastName

Avg()
Ave Sums all values in a field in all records that meet the criteria of a Group By clause and divides them by the number of records in the grouping.

Example of a Select statement with a Avg aggregate function Averaging the field HourlyWage and a Group By clause which groups on the LastName field of the Employees table.
Select LastName, Avg(HourlyWage)
From Employees
Group By LastName

Count()
Returns the number of records in the grouping specified by the Group By clause.

Example of a Select statement with a Count aggregate function Counting the number of records in a table.  Note that no Group By is given, so all records are counted and in sub groupings.
Select Count(LastName) From Employees

Inner Join, Outer Join, Huh?
Inner Join
An Inner Join joins two tables together on a common field and only returns those records where the common fields match.

An Outer Join joins two tables together on a common field and returns those records that match and also records that do not match.  To determine which table’s fields are returned when there is no match is determined by the modifier Left or Right or Full.

Left Join.  Right Join.  Go Left.  Go Right.  What is Going On?
The Left, Right, and Full modifiers are only available with the Outer Join.

A Left Outer Join returns all records from the table on the left side of the equals sign.  The records that match the sort criteria will have the values from the fields from the other table as well.  When they do not match, the values for those fields in the right table will be null.

Example of a Select statement that joins two tables and all records from the table on the left side of the equals sign being returned.  All records will be returned from the Employees table and only the records from the Address table where the AddressId matched in the Employees table.
Select *
From Employees
Left Outer Join Address On Employees.AddressId = Address.AddressId

A Right Outer Join returns all records from the table on the right side of the equals sign.  The records that match the sort criteria will have fields from the other table as well.  When they do not match those fields will be null.

Example of a Select statement that joins two tables and all records from the table on the right side of the equals sign being returned.  All records will be returned from the Address table and only the records from the Employees table where the AddressId in the Address table matches the AddressId in  the Employees table.
Select *
From Employees
Right Outer Join Address On Employees.AddressId = Address.AddressId

A Full Outer Join returns all records from both tables.  The records that match the sort criteria will have fields from both tables.  When they do not match fields from the table that could not match will be null.

Example of a Select statement that joins two tables and all records from both tables being returned.  Nulls will be returned for records that do not match the join field.
Select *
From Employees
Full Outer Join Address On Employees.AddressId = Address.AddressId


The Having Clause
The Having clause is similar to the Where clause, but is for use with the Group By clause and aggregate functions.

Example of a Select statement that filters on the Count of employees that have the same last name.  It only returns last names that appear on more than one record.

SELECT LastName, Count(LastName)
FROM Employee
GROUP BY LastName
HAVING Count(LastName) > 1
ORDER BY LastName;

Subqueries
One can also create subqueries within SQL queries.

Example of a subquery that finds the date that an employee’s address was change last.
Select Employees.FirstName, Employee.LastName,
       (Select Max(Address.ChangeDate)
        From Address
        Where Address Employees.AddressId = Address.AddressId) As MostRecentAddressChange
From Employees

Aliasing Tables and Fields
One can change the name that they reference a table as in the query or the name of the field that is returned from the query by using aliases.

Example of using Aliases for tables and field names.
Select e.FirstName As FName, e.LastName As LName, a.ZipCode As Zip
From Employees As e
Full Outer Join Address As a On e.AddressId = a.AddressId

Notice that when I reference the tables in the query I only need to use their aliases, which has the potential to make it shorter and more readable.  In my case I just use a single letter.  Using a little longer Alias might be more readable, without having to type out the entire table name.


Monday, March 26, 2012

SQL Server Acronyms

I have titled this post SQL Server Acronyms, but I think several of these acronyms refer to SQL in general and not a specific implementation like Microsoft SQL Server.

I had a job interview recently and the interviewer quized me on some SQL Server acronyms.  I was not as prepared as I should've been.  I had heard several of them, but not committed them to memory.  I write the code and don't always remember the catch phrases.

I have created a list below.  Some the interviewer asked about and some I came across as I was researching the others.


  • SRS/SSRS - SQL Reporting Services/SQL Server Reporting Services
    • I have seen this referred to as SRS in some places and SSRS in others.
    • It is a Microsoft reporting engine.
    • It competes with Crystal Reports and other reporting technologies.
    • The reports are defined with RDL - Report Definition Language.
  • SSIS - SQL Server Integration Services
    • Performs data migration tasks.
  • SSB - SQL Server Service Broker
    • Provides message queuing.
  • DTS - Data Transformation Services
    • Use to load, extract, and transform data in and out of a database.
  • DML - Data Manipulation Language
    • Things like Update, Insert, and Delete.
  • DDL - Data Definition Language
    • Things like Alter, Create, and Drop
  • DCL - Data Control Language
    • Things like Grant and Revoke statements
  • TCL - Transactional Control Language
    • Things like Commit and Rollback
  • DQL - Data Query Language
    • Some resources I have been reading say DQL is Select statements and others say Select is part of DML.

Truncate Table SQL Server

I was asked in an interview not long ago what Truncate did in SQL Server.  I had remembered seeing a Truncate statement in the code of the project that I am currently working on, but couldn't remember what we used it for.  I came home and read up on it.

The statement is Truncate Table [table name].

It appears to be the equivalent to the DELETE statement without a where clause.  Basically it dumps all your records in the table.  This is handy for temp tables that you might be using and reusing for heavy lifting.

Truncate Table is faster than Delete.  It is likely that truncate doesn't pick each row, but just dumps them all.  Perhaps moving a record pointer to the top of the table and letting all the other memory be reclaimed.  It also doesn't appear to do all the logging that Delete does.  It you need logging you should stick with Delete and take the performance hit.

In many ways the end result of Truncate Table is the same as executing a Drop Table and then a Create Table with the exact same table layout.

There seem to be limitations on the Truncate command that it can't be used if the table has a foreign key constraint.  The Delete statement does not have that limitation.