Tables are the core of any database platform. Without tables we would be able to process data, but we would have no way to store the data. In the simplest terms tables look like Excel sheets. They both have columns and rows. When you view a table in the client tools it looks much like an Excel sheet does. While the basic concept is the same tables are very, very different that Excel sheets. SQL Server is optomized to store large quanties and data and search through that data as quickly as possible.
Unlike Excel, tables have indexes. Indexes are copies of the column which comprise the index. The reason that we use indexes is to speed up the processing of the query. Whle the index is a copy of the column, the copy within the index in sorted in order while the table is not. Because the data is sorted SQL Server can search through the data much easier and there for faster.
To make this easier to understand think of an Excel sheet with 1000 rows of data in it. Each row has a single value in column A. Each value is a random number between 1 and 1000. Now try to find all the rows with the value of 25. You need to search down the column looking for the data. This is how your table work. When you tell SQL Server to search the table for the value of 25 using this statement SQL has to look at every record in the table.
WHERE Column1 = 25
Now when you create an index on this column think of sorting the Excel sheet in order. Now find the records with the value of 25. You can simply scan down the column and find the records and not look any further. This is the same thing that SQL Server does when you us the same command but with the index. SQL Server uses the index automatically, so no changes to code are required when you create the index.
Normally when running a query against a table and using a CAST or CONVERT function against a datetime field any index is made useless. In SQL Server 2008 this problem is fixed. Microsoft has come up with a way for SELECT statements which use CAST or CONVERT against a column of the datetime datatype to continue to use the index. Now keep in mind that this only works for the datetime datatypes and not other data types.
I believe that this feature showed up in CTP 5 (November).
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.