Paging through data is something that developers need to do frequently. Until SQL Server 2012, the best way to achieve this is with the ROW_NUMBER function… and let’s face it… that wasn’t the easiest/most elegant thing to use. Our prayers have been answered in SQL Server 2012. The SQL Server team has come out with a better way of doing pagination using the OFFSET FETCH clause.
The OFFSET FETCH Clause
The OFFSET FETCH clause allows the client application to pull only a specified range of records. To implement pagination using the OFFSET FETCH clause, it takes two parts… the OFFSET and the FETCH. 🙂
NOTE: To use the OFFSET FETCH clause for pagination, you must use an ORDER BY clause as well.
This part tells SQL Server to skip N number of rows in the result set.
The above statement tells SQL Server to return all of the AnimalNames in the Animal table, but only return the names after the 50th. So 51 and beyond.
This part tells SQL Server how many records to return in the result set. If you use FETCH, you always need to use OFFSET.
The above statement tells SQL Server to return the AnimalNames in the Animal table with row numbers 51-60. The OFFSET clause tells it to skip 50 rows and the FETCH clause tells it to return 10 records.
Variable Row Counts
You are also able to use a variable for the record counts in the query. If you wanted to do this, it would look like this:
Occasionally we need to do a string comparison in SQL Server as a case sensitive compare. By default, case sensitive compare is not turned on for tables in SQL Server. It’s actually pretty easy to change a string comparison to a case sensitive compare. You can do this by using the COLLATE clause.
Read below to find out why this works and other tips on doing a case sensitive compare.
The COLLATE clause is something that you can add to the end of an expression to set the collation of the operation to a specific collation. So in the example… the string equal comparison is being changed to a collation of SQL_Latin1_General_CP1_CS_AS.
A collation in SQL Server is something defines how data is compared. This could be things like case sensitivity, acceptable characters, etc… Most of the time (for English installations of SQL Server) it will be a collation type of SQL_Latin1_General_CP1_CI_AS. This is because the Latin character set covers English and other Latin based languages.
The two most common collations that I deal with are SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP1_CS_AS. You can see that they are very similar. In fact… the only difference is the _CI_ and _CS_. These actually stand for Case-Insensitive and Case-Sensitive.
Field Level Case Sensitivity
Even better… you don’t need to set the collation on every sql statement that you want to make case sensitive. There is actually a setting that you can apply to a field in a table.
NOTE: that if you are joining on this table, both collations must be the same or you will need to specify the collation to use.
Have you ever wanted to insert more than one row of static text in to a SQL table using the VALUES argument? Well, this is actually pretty easy to do. To do this you just need to pass a comma delimited value list like in the example below.
Although SQL Server does not have a built in way to return your query results in random order, you can accomplish this in an easy way. One thing to note is that this way of returning the results is very expensive… so it could take a while.
In the following example, the magic happens in the order by clause. The NEWID() function will generate a unique GUID for every record. This will cause the results to come back randomized.