SQL Server Programming Logic

I'm working through some logic elements in SQL Server this morning. It's been a good review of how to use some basic SQL logic to manipulate data. It's also helped me to become more familiar with some of the built-in functions of SQL Server.

Working with dates is always a bit different in every language, so I worked through some scripts that used them in T-SQL. Here's an example of some of the IF ELSE statements I executed:

We have a variable that is holding the count of all customers in the Sales table. Then there is some conditional logic that outputs different responses based on the total count and the result of a call to the built-in DATEPART function.

Next up I worked through a slightly more complex problem with some nested logic:

The outer block checks to see if the month is August or September. The inner block checks to see if the year is even or odd using a modulo.

Next up this afternoon, I'll run through some loops and start reviewing the syntax for stored procedures in SQL Server.

User Defined Functions (UDF)

Today I'll be working with User Defined Functions (UDF) in T-SQL. Microsoft defines User Defined Functions as "routines that perform calculations/computations and return a value - scalar (singular) or a table". That sounds simple enough. UDF's can be developed using T-SQL or .NET languages in the CLR. CLR UDFs are good for tasks that T-SQL cannot perform efficiently like procedural logic, complex calculations, string manipulation, etc... Though .NET should be avoided when the task is mainly set-based.

I'm specifically looking at nondeterministic functions. They are functions that are not guaranteed to return the same output when invoked multiple times with the same input. Built in nondeterministic functions like RAND and GETDATE are invoked once for the whole query and not once per row. The NEWID function is different. It generates a globally unique identifier (GUID).

I wrote a script to test this behavior:

The Random and Date columns were both only invoked once for the whole query - the data repeats for each row. The guid column was populated by the NEWID function and was invoked on each row. A new value was generated for each record.

T-SQL Elegance

I've heard of Itzik Ben-Gan and have wanted to attend one of his conferences for some time. One of my former coworkers was able to see him not long ago and came back professing his genius. What's the next best thing to witnessing him solve programming problems in person? I hope it's reading his books, because I've been going through them this week. Here's what is on my desk:

1. Inside Microsoft SQL Server 2008: T-SQL Querying
2. Inside Microsoft SQL Server 2008: T-SQL Programming

The books are a companion set and Ben-Gan recommends reading them in order. I began to plow into the companion Programming book until I found the first book was constantly referenced. The Querying book will be a good review with some suggestions for how to elegantly use the features of SQL Server 2008 at an enterprise level.

Performance in T-SQL

I'm taking a look at performance issues in T-SQL this morning. How do indexes and functions affect performance? I'm working with the "AdventureWorks" test database from Microsoft. It can be downloaded here: link

Since there isn't currently an index on the Sales.SalesOrderHeader table, I'm going to create one with the following script:

Now that a nonclustered index exists for the SalesOrderHeader, I'm going to run some test scripts. You can see the queries and the results below:

These queries return all the orders placed in 2001. Query 1 accomplishes this without the use of a function, while query 2 uses the YEAR function. So what's the lesson here? Take a look at the execution plans. Query 1 has a cost of only 7 percent. The database engine can use the index I set up with this query because it is comparing actual indexed values. Query 2 uses a function that requires the engine to scan the entire index in order to see whether the results of the YEAR function applied to each value meets the criteria.

I dropped the index and ran the two queries again. Now each query takes the same amount of time. How you construct queries and how you implement indexes makes a big difference in performance. It's not enough to just retrieve the desired results.

SQL Server Management Studio

I'm working in SQL Server Management Studio (SMSS) this afternoon. All of my previous SQL Server work has been done from within Visual Studio. There are some nice features in SSMS:

• Query window uses IntelliSense to assist in writing code.
• Individual queries can be highlighted within the window and executed.
• Easy access to lots of administrative tasks.
• Integrated Object Explorer window that is similar to VS. It allows for some useful filtering. With a few clicks of the mouse, you can browse to specific objects to create simple scripts in the query editor. These can then be easily edited.
• Enable "Execution Plan" in order to create additional tab in query results window that will give the developer detailed information regarding query performance... cool.
  
• Immediate feedback on executed queries. Just click the "Messages" tab on the query results window and you will see the number of rows affected and any pertinent errors.

The Execution Plan results

The Messages tab in the query results window

Something Old, Something New

I've worked with Oracle exclusively in my past professional experience. School exposed me to a small amount of T-SQL and MySQL. Measurement Inc uses SQL Server, so it's time to beef up my knowledge of T-SQL. I'm looking forward to making the switch. Being a .NET developer with limited exposure to SQL Server is like being a movie director with limited exposure to editing equipment - it just doesn't make sense.

There are tons of great resources online and many thick books published on the subject. I've surfed to a few sites and bought a stack of books. Reading through the material gets me excited to do more with SQL Server than the simple implementations I've used while developing the back end of web applications in Visual Studio.

More to come soon.

Windows Communication Foundation (part three)

This is the final part of a project designed to teach myself more about Windows Communication Foundation (WCF). The previous parts are here: part 1 and part 2. When we last left off, we had just finished getting the service ready to host. Now we need to create the client that will access the service.

Step Four: Create a WCF Client

I created a new console application project within my solution and named it "Client". I then added the appropriate reference to System.ServiceModel.dll. Next, we start the service created in step three.

Once our service is running, we can run the ServiceModel Metadata Utility Tool (Svcutil.exe) from the Visual Studio command prompt. Navigate to the folder where you would like the code generated. With the correct parameters, this tool will auto-generate our client code. Here was my input:

I then added the proxy file that is generated to the client project of my solution. Next, we will need to configure the client we just created.

Step Five: Configure the WCF Client

An app.config file was also generated when we ran the Svcutil.exe program from the command line. We will add this to our project. Configuring the client consists of specifying the endpoint that the client uses to access the service. An endpoint has an address, a binding, and a contract. Each of these must be specified. Here is what the Svcutil.exe tool created for our app.config file:

Svcutil.exe generates values for every setting on the binding. The endpoint is configured here (look to the endpoint element tags). Here's a link to some more information from Microsoft about how to use the generated client: link.

Step Six: Using the WCF Client

This is the final step in my little test project. Our WCF proxy has been created and configured. A client instance can be created and the client app can now communicate with the WCF service. Here's what the procedure does:

• Creates a WCF client.
• Calls the service operations from the generated proxy
• Closes the client once the operation call is completed

The following code is all placed in the mainline of the Program class in Client.

1. Create an EndpointAddress instance for the base address of the service being called and then create a WCF client object.
2. Call the client operations from within the Client.
3. Close the WCF client

Here is the code:

Now make sure the service is up and running before you use the client. Run the client by starting a new instance in the Solution Explorer. If all is correct, the service should send the appropriate calculations to the client, and they will be displayed. Below you can see both my Service and Client running, with a sweet picture of the space shuttle Atlantis from its final mission:

And one more time, here's a link to the completed application: