The SQL Rank function was introduced in SQL Server 2005 and is part of a family of ranking functions. In order to explain the SQL Rank function, we need to look at it in context with the other rank functions. RANK DENSE_RANK ROW_NUMBER NTILE This list may seem overwhelming, however most of the ranking functions are rather similar. First, the syntax. Each ranking function takes a mandatory parameter of a column or value used in order to “rank”. Let’s look at an example run against the adventureworks light database. [cc lang=”sql”] SELECT UnitPrice = UnitPrice ,RowNumber = ROW_NUMBER() OVER (ORDER BY UnitPrice) ,Rank = RANK() OVER (ORDER BY UnitPrice) ,DenseRank = DENSE_RANK() OVER (ORDER BY UnitPrice) ,NTile = NTile(100) over (ORDER BY UnitPrice) FROM SalesLT.SalesOrderDetail [/cc] Looking at the results of the query provides the best explanation for the functions. From this list we can compare the UnitPrice in order to determine what each function is doing. The rownumber function, simply add one number per each row that is returned. The Rank function will compare the unit price and provide an integer based on it’s comparison with the previous row. If they are equal it will return the same rank. Internally, the rank will be incremented by one for each row (whether it is equal or not) and the next non-equal value will display the next internal rank. (I know this is a tough sentence, just look at the example). Dense_Rank performs the same as rank, except it does not […]
Continue reading ...
The most efficient method of copying a result set into a new table is to use the SELECT INTO method. This method also follows a very simple syntax. [cc lang=”sql”] SELECT * INTO dbo.NewTableName FROM dbo.ExistingTable [/cc] Once the query above is executed, all the columns and data in the table ExistingTable (along with their datatypes) will be copied into a brand new table named: NewTableName. The reason this is so fast is because it makes use an internal SQL Server feature called minimal logging. Minimal logging means that each row that is written to the new table is only written to the data page and is not written to the log file. Instead only a pointer to the data is inserted to the log file. This cuts the IO down by half. The query shown above is simple, but it can also be as complex as you’d like it to be. Any query that is currently returning a result set, can easily be made into a SELECT INTO query by inserting the INTO keyword after the select list. The Downside of SELECT INTO This title may be a little intense, however there are a couple issues that SELECT INTO does not cover. First, it does not copy over any of the constraints, keys, or computed columns. It also does not copy over the indexes. While this may not necessarily be a downside, it should be noted. Many times I have copied a table using SELECT INTO only to find […]
Continue reading ...
The try catch methodology of programming is a great innovation for SQL 2005+. The first question you should ask yourself before using Try/Catch should be “why?”. Why am I going to use Try/Catch? Personally, I have found a few uses, however I must say I do fall into the category of not being a developer that uses this for every procedure. Generally speaking, I do want my errors to bubble up to the next highest layer (like the web layer, and display exactly as they occur). And while you can use try/catch and still allow errors to bubble up, in a lot of cases I don’t see the need. In fact, in implementing Try/Catch in an improper way, it is possible to accidently hide errors to the extent of no one ever knowing about them. That said, probably the best use of Try/Catch is to implement your own error logging within your database. Before we get into an extensive logging example, let’s see an example of a Try Catch block. The following example would be a good use of Try/Catch because it is plausible that a negative number may enter a variable used in the Top Clause: [cc lang=”sql”] DECLARE @TopRows int SET @TopRows = -1 BEGIN TRY SELECT top (@TopRows) * FROM SalesLT.Customer END TRY BEGIN CATCH SELECT ERROR_NUMBER(), ERROR_MESSAGE() END CATCH [/cc] When we execute this script, we see that no exceptions are returned. Instead, a second result set is shown. Notice the first result set is also […]
Continue reading ...
A common scenario when querying tables is the need to find duplicate fields within the same table. To do this is simple, it requires utilizing the GROUP BY clause and counting the number of recurrences. For example, lets take a customers table. Within the customers table, we want to find all the records where the customers FirstNames are the same. We also want to find which FirstNames are the same and count them. First off, let’s get a count of how many customers share the same first name: [cc lang=”sql”] SELECT FirstName ,DuplicateCount = COUNT(1) FROM SalesLT.Customer GROUP BY FirstName HAVING COUNT(1) > 1 — more than one value ORDER BY COUNT(1) DESC — sort by most duplicates [/cc] So here we see the results of the query sorted by the names which are duplicated the most. Using the having clause restricts the result set to only those customers that have duplicates. The order by clause orders the results by those who are duplicated the most. This method can also be expanded to include multiple columns, like FirstName and LastName. In order to expand the criteria, we simply add the columns to the select list and the group by clause. [cc lang=”sql”] SELECT FirstName ,LastName ,DuplicateCount = COUNT(1) FROM SalesLT.Customer GROUP BY FirstName ,LastName HAVING COUNT(1) > 1 — more than one value ORDER BY COUNT(1) DESC — sort by most duplicates [/cc] Ok, so now that we have found the duplicate items, how do we join that back on […]
Continue reading ...
The OPENQUERY command is used to initiate an ad-hoc distributed query using a linked-server. It is initiated by specifying OPENQUERY as the table name in the from clause. Essentially, it opens a linked server, then executes a query as if executing from that server. While executing queries directly and receiving data directly in this way is not bad, there are implications when trying to join the results of OPENQUERY with a local table. Typically joining remote tables with local tables across the network is not the most efficient method of querying. In some cases it may be better to subscribe to a remote table by importing it locally, then joining it locally. Setup Ad Hoc Distributed Queries In order to utilize, a server-level configuration needs to be applied to the default configuration in order to allow ‘Ad Hoc Distributed Queries’. In order to see the current setting, run the following: [cc lang=”sql”] EXEC sp_configure [/cc] If you see the config_value set to 1 (as the figure below), then the option is already set. Otherwise you need to change the configuration. To set the configuration, simply run the following code below: [cc lang=”sql”] — Enable Ad Hoc Distributed Queries EXEC sp_configure ‘Ad Hoc Distributed Queries’,1 RECONFIGURE WITH OVERRIDE GO [/cc] OpenQuery Example The next step is to find or define the link server required and specify the linked server name as the first parameter in OPENQUERY. This tutorial already assumes you have a linked server setup, so from here we execute […]
Continue reading ...
