SQL Server 2012 actually employs a different method for pagination that is built in using the ORDER BY Clause. However for other versions, the most efficient pagination method is actually simple. Aren’t most things like this? It involves using (the mighty powerful) ROWCOUNT statement — twice. Anyways, the code: [cc lang=”sql”] CREATE PROCEDURE [dbo].[spUsersPaged] ( @RowBegin int, @MaxRows int ) AS BEGIN DECLARE @StartID int SET ROWCOUNT @RowBegin SELECT @StartID = UserID FROM dbo.Users u ORDER BY UserID — Set maximum number of rows to return SET ROWCOUNT @MaxRows SELECT * FROM dbo.Users u WHERE UserID >= @StartID ORDER BY u.UserID SET ROWCOUNT 0 END [/cc] That’s it for the first pagination sample. You may try other methods, however I challenge all Ye to overcome thy double ROWCOUNT method. Comment below please. To give a variety, I’m sharing another pagination method. This one seems more elegant. It uses a Common Table Expression) CTE, and the RowNumber function. [cc lang=”sql”] SET @rowsPerPage = 10 SET @pageNum = 3 WITH SQLPaging AS ( SELECT TOP(@rowsPerPage * @pageNum) ResultNum = ROW_NUMBER() OVER (ORDER BY id) ,id FROM dbo.Table1 ) SELECT * FROM SQLPaging WHERE ResultNum > ((@pageNum – 1) * @rowsPerPage) [/cc] Yet is it elegant, with speed?? ..uh not so much.
Continue reading ...
This article shows the usage of sp_who2. To diagnose system slowdowns, see (Troubleshooting SQL Slowness). One of the first lines of defense in determining the causes of database slowdowns is to use sp_who2. sp_who2 shows all the sessions that are currently established in the database. These are denoted as SPID‘s, or Server process Id’s. Running sp_who2 is easy, all that is required is to type sp_who2 and execute it, however it’s a good idea to call it with EXEC. [cc lang=”sql”] EXEC sp_who2 [/cc] The first 50 results are system SPIDs. Generally these do not effect slowdowns of the system. These system events include the Checkpoint writer, Log writers and task schedulers. User processes are SPID numbers 50 and over. In diagnosing slowdowns it is these SPIDs that are the potential resource hogs. sp_who2 also takes a optional parameter of a SPID. If a spid is passed, then the results of sp_who2 only show the row or rows of the executing SPID. [cc lang=”sql”] EXEC sp_who2 243 [/cc] There are four main things to look for when when diagnosing slowdowns. Blocking High CPU usage High IO usage Multiple entries for the same SPID representing parallelism. When a number is shown in the column named BlkBy, this represents the SPID that is currently stopping the SPID in the row shown. Sometimes many rows will show SPID numbers in the BlkBy column. This is because there is a chain of blockers. The way this occurs usually starts with one “lead” blocker blocking […]
Continue reading ...
SQL Server Express 2008 does not allow remote connections by default. This is done by design as SQL Express is often used either as a development tool, or as a local installation for resident applications. Another thing to note, is that SQL Express uses dynamic ports by default so in order to connect, The SQL Server Browser service needs to be started on the same machine that Express is installed on. Below are the steps to perform: Enable TCP Connections for SQL Server Express Start -> Programs -> Microsoft SQL Server 200x -> Configuration Tool -> SQL Server Configuration Manager Expand SQL Server Network Configuration Click Protocols for SQLExpress In the right hand pane, right click TCP/IP and choose ‘Enable’ Make sure SQL Server Browser Service is started. Without this service enabled, you will not be able to connect (unless you specified a static TCP port). As long as you do not have any firewall issues, you should be able to connect
Continue reading ...
Working in Administration or Development, we find ourselves performing the same tasks over and over. Often we do not recognize the time each of these menial tasks take and how much time out of our day they waste. Luckily, there are external tools we can rely on to make our lives easier. SSMS Tools Pack This is my favorite. Or should I say our development teams’ favorite. Best of all, it is free. After using some of the tools this plugin has to offer, there is no going back. Window Color Coding – Have you ever executed code on a server you shouldn’t have? (Of course you have). This plugin will put an end to that. Out of the box, it will add a distinct colored border at the top of your Query Windows that will be different for each server you are on. I use to mistaken servers (Dev, Prod, QA) not anymore. This alone is worth the price of the plugin.. (Oh wait, it’s free). Format SQL – Yes, this is self explanatory. Are you a lowercase coder, or uppercase coder? (UPPER CASE RULES!). Anyway, however you want your keywords to appear, this will help with the stroke of two quick keys. SQL Snippets – Store snippets of SQL and insert them into the query window with one quick keypress. To download SSMS Tools Pack, click here
Continue reading ...
Comparing the results of two different stored procedures output can be difficult task. The first difficulty is to return the result sets of the stored procedures into temporary tables. There are two different methods of doing this outlined in the link above. For this example we will use the local linked server method. Your method may differ depending upon whether your system setup will support one of the methods. It may also differ if you are looking at passing dynamic parameters (this method is also covered in the link above). After the result sets are in the temp tables, we need to compare the datasets. We typically need to see the data in one of three ways: What is in one result set and not in the other All results that do not match All results that do match First, let’s dump the procedures into tables: [cc lang=”sql”] — Where local server is the name we gave to our local linked server SELECT * INTO #Procedure1 FROM OPENQUERY(LOCALSERVER, ‘set fmtonly off exec dbo.Procedure1 param’) SELECT * INTO #Procedure2 FROM OPENQUERY(LOCALSERVER, ‘set fmtonly off exec dbo.Procedure2 param’) [/cc] Now, considering we have SQL 2005 and above, we will compare what is in Procedure1 that does not match what is in procedure2. We do this using the new EXCEPT Statement. [cc lang=”sql”] — Give us what IS in procedure1 SELECT * FROM #Procedure1 EXCEPT — That does not match procedure2 SELECT * FROM #Procedure2 [/cc] Next, we will get what is in […]
Continue reading ...
