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 ...
After setting up change data capture (CDC), there are a few configuration options that allow for optimal performance based upon applications load. Using the default values, the most number of transactions that can be grabbed every 5 seconds is 5000. Remember, a transaction is not the same as a database row. One transaction can update “n” number of rows. In order to determine latency for you CDC solution, you will need to query a DMV view: [cc lang=”sql”] select latency, * from sys.dm_cdc_log_scan_sessions [/cc] The latency will be shown in seconds, if it is getting too high, start increasing maxtrans, maxscans, and decrease the polling interval. polling interval – (default 5 seconds) the amount of time to wait between log scans maxtrans – (default 500) the number of transactions to grab with each scan maxscans – (default 10) the number of scans performed between each polling interval In order to change these settings, use the system stored procedure (sys.sp_cdc_change_job) after changing the database which contains your capture instance. The CDC job needs to be restarted after making these changes. [cc lang=”sql”] — Change to the database that contains your capture instance use YourDatabaseHere go exec sys.sp_cdc_change_job @job_type = ‘capture’ ,@maxtrans = 501 ,@maxscans = 10 ,@continuous = 1 ,@pollinginterval = 5 [/cc] Changing the latency between the cleanup is done using the same stored procedure: [cc lang=”sql”] exec sys.sp_cdc_change_job @job_type = ‘cleanup’ ,@retention = 4320 — Number of minutes to retain (72 hours) ,@threshold = 5000 [/cc] To view the […]
The following short code will return the date and time for the very end of the month passed in. It works by first getting the beginning of the month for the date passed in, adding one month to that date, then subtracting 3 milliseconds. This gives the latest possible date for the end of the month as the DateTime data type has a granularity of 3 milliseconds. [cc lang=”sql”] DECLARE @DateIn datetime SET @DateIn = GETDATE() SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@DateIn)+1,0))) [/cc]
Continue reading ...
Possibly the easiest way to calculate the beginning of the month, is to use the DateAdd function and add a date difference of nothing to the month: [cc lang=”sql”] DECLARE @Date datetime SET @Date = GETDATE() SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) [/cc]
Creating an index on a table variable can be done implicitly within the declaration of the table variable by defining a primary key and creating unique constraints. The primary key will represent a clustered index, while the unique constraint a non clustered index. [cc lang=”sql”] DECLARE @Users TABLE ( UserID INT PRIMARY KEY, UserName varchar(50), UNIQUE (UserName) ) [/cc] The drawback is that the indexes (or constraints) need to be unique. One potential way to circumvent this however, is to create a composite unique constraint: [cc lang=”sql”] DECLARE @Users TABLE ( UserID INT PRIMARY KEY, UserName varchar(50), FirstName varchar(50), UNIQUE (UserName,UserID) ) [/cc] You can also create the equivalent of a clustered index. To do so, just add the clustered reserved word. [cc lang=”sql”] DECLARE @Users TABLE ( UserID INT PRIMARY KEY, UserName varchar(50), FirstName varchar(50), UNIQUE CLUSTERED (UserName,UserID) ) [/cc] Generally, temp tables perform better in situations where an index is needed. The downfall to temp tables is that they will frequently cause recompilation. This was more of an issue with SQL 2000 when compilation was performed at the procedure level instead of the statement level. SQL 2005 and above perform compilation at the statement level so if only one statement utilizes a temp table then that statement is the only one that gets recompiled. Contrary to popular belief, table variables can and do write to disk.
Continue reading ...
