Not all filter commands are created equal. Different filtering operations should be used at different times to get the best performance our of your SQL Server.
While the JOIN, EXISTS and IN filters can give you the same results the way that SQL Server gets to the data is very different and can lead to poor system performance. Also when doing a select vs. a delete these same operators will give different execution plans.
I’ll refer you do this file which will provide you with some sample code which can be run against the AdventureWorksDW sample database. Run each query with the execution plan being displayed. You’ll see that the IN and EXISTS both produce the same plan, while the JOIN produces a better plan when it comes to selecting data. However when it comes to deleting the data the EXISTS and IN produce a better plan than the JOIN command does. (Don’t worry, these delete scripts won’t actually remove any data from the table. The data these scripts try to delete doesn’t actually exist. We are looking for execution plans here, not actual deletes).
I’ve recently published a new tip on SearchSQLServer.com called “SQL Server tempdb best practices increase performance“.
While the SELECT statement is probably the most important command, the INSERT comes in handy. The INSERT statement is used to do exactly what it sounds like, it inserts data into a table.
There are two ways to insert data into a table. The first is to pass in each of the values, and the second is to insert the data from a select statement.
For both commands we’ll be using a new table with this definition.
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertTable')
DROP TABLE InsertTable
CREATE TABLE InsertTable
First lets look at passing in the values. With this syntax we specify the names of the columns, and then specify each of the values.
INSERT INTO InsertTable
Second we’ll look at the SELECT statement. There are two ways we can do this as well. The first is to load a single set of values with the select statement. When doing this you can optionally specify the column names or not.
INSERT INTO InsertTable
SELECT 0, 'test'
The second option with the SELECT statement is to use a SELECT statement from a table. All of the functionally of the SELECT statement is available when using the SELECT statement as part of the INSERT statement.
INSERT INTO InsertTable
SELECT id, name
We can also do this with some of the more advanced functions of the SELECT statement.
INSERT INTO InsertTable
SELECT sysobjects.name, count(*)
JOIN syscolumns ON sysobjects.id = syscolumns.id
I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the INSERT statement. It includes more examples, and some of the other options which are available to you.
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.
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.
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.
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.
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
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
,e.[Title] AS [JobTitle] ,c.[Phone]
,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])
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
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1FROM HumanResources.Employee e
INNER JOIN DirectReports dON e.ManagerID = d.EmployeeID)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
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.
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.
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.
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.