wetmatter nonsense

Needing some sort of variation or at least a break from my certification studies I decided to revisit a title I purchased a while back: "Beginning SQL Server 2008 for Developers" from @Apress written by Robin Dewson. Truly glad I did because one of the problems I have when illustrating data related issues to clients is having to piece together a bloody spreadsheet. I typically would query the tables then query the INFORMATION_SCHEMA.COLUMNS or sys.columns to get the column names to use as headers. Then I would copy and paste them into the predetermined rows.

Well not any more because page 42-43 of this awesome book had life changing screenshots of the Options dialog. Big deal right? Well a big deal for me at least! Following the path of Query Results --> SQL Server --> Results to Grid lead me to a sudden state of euphoria. There I saw it as the lights of heaven filled my home, blurring everything around me as if a spotlight and magnify focused in on the wonderful words that read: "Include column headers when copying or saving the results". Was it really true, are my days of querying sys.columns or the INFORMATION_SCHEMA.COLUMNS finally over? In a way, maybe, not really, but at the very least delivering requested spreadsheets to clients will be a bit faster. I was so excited I tried this on MSSQL 2005 and sure enough it is there!

What you need to do is: 

Step 1 - Go to Tools --> Options

Step 2 - Expand Query Results --> SQL Server --> Results to Grid

Then check the box titled: Include column headers when copying or saving the results

Step 3 - Copy some cells

If you have tabs opened already, open a new query window and try it. You should see the menu item "Copy with Headers" as a right click option

Step 4 - Then paste your results into the spreadsheet

Just a side note, if you are dealing with datetime data types you should consider making the predetermined columns in the spreadsheet as a text category by formatting the cells, under the Number tab select the "Text" category.

Cover image taken from Apress.

Loading mentions Retweet

Today I was working on a SELECT query, nothing special or anything to get excited about. Looking through a single table for a few pieces of data. For this example I will refer to the Northwind database.

 
SELECT [ProductID] 
       ,[ProductName] 
       ,[SupplierID] 
       ,[CategoryID] 
       ,[QuantityPerUnit] 
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (CategoryID = 3) OR (UnitPrice = 14.00) OR (UnitsInStock > 100) 

Looks fairly straight forward right and it is. I mean there is nothing wrong with it. I can execute it and it returns 25 rows exactly what I was searching for. Well I decided to check out the Estimated Execution Plans (CTRL+L) while you are in SSMS to see what it looked like.

One thing I did not notice until now was the fact I can place the cursor over the lines or data flow arrows and get a little more detail about what is being returned. I knew that hovering over the icons returned some information about each, but I really didn't know what the data represented.

Then I remembered reading a post a while back that said it is better in this particular scenario to use a UNION ALL instead. Matter of fact here is the link: donetheaven.com. I love books marks! So the re-write would look like the following.

 
SELECT [ProductID]
       ,[ProductName]
       ,[SupplierID]
       ,[CategoryID]
       ,[QuantityPerUnit]
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (CategoryID = 3)
 UNION ALL
 SELECT [ProductID]
       ,[ProductName]
       ,[SupplierID]
       ,[CategoryID]
       ,[QuantityPerUnit]
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (UnitPrice = 14.00)
 UNION ALL
 SELECT [ProductID]
       ,[ProductName]
       ,[SupplierID]
       ,[CategoryID]
       ,[QuantityPerUnit]
       ,[UnitPrice]
       ,[UnitsInStock]
       ,[UnitsOnOrder]
       ,[ReorderLevel]
       ,[Discontinued]
 FROM [Northwind].[dbo].[Products]
 WHERE (UnitsInStock > 100)  

This query returned 27 rows and the execution plans for this turned out quite different.Take a look.

Loading mentions Retweet

As you may have heard SQL Saturday Phoenix is underway. We are aiming for an end of July time frame and are in need of the community's involvement. We need presenters, sponsors and volunteers. All is open to suggestions so please, PLEASE voice your opinion and participate. 

If you are interested in supporting SQL Saturday Phoenix please signup using the appropriate form below. Please pass this information on.

• Presenters Signup form
• Sponsors Signup form
• Volunteers Signup form
• Suggestions form

Loading mentions Retweet

This is a quick vid that steps you through the process of creating a database snapshot, restoring a database from a snapshot and dropping a snapshot. I typically use snapshot while I am in a testing mode. I quickly create a snapshot and make all my modifications, run through my test and blow it all away by restoring from my recent snapshot. It is very useful and I use it often on my local development instances. Hopefully you will find it just as useful.

Create Database Snapshot

 CREATE DATABASE AdventureWorks_20100401_1700 ON
 (
    NAME = AdventureWorks_Data
    ,FILENAME = '<file path>\AdventureWorks_20100401_1700.ss'
 )
 AS SNAPSHOT OF AdventureWorks; 

Revert from a Database Snapshot

 RESTORE DATABASE AdventureWorks FROM
 DATABASE_SNAPSHOT = 'AdventureWorks_20100401_1700'; 

Drop Database Snapshot

 DROP DATABASE AdventureWorks_20100401_1700 

Updated 3/24/2010 - Note: SQL Server 2005 Standard Edition does not support Database Snapshots. SQL Server 2005 Enterprise and Developer Editions do.

For more information about Database Snapshots visit the Microsoft site for Database Snapshots

Loading mentions Retweet

Is SQL Saturday really coming to Phoenix? Well the announcement was made public at the Arizona PASS chapter in a recent user group meeting. It is currently being lead by @Hafthor, @Coneybeer and myself @sqlsamson. We have teamed up and are looking to get the community involved! If you don't know what SQL Saturday is then allow me to brief you on it. It is a one day FREE training event that is conveniently held on a Saturday. In these troubled times FREE training never sounded so good! How great is that? SQL Saturday offers two tracks with four to six sessions for each track lasting about an hour each. The best part is that the speakers are fellow SQL Server enthusiast from our very own local community. You know them and you love them so come out and show your support!

If you are familiar with SQL Saturday then you know it was picked up by (PASS) the Professional Association of SQL Server not long ago and the fact it is in its planning stages here in Arizona is exciting. With such a large and overwhelming amount of knowledge sharing that SQL Saturday has to offer, it will be to valuable asset to the community. Keep in mind that SQL Saturday won't be a success unless you get involved. We need support from the community in several different areas. If you are apart of another technical group please spread the word that SQL Saturday is coming and we are looking for speakers/presenters, sponsors, volunteers, and of course venues.

If you are willing to present, sponsor, volunteer or can assist with getting locations please contact either one of the following:

• Samson Loo: @sqlsamson - sqlsatphx [at] wetmatter.com
• Chris Coneybeer @coneybeer
• Hafthor Stefansson @hafthor

For more information about SQL Saturday please visit SQLSaturday.com. I hope to hear from you and more importantly I hope to see you there!!!!

For information about the Arizona PASS chapter please visit Arizona PASS

-- samson

Loading mentions Retweet

This video steps you through the installation process of installing
SQL Server 2000 very quickly and very straightforward. This video is
one part of a series that goes through the upgrade process from 2000
to 2005 and on to 2008.

Loading mentions Retweet

Recently I faced a problem with a mobile device (subscriber) which had a suspect database. I tried running the following command to check the database:

 DBCC CHECKDB ('databasename') 

 EXEC sp_resetstatus 'yourDBname';
 ALTER DATABASE yourDBname SET EMERGENCY
 DBCC checkdb('yourDBname')
 ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
 ALTER DATABASE yourDBname SET MULTI_USER 

Loading mentions Retweet

Typically in most cases you find yourself removing leading zeros but in this case I needed to add leading zeros to a column. 

Remove Leading Zeros

 SELECT CAST(CAST(ColumnName AS INT) AS VARCHAR(10)) FROM TableName

Let's Pad the field

Since I needed 6 chars this will add 3 spaces to the front of the numbers

 SELECT STR(ColumnName, 6) FROM TableName 

Add Leading Zeros

Let's say we need to add zeros to an employee id, well this would be an easy way to accomplish it. First we pad it with spaces and replace those spaces with zeros.

 SELECT REPLACE(STR(ColumnName, 6), SPACE(1), '0') FROM TableName 

SELECT SalesPersonID 
       ,REPLACE(STR(SalesPersonID, 6), SPACE(1), '0') AS PaddedSalesPID  
FROM   Sales.SalesPerson

Note: the SPACE(1) is equivalent to ' ' (That is a Tick Space Tick) So two Ticks with a space in between)

Update Table

UPDATE Sales.SalesPerson 
SET newSalesPID = REPLACE(STR(SalesPersonID, 6), SPACE(1), '0')

Loading mentions Retweet