SQL Server with Mr. Denny


March 21, 2008  6:11 AM

I had a great time speaking at the San Diego SQL User Group

Denny Cherry Denny Cherry Profile: Denny Cherry

I’d like to thank the San Diego SQL Server User Group for having me come and speak to them tonight.  I had a great time presenting both my SQL Server Query Tuning and SQL Server Service Broker presentations.  You can grab the slide deck and sample code from those two links.

 I was happy to fill in on short notice for them when there scheduled speak cancelled on them.  Hopefully the members liked the presentations as much as I liked giving them.  Hopefully the San Diego SQL Server User Group will invite me back in the future.

Denny

March 20, 2008  12:00 PM

Remote Desktop, it’s better than ever

Denny Cherry Denny Cherry Profile: Denny Cherry

Remote desktop is something that we’ve all used.  It’s easy to find, right there under Programs > Accessories.  But there is so much more that you can do than simply connect to a remote session with it.  If you start the program manually from the run line by running “mstsc.exe” with some switches you can do some great things.

If you add the /console switch you will be connected to the actual console of the server, not the virtual session.  This is very useful if you need to interact with a service which is running with the “Allow service to interact with desktop” option enabled.  It allow may allow you to log in if both virtual sessions are already taken by someone else.  Do keep in mind that if someone is using the physical console (ie. the actual keyboard and mouse) then you will kick them off of the machine when you log in.

If you add the /span switch your remote session will span multiple monitors.  This is very useful for people who use more than one monitor at the office (I’ve got two 20″ wide screens and it’s great to have all the desktop space for my servers as well as my workstation).

If you add the /f switch your session will be started in full screen mode.  This is handy if you usually use it in a window, but want a one time full screen connection.

If you add the /v switch you can specify on the command line which server you want to connect to.  This is very handy if you have changed the port number that the Remote Desktop service listens on for security reasons as you can also specify the port to connect to.

What a connection window of a specific size that isn’t available by dragging the slider back and forth within the GUI?  Then the /w and /h switches are perfect for you.  You can setup the window to be any size that you’d like.

I know that I use Remote Desktop everyday and these switches have made things easier and faster for me.

Denny


March 18, 2008  10:24 AM

Slide Decks and Sample Code for San Diego SQL Users Group Meeting

Denny Cherry Denny Cherry Profile: Denny Cherry

Below you will find links to download file files with the MS PowerPoint slide decks and sample files which I’ll be using at this weeks presentation at the San Diego SQL Users Group Meeting.

There is a lot of sample code in the Query Tuning file.  We’ll look through as much of it as we can depending on the amount of time that we have.

SQL Server Query Tuning

SQL Server Service Broker

I’ve included the Service Broker slide deck even though I’m not sure that we will get to it.  If there isn’t time to get to the slide deck during the meeting I’ll be happy to answer questions on it after the meeting (or posted here of course).

See you Thursday night.

Denny


March 17, 2008  11:00 AM

Back To Basics: Using Common Table Expressions

Denny Cherry Denny Cherry Profile: Denny Cherry

CTEs (Common Table Expressions) are one of the very cool features introduced in SQL Server 2005.  In there simplest most common form, think of them as a temporary single use view who’s context is only within the command which follows them directly.  The syntax of a CTE is very basic.

WITH CTE_Name (ColumnName, ColumnName) AS
(SELECT *
FROM Table)
SELECT *
FROM CTE_Name

The list of column names as part of the CTE defination is optional.  If all the columns are named this portion is not needed.  Here is an example from the AdventureWorks database.

WITH EmployeeData AS
(
SELECT e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title]
AS [JobTitle] ,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name]
AS [StateProvinceName] ,a.[PostalCode]
,cr.[Name]
AS [CountryRegionName] ,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] eINNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID] INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode])
SELECT *
FROM EmployeeData
WHERE CountryRegionName = 'United States'

When done correctly CTEs can be used to link back to themselves to join child data up the chain so you can access the parent record. This is called a recursive common table expression and is done with a UNION ALL between two queries within the CTE like so.

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS (
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevelFROM HumanResources.EmployeeWHERE ManagerID IS NULL

UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1FROM HumanResources.Employee e
INNER JOIN DirectReports dON e.ManagerID = d.EmployeeID)

SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO

The first part of the UNION ALL command shows us the top level employees who have no manager. The second query is used to link back to the managers to show the employee information including how many levels down the chain the record is.

Extreme care must be used when using recursive common table expressions as doing this incorrectly can put the SQL Server into a never ending loop while SQL is trying to recurse up the never ending tree.

Denny


March 16, 2008  9:39 PM

Webcast Recover the data, the whole data and nothing but the data you need available for viewing

Denny Cherry Denny Cherry Profile: Denny Cherry

The webcast which I did with Quest Software a couple of weeks ago “Recover the data, the whole data and nothing but the data you need” has had the recording posted for viewing on the Quest website.  If you missed the webcast live now is your chance to view it.

 Denny


March 13, 2008  8:00 AM

SQL 2008 prevents schema changes if the table must be dropped

Denny Cherry Denny Cherry Profile: Denny Cherry

When editing the table schema in the SQL Server 2008 UI and the required change requires that the table needs to be dropped and recreated by default the UI will not let you make the change.

The kinds of changes that would require that the table be dropped and recreated would be inserting a column in the middle of the table, or changing the size of a column.

This setting can be overwritten by clicking on the Tools drop down menu and selecting Options.  Under the Designers menu option, select the “Table and Database Designers” menu option and uncheck the “Prevent saving changes that require table re-creation” option.  After clicking OK you will be able to save the changes.

I recommend that you leave this setting enabled normally so that you don’t make table changes which require the object to be dropped by accident on production systems.

Denny


March 11, 2008  7:41 PM

Identity Theft: A BIG issue for IT Auditors and DBAs

Denny Cherry Denny Cherry Profile: Denny Cherry

Arian Eigen Heald has posted a good blog about identity theft titled “Identity Theft: A BIG issue for IT Auditors and DBAs” over on the Sister CISA CISSP blog.  It’s a good read, so I wanted to make sure to pass the information along.

 Denny


March 10, 2008  10:00 AM

Back To Basics: The SELECT Statement

Denny Cherry Denny Cherry Profile: Denny Cherry

There are four basic commands in databases.  They are SELECT, INSERT, UPDATE and DELETE.  Probably the most important of these is the SELECT command.  The SELECT command is how the data in the database is retrieved and displayed.

(All these code samples can be run on all versions of Microsoft SQL Server from 7.0 up.)

Like a regular sentence there are a few basic parts of the SELECT statement.  First there is a list of columns that you want to see.  Then there is the FROM portion of the statement.  This is the table or tables which you will be getting the data from.  Then is the WHERE portion of the statement which is the data filter.

When you put all this together the SELECT statement will look something like this.

SELECT id, name
FROM sysobjects
WHERE type = 'U'

If you were going to read this as a normal sentence it would read something like:
I want to see the "id" and "name" columns, from the "sysobjects" table, where the "type" column has a value of "U".

Now if we need to look at data from two different tables which is combined together we add a JOIN statement between the FROM and WHERE portions of the SELECT statement.

SELECT sysobjects.name, syscolumns.name
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = 'U'

You can see from the above statement that we are getting the table names and column names for all the user tables. When you look at the JOIN command you see that we are matching up the id column from both of the tables. Now in this example the column name is the same in both tables. This isn’t always the case. If you don’t know how the tables relate to each other, you can usually look at the foreign key constraints to see how the tables relate to each other.

If you want to get counts of data you can use the GROUP BY clause along with the COUNT() function. In this next statement we will count the number of columns in each table.

SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = 'U'
GROUP BY sysobjects.name

When ever you use a mathematical function such as MIN(), MAX(), AVG(), COUNT(), etc you have to add the GROUP BY clause to the SELECT statement so that SQL Server knows how to roll up the data.

Now as a last part of the SELECT statement to look at today, we are going to filter our GROUP BY query further, by showing all the tables that have over 5 columns. To do this we use the HAVING clause. Without the HAVING clause we would have to do a very complex query as a sub-query in the WHERE clause which would be very inefficient.

SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = 'U'
GROUP BY sysobjects.name
HAVING count(*) > 5

The HAVING phrase is very simple in it’s syntax. You put the math function that you want to use (in this case COUNT(*)) then <, >, <> or = and what ever you want to compare it to.

I hope that you find this post useful. I’ve barely scratched the surface of the SELECT statement, and what it can do. I encourage everyone to open up Books OnLine and read through the information on the SELECT statement. It includes more examples, and some of the other options which are available to you.

Denny


March 6, 2008  3:12 AM

I’ll be speaking at the San Diego SQL Server Users Group

Denny Cherry Denny Cherry Profile: Denny Cherry

The good folks of the San Diego SQL Server User Group has invited me to come speak to them on March 20, 2008.  The meeting starts at 6PM.

 If you are in the area I urge you to come and check it out.  The topic of my talk will be Query Tuning as well as a bit of Query Governor.  If you attended the SoCal Code Camp in January and weren’t able to catch my session then, now is your chance.

The slide deck will be a little different so I’ll be adjusting it a little bit.  I’ll try and get the slide deck posted before the meeting, but no promises.

More information about the San Diego SQL Server User Group can be found on thier web site http://www.sdsqlug.org.  See you there.

Denny


March 6, 2008  2:26 AM

New Paper Published: Bullet-proof Change Management – Don’t just Cross your Fingers and Hope

Denny Cherry Denny Cherry Profile: Denny Cherry

I’ve written a technical brief on how my company, Awareness Technologies, uses the Quest Software Change Director for SQL Server product to monitor for and track unauthorised changes to our environments.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: