Monthly Archives: April 2013

T-SQL: TOP Values

A general best practice of data selection queries is that you never want to return more data from the database server than you’re going to use. Even with a tightly focused WHERE clause, a query might still return more data than a user can make use of. You can further restrict the number of rows returned from a query using the TOP clause of a SELECT statement.

The TOP clause lets you limit the number of rows in a result set. For example, the following query selects the three cities with the highest number of employees, no matter how many cities might otherwise be returned from the query.

The Figure below shows the result set from the query. A key element is the ORDER BY clause, which sorts the result set in descending order before the TOP clause is applied. (A sort in ascending order would return the three cities with the fewest number of employees.)

Using TOP functions

However, one problem with a TOP query is the matter of ties. In the previous query, several cities have only one employee. If you want to see the ties for last place, you need to use the WITH TIES clause:

The Figure below shows the result set. The query returns all three cities that tied for last place. For the data in this table, only a TOP 2 query will return unique values.

TOP 3 WITH TIES

TOP also enables you to specify a percent value rather than an absolute number. Here’s an example of using TOP with PERCENT to return the top 25% records. The results are shown in the Figure below.

TOP 25 PERCENT

TIP: You can use any numeric expression, even a variable, to specify the number in a TOP clause. You can also use TOP in INSERT, UPDATE, and DELETE statements.

The GROUP BY clause in a SELECT statement is a powerful way to shape the data returned by a data selection query to perform basic data analysis. It might take some time to wrap your head around the set-based operation performed by non-trivial grouping, but it provides a very flexible way to wring information out of your data.


Thumbnail for 566This post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.


How Learning HTML5 Can Help You

site_title.png.pagespeed.ce.zzya4xgl2w

With the quick rise of HTML5 in the development of modern websites and applications, it’s now more important than ever to learn the ins and outs of what’s becoming the standard markup language of the World Wide Web.

With our comprehensive selection of HTML tutorial videos online, it’s easy to become an expert in the essential coding language of the modern internet. Here are some more ways in which an HTML5 tutorial can help you:

  1. Become The Most Productive Developer You Can Be – If you’re a developer, a big part of your job is staying up-to-date on all the latest trends in technology and design. A comprehensive online video tutorial will help ensure you’re at the head of your professional field.
  2. Be Prepared To Meet The Demands Of Your Clients – To always be the most productive professional in your field you need to be able to deliver on all of the varied needs of your clients. Be keeping your skills sharp and up-to-date with video tutorials for C# and HTML5, you’ll be best prepared to deliver the highest quality service to your clients.
  3. Be Ahead Of The Curve – HTML5 is becoming the defining language of the modern internet. It’s used to build websites for the traditional and modern web, as well as the latest applications. Keeping yourself current on the latest applications of HTML5 will help your professional development.

Thumbnail for 637

 

Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.

T-SQL: Counting Rows & Column Data

One of the primary purposes of a relational database is to summarize information. Other than for data entry, you will rarely look at individual orders. Instead, using the database, you will do things like summarize all the orders by week, month, or year. SQL Server performs these kinds of summaries using aggregate functions. These aggregate functions, combined with the GROUP BY clause, can combine a tremendous amount of data into a clear and concise summary. This article looks at the COUNT aggregate function for counting rows & column data.

Counting Rows

Use the COUNT function to return the number of rows in a table, as shown in the following query. This query uses the asterisk (*) as the value of the argument to COUNT so that it counts all of the rows without considering column values and whether they have NULL values:

The Figure below shows the results of the query, indicating that there are nine employees in that table in the Northwind database.

Counting the number of rows in a table

TIP: Your aggregate queries will execute faster if you use the asterisk in the COUNT function instead of a column name. The asterisk instructs SQL Server to count only the number of rows. Using a column name forces SQL Server to retrieve every value in the column and check for nulls, which aren’t included in the count. If all you’re doing is counting the rows, the asterisk is more efficient.

Counting Column Data

When you specify a column name in the COUNT function, the count excludes any null values in the column. The following query counts the total number of rows in the Employees table, and then counts the non-null values in the Region column:

Because the table contains null Region values, the result set in the Figure below shows a discrepancy between the number of employees and the number of regions.

COUNT aggregate functions do not include null values

As you can see from these results, you’ll want to be careful about whether you use an asterisk or a column name with the COUNT aggregate function (and other aggregate functions). Most of the time an asterisk will work, unless you need to account for NULL values.


ldn-expertdkielyThis post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Need Reasons Why C# is the Best Language for Mobile Apps Development?

c video course

When it comes to honing your programming and development skills, it is worthwhile to consider the old adage “follow the money.” After all, no one wants to become an expert in a programming language that will be extinct in five years. At the same time, programmers should pay attention to heavily-hyped languages, as they could be their meal ticket towards a prosperous and exciting career.

Of course, we don’t use the word “hyped” lightly. Many languages have been billed as the “next big thing” only to quickly fizzle. That’s why we really like this article, which provides eight concrete and convincing reasons why C# is the best language for mobile development. No hype, just facts.

Ultimately, the language has many benefits that make it conducive towards long-term development for business purposes. For example, it’s very reliable and allows programmers to detect and isolate bugs quickly. It boasts advanced runtime and powerful features like object-oriented programming and encapsulation.

And perhaps most importantly, it’s easy to adopt, especially for developers well-versed in object-oriented programming. So what are you waiting for? Sign up for our C# tutorial video today.

Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.

The Sky’s the Limit with the T-SQL WHERE Clause

You will rarely want SQL Server to return every row in a table. The most efficient queries retrieve only the data you will actually use—no more, no less. To limit the rows that the query returns, you have to specify the subset of records that you want. The WHERE clause is the primary row filter of a SELECT statement.

You use the WHERE clause to specify the search conditions that SQL Server should use to identify rows that should or shouldn’t be included in the result set. The simplest WHERE clauses check for equality, so that each row returned has to meet the specified condition.

Using Transact-SQL comparison operators in a WHERE clause lets you build queries that search for records other than those that are exactly equal to a static value. This article takes a look at using the IN comparison operator.

Using the IN Operator

Sometimes you have to filter rows of a table based on whether a column’s value matches multiple values. You can use the OR operator to filter on a list of values, as shown in the following query, which returns customers in France or Spain:

However, a much more efficient way (one that SQL Server can often execute faster) is to use the IN operator. The IN operator compares a field against an array of values. The following query returns the same list of all customers in France and Spain:

The following Figure shows the result set:

Using the IN operator

TIP: You can also use the IN operator with a subquery, which is a query within a query. For example, to find all customers who have not placed orders, you could use this:

SQL Server first executes the subquery (highlighted in this code), then uses that list of CustomerID values as the set of values used by the IN operator to compare CustomerID values in the Customers table against. So you are not limited to using the IN operator with a fixed set of static string values.

The WHERE clause of a SELECT statement in SQL Server is an incredibly powerful way to filter the rows returned by a data selection query. There are a number of ways to put this clause to use, and as you learn more about T-SQL you’ll see that you can get very creative with how you control the rows in a query result set. By creating a filtering condition based on other data in the database, you can dynamically return results based on the current state of your data, rather than a fixed set of static conditions. The sky’s the limit!


Thumbnail for 566This post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

T-SQL: Using the SELECT Statement for Naming Columns

Giving a name to the computed column

One of the most versatile statements in all of T-SQL is the SELECT statement. It’s guaranteed to become your go-to tool for retrieving data stored in a SQL Server database, with a dizzying array of clauses and options that let you select data from any databases and tables, shape it however you want, and apply conditions and filters so that you get exactly the data you need. This article discusses how to use the SELECT statement for naming columns.

Naming Columns

Notice in the Figure below that the column has no name. Since the column is the result of an expression, rather than a column in the database, you must explicitly name the column if you want to be able to reference the calculated column in a client application. Here is how you can do that by using the AS keyword:

The query itself is essentially the same as the previous example; all that’s changed is that the column now has a name, FullName. This name is also called an alias. Aliases are used routinely in complex SQL and are necessary for certain types of joins, such as the self joins that you’ll learn about later in the chapter. To include a space within an alias, surround the alias with square brackets; the results appear in Figure 4.

 

The AS clause is optional—you can name a column just by using the name separated from the column list by a space:

 

This query functions in the same way as the query with the AS clause, but makes your SQL statement harder to read. Is FullName a column in the database and the programmer forgot the comma, or an alias? It is better to be explicit with the AS clause. The AS clause is the most explicit way to alias a column and is supported by the ANSI standard.

Another supported option is to use an equal sign for defining a column alias, which produces the same results as the previous statements. This syntax is required in some cases when using more advanced features of the SELECT statement.

Deprecated Syntax – One method of defining column aliases using the equal sign (=) is now deprecated, which means it will not be supported in future versions of SQL Server. (Even though it has been deprecated for a long time, it still works in SQL Server 2012.) This method uses a string expression to define the alias:

You can, however, still use a string expression to define the column alias if you use the AS syntax (even if you leave out the word AS).


Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

T-SQL: The WAITFOR Statement

Syntax that controls the flow of statement execution is an essential feature of any procedural programming language. The core SQL standard implements set-based operations, and only introduced procedural features like flow control starting in the 1999 version. Transact-SQL extends the standard to support the usual flow control mechanisms for conditional branching and looping of code execution. This article discusses the WAITFOR statement.

The WAITFOR statement

The WAITFOR statement sets the SQL Server query processor to a holding pattern until either a specific amount of time elapses or an absolute time occurs:

This is especially useful during testing, if you need to simulate a long-running query. The following examples show how to use WAITFOR. The first statement pauses for 10 seconds, and the second until noon. Keep in mind that your database connection will be considered blocked until the WAITFOR completes, so execute the second example with care!

You can keep an eye on the duration of the pause in the first query in the lower right corner of the query editor window, as shown in the Figure below. You might not see every one of the 10 seconds of the count because of Windows’ preemptive multitasking, but it will give you an idea of how long the query is taking to execute. At 10 seconds, it should complete.

The query duration counter in the query editor window

If you try to run the second query and it doesn’t happen to be shortly before noon, it will be a good time to know about the square red toolbar button in Management Studio, which allows you to cancel an executing query as shown in the Figure below.

Click the square red button to cancel an executing query

WAITFOR isn’t a statement that you’ll want to use very often—it runs counter to our usual obsession with making code run faster, not slowing it down! But sometimes you have to wait for other external things to happen, and in those cases a strategically placed WAITFOR is just what you need.


ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL
course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

T-SQL: DATEADD and DATEDIFF Functions

Date and time functions perform operations on date and time values to return a string, numeric, or date/time value. Compared to the string manipulation functions in T-SQL, you have way more flexibility when working with dates and times.

Use DATEADD to perform computations on dates by adding an interval of time to the specified date. The following query adds 2 to the year, month, and day. The Figure below shows the results.

Performing computations on dates with DATEADDTo subtract from a date, use DATEADD and pass in a negative number for the time interval.

Use DATEDIFF if you want to calculate the length of time between two date/time values. As with DATEADD, DATEDIFF allows you to specify the unit of measure. The following query uses the Northwind database to calculate the number of days that elapsed between various dates in the Northwind Orders table, to provide various metrics about company performance. The first date passed to DATEDIFF is subtracted from the second date. The Figure below shows a few rows of the result set.

DATEDIFFThe following code uses a query that specifies a date and then calculates the last day of the month that the date is in. The logic is to add one month to the date and then subtract the number of days in the day portion of that date in the next month. This brings the calculation back to the last day of the original date’s month. This calculates that 2016-02-29 is the last day of that month.

In SQL Server 2012, an even easier way to find the last day of the month is with the EOMONTH function, which takes a date and an optional parameter that lets you specify a number of months to add. The following code finds the last day of the month for the same date as the previous code, as well as the last day of the month five months later. Notice that in February the last day is the 29th but in July it is the 31st, as shown in the Figure below.

EOMONTH


ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL
course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

T-SQL: PATINDEX Function

In a previous article we discussed the REPLACE and STUFF functions. This article takes a look at PATINDEX, which supports wildcard characters and other regular-expression-like features for a pattern search. PATINDEX returns the starting position of the first occurrence of a pattern in an expression, using the following syntax. Like CHARINDEX, it returns zero if it doesn’t find the pattern.

PATINDEX supports the same syntax that you can use with the LIKE operator. This includes the % wildcard for one or more characters and the _ wildcard for any single character.

The following queries return different results, as shown in the Figure below, because they use different wildcards.

 

PATINDEXIn addition to wildcards, PATINDEX supports the use of square brackets to indicate ranges of characters to match. Use a caret (^) inside the brackets to indicate that the match is based on NOT finding the specified range of characters.

For example, you could use the following query instead of ISNUMERIC if you don’t want to return rows with numeric values like “123d4” or “123e4.” This query filters out all rows where PostalCode contains any characters other than the digits 0–9: Literally, it finds strings that don’t contain any characters that aren’t numbers. Removing the double negative from that last sentence, it returns only rows where the PostalCode contains only numbers.

PostalCodes with only numeric values

Another advantage of PATINDEX over CHARINDEX is that you can use it with text, ntext, and image columns. The Figure below shows the results from the following example, which finds all Descriptions that contain ‘sweet’ and the position in each string.

Products that are sweet


Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL
course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

The Decline of the IT Generalist

vsBack in the good old days – say, 20 years ago – many small companies had what was considered an “IT generalist.” They pretty much did everything. They set up and managed the e-mail, fixed computers, kept an eye on the network, and managed small projects.

They were, of course, “the IT guy.” And even in companies with larger IT staffs, many of the IT workers had a hand in all different types of technology. They even dabbled in programming; it wasn’t abnormal for IT workers to be mildly proficient in Windows, Cisco, Perl, etc.

Well, now we’re approaching the age of “specialists.” Many companies have a separate “expert” in network management, help desk, project management, etc. And this trend is particularly acute in the programming world. The list of languages, of course, is a long one: Java, VB, C#, Ruby, Python, etc.

More and more companies are looking for language-specific specialists, and that’s good news for job seekers. Check out our Java, C#, or Visual C# tutorials and become a “specialist.”

Thumbnail for 628

Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.