The certification treadmill

posted @ 9/4/2008 10:47 AM by Joe Webb

Defragmenting Indexes in SQL Server 2005

I've made a couple of changes to my defragment indexes stored procedure, isp_ALTER_INDEX, based upon feedback I've received from my blog readers.  For those unfamiliar with this object, it defragments indexes in SQL Server 2005 using the ALTER INDEX command.  Check the comment header block for its documentation.

Here are the changes:

1. Added support for database and object names with special characters.
2. Added support for indexes with the ALLOW_PAGE_LOCKS option enabled.  Online reindexing is not available for indexes with this option enabled.
3. Fixed the code so that it handles "gaps" in the temp table.  In previous versions, you would hit a gap in the identity values of the temp table if rows had been deleted from it due to @minRowCount.  This issue caused all indexes after the gap to not get defragmented.

In the next version, I will add a logging feature so that you can see what indexes were defragmented, see how bad the fragmentation was, and perhaps do some trending on the data.

Let me know if you'd like me to add any other features or if you find a bug.

You can download the stored procedure here.

posted @ 9/3/2008 9:56 PM by Tara Kizer

Creating Technical Presentations

posted @ 9/2/2008 10:50 AM by Joe Webb

Updated XML search (test case with variables)

/*******************************************************************************
  Prepare script with testcase
*******************************************************************************/

DECLARE     @XMLString XML,
            @ElementEqual VARCHAR(50),
            @ElementLike VARCHAR(50),
            @AttributeEqual VARCHAR(50),
            @AttributeLike VARCHAR(50)

SELECT      @XMLString = '
                  <Customers>
                        <Customer>
                              <FirstName>Kevin</FirstName>
                              <LastName>Goff</LastName>
                              <City type="aca">Camp Hill</City>
                        </Customer>
                        <Customer>
                              <FirstName>Steve</FirstName>
                              <LastName>Goff</LastName>
                              <City type="acb"> Philadelphia</City>
                        </Customer>
                  </Customers>',
      @ElementEqual = 'Camp Hill',
      @ElementLike = 'adel',
      @AttributeEqual = 'acb',
      @AttributeLike = 'c'

/*******************************************************************************
  Search for City-nodes
*******************************************************************************/

-- Get all City where element is equal to "Camp Hill"
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City/text()[. = "Camp Hill"]') = 1

-- Get all City where element is equal to @ElementEqual
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City/text()[. = sql:variable("@ElementEqual")]') = 1

-- Get all City where element like "adel"
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City [contains(. , "adel")]') = 1

-- Get all City where element like @ElementLike
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City [contains(. , sql:variable("@ElementLike"))]') = 1

/*******************************************************************************
  Search for Type-attribute in City-nodes
*******************************************************************************/

-- Get all City where attribute is equal to "acb"
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City[@type = "acb"]') = 1

-- Get all City where attribute is equal to @AttributeEqual
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City[@type = sql:variable("@AttributeEqual")]') = 1

--Get all City where attribute like "c"
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City [contains(@type, "c")]') = 1

-- Get all City where attribute like @AttributeLike
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City [contains(@type, sql:variable("@AttributeLike"))]') = 1

posted @ 9/1/2008 2:29 PM by Peter Larsson

Sliding Window Table Partitioning

SQL Server table partitioning provides a great way to manage a time-based sliding window.  By mapping each time period to an individual partition, old data can be efficiently purged or archived using a nearly instantaneous switch out of an entire partition.  However, there are a couple of aspects of a time-base sliding window strategy that require some thought and planning.

RANGE LEFT or RIGHT

The RANGE LEFT or RIGHT partition function specification indicates which partition includes the exact match on the partition boundary.  It is especially important to consider both the RANGE specification and boundary value when the partition function data type includes a time component (smalldatetime, datetime, datatime2 and datetimeoffset) because one usually wants all data for a given date in the same partition.

In the case of a RANGE LEFT, specify the latest time allowable for the data type and beware of implicit rounding.  For example, a boundary value of ‘2008-08-30T23:59:59.999’ for a datetime partition function will get rounded up to ‘2008-08-31T00:00:00.000’ and result in data for midnight 2008-08-31 getting inserted into the right partition instead of the left.  To avoid this rounding issue, specify ‘2008-08-30T23:59:59.997’ instead. 

The following table shows examples that ensure ‘2008-08-31T00:00:00’ always ends up in the LEFT partition.  The datetimeoffset examples assume UTC time zone (+00:00) is the desired date boundary but this can be adjusted as desired.

With a RANGE RIGHT partition function, specify only the desired date (or specify date with time of midnight).  This will ensure that all data for the specified date is in the same partition.

Avoid Data movement with SPLIT and MERGE

SPLIT and MERGE is very fast when the partitions are empty because no data movement is necessary.  The goal is to plan sliding window partition maintenance accordingly.  After the start of a new period, SWITCH out old data and remove the vacated empty partition with MERGE.  SPLIT the last partition (empty) in anticipation of future data.  

The choice of a RANGE LEFT or RIGHT partition also affects how one uses SWITCH, SPLIT and MERGE to maintain the sliding window. 

Sliding a LEFT Partition Function Window

At the start of a new period with a LEFT partition function, partition 1 contains the old data, the second from last partition contains the current data and then last partition (empty) is for future data.  The partition can be maintained as follows:

1) SWITCH out partition 1 to a staging table for archive/purge

2) MERGE empty partition 1 with non-empty partition 2 to form a new partition 1 (with data)

3) SPLIT last partition so that the last 2 partitions are now empty

4) Repeat steps 1 through 3 after the start of the next period

Sliding a RIGHT Partition Function Window

The RIGHT partition maintenance strategy is slightly different that the LEFT strategy.  The difference is that partition 2 rather than partition 1 contains the old data at the start of a new period.  However, just like the LEFT strategy, the second from last partition contains the current data and then last partition (empty) is for future data.  The partition can be maintained as follows:

1) SWITCH out partition 2 to a staging table for archive/purge

2) MERGE empty partition 1 with empty partition 2 to form a new partition 1 (empty)

3) SPLIT last partition so the last 2 partitions are now empty

4) Repeat steps 1 through 3 after the start of the next period

Automating the Sliding Window Maintenance

In my next post, I’ll share a script that will help automate sliding window maintenance.

posted @ 8/30/2008 6:58 PM by Dan Guzman

SQL Server 2005 Express Job Scheduling - Part 1

I've written an article here on SQL Team on how to schedule jobs in SQL Server 2005 Express

Intro

Part 1 - Scheduling Jobs in SQL Server Express

Part 2 will be out in couple of weeks

posted @ 8/28/2008 4:32 PM by Mladen Prajdić

New New Old Job Today!

posted @ 8/27/2008 9:58 PM by Travis Laborde

The undocumented sp_MSforeachdb procedure

Late last year, I posted a blog that described the undocumented sp_MSforeachtable stored procedure in Microsoft SQL Server and how it could be used to execute commands on each table within a database. The examples I gave were executing DBCC CHECKTABLE and EXECUTE sp_spaceused() for each table in a given database.

But there's another undocumented stored procedure designed for iteration. This one allows us to iterate through each database in a SQL Server instance. It's called sp_MSforeachdb.

It's usage is very similar to that of sp_MSforeachtable; simply execute the stored procedure, passing it the command that you'd like to execute as a string. For example, to see the space consumed by each database, run the following command.

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

Notice that [?] is used as a placeholder for the heretofore unspecified database name. The results can be seen below.

As another example, you can execute sp_helpfile on each database by running the following command.

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_helpfile'

Of course undocumented implies unsupported and that there is no guarantees about whether the procedure will exist in future versions of the product. So, use at your own risk and discretion.

Got any favorite uses for sp_MSforeachdb? Feel free to share in the comments below.

Cheers!

Joe

posted @ 8/27/2008 5:28 PM by Joe Webb

How to run a process using different credentials

There are times when I need to run some process using credentials other than my current security context, such as when my userid doesn't have permissions to a remote resource but another userid does.  I can easily do this interactively via the "Run as..." option (right click a process and then enter credentials) or via the runas command in a cmd window.  The problem with these though is that I have to type in a password, which creates a problem when I want to do this as a scheduled job.  In the past, I've written a batch file to map a drive using the other credentials via the NET USE command.  I've never liked that approach so when I recently needed to revisit this, I decided I'd look for alternative solutions. 

I tried three different solutions: adding an echo to the runas command, runasspc, and CPAU. 

The first solution didn't work at all for me.  I found several pages that suggested piping the password with the runas command via echo command.  I didn't see any evidence that this approach worked, but rather they were suggesting to try it.  Here's what I tried:

echo password | runas /user:DomainName\UserName E:\Folder1\Process1.exe

I couldn't get the second solution, runasspc, to work, but I'm sure I just didn't try hard enough.  I had very little patience that day.  You don't need to install the tool, but you will need to copy all of the files and subdirectories if you want it to run on other servers.  Here is an example call:

runasspc.exe /program:"E:\Folder1\Process1.exe" /domain:"DomainName /user:"UserName" /password:"password"

The third solution, CPAU, worked great and didn't have any other dependent files which makes it easy to setup other servers with it.  Here are some example calls:

E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex E:\Folder1\Process1.exe -nowarn

E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex E:\Folder1\BatchFile1.cmd -nowarn

E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex "xcopy \\Server1\Share1\File1.txt E:\Folder1\ /Y" -nowarn

Any of the above example calls would solve my current issue, however I ended up using the last one since I'm simply doing a file copy.  The third one is better than the second one as I don't need an extra file to do the xcopy. 

Here's what I embedded into my stored procedure that copies the production databases down to development:

EXEC master.sys.xp_cmdshell 'E:\CPAU\CPAU.exe -u DomainName\UserName -p password -ex "xcopy \\Server1\Share1\File1.txt E:\Folder1\ /Y" -nowarn -wait -hide', NO_OUTPUT

There are other ways to do this, but CPAU works great for me and is lightweight.  Let me know if there is something better out there though.

posted @ 8/25/2008 2:34 PM by Tara Kizer

New Job Today!

posted @ 8/25/2008 8:38 AM by Travis Laborde

Blogging and my new Twitter site

My good friend Kevin Kline recently posed a question on one of his blogs: what is the best frequency, depth, and type of blog posting? Are readers most interested in more thorough postings that are more akin to full fledged magazine articles, with the understanding that those will appear far less often since they are more time consuming to create? Or is it better to post more frequent, but perhaps less comprehensive postings? Most of these postings would be less than a few hundred words; at the extreme they could be almost Twitter-ish in length and content.

I've been pondering that same question for quite some time. It started when I began evaluating a new utility for SQL Server. Should I create a post that shares with the world that I have found this utility, that I'm eager to evaluate it, and that asks for others to share their experiences with it? That eventually may lead to a relatively lengthy review of the product.

Or is it more beneficial to perform the entire evaluation before mentioning it in a blog and later post a single comprehensive review of the software utility? The risk there is that I will never feel that I've reached a point where I've learned enough to provide a good and insightful review and thus never write about it.

I've bounced between the two extremes, trying to find a happy median. So far I've decided to err on the longer side, with postings that range in the 500 - 600 word range.

However, while attending DevLink the past weekend, I learned how Twitter can be used to bridge the gap. I'll admit that I've been somewhat under a rock with regards to social networking sites. I just haven't really made the time to figure them out and see how they can be used in a good way. I had always rather naively assumed that they would be yet another distraction and a time sink. However, thanks to Microsoft's Brian H Prince's soft skills presentation at DevLink, I'm beginning to see that they can be used in a more productive and positively engaging way.

So, I've created a Twitter account that will give me a forum for posting those more frequent, less comprehensive posts. If you're interested and you use Twitter, check it out, http://www.twitter.com/joewebb.

If you're a Twitter person, I'd love to hear your experiences on how it can be used in a productive manner. I'm still a neophyte in this social networking arena.

Cheers!

Joe

posted @ 8/25/2008 6:44 AM by Joe Webb

DevLink 2008 Presentation

posted @ 8/23/2008 2:58 PM by Joe Webb

Using Excel as a Reporting Services datasource

While teaching a Microsoft SQL Server Reporting Services class in Charlotte, North Carolina, this week, one of the students asked to see an example of a report that uses a Microsoft Excel Spreadsheet as its datasource. Here are some screenshots from the demonstration.

The first step in using an Excel spreadsheet as a datasource is to create a System Data Source Name (DSN) for the spreadsheet. Open the ODBC Data Source Administrator from Control Panel | Administrative Tools.

Add a new System DSN, providing a name in the Data Source Name box, and selecting the workbook that you wish to use for your report.

Next, from within Business Intelligence Developers Studio (BIDS), create a new data source. Change the Type to ODBC. Click the Edit... button and select the DSN that you just created in the prior steps.

To use the data from the spreadsheed in a report, create a new Dataset. For the query, type

SELECT *

FROM [Sheet1$]

Note that you can replace Sheet1 with the actual name of the worksheet tab in the Excel Workbook. The syntax requires that the name of the worksheet be followed by a dollar sign ($). As such, you must enclose the worksheet name in square brackets [] as shown below since a dollar sign is not considered a valid character in a select statement.

Once we've created the Dataset, we can display it in a report like any other Dataset.

Thanks Kim and Debbie for the question and for providing the fodder for this post.

Cheers!

Joe

posted @ 8/22/2008 4:46 PM by Joe Webb

SQL Server 2005 temporary tables bug, feature or expected behavior?

In my opinion these 2 batches should behave the same but they don't. the first fails and the second runs ok.

I've searched through Books Online for any clue but i haven't really found anything useful.

Does anyone have any clue about this?

Permissions and transaction isolation levels are not an issue here.

-- just to make sure it doesn't already exist
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
BEGIN 
    SELECT 'DROP Temporary table'
    DROP TABLE #tempTable
END 
IF OBJECT_ID('normalTable') IS NOT NULL
BEGIN 
    SELECT 'DROP Normal table'
    DROP TABLE normalTable
END 

GO
-- THIS FAILS WITH ERROR MESSAGE: Msg 2714, Level 16, State 1, Line 7 There is already an object named '#tempTable' in the database.
SELECT 'Temporary table'

SELECT * INTO #tempTable FROM [master]..spt_values
SELECT * FROM #tempTable
DROP TABLE #tempTable

SELECT * INTO #tempTable FROM [master]..spt_values
SELECT * FROM #tempTable
DROP TABLE #tempTable

GO
-- THIS RUNS OK
SELECT 'Normal table'
SELECT * INTO normalTable FROM [master]..spt_values
SELECT * FROM normalTable
DROP TABLE normalTable

SELECT * INTO normalTable FROM [master]..spt_values
SELECT * FROM normalTable
DROP TABLE normalTable

I've also tried this on SQL Server 2000 and it behaves the same. Haven't tried it on SQL Server 2008 though.

posted @ 8/21/2008 4:56 PM by Mladen Prajdić

More conditional formatting in SSRS

In a prior post, I demonstrated how to use conditional formatting techniques to change the background color of every other row in tabular report. In that example, I used the Immediate If (IIF) function to change the BackgroundColor property of the odd rows to silver.

The IIF function only allows for two states. In my example, the odd rows were silver and the even rows had a white background color.

In the comments section of that post, one reader, Wally, asked to have more than two states, silver, white, and red. Another reader, Darin, replied that you can nest your IIF statements. You can read the comments here. Thanks, Wally and Darin! I love with people comment to posts, especially when it's in the spirit of helping each other.

Nesting IIF functions is commonly done. I've done it many times. However getting more than three states can get rather messy. You have multiple IIF functions nested, each with its own set of commas and parenthesis. It's very easy to mistype something and have the whole thing not work.

Fortunately, SSRS supports other VB functions that we can use to create more complex conditional formatting settings.

Let's consider a rather simple Sales Report as an example. In the following report, we have a report with three columns of information - the Sales Order Id, the Order Date, and the Sub Total for the Sales Order.