Lunch-n-Learn #01: SQL Server 2005 Features

Yesterday I did a quick Lunch-n-Learn here at my company on a few SQL Server 2005 features and this post is just a follow-up to remind everyone what we saw and where you can find those features.  I will try to still make this useful for those of you who didn’t attend.

0:  To make any of these code examples work please download and install the Sample Databases for SQL Server.  (Don’t worry, they’re pretty small.)

1. The first thing that you folks saw was the intelli-sense where as I was typing the name of a table and SQL Server Management Studio (SSMS) was finishing the table name for me.  That was the only SQL Server 2008 exclusive feature that you saw in the entire session.  You can only see this if you have the SSMS 2008 client pointed to a SQL 2008 db.
2. I showed you guys how to find out the name of every table and column of the db that you are in using the system views:
   SELECT *
   FROM INFORMATION_SCHEMA.TABLES

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS

3. Ticks!  How did this apostrophe get into our db?  And how do we get it out?
   SELECT  [Comments] ,[ProductReviewID] ,[ProductID] ,[ReviewerName] ,[ReviewDate] ,[EmailAddress] ,[Rating] ,[ModifiedDate]
     FROM [AdventureWorks].[Production].[ProductReview]

Well you just use 2 ticks to insert a single tick.  Said again, you use “’’” to insert “’” into SQL. Here’s an example from the AdventureWorks database:
SET IDENTITY_INSERT [Production].[ProductReview] ON
INSERT [Production].[ProductReview] ([ProductReviewID], [ProductID], [ReviewerName], [ReviewDate], [EmailAddress], [Rating], [Comments], [ModifiedDate])
VALUES
(6, 709, ‘John Smith’, CAST(0x0000941800000000 AS DateTime), “>‘john@fourthcoffee.com’, 5
, ‘I can”t believe I”m singing the praises of a pair of socks, but I just came back from a grueling
3-day ride and these socks really helped make the trip a blast. They”re lightweight yet really cushioned my feet all day.
The reinforced toe is nearly bullet-proof and I didn”t experience any problems with rubbing or blisters like I have with
other brands. I know it sounds silly, but it”s always the little stuff (like comfortable feet) that makes or breaks a long trip.
I won”t go on another trip without them!’, CAST(0x0000941800000000 AS DateTime))
SET IDENTITY_INSERT [Production].[ProductReview] OFF

And we can get it out like this:
UPDATE [Production].[ProductReview]
   SET Comments = REPLACE(Comments, ””, ”)
 WHERE ProductReviewID = 6

4. To make your keyboard as useful as mine what you do is open up SSMS click on Tool > Options > Environment > Keyboard >and add the commands that you would like the corresponding shortcut key to execute.
   
5. What Exactly is XACT_ABORT?  When XACT_ABORT is ON SQL Server will stop and roll back the transaction as soon as it hits an error; it won’t continue processing all the way to the end.  “When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.”
   Use this script to see it work for yourself.
6. The next feature that I showed you was how you can create an Identity Column for the data you are  selecting without having to insert the data into a table by using the ROW_NUMBER() function.  I mentioned several other features along with that one and I will demo those features in our next Lunch-n-Learn.  In the meantime here’s a script that will show you it do 4 different row numbers on 4 different fields all at the same time.
   /* SQL Purists: please don’t freak out at this script, I’m just showing functionality*/
SELECT TOP 500 *
             ROW_NUMBER() OVER (ORDER BY AvgCPUTime DESC) AS 'AVG Time Rank'
       ,     ROW_NUMBER() OVER (ORDER BY total_cpu_time DESC) AS 'Total CPU Rank'
       ,     ROW_NUMBER() OVER (ORDER BY total_duration_time DESC) AS 'Total Duration Rank'
       ,     ROW_NUMBER() OVER (ORDER BY total_execution_count DESC) AS 'Total Executions Rank'
       , a.TEXT AS 'StatementText'
       , a.total_cpu_time
       , a.total_execution_count
       , a.total_duration_time
       , a.AvgCPUTime
       , a.number_of_statements
       , a.plan_handle
       , SUBSTRING(a.TEXT, 1, 100) AS 'hundy', a.name
  FROM (
       SELECT
             sql_text.TEXT,
           SUM(qs.total_worker_time) AS total_cpu_time,�
           SUM(qs.total_elapsed_time) AS total_duration_time,�
           SUM(qs.execution_count) AS total_execution_count,
           SUM(qs.total_worker_time) / SUM(qs.execution_count) AS AvgCPUTime,
           COUNT(*) AS  number_of_statements,�
           qs.plan_handle , db.name
       FROM�
           sys.dm_exec_query_stats qs
           CROSS apply sys.dm_exec_sql_text(sql_handle) AS sql_text
       LEFT OUTER JOIN sys.databases db
         ON sql_text.dbid = db.database_id
       --WHERE dbid = 10
       GROUP BY sql_text.TEXT,
       qs.plan_handle , db.name
)a
ORDER BY AvgCPUTime DESC
7. Comparing which way is better.  To compare how long and how much data was needed to satisfy two different queries you can just click on the Include Client Statistics button before you run the first query and it will start capturing the statistics for you:
   
8. And finally the built-in reports.  To run any of them just Right-Click any database in SSMS Object Explorer and navigate to the report that you want:
   

That pretty much wraps it up for what we covered yesterday.  I will put some examples together for the Common Table Expressions (CTE), RANK, DENSE_RANK, and NTILE fuctions that I mentioned yesterday so that we can go over them at the next one.  Next Tuesday work good for you?  🙂
If you would like anything else covered you can just email me or comment here.