My SQL Server Blog

Passing a temp table to a stored procedure

Adam Rink — Mon, 06 Jul 2009 04:31:00 GMT

With SQL 2008, table variables can now be passed into stored procedures. Temp tables can also be passed into stored procedures from other stored procedures and triggers. This is true in 2008 and earlier versions like SQL 2005 and 2000.   This allows for cleaner code as many lined stored procedures can now be broken up into multiple sub-procedures.

A child stored procedure can see its parent's temp table. This child procedure can INSERT, DELETE and UPDATE rows just like a normal temp table. When the child returns to the parent, the data is changed and can be passed into more child stored procedures.

Using a temp table and passing it to another procedure, in most cases, will be faster than looping through a result set with a cursor and calling a stored procedure with parameters.

An example:

CREATE PROC dbo.udfParent
AS
BEGIN

   SELECT 1 AS MyInt,
   'Hello' AS MyChar
   INTO #myTemp

   EXEC dbo.udfChild

   SELECT *
   FROM #myTemp

   DROP TABLE #myTemp

   RETURN (0)
END

GO

CREATE PROC dbo.udfChild
AS
BEGIN

   SELECT *
   FROM #myTemp

   UPDATE #myTemp
   SET MyInt = 2

   RETURN (0)
END

GO

EXEC dbo.udfParent

Extending TSQL ROW_NUMBER to Find MAX Rows or Row Deltas

Adam Rink — Wed, 11 Feb 2009 04:39:00 GMT

Some common queries in a database involve grabbing the last value entered into a table for a set of items or comparing a row to its previous row. In the past this involved nasty sub queries one to find the max date and then use that value in outer query. This did not provide the cleanest of code or the best performance. Using the ROW_NUMBER function allows us to simplfy the code and improve performance.

Let's make a CTE first off our query to simplify our code:

WITH cteTranLog (Item, LogValue, MaxDateNum, Date)
AS 
(    SELECT   Item,	   
              LogValue,
              MaxDateNum = ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Date DESC),
              Date    
     FROM dbo.TransactionLog    
     WHERE ProcessID = 514	
)

To grab the last values entered in a table:

SELECT Item, LogValue, Date
FROM cteTranLog
WHERE MaxDateNum = 1  -- we grab the top row only since we are sorted by max date on top

Notice there is only one table seek or scan. A subquery to grab the max date for each item would involve 2 seeks or scans of the table.

The other query involves using row number to look at the current row. This can be handy with running totals or finding deltas between rows.

SELECT cur.Item, cur.LogValue, cur.Date, prev.Date, prev.LogValueFROM cteTranLog cur	
INNER JOIN cteTranLog prev ON (prev.MaxDateNum - 1) = cur.MaxDateNum	-- using a -1 because of sort order of desc, dates are descending so 1 is the current making a lesser date 2

Searching All Fields in a Database for a Text String

Adam Rink — Thu, 22 Jan 2009 06:00:00 GMT

The other day a person in my QA department came to me with a request for a script that searches all fields in a database for a text string. They were unsure where a configuration value was being stored on the system. While you could use SQL Profiler to find this information, I thought writing the script would be fun.

Below is the script:

DECLARE @tblTables TABLE (Col varchar(128), Tab varchar(128), Sch varchar(128))
DECLARE @Tab varchar(128), @Col varchar(128), @Sch varchar(128), @SearchValue varchar(MAX), @Query varchar(MAX)

SET @SearchValue = 'Smith' --set this to the string you wish to find

INSERT INTO @tblTables (Col, Tab, Sch)
SELECT c.Name AS Col, t.Name AS Tab, s.[Name] AS Sch
FROM sys.columns c
    JOIN sys.tables t ON t.[object_id] = c.[object_id]
    JOIN sys.types ty ON ty.[user_type_id] = c.[user_type_id]
    JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
WHERE t.[type] = 'U'
    AND ty.[name] IN ('varchar','char','nvarchar','nchar')

-- use temp table for persistence with executing dynamic sql
CREATE TABLE #tblValues (Col varchar(128), Tab varchar(128), Sch varchar(128), ColValue varchar(MAX))

WHILE EXISTS (SELECT 1 FROM @tblTables)
BEGIN
    SELECT TOP(1) @Col = Col, @Tab = Tab, @Sch = Sch
    FROM @tblTables

    SET @Query =    'INSERT INTO #tblValues (Tab, Col, Sch, ColValue)' +
                    ' SELECT ''' + @Tab + ''' AS Tab, ''' + @Col + ''' AS Col, ''' + @Sch + ''' AS Sch, [' + @Col + '] AS ColValue' +
                    ' FROM [' + @Sch + '].[' + @Tab + ']' +
                    ' WHERE [' + @Col + '] LIKE ''%' + @SearchValue + '%'''


    EXEC (@Query)

    DELETE TOP(1) FROM @tblTables
END

SELECT * FROM #tblValues

DROP TABLE #tblValues

RAISERROR Percent % Sign usage

Adam Rink — Tue, 29 Jan 2008 05:40:00 GMT

Have you ever wondered how to build a string with values for an error message? One would think that RASIERROR could simply take a string that was concatenated together with variables.

Something like RAISERROR ('My Error' + @myErrorString, 16,1)

Unfortunately, RAISERROR is not as intuitive as this.

The syntax for RAISERROR is:

RAISERROR ({msg_id | msg_str}{, severity, state}
    [, argument
        [,...n]] )
    [WITH option[,...n]]

Maybe you have seen the arguments passed into the error message string with percent sign.

RAISERROR ('My Error %s', 16,1, @myErrorString)

The % sign indicates the use of a parameter.

d or i	Signed integer
o	Unsigned octal
p	Pointer
s	String
u	Unsigned integer
x or X	Unsigned hexadecimal

To pad some space a number must be placed infront of the type.

RAISERROR ('My Error %10s', 16,1, @myErrorString,@myErrorString2)

To justify simply use the + or - sign after the % sign. To pad with a space, use `` (next to the 1 key)

If you pass more than one argument then you must use the percent signs in order.

RAISERROR ('My Error %10s is not cool %10s', 16,1, @myErrorString,@myErrorString2)

Changing Export Options for Reporting Services

Adam Rink — Fri, 04 Jan 2008 05:36:00 GMT

Have you ever wanted to change the export options for Reporting Services? Have you seen issues with report page headers being exported to Excel as non headers? Other times displaying all export options on the web is not the best idea. An end user trying to figure out what a TIF is can be a more user support time then is desired. In other cases the export might need to be renamed, instead of TIF call it Image.

To make these changes, you will have to open up the RSReportServer.config file. It is located in the report directory of SQL server (normally something like C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer). This is a an XML file that can be opened in Notepad. In the XML file, about half way through it you will see a tag called <RENDER>. To remove options from the drop down export, delete each extension tag that you do not want.

<Render>
...

            <Extension Name="EXCEL"   Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
                <OverrideNames>
                    <Name Language="en-US">EXCEL AMR</Name>
                </OverrideNames>
                <Configuration>
                    <DeviceInfo>
                        <RemoveSpace>0.25in</RemoveSpace>
                        <SimplePageHeaders>true</SimplePageHeaders>
                    </DeviceInfo>
                </Configuration>
            </Extension>
        </Render>

To rename the options, add the tag <OverrideNames> then the sub element tag <Name Language="lang">(New Name)</Name></OverrideNames>

Other changes can also be configured. In the case above I am removing any blank columns and rows that are 1/4 of an inch or less. I am also forcing simple page headers to ensure that when exporting to Excel the headers are in Excel headers not in the first few rows of the document with a freeze pane. This is a common problem with exporting to Excel with page headers from Reporting Services.

To find out more about what tags are available for each option, check out MS's Technet page.

SSRS Fit to Table to Page

Adam Rink — Wed, 05 Dec 2007 05:20:00 GMT

Have you ever noticed that if a Total or Sub-Total line floats until to the last page by itself? Worse, table headers do not copy over when only a Sub-Total or Total is all by itself on the last page. To remedy this situation there is a table option that can be set called Fit table ton one page if possible.

TSQL ROW_NUMBER Function

Adam Rink — Tue, 04 Dec 2007 04:42:00 GMT

A great new feature of SQL Server 2005 is the ROW_NUMBER function. In previous versions of SQL to get a numbered row would pretty much require moving the data into a temp table with an identity column, placing an identity column on the original table, or looping through data with a cursor.

SQL Server 2005 makes adding a row number to a query a very simple task with the ROW_NUMBER function. To use this, simply write the query like normal, say Sales Orders by Customer. As one of the columns include the ROW_NUMBER() function with the OVER clause.

SELECT C.AccountNumber, C.CustomerID, SO.OrderDate,
    ROW_NUMBER() OVER (ORDER BY SO.OrderDate) AS OrderNumber
FROM Sales.Customer C
    INNER JOIN Sales.SalesOrderHeader SO ON C.CustomerID = SO.CustomerID

The ORDER BY clause sorts the data, in this case by the OrderDate. The results will now return a sequential number based on the OrderDate along with the CustomerID, OrderDate, and AccountNumber.

Another question that might come up is if we wanted to number each row by customer and then start over at the next customer. To do this, the partition attribute must be used as well.

SELECT C.AccountNumber, C.CustomerID, SO.OrderDate,
    ROW_NUMBER() OVER (PARTITION BY SO.CustomerID ORDER BY SO.OrderDate) AS OrderNumber
FROM Sales.Customer C
    INNER JOIN Sales.SalesOrderHeader SO ON C.CustomerID = SO.CustomerID

Now the data contains a rownumber (OrderNumber) that resets for each customer.

The results of the two queries are seen below:

Subreports within table/matrix cells are ignored

Adam Rink — Wed, 28 Nov 2007 02:25:00 GMT

Unfortunately, subreports within a table or matrix cannot be exported to Excel. I am not sure why MS did not allow this with Reporting Services. This came into play recently when I wanted to make a report that displayed detail data next to the header data. This would be like displaying customer information and then orders and dollars next to the customer's information. I wanted to display the data side by side with a running list of customer information on the left and a listing of orders on the right. Unfortunately, lack of a vertical cell merge forced me into a subreport within a matrix. This is not very efficient but worked.

The work around was to change out the table and go with a table of orders inside a list of customers. Unfortunately, the table is easier when modifying 30 rows of data. In a list, each field is a text box not a row and column. Now imagine moving and resizing all of them at once, can someone say Crystal Reports :O( Placing the table next to these text boxes also created interesting issues. When there was multiple orders, the text boxes would slide down on the print preview leaving a gap in the customer information. To remedy this, I had to place the table in a box. I expanded the box to the bottom of the page.

The last weird issue I had was how the table is treated in a list box. I wanted to filter the table to only display that customer's orders. Instead of calling my customer dataset for the list and order dataset for the table and filter orders based on customer; I had to make one dataset to return both customer and order information. I grouped the list by customer data, then I had to filter the table that was the same dataset by the customer number.

In all, this was a very time consuming and annoying process just to display the data. I am not sure why a table in a table does not work, why there is no vertical cell merge, and why subreports don't export to excel; but I encourage MS to fix these issues.

SSRS Query Data in Header or Footer Not Exporting to Excel

Adam Rink — Sat, 17 Nov 2007 03:47:00 GMT

One issue that I have noticed with SSRS is that headers and footers do not contain query data that is derived from a hidden text field.

The work around is to assign the data to a global parameter that is hidden. Then reference the global parameter in the footer or header. The data will now be available in Excel headers and footers.

If you are not seeing a header or footer this is because by default Excel hides the headers and footers. In Excel 2007, click the Insert menu then the Headers & Footers button. In Excel 2003, click the View menu, then Headers & Footers.

DELETE Top TSQL Statement

Adam Rink — Sun, 04 Nov 2007 17:32:00 GMT

A new addition to the DELETE command in SQL Server 2005 is the TOP statement. The DELETE TOP does the same thing as a SELECT TOP WHERE only the TOP number of rows are deleted.

This can be very helpful when there are duplicate rows of data present.

DELETE TOP (1)
FROM Sales.Customer
WHERE CustomerID = 1

This would delete one of the duplicate rows for Customer number 1

Suppose somehow the whole customer table got duplicated. I duplicated the Sales.Customer table into a tmpCustomer table.

SELECT Top 1 CustomerID, COUNT(CustomerID) AS Cnt
FROM tmpCustomer
GROUP BY CustomerID
HAVING COUNT(CustomerID) > 1

WHILE @@RowCount > 0
BEGIN
    DELETE Top (1)
    FROM tmpCustomer
    WHERE CustomerID = (SELECT Top (1) CustomerID
                        FROM tmpCustomer
                        GROUP BY CustomerID
                        HAVING COUNT(CustomerID) > 1)

END

While this worked just fine, it ran about 4 minutes for 38K rows. Let's try the dreaded CURSOR.   Notice I can stick a variable in where the TOP () statement is. I subtracted -1 because we don't want to delete every row.

DECLARE @cnt int, @custID as int

DECLARE dupCursor CURSOR FAST_FORWARD
FOR SELECT CustomerID, COUNT(CustomerID) AS Cnt
    FROM tmpCustomer
    GROUP BY CustomerID
    HAVING COUNT(CustomerID) > 1

OPEN dupCursor

FETCH NEXT FROM dupCursor
INTO @custID, @cnt

WHILE @@FETCH_STATUS = 0
BEGIN
    DELETE Top (@cnt-1)
    FROM tmpCustomer
    WHERE CustomerID = @custID

    FETCH NEXT FROM dupCursor
    INTO @custID, @cnt
END

CLOSE dupCursor
DEALLOCATE dupCursor

This ran much better at 18 seconds.

Writing JOINs vs Nested Select Queries

Adam Rink — Fri, 02 Nov 2007 03:51:00 GMT

I have seen a lot of is queries written without using the JOIN syntax. Instead the developer chose to write the query using an IN clause in the WHERE clause with a SELECT statement; a Nested Query. While many people blame nested queries on developers that don't understand joins, they can actually be beneficial. Let's take a query out of the AdventureWorks DB.

An example would be:

SELECT Distinct C.AccountNumber
FROM Sales.Customer C
    INNER JOIN Sales.SalesOrderHeader O ON O.CustomerID = C.CustomerID
    INNER JOIN Sales.SalesPerson SP ON SP.SalesPersonID = O.SalesPersonID
WHERE SP.CommissionPct >= .015

this can also written as

SELECT AccountNumber
FROM Sales.Customer
WHERE CustomerID IN (SELECT CustomerID
                        FROM Sales.SalesOrderHeader
                        WHERE SalesPersonID IN (SELECT SalesPersonID
                                                      FROM Sales.SalesPerson
                                                      WHERE CommissionPct >= .015))

The second query is very messy to read and I personally hate looking at queries like this, but it is faster. To note I have an index on the SalesOrderHeader table with columns SalesPersonID, CustomerID. A distinct has to be used because multiple orders exist for a SalesPerson and Multiple Orders belong to multiple customers.

The following are the two execution plans.

Basically, the point is to not throw out a query because it looks messy. Every query must be analyzed for performance and different approaches should be looked at.

Reseed an Identity Column

Adam Rink — Sat, 27 Oct 2007 03:12:00 GMT

Sometimes identity columns must be reseeded. A good example is when merging data from a couple of different data sources. There is a DBCC command that makes this task easy.

DBCC CHECKIDENT (<table name>, RESEED, <new value>

To find what existing identity column values are, a simple query can be written off the sys.identity_columns system view.

SELECT   OBJECT_NAME(object_id) AS [Table],
         [name] AS [column],
         seed_value,
         last_value
FROM     sys.identity_columns
ORDER BY 1

Selecting a Comma Delimited List with TSQL

Adam Rink — Wed, 26 Sep 2007 01:09:00 GMT

Sometimes instead of bringing back a table set of information, a query needs to return a comma delimited list. I have seen some crazy stuff to do this like UDFs and Cursors. There is a really simple way to do this in a select statement though.

--declare a variable first
DECLARE @Orders varchar(MAX)

--varchar max is nice because there won't be overflow if the table is large
SET @Orders = '' --we can't concatenate NULLs so make empty string

SELECT @Orders = @Orders + Convert(varchar(10),Orders) + ','
FROM dbo.Orders

PRINT Left(@Orders,Len(@Orders)-1) --remove last comma

Snapshot Transaction

Adam Rink — Fri, 21 Sep 2007 05:54:00 GMT

A new transaction level included in SQL Server 2005 is the snapshot transaction level. Snapshot transactions allow for data consistency without blocking. A Snapshot transaction fits between a dirty read, Read Uncommitted, and the Read Committed level.

Read Committed will only read data that is committed to the database. If a process is in the middle of changing data, then the Read Committed transaction will wait for that update to be complete causing some blocking. A Snapshot transaction will read previously committed data. Therefore it reads a version of the data that was consistent when it was started, but does not cause blocking because it does not wait for data to be updated.

Snapshot transactions must be enabled in the database. The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON for Snapshot transactions to work. Also, if a transaction was started with another level then it cannot be changed to snapshot. You can move from Snapshot to another form though.

Formatting SubTotal Columns in an SSRS Matrix View

Adam Rink — Sat, 15 Sep 2007 18:16:00 GMT

Developers that are new to SSRS might be frustrated with formatting Matrix subtotal columns. Once the data in the Matrix is setup the developer might choose a sub-total for one of the column groups. This makes a gray empty box on the row level which cannot be formatted. Logically, this would be where the formatting would take place.

One of my first projects with a Matrix report involved adding a double line border above the total field of a Matrix report. I struggled with this for a bit, then I realized the green triangle in the corner is for formatting the group. Right clicking this green corner will display its properties in the properties dialog. Clicking on the cell will only bring up the properties for the row group cell (Total in this example).

SQL Server Dynamic Management Views

Adam Rink — Wed, 29 Aug 2007 01:43:00 GMT

One of the greatest features, and in my opinion the one that makes SQL Server a top tier enterprise database, is dynamic management views. Before SQL Server 2005, lots of time was wasted trying to analyze and solve problems using Profiler, Query Analyzer, PerfMon and other tools. With the introduction of Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) , administrators have a place to query recorded data to analyze issues. DMVs provide vital information, such as index usage stats. Since the information is already recorded by the DBMS lot's of time is saved. Also, performance tuning and other management activities can be done proactive.

I want to briefly discuss a couple of these views.

The sys.dm_exec_requests view is quite useful to show processes running on the database.

The sys.dm_exec_sessions will tell the sessions that are logged in. More information is contained here than using sp_who2.   Join this view with the sys.dm_exec_requests view and a lot of information will be returned allowing a DBA to find out who is running what on the server.

sys.dm_db_index_usage_stats view will explain how much an index is used. It makes a great place for finding indexes that aren't needed.

The missing index views (sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups), can help to identify indexes that are needed on tables.   The DBMS logs entries into these views when a table is scanned and an index could have been used. index_group_stats is helpful because it identifies how many times the index could have been used.

To find out information relating to cached query plans, look in the sys.dm_exec_cached_plans view.   While this won't tell you a lot of meaningful information, the plan handle can be gotten and used with the sys.dm_exec_sql_text and sys.dm_exec_query_plans functions to get the XML showplan and text of the query.

If you are using a service broker application the following views are of interest:
sys.dm_broker_activated_tasks, sys.dm_broker_connections, sys.dm_broker_activated_tasks, sys.dm_broker_forwarded_messages, sys.dm_broker_queue_monitors. These views can help monitor message volume.

What about hardware information relating to the database? The sys.dm_os_wait_stats view will explain the length of time processes had to wait for resources.

Another important hardware view is sys.dm_os_performance_counters. I like this view. It is basically all the counters that are pulled in PerfMon for the SQL Server category. While it is better to pull this information in PerfMon, it is easier to manipulate by querying it here.

Correlating PerfMon with Profiler

Adam Rink — Mon, 27 Aug 2007 04:03:00 GMT

System monitor or PerfMon is a tool that most DBAs need to become familiar with. Many times Profiler will give information regarding to what is running on the database but can't pinpoint the problem. A new feature of SQL Server 2005 is the ability to correlate Profiler data with PerfMon (System Monitor).

To correlate the two logs:

Create a trace log in SQL Server Profiler.
At the same time create a PerfMon log.
Once the Profiler trace is done, stop it.
In Profiler Select File -> Import Performance Data
Select the counters to correlate.

What is great about this is that performance problems become more apparent. For instance, maybe the average disk queue spikes when a certain query is run. There might be a table scan going on and the solution might be to separate the tables unto different disk arrays.

Partitioning Tables part 2

Adam Rink — Thu, 23 Aug 2007 01:55:00 GMT

A few days ago, I explained how to partition a table in SQL Server 2005. I also mentioned how moving data is very fast when using partitions. This is because you can swap partitions to other tables without having to move data. All that happens is a pointer to the data is changed. This makes a file partition move great for archiving data.

For instance, say that we have two invoice tables, one that should contain the last two years of current invoices and an archival invoice data table. Each year is partitioned into its own filegroup. Suppose that our production table has the year 2005 in it. This has to be moved over to our archive table. On the archival data table we have to make a partition to contain 2005.

First, we need to add a filegroup for the data to be put into.

ALTER PARTITION SCHEME myscheme
NEXT USED [filearch2005]

To add a partition, we must split them. Therefore, let's use the following function:

ALTER PARTITION FUNCTION mypartfunc ()
SPLIT ('1/1/2005')

Now everything after 2005 is in its own partition and we just need to SWITCH from our Invoice table to our InvoiceArch table.

ALTER TABLE dbo.Invoice
SWITCH PARTITION 1 TO dbo.InvoiceArch PARTITION 5

After the PARTITION word we need to specify the number of the partition. Now we have moved all the 2005 data from Invoice table to the InvoiceArch table in no time without locks.

Partitioning Tables

Adam Rink — Tue, 21 Aug 2007 02:18:00 GMT

SQL Server 2005 introduced a new feature to improve performance for accessing tables. This feature is the ability to partition a table across many disk arrays. In SQL Server 2000 a table can be placed into a file group, but there is not a way to divide up a table. A DBA could make an indexed view that combines multiple tables via a UNION. Each of those tables could then be put in a file group. This takes a lot of work maintaining the code and tables. Partitioning the data also allows for data to be moved from table to table with minimal overhead.

To partition a table a Partition Function must be created first. To create this use the following command.

CREATE PARTITION FUNCTION mypartfunc (datetime) AS
RANGE RIGHT FOR VALUES ('1/1/2005','1/1/2006','1/1/2007')

Mypartfunc is the name of the function where datetime is of course the data type to divide on. The last piece is range or the values that the data is split on. Everything before 1/1/2005 is in the first file partition, 1/1/2005 - 12/31/2006 is the second, 1/1/2006 - 12/31/2006 is in a third file group and everything 1/1/2007 and greater is in the last file group. RANGE RIGHT means that the boundary point is included in the file group to the right.

Once a function is created, a scheme must then be created. A function could be used for many different schemes; therefore, a function is really the business logic. The scheme will tie the function to actual file groups. These file groups can be place on separate disk arrays.

CREATE PARTITION SCHEME myscheme AS
PARTITION mypartfunc TO
(filegroup1, filegroup2, filegroup3, filegroup4)

Myscheme is the name of the scheme and the file groups are defined.

So there is a function and a scheme. The last piece of the puzzle is to tie these to a table or index. You can easily do this with a CREATE TABLE or INDEX statement.

CREATE TABLE myTable (mydate datetime, othervalue varchar(10))
ON myscheme(mydate)

Myscheme is the scheme that was defined, where there the value of the scheme is the column in the table to partition on.

In my next blog, I will discuss how using partitions can really increase performance in copying tables.

Technorati

Adam Rink — Wed, 15 Aug 2007 04:20:00 GMT

Technorati Profile