It has come to my attention that many of us are using the SQL servers very inefficiently. Barry is going to have some training on this in the near future, but to tide us over, I have compiled a little information that will hopefully get us started.
Transact SQL is our friend, use it to your advantage. You can do almost anything you need to do with data using T-SQL. I am sure there are many things we are doing in Fox just because we haven't figured out a good way to do them in T-SQL. Books Online is a very good reference for syntax and such, and you can learn a lot from the examples they give there. The fastest way to get to the T-SQL help is to hit Shift-F1 in the Query Analyzer. Another good place I find a lot of info is at http://www.sqlteam.com. Remember, Transact SQL is good... learn it, know it, live it.
DTS packages are a very handy tool. Some examples of times when you will need to use a DTS package are exporting or importing data to or from another data source (Fox, Access, Excel, text file, etc.).
The problem is, we use them for far to many things. Many things like deleting a table or accessing data from another database on the same server can be done in T-SQL. It is far more efficient to simply put these SQL statements in a stored procedure, or execute them from the Query Analyzer than to put them into a DTS package.
1. Syntax for accessing data from another database on the same server without using a DTS package:
SELECT a.ordernumber, b.agentname
FROM orderdb.dbo.ordertable a
INNER JOIN repdb.dbo.reptable b
ON (a.agentcode = b.agentcode)
This will return the order numbers from the "ordertable" table in the "orderdb" database, and the agent name from the "reptable" table in the "repdb" database. The "dbo" is the owner of the table. If your table is owned by someone other than dbo, you would replace "dbo" with the user name. Very easy to do, and you can execute this in a stored procedure or the Query Analyzer in any database, as long as the user executing it has select permissions in both databases.
2. Dropping a table, which completely removes the table from the database. The first line of this example checks to see if the table exists before it tries to drop it.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'mytable')
DROP TABLE mytable
3. Deleting rows from a table:
There are a couple ways to do this. Using a delete statement is slowest. This method creates a log of every record deleted. Usually you only want to use this if you are using a where clause, however there may be instances where you want to use delete when deleting an entire table.
DELETE FROM mytable
WHERE fieldname1 = 1
If you are deleting all the rows from a table, consider using a truncate statement which is faster, and creates no log of the records deleted, thereby reducing system load and log space used.
TRUNCATE TABLE mytable
4. Creating a table:
CREATE TABLE dbo.mytable (
fieldname1 INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
fieldname2 CHAR(4) NULL,
fieldname3 BIT NOT NULL DEFAULT(0)
)
This creates a table owned by "dbo" called "mytable", with 3 fields.
fieldname1, which is an integer field, the primary key of the table, and an identity increment that starts at 1 and increments by 1.
fieldname2, which is a 4 character long character field and allows null values.
fieldname3, which is a Boolean field, does not allow nulls, and has a default value of 0, which evaluates to false.
There are many more paramaters that CREATE TABLE can use. Please check Books Online for these additional options. Also, please note the CLUSTERED keyword. What this tell the server to do is make the primary key a clustered index. Clustered indexes physically reorder the table, so it is usually only a good idea to use these on tables which do not change often, or use an identity increment for a primary key.
5. Create a stored procedure:
The following stored procedure will accept 2 paramters, input1, which is a 4 character variable, and input2, which is a Boolean variable. In T-SQL variables have a @ before their name.
CREATE PROCEDURE dbo.usp_myStoredProcedure
(
@input1 CHAR(4),
@input2 BIT
)
AS
SELECT *
FROM mytable
WHERE fieldname2 = @input1
AND fieldname3 = @input2
GO
That's all there is to it. The stored procedure will be compiled by the server, and statistics will be kept on it, so its execution plan can be optimized by the server, which leads to much better performance over non stored procedure queries.
That's all there is for now. If you want any more information or clarification on these examples, please check Books Online, or send an email to Barry, and he will address your questions during his training session which will be scheduled soon.