One of the very cool new feature which SQL Server 2008 gives us is an change to the INSERT statement. Now you can specify multiple rows to insert into a table from a single insert command.
The syntax is:
CREATE TABLE TableName (Column1 INT, Column2 VARCHAR(10))
INSERT INTO TableName
(1, 'test1'), (2, 'test2'), (3, 'test4')
I see this as being a very handy especially when doing an initial data load into a table as you can now load lots of data without having to run a lot of seperate insert statements.
I’ve just published a new tip on SearchSQLServer.com called Tips for scheduling and testing SQL Server backups. In it I’m talking about server backups including how to schedule those backups on SQL Server Express edition which doesn’t have the SQL Server Agent.
After you’ve inserted the data into the table, it’s time to update the data. We do this by using the UPDATE statement. The update statment can be used in two ways. The first is to update a record or set of records in a single table, by simply filtering the data in the table by using values in the table.
SET Column1 = 'Value'
WHERE AnotherColumn = 'AnotherValue'
A more complex update uses another table as the source of the data. This makes the UPDATE statement look like a combination of the UPDATE statement and the SELECT statement.
SET Column2 = AnotherTable.Column3
WHERE TableName.Column1 = AnotherTable.Column1
We can add joins into this as well, so that we can update more than one column from different tables at the same time.
SET Column2 = AnotherTable.Column3,
Column3 = ThirdTable.Column2
JOIN ThirdTable ON AnotherTable.Column5 = ThirdTable.Column4
WHERE TableName.Column1 = ThirdTable.Column1
I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the UPDATE statement. It includes more examples, and some of the other options which are available to you.
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.