Reloading a Form in C#

I'm been doing some work recently in C# on a Windows Form I'm creating for a pet project.  I'm creating a form that presents customer information and allows the user to scroll through each customer using "Previous" and "Next" buttons.

While working through the form, I was able to get my data into an array and then present the first customer in the form.  I then figured out how to update the index of the array for the next customer, but I couldn't figure out how to present the next data into the form.  Through my research, I found the following post:

Reloads Form in C#

Scrolling through the answers, one answer says this:

try this

this.formName_Load(this, null);

---

Kapul

I then put this code into my program right after I've updated the index of my array for the next customer.  Magically (not really) the form reloaded with my new data.

I wanted to discuss the different portions of the above code and what it does:

• this - refers to the current instance of the class.  For example, you could have multiple classes that have the same form names and each could be referenced by other classes.  "this" makes sure that you are referencing the current class.
• formName - the name of your form.  For example, my form is called "customerForm", so I would replace formName with customerForm.
• _Load - tells the form to load.  To read more on Load event, go here: Form.Load Event
• (this, null) - The "this" again refers to the current class.  null is used in place of the EventArgs class (which is used to provide data for events).  Since this is an event that doesn't contain any data, we then pass a null, which is an empty field.  To read more on the EventArgs class, go here: EventArgs Class

Calculating a Remainder in SQL

When doing math problems using SQL, it is a good idea to consider what type of data you are working with.  In SQL, you will often deal with a data type called Integer.  With integers, if you do any division, all your answers will come out in whole numbers (i.e. you won't get any decimal numbers back).  For example, if you run the following in SQL, the answer will be 2 instead of 2.5:

select 5 / 2

Clearly you can start to lose data if you are expecting decimal places while dividing integers.  It is often better to convert your integers to a data type that allows for decimals.  For example, you can convert your integer data types to numeric and then perform the division.

select cast(5 as numeric(2,1)) / 2

If you don't want to cast your data to another data type because of possible loss of data, you can also use the Modulo operator (sometimes called Modulus in other programming languages).  To use the Modulo operator, replace the divide sign (/) with the percent sign (%).  Using the same example from above, we will use the Modulo operator:

select 5 % 2

The answer to the above would then be 1, which is the remainder of 5 / 2.

We could then display our data using the following query:

select ' 5 / 2 is ' + cast(5 / 2 as varchar) + ' with a remainder of ' + cast(5 % 2 as varchar)

Which gives us the results of

 5 / 2 is 2 with a remainder of 1

To read more on Modulo, go here: Modulo

Arithmetic Operators in SQL

Yesterday we talked about variables.  Today we will talk about an arithmetic operators that can be applied to your variable.

An arithmetic operator is one that applies some type of math to a value.

Let's first look at an example of code.  For instance, if you wanted to add a 1 onto an integer variable, the most basic way to do this is by doing the following:

set @integer = @integer + 1

Since you want to add 1 onto the original value of your integer, you have to set the variable equal to your current value plus 1.  Using the above, however, requires unnecessary retyping.  A better way to approach this would be to use the Add Equal operator, like so:

set @integer += 1

The above will add one onto the current value of the variable @integer, while only using our variable once.  This gives us less of a chance of a spelling mistake.  The same works for the Subtract Equals (-=), Multiply Equals (*=) and Divide Equals (/=) operators.

If we create different integer variables, we can see how each works

declare @integer1 int = 1
declare @integer2 int = 1
declare @integer3 int = 2
declare @integer4 int = 2

set @integer1 += 1
print @integer1

set @integer2 -= 1
print @integer2

set @integer3 *= 2
print @integer3

set @integer4 /= 2
print @integer4

We would get the following results:

2 (1 + 1 = 2)
0 (1 - 1 = 0)
4 (2 * 2 = 4)
1 (2 / 2 = 1)

As a programmer, it is always better not to have to retype code or variables.  In this case, you are saving yourself from retyping variables.


To read more on Arithmetic Operators, go here: Arithmetic Operators

SQL Variables

It's been a while since the last time I blogged.  After a family reunion and a busy week at work, I've been slow to get back into the blogging side of life.

Today I've decided to start off with a subject that is rather easy.

In SQL, as with most programming languages, you will find something called a variable (it may be called something else in other programming languages).  A variable is a set of data that you give a name to and then can assign values to.  For example, in SQL you could have a variable called @pi (the @ symbol tells SQL that this is a variable) and assign it a value of 3.14.  If you need to use the 3.14 value throughout your program, you can then call @pi instead of 3.14 each time.

You might be thinking to yourself "why would I want to create/use a variable instead of just entering 3.14 every time I need it?"  Well, there are many ways to answer this question, but the best answer is that what would happen if you accidentally entered 3.15 in one instance instead of 3.14?  You could potentially throw off the rest of your program.  A variable allows you to use the same value over again without having to re-type it and therefore not leaving yourself open for mistakes.

There are two steps to creating variables:

1. Declare the variable.  You have to let SQL know that the variable exists and give the variable a data type (int, varchar, datetime, etc.)
2. Set a value to the variable.

So, if I wanted to create my @pi variable, I would do it like this:

DECLARE @pi numeric(5,2)

SET @pi = 3.14

You can also declare and set the variable on the same line:

DECLARE @pi numeric(5,2) = 3.14

We can then take that variable and use it:

@Circumference = 2 * @pi * radius

As you dive more into using variables, you will see they are quite useful.

To read more on SQL variables, go here: Local Variables

Using Multiple Databases in SQL

In SQL Server, you can have multiple databases on the same instance of SQL Server.  In SQL Server 2012, you can have over 32,000 (go HERE to read up more maximum capacity specifications for SQL Server) databases in one SQL Server instance.  So, the question is, how do you use/reference all these databases in the same query.

There are two main ways to accomplish this:

1. The use of USE
2. Reference the database in your FROM statement

First, we can use the word USE.  The general syntax for this is:

USE database

Where database is the database you are referencing.  If our Customer table was in the Billing database, it would look something like this:

USE Billing

SELECT *

FROM Customer

Using USE leaves you rather limited, though, because only the one database can be referenced at a time using USE.

Our other option is to specify the database in our FROM statement.  Let's say, for example, that we have our Customer table in the Billing database, while the Orders table is in the Shipping database.  If we wanted to use both tables in the same query, it would look like this

SELECT *

FROM Billing.dbo.Customer

LEFT OUTER JOIN Shipping.dbo.Orders

on Customer.CustomerNumber = Orders.CustomerNumber

In the above, we put the database, a dot (.), the user (in this case dbo, which means database owner), another dot (.) and then the table name.  Using this method, we can query multiple databases at the same time within the same query.

To read more on USE, go here: USE

To read more on selecting your database, go here: Select a Database

Wildcards while Searching Text in SQL Continued

One part of using wildcards that wasn't covered yesterday was the ability to use the keyword NOT in conjunction with LIKE.  Using an example from yesterday, this returns all customers that start with the letter L:

SELECT *
FROM Customer
WHERE CompanyName LIKE 'L%'

If we put the word NOT before LIKE, we will get all customers that do not start with an L:

SELECT *
FROM Customer
WHERE CompanyName NOT LIKE 'L%'

Likewise, we can exclude any customer that has an L in their name:

SELECT *
FROM Customer
WHERE CompanyName NOT LIKE '%L%'

The same works with another one of our examples:

SELECT *
FROM Customer

WHERE FirstName LIKE '_ean'

The above returns all customers that have a name that is four digits and ends in "ean".  The following returns any customer not with four digits AND does not end with "ean".  It could still be four digits, but cannot end with "ean".

SELECT *
FROM Customer

WHERE FirstName NOT LIKE '_ean'

Wildcards while Searching Text in SQL

Let's say you are looking for some text in SQL, but not exactly sure what the text is.  For example, you are looking for any customer where their last name starts with an L.  How would you do that in SQL?

There are two ways you can accomplish this.  The first is by using substring like the following:

SELECT *
FROM Customer
WHERE Substring(CompanyName, 1, 1) = 'L'

This would certainly work, but makes your search NonSARGable (to read up more on SARGable queries, go to this post Non-SARGable Queries)

Luckily, SQL has a logical operator called LIKE (which keeps your query SARGable), that allows you to search for text that matches a specific pattern, as follows:

SELECT *
FROM Customer
WHERE CompanyName like 'L%'

In this instance, the percent sign (%) allows for any characters before or after it, depending on where the % is placed.  The above would look for customers that started with an L.  The following would look for all customers that ended with an L:

SELECT *
FROM Customer
WHERE CompanyName like '%L'

This would look for all customers that had an L anywhere in its name:

SELECT *
FROM Customer
WHERE CompanyName like '%L%'

The % is a wild card option in SQL.  There are other wild card options to consider:

• _ (underscore)
• The underscore represents a single value.  For instance, if you are looking for a four letter name that ends in "ean", you could run the following:

SELECT *
FROM Customer

WHERE FirstName like '_ean'

• [ ]
• A single character within a range or given set.  For instance:

--Range of letters

SELECT *
FROM Customer

WHERE FirstName like '[D-S]ean'

--Set of letters

SELECT *
FROM Customer

WHERE FirstName like '[DEFGHS]ean'

• [^]
• A single character not within a range or give set

--Not in this range of letters

SELECT *
FROM Customer

WHERE FirstName like '[^D-S]ean'

--Not in this set of letters

SELECT *
FROM Customer

WHERE FirstName like '[^DEFGHS]ean'

To read more on LIKE and different wild card options, go here: LIKE

WHILE Statements in SQL

Previously I've spoken about control of flow statements in SQL (see the following: IF...ELSE, GOTO).  Today I will talk about another control of flow statement: the WHILE statement.

A WHILE statement allows you to specify a statement to check and then SQL statement(s) to run while that expression is still true.

The general outline for a WHILE statement is as follows:

WHILE Boolean_expression
{ sql_statement }

• A boolean expression is one that is either true or false
• The SQL statement(s) are the statements to run while the WHILE statement is still true
• If your SQL statement is more than one statement, you will have to begin and end your WHILE statement with BEGIN and END, as seen below.

WHILE Boolean_expression

BEGIN

SQL Statement Number 1

SQL Statement Number 2

END

Following is an example of when a WHILE statement may be useful:

WHILE (SELECT count(*) FROM Customers Where Balance < 50) > 0

BEGIN

UPDATE Customers

SET Balance = Balance * 2

Where Balance < 50

END

The above will update the balance of a customer to their balance times 2 until that balance is above $50.  For example, if we have a customer whose balance is $5, it will go through the WHILE statement 4 times until the balance is $80.

Keep in mind that when WHILE statements are used, it is possible to run into an infinite loop (a loop that never stops) if you aren't incrementing your values correctly.  Using the same query, if I change it a little bit, I can get an infinite loop:

WHILE (SELECT count(*) FROM Customers Where Balance > 50) > 0

BEGIN

UPDATE Customers

SET Balance = Balance * 2

Where Balance > 50

END

The above will never stop if I have one customer that has a balance over $50.  Because they are over $50, our boolean expression is and will always be true.

To read up more on WHILE statements, go here: WHILE

Creating a Child Form in C#

I spent some time this weekend working in C# on a personal project.  One of the subjects I came across was creating a child form.  A child form opens a new form within your current window.

During my research, I came across the following help document:

How to: Create MDI Child Forms

I have a program I created at work that has different tabs for specific tasks that need to be run.  I will be working on migrating from the tabbed format to the child form format, as this seems to be less confusing and the layout looks better.

A few key points for creating a child form:

1. Create a new windows form in your project for each new window.
2. Create an event handler for when you click on the button/menu item that will open the new form (Create Event Handler)
3. Paste the following in your event handler:

protected void MDIChildNew_Click(object sender, System.EventArgs e)
{

   Form2 newMDIChild = new Form2();
   // Set the Parent Form of the Child window.
   newMDIChild.MdiParent = this;
   // Display the new form.
   newMDIChild.Show();
}

Subqueries in SQL

A very helpful feature of SQL is the use of subqueries.  These subqueries allow you to nest one query inside of another query, thus giving you more flexibility with your data.  There are two areas in which a subquery can be used.  The first of these areas is in the SELECT statement.  For example, if we wanted to see how many purchases a customer has made, we can run a query like this:

SELECT CustomerNumber, CustomerName,
(SELECT SUM(Quantity)
FROM Orders
WHERE Orders.CustomerNumber = Customer.CustomerNumber) as Quantity
FROM Customers

In the above example, we used a subquery on the Orders table to get the total quantity (number of purchases) and then the Customers table to get our customer information.

The second instance where a subquery can be used is in the WHERE clause.  This gives us the ability to look for a particular set of data.  As another example, let's say that we want to look for customers that have an order greater than $50:

SELECT *
FROM Customers
WHERE CustomerNumber in
(
SELECT CustomerNumber
FROM Orders
WHERE TotalPrice >= 50.00
)

The subquery returns all CustomerNumbers that have an order of $50 or greater.  We then tell SQL to look for all those (in) CustomerNumbers in our Customers table.  We could also tell SQL to give us CustomerNumbers that are not in the list by putting the word "not" in front of "in, like so:

SELECT *
FROM Customers
WHERE CustomerNumber not in
(
SELECT CustomerNumber
FROM Orders
WHERE TotalPrice >= 50.00
)

This would return all customers that did not have an order of $50 or more.  The word "in" in this situation is called a "logical operator", which we will discuss in my next blog.

To read more on subqueries, go here: Subquery Fundamentals

SQL Joins Part 3

I was doing some research on the CROSS join since I haven't put this join into practice.  I'm still having a hard time imagining when this would be useful.

For purposes of completeness, I will explain how a CROSS join works.

Unlike other joins, a CROSS join does not have an "on" statement where you provide columns for comparisons.  You can insert comparison columns into the where clause, but when this occurs, you are basically running an inner join.

Following is an example of CROSS join, using two tables that are not related in any way.

SELECT *
FROM Customers
CROSS JOIN Vendors

If our Customers table has ten rows and our Vendors table has ten rows, this query will return 100 rows.  Each row from the first table will show with each row in the second table.

Following are two tables that do have comparison columns, with which we will run a CROSS join:

SELECT *
FROM Customers
CROSS JOIN Orders
WHERE Customers.CustomerNumber = Orders.CustomerNumber

We've essentially just run an inner join.

To read more on Cross Joins, go here: Using Cross Joins

What situations do you think a CROSS join could be useful?

SQL Joins Part 2

Yesterday we spoke about OUTER joins.  Today we will talk about INNER joins.  INNER joins will return all rows from both tables, as long as the values in the columns you are joining on match.  This differs from OUTER joins in the fact that OUTER joins could in fact return NULL values for one table.  INNER joins only return those values that match within both tables.

Going back to our example of yesterday, we will use the Customers and Orders tables as examples.  First, lets look at an OUTER join:

SELECT *
FROM Customers
LEFT JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber

The above would return all Customers and any matching orders in the Orders table, even if no orders exist.  Next, we will use the INNER join, as follows:

SELECT *
FROM Customers
INNER JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber

This will only return data where it exists in both tables.  If a customer doesn't have any orders, no data will be returned for that customer.

As part of your column matching, you can also use math operators ( >, <, <>).  For example, if we want only orders that are over a certain amount, you would run a query like so:

SELECT *
FROM Customers
INNER JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber and Orders.Amount > 10.00

This will return only those orders where the amount is greater than $10.

To review, OUTER joins will return data for both tables, even if data doesn't exist (it will return NULLs for non-existent data).  INNER joins, on the other hand, will only return data that exists, based upon your matching columns.

To read more on INNER joins, go here: Using Inner Joins

SQL Joins Part 1

In SQL, there are three different types of JOINs, which, if mastered, can be extremely helpful in querying your data.  The three different joins are:

1. OUTER JOIN
2. INNER JOIN
3. CROSS JOIN

First, with any JOIN statement, you need to specify the columns you will join on.  This tells SQL which columns to use as comparison.  After our second table, we put the word "on" and follow that with our columns comparisons.  As we jump into this post, we will see the "on" used in our FROM clause.

Today I will focus on the OUTER Joins.

Within the OUTER Joins there are the LEFT, RIGHT and FULL JOINs.  These joins are pretty easy to understand.  A query containing a LEFT or RIGHT JOIN will return all rows in the described table (left or right) and matched rows in the other table.  This is defined by which table is on the left (the first table in your FROM clause) or on the right (the second table).  Here is an example if we are using our Customers and Orders tables:

SELECT *

FROM Customers 

LEFT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

In this example, Customers is on the left, while Orders is on the right.  This will return all rows in the Customers  table and any matching data in the Orders table.

We can then reverse it for another example:

SELECT *

FROM Customers 

RIGHT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

The above will return all rows in the Orders table and any matching rows in the Customers table.  You can also get the same results by running the following:

SELECT *

FROM Orders

LEFT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

Notice that Orders is the "left" table and Customers is the "right" table.  It may become more evident why you would use LEFT or RIGHT when we reference more than one table.  Let's take our example and add OrderDetails into the mix:

SELECT *

FROM Customers 

LEFT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

LEFT JOIN OrderDetails on Orders.OrderNumber = OrderDetails.OrderNumber

This query gives us all Customers with matching rows in the Orders table and then all OrderDetails matching with the Orders table.  We could accomplish the same by running the following:

SELECT *

FROM OrderDetails 

RIGHT JOIN Orders on Orders.OrderNumber = OrderDetails.OrderNumber

RIGHT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

We could also mix it up:

SELECT *

FROM OrderDetails 

RIGHT JOIN Orders on Orders.OrderNumber = OrderDetails.OrderNumber

LEFT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

This would give all rows in the Orders table with matching rows in the OrderDetails and Customers table.

A FULL Join will return all rows from both tables.  Any row from one table that doesn't have a match in the other table will return all NULLS for the other table.

SELECT *

FROM Customers

FULL JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

To read up more on SQL Join, go here: Using Outer Joins

GOTO in SQL

I've previously talked about control-of-flow language in SQL.  Another way to control the flow of your programs is using the GOTO.  GOTO allows you to automatically jump to a particular portion of your code, while skipping other portions.

Here is a good example of what GOTO would do:

DECLARE @Counter int;
SET @Counter = 4;
WHILE @Counter < 10
BEGIN 
    SELECT @Counter
    SET @Counter = @Counter + 1
    IF @Counter = 4 GOTO Branch_One
    IF @Counter = 5 GOTO Branch_Two
END
Branch_One:
    SELECT 'Jumping To Branch One.'
    GOTO Branch_Three;
Branch_Two:
    SELECT 'Jumping To Branch Two.'
    GOTO FINISH;
Branch_Three:
    SELECT 'Jumping To Branch Three.'
 
FINISH:
SELECT 'FINISH'

You declare your GOTO section by giving it a name and then following it with a ":".  After you specify your section, you can then put in what you want to happen in that section.

You can specify in your code that you want to go to that section by saying "GOTO" and then the section name.  The above query gives the following results:

4
Jumping To Branch Two.
FINISH

Keep in mind that the program will continue to run from the current GOTO onward if no other GOTO's are specified. If we remove the reference to "GOTO FINISH" in Branch_Two, it will then move to Branch_Three and then FINISH.  Also, you can go to sections before or after the current section.  For example, from Branch_Two, you can then jump back to Branch_One and from Branch_Three you can go to Branch_One or Branch_Two.  Be careful, because if you aren't paying attention, you could put yourself in an infinite loop.

To read more on GOTO, go here: GOTO

Question of the Day 2

I've had my second Question of the Day presented on SQLServerCentral.com.  To visit the question of the day, go here: SQL CHOOSE

To re-read my post on SQL CHOOSE, go here: SQL CHOOSE Post

You can also join the discussion on the question by going here: SQL CHOOSE Discussion

I'm certainly learning a lot about what needs to be considered when putting together these questions.  With this question of the day, I've found out that people outside of the U.S. are answering.  Because of this, they are using a different language for SQL Server than me, which apparently affects how different data is presented.  In my case, dates are presented differently depending on the language you use and this caused more people to get unexpected errors.  This is a good thing to consider in case you are working with people overseas.

Using APIs

I've been spending much of my time recently using APIs (application programming interface) at work, so I haven't had much time to research SQL topics.  For any of you who don't know, APIs are used as a way into an application without providing the code/table diagrams underneath.

For example, if I, as a programmer, want to give people access into my database, but not allow them in directly, I would give them access to an API.  APIs are used for inserting data into the database or retrieving information without giving away what's in your database.

Anyway, I've been spending time recently trying to use some of the APIs from our billing vendor to see if I can increase my work performance.  I'll keep you informed of anything I think worthy of note.

Thanks.

SQL Views

I've briefly spoken about SQL Views in prior posts (see here), so I wanted to take this opportunity to talk a little more about SQL Views.

First, what is a SQL view?

A view is just exactly what it sounds like: it allows you to "view" information.  Views can and are usually created to present data in a certain way.  It is what we may call a virtual table, meaning that the table doesn't actually reside in the database the way it is presented in the view.

A view allows you to take a T-SQL query, spanning across multiple tables if needed, and present it as a view.  Then, someone can write a query using the view and get all the data they need.  This helps to avoid writing the same query multiple times.  The query is already written as a view.

An example might help us better understand the concept.  Let's say that we have three different tables: Customer, Orders and OrderDetails.  We use the following query to get information for an order:

SELECT *
FROM Customer
join Orders on Customer.CustomerId = Orders.CustomerId
join OrderDetails on Orders.OrderId = OrderDetails.OrderId

It can sometimes be difficult to remember the relationship between each table, so instead of creating the same query over and over again, we create a view.  We create a view using the following syntax:

CREATE VIEW [dbo].[vOrderDetail]

AS

SELECT *
FROM Customer
join Orders on Customer.CustomerId = Orders.CustomerId
join OrderDetails on Orders.OrderId = OrderDetails.OrderId

Now, when you want to get the information for a customer order, your query can look like this:

SELECT *
FROM vOrderDetail

To avoid confusion, it is a good practice to name your views differently from your tables.  In my example, I put a lowercase "v" at the beginning of the view name so I know I am using a view.

Once you've created your view, now you can give people access to views.  These are used for just viewing data, users can't update or delete these records.  The deletion or updating of records has to be done on the tables the view refers to.

To read more on views, go here: SQL Views

Querying NULL Values in SQL

When querying a SQL database, you need to be aware of the presence of NULLs.  In SQL, a NULL represents an unknown, non-existent or empty value.  Sometimes the NULL value is put in explicitly or no values was specified for the column, so a NULL was entered.  The reason we need to be careful is we cannot query NULLs the way we do for data types.

For example, let's say that you wanted to query a customer's balance where it is zero (either the customer has paid his balance or he hasn't made any purchases).  Since this would be a money or other number type, you could run a query, like so, to get all your customers:

SELECT *
From CustomerTable
where Balance = 0

But, depending on how you set up your table, new customers may not have a value in the Balance field.  Therefore, this value could be a NULL.  The above query would not return a NULL value, so you aren't getting all the results you require.

There are two ways you can update your query.  Here is the first one:

SELECT *
From CustomerTable
where Balance = 0 or Balance IS NULL

To query on NULL values, you need to specify IS NULL or IS NOT NULL.  This is the only way to query NULLs directly.

The other way to query NULLs is the following.  Keep in mind that this will work, while also making your query NonSARGable:

SELECT *
From CustomerTable
where ISNULL(Balance, 0) = 0

ISNULL() is a function that allows you to convert NULLs in your field to a specified value.  ISNULL() is used like this:

ISNULL( column, value to replace NULL)

We replaced our NULLs with the value of 0.  We can then query all 0s using one simple clause.

To read more on NULL values, go here: NULL Values

Index Design Tips

I've written previously about SQL Indexes.  I've been reviewing tips on Index Design and came across the following Microsoft site:

General Index Design Guidelines

A few things I thought were interesting in the article:

• A large number of indexes on one table can affect your Update, Insert and Delete statements.
• Keep the number of indexes or index columns small on these tables.
• It may be a good idea to create a view and create indexes on the view rather than on the table that will receive Update, Insert of Delete.  This would allow you to modify your original table, but view the data while using a cluster.
• Indexing small tables is not always optimal, since it may take longer to search through the indexes than to find the records.
• Create nonclustered indexes on columns that are referenced frequently in your queries.
• Order the columns in your index based upon how often those columns are used.  For example, if column c is used more than b and b is used more than a, then the column order in the index should be: c, b, and a.
• Columns that are referenced in query using exact matches could be a good candidate for clustered or nonclustered indexes.

These are all good pointers and should get any person inexperienced in creating indexes started.  From there, you can usually play around with different indexes until you find the right ones.

What types have you found while creating indexes on your database?

IF....ELSE in SQL

Every programming language has flow of control ability.  What I mean by this is the language allows you to check your data and make a decision based on what is returned.  One of the most popular ways to control the flow of your program is an IF/ELSE statement.  An IF/ELSE statement allows you to check a value and IF it matches a certain criteria, do something, ELSE do something else.

The basic look of an IF/ELSE statement looks like this:

IF expression
    do this
ELSE
    do this

Let's say that we are building a table of customer data.  Before we continue, we want to check and see if we have any customers in our table.  If we do, print one message or else we print another message:

IF (select count(*) from CustomerTable) > 0
    PRINT 'We have customers'
ELSE
    PRINT 'We dont have customers'

If your block statement after the IF or ELSE does more than one thing, you will need to insert a BEGIN and END at the beginning of each block.  Below is an example:

IF (select count(*) from CustomerTable) > 0
BEGIN
    PRINT 'We have customers'
    PRINT 'That is great'
END
ELSE
    PRINT 'We dont have customers'
    PRINT 'Isnt that sad?'
END

These are very simple examples.  You can do much more than print messages to the screen.  You can also update data, delete data, select data, and much more.  Below is one example:

IF (select count(*) from CustomerTable ) > 0
BEGIN
    delete from CustomerTable
    PRINT 'We just deleted our customers'
END
ELSE
BEGIN
    PRINT 'We dont have customers'
    PRINT 'Isnt that sad?'

END

To read more on IF/ELSE statements, go here: SQL IF/ELSE

Using CONCAT in SQL

In my line of work, we regularly put different fields together for representation to our customers.  There are two ways we can approach this in SQL:

• Concatenate the fields together using the "+" sign
• Using the CONCAT() function

Both of these approaches give the same results in most cases, so why should you use one solution over another?  I would first say because of how it looks.  For example, if we want to concatenate the first name and last name together, here is what our two solutions would look like:

Using "+" sign:

SELECT FirstName + ' ' + LastName from Customers

Using CONCAT() function:

SELECT CONCAT(FirstName, ' ', LastName) from Customers

This is a pretty simplistic example.  If we use something with more fields, it may become more evident.  For example, let's say that we have three different fields for a customer's phone number, we could put the three fields together like so:

Using "+" sign:

SELECT '(' + AreaCode + ')' + CityCode + '-' + Suffix from Customers

Or

Using CONCAT() function:

SELECT CONCAT( '(', AreaCode, ')', CityCode, '-', Suffix) from Customers

Another reason to use the CONCAT() function is that integer values are automatically converted to string.  If, for example, you put numbers together when using your "+" sign, SQL will add the two values together instead of concatenating them:

This returns the value of 3:

SELECT 1 + 2

This returns the value of 12:

SELECT CONCAT(1, 2)

In the first example, you will need to make sure you are converting integer values to a string, which could be quite cumbersome.

To read more on the CONCAT() function, go here: SQL CONCAT

Rounding Up in SQL

Working in the telecommunications industry, you run into situations not always found in the real world.  For example, when billing a call or applying taxes to an account, you always round up.  We can't charge a customer anything less than 1¢ (since they can't pay less than 1¢), so you have to round up.

SQL has a function that allows you to perform rounding on your values.  This function is called ROUND(), but only does true rounding (below 5 = round down, above 5 = round up).  So using this function, how do we always round up?

First, we have to figure out how many decimal places we will be billing our customer.  Let's say, for example, that our customer's calls are rounded to the nearest two decimals.  Once we know how many decimals we need to round, we can apply our logic.  The logic is then to add 49 one digit past the decimal place you are trying to round.  In this case, since we want to round to the nearest two decimal places, we will add $0.0049 to our value and then round.  If we wanted to go three decimal places, we would add $0.00049, $0.000049 to four decimal places, etc.

The following will round to $0.01:

select ROUND(0.013, 2)

But if we want to always round up, we need to add $0.0049:

select ROUND(0.013+0.0049, 2)

The above rounds to $0.02.

Now, you might be asking yourself: why do we add $0.0049 instead of $0.005?  Well, adding $0.005 might inadvertently makes us round higher than we want to go.  Look below for an example, which will round up to $0.03:

select ROUND(0.020+0.005, 2)

When we actually want it to stay at $0.02:

select ROUND(0.020+0.0049, 2)

I've found it is more productive to add $0.0049 to the number and then round, as opposed to trying to figure out a way for SQL to round up only those values that need it.

To read up more on rounding, go here: SQL ROUNDING

Sending Email Using C#

I thought I would change things up a bit today and actually talk about a little bit of C#.  I currently use Visual C# at work for some projects and some of those include sending email.  First, you need to make sure to add the System.Net.Mail namespace to your C# project.  This namespace gives you a list of different classes you can use to send email.  Once the namespace has been added, here is the code I use to create a method to send email.  It allows you to provide the body, subject and an attachment to the method:

public static string SendEmail(String body, String subject, Attachment attachment)
        {
            SmtpClient smtp = new SmtpClient("mail.companyname.com");

            string fromEmail = "my.email@companyname.com";

            MailMessage objEmail = new MailMessage();

            objEmail.IsBodyHtml = true;

            objEmail.To.Add("to.email@companyname.com");

            objEmail.From = new MailAddress(fromEmail, "My Name");

            objEmail.Subject = subject;

            objEmail.Body = body;

            objEmail.Attachments.Add(attachment);

            try
            {
             
                smtp.Send(objEmail);

                return "true";

            }
         
            catch (Exception exc)
         
            {
                return exc.Message;
             }

        }

If you wanted to create a method that sends an email, but doesn't include an attachment, use the following:

public static string SendEmail(String body, String subject)
        {
            SmtpClient smtp = new SmtpClient("mail.companyname.com");

            string fromEmail = "my.email@companyname.com";

            MailMessage objEmail = new MailMessage();

            objEmail.IsBodyHtml = true;

            objEmail.To.Add("to.email@companyname.com");

            objEmail.From = new MailAddress(fromEmail, "My Name");

            objEmail.Subject = subject;

            objEmail.Body = body;

            try
            {
             
                smtp.Send(objEmail);

                return "true";

            }
         
            catch (Exception exc)
         
            {
                return exc.Message;
             }

        }

These methods are pretty basic, since the email addresses are hard-coded.  You will want to create your own method if your "to" email is dynamic or you want to pass the email values into the method.

SQL CHOOSE

SQL Server has a list of logical functions that can be used on data.  The first of these logical functions is the CHOOSE() function.  This function will return the value identified at the specified index.  Here is what the syntax for the CHOOSE() function looks like:

CHOOSE(index value, value 1, value 2, value 3, etc.)

A pretty simple example of the CHOOSE() function looks like this:

SELECT CHOOSE(3, 'Executive', 'Senior Management', 'Vice President')

The above would return 'Vice President', since the index for lookup is 3 and the third value is 'Vice President'.

So how would a function like this be useful?  Well, we could use it to list out the months of the year for an employee's birthday, like so:

SELECT CHOOSE(Month(Birthday), 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')
FROM Employee

How does this differ from what you might see using the CASE() function?  There isn't a difference.  CHOOSE() is just a short hand version of CASE().  It definitely looks neater than CASE(), since you don't have to do so much coding.  Using the example above, CASE() would look something like this:

SELECT CASE Month(Birthday)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
FROM Employee

As you can see, much mode coding needs to be done, while also adding the value you are looking for.  CHOOSE() already knows the value you are looking for and selects it based on the order in the provided value list.  Any value searched for that is not contained in the value list (such as 13 for our months) will return a NULL value.

How do you think you could use the CHOOSE() function at your organization?

To read up more on SQL CHOOSE(), go here: SQL CHOOSE

SQL SUBSTRING

There may be reasons why you may only need part of the text and not the whole thing.  For example, if you wanted to organize your customer list by first initial of their company name.  How would you do this?  SQL Server has a function called SUBSTRING().  This function allows for you to specify your starting position and the number of digits you want to take.  If you want the first digit of a customer's company name, you would write your query like this:

SELECT SUBSTRING(CompanyName, 1, 1), *
from CustomerList

You could also use it to find out the Area Code of your customers, by getting the first 3 digits of their phone number:


SELECT SUBSTRING(PhoneNumber, 1, 3), *
from CustomerList


Unlike other programming languages, SQL's SUBSTRING() function is 1-based, while other programming languages are 0-based.  This means that if you want the first digit in SQL, you use 1.  With C#, for example, if you wanted the first digit, you would use 0.

You can also run a query to see how many customers you have for each letter in the alphabet.  This could be done by combining the SUBSTRING() function with a group by, like so:


SELECT SUBSTRING(CompanyName, 1, 1), count(*)
from CustomerList
group by SUBSTRING(CompanyName, 1, 1)
order by SUBSTRING(CompanyName, 1, 1)


SQL SUBSTRING()

What data could you see using the SUBSTRING() function on?

Question of the Day

A question I put together has been featured as the "Question of the Day" at one of the sites I regularly visit (sqlservercentral.com).  Go check it out and see if you can get the answer right (you will have to register):

CHARINDEX

Here's some help if you need it:

SQL CHARINDEX

SQL SUBSTRING

Using EXCEPT in SQL

EXCEPT is an Operator in SQL that will return any distinct values found in the "left" table that is not found in the "right" table.  How can this be useful?  Let's say that one day you notice you have some data corruption in your database.  Your Orders table has some orders that are not found in the OrdersDetails table.  How do we easily figure out which orders are missing?  Our initial query could look something like this:

SELECT distinct OrderNumber
from Orders o
LEFT OUTER JOIN OrdersDetails d
on o.OrderNumber = d.OrderNumber
WHERE d.OrderNumber is null

This gets the job done, but doesn't look very organized.  We have to make sure we:

• Have the correct join (LEFT or RIGHT, depending on which table has the rows we want)
• Make sure the NULL is on the correct table (NULL would return where rows don't exist).

An easier and more efficient way to do this is to use the EXIST Operator in SQL:


SELECT OrderNumber
from Orders
EXISTS
select OrderNumber
from OrdersDetails

You will want to make sure that your data types for comparing are the same.  If not, it is possible you will not get expected results.

Where do you think you could use EXCEPT in your organization?

To read more on EXCEPT, go here: SQL EXCEPT

SQL Having Clause

Let's say you have a query that you want to run, where you summarize customer information (order totals, number of calls, etc.).  With your query, though, you only want to include customers that have over a certain value for what you are summarizing.  The SQL WHERE clause doesn't allow for aggregate functions, so what do you do?

To exclude customers under a certain value, you would have to use the SQL HAVING Clause.  There are a few things you need to keep in mind when using the HAVING Clause.

• The HAVING Clause occurs after your GROUP BY.
• To use the HAVING Clause, you must have a GROUP BY because you are aggregating data together
• You do not need a WHERE Clause

So, how does the HAVING Clause work?  Let's say that we want to see the total dollar value of a customer's order.  The statement would look something like this:

SELECT LastName, SUM(Total) Total

from OrderDetails

Group By LastName

Now, we want the query to return customers whose sum total is greater than $100.  That query would look like this:

SELECT LastName, SUM(Total) Total

from OrderDetails

Group By LastName

Having SUM(Total) > 100

We can also throw a WHERE Clause in there if we just want it returned for customers that have a certain last name:

SELECT LastName, SUM(Total) Total

from OrderDetails

WHERE LastName = 'Smith'

Group By LastName

Having SUM(Total) > 100

The HAVING Clause can also be used for non-aggregate functions, but at that point, it is better to use the WHERE Clause.  This is because the WHERE Clause removes the data before the result set is returned, while the HAVING Clause is applied to the rows after the result set has been returned.

Read more on SQL HAVING Clause here: SQL Having Clause

Using SQL Mail Part 2

Yesterday we talked about setting up SQL Mail.  Today we are going to talk about how we can take advantage of SQL Mail.  To do so, we need to be familiar with the sp_send_dbmail stored procedure.  This stored procedure allows for sending emails to a given list of recipients and many other options:

The format for the stored procedure looks something like this:

exec msdb.dbo.sp_send_dbmail @profile_name = 'profile_name'
    ,@recipients = 'recipients'
    ,@copy_recipients = 'copy_recipient'
    ,@blind_copy_recipients = 'blind_copy_recipient'
    ,@from_address = 'from_address'
    ,@reply_to = 'reply_to'
    ,@subject = 'subject'
    ,@body = 'body'
    ,@body_format = 'body_format'
    ,@importance = 'importance'
    ,@sensitivity = 'sensitivity'
    ,@file_attachments = 'attachment'
    ,@query = 'query'
    ,@execute_query_database = 'execute_query_database'
    ,@attach_query_result_as_file = attach_query_result_as_file
    ,@query_attachment_filename = query_attachment_filename
    ,@query_result_header = query_result_header
    ,@query_result_width = query_result_width
    ,@query_result_separator = 'query_result_separator'
    ,@exclude_query_output = exclude_query_output
    ,@append_query_error = append_query_error
    ,@query_no_truncate = query_no_truncate
    ,@query_result_no_padding = @query_result_no_padding
    ,@mailitem_id = mailitem_id OUTPUT

As you can see, there are many different options available when using this stored procedure.  Here are some items of note on the variables:

• @profile_name is the Profile Name used in the previous post.  The email will not go out if the profile_name is not spelled correctly.
• @recipients is a comma separated list of recipients.
• @body_format is the format to use for the body of the email.  The options are "Text" and "HTML".  The HTML options allows you to use HTML formatting for the email (which includes line breaks, etc.)
• @file_attachment is a comma separated list of files to attach to the email.
• @query (query to execute), @execute_query_database (database to execute the query on), @attach_query_result_as_file (selected whether to put the results as an attachment or in the body of the email, and @query_attachment_filename (the name for the attached query results) can all work together to give you different options when sending your email.

Once you have decided on your different options, you can then format your email message and include the SQL stored procedure when scheduling jobs or directly into stored procedures you have written.  You can then attach any results you want into the email.

As an example, let's say that I set up a stored procedure that emails customers once they have made a purchase.  I have variables that I would use in the stored procedure to perform this.  Those variables could include: @customernumber (customers account number), @ordernumber (order number), @customeremail (customers email address).  Using these variables, I could then send out an email on order like so:

declare @query1 varchar(4000) = 'select * from CustomerOrder where custombernumber = ' + @customernumber

exec msdb.dbo.sp_send_dbmail @profile_name = 'Test Profile'
    ,@recipients = @customeremail
    ,@from_address = 'myemail@gmail.com'
    ,@reply_to = 'myemail@gmail.com'
    ,@subject = 'Customer Order'
    ,@body = 'Attached is the information for your order'
    ,@body_format = 'HTML'
    ,@query = @query1
    ,@execute_query_database = 'MyDB'
    ,@attach_query_result_as_file = 1

To read more on SQL Mail, visit the following: SQL Mail

Using SQL Mail Part 1

You may have reports for your organization that you would like to mail out each day, but you don't have the time to run them manually.  So, what are your options?  They are as follows:

• Create an SSIS (SQL Server Integration Services) package and within it create a Mail process.
• Configure SQL Mail and use sp_send_dbmail stored procedure to send your reports.

The first choice (SSIS) is the simplest option and allows for a very minimal knowledge of T-SQL using system stored procedures.  The second choice allows for more options, but is also more complicated to set up.  The article will mostly focus on using the second option.

First, you have to configure Database Mail.  To do this, go in SSMS (SQL Server Management Studio), expand Management and then right click Database Mail (as seen below).  Then, select Configure Database Mail.

A window will then open.  In this new window, select Management Database Mail account and profile and click Next.

Then, select Create a new account and click Next.

In this new window, you will want to enter your mail settings.  Get it an account name and description.  The description should be one that you can use easily to identify the account later.  Then, enter your outgoing mail settings, which include e-mail address, display name, reply e-mail and server name/port number.

It should be noted that you can have a different Reply e-mail address from the E-mail address.  The e-mail address is used to send the email, while the reply e-mail is used when the person receiving e-mail hits reply.  This is useful if you need to send an e-mail from your address, but want any replies sent to a different e-mail address.

You will also need to enter your SMTP authentication, if need be.  Refer to your IT department to get these settings.

Once all information has been entered, click Next.

SQL will then give you all the steps that will be taken when the wizard finishes.  Click Finish.

If there were any errors, they will be shown in this screen.  Otherwise, click Close to finish the wizard.

Next, you will need to set up an e-mail profile.  Go back to the Configuration wizard and this time select Set up Database Mail by performing the following tasks.  Then click Next.

In this new window, enter your profile name and description.  From here, you will select which accounts you want assigned to the profile.  Now, why would you select more than one profile?  Redundancy, in the case one profile fails, it will go to the next profile.  Click Add.

Select your account from the Account Name drop down list and click OK.  If you want to add more than one account, click on the Add button again.

Your accounts will then be added to the list.  Click Next.

If you want your profile to be available for all databases, make it a public profile.  Click Next.

You can then change system parameters for your e-mails.  For example, most mail servers can handle files between 5MB and 10MB in size.  You can set your database mail to allow file sizes greater or lower using the Maximum File Size option.  Once you have made necessary changes, click Next.

You will then see a list of changes that will be made.  Click Finish.

SQL will then make the appropriate changes.  Click Close when done.

Tomorrow we will cover how we can then take advantage of SQL Mail in our processes.